快捷導(dǎo)航

SQL?Server索引結(jié)構(gòu)的具體使用

更新時(shí)間：2022年02月25日 08:54:28 作者：?jiǎn)贪采?

索引是數(shù)據(jù)庫(kù)的基礎(chǔ)，只有先搞明白索引的結(jié)構(gòu)，才能搞明白索引運(yùn)行的邏輯，本文主要介紹了SQL?Server?索引結(jié)構(gòu)的具體使用，具有一定的參考價(jià)值，感興趣的可以了解一下

名詞介紹

B+Tree：一種數(shù)據(jù)結(jié)構(gòu)

數(shù)據(jù)頁(yè)：數(shù)據(jù)庫(kù)保存數(shù)據(jù)的最小單位。(SQL Server一個(gè)數(shù)據(jù)頁(yè)的大小是 8K，一個(gè)表中所有的數(shù)據(jù)都被保存到一個(gè)個(gè)的數(shù)據(jù)頁(yè)中)
索引組織表：大白話一張表有聚集索引就是索引組織表（把表中的數(shù)據(jù)頁(yè)以 B+Tree 的方式組織起來(lái)）
索引表：一個(gè)索引對(duì)應(yīng)一張索引表，索引表中每條數(shù)據(jù)都對(duì)應(yīng)一張數(shù)據(jù)頁(yè)。

通過(guò)DBCC IND(數(shù)據(jù)庫(kù), 表名, 索引Id) 命令可以獲取到表中指定索引的索引表信息

通過(guò)DBCC PAGE(數(shù)據(jù)庫(kù), 1, 數(shù)據(jù)頁(yè)Id, 3) 命令可以獲取到某個(gè)數(shù)據(jù)頁(yè)中的數(shù)據(jù)

B+Tree結(jié)構(gòu)

準(zhǔn)備數(shù)據(jù)

DROP TABLE Org_User
-- 創(chuàng)建測(cè)試表
CREATE TABLE Org_User(Id INT,UserName NVARCHAR(50),Age INT)
-- 創(chuàng)建聚集索引和非聚集索引
CREATE CLUSTERED INDEX Org_User_Id ON Org_User(Id)
CREATE NONCLUSTERED INDEX Org_User_Name ON Org_User(UserName)

CREATE TABLE #Temp(Id INT)
INSERT INTO #Temp VALUES(1)
INSERT INTO #Temp VALUES(2)
INSERT INTO #Temp VALUES(3)
INSERT INTO #Temp VALUES(4)
INSERT INTO #Temp VALUES(5)
INSERT INTO #Temp VALUES(6)
INSERT INTO #Temp VALUES(7)
INSERT INTO #Temp VALUES(8)
INSERT INTO #Temp VALUES(9)
INSERT INTO #Temp VALUES(10)

-- 批量插入10W條數(shù)據(jù)
INSERT  INTO dbo.Org_User
SELECT T1.Id, 'UserName_' + CONVERT(NVARCHAR(20), T1.Id) AS 'UserName', T1.Id + 10 AS 'Age' FROM 
(
    SELECT TOP 100000 Id = ROW_NUMBER() OVER (ORDER BY T1.Id)
    FROM #Temp AS T1
    CROSS JOIN #Temp AS T2
    CROSS JOIN #Temp AS T3
    CROSS JOIN #Temp AS T4
    CROSS JOIN #Temp AS T5
    ORDER BY T1.Id
) AS T1

SELECT name, index_id,type_desc FROM SYS.INDEXES WHERE object_id = OBJECT_ID('Org_User');

SELECT  index_id ,
        index_type_desc ,
        index_depth ,
        page_count
FROM    sys.dm_db_index_physical_stats(DB_ID('Core2022'), OBJECT_ID('Org_User'), NULL, NULL, NULL)

在 sys.dm_db_index_physical_stats 這張系統(tǒng)表中

index_depth 表示索引的深度（對(duì)應(yīng)上圖B+Tree就是樹(shù)的高度）

page_cout 表示索引數(shù)據(jù)頁(yè)的數(shù)量（對(duì)應(yīng)上圖B+Tree就是葉子節(jié)點(diǎn)的數(shù)量）

這里獲取索引信息主要是為了 index_id

索引表

DBCC IND(Core2022, Org_User, 1)

