[推荐] 【SQL SERVER2005页面存储3之--聚集索引的存储】 100feixianxxx]

发布时间:2016-12-10 18:48:38 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"[推荐] 【SQL SERVER2005页面存储3之--聚集索引的存储】 100feixianxxx]",主要涉及到[推荐] 【SQL SERVER2005页面存储3之--聚集索引的存储】 100feixianxxx]方面的内容,对于[推荐] 【SQL SERVER2005页面存储3之--聚集索引的存储】 100feixianxxx]感兴趣的同学可以参考一下。

发帖无法排版,详见博客http://blog.csdn.net/feixianxxx/archive/2010/04/14/5486513.aspx 一:聚集索引在叶级别的存储 聚集索引在叶级别的存储其实就是以数据页的形式存储的,之前几篇关于堆数据行的存储已经都详细说过了,但是这里因为有了聚集索引, 所以还是有个特殊的地方需要注意的--带有唯一标识符的聚集索引行. 我们如果在建表并为其建立聚集索引时,没有为它指定UNIQUE属性,那么系统在键值遇到重复的时候,会自动增加一个4字节的字段来保证聚集键值的唯一。 至于为什么要保证它聚集键值的唯一,那么因为非聚集索引在引用它作为书签的时候必须要征求唯一性来取到唯一行 /*---------------------------------------------------------------------- *auther:Poofly *date:2010.3.14 *VERSION: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)  Jul  9 2008 14:43:34  Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: ) *转载请注明出处 *更多精彩内容,请进http://blog.csdn.net/feixianxxx ------------------------------------------------------------------------*/ --建表(表源来自技术内幕) CREATE TABLE Clustered_Dupes    (Col1 char(5)   NOT NULL,    Col2 int     NOT NULL,    Col3 char(3)   NULL,    Col4 char(6)   NOT NULL); GO --在col1上建不唯一的聚集索引 CREATE CLUSTERED INDEX Cl_dupes_col1 ON Clustered_Dupes(col1); go --插入数据 INSERT Clustered_Dupes VALUES ('ABCDE', 123, null, 'CCCC'); GO --运行DBCC 查看各个类型页号 TRUNCATE TABLE sp_table_pages; INSERT INTO sp_table_pages     EXEC ('dbcc ind ( tempdb, Clustered_Dupes, -1)'  ); SELECT PageFID, PagePID FROM sp_table_pages    WHERE PageType = 1 and PrevPageFID = 0 and PrevPagePID = 0; GO dbcc traceon(3604); --查看数据页情况 dbcc page (tempdb,1,151,1) /* 00000000:   10001600 41424344 457b0000 00000000 †....ABCDE{......          00000010:   43434343 20200500 08†††††††††††††††††CCCC  ...  */ --这是插入一条记录后的行记录,跟一般数据行的记录并没有区别 go --插入2条主键COL1重复的记录再次查看记录 INSERT Clustered_Dupes VALUES ('ABCDE', 456, null, 'DDDD'); INSERT Clustered_Dupes VALUES ('ABCDE', 64, null, 'EEEE'); go --再次查看 dbcc page (tempdb,1,151,1) /* Slot 0, Offset 0x60, Length 25, DumpStyle BYTE 00000000:   10001600 41424344 457b0000 00000000 †....ABCDE{......          00000010:   43434343 20200500 08†††††††††††††††††CCCC  ...                 Slot 1, Offset 0x79, Length 33, DumpStyle BYTE 00000000:   30001600 41424344 45c80100 00000000 †0...ABCDE.......          00000010:   44444444 20200500 08010021 00010000 †DDDD  .....!....          00000020:   00†††††††††††††††††††††††††††††††††††.                         Slot 2, Offset 0x9a, Length 33, DumpStyle BYTE 00000000:   30001600 41424344 45400000 00000000 †0...ABCDE@......          00000010:   45454545 20200500 08010021 00020000 †EEEE  .....!....          00000020:   00†††††††††††††††††††††††††††††††††††.       */ --我们来看第二条和第三条 他们的都比第一条多了8个字节 第二条的最后面8个字节->>>>010021 00010000 00 第三条的最后面8个字节->>>>010021 00020000 00 分解下第二条: 0100->00000001=1 变成列的数目(原来的表中没有变长列,这列是唯一标识符认为是变长列的原因) 21 00->00100001=33 这列变长列字段结束的位移 即记录的长度 01000000->00000001=1唯一标识符的值为1  对应的我们看到第三条的唯一区别就是 最后的4个字节是02000000=2      小结论:通过每个记录多出来的8个字节 我们就能唯一标识每条记录了 --接下来我再插入几条看下情况 INSERT Clustered_Dupes VALUES ('ABCD', 456, null, 'DDDD'); INSERT Clustered_Dupes VALUES ('ABCD', 64, null, 'EEEE') INSERT Clustered_Dupes VALUES ('ABCD', 6, null, 'EEEE') --再次读PAGE 我们可以发现 --我们发现当新插入的三条跟上面一样,第二 三条是多出了8个字节,而且最后唯一标识符是从1 开始递增 ,并不是接着上面的2开始递增 结论: 当我们在建立聚集索引的时候没有指定唯一性,当聚集键出现重复值的时候,系统会自动为行增加一个4字节的边长列标识符。

