【SQL Server2005頁面儲存5之--非聚集索引行在非葉級別儲存】

非聚集索引的非葉級的索引行通過非聚集鍵值和指標去遍歷到下層的非聚集索引頁來查詢實際資料.

這裡有種情況,

一種是這個非聚集索引是具有唯一性的,

還有一種則相反(當然也可以再細分,比如聚集索引是否存在,聚集索引是否唯一等)

先來說說當非聚集索引定義有唯一性的情況

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

*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

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

--建表(表源來自技術內幕)

GO

CREATE TABLE NC_JNodes (

id int NOT NULL ,

str1 char (5) NOT NULL ,

str2 varchar (10) NULL

);

GO

--在str2上建立唯一性的聚集索引,在str1上建立具有唯一性的非聚集索引

CREATE UNIQUE CLUSTERED INDEX idxcl_str2 on NC_JNodes (str2);

CREATE UNIQUE INDEX idxNC ON NC_JNodes (str1);

GO

--插入資料

DECLARE @i int;

SET @i = 1240;

WHILE @i < 13000 BEGIN

INSERT INTO NC_JNodes

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

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

SET @i = @i + 1;

END;

GO

--檢視頁面分佈和頁號檔案號

TRUNCATE TABLE sp_table_pages;

INSERT INTO sp_table_pages

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

SELECT PageFID, PagePID, IndexID, IndexLevel, PageType

FROM sp_table_pages

WHERE IndexLevel >= 0;

/*

PageFID PagePID IndexID IndexLevel PageType

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

5 967 1 0 1 --該條是聚集索引的葉級別的資料頁面

5 969 2 0 2 --該條是非聚集索引的葉級別的索引頁面

5 971 1 1 2 --該條是聚集索引的子結點的索引頁面

5 972 1 0 1

5 973 2 1 2 --該條是非聚集索引的子結點的索引頁面

5 974 2 0 2

5 975 1 0 1

*/

dbcc traceon(3604)

dbcc page(poofly,5,973,1)--非聚集索引的子結點的索引頁面

/*

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

00000000: 06000000 1000c903 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?............

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

00000000: 06313036 33310304 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?.10631......

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

00000000: 06313132 34340404 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?.11244......

*/

--我們來看下第一個索引行

06->狀態位A

000000 1000->無效的鍵值

c903 0000->Ox03c9==969 表示指向下一層的第一個非聚集索引頁面號

0500->檔案號

--接下來看第二個索引行

06->狀態位A

313036 3331->'10631' 這個是str1 非聚集索引值,是下一層非聚集索引頁的第一個值

0304 0000->Ox0403==1027 表示指向下一層的非聚集索引頁面號

0500->檔案號

--可以發現這個非聚集索引的結點行的結構好像跟聚集索引的結點行很像,第一行的所在鍵值都是無效的,只有從第二行開始有效

--我們來驗證下這個假設吧

dbcc page(poofly,5,1027,1)

/*

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

00000000: 36313036 33310200 00010017 00363238 †10631.......628

00000010: 38202020 202020†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?8

*/

注意看這裡的第個到第個位元組 3130363331='10631' 正好對應上我們在葉級別儲存的那個非聚集值

這條記錄下面的儲存我在以前也說過了就是一般的擁有聚集索引的表的非聚集索引葉級別的儲存了,不再多描述

結論:

在搜尋記錄的時候,我們可能通過非聚集索引列的值在非葉級別的索引行中找到對應的非聚集鍵值,然後遍歷到葉級別的索引行中

然後利用葉級別的索引行中的書籤鍵值,去遍歷聚集索引樹,然後找到我們要的資料.

接下來說說非聚集索引的非葉級的索引行在其不定義為UNIQUE的情況下的儲存

--建表(表源技術內幕系列)

CREATE TABLE NC_NU_JNodes (

id int NOT NULL ,

str1 char (5) NOT NULL ,

str2 varchar (10) NULL

);

GO

--在str2建立

