sql-server – 当WHERE子句包含参数化值时

那么回答你的问题为什么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添加值为1-10的1000行,但是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,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％.

由于查询在使用变量时不知道将匹配多少行,因此最简单的方法是找出总行数,并将其除以列中不同值的总数,所以在这个例子中mssql 查询子句,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',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)的问题在于这意味着您无法利用缓存的查询计划,因此每次编译查询都需要额外的成本.

（编辑：通辽站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!