DROP TABLE dbcc_ind
-- 創(chuàng)建一張表用來(lái)保存索引表信息
CREATE TABLE dbcc_ind
(
    PageFID NUMERIC(20),
    PagePID NUMERIC(20),
    IAMFID NUMERIC(20),
    IAMPID NUMERIC(20),
    ObjectID NUMERIC(20),
    IndexID NUMERIC(20),
    PartitionNumber NUMERIC(20),
    PartitionID NUMERIC(20),
    iam_chain_type VARCHAR(100),
    PageType NUMERIC(20),
    IndexLevel NUMERIC(20),
    NextPageFID NUMERIC(20),
    NextPagePID NUMERIC(20),
    PrevPageFID NUMERIC(20),
    PrevPagePID NUMERIC(20)
)

--DROP PROC proc_dbcc_ind
-- 創(chuàng)建存儲(chǔ)過(guò)程
CREATE PROC proc_dbcc_ind
AS
DBCC IND(Core2022,Org_User,1)

-- 把索引表中的數(shù)據(jù)批量插入到 dbcc_ind 中
INSERT INTO dbcc_ind
EXEC proc_dbcc_ind

SELECT 
    PagePID, -- 改行數(shù)據(jù)對(duì)應(yīng)的數(shù)據(jù)頁(yè)
    IndexLevel, -- 表示改行數(shù)據(jù)的級(jí)別 0葉子節(jié)點(diǎn)，1分支節(jié)點(diǎn)，=2根節(jié)點(diǎn)，僅限該Demo
    NextPagePID, -- 當(dāng)前節(jié)點(diǎn)的后繼節(jié)點(diǎn) （后面的那個(gè)數(shù)據(jù)頁(yè)）
    PrevPagePID -- 當(dāng)前節(jié)點(diǎn)的前驅(qū)節(jié)點(diǎn) （前面的那個(gè)數(shù)據(jù)頁(yè)）
FROM dbcc_ind

SELECT 
    PagePID,
    IndexLevel,
    NextPagePID,
    PrevPagePID 
FROM dbcc_ind 
WHERE IndexLevel = 0
ORDER BY NextPagePID

對(duì) DBCC IND 中的數(shù)據(jù)進(jìn)行一個(gè)總結(jié)

通過(guò)觀察葉子節(jié)點(diǎn)的數(shù)據(jù)可以得到，每個(gè)節(jié)點(diǎn)都有一個(gè)前驅(qū)指針和后繼指針，構(gòu)成了一個(gè)雙向鏈表

通過(guò) IndexLevel 這個(gè)字段區(qū)分根節(jié)點(diǎn)、分支節(jié)點(diǎn)、葉子節(jié)點(diǎn)

通過(guò) NextPagePID 和 PrevPagePID 兩個(gè)字段把相同深度的節(jié)點(diǎn)構(gòu)成了一個(gè)雙向鏈表

數(shù)據(jù)頁(yè)

DBCC TRACEON(3604) — 打開(kāi)跟蹤標(biāo)記，不打開(kāi)的話 DBCC PAGE 只能查看分支節(jié)點(diǎn)中的數(shù)據(jù)，不能查看葉子節(jié)點(diǎn)中的數(shù)據(jù)

根節(jié)點(diǎn)

分支節(jié)點(diǎn)

葉子節(jié)點(diǎn)

非聚集索引的葉子節(jié)點(diǎn)

對(duì)索引表和根節(jié)點(diǎn)對(duì)應(yīng)的數(shù)據(jù)頁(yè)，分支節(jié)點(diǎn)對(duì)應(yīng)的數(shù)據(jù)頁(yè)，葉子節(jié)點(diǎn)對(duì)應(yīng)的數(shù)據(jù)頁(yè)進(jìn)行總結(jié)

聚集索引

　　葉子節(jié)點(diǎn)中保存的是 Org_User 表中的數(shù)據(jù)

　　根節(jié)點(diǎn)和分支節(jié)點(diǎn)中保存的是指向下一級(jí)節(jié)點(diǎn)的條件

　　索引表中同級(jí)的節(jié)點(diǎn)都有一個(gè)前驅(qū)和后繼指針，這兩個(gè)指針把同級(jí)的節(jié)點(diǎn)構(gòu)建成了一個(gè)雙向鏈表

非聚集索引

　　根節(jié)點(diǎn)和分支節(jié)點(diǎn)與聚集索引一直，都是指向下一級(jí)節(jié)點(diǎn)的條件

　　葉子節(jié)點(diǎn)有區(qū)別包含創(chuàng)建非聚集索引是指定的Key、指向該行數(shù)據(jù)實(shí)際地址的Key、保證索引唯一的Key

