📚 课程目录第3章 BigQuery核心机制与工程化使用

3. BigQuery核心机制与工程化使用

BigQuery 的工程化使用，核心不是“会写 SQL”，而是理解它把存储、计算、元数据与调度拆成了不同层次：数据落在列式存储中，查询由 Dremel 执行引擎按槽位（slot）并行处理，而数据集与表则承担命名空间、权限边界和生命周期管理的职责。只有把这几层关系理顺，分区、聚簇、预留和优化才有明确的落点。

数据集与表：先定边界，再定结构

数据集（dataset）是 BigQuery 中最基本的资源容器，它绑定一个区域或双区域/多区域位置，并作为表、视图、例程和模型的命名空间。工程上第一件事是确定数据集的区域：数据集位置一旦创建不可更改，跨区域查询虽然可行，但会带来额外网络开销，且不能直接 JOIN 不同区域的数据。因此建议按“数据主权 + 计算就近”原则划分，例如面向中国大陆分析任务的数据集放在 asia-east1，面向欧洲合规数据的放在 europe-west1。

表设计层面，BigQuery 支持原生表、外部表、视图和物化视图。原生表适合高频查询与需要分区/聚簇优化的场景；外部表适合数据仍留在 GCS 或 Cloud SQL 中的情况，但每次查询都要读取外部源，延迟和成本更高。字段类型选择上，优先使用 INT64、NUMERIC、STRING、TIMESTAMP 等原生类型，避免用 STRING 存日期或金额，否则后续分区、聚簇和比较都会失效。嵌套结构（STRUCT 与 ARRAY）是 BigQuery 的强项，适合把“订单 + 订单行”这类一对多关系压进单表，减少 JOIN。

分区与聚簇：让扫描量可控

分区（partitioning）把一张表按时间或整数范围切成物理块，查询时只扫描命中分区。最常用的是按 DATE/TIMESTAMP 列做时间分区，也支持按 INT64 范围分区。分区列必须是顶层字段，且不能是表达式结果。例如按天分区的事实表：

CREATE TABLE `proj.sales.orders`
(
  order_id      INT64,
  order_ts      TIMESTAMP,
  customer_id   INT64,
  amount        NUMERIC,
  region        STRING
)
PARTITION BY DATE(order_ts)
CLUSTER BY region, customer_id
OPTIONS (
  partition_expiration_days = 365,
  description = "订单事实表，按天分区，按区域和客户聚簇"
);

聚簇（clustering）是在分区内部按列排序存储，最多 4 列，顺序影响效果：先按高基数、常用于过滤的列排。上例中 region 基数低但过滤频繁，customer_id 基数高，组合后能显著减少块扫描。分区和聚簇不是二选一：分区决定“扫哪些块”，聚簇决定“块内怎么排”，两者叠加才能把扫描量压到最低。

需要特别注意的是，分区裁剪依赖查询条件能直接映射到分区列。写成 WHERE DATE(order_ts) = '2024-06-01' 可以裁剪，而 WHERE EXTRACT(YEAR FROM order_ts) = 2024 通常无法有效裁剪，因为函数包裹了分区列。工程上应尽量让过滤条件保持“裸列比较”。

槽位与预留：把计算资源变成可规划项

BigQuery 的计算资源以“槽位”计量。按需（on-demand）模式下，所有用户共享一个弹性槽位池，按扫描字节计费，适合负载波动大、预算按查询控制的团队。但当并发查询多、单查询扫描量大时，按需模式会出现排队，且成本随扫描量线性上升。此时可考虑预留（reservation）：购买固定数量的槽位，绑定到项目或组织，查询按槽位分配执行，成本从“按扫描量”转为“按时间”。

预留的关键参数包括：槽位数量（如 500 slots）、区域（必须与数据集位置一致）、承诺期（无承诺、1 年、3 年，承诺越长单价越低）。工程上常见的做法是：为 BI 报表类稳定负载购买 1 年承诺预留，为临时探索类查询保留按需模式，通过项目分层隔离。需要提醒的是，预留槽位不会自动“加速”单条查询，它改变的是并发能力和成本模型；单条查询的耗时仍取决于扫描量、分区裁剪和 JOIN 规模。

查询优化基础：从扫描量入手

BigQuery 优化的第一原则是减少扫描字节，因为按需计费和查询耗时都与扫描量强相关。具体做法包括：只 SELECT 需要的列（列式存储下，不读的列不产生扫描量）；让 WHERE 条件命中分区和聚簇；避免在分区列上套函数；对大表先过滤再 JOIN；用 APPROX_COUNT_DISTINCT 替代精确去重；对重复使用的中间结果用 CTAS 或物化视图固化。

执行后应查看查询计划（Execution Details），重点关注“Bytes processed”“Bytes billed”“Stage timing”和“Slot time”。如果发现某个阶段扫描量远大于预期，通常意味着分区裁剪未生效或 JOIN 顺序不合理。例如下面这条查询：

-- 不推荐：函数包裹分区列，无法裁剪
SELECT region, SUM(amount)
FROM `proj.sales.orders`
WHERE EXTRACT(YEAR FROM order_ts) = 2024
GROUP BY region;

-- 推荐：裸列范围比较，可裁剪
SELECT region, SUM(amount)
FROM `proj.sales.orders`
WHERE order_ts >= '2024-01-01' AND order_ts < '2025-01-01'
GROUP BY region;

两条 SQL 结果相同，但第二条能利用 DATE(order_ts) 分区裁剪，扫描量可能相差一个数量级。工程上应把这类写法固化为团队规范，并在代码评审中检查。

分区和聚簇的列一旦确定，后续变更需要重建表。建议在建表前先分析典型查询的过滤列和聚合列，再决定分区键与聚簇键，避免上线后频繁迁移。

预留槽位必须与数据集位于同一区域，否则查询无法使用该预留。跨区域查询不仅无法享受预留，还会产生额外的网络传输开销。

把以上四层串起来看：数据集与表决定“数据放在哪、长什么样”，分区与聚簇决定“扫描多少”，槽位与预留决定“用多少计算资源、花多少钱”，查询优化则是在前三者约束下把 SQL 写到最优。工程化的标志，是这些选择有明确的依据、可复现的配置和可观测的结果，而不是凭感觉调参。