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
本文版權歸作者所有,未經作者同意不得轉載。