高效能 SQL 查詢語句優化實戰指南

核心優化原則

在確保功能正確的前提下，SQL 查詢優化的核心在於：減少資料庫存取次數、縮小掃描範圍、最小化結果集、降低網路負載。以下原則能幫助你撰寫高效能的查詢語句：

1. 精準選取欄位，拒絕 SELECT *

避免使用 SELECT * FROM T1 這類通配符查詢。只選取業務真正需要的欄位，例如 SELECT COL1, COL2 FROM T1，能顯著減少 I/O 與網路傳輸。

2. 善用 LIMIT 限制結果集

當使用者不需要海量資料時，加上 LIMIT 子句（如 SELECT TOP 300 COL1, COL2, COL3 FROM T1）可避免全表掃描與大量資料傳輸。

3. 讓查詢條件符合 SARG 標準

SARG（Search Argument）是優化器能利用索引的條件形式：
欄位 操作符 常數/變數 或 常數/變數 操作符 欄位
例如：Name = '張三'、價格 > 5000、Name = '張三' AND 價格 > 5000
不符合 SARG 的條件（如函數運算、隱含轉換）會導致全表掃描。

22 條避坑指南

條件判斷類

1. 避免 WHERE 子句中判斷 NULL：WHERE num IS NULL 會放棄索引。預設值設為 0，改用 WHERE num = 0。
2. 避免 != 或 <> 操作符：優化器無法透過索引估算命中行數，必須全表掃描。
3. 避免 OR 連接條件：WHERE num = 10 OR num = 20 應改用 UNION ALL 分開查詢再合併。
4. 慎用 IN / NOT IN：連續數值優先用 BETWEEN（WHERE num BETWEEN 1 AND 3）。
5. 避免前置萬用字元搜尋：LIKE '%L%' 或 SUBSTRING(NAME,2,1)='L' 無法用索引；LIKE 'L%' 才能命中索引。

運算與函數類

6. 強制指定索引：參數化查詢（WHERE num = @num）編譯時值未知，易導致全表掃描。可用 WITH (INDEX(索引名)) 強制走索引。
7. 避免欄位做表達式運算：WHERE F1/2 = 100 → WHERE F1 = 100*2；WHERE SUBSTRING(CARD_NO,1,4)='5378' → WHERE CARD_NO LIKE '5378%'。
8. 避免欄位做函數運算：WHERE DATEDIFF(yy, datofbirth, GETDATE()) > 21 → WHERE dateofbirth < DATEADD(yy, -21, GETDATE())。原則：把運算移到等號右邊。
9. 不要在「=」左側做任何運算，否則系統可能無法正確使用索引。

索引與連接類

10. 複合索引必須用到第一欄，且條件欄位順序應與索引順序一致。
11. 優先用 EXISTS 替代 IN：
    SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num = a.num)
    效率高於 IN 子查詢，避免大量鎖定與掃描。
12. 驗證資料存在性用 EXISTS，別用 COUNT(*)：IF EXISTS (SELECT * FROM table WHERE col = 'xxx') 遠快於 COUNT(*)。
13. NOT EXISTS > LEFT JOIN ... IS NULL > NOT IN：三種寫法結果相同，效率遞減。

暫存表與交易類

14. 優先用表變數替代暫存表，但大量資料時注意索引限制（僅主鍵）。
15. 避免頻繁建立/刪除暫存表，減少系統表資源消耗。
16. 大量資料一次性插入用 SELECT INTO（避免大量 Log）；小量資料先 CREATE TABLE 再 INSERT。
17. 存儲程序結束時顯式刪除暫存表：先 TRUNCATE TABLE 再 DROP TABLE，避免長時間鎖定系統表。
18. 所有存儲程序與觸發器開頭加 SET NOCOUNT ON，結尾加 OFF，減少 DONE_IN_PROC 訊息傳輸。
19. 避免大交易，提高併發能力。
20. 避免回傳大量資料給客戶端，若必要請評估需求合理性。

資料型別與進階技巧

20. 避免不相容資料型別：如 salary (money) > 60000 (int) 會阻礙優化。編碼階段就做好型別轉換。
21. 完整寫出連接條件：多條件連接能大幅提速。
    WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO = B.ACCOUNT_NO 比單一條件快得多。
22. 善用視圖預先排序與過濾：將常用查詢結果建成視圖並排序，減少重複排序與磁碟 I/O。
23. 能用 DISTINCT 就別用 GROUP BY；能用 UNION ALL 就別用 UNION（省去 DISTINCT 開銷）。
24. 慎用 SELECT INTO：會導致表鎖定，阻塞其他使用者。

驗證優化成效的最佳方法

理論再好，不如實測。在查詢分析器中按 Ctrl+L 查看執行計劃，重點比較：索引使用情況、表掃描次數、查詢成本百分比。資料量小時差異不明顯，執行計劃分析是關鍵。