查询优化；Table.Column = @Param OR @Param IS NULL

Question

在WHERE子句中，当使用Table.Column = @Param或@Param IS NULL这样的条件时，它不会对列使用索引。

这是真的吗?如果是，那么如何编写这种也使用索引的查询

查询示例

SELECT Col1, Col2 ...
FROM Table
WHERE (Col1 = @col OR @col IS NULL)
AND   (Col2 = @col2 OR @col2 IS NULL)
AND   (Col3 = @col3 OR @col3 IS NULL)

有什么帮助吗。

Answer 1

不幸的是，执行计划的生成并不像您期望的那样。

对于该单个查询，将创建单个计划。在创建该计划时，将选择并固定要使用的索引。无论您提供什么参数，总是使用相同的计划、相同的索引等。

otpimiser试图找到可以满足所有可能性的最佳计划，但由于这种类型的查询的性质，没有一个计划。这是计划的一个特点，你根本不需要使用索引。

​

解决方案是使用动态SQL。这感觉很不整洁，但是如果你在sp_executesql中使用参数化查询，它实际上是非常结构化的，而且性能非常好。

这里有一个关于这个主题的非常有用的文章的链接：dynamic search

它非常深入，但它是解决这个问题的一种非常健壮的方法。

Answer 2

SELECT Col1, Col2 ...
FROM Table
WHERE EXISTS(
    SELECT Col1, Col2, Col3
    INTERSECT
    SELECT @col, @col2, @col3)

直观地看，这似乎应该表现得非常糟糕，但是SQL Server的查询优化器知道如何对INTERSECT进行特殊处理，并在内部将其转换为(伪SQL)

SELECT Col1, Col2 ...
FROM Table
WHERE (Col1, Col2, Col3) IS (@col, @col2, @col3)

正如您在查询计划中看到的那样。如果您在这些列上有索引，则可以并且确实可以使用它们。

我最初是从Paul怀特的Undocumented Query Plans: Equality Comparisons博客文章中读到这一点的，这可能是一篇有趣的进一步阅读。

Answer 3

为什么不试试这个：

SELECT Col1, Col2 ...
FROM Table
WHERE Col1 = IsNull(@col,Col1)
AND Col2 = IsNull(@col2,Col2)
AND Col3 = IsNull(@col3,Col3)

关于你的问题:你的查询分析器说它没有在列1，2，3上使用索引？你为所有3列都建立了索引？然后，它应该使用它，而不管其他OR IS NULL