SQL Server之旅（10）：看看DML操作對索引的影響
原文出處：
一線碼農
我們都知道建索引是需要謹慎的，當只有利大于弊的時候才適合建，我們也知道建索引是需要維護成本的，這個維護也就在于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的記錄，那這個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
接下來，我導出126號數據頁的記錄，可以看到它的范圍是1-449，如下圖：
下面我要做的事情就是插入一個ID在1-449范圍的一條記錄，這樣的話就會造成數據頁分裂了，對不對。
可以看到，現在多了一個192號數據頁，是不是很有意思，哈哈~~~然后我就非常好奇的再次導出126，192號數據頁，看看數據是不是只剩一半啦~~~
三：update操作
如果你看懂了上面的insert和delete，那么update就是這兩個操作的組合，對不對。。。所以也沒什么好說的。
好了，夜深了，洗洗睡了~