SQL Server索引顺序（日期时间字段）

Question

我有一个关于SQL Server索引的问题。我不是DBA，认为对于那些是DBA的人来说答案是很清楚的。我正在使用SQL Server 2008。

我有一个类似于以下表格的表格（但有更多列）：

CREATE TABLE [dbo].[Results](
    [ResultID] [int] IDENTITY(1,1) NOT NULL,
    [TypeID] [int] NOT NULL,
    [ItemID] [int] NOT NULL,
    [QueryTime] [datetime] NOT NULL,
    [ResultTypeID] [int] NOT NULL,
    [QueryDay]  AS (datepart(day,[querytime])) PERSISTED,
    [QueryMonth]  AS (datepart(month,[querytime])) PERSISTED,
    [QueryYear]  AS (datepart(year,[querytime])) PERSISTED,
 CONSTRAINT [PK_Results] PRIMARY KEY CLUSTERED 
(
    [ResultID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

重要的字段是ResultID，它是主键，以及QueryTime，表示产生结果的日期时间。

此外，我还有以下索引（还有其他索引）：

CREATE NONCLUSTERED INDEX [IDX_ResultDate] ON [dbo].[Results] 
(
    [QueryTime] ASC
)
INCLUDE ( [ResultID],
[ItemID],
[TypeID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

在一个拥有约一百万行数据的数据库中，当执行以下查询时会使用索引：

select top 1 * from results where querytime>'2009-05-01' order by ResultID asc

在同一数据库的另一个实例中，有5000万行数据，SQL Server决定不使用索引，而是进行聚集索引扫描，结果速度非常慢（并且速度取决于日期）。即使我使用查询提示让它使用IDX_ResultDate，它仍然有些慢，并且它花费94％的时间通过ResultID进行排序。我发现通过创建一个具有ResultID和QueryTime作为排序列的索引，可以加快我的查询速度。

因此，我创建了以下内容：

CREATE NONCLUSTERED INDEX [IDX_ResultDate2] ON [dbo].[Results] 
(
[QueryTime] ASC,    
[ResultID] ASC
)
INCLUDE ( [ItemID],
[TypeID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

我原以为它会先使用按查询时间排序的方式来查找匹配结果，这些结果已经按结果ID排序。然而，实际情况并非如此，因为这个索引对现有索引的性能没有任何改变。

接着，我尝试了以下索引：

CREATE NONCLUSTERED INDEX [IDX_ResultDate3] ON [dbo].[Results] 
(
    [ResultID] ASC,
    [QueryTime] ASC
)
INCLUDE ( [ItemID],
[TypeID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

这个函数可以产生预期的结果。它似乎在常数时间内返回（不到一秒钟）。

然而，我对于为什么IDX_ResultDate3有效而IDX_ResultDate2无效感到困惑。

我会认为，在已排序的QueryTime列表中进行二分查找，然后查看其ResultIDs子列表中的第一个结果是最快的方法来获取结果。（因此我的初始排序顺序）

附加问题：我应该创建一个包含QueryTime日期部分的持久化列，并在该列上建立索引吗？（正如您在上面看到的，我已经有三个持久化列）

- Jason Kealey

Answer 1

我认为，在已排序的QueryTime列表中执行二分查找，然后查看其ResultIDs子列表中的第一个结果，是以最快的方式获取结果的方法（因此我进行了初始排序）。这确实很快，但是您的查询表达了不同的请求：您要求从“2009-05-01”之后发生的所有查询中具有最小ResultId的结果。为满足该请求，必须在范围的开头（“2009-05-01”）进行搜索，从此位置开始扫描以提取所有的ResultId，对它们进行排序，然后返回前1个（即最小的ResultId）。您添加的第二个索引[idx_ResultDate2]没有帮助太多。查询必须做几乎完全相同的搜索和扫描：ResultIds 在结果日期内 排序，因此要找出在'2009-05- 01 '之后的所有结果中的顶部ResultId，查询仍然必须扫描索引直到结束。

在最后一个索引[IDX_ResultDate3]上，查询是欺骗的。它在索引上启动扫描，并查看QueryTime值，知道在这个索引扫描中，具有所需范围内的QueryTime的第一个结果就是您想要的（因为保证ResultId是Top 1）。由于您在索引开头有匹配的结果，因此纯属运气可以“在几分之一秒”内获得结果。查询也可能扫描整个索引并匹配最后一个结果。您可以插入一个具有QueryTime（如“2010-01-01”）的新结果，然后寻找它，您会发现性能会随着查询必须扫描整个索引而变差（仍然比表扫描快，因为索引更窄）。我的问题是：你确定你的查询一定需要在ORDER BY中返回TOP 1吗？还是你只是随意选择了排序方式？如果你可以将ORDER BY请求更改为例如QueryTime，那么任何一个索引（更新：以QueryTime作为左侧列）都将返回一个简单的Seek和Fetch，没有扫描和排序。

- Remus Rusanu

---

非常好的解释。我现在明白了。我会看看能否重新设计应用程序以使用QueryTime排序。 - Jason Kealey

Answer 2

您在一个字段上有一个范围筛选条件，同时使用ORDER BY另一个字段。

在这种情况下，即使是复合索引，索引也无法用于满足这两个条件。

当您在(queryTime, resultId)上创建索引时，索引用于过滤。引擎仍然需要对结果集进行排序。

当您在(resultId, queryTime)上创建索引时，索引用于排序。

由于您需要一个TOP 1的结果，并且满足此结果的行恰好位于索引开头，因此后一种方法表现更好。

如果您的筛选条件是选择性的（即它将返回少量行），并且您所需的第一个结果恰好位于索引结尾，则第一种方法更好。

请参阅我的博客文章，了解有关在哪些条件下创建哪些索引的更多解释和提示：

• 选择索引

- Quassnoi

Answer 3

你可以将聚集索引更改为（[QueryTime]，[ResultID]），或更改查询方式。

select top 1 * from results where querytime>'2009-05-01' order by ResultID asc

to

select top 1 <only the columns you actually need> from results where querytime>'2009-05-01' order by ResultID asc

并将所有这些列包括在[IDX_ResultDate2]中

- A-K

---

+1确切地说，应该追求一个“覆盖”索引，其中包含满足查询所需的所有字段（如果可能的话）。 - marc_s

是的，已经在做了（没有在这里发布），但是性能相同。 - Jason Kealey

Answer 4

我首先建议您检查该表的统计信息（所有索引）是否是最新的。

由于使用不同的数据集获得了两个不同的执行计划，似乎SQL Server在选择一个执行计划而非另一个时做出了臭名昭著的“判断”。

我赞同 Remus 对您最后一个索引出现“神奇”结果的解释。

他的建议也很好 - 您真的想要按照 resultID 进行排序吗？如果您可以按照 queryTime 进行排序，那么性能会大大提高，因为执行计划将能够使用索引顺序作为结果集的顺序（并且它将通过索引进行搜索，而非扫描）。

- Jeff Meatball Yang

---

是的，统计数据是最新的。（而且，需要排序...不幸的是！） - Jason Kealey

Answer 5

我不确定我是否能回答这个问题，但要指出聚集索引键已作为任何其他索引的一部分包含在内，因此将ResultID作为您提出的任何其他索引的一部分是多余的。

- JNappi