照片由 jesse orrico 拍摄，来自 Unsplash

当我们处理BigQuery表时，分区和聚簇通常是常用的操作之一。通常我们会按有意义的日期进行分区（这有助于联接操作以及在增量加载中的水印处理），并根据构成粒度的列、常见的过滤条件或在联接操作（或SQL MERGE语句）中常被使用的列来进行聚簇。

最近在做一项任务时，我有一个假设没有像预期那样实现。当时的情况是这样：

假设有两个表：
- 每个商店的订单，包含类似 order_id、order_date 和 store_id 这几个字段。
- 未分区的订单金额，该表未分区，仅按 order_id 排序。该表没有任何日期字段。

由于 order_id 是一个唯一标识符，因此它足够用来连接。然而，我认为将 order_amounts_unpartitioned 表按 order_date 分区可以提高性能。我的想法是通过在两个表中使用相同的 order_date 字段进行分区来优化连接。

为了测试这一点，我在一个大约有500万订单的沙盒项目中做了一次实验。结果让我感到惊讶。

结果：
- 与未分区的原始表（order_amounts_unpartitioned）连接，并按order_id进行聚簇，在仅以order_id为连接条件时，实际上在成本和效率方面表现最佳，
- 与我原先的假设相反，我认为会更高效的选项——通过order_date（分区字段）和order_id进行连接——实际上成本要高得多，
- 最后，仅使用order_id与分区表连接——结果证明效率最低。

关键要点：这很好地提醒了我们一下：单独使用聚簇就足够了（甚至是最优的方案之一）来优化查询性能，尤其是在将结果分成较小的分区时（文档建议每个分区至少要有10GB大小）。

默认分区并不总是最佳选择，特别是对于小表而言，因此，请仔细考虑聚簇方式，包括聚簇字段的排列顺序。

通过实际世界中的测试来验证假设的重要性最终被突显。

资源消耗在不同运行之间有很大差异（所以不要过于纠结于具体的百分比数值），但相对性能排名稳定。另外还要注意，结果可能因查询模式和需求的不同而有所变化。

觉得有用吗？可以在这里订阅我的数据分析通讯，网址是 https://notjustsql.com .

原发表于 https://datawise.dev 2024年10月15日.