为什么SQL Server有时选择索引扫描而不是书签查找?

Question

我们有一个简单的表，像这样:

OrderID primary key / clustered index
CustomerID foreign key / single-column non-clustered index
[a bunch more columns]

然后，我们有这样一个查询:

SELECT [a bunch of columns]
FROM Orders
WHERE CustomerID = 1234

我们发现有时SQL Server 2008 R2在非聚集索引上执行查找，然后在聚集索引上执行书签查找(我们喜欢这种方式-它非常快)。

但是在其他看似随机的情况下，SQL Server反而对聚集索引进行扫描(非常慢-使我们的应用程序爬行-并且它似乎在我们一天中最繁忙的时候这样做)。

我知道我们可以(a)使用索引提示，或者(b)增强我们的非聚类索引，以便它覆盖我们选择的大列集。但是(a)将逻辑与物理联系起来，关于(b)，我读到过索引不应该覆盖太多的列。

我首先想听听你的想法为什么SQL Server正在做它正在做的事情。此外，任何建议将是最感激的。谢谢!

Answer 1

CustomerID的选择性将在查询优化器的决策中发挥一定作用。一方面，如果它是唯一的，那么相等操作最多只能产生一个结果，因此几乎可以保证执行SEEK/LOOKUP操作。另一方面，如果可能有数百或数千条记录匹配CustomerID的值，那么集群索引扫描可能看起来更有吸引力。

你会惊讶地发现，为了避免扫描，过滤器必须有多么的选择性。我找不到我最初提取这个数字的文章，但是如果CustomerID 1234将匹配表中只有4%的记录，那么对聚集索引进行扫描可能更有效，或者至少对优化器来说是这样的(这不是100%的时间)。

在CustomerID上保存的非聚集索引上的统计信息导致优化器根据选择性标准在查找/扫描之间切换，这听起来至少是合理的。

您可以通过引入JOIN或EXISTS操作来引导优化器使用索引:

-- Be aware: this approach is untested
select o.*
  from Orders o
       inner join Customers c on o.CustomerID = c.CustomerID
 where c.CustomerID = 1234;

或:

-- Be aware: this approach is untested
select o.*
  from Orders o
 where exists (select 1
                 from Customers c
                where c.CustomerID = 1234 and
                      o.CustomerID = c.CustomerID);

还要注意，使用这种EXISTS方法，如果您在两个表中的"join"谓词(在本例中是CustomerID字段)上没有索引，那么您将以嵌套循环结束，这是痛苦的慢。使用内连接似乎更安全，但EXISTS方法有时也有它的位置，当它可以利用索引。

这些只是建议;我不能说它们是否会有效。只是尝试一下，或者让常驻专家来确认或否认。

Answer 2

您应该使索引成为覆盖索引，这样就不需要查找书签了。这是一个潜在的昂贵操作，可能导致查询优化器忽略您的索引。

如果您使用的是SQL Server 2005或以上版本，您可以将它们添加为包含列，否则您必须将它们添加为附加键列。

覆盖索引总是比未覆盖索引执行得更好，特别是对于非选择性查询。