【译】SQL Server索引进阶第十篇:索引的内部结构

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:
    索引设计是数据库设计中比较重要的一个环节,对数据库的性能其中至关重要的作用,但是索引的设计却又不是那么容易的事情,性能也不是那么轻易就获取到的,很多的技术人员因为不恰当的创建索引,最后使得其效果适得其反,可以说“成也索引,败也索引”。

    本系列文章来自Stairway to SQL Server Indexes,翻译和整理后发布在agilesharp和博客园,希望对广大的技术朋友在如何使用索引上有所帮助


    在前一系列文章中我们着重讲述了有关索引各种比较虚的概念,比如索引可以做什么,索引的逻辑结构,接下来是时候来讲述比较实在的东西了,也就是索引的物理结构。理解索引的内部结构对于整体的理解索引是至关重要的,只有理解了索引的内部结构以及SQL Server是如何维护索引的,你才能理解数据插入,删除,更新,索引的创建、修改、删除所带来的成本。

 

叶子层级和非叶子层级

    所有的索引都是由叶子层级和非叶子层级组成的。前面的文章主要关注了索引的叶子层级。对于聚集索引来说,叶子节点就是索引本身,每一个叶子节点所包含的条目其实就是表中的行。对于非聚集索引来说,叶子节点每一个叶子包含的一行就是一个条目。每一个条目由索引键列,可选的包含列以及书签构成,而书签又由聚集索引键或RID构成。

    无论索引中条目是来自表行(聚集索引叶子节点),指向表行(非聚集索引叶子节点)或是指向低层级的节点(非叶子节点),索引条目都可以被称为索引行。

    非叶子节点是叶子节点层级的上层,SQL Server使用非叶子节点来:

    使得索引按照索引键聚集有序
    根据索引键快速找到叶子节点
    在本系列第一篇文章中,我们使用电话本的类比来解释为什么索引能够带来性能的提升。用户知道电话本是按照姓氏进行排序的,因此如果需要找”Meyer, Helen”根据首字母M知道这个人大概在电话本的中间位置,用户直接翻开大约一半电话本开始查找。但对于SQL Server来说,可并不知道什么是按字母表排序,也不知道哪一些页是所谓的中间页,除非把索引中的所有页扫描一遍。为了不用扫描所有的页来找到所需条目,SQL Server为在叶子节点之上增加了额外的页。

 

非叶子层级

    这些额外的页也就是所谓的非叶子节点,或被称为索引的节点层级,是建立在叶子层级之上的层级。非叶子层级的作用是使得SQL Server对于特定的索引进行查找时,不仅有了统一的入口页,并且不再需要扫描所有的页。

    在索引中的所有页,无论哪个层级,都包含索引条目。正如文章中不断重复说的,对于聚集索引来说,叶子节点的条目包含实际的行,所以如果一个表中包含了10亿行,那么叶子节点包含了10亿个条目。

    在叶子节点之上的层级,也就是非叶子节点的最底层,非叶子节点的最底层每一个索引条目都指向叶子节点。如果说表中每一页能容纳100个条目,那么刚才的十亿行需要1000000000/100=1000万个页,与之对应的是,那么最底层的非叶子节点就包含了1000万的条目,也就是分布在1000万/100=10万个页中。(译者注:原作者这里没说全,通常来说非叶子节点只包含索引键,因此每个条目的大小会远远小于叶子节点的条目大小,因此每页可以容纳更多的行,所以这里非叶子节点应该远远小于10W个页,后面的段落我们先不管这个,还是按照10W个页算)。

    再上一层的非叶子节点包含了指向这10万个页的条目,也就是10万个条目,这10W个索引条目分布在10万/1000=1000页中。根据这个规律,我们知道再上一层包含10个页,直至最上层的节点只有一个页了。

    索引中最上层的节点被称为根页。剩下的除了根页和叶子之外的层级就是所谓的中间层级。层级的编号是从叶子节点以0开始向上增长的,因此中间层级是以1开始的。

    非叶子节点仅仅包含索引键,对于拥有包含列的索引来说,包含列仅仅存在于叶子节点。

    索引中的页,除了根页之外,都含有两个额外的指针,分别指向按照索引顺序当前页之前和之后的页。这种双向链表结构使得SQL Server在索引扫描的时候更加有效。


一个简单的例子

    让我们通过一个简单的图示来真正理解索引的内部结构吧,如下图1所示。我们在Personnel.Employee表上创建了一个非聚集索引,代码如下:

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

 

    图例注释:

     指向页的指针包含了文件号和页号。比如说5:4567指向的就是第5个文件的4567个页。

    1001

