深入分析InnoDB索引选型为B+Tree的原因

1 本文说明

  本文只针对InnoDB存储引擎的索引选型为B+Tree做数据结构和算法上的分析，对本文中涉及到的其它数据结构如树和二叉树，索引的分类如聚簇索引、普通索引，另外的一些名词事务、主键、页、回表、IO等不作说明
  关于各类二叉树的结构和算法分析有兴趣的话可以参考本人之前写的另一篇博客：一篇文章带你玩转二叉查找树（AVL + 红黑树）

2 什么是索引

  索引是帮助MySQL高效获取数据的数据结构，在数据结构之外还维护着满足特定查找算法，下图就是一种可能的索引示例：

• 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址 为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

3 索引优势和劣势

• 优势：索引提高数据检索的效率，降低数据库的磁盘IO次数
• 劣势：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表执行DDL语句的时候：因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；另外实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引需要占用磁盘空间

4 MySQL索引数据结构选择

  上面知道MySQL索引是一种具备特定查询顺序的数据结构，那么什么数据结构可以成为索引呢？像常见的线性数据结构：数组、hash表、链表，非线性数据结构：二叉平衡树、红黑树、B树、B+树等都可以作为数据存储的数据结构。数据结构的选型决定了当前程序的存储及查询效率，当然除了效率外也会有空间复杂度上问题，虽然现今存储设备的升级及价格的降低，相信很多公司都会的优先选择牺牲空间换时间！

  那么MySQL这种关系型数据库以及使用磁盘存储数据的特点，应该选择哪种数据结构更为合适？

1）存储引擎

  选择哪种数据结构存储数据其实适合MySQL没有关系的，是和数据库表使用哪种存储引擎有关的，不同的存储引擎底层的实现自然也会不一样，这里说一下MySQL默认自带的两种存储引擎是MyISAM和InnoDb

特性	MyISAM	InnoDB
事务支持	不支持	支持
并发性能	表级锁定，对整个表进行读写操作	行级锁定，允许并发对不同行进行读写操作
数据一致性	不支持完整性约束和外键约束	支持完整性约束和外键约束
故障恢复	相对简单，恢复速度较快	故障恢复能力更强，需要更长的恢复时间
应用场景	读密集型应用，对事务支持要求较低	对事务支持要求较高，并发性能较好，数据完整性要求较高
默认使用	MySQL自带的系统表默认使用	自定义数据库创建的表默认使用

1. 事务支持：
   • MyISAM不支持事务，无法提供事务的ACID特性。每个操作都被视为一个独立的事务，所以效率上较高与InnoDB
   • InnoDB支持事务，能够提供事务的原子性、一致性、隔离性和持久性，保证数据的完整性和一致性
2. 并发性能：
   • MyISAM使用表级锁定，一次只能对整个表进行读写操作。这限制了并发性能，当有并发写入时，可能会出现锁冲突导致阻塞
   • InnoDB使用行级锁定，可以同时对不同行进行并发读写操作，减少了锁冲突的可能性，提供了更好的并发性能，当然这个行锁是以索引为条件的，如果不使用索引还是表锁
3. 数据一致性：
   • MyISAM不支持完整性约束和外键约束，不会自动处理数据的一致性。这意味着在应用层需要自行处理数据的完整性和关联关系
   • InnoDB支持完整性约束和外键约束，能够自动处理数据的一致性，保证数据的完整性和关系的正确性
4. 索引：
   • MyISAM使用B+Tree索引结构，适用于大部分查询场景，但对于大量的写操作和频繁更新的表，性能可能会受到影响。但是不提供聚簇索引，所以MyISAM主键索引叶子节点的data数据存放的是索引所在行的磁盘地址，即该存储引擎的索引文件和数据文件是分开的，如下图所示
   • InnoDB也使用B+Tree索引结构，但它还提供了聚集索引（Clustered Index）的概念，将数据按照主键的顺序物理存储，提高了范围查询的性能。而InnoDb主键索引的叶子节点数据存放的是叶子节点所在行的其它列数据
5. 故障恢复：
   • MyISAM相对简单，容易恢复，崩溃后的恢复速度较快
   • InnoDB具有更强大的故障恢复能力，通过事务日志（Redo Log）和回滚日志（Undo Log）来保证事务的持久性和一致性，但在故障恢复方面可能需要更长的时间

  使用MyISAM和InnoDB创建两个数据库表，可以看到在data数据下的test数据库下的test_myisam（test_myisam.frm、test_myisam.MYI、test_myisam.MYD）、test_innodb（test_innodb.frm、test_innodb.ibd）这两个表的磁盘文件，如下是test_myisam表的磁盘文件共三个

• test_myisam.frm：存放了test_myisam表的结构数据
• test_myisam.MYI：存放了test_myisam表的索引数据
• test_myisam.MYD：存放了test_myisam表的行列数据

