SQL Server的Descending Indexes降序索引
當(dāng)前位置：點(diǎn)晴教程→知識(shí)管理交流
→『 技術(shù)文檔交流 』
背景 特別是在涉及多字段排序的復(fù)雜查詢中，選擇合適的索引類型（如降序索引）顯得尤為重要。本文將探討如何在SQL Server中使用降序索引優(yōu)化查詢性能，并通過實(shí)例展示其應(yīng)用效果。 1、建立測(cè)試環(huán)境 測(cè)試環(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
插入測(cè)試數(shù)據(jù) --插入測(cè)試數(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)建查詢語句 查詢語句如下，可以看到這個(gè)是組合字段排序，要求按照draw_num值正序，對(duì)于相同的draw_num值，按照win_num值倒序 select top 10 * from [dbo].[tt8] order by [draw_num] asc,[win_num] desc根據(jù)查詢語句建一個(gè)非聚集組合索引 CREATE NONCLUSTERED INDEX [IX_tt8_draw_numwin_num] ON [dbo].[tt8]
(
[draw_num] ASC,
[win_num] ASC
)WITH (online= ON) ON [PRIMARY]
GO建了非聚集索引之后，執(zhí)行計(jì)劃如下，可以看到無法用到剛才建的非聚集索引[IX_tt8_draw_numwin_num]，因?yàn)榻ㄋ饕龝r(shí)候，兩個(gè)字段的排序順序都是單向遍歷的，統(tǒng)一升序或統(tǒng)一降序 下面的執(zhí)行計(jì)劃說明數(shù)據(jù)庫引擎掃描聚集索引之后，需要對(duì)[win_num]字段進(jìn)行倒序排序，所以會(huì)看到sort算子 查詢結(jié)果如下，查詢結(jié)果沒有問題
3、建降序索引 那么，建索引時(shí)候能不能按照查詢語句的排序順序，[draw_num] 升序，[win_num] 降序呢？ 答案是可以的，再建一個(gè)新索引按照[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)大概是這樣，第一個(gè)字段升序，第二個(gè)字段降序
再查詢一次，查詢結(jié)果如下，沒有問題 執(zhí)行計(jì)劃如下，可以看到這次利用到索引[IX_tt8_draw_numwin_num_reverse]，然后跟聚集索引聯(lián)合返回結(jié)果
可以看到聚集索引/主鍵索引的存儲(chǔ)結(jié)構(gòu)，winnum字段是順序排序存儲(chǔ)的 SELECT TOP 10 * FROM [dbo].[tt8]
[IX_tt8_draw_numwin_num_reverse]索引的存儲(chǔ)結(jié)構(gòu)是[win_num]字段倒序，[draw_num]字段升序存儲(chǔ)的 這個(gè)倒序索引的弊端是，當(dāng)向表插入數(shù)據(jù)或者更新數(shù)據(jù)時(shí)，需要先對(duì)[win_num]字段倒序排序再插入或者更新到[IX_tt8_draw_numwin_num_reverse]索引，所以性能會(huì)有一點(diǎn)損耗 select [draw_num],[win_num] from [dbo].[tt8] with (INDEX([IX_tt8_draw_numwin_num_reverse]))
查詢結(jié)果
通過這個(gè)例子說明，對(duì)于組合字段排序的語句，當(dāng)多個(gè)字段排序順序不一致的時(shí)候，只建單個(gè)字段的索引無法利用到索引，例如下面只建一個(gè)[draw_num] 字段的索引，在遇到下面語句時(shí)無法使用[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 版本才引入這個(gè)功能，這也體現(xiàn)了不同數(shù)據(jù)庫系統(tǒng)在功能上的演進(jìn)。 參考文章 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/ 轉(zhuǎn)自https://www.cnblogs.com/lyhabc/p/18417544/sql-server-index-optimization-with-ascending-and-descending-order 該文章在 2024/12/12 8:48:40 編輯過 |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