图1.索引的竖切图

 

    值得说明的是,上面的图只是一个样子,正常的情况下一个页中会包含远多于上面例子的行,并且页也会远远多于上面的例子。

    实际在页中索引条目并不是有序的,而是靠偏移指针进行定位的,这个页尾的偏移表是有序的。

    很多情况下,页中并不像上面图中所展现的那样,页之间物理上是连续的,但它们之间逻辑上是连续的,逻辑和物理上的差异被称之为碎片。

    正如我们之前所说,每一个索引可以包含不止一层的中间页。

    继续使用我们之前电话本的类比。比如你查找名为Helen Meyer的联系人,打开电话本找到第一页,对于在区间 “Fernandez, Zelda”和 “Olsen, Karl”之间的名字,去看页5:431.然后你找到431页,这页告诉你对于Kumar, Kevin”和“Nara, Alison”之间的名字,去找页5:2006。然后你找到5:2006就找到了你所需的联系人。

 

索引深度

    索引的根页以及相关信息是存在系统表中的。每当SQL Server进行页查找时,SQL Server都会从根页开始查找,经过中间节点,直到找到叶子节点,然后从叶子中找到需要的索引条目。对于我们10亿行的表来说,从根节点到叶子节点共需要读取5层。而对图1所示的节点来说,只需要读取3次IO。

    上面所说的层数,也被成为索引深度。取决于索引键的大小和数量。在AdventureWorks示例数据库中,没有哪个索引的层级超过3层。但对于其它索引键宽或是数据量大的表,就会有更深的层级。

    sys.dm_db_index_physical_stats函数可以展示索引的详细信息,深度和大小。这是一个表值函数,比如下面代码我们可以找到SalesOrderDetai表相关的索引信息。

 

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 ;

 

得到的结果如图2所示。

    index1002 
图2.查询sys.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所示。

   1003

图3.查询索引的详细信息

 

   通过图3所示结果,可以看出

    叶子节点的条目分布在407页中
    中间节点仅仅需要2页
    根节点只有1页
    根据索引键的选择,书签的大小的不同,叶子节点通常是非叶子节点大小的上百倍。根据具体的数据不同而不同。

     记住包含列仅仅适用在非聚集索引并且只存在于叶子节点中,包含列对于上层的层级是透明的,这也是为什么包含列不会增加非叶子节点键的大小。

     因为聚集索引的叶子节点是表数据本身,所以除了叶子节点的数据是表数据本身之外,还需要存储一些额外的非叶子层级。因为无论是否有聚集索引数据本身都是存在的,所以创建聚集索引的时候不仅需要花费一些时间和资源,创建成功后还需要一些额外的空间存储非叶子节点。

 

总结

    索引的结构使得SQL Server可以根据键值快速找到所需的列,一旦找到所需的列之后,SQL Server可以:

    直接访问所需的行
    从找到的数据位置开始,根据双向链表找相邻的页
    索引树结构早已经在没有关系数据库时就开始被使用了,事实证明,这是一种优秀的结构。

分类: SQL Server 索引


本文转自CareySon博客园博客,原文链接:http://www.cnblogs.com/CareySon/archive/2012/09/09/2677300.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
1月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
68 2
|
21天前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
27天前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
|
26天前
|
SQL 存储 关系型数据库
SQL默认索引是什么:深入解析与技巧
在SQL数据库中,索引是一种用于提高查询性能的重要数据结构
|
2月前
|
SQL 存储 索引
SQL Server的Descending Indexes降序索引
【9月更文挑战第21天】在SQL Server中,降序索引允许指定列的排序顺序为降序,可显著优化涉及降序排序的查询性能,特别是在复合索引中。通过创建降序索引,可以更高效地满足特定业务需求,如按交易时间降序获取最新记录。然而,使用时需考虑查询频率、数据分布及维护成本,以确保最佳性能。
|
27天前
|
SQL 存储 关系型数据库
SQL默认索引是什么
在SQL数据库中,索引是一种用于提高查询性能的数据结构
|
27天前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引
在数据库管理和优化过程中,确认SQL查询是否使用了索引是一个至关重要的步骤
|
30天前
|
SQL 关系型数据库 MySQL
如何确认SQL查询是否使用了索引:详细步骤与技巧
在数据库管理和优化中,确认SQL查询是否有效利用了索引是提升性能的关键步骤
|
1月前
|
索引
SQL_创建和管理索引
SQL_创建和管理索引
25 0
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")