CREATE UNIQUE CLUSTERED INDEX idxcl_str2 on NC_NU_JNodes (str2);

CREATE INDEX idxNC ON NC_NU_JNodes (str1);

GO

SET NOCOUNT ON;

GO

DECLARE @i int;

SET @i = 1240;

WHILE @i < 13000 BEGIN

INSERT INTO NC_NU_JNodes

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

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

SET @i = @i + 1;

END;

GO

--檢視頁面分佈和頁號檔案號

TRUNCATE TABLE sp_table_pages;

INSERT INTO sp_table_pages

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

SELECT PageFID, PagePID, IndexID, IndexLevel, PageType

FROM sp_table_pages

WHERE IndexLevel >= 0;

/*

PageFID PagePID IndexID IndexLevel PageType

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

5 1051 1 0 1

5 1053 2 0 2

5 1055 1 1 2

5 1104 1 0 1

5 1105 2 1 2

5 1106 2 0 2

5 1107 1 0 1

。。。。。

*/

dbcc traceon(3604)

dbcc page(poofly,5,1105,1)

/*

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

00000000: 06066161 de001d04 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?..aa.......

Slot 1, Offset 0x259, Length 29, DumpStyle BYTE

00000000: 36313036 33318304 00000500 02000001 †10631..........

00000010: 001d0037 37393920 20202020 20†8224 ?†8224 ?†8224 ?†8224 ?...7799

Slot 2, Offset 0x276, Length 29, DumpStyle BYTE

00000000: 36313133 30318404 00000500 02000001 †11301..........

00000010: 001d0031 30303832 20202020 20†8224 ?†8224 ?†8224 ?†8224 ?...10082

*/

--讓我們來分析下索引行的第一行

06->狀態位A

066161 de00->無效的鍵值

1d04 0000->Ox041d==1053 表示指向下一層的第一個非聚集索引頁面號

0500->檔案號

--第二條索引行

36->狀態位A

313036 3331->=='10631'這個是str1 非聚集索引值,是下一層非聚集索引頁的第一個值

8304 0000->Ox0483==1155 表示指向下一層的第一個非聚集索引頁面號

0500->==5 檔案號

0200->==2該頁上的列數

00->NULL點陣圖

0100->==1變長列的數目

1d00->00011101=13+16=29 第一列變長列的結束位置即該索引行的長度

37 37393920 20202020 20->=='7799 '聚集鍵值

--驗證下：

dbcc page(poofly,5,1155,3)--STYLE 3 可以以表格形式顯示一些資訊

/*

FileId PageId Row Level str1 (key) str2 (key) KeyHashValue

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

5 1155 0 0 10631 7799 (f90044db974c)

5 1155 1 0 10641 3241 (f500488c99d3)

5 1155 2 0 10644 8469 (fd006debfe58)

....

*/

看這裡的第一條 str1='10631' str2='7799' 就是上面非聚集索引非葉級別的第二條索引行讀出的值

結論:

其實這裡跟我在非聚集索引在結點行的儲存中說的行結構區別就是後面多了做為聚集索引的變長列所帶來的多餘位元組

其作用也是顯而易見的,就是以防止我們的非聚集鍵值因為不唯一性而帶來的遍歷不唯一

這裡還有一點：如果你的索引沒有定義成唯一性,即使所有的列值都是唯一的(就像本例中的str1不可能重複),但是它還是會包含書籤。

PS：這個儲存情況有很多種讀者可自行嘗試

有關這方面的專題可參看

1:http://blog.csdn.net/feixianxxx/archive/2010/03/17/5390317.aspx

2: http://blog.csdn.net/feixianxxx/archive/2010/04/05/5452759.aspx

3: http://blog.csdn.net/feixianxxx/archive/2010/04/14/5486513.aspx

4: http://blog.csdn.net/feixianxxx/archive/2010/05/08/5569945.aspx

【SQL Server2005頁面儲存5之--非聚集索引行在非葉級別儲存】

相關文章