　　　　UserName 就是創(chuàng)建索引時(shí)指定的，如果創(chuàng)建時(shí)指定多個(gè)，這里也會(huì)有多個(gè)

　　　　Id 這個(gè)是指向這行數(shù)據(jù)真實(shí)地址的指針表結(jié)構(gòu)不同這個(gè)Key也不一樣

　　　　　　索引組織表：這個(gè)Key就是創(chuàng)建聚集索引時(shí)指定的 Key

　　　　　　堆表：就值這個(gè)行數(shù)據(jù)所在堆表的地址

　　　　UNIQUIFIER 如果創(chuàng)建索引時(shí)指定該索引時(shí)唯一索引，那么這里就不會(huì)有這個(gè)字段，否則就會(huì)有這個(gè)字段用來(lái)區(qū)分重復(fù)的數(shù)據(jù)

通過(guò)索引表，找到 Id = 66666 的這行數(shù)據(jù)所在的數(shù)據(jù)頁(yè)　　　　

對(duì)上圖進(jìn)行解釋

拿著 66666 從根節(jié)點(diǎn)指向的數(shù)據(jù)頁(yè)開(kāi)始找

66666 > 36017 所以就跳轉(zhuǎn)到 491 這個(gè)數(shù)據(jù)頁(yè)

66511 < 66666 ≤ 66669 所以就跳轉(zhuǎn)到 2755 這個(gè)數(shù)據(jù)頁(yè)

因?yàn)?2755 這個(gè)數(shù)據(jù)頁(yè)已經(jīng)是葉子節(jié)點(diǎn)了，直接在里面搜索 66666

就找到了這一行數(shù)據(jù)

SET STATISTICS IO ON 
SELECT * FROM Org_User WHERE Id = 66666

回表

因?yàn)檫@條SQL返回的字段是 Select *

非聚集索引里面沒(méi)有 Age 這個(gè)字段

因此根據(jù) UserName_66666 從非聚集索引中找到這條數(shù)據(jù)之后，根據(jù) Id 到聚集索引里面在查一次，找到 Age 這個(gè)字段

覆蓋索引

Select Id,UserName 非聚集索引里面這兩個(gè)字段都有，所以就沒(méi)有必要在查詢聚集索引了

舉一個(gè)例子

SET STATISTICS IO ON
SELECT * FROM [Org_User] WHERE Id >= 1 AND Id <= 10
SELECT * FROM [Org_User] WHERE Id IN (1,2,3,4,5,6,7,8,9,10)

-- 上面這兩個(gè)SQL只有在 Id 為 Int 類型的時(shí)候才等價(jià)，在等價(jià)的前提下
-- 第一個(gè)SQL的效率要遠(yuǎn)超于第二個(gè)SQL

/*
SET STATISTICS IO ON (開(kāi)啟后輸出的內(nèi)容)
(10 行受影響)
表 'Org_User'。掃描計(jì)數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

(10 行受影響)
表 'Org_User'。掃描計(jì)數(shù) 10，邏輯讀取 30 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

很明顯 第一個(gè)SQL只有3次邏輯讀，而第二個(gè)有30次邏輯讀

*/

只有搞明白了索引運(yùn)行的邏輯，結(jié)合執(zhí)行計(jì)劃等工具，才能搞明白什么情況下那些SQL更好

謠言：

　　COUNT(*) 和 COUNT(列) 誰(shuí)快，誰(shuí)慢

　　首先這兩種寫(xiě)法都不等價(jià) COUNT(*) 是所有的數(shù)據(jù) COUNT(列) NULL值不參與運(yùn)算，所以如果COUNT的某一列中包含了NULL值算出來(lái)的數(shù)據(jù)可能就有問(wèn)題了

　　查詢速度

　　　　COUNT(*) 更塊

　　　　COUNT(列) 會(huì)受偏移量和字段中數(shù)據(jù)的大小影響

　　　　　　（通過(guò) SET STATISTICS TIME ON 可以非常簡(jiǎn)單的得出結(jié)論）

　　SQL語(yǔ)句大表寫(xiě)前面，小表寫(xiě)后面

　　　　當(dāng)前數(shù)據(jù)庫(kù)都會(huì)對(duì)SQL進(jìn)行優(yōu)化，所以無(wú)所謂誰(shuí)在前，誰(shuí)在后

　　IN 與 EXISTS 誰(shuí)好誰(shuí)壞

　　　　當(dāng)前數(shù)據(jù)庫(kù)都會(huì)對(duì)SQL進(jìn)行優(yōu)化，所以無(wú)所謂誰(shuí)好，誰(shuí)壞