SQL索引查找

Question

我有一个关于查询的奇怪问题。我有一个包含两个bigint类型的表，StartNum和EndNum，用于定义数字范围。这两列都建立了索引。查询接收一个数字并返回它所属的范围。WHERE子句为where @Num >= StartNum and @Num <= EndNum。

在Profiler中，搜索接近列表开头和结尾的数字有很大的差异。数字越靠后，读取量和持续时间就越多。查询计划显示正在使用索引查找。

由于索引是平衡树，因此不应该有太大的区别。请问有人可以解释一下这是为什么吗？

注：这是在SQL 2005 Workgoup版上进行的。表中有约200,000行数据。索引是非聚集的（聚集索引位于标识列上，但数据按StartNum顺序插入）。索引有716页，深度为3，碎片率为3%。

- David Wimbush

---

在StartNum或EndNum上建立索引？计划是执行Seek或Seek后跟扫描（听起来是这样做的）。为什么您期望一个列上的一个索引能够同时满足两个条件呢？ - Remus Rusanu

嗨，雷姆斯， 索引位于两个列上，只有一个索引查找。 - David Wimbush

Answer 1

这取决于你的查询。如果你只查询这两个值，例如：

SELECT StartNum, EndNum
FROM Books
WHERE (@Num >= StartNum) AND (@Num <= EndNum)

在这种情况下，SQL Server只需要搜索这两个索引就能返回数字。你的两个索引包含了你要索引的值，并且因为它们是索引，所以它们是排序的。

但是我确信你的查询实际上还包括其他列：

SELECT BookID, Title, IDBN, Author, StartNum, EndNum
FROM Books
WHERE (@Num >= StartNum) AND (@Num <= EndNum)

在这种情况下，SQL Server 一旦找到与条件匹配的行的 id，就必须返回到数据库中查找这些行，以便可以返回以下内容：

• 标题
• ISBN
• 作者

除了它已经从两个索引中拥有的值：

• StartNum
• EndNum
• BookID

注意：StartNum 上的索引隐含包含聚集键的值，因为它知道哪一行对应于索引中的条目。

问题是，如果有“太多的书”需要在表中查找，那么读取整个表可能会更快。

---

类比：您在书的索引中查找所有与“设计模式”有关的参考文献。

设计模式：4、89、221、442

如果只有4个条目，那么您可以翻到索引中列出的页面。这被称为书签查找。

但是，如果索引中说有827个短语的参考文献呢？

计算机：1、2、6、[省略825个条目]、1087、1128

那么自己阅读整本书可能会更快。在这种情况下，我们放弃了并扫描整本书。如果表具有聚集索引，则SQL Server将其称为聚集索引扫描，如果没有聚集索引（即它是“堆表”），则称为表扫描。

---

如果您在查询中只是引用了 StartNum 和 EndNum（比如说您正在获取计数），那么我相信您会看到一致性低的读取和执行时间。但是，如果您包括其他列，并且 SQL Server 认为该查询将返回太多行（例如超过表的 5%），那么它就会忘记索引并扫描整个表。

存在一个交叉点，SQL Server 知道您的表中 StartNum 和 EndNum 值的分布情况，因为它对值进行采样并具有其分布的统计信息。如果 @Num 的某些值恰好返回少量行，并且 SQL Server 知道这一点，它将执行相对较少的 书签查找。但是，如果您的数据分布会导致返回更多行，则会出现 聚集索引扫描。

- Ian Boyd

---

谢谢，Ian，但我说的确实很简单。我开始处理更多的事情，所以我把它们全部剔除了，只从索引中选择列。没有任何书签查找或其他东西，只是在我的索引上进行索引搜索。仍然会因为所查询的数字在“列表”中的位置而产生广泛变化的读取次数和持续时间。 - David Wimbush

Answer 2

你的StartNum是按升序排列，EndNum是按降序排列吗？

因为如果你的StartNum是按升序排列，EndNum也是按升序排列，那么它就不能进行两次查找。它必须进行一次查找和一次扫描。

另一个可能性是分别在StartNum和EndNum上建立索引（顺序无关紧要），看看它是否会按顺序使用两个索引，然后进行某种连接操作。

只有查看不同的执行计划，你才能真正了解情况。

我没有真实的列数据，所以很难复制这种行为。我设置了一个测试表，其中包含200000个StartNum-EndNum范围100-199、200-299等。我已经能够进行聚集索引扫描、表扫描和非聚集索引查找，但与你所说的情况相差甚远。

- Cade Roux

---

谢谢，Cade。索引是StartNum升序，EndNum升序，实际上在查询计划中只有一个单一的索引查找。我尝试了升序和降序，但没有帮助。我还尝试了分开的索引，然后它使用了StartNum索引的查找结合表扫描来找到那些小于等于EndNum的值。 - David Wimbush

Answer 3

搞定了！

EndNum始终大于等于StartNum，因此我只需要找到最大的StartNum，使其小于等于@Num。我将索引更改为StartNum desc，并将查询更改为top 1 ... where StartNum <= @Num order by StartNum desc。现在对于任何@Num值，它只是一个带有5个读取和0持续时间的索引查找，这正是我想要的。

感谢大家的帮助。

- David Wimbush