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