SQL 查詢效能 PK：OR 條件 vs UNION ALL — 百萬/千萬筆實測數據揭曉真相

測試環境與設計

• 硬體：Q8200 (4 核) / 4GB RAM
• OS：Windows Server 2003 R2
• DB：SQL Server 2005
• 資料表：三張同結構表 (8 個 VARCHAR(20) 欄位)，分別塞入 10 萬、100 萬、1000 萬 筆全相同資料 ('123')。
• 查詢：WHERE col1='123' OR col2='123' OR ... vs UNION ALL 多條 SELECT 串接。
• 測試 3 欄位 OR / UNION ALL、5 欄位 OR / UNION ALL。

實測耗時結果 (單位：秒)

核心發現：OR 完勝 UNION ALL (在無索引/全表掃描場景)

為什麼 OR 較快？

• 單次掃描：OR 讓優化器產生 **單一 Clustered Index Scan/Table Scan**，一遍讀完所有頁面，在記憶體中評估所有 OR 條件。
• UNION ALL = 多次掃描：每個 SELECT 獨立掃描整張表。3 條 UNION ALL = 掃 3 次表；5 條 = 掃 5 次。I/O 成倍增長。
• 測試資料**無索引**，全表掃描是唯一路徑，掃描次數直接決定效能。

什麼時候 UNION ALL 較好？

• 各條件各有高選擇性索引：WHERE col1=@v 用 IX_col1，WHERE col2=@v 用 IX_col2。OR 往往導致**索引合併失效** 或轉全掃，而 UNION ALL 能精準走各自索引求並集。
• 需去重：UNION (非 ALL) 自動去重，但需排序/雜湊成本高。
• 分頁/Top N：UNION ALL 配合 ORDER BY ... OFFSET ... FETCH 可避免全量排序。

進階優化策略

1. 計算欄位 + 索引 (適用固定 OR 模式)

ALTER TABLE BigTable ADD SearchKey AS (ISNULL(col1,'') + '|' + ISNULL(col2,'') + '|' + ISNULL(col3,''));
CREATE INDEX IX_SearchKey ON BigTable(SearchKey);
-- 查詢: WHERE SearchKey LIKE '%|123|%' -- 仍需掃描，但可配合全文檢索

2. 全文檢索 / Columnstore 索引 (SQL 2012+)

• 全文檢索：CONTAINS((col1,col2,col3), '123') 反向索引極快。
• Columnstore：分析型查詢 (大量掃描+彙總) 壓縮率高、批次模式執行，對 OR/UNION 皆大幅加速。

3. 動態 SQL 依參數選擇策略

IF EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_col1' AND object_id=OBJECT_ID('BigTable'))
    -- 有索引走 UNION ALL 動態拼接
ELSE
    -- 無索引走單一 OR 查詢

擴展測試：加上索引後的對比 (補充驗證)

若在 col1, col2, col3 各建非叢集索引：

• OR：優化器常選 **Index Union** (多索引掃描 + Merge Join) 或直接 **Clustered Scan** (若估算回表成本高)。
• UNION ALL：各 SELECT 精準 **Index Seek + Key Lookup**，總成本 = Σ(索引範圍掃描 + 回表)。
  當 **選擇性高 (回傳列 < 1-2%)** 時，UNION ALL 勝出；**選擇性低** 時，OR 單掃勝出。

結論與最佳實踐

1. 無索引/全表掃描場景：堅決用 OR (或 IN)，單次 I/O 最省。
2. 高選擇性索引存在：用 UNION ALL 讓每個條件獨立走索引，避免優化器放棄索引。
3. 可維護性：UNION ALL 程式碼較長但邏輯清晰；OR 簡潔但超過 10 條件易讀性下降。
4. 現代 SQL Server (2016+)：查詢存儲 + 自動調優 + 批次模式 Columnstore，許多舊經驗法則需重新驗證。**以實際執行計畫 (Actual Execution Plan) 為準，勿迷信經驗。**