MySQL的B-Tree索引底层结构以及具体实现原理详解

╰+哭是因爲堅強的太久メ 2022-09-02 04:24 44阅读 0赞

> 深入解析了Mysql的B+Tree索引底层数据结构，以及MyISAM和InnoDB 存储引擎的索引底层原理。

**上一篇文章中，我们介绍了索引的概念以及MySQL常见索引类型：[索引的概念以及MySQL七种索引类型][MySQL]。下面我们来看看常见的索引结构的底层实现原理。包括B-Tree、B+Tree的数据结构，以及MyISAM和InnoDB 存储引擎对于B+Tree索引的具体实现。**

### 文章目录 ###

*  1 索引的数据结构
 *  2 B-Tree索引的介绍
 *   *  2.1 为什么选择B-Tree结构
     *  2.2 B+Tree和B-Tree结构的区别
 *  3 MyISAM的BTREE索引实现
 *  4 InnoDB的BTREE索引实现
 *   *  4.1 ROWID
 *  5 B-Tree索引的有效查询类型

# 1 索引的数据结构 #

索引有多种数据结构，**在MySQL中，索引是在存储引擎层而不是服务器层实现的**。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一类型的索引，其底层实现也可能不同。

一般来说常见的索引结构是**HASH和BTREE**。  
![在这里插入图片描述][882d9592fdc641c3b0c1a3788041b838.png_pic_center]

# 2 B-Tree索引的介绍 #

如果在谈论索引的时候没有指明类型，那么多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据，大多数存储引擎都支持这种索引。

不过，**虽然大多都支持B-Tree索引，但是底层的存储引擎也可能使用不同的存储结构**，例如NDB集群存储引擎内部实际上使用了T-Tree结构存储这种索引，即使其名字是BTREE；而InnoDB和MyISAM则使用的是**B+Tree**结构（实际上很多存储引擎使用的是B+Tree，**即每个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历**），但其名字也还是叫**BTREE**。

我们通常所说**聚集索引、覆盖索引、组合索引、前缀索引、普通索引、唯一索引**等，没有特别说明，**默认都是使用B+Tree结构的索引**。

## 2.1 为什么选择B-Tree结构 ##

B-Tree是一种**自平衡的多路查找树**，其中B是**Balanced** （平衡）的意思，节点最大的孩子数目称为B-Tree的阶（order）。

**关于B-Tree和B+Tree，可以看看以前我的这篇文章：[数据结构—多路查找树中的2-3树、2-3-4树、B树、B+树的原理详解][2-3_2-3-4_B_B]，里面有更加详细的介绍，下面的内容都是总结自这篇文章。**

B-Tree节点也是天然有序的（排序），和平衡二叉树一样，B-Tree也能以`O(logn)`的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构，并且所有的叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。

**不同的是，平衡二叉树是一般是用于优化内存中的数据的查找速度的数据结构，B-Tree一般用于优化外存中数据的查找数据的数据结构，普遍运用在数据库索引结构和文件系统。**

对硬盘中的数据结构的某个节点的访问就会发起一次磁盘IO请求，IO操作耗时远大于内存中操作的耗时。如果一棵B-Tree的阶为1001（即1个节点包含1000个关键字），高度为2，它可以储存超过10亿个关键字。那么在这棵树上，寻找某一个关键字至多需要**两次硬盘的读取**即可。如果采用二叉树来存储这10亿个关键字，那么会需要非常多次数的IO请求。虽然两种数据结构最终找到某个数据所需的比较次数不会少，但是使用B-Tree时，可以经历非常少的IO次数（将一大批数据IO到内存中进行比较），而IO操作时非常耗时的。

