BigQuery中两种SQL查询的效率对比及性能差异疑问

我正在学习SQL，练习引导示例时会先自行编写查询语句再参考课程解法。本次使用BigQuery公开数据集bigquery-public-data.new_york_citibike，需求是找出骑行时长远超起始站点平均时长的Citibike记录。

课程提供的相关子查询写法 #

SELECT
  starttime,
  start_station_id,
  tripduration,
  (
    SELECT ROUND(AVG(tripduration),2)
    FROM bigquery-public-data.new_york_citibike.citibike_trips
    WHERE start_station_id = outer_trips.start_station_id
  ) AS avg_duration_for_station,
  ROUND(tripduration - (
    SELECT AVG(tripduration)
    FROM bigquery-public-data.new_york_citibike.citibike_trips
    WHERE start_station_id = outer_trips.start_station_id), 2) AS difference_from_avg
FROM bigquery-public-data.new_york_citibike.citibike_trips AS outer_trips
ORDER BY difference_from_avg DESC
LIMIT 25

我自行编写的JOIN子查询写法 #

SELECT
  starttime,
  start_station_id,
  tripduration,
  station_averages.station_average AS station_avg,
  ROUND (tripduration - station_averages.station_average,2) AS diff_from_station_avg
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  JOIN (
      SELECT
        start_station_id AS station_id,
        ROUND(AVG(tripduration),2) as station_average
      FROM `bigquery-public-data.new_york_citibike.citibike_trips`
      GROUP BY station_id
    ) AS station_averages
  ON start_station_id = station_averages.station_id
ORDER BY 5 DESC
LIMIT 25

测试结果与疑问 #

我原以为自己的语句更高效，因为仅计算一次各站点平均时长，而课程语句会为每个站点的每条记录重复计算平均。但测试发现：

• LIMIT为25、250、2500等较小值时，课程语句比我的快约1秒；
• 当LIMIT增至250万时，我的语句耗时17秒，课程语句耗时18秒。

现请教：

• 我的效率判断是否正确？
• 为何两者性能随LIMIT缩放不同？
• 为何LIMIT会影响耗时，毕竟排序前需计算所有结果？

解答 #

1. 你的效率判断仅在大结果集场景下成立
   你认为JOIN方式只计算一次站点平均是对的，但BigQuery的查询优化器对相关子查询做了关键优化——它不会真的为每条记录重复计算平均，而是会预先计算所有站点的平均值并缓存，这和你的JOIN方式本质上做了同样的预计算。但两种实现的执行计划细节不同，导致在不同LIMIT下表现有差异。

2. 小LIMIT时相关子查询更快的原因
   当LIMIT很小（比如25），BigQuery的优化器会触发提前终止计算逻辑：它不需要扫描全表，而是可以在找到Top N条符合条件的记录后就停止。相关子查询的执行计划可以配合这种优化——它可以先按tripduration降序扫描数据，每拿到一条记录就计算其与站点平均的差值，一旦凑够25条最大差值的记录就停止，不需要处理全表。而你的JOIN方式需要先完成全表的JOIN操作（先计算所有站点平均，再关联所有记录），之后才能排序取Top N，所以在小LIMIT时会更慢。

3. 大LIMIT时JOIN方式更优的原因
   当LIMIT增大到接近全表规模（比如250万），提前终止的优化失效了，此时两种方式都需要处理大部分数据。你的JOIN方式只做一次站点平均计算，而相关子查询虽然也做了预计算，但执行计划的额外逻辑开销（比如多次子查询的封装处理）会稍微增加耗时，所以此时你的语句更快。

4. LIMIT影响耗时的原因
   你以为排序前需要计算所有结果，但BigQuery的Top-N排序优化可以避免全表排序。当你使用ORDER BY ... LIMIT N时，优化器会使用类似堆排序的算法，在扫描数据的过程中维护一个大小为N的堆，只保留当前最大的N条记录，不需要对全表数据进行完整排序。这种优化在LIMIT较小时效果非常明显，能大幅减少计算和IO开销，所以LIMIT越小，耗时越低。

内容的提问来源于stack exchange，提问作者G Tony Jacobs