为何T-SQL查询中用未使用的IN替换等于判断会导致性能骤降？

问题背景 #

原有存储过程逻辑是检查myTable中是否存在满足任一字段匹配对应参数的记录，所有字段和参数原均为整数类型，常规仅一个参数非NULL。为支持传入列表，将@param3改为nvarchar(max)，通过自定义拆分函数fnSplit处理后用IN语句匹配field3。修改后出现以下异常：

• 仅当@param3为NULL时，查询耗时从亚秒级飙升至超1分钟；
• 注释掉IN语句后恢复正常速度；
• 移除IF语句直接查询COUNT(*)，耗时约10秒，远快于带IF的版本。

原有代码（伪代码） #

DECLARE @HasResults BIT = 0;

IF 
    (SELECT COUNT(*) FROM myTable t
    WHERE
        t.field1 = @param1
        OR t.field2 = @param2
        OR t.field3 = @param3
        OR t.field4 = @param4) > 0
    SET @HasResults = 1

SELECT @HasResults AS HasResults

修改后代码 #

DECLARE @HasResults BIT = 0;

IF 
    (SELECT COUNT(*) FROM myTable t
    WHERE
        t.field1 = @param1
        OR t.field2 = @param2
        OR t.field3 IN (select ID from fnSplit(@param3))
        OR t.field4 = @param4) > 0
    SET @HasResults = 1

SELECT @HasResults AS HasResults 

自定义拆分函数fnSplit代码 #

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(MAX) 
  , @sDelimiter VARCHAR(MAX) = ','
) RETURNS @List TABLE (item VARCHAR(MAX))

BEGIN
DECLARE @sItem VARCHAR(MAX)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT  
 @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList
RETURN
END
GO

问题原因解析 #

1. @param3为NULL时IN语句变慢的原因 #

当@param3为NULL时，fnSplit函数的输入参数@sInputList是NULL：

• 函数内的WHILE循环条件CHARINDEX(@sDelimiter, NULL)返回NULL，不满足<>0的条件，循环直接跳过；
• 后续LEN(@sInputList)同样返回NULL，不满足>0的条件，最终返回的@List是空表。

此时WHERE条件中的t.field3 IN (空表)等价于永远为假的条件，但SQL Server查询优化器可能无法直接识别这一点，反而执行以下低效操作：

• 对myTable进行全表扫描，逐行尝试与空表做匹配（尽管结果必然为假）；
• field3是整数类型，而fnSplit返回的是VARCHAR(MAX)类型，存在隐式类型转换，导致field3上的索引无法被利用，进一步加剧性能损耗。

2. IF语句导致性能骤降的原因 #

IF语句的存在会干扰查询优化器的执行计划生成：

• 参数嗅探问题：存储过程第一次执行时如果使用的是有值的@param3，优化器会生成适合该场景的执行计划（比如利用field3的索引）。当后续@param3为NULL时，存储过程可能复用这个不合适的执行计划，导致全表扫描等低效操作；
• COUNT(*)的优化差异：IF语句中需要判断COUNT(*) >0，优化器可能选择逐行计数的方式；而直接执行SELECT COUNT(*)时，优化器可以利用更高效的聚合逻辑（比如通过索引快速统计行数），因此耗时更短。

解决方案 #

方案1：添加NULL判断，跳过无效的IN条件 #

修改WHERE条件，当@param3为NULL时直接忽略该分支，让优化器明确识别该条件为假，避免不必要的计算：

DECLARE @HasResults BIT = 0;

IF 
    (SELECT COUNT(*) FROM myTable t
    WHERE
        t.field1 = @param1
        OR t.field2 = @param2
        OR (@param3 IS NOT NULL AND t.field3 IN (SELECT CAST(item AS INT) FROM fnSplit(@param3)))
        OR t.field4 = @param4) > 0
    SET @HasResults = 1

SELECT @HasResults AS HasResults 

注意：添加CAST(item AS INT)显式转换拆分后的字符串为整数，避免隐式转换导致索引失效。

方案2：改用STRING_SPLIT（SQL Server 2016+） #

如果使用SQL Server 2016及以上版本，建议替换自定义拆分函数为内置的STRING_SPLIT，性能更优且支持类型转换：

OR (@param3 IS NOT NULL AND t.field3 IN (SELECT CAST(value AS INT) FROM STRING_SPLIT(@param3, ',')))

方案3：提前处理空参数 #

在进入查询前判断@param3是否为NULL，直接设置对应的条件分支，减少优化器的判断负担：

DECLARE @HasResults BIT = 0;
DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N'
SELECT @HasResults = CASE WHEN EXISTS(
    SELECT 1 FROM myTable t
    WHERE
        t.field1 = @param1
        OR t.field2 = @param2
        ' + CASE WHEN @param3 IS NOT NULL THEN N'OR t.field3 IN (SELECT CAST(item AS INT) FROM fnSplit(@param3))' ELSE N'' END + N'
        OR t.field4 = @param4
) THEN 1 ELSE 0 END';

EXEC sp_executesql @Sql, 
    N'@param1 INT, @param2 INT, @param3 NVARCHAR(MAX), @param4 INT, @HasResults BIT OUTPUT',
    @param1 = @param1, @param2 = @param2, @param3 = @param3, @param4 = @param4, @HasResults = @HasResults OUTPUT;

SELECT @HasResults AS HasResults;

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