SQL查询优化求助：耗时2小时的BI工具适配查询（已加分区）

查询加载耗时约2小时，数据量过大导致无法在BI工具中使用。问题根源是huge_table，已为该表添加日期分区（ds），但加载速度仍然极慢，请帮忙优化以下查询：

WITH huge.table AS (
    SELECT distinct ORDER_ID, account_id, ds
    FROM "huge.table"
    WHERE yesno_condition='y' AND days_active>0
), CTE2 AS (
    SELECT CTE2.EMAIL, CTE2.CONTACT_ID 
    FROM Contact_details_table 
    JOIN huge.table HT ON (CTE2.ORDER_ID=HT.ORDER_ID AND CTE2.account_id=HT.account_id) 
    Where CTE2.EMAIL IN (
        SELECT NEW_EMAIL as EMAIL FROM maintable
        UNION ALL 
        SELECT EMAIL as EMAIL FROM maintable
    ) AND HT.ds>= dateadd(year, -2, current_date)
)
SELECT  
    mt.metric1, 
    mt.metric2, 
    mt.metric3, 
    mt.metric4, 
    mt.metric5,
    mt.metric6, 
    mt.metric7, 
    mt.metric8, 
    mt.metric9, 
    mt.metric10,
    mt.metric11, 
    mt.metric12, 
    mt.metric13, 
    mt.metric14, 
    ot.metric1,
    CTE2.CONTACT_ID 
FROM  maintable as L 
JOIN CTE2 U ON lower(CTE2.EMAIL)=(case when (mt.EMAIL !=CTE2.EMAIL) then NEW_EMAIL END) 
JOIN othertable AS ot ON (mt.old_email=ot.email OR mt.new_email=ot.email) 
WHERE ot.exist_condition='Y'  
AND ot.ACCOUNT_TYPE !='inactive' 
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19

优化建议 #

1. 修复语法与别名错误 #

原查询存在多处基础语法问题，先修正：

• CTE命名不能包含.，将huge.table改为合法名称huge_table_cte
• CTE2中未给Contact_details_table指定别名，直接用CTE2.EMAIL属于未定义，新增别名cd
• 主查询中maintable别名为L却用mt引用，统一为mt

2. 提前过滤大表数据 #

利用分区字段先缩小huge_table的扫描范围，再做后续处理：

• 将HT.ds >= dateadd(year, -2, current_date)放到huge_table_cte的WHERE条件中，减少去重和关联的数据量
• 用GROUP BY替代DISTINCT，多列去重时性能更稳定

3. 简化IN子查询 #

原IN子查询用UNION ALL会产生重复邮箱，提前去重减少关联压力：

SELECT DISTINCT email FROM (
    SELECT NEW_EMAIL as email FROM maintable
    UNION ALL 
    SELECT EMAIL as email FROM maintable
) t

4. 优化JOIN条件 #

• 原CTE2关联条件逻辑混乱，明确匹配规则，比如如果是匹配mt.NEW_EMAIL，直接写成lower(U.EMAIL) = lower(mt.NEW_EMAIL)，避免CASE表达式的性能损耗
• othertable的JOIN用OR会触发全表扫描，建议拆分为两个JOIN后用UNION ALL合并，或者给othertable.email加索引

5. 索引优化 #

• 给huge_table创建组合索引：ds, yesno_condition, days_active, ORDER_ID, account_id，利用分区+过滤条件+关联字段的顺序提升扫描效率
• 给Contact_details_table创建组合索引：ORDER_ID, account_id, EMAIL, CONTACT_ID，覆盖关联和查询字段
• 给maintable的EMAIL, NEW_EMAIL创建索引
• 给othertable创建组合索引：email, exist_condition, ACCOUNT_TYPE, metric1，覆盖过滤和查询字段

6. 简化分组逻辑 #

如果SELECT字段没有聚合函数，GROUP BY可以替换为DISTINCT，减少分组计算开销；如果不需要去重，直接去掉GROUP BY

优化后的SQL示例 #

WITH huge_table_cte AS (
    SELECT ORDER_ID, account_id, ds
    FROM "huge.table"
    WHERE ds >= dateadd(year, -2, current_date)
      AND yesno_condition='y' 
      AND days_active>0
    GROUP BY ORDER_ID, account_id, ds -- 替代DISTINCT
), email_list AS (
    SELECT DISTINCT email FROM (
        SELECT NEW_EMAIL as email FROM maintable
        UNION ALL 
        SELECT EMAIL as email FROM maintable
    ) t
), CTE2 AS (
    SELECT cd.EMAIL, cd.CONTACT_ID 
    FROM Contact_details_table cd
    JOIN huge_table_cte HT 
      ON cd.ORDER_ID = HT.ORDER_ID 
     AND cd.account_id = HT.account_id
    WHERE cd.EMAIL IN (SELECT email FROM email_list)
)
SELECT  
    mt.metric1, 
    mt.metric2, 
    mt.metric3, 
    mt.metric4, 
    mt.metric5,
    mt.metric6, 
    mt.metric7, 
    mt.metric8, 
    mt.metric9, 
    mt.metric10,
    mt.metric11, 
    mt.metric12, 
    mt.metric13, 
    mt.metric14, 
    ot.metric1,
    U.CONTACT_ID 
FROM maintable mt
JOIN CTE2 U 
  ON lower(U.EMAIL) = lower(mt.NEW_EMAIL) -- 修正关联逻辑，根据实际需求调整
JOIN othertable ot 
  ON mt.old_email = ot.email 
WHERE ot.exist_condition='Y'  
  AND ot.ACCOUNT_TYPE != 'inactive'
UNION ALL
SELECT  
    mt.metric1, 
    mt.metric2, 
    mt.metric3, 
    mt.metric4, 
    mt.metric5,
    mt.metric6, 
    mt.metric7, 
    mt.metric8, 
    mt.metric9, 
    mt.metric10,
    mt.metric11, 
    mt.metric12, 
    mt.metric13, 
    mt.metric14, 
    ot.metric1,
    U.CONTACT_ID 
FROM maintable mt
JOIN CTE2 U 
  ON lower(U.EMAIL) = lower(mt.NEW_EMAIL)
JOIN othertable ot 
  ON mt.new_email = ot.email 
WHERE ot.exist_condition='Y'  
  AND ot.ACCOUNT_TYPE != 'inactive'
-- 如果需要去重，添加DISTINCT
-- DISTINCT

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