LOGO OA教程 ERP教程 模切知识交流 PMS教程 CRM教程 开发文档 其他文档  
 
网站管理员

SQL Server的Descending Indexes降序索引

freeflydom
2024年12月12日 8:48 本文热度 230

背景
索引是关系型数据库中优化查询性能的重要手段之一。对于需要处理大量数据的场景,合理的索引策略能够显著减少查询时间。

特别是在涉及多字段排序的复杂查询中,选择合适的索引类型(如降序索引)显得尤为重要。本文将探讨如何在SQL Server中使用降序索引优化查询性能,并通过实例展示其应用效果。

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 直到 8.0 版本才引入这个功能,这也体现了不同数据库系统在功能上的演进。
在性能关键的场景下,商业数据库往往提供更强大和优化的功能。然而,索引策略的选择需要谨慎,尤其是面对字段排序顺序不一致的查询时,正确的索引能够极大提升查询性能。

参考文章

https://www.mssqltips.com/sqlservertip/1337/building-sql-server-indexes-in-ascending-vs-descending-order/

https://sqlmaestros.com/free-sql-video-troubleshoot-slow-running-query-sql-server-extended-events-wait-stats/

转自https://www.cnblogs.com/lyhabc/p/18417544/sql-server-index-optimization-with-ascending-and-descending-order


该文章在 2024/12/12 8:48:40 编辑过
关键字查询
相关文章
正在查询...
点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。
点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理,结合码头的业务特点,围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体,是物流码头及其他港口类企业的高效ERP管理信息系统。
点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。
点晴免费OA是一款软件和通用服务都免费,不限功能、不限时间、不限用户的免费OA协同办公管理系统。
Copyright 2010-2024 ClickSun All Rights Reserved