MySQL 索引底层 B+Tree 原理解析

一、前言

• 索引是帮助高效获取数据排好序的数据结构，任何数据库都会使用到索引，常用索引数据结构有，二叉树、平衡二叉树、Hash表、红黑树、B-Tree、B+Tree，MySQL中会使用B+Tree，这里会对部分数据结构以及MySQL中是如果存储获取数据的原理进行详细说明。

二、B-Tree 和 B+Tree 的区别

• B-Tree

  • B-Tree是一种多路二叉树
  • 叶节点具有相同的深度，叶节点的指针为空；
  • 所有索引元素不重复
  • 节点中的数据索引从左到右递增排列
    

• B+Tree

  • B+Tree是B-Tree的变种
  • 非叶子节点不存储data，只存储索引(冗余)，这样可以提升索引查找性能
  • 叶子节点包含所有索引字段
  • 叶子节点用指针连接，提高区间访问的性能
    

B-Tree和B+Tree之间一个很大的不同，是B+Tree的节点上不储存value，只储存key，而叶子节点上储存了所有key-value集合，并且节点之间都是有序的。这样的好处是每一次磁盘IO能够读取的节点更多，也就是树的度(Max.Degree)可以设置的更大一些，因为每次磁盘IO读取的磁盘页数是一定的。例如，每次磁盘IO能够读取1页=4kb，那么省去value的情况下同样一页数据能够读取更多的key，这样就大大减少了磁盘的IO次数。

此外，B+Tree也是排好序的数据结构，数据库中><或者order by等都可以直接依赖这一特性。

MySQL中对于索引使用的主要数据结构也是B+Tree，目的也是在读取数据时能够减少磁盘IO。

三、InnoDB 和 MyISAM 存储引擎索引存储区别

MyISAM

• 在MyISAM储存引擎中，数据和索引文件试试分开储存的，数据存在 表名.MYD 的文件中，索引单独存在 表名.MYI 的文件中。
  

• MyISAM索引文件和数据文件是分离的(非聚集)，并且主键索引和辅助索引（二级索引）的储存方式是一样的。

InnoDB

• 在InnoDB中，数据和索引文件是合起来储存的，都会存储在 表名.ibd 文件中。
  

• InnoDB中索引文件和数据文件是同一个文件（聚集），并且主键索引和二级索引储存方式有所不同，如图所示，二级索引的叶子节点不储存数据，仅储存主键ID。
  

• 这里注意几点：

  • 1、为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

    • 如果我们在创建表时不设置主键，InnoDB会自动帮我们从第一列开始筛选一列数据不重复的列做为主键，如果找不到这样的列，就会创建一个隐藏的列（rowid）做为主键，这会增加很多MySQL的工作，所以建议我们在创建InnoDB表时一定要设置主键。
    • 如果主键id是无序的，那么很有可能新插入的值会导致当前节点分裂，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。反之，如果每次插入有序，那就会在当前页后面连续写入，写不下就会重新分配一个节点，内存都是连续的，这样效率自然也就最高了

  • 2、为什么非主键索引结构叶子节点存储的是主键值？

    • 非主键索引的叶子节点存储主键值而非全部数据，主要也是为了一致性和节省空间。如果二级索引储存的也是数据，那么每次插入MySQL都不得不更新每棵索引树，这样就加剧了新增编辑时的性能损耗，并且这样一来空间利用率也不高，必然产生了大量冗余数据

四、InnoDB 联合索引底层数据结构

联合索引又叫复合索引，假设我们有一个用户表有5个字段（id、name、age、job、address），我们建立name、age、job这三个字段的联合索引 例如：

`idx_name_age_job` (`name`,`age`,`job`);

索引结构：

• 比较相等时，先比较第一列的值，如果相等，再继续比较第二列，以此类推。

• 了解了联合索引的存储结构，我们就知道了索引最左前缀优化原则是怎么回事了，在使用联合索引时，对于索引列的定义顺序将会影响到最终查询时索引的使用情况。例如联合索引（name,age,job），MySQL会从最左边的列优先匹配，如果最左边的带头大哥name没有使用到，在未使用覆盖索引的情况下，就只能全表扫描。

• 联合底层数据结构思考：MySQL会优先以联合索引第一列匹配，此后才会匹配下一列，如果不指定第一列匹配的值，也就无法得知下一步查询哪个节点。

五、MySQL 中三次磁盘IO最大能检索多少数据

在InnoDB里，每个页面默认16KB，可以通过以下SQL语句查询到，当然这个值是可以调的，既然官方给出这个阈值说明再大的话会影响磁盘IO效率。

• 这里可以看到 MySQL Innodb查询页数据大小为 16384B，即16KB

• 假如：B+Tree高度为3，B+Tree的表都存满了，主键索引的类型为BigInt，大小为8B，指针存储了下个节点的文件地址，大小为6B。最后一层，假设存放的数据data为1K 大小，那么一页里就可以存储16K/14=1170个(主键+指针)。

• 一颗高度为2的B+树能存储的数据为：1170 * 16 = 18720（条）

• 一颗高度为3的B+树能存储的数据为：1170 * 1170 * 16 ≈ 2190（万条）

• 同理，在高度h=4时，总行数=1170 * 1170 * 1170 *16 ≈ 256（亿条）！！！

而且一般来说，MySQL会把 B+Tree 根节点放在内存中，那只需要两次磁盘IO就能检索千万级数据，三次磁盘IO就能检索百亿级数据。