快捷導(dǎo)航

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

更新時(shí)間：2022年02月25日 08:54:28 作者：喬安生

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

名詞介紹

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

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

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

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

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

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

DROP TABLE Org_User
-- 創(chuàng)建測試表
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 表示索引的深度（對應(yīng)上圖B+Tree就是樹的高度）

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

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

索引表

DBCC IND(Core2022, Org_User, 1)

DROP TABLE dbcc_ind
-- 創(chuàng)建一張表用來保存索引表信息
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ǔ)過程
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ù)對應(yīng)的數(shù)據(jù)頁
    IndexLevel, -- 表示改行數(shù)據(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ù)頁）
    PrevPagePID -- 當(dāng)前節(jié)點(diǎn)的前驅(qū)節(jié)點(diǎn) （前面的那個(gè)數(shù)據(jù)頁）
FROM dbcc_ind

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

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

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

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

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

數(shù)據(jù)頁

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

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

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

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

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

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

聚集索引

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

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

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

非聚集索引

　　根節(jié)點(diǎn)和分支節(jié)點(diǎn)與聚集索引一直，都是指向下一級節(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è)字段用來區(qū)分重復(fù)的數(shù)據(jù)

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

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

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

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

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

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

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

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

回表

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

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

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

覆蓋索引

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

舉一個(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 (開啟后輸出的內(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(列) 誰快，誰慢

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

　　查詢速度

　　　　COUNT(*) 更塊

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

　　　　　?。ㄍㄟ^ SET STATISTICS TIME ON 可以非常簡單的得出結(jié)論）

　　SQL語句大表寫前面，小表寫后面

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

　　IN 與 EXISTS 誰好誰壞

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