where @Num >= StartNum and @Num <= EndNum
。在Profiler中,搜索接近列表开头和结尾的数字有很大的差异。数字越靠后,读取量和持续时间就越多。查询计划显示正在使用索引查找。
由于索引是平衡树,因此不应该有太大的区别。请问有人可以解释一下这是为什么吗?
注:这是在SQL 2005 Workgoup版上进行的。表中有约200,000行数据。索引是非聚集的(聚集索引位于标识列上,但数据按StartNum顺序插入)。索引有716页,深度为3,碎片率为3%。
where @Num >= StartNum and @Num <= EndNum
。这取决于你的查询。如果你只查询这两个值,例如:
SELECT StartNum, EndNum
FROM Books
WHERE (@Num >= StartNum) AND (@Num <= EndNum)
SELECT BookID, Title, IDBN, Author, StartNum, EndNum
FROM Books
WHERE (@Num >= StartNum) AND (@Num <= EndNum)
除了它已经从两个索引中拥有的值:
注意: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 知道这一点,它将执行相对较少的 书签查找。但是,如果您的数据分布会导致返回更多行,则会出现 聚集索引扫描。
你的StartNum是按升序排列,EndNum是按降序排列吗?
因为如果你的StartNum是按升序排列,EndNum也是按升序排列,那么它就不能进行两次查找。它必须进行一次查找和一次扫描。
另一个可能性是分别在StartNum和EndNum上建立索引(顺序无关紧要),看看它是否会按顺序使用两个索引,然后进行某种连接操作。
只有查看不同的执行计划,你才能真正了解情况。
我没有真实的列数据,所以很难复制这种行为。我设置了一个测试表,其中包含200000个StartNum-EndNum范围100-199、200-299等。我已经能够进行聚集索引扫描、表扫描和非聚集索引查找,但与你所说的情况相差甚远。
搞定了!
EndNum始终大于等于StartNum,因此我只需要找到最大的StartNum,使其小于等于@Num。我将索引更改为StartNum desc,并将查询更改为top 1 ... where StartNum <= @Num order by StartNum desc
。现在对于任何@Num值,它只是一个带有5个读取和0持续时间的索引查找,这正是我想要的。
感谢大家的帮助。