MySQL 啟用慢查詢日誌完整指南：配置、分析工具、生產建議

MySQL 啟用慢查詢日誌完整指南：配置、分析工具、生產建議

慢查詢日誌是效能調優核心工具，記錄執行時間超過閾值或未使用索引的 SQL，助力發現效能瓶頸。

一、配置啟用

Linux (my.cnf)

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow.log
long_query_time=2                 # 超過 2 秒記錄（預設 10 秒）
log_queries_not_using_indexes=1   # 記錄未用索引查詢
# log_throttle_queries_not_using_indexes=10  # 5.6+ 限流頻率（每分鐘）
# log_slow_admin_statements=1    # 記錄管理語句（ANALYZE, OPTIMIZE 等）
# log_slow_slave_statements=1    # 從庫慢查詢（5.6+）

Windows (my.ini)

[mysqld]
slow_query_log=1
slow_query_log_file=E:\mysql\log\slow.log
long_query_time=2

動態開啟（無需重啟）

SET GLOBAL slow_query_log=1;
SET GLOBAL long_query_time=2;
SET GLOBAL log_queries_not_using_indexes=1;

二、日誌分析工具

1. mysqldumpslow（官方內建）

# 按查詢次數排序 Top 20
mysqldumpslow -s c -t 20 /var/lib/mysql/slow.log

# 按總耗時排序 Top 20
mysqldumpslow -s t -t 20 /var/lib/mysql/slow.log

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

# 含 LEFT JOIN 的 Top 10 耗時查詢
mysqldumpslow -t 10 -s t -g "left join" /var/lib/mysql/slow.log

參數說明：-s 排序鍵（c=count, t=time, l=lock time, r=rows），前綴 a 為倒序；-t Top N；-g 正則過濾。

2. pt-query-digest（Percona Toolkit，推薦）

pt-query-digest /var/lib/mysql/slow.log --limit 20 --output json > report.json
# 或直接生成人類可讀報表
pt-query-digest /var/lib/mysql/slow.log --top 20

優勢：多維度統計、指紋分組、EXPLAIN 自動分析、JSON/HTML 輸出。

三、狀態變數監控

SHOW GLOBAL STATUS LIKE '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 123   |
+---------------------+-------+

Slow_queries 計數器可用於監控告警。

四、生產環境最佳實踐

1. 長期開啟：性能影響極微，建議永久啟用。
2. 設定合理閾值：OLTP 系統 long_query_time=1 或 0.5；OLAP 可放寬至 5-10 秒。
3. 啟用 log_queries_not_using_indexes：但需配合 log_throttle_queries_not_using_indexes 限流，避免全表掃描刷爆日誌。
4. 日誌輪轉：配合 logrotate 定期切割、壓縮、清理舊日誌。
5. 定期分析：每日/每週跑 pt-query-digest 生成報表，納入效能基線追蹤。
6. 關聯 EXPLAIN：對 Top SQL 執行 EXPLAIN 驗證執行計劃，優化索引或改寫 SQL。

五、常見誤區

• 以為 long_query_time 單位是毫秒 → 實為秒（支援微秒精度，如 0.05）。
• 只看 Query_time 忽略 Lock_time、Rows_sent、Rows_examined → 後三者對診斷鎖爭用、全表掃描同樣關鍵。
• 慢查詢日誌記錄的是完成的查詢，正在執行的長查詢不會出現 → 需配合 SHOW PROCESSLIST 或 performance_schema 即時監控。