SQL查询使用outer apply关联大表无数据时执行极慢问题

问题根因 #

性能差异核心是OUTER APPLY关联mInstLocations的执行逻辑在匹配/不匹配数据场景下的开销差导致的，具体有两个核心原因：

• mInstLocations表现有索引IX_InstrumentId_mInstLocationId未覆盖查询所需的Lat、Long字段，每次查询需要回表查找对应数据，当没有匹配的InstrumentId时，数据库会遍历完该索引下所有对应前缀的条目确认无匹配数据，1100万行的大表下这个遍历开销会被外层符合条件的Instrument行数放大。
• 强制指定了索引hintWITH ( index(IX_InstrumentId_mInstLocationId),NOLOCK)，限制了优化器的可选执行路径，当无匹配数据时优化器无法选择更高效的查找逻辑。

优化方案 #

1. 重构mInstLocations的覆盖索引（优先级最高） #

把Lat和Long加入索引的包含列，避免回表开销，创建语句如下：

CREATE NONCLUSTERED INDEX IX_InstrumentId_mInstLocationId_Incl_LatLong 
ON mInstLocations (InstrumentId, mInstLocationId DESC)
INCLUDE (Lat, Long)
-- 生产环境建议加WITH (ONLINE = ON)避免锁表，可根据数据库版本调整

创建完成后可以删除原来的旧索引IX_InstrumentId_mInstLocationId，避免索引冗余。

2. 移除不必要的索引hint #

去掉mInstLocations、InstrumentUpdatedLog、InstLocations三个表后面强制指定索引的hint，数据库优化器通常会自动选择最优索引，强制指定反而会在数据分布变化时出现性能退化。调整后的mLocations关联逻辑示例：

outer apply (
    SELECT TOP 1 Lat, Long 
    FROM mInstLocations WITH (NOLOCK) 
    WHERE mInstLocations.InstrumentId = dbo.Instruments.InstrumentId Order by mInstLocationId DESC
) mLocations

3. 可选优化：提前过滤符合条件的仪器数据 #

如果外层Instruments表筛选后符合Disabled != 1 AND OrganizationId = @OrganizationId的行数较多，可以先把符合条件的仪器数据存入临时表，再做关联，减少外层循环次数：

SELECT InstrumentId, InstrumentName, SiteId, DataFrequency 
INTO #Temp_Instruments
FROM Instruments 
WHERE Disabled != 1 AND OrganizationId = @OrganizationId

后续查询关联用#Temp_Instruments代替原Instruments表即可。

优化完成后无匹配数据的场景查询耗时会降到秒级，和有匹配数据的场景性能基本一致。

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