4. BigQuery高级查询与性能调优

当查询从“能跑通”进入“跑得快、跑得省”的阶段，调优的核心就不再是语法，而是对执行过程的理解。BigQuery 是列式存储 + Dremel 执行引擎的托管数仓，真正决定扫描量、Shuffle 量和槽位占用的是执行阶段（Stage）之间的数据流动。因此，调优的第一步永远是看执行计划，而不是凭感觉改 SQL。

一、用执行计划定位瓶颈

在控制台查询结果上方点击“执行详情”，或在 bq query --dry_run 之外配合 INFORMATION_SCHEMA.JOBS_BY_PROJECT 查看历史作业，都能拿到执行计划。重点看三类信息：每个 Stage 的输入/输出行数、读写字节数、以及 records written 与 shuffle output。如果某个 Stage 的输入行数远大于输出行数，说明过滤或聚合生效；如果输出行数反而膨胀，通常是 JOIN 产生了笛卡尔式放大，或窗口函数分区粒度过细。

一个典型信号是：READ 阶段扫描了 2 TB，但最终只返回 300 行。这往往意味着分区裁剪和聚簇没有命中，或者过滤条件写在了 JOIN 之后。此时应优先检查 WHERE 中是否对分区列使用了函数包裹，例如 WHERE DATE(ts) = '2024-05-01' 会导致分区裁剪失效，应改写为 WHERE ts >= '2024-05-01' AND ts < '2024-05-02'。

二、连接优化：小表广播与大表分区对齐

BigQuery 的 JOIN 主要有两种物理实现：Broadcast Join 和 Shuffle Join。当一侧表很小（通常几十 MB 以内）时，引擎会把小表广播到每个槽位，避免大表 Shuffle；当两侧都很大时，则按 JOIN Key 做哈希重分布。优化的关键就是让优化器能选择 Broadcast，并让大表侧的过滤尽量下推。

实践中有三条高频规则：第一，把过滤条件写在子查询里，先缩小参与 JOIN 的数据量；第二，JOIN Key 两侧类型必须一致，INT64 与 STRING 隐式转换会破坏分区与聚簇；第三，避免在 JOIN Key 上使用 CAST、UPPER 等函数。对于星型模型，事实表按日期分区、按常用维度聚簇，维度表保持小体量，是最稳妥的结构。

BigQuery 查询执行计划与调优关键路径 事实表 fact_sales 分区：dt（按天） 聚簇：store_id, sku_id 维表 dim_store 约 12 MB · 小表 可广播 执行阶段（Stage） S00 READ + 分区裁剪 S01 FILTER + 聚合 S02 JOIN（Shuffle 风险） S03 WINDOW / OUTPUT 优化动作 A：过滤下推 WHERE dt 范围前置 减少 S00 扫描字节 优化动作 B：广播小表 dim_store 走 Broadcast 避免大表 Shuffle 优化动作 C：物化视图 预聚合日粒度指标 命中后免重复扫描 结果判读：执行详情中的关键指标 • S00 扫描字节 ↓ 表示分区裁剪生效 • S02 shuffle output ↓ 表示 Broadcast 生效 • 输出行数 ≈ 输入行数 表示无异常放大 • 槽位等待时间 ↓ 表示并发压力缓解

三、窗口函数：把“分组内计算”写对

窗口函数是 BigQuery 最容易被误用的能力之一。它的执行代价与分区数量和分区内排序强相关。常见写法如 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC)，如果 user_id 基数极高，会产生大量小分区，Shuffle 成本上升。此时可考虑先用 GROUP BY 聚合到用户粒度，再在结果上做窗口计算。

另一个高频问题是 QUALIFY 的使用。BigQuery 支持 QUALIFY ROW_NUMBER() OVER (...) = 1，它比“子查询 + WHERE rn = 1”更简洁，且优化器能更好地融合过滤。对于去重取最新场景，这是首选写法。注意窗口函数不能直接写在 WHERE 中，必须借助 QUALIFY 或子查询。

四、UDF 与脚本：把复杂逻辑封装起来

当一段逻辑在多个查询中重复出现，例如地址清洗、金额分桶、会话切分，就应该封装为 UDF。BigQuery 支持 SQL UDF 和 JavaScript UDF。SQL UDF 性能更好、可被优化器内联；JavaScript UDF 适合复杂字符串处理，但会引入逐行调用开销，不适合大表全量扫描。

CREATE TEMP FUNCTION bucket_amount(x FLOAT64)
RETURNS STRING AS (
  CASE
    WHEN x < 100 THEN 'low'
    WHEN x < 1000 THEN 'mid'
    ELSE 'high'
  END
);

SELECT bucket_amount(amount) AS amt_bucket, COUNT(*) AS cnt
FROM `proj.dataset.fact_sales`
WHERE dt = '2024-05-01'
GROUP BY amt_bucket;

脚本（Scripting）则用于把多个语句串成可编排的流程，支持变量、循环、异常处理。典型场景是“先建临时表 → 再跑多个聚合 → 最后写入结果表”。脚本中的 DECLARE、SET、IF、LOOP 让 BigQuery 具备了一定的过程化能力，但要注意：脚本中的每条语句仍是独立作业，调试时应逐段执行并观察执行计划。

五、物化视图：用空间换时间

物化视图（Materialized View）会预先计算并存储聚合结果，查询命中时直接读取预计算结果，从而大幅降低扫描量。它最适合“高频、固定维度、可增量刷新”的聚合场景，例如按天、按门店统计销售额。创建后，BigQuery 会在基表变更时自动增量刷新，无需人工调度。

使用物化视图有三个判断点：第一，查询是否与视图定义高度一致，否则无法命中；第二，基表是否持续写入，增量刷新是否跟得上；第三，视图是否被过度使用导致存储成本上升。实践中建议先用执行计划确认“扫描字节”是否真的下降，再决定是否保留。

调优顺序建议：先看执行计划定位扫描与 Shuffle 瓶颈，再改分区/聚簇与 JOIN 结构，最后才考虑窗口函数重写、UDF 封装和物化视图。顺序颠倒容易在错误的地方投入优化成本。
不要用 SELECT * 做探索性查询后再逐步删列，列式存储下这会直接放大扫描字节。探索阶段也应显式列出所需字段，并始终带上分区过滤条件。

把上述方法串起来，一次完整的调优闭环是：用执行计划找到最贵的 Stage，判断是扫描、Shuffle 还是计算瓶颈；针对性地做过滤下推、广播小表、重写窗口或引入物化视图；再次运行并对比扫描字节、Shuffle 输出与耗时。只有指标确实下降，优化才算成立。

← 上一章 📖 返回目录 下一章 →