sql-server – SQL Server索引顺序(datetime字段)

我有一个类似于以下的表(但有更多的列)：

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,[QueryYear]  AS (datepart(year,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,SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,ONLINE = OFF,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],FILLFACTOR = 90) ON [PRIMARY]
GO

我假设它首先使用QueryTime排序来查找匹配结果,这些结果已经按ResultID排序.但是,情况并非如此,因为此索引的性能与现有索引相比没有任何变化.

然后我尝试了以下索引：

CREATE NONCLUSTERED INDEX [IDX_ResultDate3] ON [dbo].[Results] 
(
    [ResultID] ASC,[QueryTime] ASC
)
INCLUDE ( [ItemID],FILLFACTOR = 90) ON [PRIMARY]
GO

这个产生预期的结果.它似乎以恒定的时间(几分之一秒)返回.

但是,我很困惑为什么IDX_ResultDate3运行良好而IDX_ResultDate2没有.

我假设在QueryTime的排序列表中进行二进制搜索,然后查看ResultID的子列表中的第一个结果是获取结果的最快方法. (因此我的初始排序顺序).

附带问题：我应该创建一个持久化的列,其中包含QueryTime的日期部分和索引(我已经有三个持久列,如上所示)？

解决方法

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).

这确实很快,但是您的查询表达了不同的请求：您要求在“2009-05-01”之后发生的所有查询中使用最小ResultId的结果.为了满足它必须在范围的开始处寻求的请求(‘2009-05-01’),从该位置开始扫描以提取所有ResultId,对它们进行排序然后返回前1(最小ResultId).您添加的第二个索引[idx_ResultDate2]也没有多大帮助.查询必须执行完全相同的搜索和扫描：ResultIds在结果日期排序,因此要查找“2009-05-01”之后的所有结果中的顶级ResultId,查询仍然必须扫描指数直到结束.

在您的上一个索引[IDX_ResultDate3]上,查询是作弊.它做了什么它开始扫描inde并查看QueryTime值,知道在这个索引扫描中,第一个具有所需范围内的QueryTime的结果(>’2009-05-01′)就是你的那个想要(因为ResultId保证是Top 1).您可以从纯粹的运气中获得“一小部分”的结果：您在索引的开头有一个匹配的结果.查询可能会扫描整个索引并匹配非常的lat结果.您可以使用类似’2010-01-01’的QueryTime插入新结果,然后寻找它,您将看到性能下降,因为查询必须扫描整个索引直到结束(仍然比表扫描更快,因为缩小索引大小).

我的问题是：您是否绝对肯定您的查询必须在ORDER BY ResultID中返回TOP 1？或者您只是随意选择了订单？如果你可以将ORDER BY请求更改为,例如,QueryTime,那么任何索引(更新：使用QueryTime作为最左边的列)将返回一个简单的Seek和Fetch,没有扫描和没有排序.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!