优化长时运行SQL查询：解析相似查询性能差异原因

慢查询（耗时约25分钟，影响9,095,000行） #

select @TimeStamp = getdate()
select @TimeStamp
Use FSFruit;
select *,
 case when @intDaysToGrowInQ1 = 0 then 0 else (me1) end yme1,
 case when @intDaysToGrowInQ2 = 0 then 0 else (me1 + me2) end yme2,
 case when @intDaysToGrowInQ3 = 0 then 0 else (me1 + me2 + me3) end yme3,
 case when @intDaysToGrowInQ4 = 0 then 0 else (me1 + me2 + me3 + me4) end yme4
into #temp1
from mq_fruit
where fruit_suffix in ('A1', 'O1', 'S1') OR
balance_type in (select fruit_type from REF.dbo.fruit_type_tbl where fruit_type_group = 'TOT')

select @TimeStamp = getdate()
select @TimeStamp

快查询（耗时约3分钟，影响7,080,000行） #

select @TimeStamp = getdate()
select @TimeStamp

Use FSFruit;
select *,
 case when @intDaysToGrowInQ1 = 0 then 0 else (me1) end yme1,
 case when @intDaysToGrowInQ2 = 0 then 0 else (me1 + me2) end yme2,
 case when @intDaysToGrowInQ3 = 0 then 0 else (me1 + me2 + me3) end yme3,
 case when @intDaysToGrowInQ4 = 0 then 0 else (me1 + me2 + me3 + me4) end yme4
into #temp1
from mq_fruit
where fruit_suffix not in ('A1', 'O1', 'S1') and
fruit_type not in (select fruit_type from REF.dbo.fruit_type_tbl where fruit_type_group = 'TOT')

select @TimeStamp = getdate()
select @TimeStamp

已知条件 #

• mq_fruit和fruit_type_tbl均未创建任何索引
• 执行计划显示：慢查询采用嵌套循环+键查找组合，对mq_fruit全表扫描后执行大量重复键查找；快查询采用哈希匹配，避免了频繁的键查找操作。

性能差异原因 #

1. OR条件的执行计划局限性：SQL Server对OR连接的过滤条件优化能力有限，无法生成高效的索引利用计划。慢查询中OR导致数据库先对mq_fruit做全表扫描，再逐行验证两个条件，加上子查询的嵌套循环执行，引发大量I/O开销。
2. NOT IN + AND的过滤逻辑更高效：快查询的NOT IN组合AND条件，SQL Server可将两个过滤条件合并为单一逻辑，通过哈希匹配快速排除不符合条件的行，减少了数据处理量，同时避免了频繁的键查找。
3. 无索引的放大影响：两张表均无索引，全表扫描和子查询的执行成本被最大化。慢查询的OR条件无法通过索引快速定位目标数据，进一步拖慢了执行速度。

优化建议 #

1. 添加针对性索引 #

• 对mq_fruit创建复合索引（包含查询所需所有列，避免键查找开销）：

CREATE NONCLUSTERED INDEX IX_mq_fruit_fruit_suffix_balance_type 
ON mq_fruit (fruit_suffix, balance_type) 
INCLUDE (me1, me2, me3, me4, [其他需要返回的列]);

• 对REF.dbo.fruit_type_tbl创建索引（加速子查询过滤）：

CREATE NONCLUSTERED INDEX IX_fruit_type_tbl_group 
ON fruit_type_tbl (fruit_type_group) 
INCLUDE (fruit_type);

2. 重写OR条件为UNION ALL（避免重复行） #

将OR拆分为两个独立查询，用UNION ALL合并，避免低效的嵌套循环：

select @TimeStamp = getdate()
select @TimeStamp
Use FSFruit;

-- 匹配fruit_suffix条件的行
select *,
 case when @intDaysToGrowInQ1 = 0 then 0 else (me1) end yme1,
 case when @intDaysToGrowInQ2 = 0 then 0 else (me1 + me2) end yme2,
 case when @intDaysToGrowInQ3 = 0 then 0 else (me1 + me2 + me3) end yme3,
 case when @intDaysToGrowInQ4 = 0 then 0 else (me1 + me2 + me3 + me4) end yme4
into #temp1
from mq_fruit
where fruit_suffix in ('A1', 'O1', 'S1')

-- 匹配balance_type条件且未被第一个查询覆盖的行
union all
select *,
 case when @intDaysToGrowInQ1 = 0 then 0 else (me1) end yme1,
 case when @intDaysToGrowInQ2 = 0 then 0 else (me1 + me2) end yme2,
 case when @intDaysToGrowInQ3 = 0 then 0 else (me1 + me2 + me3) end yme3,
 case when @intDaysToGrowInQ4 = 0 then 0 else (me1 + me2 + me3 + me4) end yme4
from mq_fruit
where balance_type in (select fruit_type from REF.dbo.fruit_type_tbl where fruit_type_group = 'TOT')
and fruit_suffix not in ('A1', 'O1', 'S1')

select @TimeStamp = getdate()
select @TimeStamp

注：若存在重复行风险，将UNION ALL替换为UNION

3. 预存子查询结果 #

将子查询结果存入临时表，减少重复执行开销：

select @TimeStamp = getdate()
select @TimeStamp
Use FSFruit;

-- 预存子查询结果
select fruit_type into #temp_fruit_types 
from REF.dbo.fruit_type_tbl 
where fruit_type_group = 'TOT'

select *,
 case when @intDaysToGrowInQ1 = 0 then 0 else (me1) end yme1,
 case when @intDaysToGrowInQ2 = 0 then 0 else (me1 + me2) end yme2,
 case when @intDaysToGrowInQ3 = 0 then 0 else (me1 + me2 + me3) end yme3,
 case when @intDaysToGrowInQ4 = 0 then 0 else (me1 + me2 + me3 + me4) end yme4
into #temp1
from mq_fruit
where fruit_suffix in ('A1', 'O1', 'S1') OR
balance_type in (select fruit_type from #temp_fruit_types)

select @TimeStamp = getdate()
select @TimeStamp

4. 更新统计信息 #

确保表统计信息最新，帮助SQL Server生成最优执行计划：

UPDATE STATISTICS mq_fruit;
UPDATE STATISTICS REF.dbo.fruit_type_tbl;

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