MySQL 效能優化實戰：從慢查詢日誌到效能倍增的完整複盤

前言：痛點驅動優化

春節期間線上資料庫頻繁因壓力過大導致回應緩慢甚至癱瘓，團隊下定決心徹底解決效能瓶頸。由於長期秉持「快速開發、頻繁上線」原則，性能優化屢被忽視，技術債日積月累，系統愈發遲緩。本文將實戰過程完整複盤，從慢查詢日誌分析到具體優化策略，提供可落地的方法論。

一、慢查詢日誌：優化的「體檢報告」

1. 開啟與提取慢查詢日誌

慢查詢日誌預設位於 MySQL 資料目錄。確認 slow_query_log=ON 並設定 long_query_time 閾值（如 2 秒），即可記錄超時查詢。

2. 將日誌導入資料庫便於分析

建立分析表 slow_query，欄位涵蓋時間、用戶、主機、SQL 指紋、執行時間、鎖定時間、回傳行數、掃描行數、原始語句等。透過腳本將日誌解析入庫，便於後續 SQL 聚合分析。

二、關鍵指標與分析維度

在 slow_query 表上發揮想像力，重點捕捉：

• 總耗時最高 的語句類型（SUM(query_time)）
• 執行頻率最高 的語句（COUNT(*)）
• 平均耗時最長 的語句（AVG(query_time)）
• 掃描行數最多 的語句（rows_examined）
• 鎖定等待最久 的語句（lock_time）

三、實戰優化案例複盤

案例 1：整體效能對比——優化前後對比

-- 優化後 (2008-02-20 至 21)
AVG 5.72 秒 | 總計 2,197 筆 | 總耗時 12,574 秒

-- 優化前 (2008-02-17 至 18)
AVG 2.60 秒 | 總計 16,091 筆 | 總耗時 41,810 秒

雖平均耗時略增，但慢查詢筆數暴跌 86%，總資源消耗降低 70%，系統吞吐量大幅提升。

案例 2：UPDATE 鎖爭用問題定位

SELECT COUNT(*) n, SUM(query_time) s, AVG(query_time) avg,
       SUBSTRING_INDEX(statement, ' ', 2) u
FROM slow_query
WHERE statement LIKE 'update%' AND query_time > 14
GROUP BY u;

結果顯示 update user 佔絕大多數（151 筆、平均 16 秒），揭露程式碼中存在「忘記釋放事務鎖」導致長時間阻塞。

案例 3：Top 10 耗資源查詢聚焦

SELECT SUM(query_time) sum, COUNT(*) cnt, AVG(query_time) avg, statement
FROM slow_query
WHERE host LIKE '%69.12.23.%'
GROUP BY content
ORDER BY sum DESC LIMIT 10;

前 3 筆屬同類查詢，佔總量 50%+，每分鐘 10+ 筆慢查詢，列為優先解決目標。

四、優化策略總結

1. 索引優化： 針對高頻 WHERE/JOIN/ORDER BY 欄位建立複合索引，消除全表掃描。
2. SQL 改寫： 拆分大查詢、避免 SELECT *、優化子查詢為 JOIN、善用 LIMIT。
3. 事務管理： 縮短事務範圍，確保異常路徑也能正確 COMMIT/ROLLBACK，避免長鎖持有。
4. 架構層面： 讀寫分離、分庫分表、引入快取層，從根本降低單庫壓力。

五、持續優化機制

• 建立每日慢查詢報表自動推送。
• 上線前強制 EXPLAIN 分析，納入 Code Review 標準。
• 定期歸檔清理慢查詢表，避免分析庫膨脹。

結語

性能優化非一次性專案，而是持續迭代的工程文化。從「救火」到「防火」，關鍵在於建立可視化、可量化、可追蹤的慢查詢分析體系。願本文實戰經驗能為您的優化之路提供參考與啟發。