WHERE条件为何意外大幅降低SQL查询性能？

核心原因 #

添加WHERE t1.Id1 = @id后查询计划突变，本质是两个因素共同作用的结果：

1. FORCE ORDER约束+小数据集的优化倾向：WHERE条件过滤后t1的数据集大幅缩小，SQL Server优化器默认认为小数据集作为驱动表时，嵌套循环连接的开销更低。但你的连接条件是t1.Id2 LIKE t2.Id2 + '%'——这种动态前缀匹配无法利用t2的(Id1, Id2)索引做高效查找，嵌套循环下每一行t1数据都要扫描t2中Id1=@id的所有行并做字符串匹配，数据量大时性能直接崩盘。
2. HASH连接的适配限制：HASH连接要求连接条件是可哈希的等值逻辑，但t2.Id2 + '%'是连接时动态生成的模式，优化器无法为这种动态模式匹配构建有效的哈希表。再加上FORCE ORDER强制t1先被访问，而t1过滤后的数据量太小，优化器会直接放弃HASH连接的尝试。

另外，合并连接也不适用——合并连接需要两边数据集按连接键排序，而你的条件是前缀匹配而非等值连接，无法转化为有序范围查询。

可行优化方案 #

1. 调整连接顺序，让t2作为驱动表

既然t1.Id1=@id，那t2.Id1必然也等于@id，可以把t2放在前面过滤，再用HASH连接匹配t1：

DECLARE @id INT = 1

SELECT * 
FROM  #test2 t2
INNER HASH JOIN #test1 t1 
    ON t1.Id1 = t2.Id1
    AND t1.Id2 LIKE t2.Id2 + '%'
WHERE t2.Id1 = @id 
OPTION (FORCE ORDER)

这样t2先被过滤出Id1=@id的行，构建哈希表后，t1可以通过(Id1, Id2)索引快速定位Id1=@id的行，再和哈希表中的t2.Id2做前缀匹配，性能会大幅提升。

2. 预处理前缀，将匹配转化为等值连接

如果业务允许，可以预先生成t2中每个Id2的所有前缀，存储到辅助表中，把前缀匹配转化为等值连接：

-- 示例：生成#test2的前缀表
CREATE TABLE #test2_prefixes (
    Id1 INT,
    Id2 NVARCHAR(256),
    Prefix NVARCHAR(256)
)

-- 根据Id2的分隔符格式生成所有前缀（这里以|为分隔符）
INSERT INTO #test2_prefixes (Id1, Id2, Prefix)
SELECT 
    t2.Id1,
    t2.Id2,
    STRING_AGG(s.value, '|') WITHIN GROUP (ORDER BY s.ordinal)
FROM #test2 t2
CROSS APPLY STRING_SPLIT(t2.Id2, '|', 1) s
GROUP BY t2.Id1, t2.Id2, s.ordinal

-- 改为等值连接查询
SELECT t1.*, t2.*
FROM #test1 t1
INNER HASH JOIN #test2_prefixes tp 
    ON t1.Id1 = tp.Id1
    AND t1.Id2 LIKE tp.Prefix + '%'
INNER JOIN #test2 t2 ON tp.Id1 = t2.Id1 AND tp.Id2 = t2.Id2
WHERE t1.Id1 = @id

这种方式把前缀匹配转化为更易优化的逻辑，完全适配HASH/合并连接，性能最优，但需要维护前缀表的更新。

3. 使用APPLY结合索引范围扫描

如果不想改连接顺序，可以尝试用APPLY配合索引的范围过滤，减少嵌套循环中的扫描行数：

DECLARE @id INT = 1

SELECT * 
FROM #test1 t1
CROSS APPLY (
    SELECT * 
    FROM #test2 t2
    WHERE t2.Id1 = t1.Id1
      -- 利用(Id1, Id2)索引的有序性，先过滤出可能的前缀范围
      AND t2.Id2 <= t1.Id2
      AND t1.Id2 LIKE t2.Id2 + '%'
) t2
WHERE t1.Id1 = @id

这里t2.Id2 <= t1.Id2会利用索引做范围扫描，减少需要做LIKE匹配的行数，比直接全扫t2的Id1=@id数据要好。

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