由于**B-Tree每个节点能包含比二叉树更多的数据，同时树的层级比原来的二叉树更少**的特性，加上数据库充分利用了**磁盘预读**原理（磁盘数据存储是采用块的形式存储的，每个块的大小为4k或8k，每次IO进行数据读取时，同一个磁盘块的数据可以一次性读取出来（InnoDB存储引擎一次IO会读取的一页（默认一页16K））），因此采用**B-Tree**作为索引结构，能够减少定位记录时所经历IO次数，从而加快存取速度。可以说，**B-Tree的数据结构就是为内外存的数据交互准备的。**

以上特点也是“**为什么不选择红黑树或者其他二叉树作为外存中进行查找的数据结构**”等问题的原因。

## 2.2 B+Tree和B-Tree结构的区别 ##

**B+Tree可以说是B-Tree的升级版，相对于B-Tree来说B+Tree更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。**

**目前大部分数据库系统及文件系统都采用 B-Tree或其变种B+Tree作为索引结构。**

B-Tree和B+Tree的主要区别在于：

1.  B-Tree的非叶节点会存储关键字（key）及其对应的数据（data），而B+Tree的非叶节点只存关键字索引（key），不会存具体的数据（data），因此B+Tree的一个节点相比B-Tree能存储更多的索引元素，一次性读入内存的需要查找的关键字（key）也就越多，存储同样的数量的数据，B+Tree的高度可以更小，相对IO读写次数就降低了。
2.  B-Tree的查询效率并不稳定，最好的情况只查询一次（根节点），最坏情况是查找到叶子节点，而B+Tree由于非叶节点不存数据，而只是叶子节点中存储有关键字的索引。所有查询都要查找到叶子节点才算命中，查询效率比较稳定。这对于sql语句的优化是非常有帮助的。
3.  B-Tree的叶子节点之间都是独立的，而B+Tree所有叶子节点形成有序链表（可以是双向链表），只需要去遍历叶子节点就可以实现整棵树的遍历。提高范围查找的效率，而B-Tree不支持这样的操作或者说效率太低。

如下，是一颗典型的B-Tree，它的非叶节点中存储了key和data：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center]

如下，是一颗mysql索引表中实现的一种B+Tree，它的非叶节点中只存储了key，叶子节点之间使用链表关联起来：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 1]

这里的data，不同的存储引擎有不同的实现，对于MyISAM来说，data域存放的是数据记录的地址，对于InnoDB来说，data域保存了完整的数据记录。另外，即使InnoDB和MyISAM的BTREE索引都是采用的B+Tree结构，但其具体的存储实现上，仍有很大的不同。

磁盘中的数据页是按顺序一页一页存放的，读取的时候也都是以页为单位读取的，InnoDB引擎一页的大小通常为16K。而B+Tree结构的每一页的数据还会从按照从小到大的顺序进行排序。

B+Tree索引结构中，非叶节点作为索引页，key专门存放索引值，data域存储的是指向另一页的指针，key索引值的大小就是data对应的页的数据中的最小索引值，即对应页最左边的数据。

**叶节点作为数据叶，key同样是索引，但data存放了对应的数据，这个数据可能是主键id，指向表数据的指针，或者是真实的表数据，两两相邻的数据页之间会采用双向链表的格式互相引用，而每一页中的数据之间则是通过单链表来连接的。**

# 3 MyISAM的BTREE索引实现 #

**MyISAM存储引擎的数据文件和索引文件是分开存储的，索引文件名为tablename. MYI，而数据文件名为tablename.MDB，并且表空间中可存储行记录数。**

**在MyISAM引擎中，使用 B+Tree作为BTREE索引结构，叶节点的关键key是索引列的值，而data 则是保存数据记录的地址。**

假设有一个user表，具有id、age、name字段，其中id是主键。下图是 在MyISAM 引擎中user表的**主键索引**图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 2]

假设我们要根据主键id精确查询值为12的数据，则需要进行4次磁盘IO，其中3次索引IO，一次数据IO。