二:聚集索引在非叶级别的存储说到聚集索引的存储,大家可能觉得聚集索引不就是存着数据本身么?确实,在叶级别是如此,但是也和一般的数据页是不一样,上篇没有说到这个点. 另外聚集索引在非叶级别的存储也是很不一样的。 接下来我们来看看它的存储吧。 /*---------------------------------------------------------------------- *auther:Poofly *date:2010.3.14 *VERSION: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)  Jul  9 2008 14:43:34  Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: ) *转载请注明出处 *更多精彩内容,请进http://blog.csdn.net/feixianxxx ------------------------------------------------------------------------*/ --建表(表源来自技术内幕) CREATE TABLE clustered_nodupes (   id int NOT NULL ,   str1 char (5) NOT NULL ,   str2 char (600) NULL );  GO --在STR1上建立聚集索引(这列是第二列) CREATE CLUSTERED INDEX idxCL ON Clustered_Nodupes(str1);  GO --插入测试数据 SET NOCOUNT ON; GO DECLARE @i int; SET @i = 1240; WHILE @i < 13000 BEGIN   INSERT INTO Clustered_Nodupes     SELECT @i, cast(@i AS char), cast(@i AS char);   SET @i = @i + 1;  END;  GO TRUNCATE TABLE sp_table_pages; INSERT INTO sp_table_pages     EXEC ('dbcc ind ( poofly, Clustered_Nodupes, -1)'  ); SELECT PageFID, PagePID, IndexLevel, PageType FROM sp_table_pages WHERE IndexId=1 and IndexLevel >= 0         and PrevPageFID = 0 and PrevPagePID = 0; /* PageFID PagePID     IndexLevel PageType ------- ----------- ---------- -------- 5       38          0          1            --PageType1代表数据分页 2代表索引分页 5       45          1          2            --IndexLevel NULL代表索引的IAM页 O代表叶级别,>0代表索引层次 5       602         2          2        */ GO dbcc traceon(3604); dbcc page(poofly,5,38,1)--数据页的读取 /* Slot 0, Offset 0x60, Length 616, DumpStyle BYTE Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 616 Memory Dump @0x60FDC060 00000000:   10006502 31303030 30102700 00313030 †..e.10000.'..100          00000010:   30302020 20202020 20202020 20202020 †00                        00000020:   20202020 20202020 20202020 20202020 †                          00000030:   20202020 20202020 20202020 20202020 †      ...... 00000250:   20202020 20202020 20202020 20202020 †                          00000260:   20202020 20040000 †††††††††††††††††††     ...                  */ --我们来分析下简单分析下这里的数据页, 前面的4个字节跟以前一样,可以参考... 31303030 30->它表示的是我们的主键str1的值(注意这里把本来建表的第二列 因为聚集索引的原因放前面了),还有这里的读取是一个个字节读 31='1' 30='0'  所以五个字节就是'10000' 这里不是'1024'的原因呢是因为我们的主键str是char类型 所以排序的时候'10000'是排在'1240'前面做为第一条记录. 102700 00->00000000000000000010011100010000=10000 是我们的ID值 注意我们读取数字的时候要倒过来再转,它是先存储低字节的。 313030 30302020 20202020 20202020......20202020 20->'10000   ..  'char类型所以后面的字节都用空格 20是空格的ASCII值 0400->0000000000000100=4 列数 00->00000000 最后四位为0 表示都不为NULL --接下来看下我们的索引页 dbcc page(poofly,5,45,1) /* Slot 0, Offset 0x60, Length 12, DumpStyle BYTE 00000000:   06000000 00002600 00000500 ††††††††††......&.....              Slot 1, Offset 0xc54, Length 12, DumpStyle BYTE 00000000:   06313030 3133bf02 00000500 ††††††††††.10013......              Slot 2, Offset 0xc60, Length 12, DumpStyle BYTE 00000000:   06313030 3236c002 00000500 ††††††††††.10026......              ..... Slot 502, Offset 0xc30, Length 12, DumpStyle BYTE 00000000:   06343531 36201501 00000500 ††††††††††.4516 ..             */ --首先说明一点,索引分页的第一条通常是没有意义或者干脆是空的,但是它的部分是正确的.  2600 0000->Ox0026=38 表示下一层的第一个分页页号   PS:看到了么?这里的38号页面不就是我们的叶级层的第一个页号么?  0500->0000000000000101=5表示下一层的文件ID --接着看第二条索引记录(ps:SQL遍历我们的索引时都是首先与该页上的第二条记录进行比较的,因为第一条的聚集值无效)  06(状态位A)->00000110 1-3byte位011=3 表示索引记录(INDEX_RECORD)  313030 3133->00110001 00110000 00110000 00110001 00110011 ='10013' 这里的5个字节表示下一层分页上的第一个键值  bf02 0000->Ox02bf=703 跟上面意义 指向下一层的分页号   0500->0000000000000101=5表示下一层的文件ID --第三条跟第二条的区别就在于1-5 6-9字节的值(从0开始) 前面的表示下一层分页上的第一个键值 后面的表示指向下层分页的页号  313030 3236->=='10026'  c002 0000->Ox02c0=704 看到了么 上面的指针指向的是703页 --最后再来看下最后一条记录第503条  343531 3620->=='3415 '  1501 0000->Ox0115=277    我们可以大概想到 当你的搜索where [str]='10024'的时候,与第二条的'10026'进行比较,发现小于该键值,  于是它知道'10024'不在该页的下面的记录可以找到,也说明它的值一定在上面索引条目所指的数据页里,  所以直接通过第一条的指针进入38号页面   --我们再来看下我们的根分页 dbcc page(poofly,5,602,1) /* Slot 0, Offset 0x60, Length 12, DumpStyle BYTE Memory Dump @0x6244C060 00000000:   06000000 00002d00 00000500 ††††††††††......-.....              Slot 1, Offset 0x6c, Length 12, DumpStyle BYTE 00000000:   06343532 39205b02 00000500 ††††††††††.4529 [.....   */ 看第一条的 2d00 0000->==45 正好是下一层的结点页的第一个页号 ... 结论: 1.聚集索引在叶级别存储时候和一般堆在叶级别存储的区别在于拥有聚集索引的列会放到开始存储数据的最前端; 2.聚集索引在非叶级别的索引行含有聚集键+一个6字节长的'指针导航'(4字节指向下页+2字节下层分页的文件号)

