【SQL Server2005页面存储4之--非聚集索引行在叶级别存储】

来源：互联网发布：徐水区政府网络平台编辑：程序博客网时间：2024/05/09 04:15

非聚集索引行在叶级别存储的时候也分在堆上、在聚集索引的表上.

一：堆上的非聚集索引在叶级别的存储

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

*auther:Poofly

*date:2010.3.14

*VERSION:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

Jul 9 2008 14:43:34

Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )

*转载请注明出处

*更多精彩内容,请进http://blog.csdn.net/feixianxxx

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

--建表(表源技术内幕)

CREATE TABLE NC_Heap_Nodupes (

id int NOT NULL ,

str1 char (5) NOT NULL ,

str2 char (600) NULL );

--在str1上建立非聚集索引

CREATE UNIQUE INDEX idxNC_heap ON NC_Heap_Nodupes (str1);

--插入数据

DECLARE @i int;

SET @i = 1240;

WHILE @i < 1300 BEGIN

INSERT INTO NC_Heap_Nodupes

SELECT @i, cast(@i AS char), cast(@i AS char);

SET @i = @i + 1;

END;

--执行DBCC将结果插入表并显示相关页号

TRUNCATE TABLE sp_table_pages;

INSERT INTO sp_table_pages

EXEC ('dbcc ind ( poofly, NC_Heap_Nodupes, -1)' );

SELECT PageFID, PagePID, IndexID, IndexLevel, PageType

FROM sp_table_pages

WHERE IndexLevel >= 0;

PageFID PagePID IndexID IndexLevel PageType

------- ----------- ------- ---------- --------

5 52 0 0 1 --pagetype 1 数据分页2索引分页

5 54 2 0 2 --IndexID 0为堆 1为聚集索引2-250为非聚集索引

5 952 0 0 1

5 953 0 0 1

5 954 0 0 1

5 955 0 0 1

5 956 0 0 1

我们从结果可以看到由于数据行的量比较少只有行所以我们只需要一个索引页来存储.

IndexID为 PageType为说明它是一个存储非聚集索引的索引分页,IndexLevel为说明是叶级别的

--来看看这个页面的存储特点:

dbcc traceon(3604);

dbcc page(poofly,5,54,1)

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

00000000: 06313234 30203400 00000500 0000††††††.1240 4.......

Slot 1, Offset 0x6e, Length 14, DumpStyle BYTE

00000000: 06313234 31203400 00000500 0100††††††.1241 4.......

....

Slot 59, Offset 0x39a, Length 14, DumpStyle BYTE

00000000: 06313239 3920bc03 00000500 0400††††††.1299 ........

--先来看第一条索引行Slot 0

06->状态位A(没有状态位B在索引行中)

313234 3020->=='1240 ' 它是一个非聚集值

3400 0000->Ox0034=52 这个书签的分页指针指向的页号 52

0500->书签的文件ID

0000->书签的槽号(行号,从开始)

--第二条和第一条的区别

313234 3120->=='1241 '

3400 00000500 0100->这个字节其实就是一个完整的书签我们发现这个和上面记录的区别就是最后的书签的槽号(行号)不同,因为是条记录嘛

--最后一条索引记录

313239 3920->=='1299 ' 我们发现它的值就是我们表内的最后一条记录的非聚集值

bc03 0000->Ox03bc==956 我们可以看这个页号,这个存储我们表内最后一条记录的页号不就是么?

0500->文件号

0400->说明我们的这条记录在页里面是第五条记录

--我们来验证下上面的假设

dbcc page(poofly,5,956,1)

Slot 4, Offset 0xa00, Length 616, DumpStyle BYTE ...

00000000: 10006502 13050000 31323939 20313239 †.e.....1299 129

00000010: 39202020 20202020 20202020 20202020 †

00000020: 20202020 20202020 20202020 20202020 †

、。。。。。

00000250: 20202020 20202020 20202020 20202020 †

00000260: 20202020 20030000 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†

我们拿出第四条来看看我们的str1值的大小吧?

注意看第个字节-第字节 3132303920=='1299 ',而它对应的行数正是第五行 Slot 4

验证完毕,确实就如索引行记录的那样,利用书签,可以精确地找到我们的值在数据页的位置。

结论:

这里需要注意的是索引行的不包含状态位B.书签中的行号是从开始的.