而test_innodb的磁盘文件共两个

• test_innodb.frm：存放了test_innodb表的结构数据
• test_innodb.ibd：存放了test_innodb表的索引及行列数据

  综上可以得到：使用myisam存储引擎的数据库表的索引文件和数据文件是分开的，所以在查询数据文件之前必须先查询索引文件；而使用innodb存储引擎的数据库表的索引文件和数据文件是同一个文件，即有可能是根据索引直接在当前磁盘文件中查询到对应的数据内容！所以查询效率比较的话理论上（不涉及回表情况下的时间复杂度）是innodb存储引擎的表要优于使用了myisam存储引擎的表

create table test_myisam
(
    id   bigint(19) auto_increment unique primary key,
    name varchar(30) default '' not null
) ENGINE = MyISAM comment 'MyISAM存储引擎测试表';


create table test_innodb
(
    id   bigint(19) auto_increment unique primary key,
    name varchar(30) default '' not null
) ENGINE = InnoDB comment 'InnoDB存储引擎测试表'

2）各种数据结构比对

最常用的数据结构汇总：

• 二叉树：每个节点最多有两个子节点的树结构，左子节点和右子节点
• 平衡二叉树：也称为AVL树，是一种特殊的二叉树，它的左右子树的高度差不超过1。通过自动调整节点的插入和删除操作，保持树的平衡
• 红黑树：一种自平衡的二叉查找树，它具有以下特性：每个节点都有一个颜色属性，红色或黑色；根节点和叶子节点（NIL节点）是黑色的；红色节点的两个子节点都是黑色的；从任意节点到其每个叶子的路径上包含相同数量的黑色节点。红黑树的平衡性质保证了树的高度是对数级别的
• 数组：一种线性数据结构，它由连续的内存空间组成，用于存储相同类型的元素。数组通过索引访问元素，具有快速的随机访问能力，但插入和删除操作可能需要移动大量元素
• Hash表：种使用哈希函数将键映射到存储桶的数据结构。哈希表提供了快速的插入和查找操作，通过哈希函数计算键的哈希值，将其映射到存储桶中。解决哈希冲突的常见方法有链地址法和开放地址法
• B-Tree：一种自平衡的搜索树，被广泛应用于文件系统和数据库中。B-Tree具有多个子节点，通常用于处理大量数据和磁盘存储。它的特点包括节点具有多个键和子节点、节点中的键按顺序排列、所有叶子节点位于相同的深度等
• B+Tree：基于B-树的一种变体，也用于处理大量数据和磁盘存储。B+树与B-树类似，但在内部节点中只存储键，而不存储数据，数据只存在于叶子节点中。B+树的叶子节点通过指针连接形成有序链表，提供了高效的范围查询和顺序访问能力

以上的数据结构为什么MySQL使用了B+Tree作为索引的数据结构？

1. 二叉树不适合的原因：因为MySQL的数据/索引都是存储在磁盘的，数据量大的时候二叉树的高度无疑会增大，多次的磁盘IO操作无疑是效率极低的，而且查询需要按照范围查找的话需要二叉树有序，普通的二叉树如果需要有序的话最坏的情况是成为了一个线性链表，这时候的时间复杂度就是O(n)，如果使用AVL和红黑树虽然是自平衡的二叉树，时间复杂度是在O(log n),但是数据量大的话树高也是无法保证的，假如有1024个数据这时候的树的高度最低也是10层了（红黑树还可能比AVL树高呢），也是需要10次以上的IO这显然也是不能接受的，况且频繁的插入和顺序还需要调整树的结构以保证平衡，所以二叉树是不适合作为MySQL数据的存储数据结构的！
2. hash不适合的原因：在创建索引的时候看到了是可以勾选hash的，那么为什么不选择hash呢？虽然hash的时间复杂度理论是O(1)（没有hash冲突的前提下），但是hash是一个散列表，没有办法支持范围查询的，不支持范围查询的数据库表的场景在业务开发中应该没有吧？
3. 数组不适合的原因：从二叉树和hash不适合的原因得出了最重要的两个结论，一是降低高度减少磁盘IO次数二是支持范围查询，那有序数组这种线性结构完美适配了这一点，为什么不使用？原因是数据库基本都是涉及增删改的，不只是只有查询，数据的增删都是有可能改变数组的结构的（扩容/收缩），就有可能涉及到数据的复制和新的存储空间的创建，性能影响是极大的！但是用数组来存储一些静态的数据如历年的账单数据、年度获奖数据、历史订单数据等静态数据（需要备份的数据），那数组无疑是比任何数据结构更适合的存储结构！
4. B-Tree：B-Tree其实就已经很适合作为MySQL的存储数据结构了，B+Tree其实就是B-Tree的一个升级，所以下面就重点说明一下B—Tree的小缺点和B+Tree优化后比B-Tree更适合作为存储结构的原因

