BigQuery优化SQL新增字段后性能骤降，求助排查方向

问题概述 #

我在BigQuery中优化一个两步SQL流程时遇到了性能骤降的异常：

• 原流程：第一步从数十亿条记录的TABLE_1选取10个字段，左连接TABLE_2、TABLE_3各获取1个字段；第二步将中间表与同量级的T4左连接获取20个额外字段。
• 优化思路：发现T4的目标字段均存在于TABLE_1，因此改为一步直接从TABLE_1选取所有所需字段，省去第二步的左连接操作，预期大幅提升性能。
• 实际结果：优化后第一步的槽位时间（slot time）从9小时增至9天。字段数量仅增加到原来的3倍，但耗时却提升了10-15倍；且TABLE_1与TABLE_2、TABLE_3的连接操作耗时大幅增加，尽管连接条件和过滤条件完全未变。两种方案的执行步骤图几乎一致，但性能差异极大。

代码示例 #

原方案第一步（少字段，需第二步左连T4） #

CREATE OR REPLACE TABLE `INTERMEDIATE_TABLE_STEP_1_TEST_1`
CLUSTER BY field1,field3,field5,field6
OPTIONS (
 expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
)
AS (
SELECT
   a.key,
   a.field1,
   CASE WHEN b.field2 IS NOT NULL THEN b.field2
        ELSE a.field3 END AS field3,
   a.field4,
   a.field5,
   a.field6,
   a.field7 as field7,
   a.field8,
   a.field9,
   CASE WHEN a.field10 <> 'LOW' OR a.field8 = 'asdasd' THEN 'cas1' ELSE 'cas2' END AS field10,
   CASE WHEN a.field11='field11' AND a.field12 LIKE 'field12%' AND a.field13 = TRUE THEN 1 ELSE 0 END AS field13,
   CASE WHEN pape.key IS NOT NULL THEN 1 ELSE 0 END AS field14
FROM `TABLE_1` a
LEFT JOIN `TABLE_2` b ON a.key = b.key
LEFT JOIN `TABLE_3` pape ON pape.key = a.key
WHERE a.field11 = 'filter_1'
   AND a.date_filter >= (DATE_ADD((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),INTERVAL -5 MONTH))
);

优化后第一步（多字段，省去第二步） #

CREATE OR REPLACE TABLE `INTERMEDIATE_TABLE_STEP_1_TEST_2`
OPTIONS (
 expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
)
AS (
SELECT
   SPLIT(SPLIT(a.field15, ',') [OFFSET(2)],':')[OFFSET(1)] AS field15,
   SPLIT(SPLIT(a.field15, ',') [OFFSET(1)],':')[OFFSET(1)] AS field15bis,
   Extract(HOUR From a.dttm_field) AS hora,
      a.key,
      a.field16,
      a.field17,
      a.field18,
   CASE WHEN LENGTH(CAST(a.field19 AS STRING)) = 1 THEN
             a.field20||"_000"||a.field19
        WHEN LENGTH(CAST(a.field19 AS STRING)) = 2 THEN
             a.field20||"_00"||a.field19   
        WHEN LENGTH(CAST(a.field19 AS STRING)) = 3 THEN
             a.field20||"_0"||a.field19
        ELSE a.field20||"_"||a.field19 END AS field19,
      a.field20,
      a.field21,
      a.date_filter,
      a.field22,
      a.field23,
      a.field24,
      a.field25,
      a.field26,
      a.field27,
      a.field28,
      a.field29,
      a.field30,
      a.field31,
      a.field4 AS field4b,
      a.field4,
      a.field32,
   a.field1,
      a.field6,
   a.field5,
      a.field33,  
      a.field19,
      JSON_EXTRACT_SCALAR(field34, '$.f1.f2') AS f1f2,
      SUBSTR(field35,2,LENGTH(field35)-2) AS field35,
   CASE WHEN a.field10 <> 'LOW' OR a.field8 = 'asdasd' THEN 'cas1' ELSE 'cas2' END AS field10,
   CASE WHEN a.field11='field11' AND a.field12 LIKE 'field12%' AND a.field13 = TRUE THEN 1 ELSE 0 END AS field13,
   CASE WHEN pape.key IS NOT NULL THEN 1 ELSE 0 END AS field14,
   'null' as nullf,
   CASE WHEN b.field2 IS NOT NULL THEN b.field2
        ELSE a.field3 END AS field3,
   a.field7 AS fieled7,
   a.field8,
   a.field9,
   a.field36 AS field36
from `TABLE_1` a
LEFT JOIN `TABLE_3` pape ON pape.key = a.key
LEFT JOIN `TABLE_2` b ON a.key = b.key

WHERE 1=1
    AND a.field11 = 'filter_1'
    AND a.date_filter >= (DATE_ADD((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),INTERVAL -5 MONTH))
);

执行差异参考 #

• 少字段第一步：执行详情截图、性能趋势图表
• 多字段第一步：执行详情截图、性能趋势图表

求助需求 #

请提供该性能异常的排查方向，帮助解决BigQuery查询性能问题并制定高效的查询方案。

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