MS SQL Server 索引優化實戰：百萬至五千萬筆資料下多鍵值交集查詢效能比較

測試環境與資料分布

• 硬體：P4 2.6GHz + 512MB RAM + 80GB HDD
• 軟體：Windows Server 2003 Enterprise + SQL Server 2000 SP3a
• 資料表：Stress_test(id INT, key CHAR(2))
• 索引：id 非叢集索引；key 叢集索引
• 資料特性：id 有限重複；key 大量重複
• 測試量級：100 萬 ~ 5800 萬筆 (透過 RAND() 產生隨機組合)

目標查詢

找出同時滿足 key='Az'、key='Bw'、key='Cv' 三個條件的 id (即三個 key 值的交集)。

五種寫法效能比較

寫法 1：三獨立子查詢 INNER JOIN

SELECT a.id FROM
(SELECT DISTINCT id FROM stress_test WHERE key = 'Az') a,
(SELECT DISTINCT id FROM stress_test WHERE key = 'Bw') b,
(SELECT DISTINCT id FROM stress_test WHERE key = 'Cv') c
WHERE a.id = b.id AND a.id = c.id

寫法 2：OR 篩選 + GROUP BY HAVING

SELECT id FROM stress_test
WHERE key='Az' OR key='Bw' OR key='Cv'
GROUP BY id HAVING COUNT(DISTINCT key) = 3

寫法 3：NOT EXISTS + 虛擬表 LEFT JOIN (未完整呈現)

SELECT DISTINCT id FROM stress_test A
WHERE NOT EXISTS (
    SELECT 1 FROM
    (SELECT 'Az' AS k UNION ALL SELECT 'Bw' UNION ALL SELECT 'Cv') B
    LEFT JOIN stress_test C ON C.key = B.k AND C.id = A.id
    WHERE C.id IS NULL
)

寫法 4：INTERSECT 集合運算 (SQL 2005+)

SELECT id FROM stress_test WHERE key = 'Az'
INTERSECT
SELECT id FROM stress_test WHERE key = 'Bw'
INTERSECT
SELECT id FROM stress_test WHERE key = 'Cv'

寫法 5：三表自我 JOIN

SELECT DISTINCT a.id FROM stress_test a, stress_test b, stress_test c
WHERE a.key='Az' AND b.key='Bw' AND c.key='Cv'
  AND a.id=b.id AND a.id=c.id

實測效能排序 (5800 萬筆)

關鍵優化洞見

1. 叢集索引鍵 (key) 篩選 + GROUP BY 讓優化器選擇單次掃描配合雜湊聚合，避免多次索引搜尋與聯結
2. COUNT(DISTINCT key) 準確語意表達「三個 key 皆存在」
3. 大量重複鍵值下，INTERSECT / EXISTS 往往因排序/相關子查詢開銷大而較慢
4. 索引設計：key 設為叢集索引正確支援範圍/等值掃描；id 非叢集索引支援聯結

建議最佳實務

-- 參數化版本，避免硬編碼
DECLARE @k1 CHAR(2)='Az', @k2 CHAR(2)='Bw', @k3 CHAR(2)='Cv';
SELECT id FROM stress_test
WHERE key IN (@k1, @k2, @k3)
GROUP BY id HAVING COUNT(DISTINCT key) = 3;