樓主
聚集索引即基于數據行的鍵值在表內排序和存儲這些數據行。每個表只能有一個聚集索引，因為數據行本身只能按一個順序存儲。
從某種程度上，聚集索引即數據，這句話是有道理的；但正如同其他索引一樣，聚集索引也是按 B 樹結構進行組織的。既然是B樹組織，那么就有葉子結點和非葉子節點之分。聚集索引B 樹的頂端節點稱為根節點；聚集索引中的底層節點稱為葉節點。在根節點與葉節點之間的任何索引級別統稱為中間級。在聚集索引中，葉節點包含基礎表的數據頁。根節點和中間級節點包含存有索引行的索引頁。每個索引行包含一個鍵值和一個指針，該指針指向 B 樹上的某一中間級頁或葉級索引中的某個數據行。每級索引中的頁均被鏈接在雙向鏈接列表中。
因此可以這么說，聚集索引的葉子結點存儲的是按聚集索引順序排列的數據本身，而中間結點和根節點則在維護索引和其層級。
對于某個聚集索引， sys.system_internals_allocation_units 中的 root_page 列指向該聚集索引某個特定分區的頂部。SQL Server 將從索引中向下移動以查找與某個聚集索引鍵對應的行。為了查找鍵的范圍，SQL Server 將在索引中移動以查找該范圍的起始鍵值，然后用向前或向后指針在數據頁中進行掃描。為了查找數據頁鏈的首頁，SQL Server 將從索引的根節點沿最左邊的指針進行掃描。
drop table testUniqueCluster drop table testNonUniqueCluster CREATE TABLE testUniqueCluster ( name CHAR(900), remark CHAR(1100) ) CREATE UNIQUE CLUSTERED INDEX ix_testUniqueCluster ON testUniqueCluster(name) INSERT INTO testUniqueCluster VALUES('B','BBB1') INSERT INTO testUniqueCluster VALUES('A','AAA1') CREATE TABLE testNonUniqueCluster ( name CHAR(900), remark CHAR(1100) ) CREATE CLUSTERED INDEX ix_testNonUniqueCluster ON testNonUniqueCluster(name) INSERT INTO testNonUniqueCluster VALUES('B','BBB2') INSERT INTO testNonUniqueCluster VALUES('B','BBB1') INSERT INTO testNonUniqueCluster VALUES('A','AAA1') SELECT c.name,a.type_desc, total_pages,used_pages,data_pages, testdb.dbo.f_get_page(first_page) first_page_address, testdb.dbo.f_get_page(root_page) root_address, testdb.dbo.f_get_page(first_iam_page) IAM_address FROM sys.system_internals_allocation_units a,sys.partitions b,sys.objects c WHERE a.container_id=b.partition_id and b.object_id=c.object_id AND c.name in ('testUniqueCluster','testNonUniqueCluster') TRUNCATE TABLE tablepage; INSERT INTO tablepage EXEC ('DBCC IND(testdb,testUniqueCluster,1)'); INSERT INTO tablepage EXEC ('DBCC IND(testdb,testNonUniqueCluster,1)'); SELECT b.name table_name, CASE WHEN c.type=0 THEN '堆' WHEN c.type=1 THEN '聚集' WHEN c.type=2 THEN '非聚集' ELSE '其他' END index_type, c.name index_name, PagePID,IAMPID,ObjectID,IndexID,Pagetype,IndexLevel, NextPagePID,PrevPagePID FROM tablepage a,sys.objects b,sys.indexes c WHERE A.ObjectID=b.object_id AND A.ObjectID=c.object_id AND a.IndexID=c.index_id |
Name | Type_desc | Used_pages | Data_pages | First_page_address | Root_address | IAM_Address |
testUniqueCluster | IN_ROW_DATA | 2 | 1 | 1:233 | 1:233 | 1:234 |
testNonUniqueCluster | IN_ROW_DATA | 2 | 1 | 1:235 | 1:235 | 1:236 |
下面我們用dbcc命令介紹一下聚集索引的構造。
DBCC TRACEON(3604) DBCC PAGE(testDB,1,233,1) m_type = 1 5E3BC060: 1000d407 42202020 20202020 20202020 ?....B .... 5E3BC3E0: 20202020 20202020 42424231 20202020 ? BBB1 ... 5E3BC830: 20202020 0200fc10 00d40741 20202020 ? .......A ... 5E3BCBB0: 20202020 20202020 20202020 20202041 ? A 5E3BCBC0: 41413120 20202020 20202020 20202020 ?AA1 ... 5E3BD000: 20202020 20202020 20202002 00fc0000 ? ..... OFFSET TABLE: Row - Offset 1 (0x1) - 96 (0x60) 0 (0x0) - 2103 (0x837) DBCC PAGE(testDB,1,235,1) 5E3BC060: 1000d407 42202020 20202020 20202020 ?....B ... 5E3BC3E0: 20202020 20202020 42424232 20202020 ? BBB2 ... 5E3BC830: 20202020 0300f830 00d40742 20202020 ? ...0...B ... 5E3BCBB0: 20202020 20202020 20202020 20202042 ? B 5E3BCBC0: 42423120 20202020 20202020 20202020 ?BB1 ... 5E3BD000: 20202020 20202020 20202003 00f80100 ? ..... 5E3BD010: df070100 00001000 d4074120 20202020 ?..........A ... 5E3BD390: 20202020 20202020 20202020 20204141 ? AA 5E3BD3A0: 41312020 20202020 20202020 20202020 ?A1 ... 5E3BD7E0: 20202020 20202020 20200300 f8000021 ? .....! OFFSET TABLE: Row - Offset 2 (0x2) - 2103 (0x837) 1 (0x1) - 96 (0x60) 0 (0x0) - 4118 (0x1016) |
其中紅顏色的部分為每行的行頭部分，藍顏色部分為每行的結尾部分。
大家可以看到m_type=1即數據頁面，大家應該很奇怪吧，為什么明明是聚集索引，卻是數據頁面呢？正如上面所提到，聚集索引的葉子頁面即數據頁面。因為這個表只有2~3條記錄，所以root頁面還達不到需要分為B樹的程度，所以該root頁面也是葉子頁面。
我們首先來看一下1000d407的行頭部如何解釋
第0位 | 第1-3位 | 第4位 | 第5位 | 第6-7位 | 1個字節 | 2個字節 |
0 | 000 | 1 | 0 | 00 | 00 | d407 |
10 | 00 | 2004 | ||||
始終為0 | 0表示主記錄 | 存在NULL位圖 | 存在變長字段 | 保留 | 狀態B保留 | 字段長度 |
即該行為不存在變長字段的主記錄，且字段長度為2004個字節。
那30 00d407該如何解釋呢？即00001100即存在變長字段的主記錄，我們的testNonUniqueCluster怎么會存在變長字段呢？
在該非唯一聚集索引表中，我們首先插入記錄B、BBB2記錄，再插入B、BBB1記錄，這個時候對于非唯一索引如何去識別呢？SQL Server在重復行的行尾增加了8個額外的字節，稍后我們再分析行尾。
在testUniqueCluster表中正常的行尾為0200fc，其解釋如下0200表示該表有2個字段，fc則為1111 1100，即前2個字段不為空。
而對于testNonUniqueCluster表正常的行尾應為0300 f8，其解釋如下0300表示該表有3個字段，f8則為1111 1000，即前3個字段不為空；很顯然SQL Server把非唯一索引的標識符也當做字段了；但的的確確因為B、BBB2和A、AAA1在插入的時候是唯一的，所以不需要這個字段。
我們接下來看看B、BBB1行的尾部03 00f8 0100 df070100 0000，0300f8解釋同上，0100即1表示該表一共有1個變長字段，df07即2015變長字段結束的位置，最后四個字節0100 0000為非唯一索引的標識符，換算成10進制即1。
從頁面中記錄的順序我們其實可以看得出來，聚集索引的行的物理順序與行的實際存儲沒有太大關系，而是與記錄槽的順序的有關。
既然我們再談論聚集索引，那就不能不說聚集索引的中間節點和根節點了，
為了簡化處理，我們使用testUniqueCluster來做進一步的研究。
該表包含2個定長字段，合計2000字節，加上相應的頭部的4個管理字節和尾部的3個管理字節，共計2007個字節，頁頭還需要96個字節，每行的偏移量需要2個字節，所以單頁8192字節只能容納大概4條記錄。也就是說當我們完成第五條記錄時就應該產生分頁現象了。
INSERT INTO testUniqueCluster VALUES('C','CCC1') INSERT INTO testUniqueCluster VALUES('D','DDD1') INSERT INTO testUniqueCluster VALUES('E','EEE1') TRUNCATE TABLE tablepage; INSERT INTO tablepage EXEC ('DBCC IND(testdb,testUniqueCluster,1)'); SELECT b.name table_name, CASE WHEN c.type=0 THEN '堆' WHEN c.type=1 THEN '聚集' WHEN c.type=2 THEN '非聚集' ELSE '其他' END index_type, c.name index_name, PagePID,IAMPID,ObjectID,IndexID,Pagetype,IndexLevel, NextPagePID,PrevPagePID FROM tablepage a,sys.objects b,sys.indexes c WHERE A.ObjectID=b.object_id AND A.ObjectID=c.object_id AND a.IndexID=c.index_id |
以下為該表的詳細頁面分布
index_name | PagePID | IAMPID | IndexID | Pagetype | IndexLevel | NextPagePID | PrevPagePID |
… | 234 | NULL | 1 | 10 | NULL | 0 | 0 |
… | 233 | 234 | 1 | 1 | 0 | 248 | 0 |
… | 239 | 234 | 1 | 2 | 1 | 0 | 0 |
… | 248 | 234 | 1 | 1 | 0 | 0 | 233 |
我們再用sys.system_internals_allocation_units來看一下該表的頁面概要信息。
name | total_pages | used_pages | data_pages | first_address | root_address | IAM_address |
testUniqueCluster | 4 | 4 | 2 | 1:233 | 1:239 | 1:234 |
從以上兩個表格，我們可以看出IAM頁面未發生變化，仍舊是第234頁面。
根節點頁面發生了變化，現在是第239頁面，pagetype=2，即索引頁面，新增加了一個數據頁面第248頁面，第233頁面仍繼續存在；同時在第248和233個頁面之間存在著互鏈的關系。
同時觀察一下數據，發現在第233頁中存在A、AAA1；B、BBB1；C、CCC1；D、DDD1等4條記錄，而第248頁中則存在E、EEE1記錄，也就是說對于SQL Server來說索引的分裂應該是以最小代價進行，而不是完全均衡策略。
再讓我們用DBCC PAGE(1,testDB,239,3)觀察一下根節點的內容。
FileId | PageId | Row | Level | ChildFileId | ChildPageId | name (key) | KeyHashValue |
1 | 239 | 0 | 1 | 1 | 233 | NULL | (6f4251ce1f81) |
1 | 239 | 1 | 1 | 1 | 248 | E | (201c8aeace10) |
因為這是個索引的非葉子節點，所以連表現形式都簡化了。
FieldId為當前頁面的文件ID
PageId為當前頁面的頁面ID
Row表示為當前的slot槽
Level為1表示為當前為非葉子節點
ChildFieldId表示為插槽號指向的頁面的文件ID
ChildPageId表示為插槽號指向的頁面的頁面ID
Name表示為當前索引的鍵值
KeyHashValue為SQL Server鍵值的內部表示的hash值。
即E右側的數據指向第248頁面，而左側的則指向第233頁面。
那么再讓我們插入4條記錄看看根頁面的變化。
INSERT INTO testUniqueCluster VALUES('C','CCC1') INSERT INTO testUniqueCluster VALUES('D','DDD1') INSERT INTO testUniqueCluster VALUES('E','EEE1') DBCC PAGE(1,testDB,239,3) |
FileId | PageId | Row | Level | ChildFileId | ChildPageId | name (key) | KeyHashValue |
1 | 239 | 0 | 1 | 1 | 233 | NULL | (6f4251ce1f81) |
1 | 239 | 1 | 1 | 1 | 248 | E | (201c8aeace10) |
1 | 239 | 2 | 1 | 1 | 249 | I | (201cbd800c11) |
現在我們可以看到在根節點上又增加了一個新的鍵值I，凡是大于等于I的記錄均指向第249頁；結合前面的描述，我們可以得到下面的索引結構變化示意圖。
