MySQL 慢查詢日誌完整指南：開啟、分析、調優實戰 (含 mysqldumpslow/pt-query-digest)

慢查詢定義

執行時間超過 long_query_time (默認 10s，微秒精度) 的 SQL，記錄至慢查詢日誌。不含鎖等待、網絡傳輸時間。

開啟配置 (my.cnf)

[mysqld]
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
long_query_time = 0.5          -- 記錄 >500ms (微秒級)
log_queries_not_using_indexes = 1  -- 無索引查詢也記錄
log_slow_admin_statements = 1      -- 記錄 OPTIMIZE/ANALYZE/ALTER 等
# log_output = FILE,TABLE        -- 5.1+ 支援寫入 mysql.slow_log 表

動態開啟 (無需重啟)：

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;

日誌格式解讀

# Time: 2024-12-15T10:30:00.123456Z
# User@Host: root[root] @ localhost []
# Thread_id: 123  Schema: test  QC_hit: No
# Query_time: 2.345678  Lock_time: 0.000123  Rows_sent: 100  Rows_examined: 1000000
SET timestamp=1702636200;
SELECT * FROM big_table WHERE name LIKE 'abc%';

• Query_time：總耗時 (核心指標)
• Lock_time：鎖等待
• Rows_examined：掃描行數 (大於 Rows_sent 表明無索引/掃描過多)

分析工具鏈

1. mysqldumpslow (官方自帶)

pt-query-digest /data/mysql/log/slow.log --limit 20 --filter '\$event->{Query_time} > 1'
# 輸出：總數、總時間、平均、中位數、95%線、查詢樣本、EXPLAIN 計劃、索引建議

3. 圖形化：MySQL Workbench / Grafana + Prometheus (mysqld_exporter)

實戰調優流程

1. 收集高峰期 1~2h 慢日誌。
2. pt-query-digest 產出 Top 20 消耗總時間最長的查詢 (而非單次最慢)。 3. 對每條：EXPLAIN → 檢查 type (ALL/index/range/ref/eq_ref/const)、key、rows、Extra (Using where/Using index/Using temporary/Using filesort)。 4. 制定索引/改寫/分區/架構調整方案。 5. 測試環境驗證 EXPLAIN 變化、實際耗時下降。 6. 上線觀察慢日誌消失/指標改善。

常見慢查詢模式與對策

避坑指南

• log_queries_not_using_indexes=1 會產生海量日誌，**僅臨時開啟排查**，長期關閉。
• 慢日誌文件需定期切割 (logrotate) 防止磁碟爆滿。
• long_query_time 設置過小 (如 0) 會記錄所有查詢，性能開銷大、日誌無價值。
• 關注 Rows_examined / Rows_sent 比值，> 10:1 通常有優化空間。