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

摘要：從上面的圖中大概可以看到，當我插入完畢後，4個索引數據頁就出來了，其中PID=200的爲分支數據頁，其他三個爲葉子節點數據頁，編號分別爲175,201,202，然後我就挑選第二個葉子節點數據頁201號，看看裏面的數據是啥樣的。接下來，我導出126號數據頁的記錄，可以看到它的範圍是1-449，如下圖：。

我們都知道建索引是需要謹慎的，當只有利大於弊的時候才適合建，同時也知道建索引是需要維護成本的，這個維護也就在於DML操作，下面具體看看到底DML對索引都有哪些內幕。。。。

一：delete操作

現在大家都已經知道索引是以B樹的形式存在，既然是B樹就要給大家展示一下葉子節點和分支結點，先準備點測試數據，如下代碼：

CREATE TABLE Person(ID INT,NAME CHAR(200))
CREATE INDEX idx_Name ON Person(NAME)

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

1. 葉子結點的變化

從上面的圖中大概可以看到，當我插入完畢後，4個索引數據頁就出來了，其中PID=200的爲分支數據頁，其他三個爲葉子節點數據頁，編號分別爲175,201,202，然後我就挑選第二個葉子節點數據頁201號，看看裏面的數據是啥樣的。

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

接下來，大家再看看slot0槽位的內容是啥樣，如下代碼：

0000000000000000:   16484848 48484848 48484848 48484848 †.HHHHHHHHHHHHHHH
0000000000000010:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000020:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000030:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000040:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000050:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000060:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000070:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000080:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000090:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
00000000000000A0:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
00000000000000B0:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
00000000000000C0:   48484848 48484848 48970000 00010007 †HHHHHHHHH.......
00000000000000D0:   00020000 ††††††††††††††††††††††††††††....

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

仔細觀察上面的圖，你會看到m_slotCnt=18。。。。你也看到m_ghostRecCnt=1，看這個名字你就知道是“幻象”的意思。。。正因爲被標記爲幻象，是因爲sqlserver的後臺進程會在某個時候把數據正真的刪除掉，比如你過個幾秒之後再查看就能看到真的被清除了。

2. 分支節點的變化

說完葉子節點，然後我們繼續看看分支節點，通過前面的博文，你應該知道在分支節點中是依次保存着排序後的每個葉子節點中的最小值，剛好我刪除了第二個葉子節點的第一個值，那這個值也正好保存在分支節點中，下面一個問題來了，我剛纔刪除了ID=72的記錄，那這條記錄還會在分支節點中保存嗎？？？不用太興奮，用數據說話，繼續查看200號數據頁。

可以看到分支節點是不會刪除這條記錄的。

二：insert操作

索引都是按照索引列升序的，當我insert的時候是不是需要給我插入到排序的指定位置呢？？？比如說我剛纔刪除的HHH。。。數據，這次我再insert的時候，是不是需要給我插入到第二個數據頁的slot0位置呢？？？下面繼續用數據說話。

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


Slot 0, Offset 0x101c, Length 212, DumpStyle BYTE

Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 212

Memory Dump @0x000000000FE5B01C

0000000000000000:   16686868 68686868 68686868 68686868 †.hhhhhhhhhhhhhhh
0000000000000010:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh
0000000000000020:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh
0000000000000030:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh
0000000000000040:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh
0000000000000050:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh
0000000000000060:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh
0000000000000070:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh
0000000000000080:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh
0000000000000090:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh
00000000000000A0:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh
00000000000000B0:   68686868 68686868 68686868 68686868 †hhhhhhhhhhhhhhhh
00000000000000C0:   68686868 68686868 68c10000 00010002 †hhhhhhhhh.......
00000000000000D0:   00020000 ††††††††††††††††††††††††††††....

Slot 1, Offset 0x1f04, Length 212, DumpStyle BYTE

Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 212

Memory Dump @0x000000000FE5BF04

0000000000000000:   16484848 48484848 48484848 48484848 †.HHHHHHHHHHHHHHH
0000000000000010:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000020:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000030:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000040:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000050:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000060:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000070:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000080:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
0000000000000090:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
00000000000000A0:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
00000000000000B0:   48484848 48484848 48484848 48484848 †HHHHHHHHHHHHHHHH
00000000000000C0:   48484848 48484848 48c10000 00010015 †HHHHHHHHH.......
00000000000000D0:   00020000 ††††††††††††††††††††††††††††....

從上面可以看到，當我再次把刪除的”H"插入到數據頁的時候，發現“H”在201號數據頁的slot1位置了，有人就奇怪了，，，爲什麼不在slot0 的？？？

仔細想想確實是這麼一個道理，那就是sql是不區別大小寫的，“H”和“h”對sqlserver來說都是一樣，這裏還有一個問題就是數據頁分裂，比如說當你insert的數據頁已滿，那這時候該怎麼辦呢？sqlserver的手段就是數據頁分裂，將滿頁的一半數據導出到新分配的數據頁，同樣我也可以做個例子。

CREATE TABLE Person(ID INT,NAME CHAR(5) DEFAULT 'xxxxx')
CREATE INDEX idx_Name ON Person(NAME)

DECLARE @i as int=1
WHILE @i<801
BEGIN
    INSERT INTO dbo.Person(ID) VALUES(@i)
    SET @i=@i+1
END