百萬、千萬級表上,慢查詢往往由多重因素疊加引發:缺索引、全表掃描、filesort、連接數暴增、鎖競爭、I/O 抖動……本文從監控盲區入手,構建「發現→定位→優化→預防」閉環體系。
| 監控手段 | 核心痛點 | 業務影響 |
|---|---|---|
| Zabbix/Prometheus 指標採集 | 外部視角,無法見庫內執行計劃、鎖等待、緩存命中率細節 | 只能事後查歷史,無法實時阻斷惡意 SQL |
| 每周性能報表 | 粒度太粗,突發性能抖動(如定時任務、大促流量)完全漏報 | 故障發生時無告警,事後分析也難還原現場 |
| Slowlog Top 10 | 慢日志爆發時 Top 10 失去代表性;定時任務佔據榜單干擾判斷 | DBA 整天優化「必跑的定時任務」,真正業務慢查被淹沒 |
部署在 MySQL 節點側的輕量 Agent,核心能力:
long_query_time 閾值SHOW PROCESSLIST:連接數超閾值時凍結現場,事後複盤利器mysqlbinlog 精準定位異常時間窗:結合 Binlog 還原數據變更軌跡-- 查看當前慢查詢閾值(默認 10 秒,生產建議 1 秒甚至 0.5 秒)
SHOW VARIABLES LIKE 'long_query_time';
SET GLOBAL long_query_time = 1;
-- 開啟慢查詢日誌並指定文件
SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
/etc/my.cnf[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1 -- 記錄未走索引的查詢(慎用,量大)
log_slow_admin_statements = 1 -- 記錄管理命令(ALTER, ANALYZE 等)
log_slow_slave_statements = 1 -- 記錄從庫慢 SQL
mysqldumpslow 專業分析姿勢# 按訪問次數排序,Top 20 高頻慢查
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log
# 按平均耗時排序,找出最「拖慢」響應的語句
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log
# 針對特定模式(如 LEFT JOIN)分析
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log
# 參數速查:-s 排序方式(c/t/l/r=次數/時間/鎖時間/返回行數),-t Top N,-g 正則過濾
EXPLAIN 看執行計劃重點關注:type(目標 ref/range/eq_ref,避免 ALL)、key/possible_keys、rows(預估掃描行)、Extra(Using filesort、Using temporary、Using index)。
(a,b,c) 支持 a/a,b/a,b,c 查詢user_id、order_no)置前SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b) 避免回表| 反模式 | 優化寫法 | 收益 |
|---|---|---|
SELECT * | 顯式列出所需列 | 減少網絡傳輸、避免回表、利用覆蓋索引 |
WHERE col != val / NOT IN | WHERE col < val OR col > val / NOT EXISTS | 可能走索引範圍掃描 |
OR 跨列查詢 | UNION ALL 分開查再合併 | 各分支可獨立走索引 |
隱式類型轉換 WHERE varchar_col = 123 | WHERE varchar_col = '123' | 避免索引失效 |
| 大事務長鎖定 | 拆分小事務、調整隔離級別、優化索引減少鎖行 | 降低鎖競爭、縮短持鎖時間 |
Too many connections 報錯max_connectionsSHOW PROCESSLIST 觀察 Sleep 狀態連接堆積 → 應用連接池未釋放或未配置 wait_timeoutSHOW GLOBAL STATUS LIKE 'Threads_connected' 趨勢圖對比業務流量maxLifetime、idleTimeout;使用 HikariCP 等成熟池wait_timeout=600、interactive_timeout=600 及時回收空閒連接MySQL 8.0 移除 Query Cache,但以下緩存仍需精調:
innodb_buffer_pool_size 設為物理內存 60%-80%,監控 Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads 命中率 > 99%innodb_log_buffer_size(大事務場景調大至 16-64MB)sort_buffer_size、join_buffer_size(按會話分配,慎調大,防 OOM)table_open_cache、table_definition_cache 適應表數量CREATE INDEX、SQL 改寫、參數調整建議單慢查詢治理不是一次性專案,而是**持續運行的數據庫免疫系統**。從「救火」轉向「防火」,始於可見,成於自動化。