主页 / dba / 问题 / 281781
Accepted
Greg
Asked: 2020-12-18 17:18:22 +0800 CST

设置变量时使用过滤索引

  • 772

我从其他问题和帖子中知道，当 SQL 编译查询计划时，如果保证每次查询运行时都能使用过滤索引，则它只能使用过滤索引。这意味着您不能在 where 子句中使用变量，因为有时它可能能够使用过滤后的索引，有时则不能。

解决此问题的一种方法是使用OPTION(RECOMPILE)，以便它可以使用它的次数，它将获得过滤后的索引。

做一些测试，我发现这个查询可以使用过滤索引（注意，我强制索引只是为了证明一点）：

SELECT MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

但是，如果我想将结果分配给一个变量，我很不走运：

SELECT @OutputVariable = MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

结果是：

消息 8622，级别 16，状态 1，第 15 行 由于此查询中定义的提示，查询处理器无法生成查询计划。在不指定任何提示且不使用 SET FORCEPLAN 的情况下重新提交查询。

当我不想将输出保存到变量时，查询可以清楚地使用过滤后的索引，因为它运行 find 。

我有办法将此查询重写为硬代码@variable以消除问题，但有人可以解释为什么第一个查询可以使用过滤索引，但第二个查询不能吗？

sql-server index

4 个回答

  1. Best Answer

    允许您使用带有RECOMPILE提示的过滤索引的优化称为“参数嵌入优化”。这是查询解析器用变量内的文字值替换变量引用的过程。

    请参阅 Paul White 的这篇文章，了解它在第二种情况下不起作用的原因：参数嗅探、嵌入和重新编译选项

    在一种情况下，使用OPTION (RECOMPILE)不会导致应用参数嵌入优化。如果语句分配给变量，则不嵌入参数值：

    • 12

  2. 这应该有效：

    declare @myVariable int
declare @variable int = 9
declare @OutputVariable date

declare @t table(MaxDate date)

insert into @t(MaxDate) 
SELECT MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

select @OutputVariable MaxDate from @t

    而且您始终可以将动态 SQL 与文字值一起使用，而不是尝试使用 OPTION (RECOMPILE) 将参数转换为文字。例如

    declare @variable int = 9
declare @OutputVariable date

declare @sql nvarchar(max) = concat(N'
SELECT @OutputVariable = MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = ', @variable)

exec sp_executesql @sql, N'@OutputVariable date out', @OutputVariable = @OutputVariable out
select @OutputVariable
    • 4

  3. 优化器无法生成计划的原因是它无法保证变量的值实际上会匹配过滤谓词。查询计划是预编译和存储的，在所有情况下都使用该索引是不安全的。不幸的是，优化器此时不包含仅在过滤器匹配的情况下分叉过滤索引的逻辑，这是一种耻辱。正如SQLPro所展示的，您可以自己做。

    这样做的一个问题是：过滤后的索引经常会根据 DBA 认为最好的方式发生变化。我成功使用的一个选项是缓存客户端过滤的索引谓词，并在此基础上动态构建查询，同时仍然通过变量。这避免了计划缓存膨胀和 SQL 注入的问题，因为查询仍然是参数化的，除非索引更改，否则不会更改。您也可以sp_executesql在存储过程中执行此操作。

    要获取过滤谓词，请运行以下查询：

      SELECT ISNULL(
      (SELECT i.filter_definition
      FROM sys.indexes i
      WHERE i.object_id = OBJECT_ID(@tablename) AND
          i.name = @indexname AND has_filter = 1),
      '(1=1)');

    这也处理过滤器或整个索引被删除的情况。

    • 0

  4. 你可以试试这个查询：

    SELECT @OutputVariable =
(SELECT MAX(table1.SomeDateField)
FROM   dbo.table1
WHERE  table1.filteredColumn = @variable
  AND  table1.filteredColumn = the_filtered_value
UNION ALL
SELECT MAX(table1.SomeDateField)
FROM   dbo.table1
WHERE  table1.filteredColumn = @variable
  AND  NOT table1.filteredColumn = the_filtered_value)

    这样做的原因是因为您正在对过滤后的索引进行分叉，即仅在变量与过滤器谓词匹配的情况下使用它。如果没有这个，优化器就不能使用索引，因为它不能保证值与谓词匹配。

    • -1

相关问题