MySQL 慢查詢根因分析與治理體系：從 Slow Log 到節點級即時監控

常見慢查詢成因

• 缺失索引導致全表掃描 (百萬級表災難性)。
• 缺 LIMIT 返回海量結果集，佔滿網路/記憶體。
• filesort/臨時表溢寫磁碟 (I/O 飆升)。
• 線上庫特有：連接數暴增 (max_connections 耗盡)。

傳統監控三板斧與盲區

節點級即時監控方案 (作者實踐)

核心能力

1. 輕量級 Agent：運行於 DB 節點，微秒級採集。
2. 即時慢查詢捕獲：long_query_time=1 + slow_query_log=ON，毫秒級寫入。
3. 智能過濾：白名單排除已知定時任務/全量同步 SQL，聚焦真正噪音。
4. 連接數暴增自動保存 Processlist：觸發閾值時自動 SHOW FULL PROCESSLIST 落盤，事後複盤利器。
5. Binlog 異常時間段定位：配合 mysqlbinlog 快速鎖定資料不一致窗口。

關鍵配置 (MySQL 5.0+)

-- 動態調整
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- my.cnf 固化
[mysqld]
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log

mysqldumpslow 進階用法

# 按返回行數排序 Top 10
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# 按耗時排序含 LEFT JOIN 的 Top 10
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log

# 參數說明
-s c/t/l/r  : 次數/時間/查詢時間/返回行數 排序
-t N       : Top N
-g "regex" : 正則過濾 (不區分大小寫)

查詢緩存 (Query Cache) 關鍵指標

建議：Qcache_lowmem_prunes 持續增長 → 擴大 query_cache_size 或關閉 QC (高併發寫入場景 QC 往往成瓶頸)。

治理閉環流程

1. 發現：Agent 即時推送慢查詢/連接數告警。
2. 定性：EXPLAIN + SHOW CREATE TABLE + SHOW INDEX 判斷缺索引/錯誤駕駛。
3. 優化：加索引、改 SQL、調參數、拆大表。
4. 驗證：預發環境回放流量 / pt-query-digest 對比。
5. 固化：變更納入變更管理，索引變更走 Online DDL (pt-osc/gh-ost)。

結語

拋棄「事後諸葛亮」式周報，擁抱「節點內核級即時觀測 + 智能降噪 + 自動現場保存」。慢查詢治理不是一次性專案，而是持續運行的基礎設施能力。