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

一線碼農發表於2015-02-06
SQLServer索引

　　我們都知道建索引是需要謹慎的，當只有利大於弊的時候才適合建，我們也知道建索引是需要維護成本的，這個維護也就在於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槽位的內容是啥樣，如下圖：

 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> 分支節點的變化

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

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

在分支節點中儲存嗎？？？不用太興奮，我們用資料來說說看，繼續檢視200號資料頁。

 

二：insert操作

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

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

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 BYTE
23 
24 Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 212
25 
26 Memory Dump @0x000000000FE5BF04
27 
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"插入到資料頁的時候，發現“H”在201號資料頁的slot1位置了，有人就奇怪了，，，為什麼不在slot0

的？？？仔細想想確實可以告訴我們一個道理，那就是sql是不區別大小寫的，所以“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=1
5 WHILE @i<801
6 BEGIN
7     INSERT INTO dbo.Person(ID) VALUES(@i)
8     SET @i=@i+1
9 END

接下來，我匯出126號資料頁的記錄，可以看到它的範圍是1-449，如下圖：

下面我要做的事情就是插入一個ID在1-449範圍的一條記錄，這樣的話就會造成資料頁分裂了，對不對。

可以看到，現在多了一個192號資料頁，是不是很有意思，哈哈~~~然後我就非常好奇的再次匯出126，192號資料頁，看看資料是不是隻剩

一半啦~~~

 

三：update操作

　　如果你看懂了上面的insert和delete，那麼update就是這兩個操作的組合，對不對。。。所以也沒什麼好說的。

 

好了，夜深了，洗洗睡了~

 

相關文章