技术书栈技术书栈

  1. 技术书栈首页
  2. 文章列表
  3. 技术文章第 202507 期

深入理解MySQL InnoDB中的B+索引机制

文档大纲

前言

在当今的现代数据库系统里，索引堪称提升数据检索速度的核心机制之一。InnoDB 作为 MySQL 的默认存储引擎，运用了高效的 B+ 树结构来达成其索引功能。这一结构的优势显著，它不仅能保障数据实现快速检索，而且还能高效地支持插入、更新和删除操作。深入理解 InnoDB 中的 B+ 树索引，对于数据库的优化以及性能调优而言，有着举足轻重的意义。

为了更为透彻地领会 InnoDB 中 B+ 树索引的工作原理，我们将从创建一个名为 index_demo 的示例表入手，并且会借助详细的示意图，直观地展示记录在页中的存储结构以及索引所发挥的作用。

CREATE TABLE index_demo (
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY (c1)
) ROW_FORMAT = Compact;

这个表中有两个 INT 类型的列 c1 和 c2，一个 CHAR(1) 类型的列 c3，并且 c1 列为主键。表的行格式为 Compact。其基础可见：

一、 InnoDB中的 B+ 树 索引介绍

B+ 树索引是一种具备自平衡特性的树型结构，其节点主要划分为内部节点和叶子节点两类：

  • 内部节点（Internal Nodes）：在 B+ 树里起到索引导航的重要作用，该节点主要存储键值以及指向子节点的指针，通过这些信息可以快速定位到需要查找的数据大致位置。
  • 叶子节点（Leaf Nodes）：主要用于存储实际的数据记录，或者存储指向数据记录的指针（也被称作记录指针），是最终获取数据的地方。

在 B+ 树这种结构中，所有的数据记录都被统一存储在叶子节点之中，而内部节点仅承担存储键值以及导航信息的任务，并不直接存放具体的数据内容。

无论是用于存放用户记录的数据页，还是用于存放目录项记录的数据页，我们都会将它们整合到 B+ 树这一数据结构中。基于此，我们也把这些数据页统称为节点。

从相关图示中能够清晰地看到，实际的用户记录均存放在 B+ 树最底层的节点上，这些节点也被称作叶子节点或叶节点。而其余用于存放目录项的节点，则被称为非叶子节点或者内节点。其中，B+ 树最顶层的那个节点，我们称之为根节点。

依据 InnoDB存储引擎B+树的树高推导 ：当树高为4时，可以存放200百多亿行数据。这样的数据容量，可以满足绝大部分应用的需求，因此我们可以说在绝大部分应用中，B+树高度为3或4就可以满足数据存储的需求。 B+树这种 高扇出低树高 的特征，也大大的提高了主键查询性能。

二、聚簇索引

在InnoDB存储引擎中，聚簇索引（Clustered Index）是数据存储和索引的一种特殊而重要的结构。聚簇索引主要特点：

（一）使用记录主键值的大小进行排序

聚簇索引通过主键值对记录和页进行排序，这涉及三个方面：

页内记录排序

在每个数据页内部，记录会依据主键值的大小顺序，排列成一个单向链表。这种有序排列方式保证了页内记录的有序性，极大地方便了对记录的快速查找操作。

页内的记录会被进一步划分成若干个组。在每个组中，主键值最大的记录在页内的偏移量会作为槽，依次存放在页目录里（需要注意的是，Supermum 记录的主键值比任何用户记录的主键值都要大）。借助页目录，我们能够运用二分法高效地定位到主键列值等于指定值的记录。

页之间的排序

存放用户记录的页按照页内记录的主键大小顺序排成一个双向链表。这种结构使得范围查询和顺序扫描更加高效。

目录项页的排序

存放目录项记录的页根据页内目录项记录的主键大小顺序排成一个双向链表。不同层次的页同样遵循这种排序规则，确保树的平衡性和查询效率。

（二）叶子节点存储完整的用户记录

B+ 树的叶子节点负责存储完整的用户记录，这些记录涵盖了所有列的值，其中也包括隐藏列。在 InnoDB 存储引擎里，叶子节点的意义不仅仅局限于充当索引，它还实实在在地包含了实际的数据记录。这一独特特性使得聚簇索引与普通索引产生了明显的差异。

数据即索引

聚簇索引中的叶子节点存储了完整的用户记录，因此聚簇索引就是数据的存储方式。 换句话说，索引即数据，数据即索引。

自动创建

在InnoDB存储引擎中，聚簇索引会自动为每个表创建，并且不需要在MySQL语句中显式使用 INDEX 语句去创建。通常情况下，聚簇索引是基于表的主键创建的。

（三）聚簇索引的优缺点

