使用EXISTS匹配百万级表记录时运行过慢的优化咨询

针对两张百万级大表的匹配查询性能问题，以下是具体优化措施：

1. 移除无意义的CTE #

原SQL中的health_data_sample和criminal_justice_sample仅对原表做全量查询，无任何过滤或转换逻辑，直接使用原表即可，减少查询编译和执行的额外开销。

2. 精准简化匹配条件 #

原条件中NOT LIKE '%XXX'和NOT LIKE '%XXX %'范围过宽，且带前缀通配符的LIKE会导致索引失效。根据需求，只需排除值为'XXXXX'的token，因此将条件改为token <> 'XXXXX'，既精准又能利用索引：

-- 原条件
AND (criminal_justice.token_1 NOT LIKE '%XXX' AND health.token_1 NOT LIKE '%XXX %')
-- 优化后
AND criminal_justice.token1 <> 'XXXXX' AND health.token1 <> 'XXXXX'

3. 建立过滤式非聚集索引 #

针对criminal_justice_data的三个token字段，创建**过滤掉'XXXXX'**的非聚集索引，这是提升查询速度的核心：

-- 为每个token字段创建过滤索引
CREATE NONCLUSTERED INDEX IX_criminal_token1 
ON criminal_justice_data(token1) 
WHERE token1 <> 'XXXXX';

CREATE NONCLUSTERED INDEX IX_criminal_token2 
ON criminal_justice_data(token2) 
WHERE token2 <> 'XXXXX';

CREATE NONCLUSTERED INDEX IX_criminal_token3 
ON criminal_justice_data(token3) 
WHERE token3 <> 'XXXXX';

同时，可给health_data的三个token字段创建普通非聚集索引，进一步加速匹配：

CREATE NONCLUSTERED INDEX IX_health_token1 ON health_data(token1);
CREATE NONCLUSTERED INDEX IX_health_token2 ON health_data(token2);
CREATE NONCLUSTERED INDEX IX_health_token3 ON health_data(token3);

4. 拆分OR条件为独立EXISTS查询 #

原SQL中OR条件会让优化器难以选择最优索引，将三个token的匹配拆分为独立的EXISTS子查询，每个子查询可单独利用对应索引：

SELECT DISTINCT health.*
INTO matches
FROM health_data health
WHERE EXISTS (
    SELECT 1 
    FROM criminal_justice_data c 
    WHERE c.token1 = health.token1 
      AND c.token1 <> 'XXXXX' 
      AND health.token1 <> 'XXXXX'
)
OR EXISTS (
    SELECT 1 
    FROM criminal_justice_data c 
    WHERE c.token2 = health.token2 
      AND c.token2 <> 'XXXXX' 
      AND health.token2 <> 'XXXXX'
)
OR EXISTS (
    SELECT 1 
    FROM criminal_justice_data c 
    WHERE c.token3 = health.token3 
      AND c.token3 <> 'XXXXX' 
      AND health.token3 <> 'XXXXX'
);

5. 改用UNION ALL收集匹配ID再关联 #

先通过UNION ALL收集所有匹配的health_data的ID（自动去重），再关联原表获取完整数据，这种方式通常比直接DISTINCT更高效：

SELECT h.*
INTO matches
FROM health_data h
JOIN (
    -- 收集所有匹配的ID
    SELECT DISTINCT h1.id 
    FROM health_data h1
    JOIN criminal_justice_data c1 ON h1.token1 = c1.token1
    WHERE h1.token1 <> 'XXXXX' AND c1.token1 <> 'XXXXX'
    
    UNION ALL
    
    SELECT DISTINCT h2.id 
    FROM health_data h2
    JOIN criminal_justice_data c2 ON h2.token2 = c2.token2
    WHERE h2.token2 <> 'XXXXX' AND c2.token2 <> 'XXXXX'
    
    UNION ALL
    
    SELECT DISTINCT h3.id 
    FROM health_data h3
    JOIN criminal_justice_data c3 ON h3.token3 = c3.token3
    WHERE h3.token3 <> 'XXXXX' AND c3.token3 <> 'XXXXX'
) AS matched_ids ON h.id = matched_ids.id;

6. 临时表优化（可选） #

若criminal_justice_data中'XXXXX'占比极高，可先将有效数据（非'XXXXX'的token）导入临时表并建索引，再进行匹配：

-- 创建临时表存储有效数据
SELECT * INTO #criminal_filtered 
FROM criminal_justice_data 
WHERE token1 <> 'XXXXX' OR token2 <> 'XXXXX' OR token3 <> 'XXXXX';

-- 给临时表建索引
CREATE NONCLUSTERED INDEX IX_temp_token1 ON #criminal_filtered(token1);
CREATE NONCLUSTERED INDEX IX_temp_token2 ON #criminal_filtered(token2);
CREATE NONCLUSTERED INDEX IX_temp_token3 ON #criminal_filtered(token3);

-- 使用临时表执行查询
SELECT DISTINCT health.*
INTO matches
FROM health_data health
WHERE EXISTS (
    SELECT 1 
    FROM #criminal_filtered c 
    WHERE c.token1 = health.token1 
      AND health.token1 <> 'XXXXX'
)
OR EXISTS (
    SELECT 1 
    FROM #criminal_filtered c 
    WHERE c.token2 = health.token2 
      AND health.token2 <> 'XXXXX'
)
OR EXISTS (
    SELECT 1 
    FROM #criminal_filtered c 
    WHERE c.token3 = health.token3 
      AND health.token3 <> 'XXXXX'
);

-- 清理临时表
DROP TABLE #criminal_filtered;

7. 检查执行计划 #

运行查询前，先查看执行计划（SQL Server Management Studio中按Ctrl+M），重点关注：

• 是否存在全表扫描（Table Scan）
• 是否使用了创建的索引
• 哈希匹配（Hash Match）的开销是否过高

根据执行计划调整索引或查询逻辑，针对性解决性能瓶颈。

内容的提问来源于stack exchange，提问作者DiamondJoe12