SQL Server 2008连载之存储结构——非聚集索引
非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点：
基础表的数据行不按非聚集键的顺序排序和存储。
非聚集索引的叶层是由索引页而不是由数据页组成。
非聚集索引既可以建在堆表结构上也可以建在聚集索引表上；非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。
如果表是堆则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行> 如果表包含有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。
B 树的页集合由 sys.system_internals_allocation_units 系统视图中的 root_page 指针定位。
堆表
--创建一张堆表
CREATE TABLE testHeapIndex
(
name CHAR(200),
type1 CHAR(900),
type2 CHAR(900)
)
--分别创建一个唯一索引和一个非唯一索引
CREATE UNIQUE INDEX>
CREATE INDEX>--插入测试数据
INSERT INTO testHeapIndex VALUES('A','A1','A2')
INSERT INTO testHeapIndex VALUES('B','B1','B2')
INSERT INTO testHeapIndex VALUES('C','C1','B2')
INSERT INTO testHeapIndex VALUES('D','D1','B2')
INSERT INTO testHeapIndex VALUES('E','E1','C2')
INSERT INTO testHeapIndex VALUES('F','F1','F1')
INSERT INTO testHeapIndex VALUES('G','G1','G1')
INSERT INTO testHeapIndex VALUES('H','H1','G1')
INSERT INTO testHeapIndex VALUES('I','I1','G1')
INSERT INTO testHeapIndex VALUES('J','J1','J1')
--获取该表的相应页面信息
SELECT A.NAME TABLE_NAME,B.NAME INDEX_NAME,B.INDEX_ID
FROM SYS.OBJECTS A,SYS.INDEXES B
WHERE A.OBJECT_ID=B.OBJECT_ID AND A.NAME='testHeapIndex'
TRUNCATE TABLE tablepage;
INSERT INTO tablepage EXEC ('DBCC IND(testdb,testHeapIndex,0)');
INSERT INTO tablepage EXEC ('DBCC IND(testdb,testHeapIndex,2)');
INSERT INTO tablepage EXEC ('DBCC IND(testdb,testHeapIndex,3)');
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
--获取该表的root页面地址，聚集索引的根节点必须通过下面脚本才能找到
SELECT c.name,a.type_desc,d.name,
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,sys.indexes d
WHERE a.container_id=b.partition_id and b.object_id=c.object_id
AND d.object_id=b.object_id AND d.index_id=b.index_id
AND c.name in ('testHeapIndex')
--下面各个例子获取相关页面和root页面的脚本基本相同，不再重复
堆表上的唯一非聚集索引
首先堆表是由若干叶子页面组成的，相互之间没有链接关系，完全靠IAM页面进行管理和维护。
我们可以看到page(1:90)为该唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于I1的指向叶子页面page(1:93)，小于I1的则指向叶子页面page(1:55)页面。
叶子页面中则包括每个索引条目的索引值和该索引指向的记录的位置（文件号+页面+插槽号），因为这是唯一索引所以不需要额外的字段来记录重复值。
堆表上的非唯一非聚集索引
我们可以看到page(1:94)为该非唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于G1的指向叶子页面page(1:78)，小于G1的则指向叶子页面page(1:109)页面。
叶子页面中则包括每个索引条目的索引值和该索引指向的记录的位置（文件号+页面+插槽号）；存储结构与堆表上的唯一非聚集索引完全一致，我们可以看出在堆表中尽管索引值不唯一，但通过索引值+指针(文件号+页面+插槽号)的方式，也能够保证该索引条目的唯一性，所以不需要增加额外的辅助字段。
唯一聚集索引表
CREATE TABLE testUniqueClusterIndex
(
name CHAR(200),
type1 CHAR(900),
type2 CHAR(900)
)
CREATE UNIQUE CLUSTERED INDEX>
CREATE UNIQUE INDEX>
CREATE INDEX>INSERT INTO testUniqueClusterIndex VALUES('A','A1','A2')
INSERT INTO testUniqueClusterIndex VALUES('B','B1','B2')
INSERT INTO testUniqueClusterIndex VALUES('C','C1','B2')
INSERT INTO testUniqueClusterIndex VALUES('D','D1','B2')
INSERT INTO testUniqueClusterIndex VALUES('E','E1','C2')
INSERT INTO testUniqueClusterIndex VALUES('F','F1','F1')
INSERT INTO testUniqueClusterIndex VALUES('G','G1','G1')
INSERT INTO testUniqueClusterIndex VALUES('H','H1','G1')
INSERT INTO testUniqueClusterIndex VALUES('I','I1','G1')
INSERT INTO testUniqueClusterIndex VALUES('J','J1','J1')
唯一聚集索引表上的唯一非聚集索引
我们首先可以看到page(1:192)为聚集索引的根节点，该根节点包括2个索引值和三个索引指针指向相应的叶子页面，而聚集索引的叶子页面则按照聚集索引的排序规则进行存储。
page(1:194)为该唯一聚集索引表上的唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于H1的指向叶子页面page(1:195)，小于H1的则指向叶子页面page(1:151)页面。
唯一非聚集索引的叶子页面中则包括每个索引条目的索引值和其聚集键值；因为都是唯一的，所以两者合起来能够保证该索引条目的唯一性，所以不需要增加额外的辅助字段。
那么当对唯一非聚集索引的字段进行查找的时候是如何处理的呢？SQL Server首先从唯一非聚集索引的根节点开始查起，直到找到合适的索引叶子页面，然后根据该索引条目中的聚集索引键值，去聚集索引根节点中进行查找，一直找到正确的聚集叶子页面为止。
唯一聚集索引表上的非唯一非聚集索引
page(1:196)为该唯一聚集索引表上的非唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于G1的指向叶子页面page(1:174)，小于G1的则指向叶子页面page(1:197)页面。之所以在196页面的索引条目中包括聚集索引的键值，是因为该索引条目不唯一造成的，从图中可以看出，Type2=G1的索引条目有2条，所以需要聚集索引键值的存在才能保证该索引条目的歧义和唯一性。
非唯一非聚集索引的叶子页面中则包括每个索引条目的索引值和其聚集键值；尽管非聚集索引不唯一，但因为聚集索引是唯一的，所以两者合起来能够保证该索引条目的唯一性，所以也不需要增加额外的辅助字段。
非唯一聚集索引表
CREATE TABLE testUnUniqueClusterIndex
(
name CHAR(200),
type1 CHAR(900),
type2 CHAR(900)
)
CREATE CLUSTERED INDEX>
CREATE UNIQUE INDEX>
CREATE INDEX>INSERT INTO testUnUniqueClusterIndex VALUES('A','A1','A2')
INSERT INTO testUnUniqueClusterIndex VALUES('B','B1','B2')
INSERT INTO testUnUniqueClusterIndex VALUES('B','C1','B2')
INSERT INTO testUnUniqueClusterIndex VALUES('D','D1','B2')
INSERT INTO testUnUniqueClusterIndex VALUES('E','E1','C2')
INSERT INTO testUnUniqueClusterIndex VALUES('F','F1','F1')
INSERT INTO testUnUniqueClusterIndex VALUES('F','G1','G1')
INSERT INTO testUnUniqueClusterIndex VALUES('F','H1','G1')
INSERT INTO testUnUniqueClusterIndex VALUES('F','I1','I1')
INSERT INTO testUnUniqueClusterIndex VALUES('J','J1','J1')
非唯一聚集索引表上的唯一非聚集索引
我们首先可以看到page(1:205)为聚集索引的根节点，该根节点包括2个索引值和三个索引指针指向相应的叶子页面，其中第二个索引值后面还带了一个identifer为3的值，这是因为该聚集索引不唯一，所以必须增加一个唯一标识才能定位到相应的下级节点中。而聚集索引的叶子页面则按照聚集索引的排序规则进行存储；注意在叶子节点中重复键值的聚集索引的尾部也带有相应的唯一标识值。
page(1:207)为该非唯一聚集索引表上的唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于H1的指向叶子页面page(1:201)，小于H1的则指向叶子页面page(1:208)页面。
注意非唯一非聚集索引的叶子页面中则包括每个索引条目的索引值和其聚集键值，同时在重复聚集键值的后面增加了相应的唯一标识，因此三者结合起来就能够保证该索引条目的唯一性了。
非唯一聚集索引表上的非唯一非聚集索引
page(1:209)为该非唯一聚集索引表上的非唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于G1的指向叶子页面page(1:210)，小于G1的则指向叶子页面page(1:203)页面。
注意非唯一非聚集索引的叶子页面中则包括每个索引条目的索引值和其聚集键值，同时在重复聚集键值的后面增加了相应的唯一标识，虽然非唯一聚集索引的索引键值是重复的，但因为聚集索引键值和唯一标识已经是唯一的，所以三者结合起来依然能够保证该索引条目的唯一性。
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2018-10-18 10:36:45
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