SQL Server 索引結構

喬安生發表於2022-02-24
SQLServer索引

索引是資料庫的基礎，只有先搞明白索引的結構，才能搞明白索引執行的邏輯

本文通過 索引表、資料頁、執行計劃、IO統計、B+Tree 來儘可能的介紹 SQL 語句中 WHERE 部分，和 SELECT 部分 的執行邏輯

名詞介紹

B+Tree：一種資料結構

資料頁：資料庫儲存資料的最小單位。(SQL Server一個資料頁的大小是 8K，一個表中所有的資料都被儲存到一個個的資料頁中) 

索引組織表：大白話一張表有聚集索引就是索引組織表（把表中的資料頁以 B+Tree 的方式組織起來）

索引表：一個索引對應一張索引表，索引表中每條資料都對應一張資料頁。

 

通過 DBCC IND(資料庫, 表名, 索引Id) 命令可以獲取到表中指定索引的索引表資訊

通過 DBCC PAGE(資料庫, 1, 資料頁Id, 3) 命令可以獲取到某個資料頁中的資料

B+Tree結構

 

 

 

準備資料

DROP TABLE Org_User
-- 建立測試表
CREATE TABLE Org_User(Id INT,UserName NVARCHAR(50),Age INT)
-- 建立聚集索引和非聚集索引
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條資料
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 這張系統表中

index_depth 表示索引的深度 （對應上圖B+Tree就是樹的高度）

page_cout 表示索引資料頁的數量 （對應上圖B+Tree就是葉子節點的數量）

這裡獲取索引資訊主要是為了 index_id

 

索引表

DBCC IND(Core2022, Org_User, 1)

 

 

 

DROP TABLE dbcc_ind
-- 建立一張表用來儲存索引表資訊
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
-- 建立儲存過程
CREATE PROC proc_dbcc_ind
AS
DBCC IND(Core2022,Org_User,1)

-- 把索引表中的資料批量插入到 dbcc_ind 中
INSERT INTO dbcc_ind
EXEC proc_dbcc_ind

 

SELECT 
    PagePID, -- 改行資料對應的資料頁
    IndexLevel, -- 表示改行資料的級別 0葉子節點，1分支節點，=2根節點，僅限該Demo
    NextPagePID, -- 當前節點的後繼節點 （後面的那個資料頁）
    PrevPagePID -- 當前節點的前驅節點 （前面的那個資料頁）
FROM dbcc_ind

 

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

 

 

 

對 DBCC IND 中的資料進行一個總結

通過觀察葉子節點的資料可以得到，每個節點都有一個前驅指標和後繼指標，構成了一個雙向連結串列

通過 IndexLevel 這個欄位區分 根節點、分支節點、葉子節點

通過 NextPagePID 和 PrevPagePID 兩個欄位把相同深度的節點構成了一個雙向連結串列

 

資料頁

DBCC TRACEON(3604) — 開啟跟蹤標記，不開啟的話 DBCC PAGE 只能檢視分支節點中的資料，不能檢視葉子節點中的資料

根節點

 

 

 分支節點

 

葉子節點

 

 

 

 

 非聚集索引的葉子節點

 

 

 

對索引表和根節點對應的資料頁，分支節點對應的資料頁，葉子節點對應的資料頁進行總結

聚集索引

　　葉子節點中儲存的是 Org_User 表中的資料

　　根節點和分支節點中儲存的是指向下一級節點的條件

 

　　索引表中同級的節點都有一個前驅和後繼指標，這兩個指標把同級的節點構建成了一個雙向連結串列

 

非聚集索引

　　根節點和分支節點與聚集索引一直，都是指向下一級節點的條件

　　葉子節點有區別包含 建立非聚集索引是指定的Key、指向該行資料實際地址的Key、保證索引唯一的Key

　　　　UserName 就是建立索引時指定的，如果建立時指定多個，這裡也會有多個

　　　　Id 這個是指向這行資料真實地址的指標表結構不同這個Key也不一樣

　　　　　　索引組織表：這個Key就是建立聚集索引時指定的 Key

　　　　　　堆表：就值這個行資料所在堆表的地址

　　　　UNIQUIFIER 如果建立索引時指定該索引時唯一索引，那麼這裡就不會有這個欄位，否則就會有這個欄位用來區分重複的資料

 

 

通過索引表，找到 Id = 66666 的這行資料所在的資料頁　　　　

 

 

 

對上圖進行解釋

拿著 66666 從根節點指向的資料頁開始找

66666 > 36017 所以就跳轉到 491 這個資料頁

66511 < 66666 ≤ 66669 所以就跳轉到 2755 這個資料頁

因為 2755 這個資料頁已經是葉子節點了，直接在裡面搜尋 66666

就找到了這一行資料

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

 

 

回表

 

 

因為這條SQL返回的欄位是 Select *

非聚集索引裡面沒有 Age 這個欄位

因此根據 UserName_66666 從非聚集索引中找到這條資料之後，根據 Id 到聚集索引裡面在查一次，找到 Age 這個欄位

 

覆蓋索引 

 

 Select Id,UserName 非聚集索引裡面這兩個欄位都有，所以就沒有必要在查詢聚集索引了

 

 

舉一個例子

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)

-- 上面這兩個SQL只有在 Id 為 Int 型別的時候才等價，在等價的前提下
-- 第一個SQL的效率要遠超於第二個SQL

/*
SET STATISTICS IO ON (開啟後輸出的內容)
(10 行受影響)
表 'Org_User'。掃描計數 1，邏輯讀取 3 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

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

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

*/

只有搞明白了索引執行的邏輯，結合執行計劃等工具，才能搞明白什麼情況下那些SQL更好

 

謠言：

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

　　首先這兩種寫法都不等價 COUNT(*) 是所有的資料 COUNT(列) NULL值不參與運算，所以如果COUNT的某一列中包含了NULL值算出來的資料可能就有問題了

　　查詢速度

　　　　COUNT(*) 更塊

　　　　COUNT(列) 會受偏移量和欄位中資料的大小影響

　　　　　　（通過 SET STATISTICS TIME ON 可以非常簡單的得出結論）

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

　　　　當前資料庫都會對SQL進行優化，所以無所謂誰在前，誰在後

　　IN 與 EXISTS 誰好誰壞

　　　　當前資料庫都會對SQL進行優化，所以無所謂誰好，誰壞

　　這些坑人的謠言還有很多，有些在老版本的資料庫是對的，在當前的資料庫中已經過時了。

 

相關文章