【翻译】SQL Server索引进阶：第十级，索引内部结构

2022-05-15 02:26:20

本文是SQL Server索引进阶系列（Stairway to SQL Server Indexes）的一部分。

在之前的级别中，我们从逻辑的角度介绍索引，集中于它们能为我们做什么。现在，是时候从物理的角度，并且检查一下索引的内部结构，从理解索引的内部结构，引导我们理解索引在上层做的工作。通过索引的结构，它是如何维护的，你可以理解在进行插入，更新，删除的时候，最小化索引的创建，修改，移动。

因此，从现在开始，我们除了要关心索引带来的好处，还要关心索引的消耗。毕竟，最小化消耗可以带来最大化的好处，带来最大化的好处是本系列的宗旨。

叶子和非叶子层

索引的机构由叶子和非叶子层组成。尽管没有明显的说明，我们之前的级别主要集中于索引的叶子层。因此，聚集索引的叶子层就是表本身，每个叶子层的入口都是表中的一行。对于非聚集索引来说，在叶子层每行都有一个入口（过滤索引除外），每个入口由索引键列，可选的包含列，以及标签组成，标签的内存是聚集索引的键列，或者RID（Row ID）。

索引入口也叫做索引行，不管它是表的一行（聚集索引叶子入口），还是表中一行的引用（非聚集索引叶子层），还是指向更低级别（非叶子层）的一页。

非叶子层是构建在叶子层上的结构，使得SQL Server可以完成下面的工作：

以索引键的顺序维护索引的入口。
根据给定的索引键值，快速的找到叶子层。

在第一级中，我们用电话本来介绍索引的好处。在电话本中，名叫“Meyer，Helen”的人，因为电话本是按照last name排序的，因此我们知道这个人应该再中间位置，直接跳到电话本的中间位置开始查找。但是SQL Server没有这种知识。它不知道哪一页是中间页，除非它从索引的开始访问到结束。因此，SQL Server在索引中构建了一些额外的结构。

非叶子层

这些额外的结构叫做非叶子层，也叫节点层。是构建在叶子层上的，不论页的物理位置在哪里。目的是给SQL Server指出每个索引的单独的页入口点，从一页到另一页的较短路劲。

在索引中的每一页，不管他是哪一层，都包含索引行或者入口。在叶子层的页中，每个入口点都指向表中的一行，或者就是表中的一行。如果表有十亿行数据，索引的叶子层就会有十亿个入口。

紧挨着叶子层的上一层，是最低的非叶子层，他的每一个入口都指向一个叶子层的页。如果我们的十亿个入口，平均每页有100个入口，叶子层将包含1,000,000,000/100=10,000,000页。如果最低的非叶子层包含10,000,000个入口，每个都指向叶子层的页，将包含10,000,000/100=100,000页。

每个较高的非叶子层的页中的入口，都指向下一层的页。因此，下一个较高的非叶子层就会有100,000个入口，1000个页。在上一层，就会是1000个入口，10页，再往上就是10个入口，1页，这就是最上面了。

索引顶端的页叫做根页。索引中，在根页之下，在叶子层之上的层叫做中间层。层数从0开始（叶子层就是0）向上增加。因此，中间层至少也是1.

非叶子层的入口只包含索引键的列和指向下一层页的指针。索引的包含列只存在于叶子层的入口，非叶子层的入口中没有这类信息。

索引中的每一页，除去根页，都包含两个额外的指针。一个指向下一页，一个指向上一页。页的双向链的结果就是，使得SQL Server可以正向或者反向扫描任何一层的页面。

简单例子

通过上图，可以说明索引的树形结构。我们在Personnel.Employee表的LastName和FirstName列创建了索引。

CREATE NONCLUSTERED INDEX IX_Full_Name
ON Personnel.Employee
(
LastName,
FirstName,
)
GO

指向页的指针除了包含页的编号，还包含数据文件的编号。如果一个指针是5:4567，表示指向#5文件的第4567页。

为了清除和明白，上面的索引和实际的索引在下面几个方面有一些不同：

每页的入口数量，在实际的索引中要比上面图中的多很多，每一层的页也要比图中的多很多。尤其是叶子层，在实际中会比图中多很多。

在实际的索引中，页上的入口是无序的。页入口的偏移指针，提供入口的顺序访问。（关于偏移指针可以查看第四级，页和分区中的介绍。）

索引的深度

根页的位置和索引的其他信息存储在一张系统表中。当SQL Server需要访问给定索引键值的索引入口的时候，它就用自己的方式从根页开始，访问每一层的每一页，直到包含索引键入口的叶子层。在我们十亿行表的例子中，SQL Server访问到需要的叶子层入口只需要读取5页；在上图的例子中，只需要读取3页就可以了。在聚集索引中，叶子层的入口就是实际的数据行，在非聚集索引中，入口可能是聚集索引的键，也可能是RID（Row ID）。

层的数目，也叫做深度，AdventureWorks数据库中，没有深度超过3的索引。数据库中如果有很大的表，或者索引键的列很多，深度有可能会超过6或者更深。

sys.dm_db_index_physical_stats函数给我们提供了一些索引的信息，包括索引的类型，深度，和大小；是一个表值函数，可以执行查询。下面的例子就是查看SalesOrderDetail表的索引信息。

SELECT OBJECT_NAME(P.OBJECT_ID) AS 'Table'
, I.name AS 'Index'
, P.index_id AS 'IndexID'
, P.index_type_desc
, P.index_depth
, P.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(),
OBJECT_ID('Sales.SalesOrderDetail'),
NULL, NULL, NULL) P
JOIN sys.indexes I ON I.OBJECT_ID = P.OBJECT_ID
AND I.index_id = P.index_id;

结果显示如下。

下面的代码会显示表的指定的索引的信息，SalesOrderDetail表的uniqueidentifier列的非聚集索引，结果中的一行就是索引的一层。

SELECT OBJECT_NAME(P.OBJECT_ID) AS 'Table'
, I.name AS 'Index'
, P.index_id AS 'IndexID'
, P.index_type_desc
, P.index_level
, P.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'), 2, NULL, 'DETAILED') P
JOIN sys.indexes I ON I.OBJECT_ID = P.OBJECT_ID
AND I.index_id = P.index_id;

结果如下。

从上图的结果中，我们可以看出：

索引的叶子层有408页。
唯一的中间层只有2页。
根层只有1页。

非叶子层的大小通常是叶子层的十分之一或者百分之二，这依赖于查询键包含哪些列，标签的大小，是否有包含列。换句话说，索引可能很大，也可能很小。

请记住，包含列只在非聚集索引中可用，他们只出现在叶子层的入口。在高层的入口中会忽略他们，这就是他们不增加非叶子层大小的原因。

因为聚集索引的叶子层就是表的数据行，在聚集索引中只有非叶子层额外的信息，需要额外的空间。无论是否创建索引，数据行都是存在的。因此，创建聚集索引的时候可能会花费一些时间，消耗一些资源，但是在创建完成之后，只需要很小的数据空间。

结论

索引的结构使得SQL Server可以快速的访问索引的入口。一旦发现入口，SQL Server就可以：

访问入口的数据行。
正向或者反向访问索引。

索引的树形结构已经使用了很长一段时间，甚至比关系数据库还要久远，随着时间它已经被证明很有用。

本文转自 virusswb 51CTO博客，原文链接：http://blog.51cto.com/virusswb/989316，如需转载请自行联系原作者

码农公寓

相关文章