SQL 語句優化 34 條鐵律：從執行計劃到寫法重構的完整檢查清單

高性能 SQL 是系統吞吐量的基石。本文匯總 34 條經過生產驗證的優化鐵律，涵蓋 優化器行為利用、索引設計、WHERE 寫法重構、連接優化、聚合排序、臨時對象、事務控制、數據類型 八大維度，每條附反模式→最佳實踐對比與核心原理，可直接作為代碼審查清單。

一、 優化器與執行計劃層面

1. 表連接順序：基於規則優化器（舊版）從右到左解析 FROM，小表駐大表；成本優化器依賴統計信息，保持統計信息最新（UPDATE STATISTICS / ANALYZE TABLE）。
2. WHERE 條件順序：優化器自下而上解析，過濾性最強（選擇率最低）的條件寫在最後，先縮小結果集。
3. 避免 SELECT *：解析開銷、網絡傳輸、緩存污染、失去覆蓋索引機會。顯式列出列名。
4. 減少數據庫往返：批量綁定、數組提交、存儲過程封裝邏輯，降低網絡延遲與解析開銷。
5. 調大 ARRAYSIZE / Fetch Size：客戶端驅動默認 10-50 行，改 200-500 行，大幅減少網絡交互。

二、 索引設計鐵律

6. 覆蓋索引消除書簽查找：INCLUDE 選列表列，讓非聚集索引直接滿足查詢。
7. 複合索引列序 = 等值列 → 範圍列 → ORDER BY 列，最左前綴原則。
8. 單表索引 ≤ 6 個：每增一索引，寫入多 4-5 次 I/O 維護 B+ 樹。
9. 避免更新聚簇索引鍵：導致頁分裂、行移動、二級索引鍵值更新（主鍵值變更）。
10. 外鍵列必建索引：避免父表刪除/更新時鎖定子表全表掃描檢查引用完整性。

三、 WHERE 寫法重構（高頻失分點）

11. col IS NULL → 設默認值 0/-1 查 col=0（NULL 不入普通索引）。
12. col != val → 改寫為 col < val OR col > val 或範圍查詢（不等值不走索引）。
13. WHERE a=1 OR b=2 → UNION ALL 合併（OR 導致全掃，除非兩側各有索引且優化器支持 Index Merge）。
14. IN (列表) 連續值 → BETWEEN；列表過長（>100）考慮臨時表 JOIN。
15. LIKE '%abc' → 全文索引 / 反向存儲列 / LIKE 'abc%'（前導通配符殺死索引）。
16. 列上運算/函數殺死索引：col/2=100→col=200；SUBSTRING(col,1,3)='abc'→col LIKE 'abc%'；DATEDIFF(day,dt,'2024')=0→dt>='2024-01-01' AND dt<'2025-01-01'。
17. 參數化查詢 @var 導致計劃不穩：簡單語句用 OPTION (RECOMPILE)；複雜語句用動態 SQL 構建具體值計劃。
18. 隱式轉換殺死索引：字符列比數字 col=123 → 強制 col='123'；數字列比字符同理。

四、 連接與子查詢優化

19. IN (subquery) → EXISTS (subquery)：半連接，滿足即返回，不產生中間結果集。
20. NOT IN (subquery) → NOT EXISTS / LEFT JOIN ... WHERE IS NULL：NOT IN 含 NULL 邏輯陷阱且全掃。
21. DISTINCT → EXISTS / 分組去重：DISTINCT 觸發排序，大結果集極慢。
22. UNION → UNION ALL（業務允許重複時）：省去排序去重，節省 SORT_AREA_SIZE 內存。
23. OR 條件列有索引 → UNION ALL 分支查詢合併（各分支走各自索引）。

五、 聚合、排序、分頁

24. GROUP BY 隱式排序 → ORDER BY NULL 禁止排序（MySQL）。
25. ORDER BY 無索引 → 建複合索引 (where_eq_col, order_col) 實現索引有序掃描。
26. 深分頁 OFFSET 100000 LIMIT 20 → 鍵集分頁 WHERE pk > last_seen_pk ORDER BY pk LIMIT 20 或 ROW_NUMBER() 篩選主鍵回表。
27. COUNT(1)/COUNT(pk) → COUNT(*)：引擎對 COUNT(*) 有特殊優化（MyISAM 直接讀元數據，InnoDB 掃描最小二級索引）。
28. COUNT(col) ≠ COUNT(*)：前者統計非 NULL 行，後者統計總行數，語義不同勿混用。

六、 臨時對象與游標

29. 臨時表 → 表變量（小數據）或 CTE/導出表：減少 tempdb 系統表鎖定。
30. 大數據量臨時表首載 → SELECT INTO（最小日誌）+ 後建索引。
31. 游標循環 → 集合操作（JOIN/窗口函數/APPLY）：游標單行處理，>1 萬行必改寫。
32. 存儲過程首尾 SET NOCOUNT ON/OFF：抑制 DONE_IN_PROC 消息，減少網絡包。

七、 事務與併發

33. 大事務拆小 / 批次提交：長鎖定殺併發，回滾段膨脹，鎖升級風險。
34. 避免向客戶端返回大結果集：分頁、流式處理、異步導出，防 OOM 與網絡阻塞。

八、 數據類型與架構

35. 純數字業務鍵 → 數值型（INT/BIGINT）：比較一次 vs 字符逐字符。
36. 固定長 CHAR → 變長 VARCHAR：節省空間，縮小掃描範圍，利用緩存。
37. 將頻繁更新的列集中：減少行版本/日誌記錄開銷（針對某些引擎）。

使用建議

• 將本清單集成到 Code Review Checklist（GitLab MR 模板 / SonarQube 規則 / 內部 Lint 工具）。
• 每條優化必驗證：EXPLAIN 對比、邏輯讀對比、實際耗時對比（預熱後多次取中位數）。
• 優化無銀彈，但有鐵律：**先測量、再診斷、後手術、必複查**。

內化這 34 條，SQL 性能問題將在代碼評審階段被 90% 以上攔截。