Snowflake中含OR运算符的LEFT JOIN查询性能优化替代方案

Snowflake查询优化器对JOIN ON子句中的OR逻辑适配性较差，这类条件通常会导致优化器无法选用效率最高的HASH JOIN算法，退化为嵌套循环匹配，同时无法正常触发分区裁剪、聚簇键定位等优化，最终造成查询耗时过长。针对你描述的「优先匹配LEAD_ID、LEAD_ID为空时匹配CONTACT_ID，且必须同步匹配CAMPAIGN_ID」的业务规则，可通过以下方案改写和优化，性能通常可提升数倍到数十倍。

核心改写方案：拆分等值LEFT JOIN替代OR条件 #

将原逻辑中单个带OR的MEMBERS表关联，拆分为两个纯等值条件的LEFT JOIN，通过字段优先级取值实现原有匹配逻辑，完全避免OR判断：

1. 拆分两次关联MEMBERS表，分别对应两个匹配规则，其中兜底的CONTACT_ID关联增加前置判定，仅在LEAD_ID为空时才触发匹配，减少无效计算
2. SELECT层通过COALESCE函数优先取LEAD维度关联到的字段值，无匹配时再取CONTACT维度的关联值

改写后的关联部分代码如下：

SELECT T1.EMAIL,
       -- 优先取LEAD匹配的结果，无值时取CONTACT匹配结果
       COALESCE(M_LEAD.STATUS, M_CONTACT.STATUS) AS STATUS,
       COALESCE(M_LEAD.CAMPAIGN_ID, M_CONTACT.CAMPAIGN_ID) AS CAMPAIGN_ID,
       COALESCE(M_LEAD.CAMPAIGN_TYPE, M_CONTACT.CAMPAIGN_TYPE) AS CAMPAIGN_TYPE
FROM (
      -- T1子查询逻辑对齐业务字段要求
      SELECT VISITOR_ID,
             ACCOUNT_ID,
             CONTACT_ID,
             ROW_KEY,
             LEAD_ID,
             EMAIL,
             CAMPAIGN_UNIQUE_ID AS CAMPAIGN_ID
      FROM NORMAL_TOUCHPOINTS NTP
      UNION ALL
      SELECT VISITOR_ID,
             ACCOUNT_ID,
             CONTACT_ID,
             ROW_KEY,
             NULL AS LEAD_ID,
             EMAIL,
             CAMPAIGN_ID
      FROM OPP_TOUCHPOINTS OTP
) T1
-- 注意：此处原LIST_FACTS关联也带OR条件，同样是性能瓶颈，改写见下文
JOIN LIST_FACTS LF ON (LF.TP_KEY = T1.ROW_KEY) OR (LF.ATP_KEY = T1.ROW_KEY)
LEFT JOIN ACCOUNTS A ON A.ID = T1.ACCOUNT_ID
LEFT JOIN LEADS L ON L.ID = LF.LEAD_ID
-- 替换原带OR的MEMBERS关联逻辑
LEFT JOIN MEMBERS M_LEAD 
  ON M_LEAD.LEAD_ID = T1.LEAD_ID
  AND M_LEAD.CAMPAIGN_ID = T1.CAMPAIGN_ID
LEFT JOIN MEMBERS M_CONTACT
  ON M_CONTACT.CONTACT_ID = T1.CONTACT_ID
  AND M_CONTACT.CAMPAIGN_ID = T1.CAMPAIGN_ID
  AND T1.LEAD_ID IS NULL -- 仅LEAD_ID为空时才走CONTACT匹配，减少无效关联

该写法的优势：

• 所有关联条件均为纯等值判断，Snowflake可自动选用HASH JOIN执行算法，关联效率远高于带OR的模糊匹配
• 兜底关联增加了T1.LEAD_ID IS NULL过滤条件，避免LEAD_ID有值时的无效CONTACT维度匹配，进一步降低计算量
• 逻辑完全对齐业务要求的匹配优先级，不会出现结果偏差：只要LEAD_ID能匹配到MEMBERS记录，就不会取用CONTACT维度的匹配结果

额外性能瓶颈修复 #

你的SQL中LIST_FACTS表的关联同样使用了OR条件，也是主要性能损耗点，可通过UNION ALL打平关联键的方式改写为纯等值关联：

-- 替换原JOIN LIST_FACTS LF ... 部分
JOIN (
  SELECT TP_KEY AS MATCH_ROW_KEY, LEAD_ID FROM LIST_FACTS WHERE TP_KEY IS NOT NULL
  UNION ALL
  SELECT ATP_KEY AS MATCH_ROW_KEY, LEAD_ID FROM LIST_FACTS WHERE ATP_KEY IS NOT NULL
) LF ON LF.MATCH_ROW_KEY = T1.ROW_KEY

辅助优化手段 #

• 若MEMBERS、LIST_FACTS为TB级大表，可将关联字段设置为聚簇键：MEMBERS表可按CAMPAIGN_ID, LEAD_ID、CAMPAIGN_ID, CONTACT_ID配置聚簇，LIST_FACTS可按TP_KEY、ATP_KEY配置聚簇，开启自动聚类后可进一步减少扫描数据量
• T1的UNION ALL子查询中仅保留上层需要用到的字段，提前过滤掉无意义的空值行、测试数据行，减少参与JOIN的数据集规模
• 改写后可通过EXPLAIN命令查看执行计划，确认所有JOIN均选用HASH JOIN算法，无嵌套循环、笛卡尔积的提示，若出现异常可再针对性调整过滤条件

注意：如果存在单条T1记录同时匹配多条M_LEAD或M_CONTACT记录的情况，需要提前对MEMBERS表按业务规则去重，避免最终结果出现重复行。

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