假设我们要根据主键id精确查询值为10~20的数据，则需要进行5次磁盘IO，其中4次索引IO，一次数据IO。多出来的一次索引IO是对叶子节点横向进行的范围查找，并且id的查询范围跨越了不同的数据块，如果范围在同一个数据块中，则不需要多一次的范围查找。

**MyISAM存储引擎的主索引和辅助索引(Secondary key)在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。所以在查询时即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。**

对于user表，假设age字段是辅助索引，那么下图是 MyISAM 引擎中user表的**辅助索引**图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 3]

另外，加载到内存中的数据将可能被MyISAM缓存起来，因此不是每次的查找都会进行磁盘IO。

# 4 InnoDB的BTREE索引实现 #

**InnoDB 也使用 B+Tree 作为BTREE索引结构。一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小。**

**MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，而InnoDB的数据文件本身就是索引文件，数据和索引存储在一个文件t\_tablename.ibd中。**

**其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录，InnoDB表数据文件本身就是主索引，索引的key是数据表的主键，这种索引就是主键索引，也称为聚集（聚簇）索引，相应的，MyISAM存储引擎的索引被称为非聚集（聚簇）索引。**

**聚簇索引的一个叶子节点存储的是被查找的数据行所在的一个页（同理一个非叶子节点则是存放的一个索引页），通过B+Tree最终找到的也是一个数据页，然后数据库通过把数据页读入内存，再在内存中进行查找（数据是有序的），最后得到要查找的数据。数据页占用固定大小的空间，但是却不一定会被数据填满，因为各种原因，比如，对一个满页插入数据时会使用页分裂计数将一页分裂成两个页，造成每一个数据页有近50%的空闲空间，形成很多磁盘碎片，可能导致全表扫描变慢（数据页和索引页都有可能页分裂）。**

假设有一个user表，具有id、age、name字段，其中id是主键。下图是在InnoDB引擎中user表的**主键索引**图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 4]

假设我们要根据主键id精确查询值为12的数据，则需要进行3次磁盘IO，其中3次索引IO。

假设我们要根据主键id精确查询值为10~20的数据，则需要进行4次磁盘IO，其中4次索引IO。多出来的一次索引IO是对叶子节点横向进行的范围查找，并且id的查询范围跨越了不同的数据块，如果范围在同一个数据块中，则不需要多一次的范围查找。

**因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列（唯一索引且not null）作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含ROWID字段作为主键，这个字段长度为6个字节，类型为长整形。**

**InnoDB存储引擎除了主键/聚簇索引之外的其余的索引都作为辅助索引，也称为二级索引，或者非聚集（聚簇）索引。需要注意的是，辅助索引的 data 域存储相应记录主键的值而不是地址，而MyISAM的辅助索引的data域同样存放的是数据的地址。**

假设user表的age字段作为辅助索引，下图是在InnoDB引擎中user表的**辅助索引**图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 5]

**在根据主索引（主键）搜索时，直接找到 key 所在的节点的data即可取出数据，而在根据辅助索引查找时，则需要先走辅助索引取出对应的主键值，然后再走一遍主索引。**

根据在辅助索引树中先获取的主键id，然后再到主键索引树检索数据的过程（即二次查询）称为回表查询。假设我们要根据age字段查询age值为18的数据，则需要进行6次磁盘IO，其中3次辅助索引IO，3次回表主索引的IO。**我们在应用中应该尽量使用主键查询。**

**因此，在设计表的时候，不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。也不建议使用非单调的字段作为主键，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整树的平衡，导致效率降低，而使用自增或者单调字段作为主键则是一个很好的选择（这一点对于MyISAM同样有效）。**

## 4.1 ROWID ##

**InnoDB主键策略：每个InnoDB引擎的表必须有一个“聚簇索引”，通常是通过主键索引字段构建。如果没有指定主键，则会通过第一个not null的唯一索引字段构建，如果这两个都没有，那么InnoDB会给你创建一个不可见的，长度为6个字节的row\_id字段来构建。（https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html）。**

