MySQL SQL 優化實戰：從執行計劃到十大優化鐵律

MySQL SQL 優化實戰：從執行計劃到十大優化鐵律

SQL 優化的本質只有一個：**改變執行計劃，讓優化器走「捷徑」**。核心目標雙支柱：**減少 IO 次數**（90%+ 瓶頸在這）與 **降低 CPU 計算**（排序、分組、去重）。本文拆解常見誤區、確立優化原則、給出可落地的十大鐵律。

優化目標雙支柱

1. 減少 IO：優化索引、避免全表掃描、利用覆蓋索引、減少回表、壓縮數據
2. 降低 CPU：消除 filesort、避免臨時表、減少複雜表達式計算、優化 JOIN 算法

唯一手段：通過索引設計、SQL 改寫、參數調優，引導優化器選擇最優執行計劃。

五大常見誤區（避坑指南）

SQL 優化十大鐵律（按優先級排序）

1. 盡量少 JOIN

MySQL 優化器統計信息有限，複雜多表 JOIN 易誤判。單表查詢性能可媲美 Oracle，複雜 JOIN 則明顯弱勢。拆分為多條簡單 SQL，應用層組裝，利用多核並行、緩存命中率更高。

2. 盡量少排序

• 利用索引順序滿足 ORDER BY/GROUP BY/DISTINCT
• 減少參與排序行數（先過濾再排序）
• 非必要不排序（應用層處理展示順序）

3. 避免 SELECT *

• 阻礙覆蓋索引，增加回表 IO
• 排序時增加 sort_buffer 壓力（MySQL 雙路/單路排序算法受選列影響大）
• 表結構變更時影響擴大

4. JOIN 代替子查詢

MySQL 子查詢執行計劃長期存在缺陷（衍生表物化、無法下推條件），雖然 8.0+ 有改善但仍不穩定。手寫 JOIN 可控性更強。

5. 少用 OR，改用 UNION ALL

WHERE a=1 OR b=2 導致優化器放棄索引掃描全表。拆分為 SELECT ... WHERE a=1 UNION ALL SELECT ... WHERE b=2，各自走索引，再合併結果。

6. UNION ALL 代替 UNION

UNION = UNION ALL + DISTINCT（排序去重）。確定無重複或不介意重複時，**必用 UNION ALL** 省去 CPU 密集型排序。

7. 盡早過濾

• 索引設計：高選擇性列前置
• SQL 編寫：大表 JOIN 前先在駕駛表過濾分頁，再關聯小表
• 減少無效行參與後續 JOIN/排序/聚合

8. 避免隱式類型轉換

-- 錯誤：列上用函數/轉換 → 索引失效
WHERE DATE(create_time) = '2023-10-01'
WHERE CAST(user_id AS CHAR) = '12345'

-- 正確：參數側轉換，列保持原樣
WHERE create_time >= '2023-10-01' AND create_time < '2023-10-02'
WHERE user_id = 12345

隱式轉換同樣導致索引失效（如 VARCHAR 列比較數字參數）。

9. 優先優化高併發 SQL

高併發 SQL 一旦退化，系統秒級崩潰；低頻大查詢最多讓系統慢一會兒，有緩衝時間。保護核心鏈路優先。

10. 全局視角優化，勿顧此失彼

調整索引需評估所有相關 SQL。新索引加速查詢 A 可能拖慢寫入 B、導致查詢 C 選錯索引。建立 EXPLAIN 基線庫，變更前後對比。

實戰檢查清單（每條 SQL 上線前）

1. EXPLAIN 確認：type ≥ ref、key 非 NULL、rows 合理、Extra 無 Using temporary/Using filesort
2. 驗證覆蓋索引：Extra 含 Using index
3. 檢查 key_len 推斷聯合索引列使用情況
4. 慢查詢日誌分析：pt-query-digest 找出 Top 10 高負載 SQL
5. 壓測驗證：模擬生產並發，觀察 QPS、RT、錯誤率

總結

SQL 優化無銀彈，有的是：**讀懂 EXPLAIN → 定位瓶頸（IO/CPU） → 針對性調整索引/改寫 SQL → 全局回歸驗證**。將「十大鐵律」內化為編碼直覺，配合系統化 EXPLAIN 審查機制，才能在高併發、大數據量下穩住 MySQL 性能底線。