我有一个类似于以下的表(但有更多的列):
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
@H_502_48@
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,没有扫描和没有排序.