SQL Server的Descending Indexes降序索引實例展示

更新時間：2024年09月23日 09:20:42 作者：樺仔

在涉及多字段排序的復雜查詢中,合理使用降序索引可以顯著提升SQLServer的查詢效率,本文通過構(gòu)建實際的查詢案例,展示了如何在SQLServer中建立并利用降序索引優(yōu)化查詢性能,感興趣的朋友一起看看吧

背景

索引是關(guān)系型數(shù)據(jù)庫中優(yōu)化查詢性能的重要手段之一。對于需要處理大量數(shù)據(jù)的場景，合理的索引策略能夠顯著減少查詢時間。

特別是在涉及多字段排序的復雜查詢中，選擇合適的索引類型（如降序索引）顯得尤為重要。本文將探討如何在SQL Server中使用降序索引優(yōu)化查詢性能，并通過實例展示其應(yīng)用效果。

1、建立測試環(huán)境

測試環(huán)境：SQL Server 2012

表結(jié)構(gòu)如下

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

插入測試數(shù)據(jù)

--插入測試數(shù)據(jù)
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;

瀏覽數(shù)據(jù)

SELECT * FROM [dbo].[tt8]

2、構(gòu)建查詢語句

查詢語句如下，可以看到這個是組合字段排序，要求按照draw_num值正序，對于相同的draw_num值，按照win_num值倒序

select top 10 * from [dbo].[tt8] order by  [draw_num] asc,[win_num] desc

根據(jù)查詢語句建一個非聚集組合索引

CREATE NONCLUSTERED INDEX [IX_tt8_draw_numwin_num] ON [dbo].[tt8]
(
    [draw_num] ASC,
    [win_num] ASC
)WITH (online= ON) ON [PRIMARY]
GO

建了非聚集索引之后，執(zhí)行計劃如下，可以看到無法用到剛才建的非聚集索引[IX_tt8_draw_numwin_num]，因為建索引時候，兩個字段的排序順序都是單向遍歷的，統(tǒng)一升序或統(tǒng)一降序

下面的執(zhí)行計劃說明數(shù)據(jù)庫引擎掃描聚集索引之后，需要對[win_num]字段進行倒序排序，所以會看到sort算子

查詢結(jié)果如下，查詢結(jié)果沒有問題

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

建了索引之后，非聚集索引的結(jié)構(gòu)大概是這樣，第一個字段升序，第二個字段降序

再查詢一次，查詢結(jié)果如下，沒有問題

執(zhí)行計劃如下，可以看到這次利用到索引[IX_tt8_draw_numwin_num_reverse]，然后跟聚集索引聯(lián)合返回結(jié)果

可以看到聚集索引/主鍵索引的存儲結(jié)構(gòu)，winnum字段是順序排序存儲的

SELECT TOP 10 * FROM [dbo].[tt8]

[IX_tt8_draw_numwin_num_reverse]索引的存儲結(jié)構(gòu)是[win_num]字段倒序，[draw_num]字段升序存儲的

這個倒序索引的弊端是，當向表插入數(shù)據(jù)或者更新數(shù)據(jù)時，需要先對[win_num]字段倒序排序再插入或者更新到[IX_tt8_draw_numwin_num_reverse]索引，所以性能會有一點損耗

select [draw_num],[win_num]  from [dbo].[tt8] with (INDEX([IX_tt8_draw_numwin_num_reverse]))

查詢結(jié)果

通過這個例子說明，對于組合字段排序的語句，當多個字段排序順序不一致的時候，只建單個字段的索引無法利用到索引，例如下面只建一個[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

總結(jié)

降序索引（Descending Indexes）在 SQL Server 和 Oracle 的早期版本中已經(jīng)支持，而 MySQL 直到 8.0 版本才引入這個功能，這也體現(xiàn)了不同數(shù)據(jù)庫系統(tǒng)在功能上的演進。
在性能關(guān)鍵的場景下，商業(yè)數(shù)據(jù)庫往往提供更強大和優(yōu)化的功能。然而，索引策略的選擇需要謹慎，尤其是面對字段排序順序不一致的查詢時，正確的索引能夠極大提升查詢性能。