包含两个EXISTS子查询的SQL查询性能优化方案咨询

核心优化思路 #

你原写法最大的性能问题是：外层每返回一行分组结果，就会对全表执行两次关联子查询，数据量大的时候重复扫描开销会指数级上升。我们可以通过一次预聚合先计算好所有需要的标志位，完全避免多次重复扫表，同时去掉多余的无效操作。

具体优化点 #

• 去掉EXISTS子查询里多余的GROUP BY：EXISTS只需要判断是否存在匹配行，不需要做分组操作，这部分是完全无效的计算开销
• 把两次子查询的判断合并成一次预聚合查询，提前把所有cc_id_hashed、对应年份、周的两个division_cd存在标志计算好，且直接提前过滤出两个标志都为存在的行，减少后续计算量
• 你原SQL第一个子查询里关联条件写的是t2.create_date，第二个是t2.trade_datetime，如果是手误建议统一，下面的优化代码默认按都是trade_datetime处理，你可以按需调整
• 如果division_cd、payment_type、company_cd是字符串类型，条件值要加单引号，避免隐式转换导致索引失效
• 建议给iy_store_sales表建立联合索引：(company_cd, payment_type, is_returned_trade, division_cd, cc_id_hashed, trade_datetime)，也可以提前把年份、周存为计算列加索引，避免查询时对trade_datetime做函数计算无法用到索引

优化后SQL代码 #

WITH pre_agg AS (
    -- 预聚合计算每个cc_id_hashed、年份、周对应的两个division_cd是否存在
    SELECT 
        cc_id_hashed,
        YEAR(trade_datetime) as year_nb,
        DATEPART(week, trade_datetime) as week_nb,
        MAX(CASE WHEN division_cd = '01' THEN 1 ELSE 0 END) AS has_01,
        MAX(CASE WHEN division_cd = '03' THEN 1 ELSE 0 END) AS has_03
    FROM iy_store_sales
    WHERE 
        division_cd IN ('01', '03')
        AND is_returned_trade = 0 
        AND payment_type = '01000'
        AND company_cd = '001'
    GROUP BY cc_id_hashed, YEAR(trade_datetime), DATEPART(week, trade_datetime)
    -- 直接过滤出两个标志都存在的行，减少后续关联数据量
    HAVING MAX(CASE WHEN division_cd = '01' THEN 1 ELSE 0 END) = 1
       AND MAX(CASE WHEN division_cd = '03' THEN 1 ELSE 0 END) = 1
),
main_agg AS (
    -- 计算主表的销售聚合数据
    SELECT 
        cc_id_hashed,
        division_cd,
        dpt_code,
        sales_area_cd,
        YEAR(trade_datetime) as year_nb,
        DATEPART(week, trade_datetime) as week_nb,
        SUM(item_unit_price_aftertax * sales_num) as sales
    FROM iy_store_sales t1
    WHERE 
        division_cd IN ('01', '03')
        AND is_returned_trade = 0 
        AND payment_type = '01000'
        AND company_cd = '001'
    GROUP BY cc_id_hashed, division_cd, dpt_code, sales_area_cd, YEAR(trade_datetime), DATEPART(week, trade_datetime)
)
-- 关联预聚合结果，直接拿到符合条件的所有数据
SELECT 
    m.*,
    'Record Exists' AS first_flag,
    'Record Exists' AS second_flag
FROM main_agg m
INNER JOIN pre_agg p 
    ON m.cc_id_hashed = p.cc_id_hashed
    AND m.year_nb = p.year_nb
    AND m.week_nb = p.week_nb
ORDER BY m.year_nb, m.week_nb

性能提升说明 #

原写法如果外层有N行分组结果，就需要扫2*N次表；优化后的写法仅需要全量扫描2次表（预聚合+主聚合，关联是内存级的小结果集关联），数据量越大性能提升越明显，百万级以上数据量预计能有10倍以上的耗时下降。

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