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 9999
BEGIN
SET @sql = 'INSERT INTO [dbo].[tt8]
(
[win_num] ,
[lost_num] ,
[draw_num] ,
[offline_num] ,
[login_key]
)
VALUES (
''' + CAST(@i AS NVARCHAR(3000)) + ''' ,
''' + CAST(@i AS NVARCHAR(3000)) + ''' ,
''' + CAST(@i AS NVARCHAR(3000)) + ''' ,
''' + CAST(@i AS NVARCHAR(3000)) + ''' ,
''' + CAST(@i AS NVARCHAR(3000)) + '''
);';
EXEC ( @sql );
SET @i = @i + 1;
END;
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
建了索引之后，执行计划如下，可以看到无法用到刚才建的索引，因为建索引时候，两个字段的排序顺序都是单向遍历的，统一升序或统一降序
3、建索引
那么，建索引时候能不能按照查询语句的顺序，[draw_num] 升序，[win_num] 降序呢？
答案是可以的，删除刚才建的索引，再建一个新索引
DROP INDEX [IX_tt8_draw_numwin_num] ON [tt8]
CREATE NONCLUSTERED INDEX [IX_tt8_draw_numwin_num] ON [dbo].[tt8]
(
[draw_num] ASC ,
[win_num] DESC ) WITH ( ONLINE = ON ) ON [PRIMARY]
GO
建了索引之后，索引大概是这样，第一个字段升序，第二个字段降序
再查询一次，执行计划如下，可以看到这次利用到索引
通过这个例子说明，对于组合字段排序的语句，当多个字段排序顺序不一致的时候，只建单个字段的索引是无法利用到索引的，例如下面只建一个[draw_num] 字段的索引是无法利用到[IX_tt8_draw_num]索引的
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
本文版权归作者所有，未经作者同意不得转载。
Original: https://www.cnblogs.com/lyhabc/p/10790266.html
Author: 桦仔
Title: SQL Server的Descending Indexes降序索引
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/590813/
转载文章受原作者版权保护。转载请注明原作者出处！