sff.

引用 2 楼 fredrickhu 的回复:sff. sf没了。。。

sf......

嘿嘿.............

好东西就要收藏

高手用VB给整一个替换软件啊

好东西

,,,,

sf问题

....

学习,收藏!

不怎么懂先收藏

这是哪个内幕上的 存储引擎?

经典经典经典经典经典经典经典经典经典经典经典经典经典

很好!收藏!~

好东西就要收藏

过来凑凑热闹

顶起,接分.

我是来接分的

顶一个,很发

很好  很强大!、、

看不懂啊  !!看来 还要 努力 学习 呢 啊  !!

学习,收藏!

 不懂.....

引用 1 楼 feixianxxx 的回复:二:聚集索引在非叶级别的存储 SQL code 说到聚集索引的存储,大家可能觉得聚集索引不就是存着数据本身么?确实,在叶级别是如此,但是也和一般的数据页是不一样,上篇没有说到这个点. 另外聚集索引在非叶级别的存储也是很不一样的。 接下来我们来看看它的存储吧。 /*-----------------------------------------------------------…… 学习了

学习了~~~~~~~~~~~~~~~~~

一小撮不明真相的烤鸭路过 T-MAC越来越强了

不错,分享了

第一篇看懂一些,最近对索引有点感兴趣了

谢谢分享

顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶

比数据列上的存储更进一步了。 恭喜楼主!!!!!

引用 31 楼 guguda2008 的回复:一小撮不明真相的烤鸭路过 T-MAC越来越强了 纯学习的路过,顶

学习。

好东西 收藏!

上来拿分,以示存在.

引用 7 楼 crackingx 的回复:高手用VB给整一个替换软件啊 ...无解

mark ,有时间静下心来看看

学习下啊

继续努力

非常感谢, 经典!!!!!!

学习,收藏了

学习学习!!

good learn

学习了,呵呵~

引用 15 楼 bancxc 的回复:这是哪个内幕上的 存储引擎? 是的 上面也有相关介绍

每天拿分 以示存在

学习了,感谢楼主分享,

学习了,感谢楼主分享

谢谢了

是对吗

学习了

呵呵,学习了

说得不错,觉得内容有点深.

顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶

学习,好东东

看不懂,收藏!

好像要装.Net Framework 2.0

知识贴收藏

本帖最后由 feixianxxx 于 2010-04-16 19:12:12 编辑

知识贴收藏

get

的发生的非

收藏了

好东西大家用

加油!!!!!!!!!!

走前一顶

很好。。。。

每天回帖即可获得10分可用分!小技巧    呵呵

每天回帖即可获得10分可用分!小技巧 呵呵

暂时用不到,学习学习..

学习学习

上一篇:mysql从一个表中复制到另一个表中 30wuda00001]
下一篇:sql 存储过程中字段可以做参数吗? 40q312977]

相关文章

相关评论