如何在BigQuery中优化跨表查询以减少HTTPArchive大表的数据处理量？

你的问题很典型——当用小表过滤大表时，错误的写法会导致BigQuery扫描整个大表的海量数据，而我们的目标是只扫描大表中与小表匹配的部分。

为什么你的原查询效率低？ #

你原来的CTE写法会先完整扫描大表的page和body字段（20GB），提取每个page的host后，再和小表做JOIN过滤。这就相当于先把所有数据都拉出来，再挑需要的部分，完全没利用小表的过滤作用提前缩小范围。

优化方案：提前用小表过滤大表 #

我们可以用IN子查询或者EXISTS半连接，让BigQuery先获取小表的域名列表，然后只扫描大表中page的host匹配这些域名的记录，这样能大幅减少扫描的数据量。

方案1：使用IN子查询

SELECT 
  NET.HOST(page) AS dhost, 
  body
FROM `httparchive.sample_data_2020.response_bodies_desktop_10k`
WHERE NET.HOST(page) IN (SELECT host FROM `webhistory.dom100.100domains`)

方案2：使用EXISTS半连接（推荐，性能更优）

EXISTS是半连接逻辑，只要小表中存在匹配的host，就返回大表的这条记录，不需要处理所有匹配项，在大表数据量极大时性能更好：

SELECT 
  NET.HOST(page) AS dhost, 
  body
FROM `httparchive.sample_data_2020.response_bodies_desktop_10k` AS main_table
WHERE EXISTS (
  SELECT 1 
  FROM `webhistory.dom100.100domains` AS domain_list
  WHERE domain_list.host = NET.HOST(main_table.page)
)

额外注意事项 #

• 统一域名格式：确保小表的host和NET.HOST(page)的输出格式一致（比如是否带www、大小写）。如果有不一致的情况，可以用LOWER()统一转换：

  WHERE LOWER(domain_list.host) = LOWER(NET.HOST(main_table.page))
  

• 小表去重：如果小表存在重复的host，可以先去重，减少匹配时的计算量：

  WHERE NET.HOST(page) IN (SELECT DISTINCT host FROM `webhistory.dom100.100domains`)
  

这些写法会让BigQuery的查询优化器优先利用小表的条件筛选大表，只扫描符合条件的记录，从而把数据扫描量从20GB降到极小的规模，完全符合你在真实HTTPArchive数据中使用的需求。

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