sql-server - 如何在与参数一起使用时优化 "OR"子句的使用 (SQL Server 2008)

Question

我想知道是否有任何明智的方法来重写以下查询，以便优化器使用列上的索引？

CREATE PROCEDURE select_Proc1
    @Key1 int=0,
    @Key2 int=0
AS
BEGIN
    SELECT key3
    FROM Or_Table
    WHERE (@key1 = 0 OR Key1 = @Key1) AND
          (@key2 = 0 OR Key2 = @Key2)
END
GO

根据这篇文章How to Optimize the Use of the "OR" Clause When Used with Parameters作者:Preethiviraj Kulasingham:

Even though columns in the WHERE clauses are covered by indexes, SQL Server is unable to use these indexes. This raises the question as to whether anything is "blocking" the use of the indexes? The answer to this question is yes -- the culprits are the parameters and the OR condition.

The parameters are not covered by indexes, which means SQL Server cannot use any of the indexes to evaluate @key1=0 (a condition which also applies to @key2=0).

Effectively, this means SQL Server cannot use indexes to evaluate the clause @key1=0 OR Key1= @key1 (as the OR clause is the union of rows covered by both conditions). The same principle applies to the other clause (re. key2) as well. This leads SQL Server to conclude that no indexes can be used to extract the rows, leaving SQL Server to utilize the next best approach -- a clustered index scan

如您所见，如果在 WHERE 子句中对谓词进行 OR 运算，SQL 优化器将不会在列上使用索引。此问题的一种解决方案是使用 IF 子句对所有可能的参数组合进行单独查询。

现在我的问题是 - 如果可能的组合超过三个或四个，我们该怎么办？为每个组合编写单独的查询似乎不是一个合理的解决方案。

这个问题还有其他解决方法吗？

Answer 1

SQL Server 不太擅长优化 OR 谓词。

使用这个:

SELECT  key3
FROM    or_table
WHERE   @key1 = 0
        AND @key2 = 0
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key1 = 0
        AND @key2 <> 0
        AND key2 = @key2
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key2 = 0
        AND @key1 <> 0
        AND key1 = @key1
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key1 <> 0
        AND @key2 <> 0
        AND key1 = @key1
        AND key2 = @key2

SQL Server 将在执行查询之前查看变量的值，并优化冗余查询。

这意味着四个查询中只会实际执行一个。