聚簇索引的优点 聚簇索引的缺点
快速数据访问：由于数据和索引存储在一起，基于主键的查询非常高效，不需要额外的索引查找。 插入和删除成本较高：由于需要维护数据的有序性，插入和删除操作可能需要移动大量记录，导致性能开销。
有序数据存储：记录按照主键顺序存储，适合范围查询和顺序扫描，提高查询性能。 更新成本较高：如果更新操作导致主键变化，会引发记录的重新定位和页的重新排序，影响性能。

聚簇索引是InnoDB存储引擎中一种关键的索引类型，通过主键排序和存储完整用户记录，提供了高效的数据访问和有序的数据存储。在优化数据库性能时，理解和合理使用聚簇索引可以显著提升查询和数据操作的效率。具体优化可见： MySQL索引性能优化分析。

三、二级索引

“聚簇索引” 只有在搜索条件为主键值时才能充分发挥其优势，这是因为 B+ 树中的数据是严格按照主键进行排序的。那么，如果我们希望以其他列作为搜索条件，该如何处理呢？难道只能按顺序沿着链表逐一遍历所有记录吗？其实并非如此。我们可以创建多棵 B+ 树，并且让不同的 B+ 树采用不同的数据排序规则。

举例来说，我们可以以 c2 列的值大小作为数据页和页中记录的排序依据，构建一棵新的 B+ 树。其效果如下图所示：

在 InnoDB 存储引擎里，除了聚簇索引（Clustered Index）这一重要的索引类型之外，我们还能够借助二级索引（Secondary Index）来提升在非主键列上进行查询操作的性能。二级索引本质上是一种基于非主键列构建的 B+ 树结构，其主要作用在于能够快速定位到我们所需要的数据记录，从而显著提高查询效率。

（一）二级索引的特点

基于非主键列排序

二级索引的B+树结构基于指定的非主键列进行排序，这包括以下几个方面：

  • 页内记录排序：在每个页内，记录按照指定列（例如c2列）的大小顺序排成一个单向链表。
  • 页之间的排序：存放用户记录的页按照页内记录的指定列顺序排成一个双向链表。这种结构便于快速范围查询和顺序扫描。
  • 目录项页的排序：存放目录项记录的页根据页内目录项记录的指定列顺序排成一个双向链表，不同层次的页同样遵循这种排序规则。

叶子节点存储部分数据

和聚簇索引有所不同，二级索引的叶子节点存储的是索引列与主键列的值，并非完整的用户记录。这样的设计有效地减少了存储空间的占用。然而，在查询时就需要额外执行回表操作，也就是依据主键列的值，再通过聚簇索引去获取完整的用户记录。

（二）二级索引的工作流程

假设我们创建了一个基于c2列的二级索引，并通过c2列的值查找某些记录，以查找 c2 列的值为 4 的记录为例，查找过程如下：：

  1. 确定目录项记录页

从根页面开始，根据c2列的值 4 定位到目录项记录所在的页，通过页44快速定位到目录项记录所在的页为页42（因为 2 < 4 < 9）。

  1. 通过目录项记录页确定用户记录真实所在的页

在页42中，根据c2列的值确定实际存储用户记录的页。由于c2列没有唯一性约束，值为4的记录可能分布在多个数据页中。最终确定实际存储用户记录的页在页34和页35中（因为 2 < 4 ≤ 4）。

  1. 在真实存储用户记录的页中定位到具体的记录

在页34和页35中定位到具体的记录，但二级索引的叶子节点中仅存储c2列和主键列c1的值。

  1. 回表操作

根据主键值到聚簇索引中查找完整的用户记录。这个过程称为回表操作，即从二级索引定位到主键，再通过主键在聚簇索引中查找完整记录。

（三）二级索引的优缺点

二级索引的优点 二级索引的缺点
提高查询效率：基于非主键列的查询可以利用二级索引快速定位数据，减少全表扫描的开销。 回表操作：查询完整记录时需要回表操作，增加了一次I/O开销。
灵活性：可以为多个列创建二级索引，提升多种查询条件下的性能。 占用空间：虽然叶子节点不存储完整记录，但仍会占用额外的存储空间。

二级索引基于非主键列进行排序，并存储索引列与主键列的值，这为针对非主键列的查询提供了高效的解决办法。不过，由于其叶子节点仅存储部分数据，在查询完整记录时不得不进行回表操作。所以，对二级索引进行合理的使用与配置，对于提高数据库查询性能起着关键作用。 具体优化可见： MySQL索引性能优化分析。

四、联合索引

在 InnoDB 存储引擎中，联合索引（Composite Index）是一种基于多列构建的索引类型，它能够显著提升复杂查询的执行效率。联合索引会对多个列进行排序，从而可以更高效地处理包含多个条件的查询。

