MySQL 慢查詢分析實戰：開啟、配置、mysqldumpslow 與優化案例

慢查詢日誌是發現效能瓶頸最直接的工具。本文實戰演示從開啟、參數調校、日誌分析到優化的完整流程。

1. 關鍵參數與動態開啟

-- 查看目前設定
SHOW VARIABLES LIKE 'long_query_time';   -- 預設 10 秒
SHOW VARIABLES LIKE 'slow_query_log%';   -- 檢查是否開啟、檔案路徑

-- 動態調整（無需重啟，但重啟後失效，需同步寫入 my.cnf）
SET GLOBAL long_query_time = 1;          -- 1 秒以上記錄
SET GLOBAL slow_query_log = 'ON';        -- 立即生效
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

2. my.cnf 永久配置

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1  -- 記錄未用索引查詢（配合分析）

修改後 systemctl restart mysqld。

3. mysqldumpslow 分析利器

# 按執行次數 Top 10
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按查詢時間 Top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

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

# 含 LEFT JOIN 的慢查詢，按時間排序 Top 10
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log

參數說明：-s 排序（c=count, t=time, l=lock time, r=rows），-t Top N，-g 正則過濾（不區分大小寫）。

4. 進階分析：pt-query-digest (Percona Toolkit)

pt-query-digest /var/log/mysql/slow.log --limit 20 --output json > report.json

產出詳細報表：查詢指紋、執行次數、總耗時、平均耗時、掃描行數、索引使用率、EXPLAIN 計畫等。

5. 實戰優化案例

案例：某電商訂單查詢頻現慢查詢

# 慢查詢樣本
SELECT * FROM orders 
WHERE user_id = 12345 AND status = 'paid' 
ORDER BY created_at DESC LIMIT 20;

EXPLAIN 發現：type=ALL（全表掃描），rows=2,000,000。

優化：

ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

結果：type=ref，rows=15，查詢時間從 2.3s 降至 0.001s，彻底消失於慢查詢日誌。

6. 持續營運建議

• 慢查詢日誌輪轉（logrotate），避免佔滿磁碟。
• 設定告警：每日新增慢查詢 > 閾值發送通知。
• 定期 pt-query-digest 全量分析，追蹤趨勢。
• 結合 Performance Schema / sys schema 深度診斷。