sql-server - 当 WHERE 子句包含参数化值时，为什么 SQL Server 使用索引扫描而不是索引查找

Question

我们发现，如果 where 子句包含参数化值而不是字符串文字，则 SQL Server 将使用索引扫描而不是索引查找。

下面是一个例子:

SQL Server 在以下情况下执行索引扫描(where 子句中的参数)

declare @val1 nvarchar(40), @val2 nvarchar(40);
set @val1 = 'val1';
set @val2 = 'val2';

select 
    min(id) 
from 
    scor_inv_binaries 
where 
    col1 in (@val1, @val2) 
group by 
    col1

另一方面，以下查询执行索引查找:

select 
    min(id) 
from 
    scor_inv_binaries 
where 
    col1 in ('val1', 'val2') 
group by 
    col1

是否有人观察到类似的行为，以及他们如何修复此问题以确保查询执行索引查找而不是索引扫描？

我们无法使用forceseek表提示，因为SQL Sserver 2005支持forceseek。

我也更新了统计数据。非常感谢您的帮助。

Answer 1

好吧，回答你的问题为什么SQL Server要这样做，答案是查询不是按逻辑顺序编译的，每个语句都是根据自己的优点编译的，因此，当生成 select 语句的查询计划时，优化器不知道 @val1 和 @Val2 将分别变为“val1”和“val2”。

当 SQL Server 不知道该值时，它必须对该变量将在表中出现的次数做出最佳猜测，这有时会导致计划不理想。我的主要观点是，具有不同值的相同查询可以生成不同的计划。想象一下这个简单的例子:

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

CREATE TABLE #T (ID INT IDENTITY PRIMARY KEY, Val INT NOT NULL, Filler CHAR(1000) NULL);
INSERT #T (Val)
SELECT  TOP 991 1
FROM    sys.all_objects a
UNION ALL
SELECT  TOP 9 ROW_NUMBER() OVER(ORDER BY a.object_id) + 1
FROM    sys.all_objects a;

CREATE NONCLUSTERED INDEX IX_T__Val ON #T (Val);

我在这里所做的只是创建一个简单的表格，并为 val 列添加 1000 行值 1-10，但是 1 出现 991 次，其他 9 只出现一次。前提是这个查询:

SELECT  COUNT(Filler)
FROM    #T
WHERE   Val = 1;

扫描整个表会比使用索引进行查找，然后执行 991 次书签查找来获取 Filler 的值更有效，但是以下查询只有 1 行:

SELECT  COUNT(Filler)
FROM    #T
WHERE   Val = 2;

将更有效地进行索引查找，并且通过单个书签查找来获取 Filler 的值(并且运行这两个查询将批准这一点)

我非常确定搜索和书签查找的截止实际上会根据情况而变化，但它相当低。使用示例表，经过一些试验和错误，我发现在优化器通过索引查找进行全表扫描之前，我需要 Val 列有 38 行且值为 2书签查找:

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

DECLARE @I INT = 38;

CREATE TABLE #T (ID INT IDENTITY PRIMARY KEY, Val INT NOT NULL, Filler CHAR(1000) NULL);
INSERT #T (Val)
SELECT  TOP (991 - @i) 1
FROM    sys.all_objects a
UNION ALL
SELECT  TOP (@i) 2
FROM    sys.all_objects a
UNION ALL
SELECT  TOP 8 ROW_NUMBER() OVER(ORDER BY a.object_id) + 2
FROM    sys.all_objects a;

CREATE NONCLUSTERED INDEX IX_T__Val ON #T (Val);

SELECT  COUNT(Filler), COUNT(*)
FROM    #T
WHERE   Val = 2;

因此，对于此示例，限制为匹配行的 3.7%。

由于查询不知道当您使用变量时将匹配多少行，因此它必须猜测，最简单的方法是找出总行数，并将其除以列，因此在本例中，WHERE val = @Val 的估计行数为 1000/10 = 100，实际算法比这更复杂，但为了举例，这样做就可以了。因此，当我们查看执行计划时:

DECLARE @i INT = 2;
SELECT  COUNT(Filler)
FROM    #T
WHERE   Val = @i;

我们可以在这里看到(使用原始数据)估计行数为 100，但实际行数为 1。从前面的步骤我们知道，如果行数超过 38，优化器将选择聚集索引扫描通过索引查找，因此由于对行数的最佳猜测高于此值，因此对于未知变量的计划是聚集索引扫描。

为了进一步证明这一理论，如果我们创建一个包含 1000 行数字 1-27 均匀分布的表(因此估计行数约为 1000/27 = 37.037)

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

CREATE TABLE #T (ID INT IDENTITY PRIMARY KEY, Val INT NOT NULL, Filler CHAR(1000) NULL);
INSERT #T (Val)
SELECT  TOP 27 ROW_NUMBER() OVER(ORDER BY a.object_id)
FROM    sys.all_objects a;

INSERT #T (val)
SELECT  TOP 973 t1.Val
FROM    #T AS t1
        CROSS JOIN #T AS t2
        CROSS JOIN #T AS t3
ORDER BY t2.Val, t3.Val;

CREATE NONCLUSTERED INDEX IX_T__Val ON #T (Val);

然后再次运行查询，我们得到一个带有索引查找的计划:

DECLARE @i INT = 2;
SELECT  COUNT(Filler)
FROM    #T
WHERE   Val = @i;

所以希望这非常全面地涵盖了您制定该计划的原因。现在我想下一个问题是如何强制执行不同的计划，答案是，使用查询提示OPTION (RECOMPILE)，强制查询在执行时编译，当值参数已知。恢复到原始数据，其中 Val = 2 的最佳计划是查找，但使用变量会产生带有索引扫描的计划，我们可以运行:

DECLARE @i INT = 2;
SELECT  COUNT(Filler)
FROM    #T
WHERE   Val = @i;

GO

DECLARE @i INT = 2;
SELECT  COUNT(Filler)
FROM    #T
WHERE   Val = @i
OPTION (RECOMPILE);

我们可以看到后者使用索引查找和键查找，因为它在执行时检查了变量的值，并为该特定值选择了最合适的计划。 OPTION (RECOMPILE) 的问题在于，这意味着您无法利用缓存的查询计划，因此每次编译查询都会产生额外的成本。