【SQL SERVER2005页面存储3之--聚集索引的存储】

                                                       聚集索引的存储

 

一：聚集索引的叶级别存储

聚集索引在叶级别的存储其实就是以数据页的形式存储的,之前几篇关于堆数据行的存储已经都详细说过了,但是这里因为有了聚集索引,

所以还是有个特殊的地方需要注意的--带有唯一标识符的聚集索引行.

我们如果在建表并为其建立聚集索引时,没有为它指定UNIQUE属性,那么系统在键值遇到重复的时候,会自动增加一个字节的字段来保证聚集键值的唯一。

至于为什么要保证它聚集键值的唯一,那么因为非聚集索引在引用它作为书签的时候必须要征求唯一性来取到唯一行

/*----------------------------------------------------------------------

*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†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†CCC  ...  */

--这是插入一条记录后的行记录,跟一般数据行的记录并没有区别

go

--插入条主键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†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†CCC  ...                

Slot 1, Offset 0x79, Length 33, DumpStyle BYTE

00000000:   30001600 41424344 45c80100 00000000 †...ABCDE.......         

00000010:   44444444 20200500 08010021 00010000 †DDD  .....!....         

00000020:   00†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†                        

Slot 2, Offset 0x9a, Length 33, DumpStyle BYTE

00000000:   30001600 41424344 45400000 00000000 †...ABCDE@......         

00000010:   45454545 20200500 08010021 00020000 †EEE  .....!....         

00000020:   00†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†      

*/

--我们来看第二条和第三条他们的都比第一条多了个字节

第二条的最后面个字节->>>>010021 00010000 00

第三条的最后面个字节->>>>010021 00020000 00

分解下第二条:

0100->00000001=1 变成列的数目(原来的表中没有变长列,这列是唯一标识符认为是变长列的原因)

21 00->00100001=33 这列变长列字段结束的位移 即记录的长度

01000000->00000001=1唯一标识符的值为 

对应的我们看到第三条的唯一区别就是 最后的个字节是=2 

    小结论:通过每个记录多出来的个字节 我们就能唯一标识每条记录了

--接下来我再插入几条看下情况

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 我们可以发现

--我们发现当新插入的三条跟上面一样,第二三条是多出了个字节,而且最后唯一标识符是从开始递增,并不是接着上面的开始递增

结论:

当我们在建立聚集索引的时候没有指定唯一性,当聚集键出现重复值的时候,系统会自动为行增加一个字节的边长列标识符。

 

二：聚集索引的非叶级别存储

说到聚集索引的存储,大家可能觉得聚集索引不就是存着数据本身么？确实,在叶级别是如此,但是也和一般的数据页是不一样,上篇没有说到这个点.

另外聚集索引在非叶级别的存储也是很不一样的。

接下来我们来看看它的存储吧。

/*----------------------------------------------------------------------

*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 †0                       

00000020:   20202020 20202020 20202020 20202020 †                        

00000030:   20202020 20202020 20202020 20202020 †    

......

00000250:   20202020 20202020 20202020 20202020 †                        

00000260:   20202020 20040000 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†    ...                 

*/

--我们来分析下简单分析下这里的数据页,

前面的个字节跟以前一样,可以参考...

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 最后四位为 表示都不为NULL

--接下来看下我们的索引页

dbcc page(poofly,5,45,1)

/*

Slot 0, Offset 0x60, Length 12, DumpStyle BYTE

00000000:   06000000 00002600 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?......&.....             

Slot 1, Offset 0xc54, Length 12, DumpStyle BYTE

00000000:   06313030 3133bf02 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?.10013......             

Slot 2, Offset 0xc60, Length 12, DumpStyle BYTE

00000000:   06313030 3236c002 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?.10026......             

.....

Slot 502, Offset 0xc30, Length 12, DumpStyle BYTE

00000000:   06343531 36201501 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?.4516 ..           

 */

--首先说明一点,索引分页的第一条通常是没有意义或者干脆是空的,但是它的部分是正确的.

 2600 0000->Ox0026=38 表示下一层的第一个分页页号 

 PS：看到了么？这里的号页面不就是我们的叶级层的第一个页号么？

 0500->0000000000000101=5表示下一层的文件ID

--接着看第二条索引记录(ps:SQL遍历我们的索引时都是首先与该页上的第二条记录进行比较的,因为第一条的聚集值无效)

 06(状态位A)->00000110 1-3byte位=3 表示索引记录(INDEX_RECORD)

 313030 3133->00110001 00110000 00110000 00110001 00110011 ='10013' 这里的个字节表示下一层分页上的第一个键值

 bf02 0000->Ox02bf=703 跟上面意义 指向下一层的分页号 

 0500->0000000000000101=5表示下一层的文件ID

--第三条跟第二条的区别就在于-5 6-9字节的值(从开始) 前面的表示下一层分页上的第一个键值后面的表示指向下层分页的页号

 313030 3236->=='10026'

 c002 0000->Ox02c0=704 看到了么 上面的指针指向的是页

--最后再来看下最后一条记录第条

 343531 3620->=='3415 '

 1501 0000->Ox0115=277

 

 我们可以大概想到 当你的搜索where [str]='10024'的时候,与第二条的'10026'进行比较,发现小于该键值,

 于是它知道'10024'不在该页的下面的记录可以找到,也说明它的值一定在上面索引条目所指的数据页里,

 所以直接通过第一条的指针进入号页面

 

--我们再来看下我们的根分页

dbcc page(poofly,5,602,1)

/*

Slot 0, Offset 0x60, Length 12, DumpStyle BYTE

Memory Dump @0x6244C060

00000000:   06000000 00002d00 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?......-.....             

Slot 1, Offset 0x6c, Length 12, DumpStyle BYTE

00000000:   06343532 39205b02 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?.4529 [.....  

*/

看第一条的 2d00 0000->==45 正好是下一层的结点页的第一个页号 ...

结论:

1.聚集索引在叶级别存储时候和一般堆在叶级别存储的区别在于拥有聚集索引的列会放到开始存储数据的最前端;

2.聚集索引在非叶级别的索引行含有聚集键+一个字节长的'指针导航'(4字节指向下页+2字节下层分页的文件号)