SQL 海量資料查詢優化 24 條鐵律與實戰案例：IN 條件資料傾斜破解

面對千萬級資料表，查詢效能優化不再是參數調整，而是邏輯重構。本文彙總 24 條優化鐵律，並以「IN 條件 + 資料傾斜」實戰案例，示範如何從資料分布切入，選擇叢集索引而非非叢集索引，將邏輯讀從百萬級降至千級。

24 條查詢優化鐵律

1. 避免 WHERE 判斷 NULL → 導致全表掃描。改用預設值 0：WHERE num = 0。
2. 避免 != / <> → 無法用索引。改寫為 UNION ALL 或篩選正向條件。
3. 避免 OR 連接條件 → 全表掃描。改用 UNION ALL 分開查詢合併。
4. 慎用 IN / NOT IN → 連續值改用 BETWEEN；大量離散值考慮 EXISTS 或臨時表 JOIN。
5. 避免前導萬用字元：LIKE '%abc%' 無法用索引。LIKE 'abc%' 可用。
6. 參數化查詢強制索引：WHERE num = @num 編譯時未知值 → 全表掃描。加 WITH (INDEX(idx_name)) 強制。
7. 避免 WHERE 欄位做運算/函數：F1/2=100 → F1=200；SUBSTRING(col,1,4)='5378' → col LIKE '5378%'；DATEDIFF(yy,dob,GETDATE())>21 → dob < DATEADD(yy,-21,GETDATE())。運算移至等號右側。
8. 優先用 EXISTS 替代 IN/COUNT(*)：EXISTS (SELECT 1 FROM B WHERE B.num=A.num) 避免大量鎖定掃描。
9. 表變數替代臨時表 (小資料量)；大資料量臨時表注意索引限制 (僅 PK)。
10. 減少建立/刪除臨時表，降低系統表資源爭用。
11. 大量插入臨時表用 SELECT INTO (減少 Log)；小量用 CREATE TABLE + INSERT (緩和系統表壓力)。
12. 存儲過程結束顯式清理臨時表：TRUNCATE TABLE #t; DROP TABLE #t; 避免長時間鎖定系統表。
13. 所有 SP/Trigger 開頭 SET NOCOUNT ON，結尾 OFF，減少 DONE_IN_PROC 網路往返。
14. 避免大交易，提升併發。
15. 避免向客戶端傳大量資料，評估需求合理性。
16. 避免不相容資料型別比較 (float vs int, char vs varchar) → 優化器無法優化。編碼時顯式轉型。
17. 充分利用多重 JOIN 條件：補全所有關聯鍵，大幅降低掃描範圍。
18. 善用 View 預排序/預篩選：將常用查詢的子集建立 View 並排序，減少 I/O 與排序成本。
19. 去重用 DISTINCT 而非 GROUP BY (無彙總時)。
20. 合併結果用 UNION ALL 而非 UNION (無需去重時)。
21. 避免 SELECT INTO (鎖定系統表)。
22. 善用執行計畫 (Ctrl+L) 對比：看索引使用、掃描次數、查詢成本百分比。

實戰案例：千萬資料表 IN 條件優化

場景

• 表 aa_001 約 1000 萬列，ip 欄位查詢：SELECT * FROM aa_001 WHERE ip IN (1,2,3) ORDER BY name DESC。
• IN 清單長度不固定。

資料分布分析 (關鍵步驟)

SELECT ip, COUNT(*) FROM aa_001 GROUP BY ip ORDER BY 2;

三類分布：

1. 高基數 (近似唯一)：建立 ip 非叢集索引即可，極快。
2. 不均勻傾斜：非叢集索引效果不穩，部分值快、部分慢。
3. 低基數 (大量重複，僅萬級 distinct ip)：非叢集索引失效，優化器選擇 Table Scan。

解決方案：針對類型 2/3 建立叢集索引

測試環境：[dbo].[[zping.com]]] 200 萬列，userid 欄位 379 個 distinct 值，頻率 1 ~ 15 萬，嚴重傾斜 (類型 2/3)。

-- 建立 userid 叢集索引
CREATE CLUSTERED INDEX IX_userid ON [dbo].[[zping.com]] (userid);

-- 再次查詢
SELECT * FROM [dbo].[[zping.com]]
WHERE userid IN ('id1','id2','id3')
ORDER BY Ruleid DESC;

結果：

• 執行計畫顯示 Clustered Index Seek。
• 回傳 15.6 萬列，邏輯讀僅 5,877 次 (Table Scan 需百萬級)。
• 查詢成本 51% 在排序 (可考慮 (userid, Ruleid) 覆蓋索引，但 DML 維護成本高，不建議)。

結論

• 資料分布類型 1 (高基數) → 非叢集索引。
• 資料分布類型 2/3 (傾斜/低基數) → 叢集索引 是破解 IN + 排序 大量回傳的關鍵。