MySQL 慢查詢日志配置、分析與優化實戰指南

慢查詢成因

• 全表掃描：無索引或索引失效，磁盤 I/O 線性增長
• 惡性 Join：大表關聯無索引，產生臨時表/文件排序
• 鎖等待：長事務阻塞、熱點行競爭
• 資源爭用：CPU/內存/IO 飽和導致排隊

配置啟用

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2                 # 超過 2 秒記錄 (建議 0.5-1 秒)
log_queries_not_using_indexes = ON  # 無索引查詢也記錄 (4.1+)
# log_output = FILE,TABLE           # 同時寫入 mysql.slow_log 表

權限：日志目錄需 mysql 用戶寫入權限。

日志分析工具鏈

1. mysqldumpslow (內建)

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

# 按平均耗時
mysqldumpslow -s at -t 20 /var/log/mysql/mysql-slow.log

# 正則過濾特定表
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log | grep orders

特性：自動抽象化參數值（N/'S'），同模式 SQL 合併統計。

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

pt-query-digest /var/log/mysql/mysql-slow.log --limit 20 --output json > digest.json
pt-query-digest --review h=localhost,u=root,p=pass,D=test --create-review-table

輸出維度：執行次數、總/平均/最大耗時、掃描行數、返回行數、指紋、EXPLAIN 計劃。

3. 實時監控

# 尾部跟蹤
tail -f /var/log/mysql/mysql-slow.log

# 系統層面
iostat -x 1, vmstat 1, pidstat -d 1

優化閉環流程

1. 收集：開啟慢查詢日志 24-48 小時覆蓋業務高峰
2. 聚焦：pt-query-digest 定位 Top 5 佔比最高的指紋
3. 診斷：
   — EXPLAIN SELECT ... 檢查 type、key、rows、Extra
   — SHOW CREATE TABLE 確認索引定義
   — SHOW TABLE STATUS 觀察行數、數據長度、索引長度
4. 處方：
   — 補充索引（聯合索引遵循最左前綴）
   — 重寫 SQL（拆分大事務、消除子查詢、優化分頁）
   — 調整參數（sort_buffer_size、join_buffer_size、tmp_table_size）
   — 架構調整（分表、分庫、引入緩存、異構存儲）
5. 驗證：預發環境壓測對比、生產灰度發布觀察
6. 固化：索引變更納入變更管理、參數寫入 my.cnf

常見慢查詢模式與處方

主從架構下的慢查詢策略

• **僅在從庫開啟** 慢查詢日志，主庫零性能影響
• 從庫 log_slow_slave_statements=ON (5.6+) 記錄 SQL 線程重放慢查詢
• 分析時注意：從庫可能因複製延遲導致統計信息舊，EXPLAIN 計劃與主庫不一致

慢查詢日志輪轉與存儲

# logrotate 配置
/var/log/mysql/mysql-slow.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    sharedscripts
    postrotate
        mysqladmin -uroot -p flush-logs
    endscript
}