主页 / dba / 问题 / 251295
Accepted
jericzech
Asked: 2019-10-17 12:31:37 +0800 CST

在 WHERE 条件下变量时不使用过滤索引

  • 772

为什么 MS SQL Server 在这种情况下拒绝使用支持的过滤索引？

-- demo data
CREATE TABLE #Test (
    ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY
    ,Col1 NVARCHAR(36) NOT NULL DEFAULT NEWID()
    ,Col2 NVARCHAR(20) NOT NULL DEFAULT N''  -- !!
    );

WITH
    L0   AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
    L1   AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
    L2   AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
    L3   AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
    L4   AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
    L5   AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)
INSERT INTO #Test(Col2)
SELECT TOP 100000 N''
FROM Nums;

INSERT INTO #Test(Col2)
VALUES(N'ABC');

-- FILTERED index to support filter predicate of a query
CREATE NONCLUSTERED INDEX IX_Test_Col2_filtered ON #Test (Col2 ASC) WHERE Col2 <> N'';

-- just checking statistics
DBCC SHOW_STATISTICS('#Test', 'IX_Test_Col2_filtered')

-- condition on variable = index scan :-(
DECLARE @Filter NVARCHAR(20) = N'ABC'

SELECT Col1
FROM #Test
WHERE Col2 = @Filter
    AND Col2 <> N'';

在此处输入图像描述

使用文字时，一切都按预期进行。

-- condition on literal value - index seek + key lookup :-)
SELECT Col1
FROM #Test
WHERE Col2 = N'ABC';

在此处输入图像描述

sql-server execution-plan

3 个回答

  1. Best Answer

    过滤索引不能使用变量/参数，除非您使用动态 SQL 构建查询，以便查询最终使用文字执行。

    一篇关于该主题的好文章是Jeremiah Peschka 的Filtered Indexes and Dynamic SQL。

    或者

    正如 Martin 建议的那样，您可以添加WITH (RECOMPILE)到查询中，但如果不了解潜在影响，则不应使用（请参阅重新编译提示和执行计划缓存）。

    • 2 
  2. DBCC SHOW_STATISTICS('Test', 'IX_Test_Col2_filtered')

    当您在过滤器中使用常量值时，请Optimizer针对该值制定计划。由于Optimizer知道该parameter值，它使用Statistics Histogram来估计查询可以返回的记录数。

    在where条件下使用局部变量时，Optimizer为“”制定计划OPTIMIZE FOR UNKNOWN。它不使用Statistics Histogram，它使用的信息Density Vector。

    `All Density* Number of rows` which is equal to `1*100001`

    所以要读取的行数将是100001。

    正如我们在成本基础优化中所知道的，Optimizer会迅速选择具有成本效益的计划。

    所以选择索引对于Optimizer.

    如果你Index Hint那么它将使用Index Seek但成本会更多。

    优化器制定计划，OPTIMIZE FOR UNKNOWN因为当您下次更改值时，local variable它将重用相同的计划。

    在同样的情况下它是有益的，在某些情况下它会损害性能。OPTIMIZE FOR UNKNOWN假设是基于计算的 All Density * Number of rows。

    现在我创建 Proc ，

    Create Proc TestProc
@Filter NVARCHAR(20)
as

Begin

SELECT Col1
FROM #Test
WHERE Col2 = @Filter
    AND Col2 <> N'';

End

Exec TestProc N'ABC'

    验证计划，计划与(dynamic)sp_executesql.

    我们看到Index seek和key look up。

    No of Execution=1
Estimated Number of rows=1

when you write dynamic sql,

DECLARE @Sql NVARCHAR(200) = ''
DECLARE @Filter NVARCHAR(20) = N'ABC'

set @Sql='SELECT Col1
FROM Test
WHERE  Col2 <> N''''
AND Col2 = @Filter1';

    print @Sql
    execute sp_executesql @Sql,N'@Filter1 NVARCHAR(20)',@Filter

    sp_executesql是一个 Proc 。变量@Filter1的行为与 Proc 的参数完全相同。 sp_executesql没有使用@Filter.

    因此optimizer，为该参数制定计划。当您传递一些其他值时，它将重用相同的计划。这称为Parameter sniffing.

    所以基本上我说的都是对的。

    • 0

  3. 另一种模式，将要查找的变量放入临时表并加入，尝试以下执行计划和统计信息

    DECLARE @Filter NVARCHAR(20) = N'ABC'
DECLARE @FilterT TABLE (
    f nvarchar(36)
)
insert into @FilterT select N'ABC'

DROP TABLE IF EXISTS #FilterT
CREATE table #FilterT (
    f nvarchar(36)
)
insert into #FilterT select N'ABC'

SELECT Col1
FROM #Test
WHERE Col2 = @Filter
    AND Col2 <> N'';

SELECT Col1
FROM #Test
WHERE Col2 = N'ABC';

select col1
from #Test
inner join @FilterT
    on #Test.Col2 = [@FilterT].f
    AND Col2 <> N'';


select col1
from #Test
inner join #FilterT
    on #Test.Col2 = [#FilterT].f
    AND Col2 <> N'';
    • 0

相关问题