SQL Server关联子查询性能优化求助：700K行表查询耗时过长

问题背景 #

我有一张名为G_HIST的表，包含70万行数据和约200列，目前仅存在一个由10列组成的主键唯一索引。以下关联子查询执行耗时近6分钟，希望能通过改写语句将耗时压缩至半分钟以内；若无法通过改写实现，需要创建哪些索引来优化？

原查询代码：

select distinct Curr.Cycle_Number, Curr.Process_Date,Curr.Group_Policy_Number,
                Curr.Record_Type, Curr.Participant_Identifier,Curr.Person_Type,
                Curr.Effective_Date
FROM G_HIST as Curr 
WHERE Curr.Participant_Identifier not in (
                                   select prev.Participant_Identifier 
                                   from G_HIST as Prev
                                   where Prev.Cycle_Number = (
                                         select max(b.Cycle_Number)-1
                                         FROM G_HIST as b
                                       WHERE b.Group_Policy_Number = Curr.Group_Policy_Number
                                          )
                                          )
AND Curr.[Cycle_Number] = (
                                 select max(a.[Cycle_Number])
                                 FROM G_HIST as a
                                 WHERE a.[Group_Policy_Number] = Curr.[Group_Policy_Number]
                                                               )

语句改写方案 #

原查询核心逻辑：筛选每个Group_Policy_Number对应最大Cycle_Number的记录，且这些记录的Participant_Identifier未出现在该组上一周期（max(Cycle_Number)-1）的记录中。

通过CTE+分组聚合/窗口函数改写，避免嵌套关联子查询的重复计算，大幅提升效率：

改写版本1（分组聚合+JOIN） #

WITH GroupCycles AS (
    -- 提前计算每个组的最大周期和上一周期
    SELECT 
        Group_Policy_Number,
        MAX(Cycle_Number) AS MaxCycle,
        MAX(Cycle_Number) - 1 AS PrevCycle
    FROM G_HIST
    GROUP BY Group_Policy_Number
),
CurrentCycleData AS (
    -- 获取当前最大周期的所有目标记录
    SELECT 
        Cycle_Number, 
        Process_Date,
        Group_Policy_Number,
        Record_Type, 
        Participant_Identifier,
        Person_Type,
        Effective_Date
    FROM G_HIST
    JOIN GroupCycles gc 
        ON G_HIST.Group_Policy_Number = gc.Group_Policy_Number
        AND G_HIST.Cycle_Number = gc.MaxCycle
),
PrevCycleParticipants AS (
    -- 获取上一周期的所有参与者（去重）
    SELECT DISTINCT Participant_Identifier, Group_Policy_Number
    FROM G_HIST
    JOIN GroupCycles gc 
        ON G_HIST.Group_Policy_Number = gc.Group_Policy_Number
        AND G_HIST.Cycle_Number = gc.PrevCycle
)
-- 筛选当前周期中未出现在上一周期的参与者
SELECT DISTINCT 
    cc.Cycle_Number, 
    cc.Process_Date,
    cc.Group_Policy_Number,
    cc.Record_Type, 
    cc.Participant_Identifier,
    cc.Person_Type,
    cc.Effective_Date
FROM CurrentCycleData cc
LEFT JOIN PrevCycleParticipants pcp 
    ON cc.Group_Policy_Number = pcp.Group_Policy_Number
    AND cc.Participant_Identifier = pcp.Participant_Identifier
WHERE pcp.Participant_Identifier IS NULL;

改写版本2（窗口函数） #

WITH RankedData AS (
    -- 给每条记录标记所属组的最大周期和上一周期
    SELECT 
        Cycle_Number, 
        Process_Date,
        Group_Policy_Number,
        Record_Type, 
        Participant_Identifier,
        Person_Type,
        Effective_Date,
        MAX(Cycle_Number) OVER (PARTITION BY Group_Policy_Number) AS MaxCycle,
        MAX(Cycle_Number) OVER (PARTITION BY Group_Policy_Number) - 1 AS PrevCycle
    FROM G_HIST
),
CurrentCycle AS (
    -- 筛选当前最大周期的记录
    SELECT *
    FROM RankedData
    WHERE Cycle_Number = MaxCycle
),
PrevCycleParticipants AS (
    -- 获取上一周期的参与者（去重）
    SELECT DISTINCT Participant_Identifier, Group_Policy_Number
    FROM RankedData
    WHERE Cycle_Number = PrevCycle
)
-- 筛选目标结果
SELECT DISTINCT 
    Cycle_Number, 
    Process_Date,
    Group_Policy_Number,
    Record_Type, 
    Participant_Identifier,
    Person_Type,
    Effective_Date
FROM CurrentCycle cc
LEFT JOIN PrevCycleParticipants pcp 
    ON cc.Group_Policy_Number = pcp.Group_Policy_Number
    AND cc.Participant_Identifier = pcp.Participant_Identifier
WHERE pcp.Participant_Identifier IS NULL;

索引优化方案 #

如果改写后性能仍未达标，建议创建以下覆盖索引，避免回表查询并加速分组、筛选逻辑：

CREATE NONCLUSTERED INDEX IX_G_HIST_GroupCycle_Participant 
ON G_HIST (Group_Policy_Number, Cycle_Number) 
INCLUDE (Participant_Identifier, Process_Date, Record_Type, Person_Type, Effective_Date);

索引设计逻辑：

• 以Group_Policy_Number和Cycle_Number作为索引键，快速分组和筛选指定周期的数据
• 通过INCLUDE子句包含查询所需的所有输出列，避免查询时回表读取主键索引的完整数据，大幅提升IO效率

原查询性能差的原因 #

1. 多层嵌套关联子查询：每条Curr记录都会触发两次子查询计算最大周期和上一周期的参与者，导致重复计算量爆炸（约70万行触发140万次子查询）
2. NOT IN子查询存在潜在的NULL值问题，且执行计划通常不如LEFT JOIN高效
3. 原主键索引无法覆盖查询所需的筛选和输出列，大量回表操作拖慢性能

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