Sql Server之旅——第十站看看DML操作對索引的影響

2024-08-31 00:54:49

字體：大中小

來源：轉載

供稿：網友

　　我們都知道建索引是需要謹慎的，當只有利大于弊的時候才適合建，我們也知道建索引是需要維護成本的，這個維護也就在于DML操作了，

下面我們具體看看到底DML對索引都有哪些內幕。。。。

一：delete操作

　　現(xiàn)在我們已經知道，索引都是以B樹的形式存在的，既然是B樹，我們就要看看他們的葉子節(jié)點和分支結點，先準備點測試數據，如下圖：

CREATE TABLE Person(ID INT,NAME CHAR(200))CREATE INDEX idx_Name ON Person(NAME)DECLARE @ch AS INT=65WHILE @ch<=122BEGIN    INSERT INTO dbo.Person(ID,NAME)    VALUES    (      @ch,      REPLICATE(CHAR(@ch),200)    )    SET @ch=@ch+1END

<1> 葉子結點的變化

　　從上面的圖中大概可以看到，當我插入完畢后，現(xiàn)在有4個索引數據頁，其中PID=200的為分支數據頁，其他三個為葉子節(jié)點數據頁，分別

為175,201,202號數據頁，然后我就挑選第二個葉子節(jié)點數據頁201號，看看里面的數據是啥樣的。

從數據頁中可以看到在201號數據頁中有18個槽位，當然除了通過槽位看記錄條數之外，你還可以通過Pageheader中的m_slotCnt來觀察記

錄個數，如下圖：

接下來，我們看看slot0槽位的內容是啥樣，如下圖：

 1 0000000000000000:   16484848 48484848 48484848 48484848 †.HHHHHHHHHHHHHHH  2 0000000000000010:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH  3 0000000000000020:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH  4 0000000000000030:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH  5 0000000000000040:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH  6 0000000000000050:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH  7 0000000000000060:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH  8 0000000000000070:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH  9 0000000000000080:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 10 0000000000000090:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 11 00000000000000A0:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 12 00000000000000B0:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 13 00000000000000C0:   48484848 48484848 48970000 00010007 †HHHHHHHHH....... 14 00000000000000D0:   00020000 ††††††††††††††††††††††††††††....

看到內容之后，我們把這條記錄刪掉，然后快速的觀察數據頁的變化，很有意思的。。。。如下圖：

仔細觀察上面的圖，你會看到m_slotCnt=18。。。。你也看到m_GhostRecCnt=1，看這個名字你就知道是“幻象”的意思。。。正因為被

標記為幻象，所以sqlserver的后臺進程會在某個時候把數據正真的刪除掉，比如你過個幾秒之后再查看就能看到真的被清除了。

<2> 分支節(jié)點的變化

　說完葉子節(jié)點，然后我們繼續(xù)看看分支節(jié)點，通過前面的博文，你應該知道在分支節(jié)點中是依次保存著排序后的每個葉子節(jié)點中的最小值，剛好

我刪除了第二個葉子節(jié)點的第一個值，那這個值也正好保存在分支節(jié)點中，那下面一個問題來了，我剛才刪除了ID=72的記錄，那這個ID=72的還會

在分支節(jié)點中保存嗎？？？不用太興奮，我們用數據來說說看，繼續(xù)查看200號數據頁。

二：insert操作

　　我們知道索引都是按照索引列升序的，那當我insert的時候，是不是需要給我插入到排序的指定位置呢？？？比如說我剛才刪除的HHH。。。

數據，這次我再insert的時候，是不是需要給我插入到第二個數據頁的slot0位置呢？？？下面繼續(xù)用數據說話。

1 INSERT INTO dbo.Person VALUES(72,REPLICATE(CHAR(72),200))2 DBCC PAGE(Ctrip,1,201,1)

 1 Slot 0, Offset 0x101c, Length 212, DumpStyle BYTE 2  3 Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 212 4  5 Memory Dump @0x000000000FE5B01C 6  7 0000000000000000:   16686868 68686868 68686868 68686868 †.hhhhhhhhhhhhhhh  8 0000000000000010:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh  9 0000000000000020:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh 10 0000000000000030:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh 11 0000000000000040:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh 12 0000000000000050:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh 13 0000000000000060:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh 14 0000000000000070:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh 15 0000000000000080:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh 16 0000000000000090:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh 17 00000000000000A0:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh 18 00000000000000B0:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh 19 00000000000000C0:   68686868 68686868 68c10000 00010002 †hhhhhhhhh....... 20 00000000000000D0:   00020000 ††††††††††††††††††††††††††††....             21 22 Slot 1, Offset 0x1f04, Length 212, DumpStyle BYTE23 24 Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 21225 26 Memory Dump @0x000000000FE5BF0427 28 0000000000000000:   16484848 48484848 48484848 48484848 †.HHHHHHHHHHHHHHH 29 0000000000000010:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 30 0000000000000020:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 31 0000000000000030:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 32 0000000000000040:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 33 0000000000000050:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 34 0000000000000060:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 35 0000000000000070:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 36 0000000000000080:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 37 0000000000000090:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 38 00000000000000A0:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 39 00000000000000B0:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH 40 00000000000000C0:   48484848 48484848 48c10000 00010015 †HHHHHHHHH....... 41 00000000000000D0:   00020000 ††††††††††††††††††††††††††††....

從上面可以看到，當我再次把刪除的”H"插入到數據頁的時候，發(fā)現(xiàn)“H”在201號數據頁的slot1位置了，有人就奇怪了，，，為什么不在slot0

的？？？仔細想想確實可以告訴我們一個道理，那就是sql是不區(qū)別大小寫的，所以“H”和“h”對sqlserver來說都是一樣的，仔細想想其實還有

一個問題，那就是數據頁分裂，比如說當你insert的數據頁已滿，那這時候該怎么辦呢？sqlserver的手段就是數據頁分裂，將滿頁的一半數據

導出到新分配的數據頁，同樣我也可以做個例子。

1 CREATE TABLE Person(ID INT,NAME CHAR(5) DEFAULT 'xxxxx')2 CREATE INDEX idx_Name ON Person(NAME)3 4 DECLARE @i as int=15 WHILE @i<8016 BEGIN7     INSERT INTO dbo.Person(ID) VALUES(@i)8     SET @i=@i+19 END