MyException - 我的异常网
当前位置:我的异常网» SQL » Sql Server之旅——第十站 见见DML操作对索引的影响

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

www.MyException.Cn  网友分享于:2015-02-08  浏览:0次
Sql Server之旅——第十站 看看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槽位的内容是啥样,如下图:

 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就是这两个操作的组合,对不对。。。所以也没什么好说的。

 

好了,夜深了,洗洗睡了~

 

文章评论

代码女神横空出世
代码女神横空出世
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
编程语言是女人
编程语言是女人
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
程序员的鄙视链
程序员的鄙视链
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
那些争议最大的编程观点
那些争议最大的编程观点
老程序员的下场
老程序员的下场
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
程序员都该阅读的书
程序员都该阅读的书
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
10个调试和排错的小建议
10个调试和排错的小建议
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
程序员必看的十大电影
程序员必看的十大电影
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
鲜为人知的编程真相
鲜为人知的编程真相
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
程序员应该关注的一些事儿
程序员应该关注的一些事儿
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
程序员和编码员之间的区别
程序员和编码员之间的区别
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
 程序员的样子
程序员的样子
为什么程序员都是夜猫子
为什么程序员都是夜猫子
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
旅行,写作,编程
旅行,写作,编程
我是如何打败拖延症的
我是如何打败拖延症的
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
中美印日四国程序员比较
中美印日四国程序员比较
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
漫画:程序员的工作
漫画:程序员的工作
每天工作4小时的程序员
每天工作4小时的程序员
一个程序员的时间管理
一个程序员的时间管理
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有