MySQL - 深入解析MySQL索引数据结构

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

• 索引是针对表来说的，不是针对数据库来说的（建表的sql语句中的index就是索引）；
• 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储；
• 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

先介绍一款可以帮助理解数据结构的网站：Data Structure Visualizations

1. 索引的数据结构演进

1.1 链表

链表是一种物理存储单元上非连续、非顺序的存储结构，数据元素的逻辑顺序是通过链表中的指针链接次序实现的。

有一张用户表，有id、username、password三个字段。现在对这个表进行查询。

假设上表中的数据存储在链表上，执行以下SQL我们可以发现影响查询速度的是这条数据在第几行

-- 查找1次
select * from user where password = 123;
-- 查找2次
select * from user where password = 234;
-- 查找3次
select * from user where password = 78;
-- 查找4次
select * from user where password = 567;
-- 查找5次
select * from user where password = 70;
-- 查找6次
select * from user where password = 379;

假设表中有1000万条数据，现在要查询第1000万条数据，这样就需要查找1000万次。顺序查找的问题是存储数据的结构是线性的，也就是说想要查询第2条数据，我需要知道第1条数据是什么。 大家可能会考虑到为什么不要数组，直接通过下标就可以查到数据，问题是数组是固定长度的，那么数组长度定义为多少合适呢？

1.2 二叉树

要解决链表的问题，我们可以用树来解决，先来看一下二叉树：二叉树是一种特殊的树，每个节点最多有两个子节点，值从左到右依次递增。

二叉搜索树通过增加了一条搜索路径，提高了查询效率，查找的效率取决于树的深度（高度）。

• 问：为什么索引的数据结构不用二叉树？

因为当值依次递增插入时，二叉树会退化成链表，对加快查询没有任何作用。

时间复杂度（代码执行的次数）：O(N)

1.3 红黑树（自平衡二叉查找树）

特点：

1. 每个节点只能是红色或黑色；
2. 跟节点必须是黑色；
3. 红色的节点，它的叶节点只能是黑色；
4. 从任一节点到其叶子节点的所有路径都包含相同数目的黑色节点。
   

为了解决二叉树变成链表的问题，出现了红黑树。当数据插入时，红黑树通过旋转和变色来达到平衡。这样就弥补了二叉树退化成链表的尴尬。 平衡后树的高度就变成了4层，相比于二叉树，效率更高。

• 问：为什么索引的数据结构不用红黑树？

因为当值依次递增插入时树的高度会变得特别高。就例如上图查询0009只用4次，那查询1000万呢？树的高度大概是24，最坏的情况需要查找24次！查找24次是概念呢？，MySQL存储数据是存文件的，每次查找都需要读一次文件到内存，即最坏的查询情况需要读24次文件到内存，也就是24次IO操作，这是非常耗时的。

时间复杂度为:O(log2N)

1.4 B树（多路平衡搜索树）

特点：

1. 索引值和data数据分布在整棵树结构中
2. 每个节点可以存放多个索引值及对应的data数据
3. 树节点中的多个索引值从左到右升序排列
   

B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

因为树的高度问题，出现了B树（多路平衡搜索树），B树的思路是多叉树。只要分叉越多，那么每一层可以存放的元素就越多，树的层级自然而然就会降低。那么分叉肯定是越多越好，最多可以多到什么程度呢？这取决于MySQL一页可存储的数据量是多少，我们可以通过SQL命令来查询：

show global status like 'Innodb_page_size';

可以看到MySQL默认一页是16384字节，大约是16kb。假设一条数据1KB为例，一颗高度为3的B树，可以存储的数据个数是 16 * 16 * 16 = 4096

问：为什么索引的数据结构不用B树？

虽然B树相对于红黑树，树的高度降低了，但是随着数据量的增多，树的高度还是会变得很高，效率会变得特别低。而且对范围查找也不方便。

1.5 B+树

特点：

1. 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
2. 叶子节点包含了所有的索引值和data数据
3. 叶子节点用指针连接，提高区间的访问性能

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

1.6 哈希

Hash 底层实现是由 Hash 表来实现的，是根据键值 <key, value> 存储数据的结构。非常适合根据key查找 value 值，也就是单个 key 查询，或者说等值查询。其结构如下所示：

从上面结构可以看出，Hash索引可以方便的提供等值查询，但是对于范围查询就需要全表扫描了。Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引 、InnoDB 自适应哈希索引。

InnoDB提供的自适应哈希索引功能强大，接下来重点描述下InnoDB 自适应哈希索引。

InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。

InnoDB自适应哈希索引：在使用Hash索引访问时，一次性查找就能定位数据，等值查询效率要优于B+Tree。

自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建立哈希索引来加速访问。另外InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉。

show engine innodb status \G; 
show variables like '%innodb_adaptive%';

2. 总结

• 为什么mysql使用的是B+树

准确的表述：为什么mysql的InnoDB和MyISAM存储引擎的索引使用的是B+树

1. hash表，等值查询是很快的，但是不满足常用的范围查找且相邻的两个值之间没有关系，而且hash比较消耗内存。
2. 二叉树/平衡二叉树/红黑树等都是有且仅有2个分支，共性就是数据量大的时候树的深度变深，增加IO的次数。
3. B树会在节点上存储数据，这样一页存放的key的数量就会减少，增加树的深度。
4. B+树中非叶子节点去除了数据，这样就会页中key的数量，而且叶子节点之间是通过链表相连，有利于范围查找和分页。