sql-server - SQL Server 索引顺序(日期时间字段)

Question

我有一个关于 SQL Server 索引的问题。我不是 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

我假设它会首先使用按 QueryTime 排序来查找匹配结果，这些结果已经按 ResultID 排序。然而，情况并非如此，因为该索引对现有索引的性能没有任何改变。

然后我尝试了以下索引:

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 列表中进行二进制搜索，然后查看它的 ResultID 子列表中的第一个结果是获得结果的最快方法。 (因此我的初始排序顺序)。

附带问题:我是否应该创建一个包含 QueryTime 日期部分的持久化列并在其上建立索引(我已经有三个持久化列，如您在上面看到的那样)？

Answer 1

I would assume that a binary search in as sorted list of QueryTime followed by peeking at the first result in it's child list of ResultIDs is the fastest way at getting the result. (Hence my initial sort order).

那确实会很快，但是您的查询表达了不同的请求:您正在请求具有最小 ResultId 的结果来自“2009-05-01”之后发生的所有查询。为了满足它必须在范围的开头('2009-05-01')寻找的请求，从这个位置开始扫描以提取所有ResultId，对它们进行排序然后返回前1(最小ResultId)。您添加的第二个索引 [idx_ResultDate2] 也无济于事。查询必须执行几乎完全相同的搜索和扫描:ResultIds 在结果日期内排序，因此要从之后的所有结果中找出最靠前的 ResultId '2009-05-01' 查询仍然必须扫描索引直到结束。

在您的最后一个索引 [IDX_ResultDate3] 上，查询是作弊的。它的作用是开始对索引进行扫描并查看 QueryTime 值，知道在该索引中扫描第一个 QueryTime 在所需范围内的结果 (> '2009-05-01 ') 就是你想要的(因为 ResultId 保证是 Top 1)。纯属运气，你在“几分之一秒”内得到结果:你在索引的开头有一个匹配的结果。查询可能会扫描整个索引并匹配非常纬度的结果。您可以插入一个带有“2010-01-01”之类的 QueryTime 的新结果，然后寻找它，您会看到性能下降，因为查询必须扫描整个索引直到结束(仍然比表扫描快，因为较窄的索引大小)。

我的问题是:您是否绝对肯定您的查询必须返回 ORDER BY ResultID 中的 TOP 1？或者您只是随意选择订单？如果您可以将 ORDER BY 请求更改为 QueryTime，那么任何索引(更新:最左边的列是 QueryTime)将返回一个简单的 Seek 和 Fetch，没有 scansn 也没有排序。