如何优化Snowflake中两张大表关联查询，将耗时从4分钟降至10秒内

问题背景 #

• 环境：Snowflake L规格仓库，由ThoughtSpot触发查询
• 核心场景：关联两张超大规模表（PRODUCTEVENTS含40亿条记录、FEATURESBRIDGE含20亿条记录），表间为一对多内连接关系
• 当前状态：查询耗时4分钟，目标将耗时降至10秒以内
• 已尝试优化手段：Query Acceleration Service（QAS）、表聚类、增大仓库规格

原始查询语句 #

SELECT 
  DATE_TRUNC('MONTH', "ta_1"."DATE") "ca_1", 
  "ta_2"."FEATURELEVEL1" "ca_2", 
  count(DISTINCT "ta_3"."EVENTUUID") "ca_3"
FROM "C_GOLD"."PRODUCT_USAGE"."FEATURESBRIDGE" "MTA_0"
  JOIN "C_GOLD"."PRODUCT_USAGE"."MAPPEDFEATURES" "ta_2"
    ON "MTA_0"."FEATUREID" = "ta_2"."FEATUREID"
  JOIN "C_GOLD"."PRODUCT_USAGE"."PRODUCTEVENTS" "ta_3"
    ON (
      "MTA_0"."APPSTRINGID" = "ta_3"."APPSTRINGID"
      AND "MTA_0"."EVENTUUID" = "ta_3"."EVENTUUID"
      AND "MTA_0"."DATEKEY" = "ta_3"."DATEKEY"
    )
  JOIN "C_GOLD"."MASTER_DATA"."DATEDIM" "ta_1"
    ON "ta_3"."DATEKEY" = "ta_1"."DATEKEY"
  JOIN "C_GOLD"."MASTER_DATA"."COMPANIES" "ta_5"
    ON "ta_3"."COMPANYID" = "ta_5"."COMPANYID"
  JOIN "C_GOLD"."MASTER_DATA"."PRODUCTTAGS" "MTA_1"
    ON "ta_3"."PRODUCTTAGID" = "MTA_1"."PRODUCTTAGID"
  LEFT OUTER JOIN "C_GOLD"."MASTER_DATA"."PARENTVIEW" "ta_4"
    ON "MTA_1"."PARENTTAGID" = "ta_4"."PARENTTAGID"
WHERE (
  "ta_1"."DATE" >= DATE('2023-08-01', 'YYYY-MM-DD')
  AND "ta_1"."DATE" < DATE('2024-08-01', 'YYYY-MM-DD')
  AND LOWER("ta_4"."PRODUCTTAGNAME") = 'smart 3d'
  AND "ta_5"."ISINTERNAL" = FALSE
)
GROUP BY 
  "ca_1", 
  "ca_2"
ORDER BY "ca_1" ASC NULLS LAST;

优化方案 #

1. 谓词下推与过滤前置 #

• 直接在PRODUCTEVENTS表用DATEKEY过滤日期（替代通过DATEDIM关联后过滤），减少大表扫描量
• 在PARENTVIEW表新增持久化列LOWER_PRODUCTTAGNAME（存储小写转换后的标签名），并创建索引，避免查询时实时计算LOWER()
• 提前过滤COMPANIES表中ISINTERNAL = FALSE的记录，缩小后续JOIN的数据范围

2. 调整JOIN顺序与关联逻辑 #

• 优先从维度小表开始过滤：先筛选PARENTVIEW（LOWER_PRODUCTTAGNAME = 'smart 3d'）、COMPANIES（ISINTERNAL = FALSE）、PRODUCTTAGS，拿到符合条件的ID集合后，再关联PRODUCTEVENTS和FEATURESBRIDGE，避免大表全量扫描
• 为FEATURESBRIDGE和PRODUCTEVENTS的关联字段（APPSTRINGID, EVENTUUID, DATEKEY）创建联合索引，提升关联匹配效率

3. 聚合逻辑优化 #

• 若EVENTUUID在PRODUCTEVENTS中是唯一值，将count(DISTINCT ta_3.EVENTUUID)替换为count(*)，消除去重开销
• 提前在FEATURESBRIDGE与PRODUCTEVENTS关联后完成去重，再进行后续维度表JOIN，减少聚合阶段的数据量

4. 物化视图预计算 #

创建针对该查询场景的物化视图，预计算聚合结果，查询时直接读取预计算数据：

CREATE MATERIALIZED VIEW MV_PRODUCT_USAGE_AGG
AS
SELECT 
  DATE_TRUNC('MONTH', ta_1.DATE) ca_1, 
  ta_2.FEATURELEVEL1 ca_2, 
  count(DISTINCT ta_3.EVENTUUID) ca_3
FROM "C_GOLD"."PRODUCT_USAGE"."FEATURESBRIDGE" MTA_0
  JOIN "C_GOLD"."PRODUCT_USAGE"."MAPPEDFEATURES" ta_2
    ON MTA_0.FEATUREID = ta_2.FEATUREID
  JOIN "C_GOLD"."PRODUCT_USAGE"."PRODUCTEVENTS" ta_3
    ON (MTA_0.APPSTRINGID = ta_3.APPSTRINGID AND MTA_0.EVENTUUID = ta_3.EVENTUUID AND MTA_0.DATEKEY = ta_3.DATEKEY)
  JOIN "C_GOLD"."MASTER_DATA"."DATEDIM" ta_1
    ON ta_3.DATEKEY = ta_1.DATEKEY
  JOIN "C_GOLD"."MASTER_DATA"."COMPANIES" ta_5
    ON ta_3.COMPANYID = ta_5.COMPANYID
  JOIN "C_GOLD"."MASTER_DATA"."PRODUCTTAGS" MTA_1
    ON ta_3.PRODUCTTAGID = MTA_1.PRODUCTTAGID
  JOIN "C_GOLD"."MASTER_DATA"."PARENTVIEW" ta_4
    ON MTA_1.PARENTTAGID = ta_4.PARENTTAGID
WHERE (ta_5.ISINTERNAL = FALSE)
GROUP BY ca_1, ca_2;

• 若查询日期范围相对固定，可创建带过滤条件的物化视图，进一步缩小预计算范围
• 为物化视图按ca_1（月份）分区，提升日期范围查询效率

5. 大表结构与索引优化 #

• 对PRODUCTEVENTS和FEATURESBRIDGE按DATEKEY分区，结合查询的日期过滤，减少扫描的分区数
• 为PRODUCTEVENTS创建覆盖索引：包含DATEKEY, COMPANYID, PRODUCTTAGID, EVENTUUID, APPSTRINGID，避免回表查询
• 为FEATURESBRIDGE创建覆盖索引：包含FEATUREID, APPSTRINGID, EVENTUUID, DATEKEY，加速JOIN过程

6. 其他配置优化 #

• 开启Snowflake的结果缓存（RESULT_SCAN），确保重复查询直接复用结果
• 针对大表的高频过滤字段，启用Snowflake搜索优化服务（Search Optimization Service）

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