BigQuery按表大小排序的朴素连接与性能对比及优化疑问

我正在学习BigQuery，探索公共数据集bigquery-public-data.uspto_oce_litigation时，看到BigQuery官方文档的连接优化建议：

最佳实践是将行数最多的表放在首位，其次是行数最少的表，其余表按规模从大到小排列。

但严格遵循该建议编写的查询（如下）无法在2分钟内完成：

WITH
  docs_and_cases AS (
  SELECT
    cases.case_name,
    cases.court_name,
    docs.case_row_id,
    docs.short_description
  FROM
    `bigquery-public-data.uspto_oce_litigation.documents` AS docs
  JOIN
    `bigquery-public-data.uspto_oce_litigation.cases` AS cases
  ON
    docs.case_row_id = docs.case_row_id
  WHERE
    docs.short_description IS NOT NULL
    AND cases.case_name IS NOT NULL
    AND docs.upload_date IS NOT NULL )
SELECT
  docs_and_cases.case_row_id,
  attorneys.name,
  attorneys.contactinfo,
  docs_and_cases.court_name,
  docs_and_cases.short_description,
FROM
  docs_and_cases
JOIN
  `bigquery-public-data.uspto_oce_litigation.attorneys` AS attorneys
ON
  docs_and_cases.case_row_id = attorneys.case_row_id

而违背该规则的查询（如下）仅需20秒即可完成：

WITH
  cases_and_attorneys AS (
  SELECT
    cases.case_row_id,
    cases.case_name,
    cases.court_name,
    attorneys.name,
    attorneys.contactinfo
  FROM
    `bigquery-public-data.uspto_oce_litigation.cases` AS cases
  JOIN
    `bigquery-public-data.uspto_oce_litigation.attorneys` AS attorneys
  ON
    cases.case_row_id = attorneys.case_row_id
  WHERE
    cases.case_name IS NOT NULL )
SELECT
  cases_and_attorneys.case_row_id,
  cases_and_attorneys.name,
  cases_and_attorneys.contactinfo,
  court_name,
  short_description
FROM
  `bigquery-public-data.uspto_oce_litigation.documents` AS docs
JOIN
  cases_and_attorneys
ON
  docs.case_row_id = cases_and_attorneys.case_row_id
WHERE
  short_description IS NOT NULL
  AND upload_date IS NOT NULL

可总结的核心经验 #

• 先精简小表，再关联大表
  第二个查询先将小表cases与attorneys关联并过滤，生成的cases_and_attorneys结果集远小于原大表documents。后续用这个精简后的小数据集关联大表，大幅减少了关联时的数据比对量；而第一个查询先处理最大的documents表，即使有过滤，初始处理的数据量依然庞大。

• 关注表的关联基数，而非仅看总大小
  cases和attorneys是主从关系，关联后不会出现数据爆炸；但documents是案件的多文档表，单案件对应多条记录，先关联它会生成大量中间数据，后续关联attorneys时需要处理的行数成倍增加。要重点看关联字段的重复度，判断关联后数据量的变化趋势。

• 过滤条件要尽早执行
  第二个查询中cases.case_name IS NOT NULL直接在小表cases上过滤，提前剔除无效数据；第一个查询的同条件是在大表关联后生效，相当于先做了大量无效数据的关联再过滤，浪费算力。

• 用WITH子句提前压缩数据集
  合理利用WITH子句完成小表的关联与过滤，生成精简的中间结果，本质是缩小后续关联的数据集规模，这比单纯调整表顺序的优化效果更显著。

总结 #

官方的表大小排序是通用准则，但实际场景中必须结合表的关联关系、过滤时机、数据重复度调整。核心优化思路是尽可能早地减少需要处理的数据量，而非机械遵循表大小顺序。

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