翻译：SQL Server中的索引内部结构:到SQL Server索引级别10的阶梯

SQL Server中的索引内部结构:到SQL Server索引级别10的阶梯

                                                  

大卫•杜兰特2012/01/20

 

该系列

  本文是楼梯系列的一部分:SQL Server索引的阶梯

  索引是数据库设计的基础，并告诉开发人员使用数据库非常了解设计器的意图。不幸的是，当性能问题出现时，索引常常被添加到事后。这里最后是一个简单的系列文章，它应该能让任何数据库专业人员快速“跟上”他们的步伐

  在以前的水平上，我们已经对索引采取了合乎逻辑的方法，专注于它们能为我们做什么。现在是采取物理方法，研究指标内部结构的时候了;为了理解索引内部性，可以理解索引开销。只有知道索引结构，以及它是如何维护的，才能理解和最小化索引创建、更改和删除的成本;以及行插入、更新和删除。

  因此，从这一层次开始，我们将重点扩展到包括索引的成本，以及索引的好处。毕竟，降低成本是最大限度地提高效益的一部分;最大化索引的好处就是这个阶梯。

 

叶和叶水平

  任何指标的结构都由叶级和非叶级组成。尽管我们从来没有明确地说过，以前所有的水平都集中在一个指数的叶级上。因此，它是集群索引的叶级，即表本身;每个叶级别的条目都是表的一行。对于非聚集索引，它是每一行包含一个条目的叶子级别(过滤后的索引除外);每个条目由索引键列组成，可选列包括列，而书签则是聚集索引键列或RID(行ID)值。

  索引项也称为索引行;不管它是否是表行(聚集索引叶级条目)，指的是一个表行(非聚集索引叶级)，或者指向一个较低层次的页面(非叶级别)。

  非叶级别是构建在叶级之上的结构，它使SQL Server能够:

1.在索引键序列中维护索引的条目。

2.快速查找给定索引键值的叶级行。

  在第1级，我们使用电话簿作为一个类比来解释索引的好处。我们的电话簿用户，正在寻找“迈耶，海伦”，他知道条目将会在任何已排序的姓氏列表的中间，然后直接跳到中间的白色页面开始搜索。然而，SQL Server对于英语的姓氏或其他数据没有这样的内在知识。它也不知道哪一页是“中间”页面，除非它从头到尾都是从头到尾的。因此，SQL Server为索引构建了一些额外的结构。

 

非叶水平

  这个额外的结构被称为索引的非叶级或节点级别;它被认为是建立在叶级之上，不管它的页面在哪里。它的目的是为每个索引提供一个单页入口点，以及从该页到包含任何给定搜索键值的页面的一个短遍历。

  索引中的每一页，无论其级别如何，都包含索引行或条目。在叶级页面中，正如我们多次看到的那样，每个条目都指向一个表行或者是表行。因此，如果表包含10亿行，索引的叶子级别将包含10亿个条目。

  在叶层以上的水平，即最低的非叶层;每个条目指向一个页级别的页面。如果我们的10亿条目索引平均每页100个条目，这对于一个索引，它的搜索关键字由几个数字、日期和代码列组成，这是一个很实际的数字;然后，叶级将包含1000,000,000 / 100 = 10,000,000页。反过来，最低的非leaf级别将包含10,000,000个条目，每个条目指向一个叶级页面，并将跨越10万页。

  每个较高的非叶级别都有页面，每个页面的条目指向下一个较低级别的页面。因此，我们的下一个更高的非叶子级别将包含100,000个条目，大小为1000页。上面的级别包含1000个条目，大小为10页;在上面的一页上有10个条目;这就是它停止的地方。

  位于索引顶部的唯一页面称为根页面。在根页级别和叶层之上的索引级别被称为中间层。等级的编号从0开始，从叶级开始向上工作。因此，最低的中级水平总是1级。

  非叶级条目只包含索引键列和指向较低级页面的指针。包含的列只存在于叶级条目中;它们不被携带在非叶级条目中。

  除了根页面之外，索引中的每个页面都包含两个额外的指针。这些指针指向下一页和前一页，在同一级别上的索引序列。由此产生的双向页面链使SQL Server能够以升序或降序的顺序扫描任何级别的页面。

 

一个简单的例子

 

下面的图1所示的简单图帮助说明了这种树状结构的索引。此图表示理论人员的LastName / FirstName列上创建的索引。Employee表，使用以下SQL:

CREATE NONCLUSTERED INDEX IX_Full_Name

ON Personnel.Employee

(

LastName,

FirstName,

)

