用 mysqldumpslow 分析 Slow Query Log 找出效能瓶頸

用 mysqldumpslow 分析 Slow Query Log 找出效能瓶頸

慢查詢日誌記錄執行超過閾值的 SQL，是效能調優的金礦。本文詳解開啟慢查詢、mysqldumpslow 核心參數、實戰分析流程與優化轉化。

一、開啟慢查詢日誌

編輯 my.cnf [mysqld] 區塊：

long_query_time = 1                    -- 超過 1 秒記錄（微秒精度可用 0.1）
slow_query_log = 1                     -- 5.1+ 參數名
slow_query_log_file = /var/log/mysql/slow.log  -- 日誌路徑
log_queries_not_using_indexes = 1      -- 記錄未用索引的查詢（慎開，量大）

修改後重啟 MySQL 或動態生效：

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

運行一段時間（如高峰期 30 分鐘）收集樣本，分析完畢建議關閉以免影響效能與磁碟。

二、mysqldumpslow 核心參數

mysqldumpslow [options] slow.log

-s ORDER     排序欄位：c(次數) t(時間) l(鎖定時間) r(返回行數) 
             前綴 a = 反向排序 (at=耗時最長, ac=最頻繁, al=鎖最久, ar=返回最多)
-t NUM       只顯示前 N 筆
-g PATTERN   正則過濾（不分大小寫），如 -g "left join"

三、實戰分析指令範例

# 1. 找出訪問次數最多的 Top 20 SQL
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log

# 2. 找出返回記錄集最多的 Top 20 SQL
mysqldumpslow -s r -t 20 /var/log/mysql/slow.log

# 3. 找出耗時最長的 Top 10 SQL（含 left join）
mysqldumpslow -t 10 -s t -g "left join" /var/log/mysql/slow.log

# 4. 找出鎖定時間最久的 Top 10
mysqldumpslow -s al -t 10 /var/log/mysql/slow.log

# 5. 綜合報表：按平均耗時排序 Top 20
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log

四、輸出結果解讀

Count: 150  Time=12.34s (1851s)  Lock=0.01s (1.5s)  Rows=45.2 (6780)  user@host
  SELECT * FROM orders WHERE user_id = 'N' AND status = 'N' ORDER BY created_at DESC LIMIT 'N'

• Count：出現次數（同模板 SQL 合併計數）
• Time：平均耗時 / 總耗時
• Lock：平均鎖定時間 / 總鎖定時間
• Rows：平均返回行數 / 總返回行數
• user@host：來源帳號與主機
• SQL 模板：具體值抽象為 'N'（數字）、'S'（字串）

五、從分析到優化的轉化流程

1. 鎖定目標：優先處理 Count × Time 最大（總耗時佔比高）或 Lock 高（阻塞嚴重）的 SQL。
2. EXPLAIN 驗證：對目標 SQL 執行 EXPLAIN，觀察 type、key、rows、Extra。
3. 加索引：type=ALL 或 key=NULL → 根據 WHERE/JOIN/ORDER BY 欄位建立索引。
4. 改寫 SQL：避免 SELECT *、OR 條件、函數包裹欄位、隱含轉型、大偏移 LIMIT。
5. 調整架構：大表分區/分表、讀寫分離、引入快取層（Redis）、異步處理。
6. 驗證效果：優化後觀察慢查詢日誌該 SQL 是否消失或耗時大幅下降。

六、進階技巧

• 抽象化參數：mysqldumpslow 自動將具體值抽象為 N/S，同模板 SQL 自動聚合，極適合找出高頻模式。
• 配合 pt-query-digest：Percona Toolkit 的 pt-query-digest 功能更強（支援多日誌合併、詳細報表、JSON 輸出、歷史趨勢）。
• 定時自動化：crontab 每日跑一次分析報表郵件發送 DBA。
• 過濾雜訊：-g 排除備份、維護、監控帳號的查詢。