为什么SQL Server没有将Index用于非常相似的日期时间查询？ [英] Why is SQL Server not using Index for very similar datetime query?

问题描述

我在SQL Server上有一个表，大约有100万行。
它有一个ID（PK），一个status（int）和一个datetime列。
我也在日期时间栏上创建了一个索引。

i have a table on SQL Server with about 1 million rows. It has an ID (PK), a status (int) and a datetime column. Also I've created an Index on the datetime column.

现在我发现了一个效果，我不明白。

Now I've found out an effect, which I don't understand.

SELECT status
FROM table
WHERE dateTime BETWEEN '2010-01-01T00:00:00' AND '2010-01-02T12:00:00'

此语句返回3664行。它运行大约150毫秒，执行计划显示它使用键查找进行索引搜索。

This statement returns 3664 rows. It runs about 150ms and the execution plan shows that its doing an index seek with key lookup.

现在，如果我将其更改为以下（只需将小时从12更改为13）：

Now, if I change it as following (just change the hour from 12 to 13):

SELECT status
FROM table
WHERE dateTime BETWEEN '2010-01-01T00:00:00' AND '2010-01-02T13:00:00'

此语句返回3667行。它运行大约600毫秒，并且exuction计划显示它使用主键！

This statement returns 3667 rows. It runs about 600ms and the exuction plan shows that its using the primary key!

我只是不明白它。对于3667及更多行，它总是使用主键，即使搜索速度要快得多。

I just don't understand it. For 3667 and more rows its always using the primary key, even if the seek is much faster.

有解释吗？

推荐答案

所以它需要为每个匹配行进行键查找以检索此值。

status is not included in the index on datetime so it needs to do key lookups for each matching row to retrieve this value.

随着范围的增长（以及因此需要的查找次数），它估计会更快只扫描整个（覆盖）聚集索引，避免查找。你的情况可能不正确。它从一个计划切换到另一个计划的点称为小费点。

As the range grows (and hence number of lookups required) it estimates that it will be quicker just to scan the whole (covering) clustered index avoiding the lookups. Possibly incorrectly in your case. The point at which it switches from one plan to the other is known as the tipping point.

您应该检查估计的行数与实际行数是否超出了重要性（可能已经删除了一些与该范围匹配的行已被删除统计数据最后更新）。

You should check if the estimated vs actual number of rows is out of whack (perhaps some rows that would have matched the range have been deleted since the statistics were last updated).

或者索引扫描比成本假设假设更昂贵，因为碎片水平高或者出于某种其他原因成本计算假设并未反映您环境中的实际相对表现。

Or maybe the index scan is more expensive than the costing assumptions assume due to high levels of fragmentation or for some other reason the costing assumptions made do not reflect the actual relative performance in your environment.

这篇关于为什么SQL Server没有将Index用于非常相似的日期时间查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！