非聚集索引在叶级别的索引行是存储了了它的非聚集键值和一个书签(指向包含非聚集值数据页的一个指针)

二：在含聚集索引的表上非聚集索引在叶级别的存储

这里也分种情况：一种是非聚集索引不包含聚集索引列,还有一种是非聚集索引列中包含了聚集索引列

--先来看第一种非聚集索引不包含聚集索引列

--建表(表源技术内幕系列)

CREATE TABLE NC_Nodupes (

id int NOT NULL ,

str1 char (5) NOT NULL ,

str2 varchar (10) NULL

);

--在str2上建立唯一聚集索引,在str1上建立唯一非聚集索引

CREATE UNIQUE CLUSTERED INDEX idxcl_str2 on NC_Nodupes (str2);

CREATE UNIQUE INDEX idxNC ON NC_Nodupes (str1);

--插入数据

DECLARE @i int;

SET @i = 1240;

WHILE @i < 1300 BEGIN

INSERT INTO NC_Nodupes

SELECT @i, cast(@i AS char),

cast(cast(@i * rand() AS int) as char); --打乱了str1的排序

SET @i = @i + 1;

END;

--检查页面分布

TRUNCATE TABLE sp_table_pages;

INSERT INTO sp_table_pages

EXEC ('dbcc ind (poofly, NC_Nodupes, -1)');

SELECT PageFID, PagePID, IndexID, IndexLevel, PageType

FROM sp_table_pages

WHERE IndexLevel >= 0;

PageFID PagePID IndexID IndexLevel PageType

------- ----------- ------- ---------- --------

5 957 1 0 1 --聚集索引对应的页面就是数据分页

5 959 2 0 2 --非聚集索引对应的页面是索引分页

dbcc traceon(3604);

--我们先来看下非聚集索引在聚集索引表上的存储

DBCC PAGE('POOFLY',5,959,1)

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

00000000: 36313234 30200200 00010017 00313531 †1240 .......151

00000010: 20202020 202020†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?

Slot 1, Offset 0x77, Length 23, DumpStyle BYTE

00000000: 36313234 31200200 00010017 00313930 †1241 .......190

00000010: 20202020 202020†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?

....

Slot 57, Offset 0x57f, Length 23, DumpStyle BYTE

00000000: 36313239 39200200 00010017 00363533 †1299 .......653

00000010: 20202020 202020†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?

--先来看第一条索引行

36->状态位A:00110110 1-3位=3表示索引记录,第位表示存在NULL位图第五位表示存在变长列

313234 3020->'1240 '

0200->表的字段数目 2行

00->00000000NULL位图最后四位为表示记录没有一个COL为NULL

0100->00000001 =1 变长列的数目

17 00->00000000 00010111=16+7=23 表示第一列变长列的结束位置因为只有一列变长列所以也就是列长了

313531 20202020 202020->=='151 '这里的这个值是聚集值str2

--再来看看聚集索引键是非聚集索引键的一部分

CREATE TABLE NC_Overlap (

id int NOT NULL ,

str1 char (5) NOT NULL ,

str2 char (10) NULL

);

--注意这里的非聚集索引中包含了聚集索引列str2

CREATE UNIQUE CLUSTERED INDEX idxCL_overlap ON NC_Overlap (str2);

CREATE UNIQUE INDEX idxNC_overlap ON NC_Overlap (str1, str2);

--用上面同样的方法插入数据后,查看PAGE

PageFID PagePID IndexID IndexLevel PageType

------- ----------- ------- ---------- --------

5 963 1 0 1

5 965 2 0 2

DBCC PAGE('POOFLY',5,965,1)

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

00000000: 16313234 30203337 32202020 20202020 †.1240 372

00000010: 020000†††††††††††††††††††††††††††††††...

。。

结论:

我们可以看到它的索引页面分布和上面的情况其实几乎一样,唯一的区别就在于STR2的使用

在上面的页面中,由于STR2只作为聚集索引,所以在索引行中仅仅起到一个书签的作用

在这个情况的页面中,STR2不仅作为书签,还因为它本身是非聚集索引的原因而作为键值存储在上面，

虽然起了个作用,但是它只作为键值出现在行中(因为后面还有),并没有重复出现.但是还是起到了书签作用.