BigQuery广播连接是否优于哈希连接？测试偏差与优化验证

你的测试是否存在偏差？ #

是的，测试偏差主要来自以下几个方面：

• 数据规模过小：20万/22.5万行的表数据量仅约620MB，BigQuery分布式引擎的调度、资源启动等固定开销占比远高于连接本身的成本差异，导致两种连接的耗时/计费差异被掩盖。
• 数据高度可压缩：你使用REPEAT(SHA512(...), 10)生成的payload是高度重复的固定字符串，BigQuery的列存储压缩算法会大幅降低实际读取的字节数，抹平了哈希连接所需的shuffle数据量带来的成本差异。
• 连接结果集接近：由于是等值连接a.id = b.id，且b表是id范围过滤，连接后的结果集大小与b表一致（20万/22.5万行），后续处理的成本差异极小。

是否需要将连接表控制在~150MB的广播连接阈值内？ #

不需要严格卡这个阈值，核心是看广播成本与哈希连接的shuffle成本的对比：

• 当小表远小于阈值时：广播连接无需shuffle数据，直接将小表分发到所有worker节点，成本远低于哈希连接，这时候尽量保持小表在阈值内更划算。
• 当小表接近阈值时：两种连接的成本差异会变得极小（如你的测试结果），BigQuery优化器会自动选择最优方案，无需刻意调整。
• 当小表远超阈值时：哈希连接的shuffle数据量会急剧上升，此时应通过过滤、聚合、预计算等方式缩小小表规模，尽量触发广播连接。

注：BigQuery的广播阈值并非固定的150MB，会根据集群资源、数据类型等动态调整，官方推荐的经验值是小表不超过1GB。

如何生成合成数据体现两种连接的性能差异？ #

可以通过以下方式调整测试数据和场景，放大两种连接的差异：

1. 大幅提升数据规模
   • 将主表a的行数提升至1亿甚至10亿行，小表b分别设置为100万行（触发广播）和500万行（触发哈希连接），此时shuffle数据量的差异会被显著放大。
   • 示例建表语句：

     CREATE TABLE test.test_join_large AS
     SELECT
       id AS id,
       GENERATE_UUID() AS payload  -- 生成不可压缩的随机字符串
     FROM
       UNNEST(GENERATE_ARRAY(1, 100000000)) AS id
     

2. 设计不可压缩的payload
   • 避免使用重复字符串，改用GENERATE_UUID()、RAND_STR(1000)等生成高基数、无重复的随机数据，让实际读取字节数接近理论值，凸显IO成本差异。
3. 调整连接场景
   • 使用非等值连接（如a.id > b.id）或低匹配率的连接条件，让哈希连接需要shuffle更多数据，放大成本差异。
   • 在连接后添加复杂计算（如分组聚合、字符串正则匹配），放大两种连接在计算资源消耗上的差异。
4. 增加迭代次数
   • 将测试迭代次数提升至100次以上，减少偶然因素的影响，让平均耗时的差异更显著。

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