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

 建了索引之後，執行計劃如下，可以看到無法用到剛纔建的索引，因為建索引時候，兩個欄位的排序順序都是單向遍歷的，統一升序或統一降序

那麼，建索引時候能不能按照查詢語句的順序，[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 

本文版權歸作者所有，未經作者同意不得轉載。