(4.6)数据页深入探索--内部探索聚集索引

转自:http://www.cnblogs.com/huangxincheng/category/555253.html

查看《sql server之旅》

dbcc ind,dbcc traceon,dbcc page数据页内部探索聚集索引

1:数据页

    这个学sqlserver的没有理由说不知道,我们的记录都是以数据页形式存储的,而且还应该知道数据页的大小是8k。。。。那数据页在哪里?我可以

让你眼见为实。

乍一看我画了好多,千万不要怕,不要以为画的多,就以为高深了。。。我简单的剖析下。

<1>:dbcc ind 命令

 你要是想看数据页的相关情况,sqlserver还真提供了专用命令dbcc 满足你,你可能会问sqlserver中有提供ind命令的参数吗?告诉你吧,还真有

的,不过这个要开启2588跟踪,就像下面这样。

  <2>:PageFID,PagePID,IAMFID

  刚才也说了,数据页有很多种,默认说的都是表数据页,其实还有IAM数据页,没什么稀奇的,IAM就是用来跟踪表数据页的,所以上面的图中,

IAMFID字段为Null的记录就是IAM页,下面的PagePID=78的,就是表数据页。

 

2.查看数据页

  为避免大家糊涂了,我先还是说说数据页内部结构大概是个什么样子,好让大家有个整体印象。

 从图中可以看到,在数据页的尾部是有很多槽位的,这些槽位指向了Data区域中一条条实际记录的地址,所以说表扫描,其实就是扫这些Slot槽位,

还是拿上面的Person表中的三条记录来说,他们都是保存在78号数据页中,现在出于好奇心把78号数据页导出来,说干就干。。。。很简单,你需

要做两件事情:

<1>开启3604跟踪: dbcc traceon(3604)

<2>使用dbcc page 命令导出1号文件下面的78号数据页(pageFID:pagePID)=(1:78),就像下面这样。。。

 

数据页头(PAGE HEADER):

 

数据内容(Page Data): 

 

数据槽位(Page Slot):

 

 

有没有看到上面(0,1,2)三个槽位,并且都有相应的偏移地址(0x7e,0x92,0xba),这个地址就指向了Data区域实际记录的偏移地址。

好了,到此为止吧,不能再往下说了,洗洗睡了。

 

 

 

 

说到聚集索引,我想每个码农都明白,但是也有很多像我这样的猥程序员,只能用死记硬背来解决这个问题,什么表中只能建一个聚集索引,

然后又扯到了目录查找来帮助读者记忆。。。。问题就在这里,我们不是学文科,,,不需要去死记硬背,,,我们需要的就是能看到在眼里面的

真实东西。。。。。我们都喜欢聚集索引,因为它能够把无序的堆表记录变成有序,还玩起了B树。。。这样就把复杂度从N降低到了LogMN。。。

这样的话逻辑读,物理读就下来了。

 

一:现象

1:无索引的情况

  还是老规矩,看个例子感受下,首先我有一个Product表,里面没有任何索引,如下图:

 

从上图中,我悲剧的看到了,物理读是9次,也就说明走了9次硬盘,你也可以想到,走硬盘的目的是为了拿数据,逻辑读有1636次,要注意的是这里

的”次“是“页”的意思,也就是在内存中走了1636个数据页,我用dbcc ind 给你看一下,是不是有1636个表数据页。

这里有1637个数据页的原因是第一个是IAM跟踪页。

 

2:有聚集索引的情况

     下面我在Product表中建一个product_idx_productid的聚集索引,然后再次看看io情况,如下图:

当你看到这个”逻辑读“为3次的时候,你是不是已经疯了。。。在多达1636个数据页中找到目标数据,只需3次。。。。这个在算法盲看来是不是神

仙下凡???当然,,,此物天上有,人间也有。。。既然有,就应该有一种非常强烈的探索欲。。。。看看这里面到底是怎么玩的。。。。。。

 

二:探索原理

1: 探索叶子节点

  刚才也说了,聚集索引玩的就是B树,既然是B树,那就有叶子节点和分支节点,专业术语就是度为0的为叶子节点,度>0的叫做分支节点。。。。

我想你也听说了,聚集索引是将索引列数据进行排序后放入B树,那为了让你眼见为实,我先建立一个ID无序的3条记录。

insert into Person values(2,'bbbbb')
insert into Person values(3,'ccccc')
insert into Person values(1,'aaaaa')

然后我用dbcc ind 命令查看下3条记录在哪个数据页中,如图:

从图中可以看到,我的三条记录是放在148号数据页中的,然后我导出148号数据页,看看内容是什么。

dbcc traceon(3604)
dbcc page(Ctrip,1,148,1)

从上图中,我们看到了”数据页“中的各个槽位的指向是按照表中的实际存储记录来的,好了,下面我创建个聚集索引,看看实际数据是不是真的有序了?

create clustered index Ctrip_idx_ID on Person(ID)

不过在这里有个有趣的问题,我的148号”表数据页“哪去了???也是够奇葩的,换来的确实173号索引页,那为了保证数据完整性,应该是把

148号数据页的内容灌到173索引页里面去了吧???? 没关系,验证一下。

1 dbcc traceon(3604)
2 dbcc page(Ctrip,1,173,1)

通过上面的图,有没有直观的感觉到? 数据现在已经是aaaaa,bbbbb,ccccc的模式了。。。有序啦。。。。同时索引页中也保存了148号数据

页的字段值,比如ID,Name信息,拿下面的slot0槽位举例:

 

 

到此为止,我想你对叶子节点的内容有了个大概的认识,起码没有让你死记硬背了~~~

 

2 :探索分支节点

  为了让你看到分支节点,我得多灌一些数据进去,好歹要让数据撑破一个索引数据页,这样分支节点索引数据页就出来了,看下面的例子:

从图中可以看到,当我插入1000条数据的时候,已经出现了一个分支节点(120号索引数据页),三个叶子节点(173,121,126),叶子

节点的数据页内容我也说过了,现在我很好奇”分支节点“中保存着什么内容???我好兴奋,我要导出120号索引数据页了。。。

1 dbcc traceon(3604)
2 dbcc page(Ctrip,1,120,1)

简单分析下slot0:06000000 00ad0000 000100 的内容

00000000:叶子索引页中的最小key值(这里有点特殊,除一行记录不是保存最小值以外,其余都是的),转换为十进制就是0。

ad000000:叶子索引页的页号,转换为十进制就是173。

0100:叶子索引页的文件号,转换为十进制就是1.

不过通过分析,我们看到了,其实分支节点中保存着有两个值,一个childpage的minkey,一个childpage的pageid,同理,其他的槽位也是这样。

我们换个参数命令,让结果更直观点,记录中就是保存着”pageID“和”minKey“。

 

这样的话,我脑海中就有一张图出来了,不知道你现在是否有了????

通过上面的分析,除了第一行记录不是保存子索引页中最小key的值外,其他记录都是提取子索引页中的最小索引键值,这一点要注意。。。

也许对sqlserver团队来说,只要判断小于449的话就直接去(1:173)数据页,小于889的直接去(1:121)数据页就可以啦。。。

 

当你看到这里的时候,不知道你是否已经明白,为什么表中只能有一个聚集索引呢???好了,乱鸡巴扯了好多,希望对你有所帮助。

 

posted @ 2018-08-07 18:53  郭大侠1  阅读(262)  评论(0编辑  收藏  举报