一次 MySQL 性能優化實戰：從每小時 1000 慢查詢降至 100 的完整複盤

線上資料庫因頻繁上線、缺乏效能把關，導致響應極慢甚至宕機。本文記錄一次真實優化案例：透過慢查詢日誌分析、建立分析表、聚焦高頻慢 SQL、釋放事務鎖、優化索引，將慢查詢從每小時 1000 筆降至 100 筆，平均查詢時間從 2.6s 降至 0.5s 以下的完整過程與關鍵 SQL 技巧。

背景與現狀

• 系統長期快速迭代，忽視 SQL 品質
• 慢查詢閾值：long_query_time = 2 秒
• 優化前 29 小時：28,914 筆慢查詢，總耗時 75,690 秒，平均 1,000 筆/小時
• 優化後：約 100 筆/小時，成效顯著

步驟 1：啟用並收集慢查詢日誌

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

步驟 2：將慢查詢日誌導入分析表

CREATE TABLE slow_query (
  `Date` varchar(32) NOT NULL,
  `user` varchar(64) NOT NULL,
  `host` varchar(64) NOT NULL,
  `content` text NOT NULL COMMENT '正規化後的 SQL 模板，便於 GROUP BY',
  `query_time` int(11) NOT NULL COMMENT '查詢耗時(秒)，核心指標',
  `lock_time` int(11) DEFAULT 0 COMMENT '鎖等待時間',
  `rows_sent` int(11) DEFAULT 0 COMMENT '回傳行數',
  `rows_examined` int(11) DEFAULT 0 COMMENT '掃描行數，超過萬筆需重點關注',
  `statement` text COMMENT '原始 SQL'
);

編寫腳本解析 mysqldumpslow 或直接正則解析慢查詢日誌，匯入上表。

步驟 3：多維度聚合分析，鎖定痛點

3.1 整體趨勢對比

-- 優化前
SELECT SUM(query_time)/COUNT(*) AS avg_q, COUNT(*), SUM(query_time), MIN(Date), MAX(Date)
FROM slow_query
WHERE Date > '2008-02-17 22:50:52' AND Date < '2008-02-18 17:34:35';
-- 結果：平均 2.5983s，16,091 筆，總 41,810s

-- 優化後
SELECT SUM(query_time)/COUNT(*) AS avg_q, COUNT(*), SUM(query_time), MIN(Date), MAX(Date)
FROM slow_query
WHERE Date > '2008-02-20 22:50:52' AND Date < '2008-02-21 17:34:35';
-- 結果：平均 5.7233s，2,197 筆，總 12,574s

筆數大幅下降（16k → 2k），總耗時降 70%，但平均耗時上升因剩餘為難啃的硬骨頭。

3.2 找出高耗時 UPDATE 鎖爭用

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

+-----+------+---------+--------------------------+
| n   | s    | avg     | sql_type                 |
+-----+------+---------+--------------------------+
| 7   | 112  | 16.00   | update conversation      |
| 151 | 2413 | 15.98   | update user              |
| 4   | 65   | 16.25   | update user_modification |
+-----+------+---------+--------------------------+

結論：大量 UPDATE 耗時 16 秒，明顯是事務鎖未釋放（長事務、死鎖或忘記 COMMIT）。修正程式碼確保事務最小化、及時提交。

3.3 Top 10 耗資源查詢（按總耗時）

SELECT SUM(query_time) AS sum_t, COUNT(*) AS cnt, SUM(query_time)/COUNT(*) AS avg_t, statement
FROM slow_query
WHERE host LIKE '%69.12.23.%'
GROUP BY content
ORDER BY sum_t DESC LIMIT 10;

前 3 筆屬同類查詢，佔總查詢 50%+，每分鐘 10+ 筆，優先解決。

步驟 4：針對性優化動作

1. 補充缺失索引：EXPLAIN 發現全表掃描，加上複合索引覆蓋 WHERE + ORDER BY
2. 拆分大事務：將長事務拆為多個短事務，減少鎖持有時間
3. 修正 N+1 查詢：應用層改用 JOIN 或批次查詢
4. 調整分頁方式：LIMIT 100000, 20 → 游標分頁 WHERE id > last_id LIMIT 20
5. 清理無用索引：降低寫入開銷

步驟 5：驗證與持續監控

• 部署後觀察 24 小時慢查詢趨勢圖
• 建立 Grafana + Prometheus 監控 Slow_queries、Queries_per_sec、Innodb_row_lock_waits
• 設定告警：慢查詢 > 50/小時 觸發通知

核心工具鏈

優化沒有終點，只有持續迭代。建立「慢查詢日誌 → 歸因分析 → 針對性修復 → 效果驗證 → 固化規範」的閉環機制，才能讓資料庫長期保持健康狀態。