3）B-Tree和B+Tree对比

  说明：树可视化模拟器地址，下面的树图都是使用这个地址模式生成的，动态效果自行去演示，这里只粘贴了静态图；之前分享的二叉树博客的AVL数和红黑树的模式也是使用这个网址提供的树可视化模拟器生成的，很棒的网站！看B-Tree和B+Tree这种多路复用的树结构之前，建议先参考我之前分享的一篇博客：一篇文章带你玩转二叉查找树（AVL + 红黑树）

  B-Tree和B+Tree都是一棵自平衡的多路复用树，自平衡是为了保证有序可以支持范围查询，多路复用是为了降低树的高度降低IO的频率从而提升查询效率，那这两棵树有什么区别呢？如下图所示的B-Tree和B+Tree：使用的数据都是同一批数据共16个值构建出来的两棵树，指定每个节点最大存4个数据（InnoDB每个节点是一个页大小，16KB，下面说明），由下面两图可得：

• 树高都是3
• B-Tree的节点总数是9个，B+Tree的节点总数是12个（其实是冗余了叶子节点的第一个元素到上一层）
• B+Tree的叶子节点之间还维护了一个单向链表
• 看到这两棵树的结构感觉上是B-Tree明显好于B+Tree，但是需要结合MySQL存储的数据是基于磁盘的，避免不了磁盘IO

  对于B-Tree没有冗余叶子节点的，根据索引定位到具体节点数据后如1004索引，那么1004索引中的data数据存放的就是当前行的其它列数据，虽然当前元素存放了当前行的完整数据不用回表，但是这样做有两个坏处：

1. 一是这个节点中的元素都是存放的完整数据，那么这个节点势必存放的元素就会减少，进而树的高度就会增高，IO自然较频繁
2. 二是假如这个数据库表有多个索引，每个索引的元素都存放一份完整的当前行数据，数据过于冗余，且数据更新需要维护多个索引，效率较低

  而B+Tree的所有data数据其实都存放在叶子节点中，冗余叶子节点的第一个元素到上一层的目的是为了找到叶子节点对应的元素范围，那B+Tree为什么把数据当存放在叶子节点，即使在非叶子节点找到了对应的元素还需要到叶子节点找到具体数据？因为就是为了解决B-Tree树高和数据冗余的问题：

1. 是非叶子节点的元素都不存放当前行数据，所以这个节点可以存放更多的元素，而叶子节点存放的是当前行的数据（非聚簇索引存放的是聚簇索引的索引值）
2. 是避免了数据的冗余，如果当前索引是聚簇索引定位到的叶子节点其实就是当前行数据，当前索引是非叶子节点存放的是聚簇索引的索引值，会进行一次回表查询进而得到当前行数据，这样做的话当前行数据只会在当前表的聚簇索引存储一份，不会冗余到其它索引上，更新或删除的时候只需要维护对应的聚簇索引和普通索引的叶子节点即可
3. 是叶子节点之间加了一个单向指针更便于范围查询！

  接下来可以分析一下B-Tree和B+Tree可以存储多少数据，先说明一下规则：

1. 都是三层的结构
2. 每个节点都是一个页的大小默认是16KB
3. 就以聚簇索引为例（非聚簇索引的话结果太欺负B-Tree了）
4. 每个元素的当前行数据大小为1KB
5. 都是理论上的极限数据（所以别抬杠计算结果）

  B+Tree计算：页的大小默认是16K，假如每个元素的索引是bigint（8个字节，int是4个字节），对应的指针大小是6个字节，那么一个节点就可以存储：16 × 1024 ÷ (8 + 6) ≈ 1170 个元素；所以第二层也约有1170个节点对应就有 1170 × 1170 = 1,368,900 个元素；第三层是叶子节点，如果是非聚簇索引那么data数据存放的是主键元素那就是 1170 × 1170 × 1170 = 1,601,613,000个元素（已经过亿了），如果是聚簇索引那么data数据存放的是当前索引的其它列数据，假设数据大小是1K（索引8个字节就忽略了），那么每个页就能存放16个索引和对应元素了，那么叶子节点就可以存放：1170 × 1170 × 16 = 21,902,400个元素（二千多万个完整元素啊！而且正经开发那个数据一行能超过1K的啊！）

  B-Tree计算：假设索引和数据大小也是1K：第一层只能存16个元素，第二层16 × 16 = 256个，第三层：16 × 16 × 16 = 4096个，是不是发现同样的三层树高一个页所能存储的数据少了很多，这样的话只能通过增加树高来解决数据量大的问题，使用B-Tree好处就是找到索引之后能直接获取到数据内容，不用继续往叶子节点遍历，但是这样做不管是时间上和空间上都是无法和B+Tree比较的

  综上所述，所以清楚了为什么B+树是最适合作为MySQL数据库表存储的数据结构了吗？