MySQL 慢查詢日誌完整配置與分析實戰指南

慢查詢日誌是 SQL 調優的核心依據。本文涵蓋開啟配置、參數說明、測試驗證、分析工具（mysqldumpslow/pt-query-digest）、常見優化方向。

一、核心參數說明

二、配置範例 (my.cnf / my.ini)

[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
log_throttle_queries_not_using_indexes = 100

修改後 systemctl restart mysqld 或動態生效：

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

三、測試驗證

mysql> SET GLOBAL long_query_time = 0.01;
mysql> SELECT SLEEP(2);
mysql> SHOW GLOBAL STATUS LIKE 'Slow_queries';

檢查日誌檔是否出現對應記錄。

四、日誌分析工具

1. mysqldumpslow（內建）

# 前 10 筆按查詢時間排序
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

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

# 含 LEFT JOIN 的前 10 筆
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log

參數：-s 排序方式 (c/t/l/r/ac/at/al/ar)，-t Top N，-g 正則過濾。

2. pt-query-digest (Percona Toolkit，推薦)

pt-query-digest /var/log/mysql/slow.log --limit 20 --filter '$event->{Query_time} > 1'

• 產生詳細報表：執行次數、總耗時、平均、最大、標準差、查詢樣本、EXPLAIN 計畫等。
• 支援從 tcpdump、binlog、general log 等多源分析。

五、常見優化方向

1. 缺少索引：EXPLAIN 觀察 type=ALL、rows 偏大，補加組合索引。
2. 隱式轉換：欄位型態與參數不符導致索引失效（如 VARCHAR 對比數字）。
3. 函數包裹欄位：WHERE DATE(created_at) = '2023-01-01' → 改為範圍查詢。
4. 深分頁：LIMIT 100000, 20 → 改用延遲關聯或游標分頁。
5. 大事務/長鎖定：拆分事務、縮短鎖定範圍、調整隔離級別。

六、生產環境最佳實踐

• 日誌存放於獨立磁碟，避免佔滿系統盤。
• 配合 logrotate 定期輪轉壓縮。
• 建立定時任務每日跑 pt-query-digest 產生報表發送給 DBA/開發。
• 將 long_query_time 設為 1~2 秒，避免日誌暴增；關鍵業務可在應用層自行埋點記錄。