SQL Server索引結構的具體使用

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)

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

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

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次邏輯讀

*/