**InnoDB维护了一个全局的`dict_sys.row_id`值，所有无主键并且没有not null的唯一键的InnoDB表，每插入一行数据，都将当前的`dict_sys.row_id`值作为要插入数据的row\_id，然后把`dict_sys.row_id`的值加1。**

实际上，在代码实现时row\_id是一个长度为8字节的无符号长整型(bigint unsigned)。但是，InnoDB在设计时，给row\_id留的只是6个字节的长度，这样写到数据表中时只放了最后6个字节，所以row\_id能写到数据表中的值，就有两个特征：

1.  row\_id写入表中的值范围，是从0到2^48-1；
2.  当row\_id为2^48-1时，如果再有插入数据的行为要来申请row\_id，拿到以后再取最后6个字节的话就是0。

也就是说，写入表的row\_id是从0开始到2^48-1。达到上限后，下一个值就是0，然后继续循环。

在InnoDB逻辑里，申请到row\_id=N后，就将这行数据写入表中；如果表中已经存在row\_id=N的行，新写入的行就会覆盖原有的行。

**实际上，InnoDB 存储引擎为每行数据添加了三个 隐藏字段：**

1.  DB\_TRX\_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头Record header 中的 deleted\_flag 字段将其标记为已删除。
2.  DB\_ROLL\_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空。
3.  DB\_ROW\_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id  
    来生成聚簇索引，这个字段值在所有的表中唯一。

# 5 B-Tree索引的有效查询类型 #

**所谓有效查询类型，就是说发生了如下情况时，可以使用索引扫描查询，而不是全表扫描。**

1.  **全值匹配**：全值匹配指的是和索引列中的所有列进行匹配，例如查询name为“张三”的用户，此时就是全值匹配，对于多列索引来说，就是所有的索引列都完全匹配。
2.  **匹配最左前缀**：对于多列索引可以从右向左一次匹配，后面会想详细讲解最左前缀。
3.  **匹配列前缀**：可以只匹配某一列的值的开头部分，例如查询所有姓“张”的用户。对于多列索引，则只能对最左列使用列前缀匹配。
4.  **匹配范围值**：由于B-Tree的有序性，可以进行基于索引列的范围匹配。所以索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY 和DISTINCT等之句的查询需求。
5.  **精确匹配和范围匹配**：对于多列索引，可以对左边的列进行精准匹配，对右边的列进行范围匹配。但对于范围匹配之后的列则不能走索引，后面会详细讲解。
6.  **只访问索引的查询**：B-Tree通常支持“只访问索引的查询”，即查询只需要访问索引，比如姓名列就是索引列，此时查询所有姓“张”的用户的姓名，那么我们无序访问数据行，因为索引就包含所要返回的全部结果，这是一种“覆盖索引”的优化，避免回表。

**基于这些有效查询类型，后面我们讲到索引优化部分的时候会非常有用。**

**参考资料：**

1.  《 MySQL 技术内幕: InnoDB 存储引擎》
2.  《高性能 MySQL》

> 如有需要交流，或者文章有误，请直接留言。另外希望点赞、收藏、关注，我将不间断更新各种Java学习博客！

[MySQL]: https://blog.csdn.net/weixin_43767015/article/details/119109385
[882d9592fdc641c3b0c1a3788041b838.png_pic_center]: /images/20220829/2591c20001fd4bf29d750616af5238be.png
[2-3_2-3-4_B_B]: https://blog.csdn.net/weixin_43767015/article/details/105880671
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20220829/d745ea99ab43452381926a27b4a63000.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20220829/989517f21a3441c694f5c389ad2fb72d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20220829/2c6ad468ea30459db0fc1fe2b95faf24.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20220829/61fcd7af37c549c7a2db13b1ecb01e69.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20220829/0659d7efba3a4ac58bb01efbfe4437eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20220829/a7b6685eb3cf4d1a9f757a1824c703d2.png