SQL Server查询优化与子查询移除方案咨询

场景说明 #

使用SQL Server 2019 RTM版本，报表查询依赖视图dbo.v_auto_no_home（关联4000万条记录的landing_CIM_NIN_DOB表），已通过添加索引提升查询速度，现需移除子查询并进一步优化性能，原查询语句如下：

select
        vautonohom0_.membership_id as col_0_0_,
        vautonohom0_.membership_name as col_1_0_,
        max(vautonohom0_.coverage_age) as col_2_0_,
        min(vautonohom0_.coverage_age) as col_3_0_,
        vautonohom0_.renewal_month as col_4_0_,
        max(vautonohom0_.policy_deposit_premium) as col_5_0_,
        vautonohom0_.life_insured_amount as col_6_0_,
        vautonohom0_.zip_postal_code as col_7_0_,
        (select
            membership1_.membership_agent_assignment_id 
        from
            membership_agent_assignment membership1_ 
        where
            membership1_.membership_id=vautonohom0_.membership_id 
            and membership1_.record_expiration_date>current_timestamp 
            and (
                membership1_.assignment_type='ASSIGNMENT' 
                or membership1_.assignment_type='NO_CALL_FLAG' 
                and membership1_.agent_email_address='johndoe@gmail.com'
            )) as col_8_0_ 
    from
        v_auto_no_home vautonohom0_ 
    where
        (
            vautonohom0_.membership_id in (
                select
                    dimcustome4_.membership_id 
                from
                    fact_policy_coverage factpolicy2_ 
                inner join
                    v_impersonated_agent dimagent3_ 
                        on factpolicy2_.dim_agent_id=dimagent3_.dim_agent_id 
                inner join
                    dim_customer dimcustome4_ 
                        on factpolicy2_.dim_customer_id=dimcustome4_.dim_customer_id 
                where
                    dimagent3_.agent_number in (
                        '0108132'
                    ) 
                group by
                    dimcustome4_.membership_id
            )
        ) 
        and (
            '' in (
                'September'
            ) 
            or vautonohom0_.renewal_month in (
                'September'
            )
        ) 
        and (
            0=0 
            or vautonohom0_.membership_id not in  (
                select
                    membership5_.membership_id 
                from
                    membership_agent_assignment membership5_ 
                where
                    membership5_.membership_id=vautonohom0_.membership_id 
                    and membership5_.record_expiration_date>current_timestamp 
                    and membership5_.assignment_type='NO_CALL_FLAG' 
                    and membership5_.agent_email_address='johndoe@gmail.com'
            )
        ) 
    group by
        vautonohom0_.membership_id ,
        vautonohom0_.membership_name ,
        vautonohom0_.life_insured_amount ,
        vautonohom0_.zip_postal_code ,
        vautonohom0_.renewal_month 
    order by
        vautonohom0_.membership_id desc

核心优化：移除子查询 #

原查询中存在三类子查询（SELECT列关联子查询、WHERE IN子查询、WHERE NOT IN子查询），全部可通过JOIN替换，减少查询执行时的重复计算：

优化后查询语句 #

SELECT
    v.membership_id AS col_0_0_,
    v.membership_name AS col_1_0_,
    MAX(v.coverage_age) AS col_2_0_,
    MIN(v.coverage_age) AS col_3_0_,
    v.renewal_month AS col_4_0_,
    MAX(v.policy_deposit_premium) AS col_5_0_,
    v.life_insured_amount AS col_6_0_,
    v.zip_postal_code AS col_7_0_,
    maa.membership_agent_assignment_id AS col_8_0_
FROM
    v_auto_no_home v
-- 替换WHERE IN子查询：提前筛选目标membership_id
INNER JOIN (
    SELECT DISTINCT
        dc.membership_id
    FROM
        fact_policy_coverage fpc
    INNER JOIN v_impersonated_agent ia 
        ON fpc.dim_agent_id = ia.dim_agent_id
    INNER JOIN dim_customer dc 
        ON fpc.dim_customer_id = dc.dim_customer_id
    WHERE
        ia.agent_number = '0108132'
) filter_members 
    ON v.membership_id = filter_members.membership_id
-- 替换SELECT列关联子查询：LEFT JOIN获取代理分配ID
LEFT JOIN membership_agent_assignment maa 
    ON v.membership_id = maa.membership_id
    AND maa.record_expiration_date > CURRENT_TIMESTAMP
    AND (
        maa.assignment_type = 'ASSIGNMENT'
        OR (maa.assignment_type = 'NO_CALL_FLAG' AND maa.agent_email_address = 'johndoe@gmail.com')
    )
-- 替换WHERE NOT IN子查询：LEFT JOIN排除指定记录
LEFT JOIN membership_agent_assignment exclude_maa 
    ON v.membership_id = exclude_maa.membership_id
    AND exclude_maa.record_expiration_date > CURRENT_TIMESTAMP
    AND exclude_maa.assignment_type = 'NO_CALL_FLAG'
    AND exclude_maa.agent_email_address = 'johndoe@gmail.com'
WHERE
    -- 清理冗余条件：移除恒假的'' in ('September')和恒真的0=0
    v.renewal_month = 'September'
    -- 通过IS NULL实现原NOT IN逻辑，避免NOT IN的性能陷阱
    AND exclude_maa.membership_id IS NULL
GROUP BY
    v.membership_id,
    v.membership_name,
    v.life_insured_amount,
    v.zip_postal_code,
    v.renewal_month,
    maa.membership_agent_assignment_id
ORDER BY
    v.membership_id DESC

其他优化方向 #

1. 清理冗余条件 #

原查询中'' in ('September')是恒假条件，0=0是恒真条件，直接删除可减少查询解析与执行的无效判断。

2. 索引针对性优化 #

• membership_agent_assignment表：创建复合索引(membership_id, record_expiration_date, assignment_type, agent_email_address)，包含membership_agent_assignment_id列，覆盖JOIN与过滤条件，避免键查找。
• fact_policy_coverage表：创建复合索引(dim_agent_id, dim_customer_id)，加速与代理、客户表的关联。
• dim_customer表：创建索引(dim_customer_id, membership_id)，快速获取membership_id。
• 视图底层表：检查v_auto_no_home关联的landing_CIM_NIN_DOB表，确保现有索引覆盖查询所需的过滤、关联列，必要时添加包含列减少书签查找。

3. 视图优化 #

• 检查v_auto_no_home和v_impersonated_agent的定义，移除不必要的关联或计算，简化视图逻辑。
• 如果视图包含聚合操作且数据更新频率较低，可创建索引视图，提前存储计算结果，大幅提升查询速度（注意：索引视图需满足SQL Server的确定性要求，且会增加数据更新的维护成本）。

4. 执行计划分析 #

通过SQL Server Management Studio查看执行计划，定位以下瓶颈：

• 表扫描/聚集索引扫描：对应表缺少合适索引。
• 键查找/书签查找：需扩展索引包含所需列。
• 哈希匹配：如果数据量过大，可考虑调整索引以触发嵌套循环关联。

5. 统计信息更新 #

定期更新各表的统计信息，确保查询优化器能生成最优执行计划：

UPDATE STATISTICS dbo.landing_CIM_NIN_DOB;
UPDATE STATISTICS dbo.membership_agent_assignment;
UPDATE STATISTICS dbo.fact_policy_coverage;

6. 分区表考虑 #

若landing_CIM_NIN_DOB表数据量持续增长，可按日期或业务维度创建分区表，减少查询时的扫描范围，提升IO效率。

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