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

2022-01-06 21:25:58

　　我们都知道建索引是需要谨慎的，当只有利大于弊的时候才适合建，我们也知道建索引是需要维护成本的，这个维护也就在于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就是这两个操作的组合，对不对。。。所以也没什么好说的。

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

码农公寓

相关文章