MySQL 慢查詢導致資料庫僵死故障完整復盤與優化實戰

故障現場

• 現象： 研發回報資料庫「僵死」，連線極難建立，簡單查詢需數十秒回應。
• 監控異常： 慢查詢數暴增、CPU 使用率飆高、連線數突破 600+（平常約 100）。
• 配置： long_query_time = 3 秒，但實際執行時間達數十秒甚至 30,000 秒。

排查與處理流程

第一階段：現場鎖定

# 1. 觀察慢查詢日誌分布
# 2. SHOW PROCESSLIST 發現大量積壓連線
# 3. SHOW FULL PROCESSLIST 定位頂部數條「元兇」SQL

關鍵發現：數條使用子查詢的 SQL，Time 欄位高達 30,000 秒（約 10 小時），EXPLAIN 顯示 Using temporary; Using filesort，典型的惡劣執行計畫。

第二階段：止血處理

# 1. 確認與開發：上午 9 點部署新代碼引入子查詢，後雖註解但連線未釋放
# 2. 批次 KILL 惡意連線
SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.processlist 
WHERE info LIKE '%子查詢特徵%';

執行後連線數從 600+ 瞬間降至 100+，CPU/記憶體使用率隨即回落，服務恢復正常。

第三階段：根因分析

慢查詢優化核心原則

1. 避免子查詢： MySQL 對 IN (SELECT ...) 優化較弱，優先改寫為 JOIN 或 EXISTS。
2. 小表驅動大表： 確保驅動表（外層循環）為小結果集，被驅動表有索引支援。
3. 索引覆蓋： 讓查詢在索引中完成，避免回表與臨時表。
4. 分頁優化： 深分頁改用延遲關聯或游標分頁。

故障排查標準化 SOP

1. 雙維監控對比： 同時檢視 OS 監控（CPU/IO/MEM）與 MySQL 監控（QPS/TPS/慢查詢/連線數），並與「昨日同一時段」對比，快速定位異常基線。
2. 分層定位： 慢查詢日誌 → SHOW PROCESSLIST → EXPLAIN → 索引/結構/代碼三層優化。
3. 主次分明： 先 KILL 佔用資源最重的頭部會話（大塊問題），服務恢復後再逐一優化殘留慢查詢（細小問題）。
4. 複盤沉澱： 產出事故報告，包含時間線、根因、影響範圍、修正措施、預防機制（如：DBA 定期輸出 Top N SQL 給開發優化）。

快速批次 KILL 腳本範例

# 1. 篩選目標會話 ID
mysql -uroot -p'XXX' -e "SHOW FULL PROCESSLIST;" | grep '特徵關鍵字' | awk '{print $1}' > kill_list.txt

# 2. 生成 KILL 語句並執行
mysql -uroot -p'XXX' -e "$(awk '{print \"KILL \" $1 \";\"}' kill_list.txt)"

經驗之談： 高效溝通是故障處理的加速器。DBA 應建立「每日/每週 Top N SQL 檢視機制」，主動發現潛在慢查詢，將優化前置到開發階段，而非等待故障爆發才被動救火。