SQL Server的Descending Indexes降序索引 背景索引是關係型資料庫中優化查詢性能的重要手段之一。對於需要處理大量數據的場景，合理的索引策略能夠顯著減少查詢時間。 特別是在涉及多欄位排序的複雜查詢中，選擇合適的索引類型（如降序索引）顯得尤為重要。本文將探討如何在SQL Serv ...
SQL Server的Descending Indexes降序索引
背景
索引是關係型資料庫中優化查詢性能的重要手段之一。對於需要處理大量數據的場景，合理的索引策略能夠顯著減少查詢時間。
特別是在涉及多欄位排序的複雜查詢中，選擇合適的索引類型（如降序索引）顯得尤為重要。本文將探討如何在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/
加入我們的微信群，與我們一起探討資料庫技術，以及SQL Server、 MySQL、PostgreSQL、MongoDB 的相關話題。
微信群僅供學習交流使用，沒有任何廣告或商業活動。
如有不對的地方，歡迎大家拍磚o(∩_∩)o
本文版權歸作者所有，未經作者同意不得轉載。
