sql-server - 为什么 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 方法时，如果您在 both 表，那么您最终会得到一个嵌套循环，它痛苦地 很慢。使用内连接似乎更安全，但 EXISTS 方法有时会在它可以利用索引时占有一席之地。

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