SQL Server中JOIN查询远慢于等价WHERE查询的原因排查

我有两个表test_table和timeseries_table：

• test_table包含Test_id列、测试运行的开始/结束时间（bigint类型）以及channel_id；
• timeseries_table包含每条观测的timestamp、测量值和channel_id。

我构造了两个逻辑等价的查询：

Query 1 #

SELECT * FROM timeseries_table 
WHERE (timestamp BETWEEN 1000 AND 2000) 
AND channel_id = 5

Query 2 #

SELECT * FROM timeseries_table A JOIN test_table B 
ON (A.timestamp BETWEEN B.start_time AND B.end_time) 
AND A.channel_id = B.channel_id
WHERE B.test_id = 8

其中查询test_id=8会从test_table返回唯一一行，其参数（start_time=1000、end_time=2000、channel_id=5）与Query 1完全一致。

Query 1耗时100ms，Query 2耗时6秒，单独查询test_table中test_id=8的行仅需4ms，且两个查询返回的行数和数据完全一致。

我原本预期JOIN查询速度与WHERE相近，请问导致这种巨大耗时差异的原因是什么？

导致这种性能差异的核心是数据库执行计划的差异，具体可拆解为以下几点：

• 谓词下推逻辑不同
  Query 1中，timestamp BETWEEN 1000 AND 2000和channel_id=5是直接作用于timeseries_table的过滤条件，数据库可直接利用timeseries_table上(channel_id, timestamp)的复合索引（若存在），快速定位符合条件的行，避免全表扫描。
  Query 2中，即便B.test_id=8仅返回一行，数据库可能未优先执行该过滤，而是先尝试将timeseries_table与整个test_table做JOIN，再过滤test_id=8的结果。这种情况下JOIN会处理大量无关数据，导致耗时剧增。即便数据库先查询test_table，若JOIN条件中的A.timestamp BETWEEN B.start_time AND B.end_time无法被优化为等价范围过滤，也无法有效利用索引。

• 索引利用效率差异
  Query 1的过滤条件是固定常量，数据库可精准匹配索引的范围查询逻辑。而Query 2的JOIN条件中，B.start_time和B.end_time来自另一表字段（即便最终是固定值），部分数据库优化器可能无法将其识别为常量，因此无法触发timeseries_table的索引范围扫描，只能全表扫描后再做JOIN匹配，大幅增加IO与计算量。

• 查询优化器的决策偏差
  数据库优化器基于统计信息估算执行成本。若test_table的统计信息过时，或优化器判定test_table行数较多，可能选择错误的执行顺序——比如先扫描timeseries_table全表，再逐行与test_table匹配，而非先获取test_id=8的单行数据，再用该数据过滤timeseries_table。

• 查看两个查询的执行计划（如MySQL用EXPLAIN，PostgreSQL用EXPLAIN ANALYZE），确认是否存在全表扫描、JOIN顺序错误的情况。
• 手动强制优化器优先执行test_table的过滤，可将Query 2改写为子查询形式：

  SELECT * FROM timeseries_table A
  JOIN (SELECT start_time, end_time, channel_id FROM test_table WHERE test_id=8) B
  ON A.timestamp BETWEEN B.start_time AND B.end_time AND A.channel_id = B.channel_id
  

  该写法明确告知优化器先获取test_table的单行数据，再用其过滤timeseries_table，通常能触发与Query 1一致的高效执行计划。
• 确保timeseries_table存在(channel_id, timestamp)复合索引，test_table存在test_id的主键或唯一索引。

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