单条查询快但关联后极慢？SQL Server查询性能优化求助

问题场景 #

以下两条SQL在SQL Server中单独执行均耗时不到1秒：

1. 查询first表中id=21的记录：

SELECT T1.id
FROM first AS T1
WHERE T1.id = 21

2. 从含5300万条记录的second表中，通过索引IX_second查询id=21且满足b=1、c=0、d=0、e=0的TOP 1记录：

SELECT TOP 1 T2.value
FROM second AS T2 WITH(INDEX(IX_second))
WHERE T2.id = 21 
  AND T2.b = 1 
  AND T2.c = 0 
  AND T2.d = 0 
  AND T2.e = 0
ORDER BY T2.id, T2.b, T2.c, T2.d, T2.e, T2.timestamp DESC

但将内部查询的常量21替换为T1.id，改写为关联查询后，执行耗时超80秒：

SELECT T1.id, T3.value
FROM first AS T1
JOIN second AS T3 ON T3.id IN (SELECT TOP 1 T2.id
                               FROM second AS T2 WITH(INDEX(IX_second))
                               WHERE T2.id = T1.id 
                                 AND T2.b = 1 
                                 AND T2.c = 0 
                                 AND T2.d = 0 
                                 AND T2.e = 0
                               ORDER BY T2.id, T2.b, T2.c, T2.d, T2.e, T2.timestamp DESC)
 WHERE T1.id = 21

耗时过长的原因 #

1. 关联子查询执行路径低效：尽管外层仅筛选出1条记录，但原写法的IN关联子查询会被SQL Server视为逐行执行的关联逻辑，无法像单独查询那样直接利用索引快速定位，触发了不必要的执行流程。
2. 冗余表访问逻辑：原查询先通过子查询获取T2.id，再关联T3.id获取value，相当于重复访问second表，额外增加IO开销。
3. 索引提示未有效应用：单独查询时的WITH(INDEX(IX_second))能强制走索引，但在关联子查询场景下，优化器可能因关联逻辑限制，无法正确应用该提示，导致未走预期的高效索引扫描。
4. 执行计划预估偏差：优化器对关联子查询的行数预估错误，选择了不合适的连接方式（如嵌套循环未有效利用索引），进一步拉低执行效率。

优化方案 #

方案1：改写为SELECT子句关联子查询 #

直接在SELECT子句中返回目标值，避免冗余JOIN：

SELECT T1.id, 
       (SELECT TOP 1 T2.value
        FROM second AS T2 WITH(INDEX(IX_second))
        WHERE T2.id = T1.id 
          AND T2.b = 1 
          AND T2.c = 0 
          AND T2.d = 0 
          AND T2.e = 0
        ORDER BY T2.id, T2.b, T2.c, T2.d, T2.e, T2.timestamp DESC) AS value
FROM first AS T1
WHERE T1.id = 21

该写法与单独查询的执行逻辑更接近，优化器易识别并应用索引。

方案2：使用CROSS APPLY替代IN子查询 #

CROSS APPLY是处理“每行对应TOP N”场景的高效写法，优化器对其支持更友好：

SELECT T1.id, T2.value
FROM first AS T1
CROSS APPLY (
    SELECT TOP 1 value
    FROM second AS T2 WITH(INDEX(IX_second))
    WHERE T2.id = T1.id 
      AND T2.b = 1 
      AND T2.c = 0 
      AND T2.d = 0 
      AND T2.e = 0
    ORDER BY T2.id, T2.b, T2.c, T2.d, T2.e, T2.timestamp DESC
) AS T2
WHERE T1.id = 21

CROSS APPLY会明确触发对T1每行的单次子查询执行，直接复用单独查询的高效索引路径。

方案3：优化为覆盖索引 #

确保IX_second是覆盖索引，包含查询所需的value列，避免回表操作：

CREATE NONCLUSTERED INDEX IX_second ON second (id, b, c, d, e, timestamp DESC)
INCLUDE (value)

覆盖索引可以让查询直接从索引中获取所有所需数据，大幅减少IO开销。

方案4：移除不必要的索引提示（可选） #

若改写后的查询中优化器能自动选择正确索引，可移除WITH(INDEX(IX_second))提示，让优化器自主选择最优执行计划，避免强制索引带来的潜在适配问题。

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