含多WHERE条件的百万级表SQL查询优化方案咨询

问题概述 #

现有查询针对500万+条记录的df表，关联384行的临时表#dx筛选符合条件的PT_ID，当前执行耗时数小时，需优化性能。

现有信息 #

表结构（df） #

相关说明 #

• #dx表数据量：384行
• df表现有索引：
  • 非唯一非聚集：insurance_clm_date
  • 非唯一非聚集：PT_ID
  • 非唯一非聚集：dx_code

待优化查询语句 #

SELECT 
DISTINCT pt_ID
INTO #pt_list
FROM df
WHERE ((Code1 IN (SELECT DISTINCT Code FROM #dx)
            OR Code2 IN (SELECT DISTINCT Code FROM #dx)
            OR Code3 IN (SELECT DISTINCT Code FROM #dx)
            OR Code4 IN (SELECT DISTINCT Code FROM #dx)
            OR Code5 IN (SELECT DISTINCT Code FROM #dx)
            OR Code6 IN (SELECT DISTINCT Code FROM #dx)
            )
OR dx_code IN (SELECT DISTINCT Code FROM #dx WHERE [Code Description] IN ('Emergency')))
AND insurance_clm_date BETWEEN '2021-12-01' AND '2022-11-15'

df临时表DDL #

CREATE TABLE #df
(pt_id    VARCHAR(11) NOT NULL PRIMARY KEY, 
 Code1 VARCHAR(7) NULL, 
 Code2     VARCHAR(7) NULL, 
 Code3  VARCHAR(7) NULL, 
 Code4      VARCHAR(7) NULL,
 Code5      VARCHAR(7) NULL, 
 Code6      VARCHAR(7) NULL,
 dx_code      VARCHAR(5) NULL,
 insurance_clm_date      DATE NULL
);

优化建议 #

1. 预缓存#dx过滤结果，避免重复子查询 #

原查询多次执行SELECT DISTINCT Code FROM #dx，会重复计算。先将需要的结果存入临时表，减少重复IO：

-- 预存所有#dx的Code值
SELECT DISTINCT Code INTO #dx_all FROM #dx;
-- 预存Emergency对应的Code值
SELECT DISTINCT Code INTO #dx_emergency FROM #dx WHERE [Code Description] = 'Emergency';

2. 改写OR条件为UNION ALL，利用索引避免全表扫描 #

OR条件容易导致数据库放弃索引改用全表扫描，拆分多个条件为独立查询后用UNION ALL合并，最后去重：

SELECT pt_ID INTO #pt_list
FROM (
    -- 匹配Code1的记录
    SELECT df.pt_ID
    FROM df
    JOIN #dx_all dx ON df.Code1 = dx.Code
    WHERE df.insurance_clm_date BETWEEN '2021-12-01' AND '2022-11-15'
    UNION ALL
    -- 匹配Code2的记录
    SELECT df.pt_ID
    FROM df
    JOIN #dx_all dx ON df.Code2 = dx.Code
    WHERE df.insurance_clm_date BETWEEN '2021-12-01' AND '2022-11-15'
    UNION ALL
    -- 匹配Code3的记录
    SELECT df.pt_ID
    FROM df
    JOIN #dx_all dx ON df.Code3 = dx.Code
    WHERE df.insurance_clm_date BETWEEN '2021-12-01' AND '2022-11-15'
    UNION ALL
    -- 匹配Code4的记录
    SELECT df.pt_ID
    FROM df
    JOIN #dx_all dx ON df.Code4 = dx.Code
    WHERE df.insurance_clm_date BETWEEN '2021-12-01' AND '2022-11-15'
    UNION ALL
    -- 匹配Code5的记录
    SELECT df.pt_ID
    FROM df
    JOIN #dx_all dx ON df.Code5 = dx.Code
    WHERE df.insurance_clm_date BETWEEN '2021-12-01' AND '2022-11-15'
    UNION ALL
    -- 匹配Code6的记录
    SELECT df.pt_ID
    FROM df
    JOIN #dx_all dx ON df.Code6 = dx.Code
    WHERE df.insurance_clm_date BETWEEN '2021-12-01' AND '2022-11-15'
    UNION ALL
    -- 匹配dx_code为Emergency的记录
    SELECT df.pt_ID
    FROM df
    JOIN #dx_emergency dx ON df.dx_code = dx.Code
    WHERE df.insurance_clm_date BETWEEN '2021-12-01' AND '2022-11-15'
) AS subquery
GROUP BY pt_ID; -- 代替外层DISTINCT，效率更高

3. 创建针对性复合索引，避免回表查询 #

现有单列索引无法满足多条件过滤需求，创建以下复合覆盖索引（包含查询所需所有字段，无需回表）：

-- 针对Code1-6的匹配，创建(insurance_clm_date, CodeX, pt_ID)的复合索引
CREATE NONCLUSTERED INDEX IX_df_Code1_Date_PTID ON df(insurance_clm_date, Code1) INCLUDE(pt_ID);
CREATE NONCLUSTERED INDEX IX_df_Code2_Date_PTID ON df(insurance_clm_date, Code2) INCLUDE(pt_ID);
CREATE NONCLUSTERED INDEX IX_df_Code3_Date_PTID ON df(insurance_clm_date, Code3) INCLUDE(pt_ID);
CREATE NONCLUSTERED INDEX IX_df_Code4_Date_PTID ON df(insurance_clm_date, Code4) INCLUDE(pt_ID);
CREATE NONCLUSTERED INDEX IX_df_Code5_Date_PTID ON df(insurance_clm_date, Code5) INCLUDE(pt_ID);
CREATE NONCLUSTERED INDEX IX_df_Code6_Date_PTID ON df(insurance_clm_date, Code6) INCLUDE(pt_ID);
-- 针对dx_code的匹配，创建(insurance_clm_date, dx_code, pt_ID)的复合索引
CREATE NONCLUSTERED INDEX IX_df_dxCode_Date_PTID ON df(insurance_clm_date, dx_code) INCLUDE(pt_ID);

4. 转置Code1-Code6列的可行性分析 #

将Code1-Code6转置为单列结构（如创建子表df_codes，包含pt_id、code_value、insurance_clm_date）确实能显著提升性能，原因如下：

• 转置后可将多个OR条件转化为单次JOIN操作，避免多次扫描表
• 可创建单一复合索引IX_df_codes_Code_Date_PTID (code_value, insurance_clm_date, pt_id)，完全覆盖查询需求
• 后续类似多Code匹配的查询无需重复改写语句，扩展性更好

示例转置后的查询：

-- 假设已转置为df_codes表，包含pt_id, code_value, insurance_clm_date
SELECT DISTINCT dc.pt_id INTO #pt_list
FROM df_codes dc
JOIN #dx_all dx ON dc.code_value = dx.Code
WHERE dc.insurance_clm_date BETWEEN '2021-12-01' AND '2022-11-15'
UNION
SELECT DISTINCT df.pt_id
FROM df
JOIN #dx_emergency dx ON df.dx_code = dx.Code
WHERE df.insurance_clm_date BETWEEN '2021-12-01' AND '2022-11-15';

额外提示 #

• 执行查询前更新表统计信息：UPDATE STATISTICS df;，帮助优化器生成更优执行计划
• 若临时表#df是从原表复制的，确保其索引与原表一致，或直接使用原表查询

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