GO

 

图注:

指向页面的指针由数据库文件号加上页码组成。因此，指针值为5:4567指向数据库文件# 5的4567页。

大多数样本值都是从这个人身上提取的。在AdventureWorksdatabase中联系表。为了说明目的，还增加了一些其他的内容。

卡尔·奥尔森(Karl Olsen)是这个例子中最受欢迎的名字。有这么多的Karl Olsens，他们的条目跨越了整个中级水平的索引页。

 

图1 -索引的垂直切片

为了清晰和插图，图与一个典型的指数不同的是:

一个典型索引中的每一页的条目数将大于图中显示的数字，因此，除了根之外，每个级别的页面数都大于显示的页数。在我们的空间有限的图表中，叶级，特别是，将会有更多的页面。

实际索引的条目没有在页面上进行排序。这是页面的条目偏移指针，它提供对条目的排序访问。(请参阅Level 4 - Pages和Extents，以获得关于偏移指针的更多信息。)

通常情况下，索引的物理和逻辑序列之间的相关性比图中显示的要多。该索引的物理和逻辑序列之间缺乏相关性，称为外部碎片，并在第11层中讨论。

如前所述，索引可以有多个中间级别。

就好像我们的白人页面用户正在寻找海伦·迈耶，打开电话簿，发现第一页只有第一页是粉红色的。在pink page的排序条目列表中，有一个“在”费尔南德斯、塞尔达和“奥森”之间的名字，“看蓝页5:431”。当我们的用户读到蓝色页面5:431时，页面上的一个条目写道:“在库马尔、凯文”和“奈良”之间的名字“见过白色页5:2006”。粉色的页面对应着根，蓝色的页对应于中间的层次，白色的页面就是叶子。

 

指数深度

根页面的位置存储在系统表中，以及其他关于索引的信息。每当SQL Server需要访问与索引键值相匹配的索引条目时，它从根页面开始，并在索引的每个级别上通过一个页面，直到它到达包含该索引键条目的叶级页面。在我们的10亿行表示例中，5个页面读取将从根页面读取SQL服务器到叶级页面及其所需的条目;在我们的图表示例中，三个读取就足够了。在集群索引中，该叶级别条目将是实际的数据行;在非聚集索引中，该条目将包含聚集索引键列或RID值。

索引的级别或深度取决于索引键的大小和条目的数量。在AdventureWorks数据库中，没有索引的深度大于3。在具有非常大的表或非常宽的索引键列的数据库中，可能出现6个或更大的深度。

sys。dm_db_index_statsfunction提供索引的信息，包括索引类型、深度和大小。它是一个可查询的表值函数。清单1中的示例返回SalesOrderDetailtable的所有索引的摘要信息。

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;

 

 

清单1:查询系统dm_db_index_physical_cal_stats函数的结果如图2所示。

 

图2:查询系统的结果。dm_db_index_physical_stats函数

相反，清单2中所示的代码要求特定索引的详细信息，在SalesOrderDetail表的表的唯一识别列上的非聚集索引。它返回每个索引级别的一行，如图3所示。

清单2:查询系统。dm_db_index_physical_stats详细信息。

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; 

 

 

图3:查询系统的结果dm_db_index_physical_stats为详细信息

 

  从图3所示的结果来看，我们可以看到:

该指数的叶级分布在407页。

唯一的中间级别只需要两页。

根级别始终是一个页面。

  一个指数的非叶部分的大小通常是叶级大小的十分之一到百分之一。根据哪个列组成搜索键，书签的大小，如果有的话，包括列。换句话说，索引相对来说非常宽且非常短。这与大多数索引的示例图不同，比如图1中的索引图，它们往往又高又窄。

  记住，包含的列只适用于非聚集索引，它们只出现在叶级条目中;它们在较高级别的条目中被忽略，这就是为什么它们不添加到非叶级别的大小。

  由于集群索引的叶级别是表的数据行，所以只有集群索引的非叶部分是额外的信息，需要额外的存储。不管是否创建索引，数据行都存在。因此，创建集群索引可能需要时间和消耗资源;但是当创建完成后，数据库中消耗的额外空间非常少。

 

结论

  索引结构使SQL Server能够快速访问特定索引键值的任何条目。一旦找到该条目，SQL Server就可以:

访问该条目的行。

从该点以升序或降序遍历索引。

  这种索引树结构已经使用了很长时间，甚至比关系数据库还要长，而且随着时间的推移已经被证明了。

链接：http://www.sqlservercentral.com/articles/Stairway+Series/72442/