SQL Server2008存储结构之聚集索引和非聚集索引

最新推荐文章于 2024-05-28 21:51:03 发布

iteye_887

最新推荐文章于 2024-05-28 21:51:03 发布

阅读量276

点赞数

文章标签： SQL 数据结构 SQL Server C C++

聚集索引即基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。
从某种程度上，聚集索引即数据，这句话是有道理的；但正如同其他索引一样，聚集索引也是按 B 树结构进行组织的。既然是B树组织，那么就有叶子结点和非叶子节点之分。聚集索引B 树的顶端节点称为根节点；聚集索引中的底层节点称为叶节点。在根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中，叶节点包含基础表的数据页。根节点和中间级节点包含存有索引行的索引页。每个索引行包含一个键值和一个指针，该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。每级索引中的页均被链接在双向链接列表中。
因此可以这么说，聚集索引的叶子结点存储的是按聚集索引顺序排列的数据本身，而中间结点和根节点则在维护索引和其层级。
对于某个聚集索引， sys.system_internals_allocation_units 中的 root_page 列指向该聚集索引某个特定分区的顶部。SQL Server 将从索引中向下移动以查找与某个聚集索引键对应的行。为了查找键的范围，SQL Server 将在索引中移动以查找该范围的起始键值，然后用向前或向后指针在数据页中进行扫描。为了查找数据页链的首页，SQL Server 将从索引的根节点沿最左边的指针进行扫描。

SQL code

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表示主记录
3表示索引记录
5表示幻影索引记录存在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条记录。也就是说当我们完成第五条记录时就应该产生分页现象了。

SQL code

 
 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
(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
(201c8aeace10)
1 239 2 1 1 249
(201cbd800c11)
现在我们可以看到在根节点上又增加了一个新的键值I，凡是大于等于I的记录均指向第249页；结合前面的描述，我们可以得到下面的索引结构变化示意图。

SQL Server 2008连载之存储结构——非聚集索引
非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点：
基础表的数据行不按非聚集键的顺序排序和存储。
非聚集索引的叶层是由索引页而不是由数据页组成。

非聚集索引既可以建在堆表结构上也可以建在聚集索引表上；非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。
如果表是堆则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)。
如果表包含有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。
B 树的页集合由 sys.system_internals_allocation_units 系统视图中的 root_page 指针定位。

堆表
--创建一张堆表

SQL code

 
 CREATE TABLE testHeapIndex 
 ( 
    name   CHAR(200), 
    type1  CHAR(900), 
    type2  CHAR(900) 
 ) 
 --分别创建一个唯一索引和一个非唯一索引 
 CREATE UNIQUE INDEX idx_testHeapIndex1 ON testHeapIndex(type1) 
 CREATE INDEX idx_testHeapIndex2 ON testHeapIndex(type2) 
 --插入测试数据 
 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)页面。
叶子页面中则包括每个索引条目的索引值和该索引指向的记录的位置（文件号+页面+插槽号）；存储结构与堆表上的唯一非聚集索引完全一致，我们可以看出在堆表中尽管索引值不唯一，但通过索引值+指针(文件号+页面+插槽号)的方式，也能够保证该索引条目的唯一性，所以不需要增加额外的辅助字段。

SQL code

 
 CREATE TABLE testUniqueClusterIndex 
 ( 
    name   CHAR(200), 
    type1  CHAR(900), 
    type2  CHAR(900) 
 ) 
 CREATE UNIQUE CLUSTERED INDEX idx_testUniqueClusterIndex_cluster ON testUniqueClusterIndex(name) 
 CREATE UNIQUE INDEX idx_testUniqueClusterIndex1 ON testUniqueClusterIndex(type1) 
 CREATE INDEX idx_testUniqueClusterIndex2 ON testUniqueClusterIndex(type2) 
 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条，所以需要聚集索引键值的存在才能保证该索引条目的歧义和唯一性。
非唯一非聚集索引的叶子页面中则包括每个索引条目的索引值和其聚集键值；尽管非聚集索引不唯一，但因为聚集索引是唯一的，所以两者合起来能够保证该索引条目的唯一性，所以也不需要增加额外的辅助字段。

非唯一聚集索引表

SQL code

 
 CREATE TABLE testUnUniqueClusterIndex 
 ( 
    name   CHAR(200), 
    type1  CHAR(900), 
    type2  CHAR(900) 
 ) 
 CREATE CLUSTERED INDEX idx_testUnUniqueClusterIndex_cluster ON testUnUniqueClusterIndex(name) 
 CREATE UNIQUE INDEX idx_testUnUniqueClusterIndex1 ON testUnUniqueClusterIndex(type1) 
 CREATE INDEX idx_testUnUniqueClusterIndex2 ON testUnUniqueClusterIndex(type2) 
 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)页面。
注意非唯一非聚集索引的叶子页面中则包括每个索引条目的索引值和其聚集键值，同时在重复聚集键值的后面增加了相应的唯一标识，虽然非唯一聚集索引的索引键值是重复的，但因为聚集索引键值和唯一标识已经是唯一的，所以三者结合起来依然能够保证该索引条目的唯一性。

iteye_887

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL Server2008存储结构之聚集索引和非聚集索引

聚集索引即基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。从某种程度上，聚集索引即数据，这句话是有道理的；但正如同其他索引一样，聚集索引也是按 B 树结构进行组织的。既然是B树组织，那么就有叶子结点和非叶子节点之分。聚集索引B 树的顶端节点称为根节点；聚集索引中的底层节点称为叶节点。在根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中...
复制链接

扫一扫