SQL Server 索引(一)数据结构和存储结构
时间:2022-03-14 01:30
本文关注以下方面(本文所有的讨论基于SQL Server数据库):
- 索引的分类;
- 索引的结构;
- 索引的存储
红色标记说明:
- PagePID:页编号
- PageType:页类型,第三部分已经说明,1为数据页(此处为聚集索引的叶节点),2为索引页(此处为聚集索引的根或中间节点),10为IAM页
- IndexLevel:标明页子在B树中的位置,0为叶节点,1为中间节点,2为根节点
- NextPagePID和PrevPageID:用于标识此页的前一页和后一页,这表明每一层是一个双向链表,为0则表明没有相应的页
为了方便查找,我们也可以把上述结果存入表中,为此建表
CREATE TABLE DBCCIndResult ( PageFID NVARCHAR(200), PagePID NVARCHAR(200), IAMFID NVARCHAR(200), IAMPID NVARCHAR(200), ObjectID NVARCHAR(200), IndexID NVARCHAR(200), PartitionNumber NVARCHAR(200), PartitionID NVARCHAR(200), iam_chain_type NVARCHAR(200), PageType NVARCHAR(200), IndexLevel NVARCHAR(200), NextPageFID NVARCHAR(200), NextPagePID NVARCHAR(200), PrevPageFID NVARCHAR(200), PrevPagePID NVARCHAR(200) )
插入数据
INSERT INTO DBCCIndResult EXEC (‘DBCC IND(PCT,Employee,-1) ‘)
我们可以通过下面的语句来查看索引的深度
select * from sys.dm_db_index_physical_stats(db_id(‘PCT‘),object_id(‘Employee‘),null,null,null)
我们看到索引的深度为3,上面的IndexLevel分别有0,1,2也验证了这一点。page_count为1944,但是我们上面查到的结果却是1977,这是因为这里的语句没有计算Index为1和2的页(注意index_level列)
接下来我们看看B树中各种节点存储的到底是什么?
找到根节点283
select * from DBCCIndResult where pagetype = 2 and indexLevel = 2
查看页里的数据
DBCC TRACEON (3604); GO DBCC PAGE (PCT, 1, 283, 3); GO
从上图,可以看出,此根节点共有31个儿子(中间节点),而且还存有主键值EmployeeId,那么这31个主键值是哪些记录的主键值呢?我们继续深入
以中间节点1863为例
DBCC TRACEON (3604); GO DBCC PAGE (PCT, 1, 1863, 3); GO
这和根节点很类似,标明了包含下一层的节点(共65个)和主键值,继续深入
以叶节点807为例
DBCC TRACEON (3604); GO DBCC PAGE (PCT, 1, 807, 3); GO
由于结果太多,我就不把所有的截图都发出来了,但是从上面我们已经看到了一些重要的东西
首先PAGE:(1:807)表明这是一个叶节点,同时也是一个数据页,因为它存放了表里所有字段的数据(EmployeeId和EmployeeName),换句话说这儿的叶节点就是表Employee在数据库中的存储数据页,也就是说聚集索引的叶节点其实就是表的数据存储页
其次我们看标红的EmployeeId,它就是我们在之前根节点283和中间节点1863存储的主键值,而且它是位于数据存储页的第一个数据
至此我们总结如下:
- 聚集索引的根节点和中间节点是索引页,都只包含下一层的入口指针和入口值(位于存储位置的第一个主键值);
- 聚集索引的叶节点就是数据页。
为了更方便地查看叶节点的数据,我们将其存入表中
DBCC PAGE(PCT,1,807, 3) WITH TABLERESULTS
这种方式是以表的方式展示
但是这种方式也不便查找,我们索性新建表
CREATE TABLE DBCCPageResult( ParentObject NVARCHAR(200), Object NVARCHAR(200), Field NVARCHAR(200), Value NVARCHAR(200) )
插入数据
INSERT INTO DBCCPageResult EXEC (‘DBCC PAGE(PCT,1,807, 3) WITH TABLERESULTS‘)
查看EmployeeId数据
select * from DBCCPageResult where Field = ‘EmployeeId‘
注意Value,是按顺序排好的,这也是聚集索引的意义了 - 把数据按顺序存储.
至此我们又可以得出:
- 聚集索引就是把数据按主键顺序存储;
- 因为一张表中的数据只能有一个物理顺序,所以一张表只能有一个主键/聚集索引。
中间红线上面的是之前聚集索引的数据,下面是非聚集索引的数据
找到非聚集索引树的根节点,为2482
select * from DBCCIndResult where IAMPID = 2320 and indexlevel = 2
查看根节点2482数据
DBCC TRACEON (3604); GO DBCC PAGE (PCT, 1, 2482, 3); GO
上图说明根节点包含下一层中间节点的页号,非聚集索引的键值EmployeeName以及聚集索引的键值EmployeeId
继续查看中间节点2481情况
DBCC TRACEON (3604); GO DBCC PAGE (PCT, 1, 2481, 3); GO
中间页节点(Level为1)同样包含了下一层(叶节点)的页号以及聚集、非聚集键值
继续查看叶节点2683情况
DBCC TRACEON (3604); GO DBCC PAGE (PCT, 1, 2683, 3); GO
此处叶节点包含聚集、非聚集索引键值以及一个KeyHasValue
至此,我们总结如下:
- 非聚集索引的根节点和中间节点是索引页,都只含下一层级的入口指针和入口值(位于存储位置的第一个键值);
- 非聚集索引的叶节点也是索引页,也存储有聚集索引和非聚集索引的键值;
- 非聚集索引中的每个索引行(不论是根节点、中间节点还是叶节点)都包含非聚集键值和行定位符(本例为聚集索引键值),此定位符指向聚集索引或堆(没有聚集索引的表)中包含该键值的数据行。
非聚集索引行中的行定位器可以是指向行的指针,也可以是行的聚集索引键,具体根据如下情况而定:
- 如果表是堆(意味着该表没有聚集索引),则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID);
- 如果表有聚集索引或索引视图上有聚集索引,则行定位器是行的聚集索引键(本例即为EmployeeId)。如果聚集索引不是唯一的索引,SQL Server 将添加在内部生成的值(称为唯一值)以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时,才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。
查看中间节点6385
DBCC TRACEON (3604); GO DBCC PAGE (PCT, 1, 6385, 3); GO
查看叶节点6715
DBCC TRACEON (3604); GO DBCC PAGE (PCT, 1, 6715, 3); GO
总结如下:
- 索引覆盖和非聚集索引的根节点和中间节点一样,都是索引页,都只包含下一层的入口指针和入口值。
- 索引覆盖的叶节点却稍有不同,多了一列DepartmentCode,此列即为索引覆盖列,而且此列只在叶节点出现,如果查询时,只需返回键值列和索引覆盖列,则只需索引查找,肯本无需访问数据页,不仅提高了性能,而且节省占用空间。