联合索引以多个列的值作为排序规则，也就是同时为多个列创建索引。例如，若我们希望 B+ 树按照 c2 列和 c3 列的值进行排序，这包含两层意思：

  • 首先，会依据 c2 列对各个记录以及数据页进行排序。
  • 当记录的 c2 列值相同时，则会进一步按照 c3 列的值进行排序。

为 c2 和 c3 列建立的索引的示意图如下：

如图所示，我们需要注意一下几点：

  • 每条 目录项记录 都由 c2、 c3、 页号 这三个部分组成，各条记录先按照 c2 列的值进行排序，如果记录的 c2 列相同，则按照 c3 列的值进行排序。

  • B+ 树叶子节点处的用户记录由 c2、 c3 和主键 c1 列组成。

（一）联合索引的特点

多列排序规则

联合索引依据多个列的值来进行排序，其排序规则具有两个层次：

  • 第一列排序：首先会依据第一个指定列（如 c2 列）的值对数据进行排序。
  • 第二列排序：当第一列的值相同时，会进一步按照第二个指定列（如 c3 列）的值来排序。

在这种联合索引结构里，每个目录项记录由 c2 列、c3 列的值以及对应的页号构成，而叶子节点则存储着 c2 列、c3 列的值以及主键 c1 的值。

联合索引的组成

  • 目录项记录：每条目录项记录由c2、c3和页号组成，先按照c2列排序，如果c2列相同，则按照c3列排序。
  • 叶子节点记录：叶子节点处的用户记录包含c2、c3和主键c1列。这种结构使得查询包含c2和c3列的条件时更加高效。

（二）联合索引与单列索引的区别

联合索引

  • 建立联合索引会生成一棵B+树，该树按照c2和c3列进行排序。
  • 查询时，如果使用c2和c3作为条件，能够快速定位记录，减少查询时间。

单列索引

  • 为c2和c3分别建立索引会生成两棵独立的B+树，每棵树分别按照c2或c3进行排序。
  • 查询时，如果只使用c2或c3作为条件，可以利用相应的索引。但如果同时使用c2和c3作为条件，可能需要进行多次索引查找和合并操作，增加查询开销。

（三）联合索引的优缺点

联合索引的优点 联合索引的缺点
高效的多列查询：联合索引能够显著提高包含多个列条件的查询性能。 插入和维护成本较高：由于需要对多个列进行排序和维护，插入和更新操作可能较慢。
减少单列索引的数量：通过一个联合索引代替多个单列索引，可以节省存储空间。 部分匹配限制：联合索引在查询中只能高效利用前缀列，如果查询条件不包括索引的最左列，索引的利用率会降低。

（四）联合索引的使用建议

前缀匹配原则

联合索引在查询过程中是按照列的先后顺序来发挥作用的。所以，在设置查询条件时，应尽可能涵盖索引中的最左列（也就是前缀列）。举例来说，当我们创建了一个由 (c2, c3) 构成的联合索引之后，若查询条件里包含 c2 列，或者同时包含 (c2, c3) 列，那么就能够有效地利用该联合索引，从而提升查询效率。

适用场景

联合索引在需要同时依据多个列开展查询的场景中具有显著优势。以电商系统为例，为商品类别和价格区间创建联合索引，能够对相关查询进行有效优化，大幅提升查询效率。

在 InnoDB 存储引擎里，联合索引是一种至关重要的索引类型。它通过对多个列进行排序并构建索引，极大地提高了多列查询的性能。相较于单列索引，联合索引在处理复杂查询时表现得更为高效。

不过，要想充分发挥数据库的性能优势，合理设计和使用索引是关键。深入理解联合索引的工作原理以及掌握其最佳实践方法，能够助力我们更充分地挖掘 MySQL 数据库的潜力，提升数据处理和查询的整体效能。

五、总结

InnoDB 中的索引是提升数据检索效率的核心要素。本文着重介绍了三种主要的索引类型：

  1. 聚簇索引：它依据主键对数据进行排序，并存储完整的用户记录。这种索引方式尤其适合快速的主键查询以及范围查询，能够高效地定位和获取所需数据。
  2. 二级索引：该索引基于非主键列进行排序，旨在提升针对非主键列的查询性能。不过，由于其叶子节点仅存储部分数据，在查询完整记录时需要进行回表操作。
  3. 联合索引：它基于多个列进行排序，特别适用于复杂查询场景。联合索引能够显著提升涉及多列条件查询的效率，让查询操作更加高效快捷。

通过合理运用和配置这些索引，可以有效提高数据库查询以及数据操作的性能。深入理解索引的工作机制并掌握其最佳实践方法，对于优化 MySQL 数据库性能起着至关重要的作用。

参考文献、书籍及链接

原文阅读

上一页：Pandas使用教程 - 正则表达式在 Pandas 中的应用
下一页：深入剖析MySQL索引优化：提升数据库性能的核心技巧

@技术书栈 Powered by gin and hexo-theme-book.