相似SQL查询执行计划与性能差异原因及优化方案

为什么直接子查询会采用Nested Loop连接？ #

SQL Server查询优化器对两种写法的处理逻辑存在本质差异：

• 变量赋值写法：优化器生成执行计划时，无法预知@id_ciudad的实际值，只能基于统计信息的默认基数估计（比如假设变量对应值匹配中等规模数据行），通常会选择哈希连接或合并连接这类适配大数据量的执行计划。
• 直接等值子查询写法：当子查询是WHERE id_ciudad = (SELECT ...)这类单值限定子查询时，优化器会先判断子查询的返回特性：如果Clientes_Ciudad表的ciudad列有唯一约束，或统计信息显示N'Ciudad_00199'对应唯一的id_ciudad，优化器会将子查询折叠为等价的连接逻辑，并且认定子查询结果仅1行。此时嵌套循环连接的成本估算会被判定为极低——优化器默认用单个值去Clientes表做查找的效率，要高于哈希/合并连接，但在你的场景中Clientes有1000万行，若id_ciudad列无覆盖索引，嵌套循环会退化为逐行匹配，反而导致性能下降。

如何避免Nested Loop执行计划？ #

以下是几种直接有效的解决方案：

1. 使用查询提示强制指定连接类型 #

直接给查询添加OPTION提示，强制优化器采用哈希连接或合并连接：

SELECT * 
FROM Clientes 
WHERE id_ciudad = (SELECT id_ciudad FROM Clientes_Ciudad WHERE ciudad = N'Ciudad_00199')
OPTION (HASH JOIN); -- 也可替换为 OPTION (MERGE JOIN)

2. 用变量包装子查询（即你已测试的方式） #

通过先赋值变量再查询的方式，让优化器无法提前执行子查询折叠，只能基于统计信息的平均分布选择合适计划：

DECLARE @id_ciudad INT;
SELECT @id_ciudad = id_ciudad FROM Clientes_Ciudad WHERE ciudad = N'Ciudad_00199';
SELECT * FROM Clientes WHERE id_ciudad = @id_ciudad;

3. 更新统计信息 #

若Clientes_Ciudad的统计信息过时，优化器可能错误判断子查询返回行数（比如误判为多行），进而选错执行计划。可通过以下语句更新统计信息：

UPDATE STATISTICS Clientes_Ciudad WITH FULLSCAN;

4. 阻止子查询折叠 #

给子查询添加TOP 1（因子查询本身返回单值，不影响结果），让优化器无法将其折叠为连接逻辑：

SELECT * 
FROM Clientes 
WHERE id_ciudad = (SELECT TOP 1 id_ciudad FROM Clientes_Ciudad WHERE ciudad = N'Ciudad_00199');

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