SQL Server的Descending Indexes降序索引
SQL Server的Descending Indexes降序索引
1、建立测试环境
测试环境:SQL Server 2012
表结构如下
USE [test] GO CREATE TABLE [dbo].[tt8]( [id] INT IDENTITY(1,1) NOT NULL, [win_num] [int] NOT NULL DEFAULT ((0)), [lost_num] [int] NOT NULL DEFAULT ((0)), [draw_num] [int] NOT NULL DEFAULT ((0)), [offline_num] [int] NOT NULL DEFAULT ((0)), [login_key] [nvarchar](50) NULL CONSTRAINT [PK_user_T] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
插入测试数据
--插入测试数据 DECLARE @i INT; DECLARE @sql NVARCHAR(MAX); SET @i = 1; WHILE @i <= 9 BEGIN IF @i % 2 = 0 BEGIN SET @sql = N'INSERT INTO [dbo].[tt8] ( [win_num] , [lost_num] , [draw_num] , [offline_num] , [login_key] ) VALUES ( ''' + CAST(@i+2 AS NVARCHAR(3000)) + N''' , ''' + CAST(@i AS NVARCHAR(3000)) + N''' , ''' + CAST(@i-1 AS NVARCHAR(3000)) + N''' , ''' + CAST(@i AS NVARCHAR(3000)) + N''' , ''' + CAST(@i AS NVARCHAR(3000)) + N''' );'; END; ELSE BEGIN SET @sql = N'INSERT INTO [dbo].[tt8] ( [win_num] , [lost_num] , [draw_num] , [offline_num] , [login_key] ) VALUES ( ''' + CAST(@i AS NVARCHAR(3000)) + N''' , ''' + CAST(@i AS NVARCHAR(3000)) + N''' , ''' + CAST(@i AS NVARCHAR(3000)) + N''' , ''' + CAST(@i AS NVARCHAR(3000)) + N''' , ''' + CAST(@i AS NVARCHAR(3000)) + N''' );'; END; EXEC (@sql); SET @i = @i + 1; END;
浏览数据
SELECT * FROM [dbo].[tt8]
2、构建查询语句
查询语句如下,可以看到这个是组合字段排序,要求按照draw_num值正序,对于相同的draw_num值,按照win_num值倒序
select top 10 * from [dbo].[tt8] order by [draw_num] asc,[win_num] desc
根据查询语句建一个非聚集组合索引
CREATE NONCLUSTERED INDEX [IX_tt8_draw_numwin_num] ON [dbo].[tt8] ( [draw_num] ASC, [win_num] ASC )WITH (online= ON) ON [PRIMARY] GO
建了非聚集索引之后,执行计划如下,可以看到无法用到刚才建的非聚集索引[IX_tt8_draw_numwin_num],因为建索引时候,两个字段的排序顺序都是单向遍历的,统一升序或统一降序
下面的执行计划说明数据库引擎扫描聚集索引之后,需要对[win_num]字段进行倒序排序,所以会看到sort算子
查询结果如下,查询结果没有问题
3、建降序索引
那么,建索引时候能不能按照查询语句的排序顺序,[draw_num] 升序,[win_num] 降序呢?
答案是可以的,再建一个新索引按照[draw_num] 升序,[win_num] 降序的排序顺序
CREATE NONCLUSTERED INDEX [IX_tt8_draw_numwin_num_reverse] ON [dbo].[tt8] ( [draw_num] ASC, [win_num] DESC )WITH (ONLINE= ON) ON [PRIMARY] GO
建了索引之后,非聚集索引的结构大概是这样,第一个字段升序,第二个字段降序
再查询一次,查询结果如下,没有问题
执行计划如下,可以看到这次利用到索引[IX_tt8_draw_numwin_num_reverse],然后跟聚集索引联合返回结果
可以看到聚集索引/主键索引的存储结构,winnum字段是顺序排序存储的
SELECT TOP 10 * FROM [dbo].[tt8]
[IX_tt8_draw_numwin_num_reverse]索引的存储结构是[win_num]字段倒序,[draw_num]字段升序存储的
这个倒序索引的弊端是,当向表插入数据或者更新数据时,需要先对[win_num]字段倒序排序再插入或者更新到[IX_tt8_draw_numwin_num_reverse]索引,所以性能会有一点损耗
select [draw_num],[win_num] from [dbo].[tt8] with (INDEX([IX_tt8_draw_numwin_num_reverse]))
查询结果
通过这个例子说明,对于组合字段排序的语句,当多个字段排序顺序不一致的时候,只建单个字段的索引无法利用到索引,例如下面只建一个[draw_num] 字段的索引,在遇到下面语句时无法使用[IX_tt8_draw_num]索引
select TOP 10 * FROM [dbo].[tt8] ORDER BY [draw_num] ASC,[win_num] DESC
单字段索引
CREATE NONCLUSTERED INDEX [IX_tt8_draw_num] ON [dbo].[tt8] ( [draw_num] ASC ) WITH ( ONLINE = ON ) ON [PRIMARY] GO
必须要建立排序字段的组合索引,并且索引字段的排序要跟查询语句一致,这种索引在Oracle里面叫Descending Indexes
总结
Descending Indexes这个特性在SQL Server和Oracle的早期版本已经支持,在MySQL里面只有MySQL8.0才支持
所以有时候,还是商业数据库比较强大
参考文章:
https://www.mssqltips.com/sqlservertip/1337/building-sql-server-indexes-in-ascending-vs-descending-order/
如有不对的地方,欢迎大家拍砖o(∩_∩)o
本文版权归作者所有,未经作者同意不得转载。