IN 與 UNION ALL 效能對決：SQL Server 2005+ 優化器行為深度解析

前言：關於 IN 是否符合 SARG 的迷思

許多開發者聽聞「IN 不符合 SARG（Search ARGument）標準，會導致全表掃描」，因此傾向改寫為 UNION ALL。但這觀點僅適用於早期資料庫版本。SQL Server 2005 之後，優化器對 IN、OR 等邏輯有顯著增強，本文透過實測執行計畫，揭示兩者在不同資料量下的真實表現。

測試環境與基準

• 資料表：Member（百萬筆），proxyID 上有非叢集索引
• 需求：查詢指定代理商的會員資料
• 對比兩種寫法：
  1. WHERE proxyID IN ('ID1','ID2',...)
  2. WHERE proxyID='ID1' UNION ALL SELECT ... WHERE proxyID='ID2' ...

情境一：代理商編號較多（30 個）

執行計畫差異

• IN：單一 Index Seek + Filter，優化器將 30 個值轉為內部篩選邏輯，一次掃描索引即可。
• UNION ALL：30 個獨立的 Index Seek 透過 Concatenation 串聯，每個分支各自求值再合併。

結論

代理商編號較多時，UNION ALL 效率顯著優於 IN。IN 的 Filter 操作需在記憶體中比對 30 個值，隨值增多 CPU 開銷線性增長；UNION ALL 則利用索引精準定位各分支，並行潛力大。

情境二：代理商編號較少（2、6、15 個）

優化器的動態選擇

可見優化器會根據值的數量自動切換策略：值少時轉為 Nested Loops（類似 UNION ALL），值多時改用單一 Seek + Filter。此時兩者效能趨近。

核心結論

1. 兩種寫法皆會使用索引，不存在「IN 不走索引」的問題。
2. 執行計畫會隨資料分佈、值數量、統計資訊動態調整，無絕對優劣。
3. 實務建議：
   • 值固定且少（<10）：直接用 IN，可讀性最佳。
   • 值極多（>50）或動態產生：考慮 UNION ALL 或臨時表/表值參數（TVP）批次傳入。
   • 同樣邏輯適用於 OR 條件，高版本優化器同樣能有效處理。

補充：避免「硬編碼最佳化」迷思

資料庫引擎持續演進，早期經驗法則（如「IN 不走索引」、「OR 不走索引」）在新版本多已失效。正確做法是：

1. 以 SET STATISTICS IO, TIME ON 實測。
2. 觀察 Actual Execution Plan 而非估計計畫。
3. 關注 Logical Reads、CPU Time、Elapsed Time 三大指標。
4. 根據實測結果決定寫法，而非盲目遵循舊聞。