MySQL 慢查詢日誌配置、分析與優化閉環實戰

MySQL 慢查詢日誌配置、分析與優化閉環實戰

慢查詢日誌是 MySQL 性能調優的「發現器」，記錄執行超過 long_query_time 的 SQL，配合 EXPLAIN、PROFILING、SHOW STATUS 形成「發現 → 定位 → 分析 → 改寫 → 驗證」閉環。本文覆蓋配置、日誌格式、mysqldumpslow 分析工具實戰。

核心配置參數

配置文件修改 (my.cnf / my.ini)

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

修改後 systemctl restart mysqld 或 net restart mysql。

動態開啟 (無需重啟)

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
-- 注意：log_queries_not_using_indexes 無法動態修改，需改配置重啟

驗證配置生效

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

慢查詢日誌格式解讀

# Time: 2024-01-15T10:05:48.123456Z
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 14.031250  Lock_time: 0.218750  Rows_sent: 0  Rows_examined: 90785
SET timestamp=1705313148;
DELETE FROM orderinfo;

• Query_time：總耗時 (壁鐘時間)
• Lock_time：鎖等待時間
• Rows_examined：掃描行數 (關鍵，遠大於 Rows_sent 通常意味著全掃或索引失效)

mysqldumpslow 分析工具 (Perl 腳本)

Windows 需安裝 ActivePerl/Strawberry Perl；Linux 通常自帶。

# 按次數倒序 Top 10
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

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

# 按平均耗時倒序
mysqldumpslow -s at -t 10 /var/lib/mysql/slow.log

# 只看含 LEFT JOIN 的語句
mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/slow.log

# 輸出到文件
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log > /tmp/slow_top10.txt

參數速查

• -s 排序：c (count)、t (time)、l (lock time)、r (rows sent)；前綴 a 表倒序 (ac, at...)
• -t N：Top N
• -g "pattern"：正則過濾 (大小寫不敏感)
• -r：不抽象化 SQL (保留具體值)

優化閉環標準流程

1. 采集：開啟慢日誌 1-7 天，覆蓋業務高峰期。
2. 聚焦：mysqldumpslow -s at -t 20 找出平均耗時最高的 20 條。
3. 診斷：對每條 SQL → EXPLAIN → SHOW CREATE TABLE → SHOW INDEX → 判斷 type、key、rows、Extra。
4. 改寫：加索引、調整 JOIN 順序、拆分大 SQL、避免函數列、覆蓋索引、分頁優化。
5. 驗證：測試環境跑負載對比 Query_time、Rows_examined、Handler_read%。
6. 上線：灰度發布，監控慢日誌新增情況。

MySQL 5.7+ 支援慢日誌寫入表 (log_output=TABLE)，可直接 SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 100; 方便程序化分析。生產建議 long_query_time=0.5 並定期歸檔清理。