欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server的Descending Indexes

程序员文章站 2022-09-27 14:18:57
SQL Server的Descending Indexes 测试环境:SQL Server 2012 表结构如下 插入测试数据 查询语句如下,可以看到这个是组合字段排序,要求是:按照draw_num值正序,对于相同的draw_num值,按照win_num值倒序 根据查询语句建一个非聚集索引 建了索引 ......

sql server的descending indexes

 

测试环境: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;

 

查询语句如下,可以看到这个是组合字段排序,要求是:按照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

 

 

 建了索引之后,执行计划如下,可以看到无法用到刚才建的索引,因为建索引时候,两个字段的排序顺序都是单向遍历的,统一升序或统一降序

 SQL Server的Descending Indexes

 

那么,建索引时候能不能按照查询语句的顺序,[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

 

建了索引之后,索引大概是这样,第一个字段升序,第二个字段降序

SQL Server的Descending Indexes

 

再查询一次,执行计划如下,可以看到这次利用到索引

SQL Server的Descending Indexes

 

通过这个例子说明,对于组合字段排序的语句,当多个字段排序顺序不一致的时候,只建单个字段的索引是无法利用到索引的,例如下面只建一个[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 

本文版权归作者所有,未经作者同意不得转载。