mysql索引全解

引言

写本篇的起因是看到字节跳动的题里有两道关于mysql的题为：

• 聊聊MySQL索引的发展过程？是一来就是B+Tree的么？从 没有索引、hash、二叉排序树、AVL树、B树、B+树 聊。
• 有了解过InnoDB底层的索引结构么？

因为之前有被问到过，但我觉得答得都不是很完整，所以想在这里做一个总结笔记，结合几篇参考文献，方便日后查阅。

mysql索引思维导图

mysql索引可选结构

本节片段主要参考 MySQL索引背后的数据结构及算法原理

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

哈希索引

哈希算法：也叫散列算法，就是把任意值(key)通过哈希函数变换为固定长度的 key 地址，通过这个地址进行具体数据的数据结构。

我们可以来看一个例子是：

select * from user where id=7;

这条sql应该会查找到name = Lily以及birthday = 1996-01-25的这条记录，hash算法在内部会将id = 7进行hash(7) == addr7，addr7是一个物理地址，比对成功后，通过映射关系直接能找到数据库中的这条记录，所以它是做数据快速检索的有效利器。时间复杂度为 O（1）

但哈希索引的缺点是做不了范围查找，同样是上述sql，将其变为：

select * from user where id>4;

那么这会让哈希无法进行查询，具体是啥效果我也不太知道，但不外乎两种，第一种情况是根本没有走索引了，按寻常方式进行查询；第二种情况就是把所有数据全部读到内存里，然后再内存中通过索引搜寻到范围数据。但两种基本没啥区别，如果hash编码弄成64位的UUID，那只会更加无序以及占用磁盘空间，所以mysql虽然提供了在创建索引的时候，可以创建hash索引，但如果范围查找的次数很少，或许也是可以进行尝试的。

二叉搜索树

二叉搜索树,Binary Search Tree,BST,它或者是一棵空树,或者是具有下列性质的二叉树:

1. 若它的左子树不空,则左子树上所有结点的值均小于它的根结点的值;
2. 若它的右子树不空,则右子树上所有结点的值均大于它的根结点的值;
3. 它的左、右子树也分别为二叉搜索树。

根据定义，它是可以作为mysql的索引结构或者文件查询系统的，但又可以从图里看到一个问题：虽然每次经过节点时，最多可以减少一半的可能，但是如果在极端环境下，比如头部是3，以后插入的树节点要大于3的情况，那么整个树结构都会变成线性，也就构不成二叉树平衡的概念了，所以为了这棵树的各个分支的高度是均匀的，之后就引入了平衡搜索二叉树，也叫AVL树，这个下面和红黑树一起说。

关于搜索二叉树，可能我先说明了它的缺点，但它的特点是解决了上述hash索引不能解决的范围查找的问题，比如说我们要查找大于4的数据情况，那么根据搜索树的定义，找到4后将左子树全部提取出来就都是大于4的部分，这也可以有效的提高效率。所以二叉查找树的时间复杂度是 O(lgn)

红黑树与AVL树

二叉查找树存在不平衡问题，因此学者提出通过树节点的自动旋转和调整，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了。基于这种思路的自调整平衡状态的二叉树有 AVL 树和红黑树。

那么关于AVL树，我们可以看如下动图：

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。下图左边是AVL树，它的任何节点的两个子树的高度差<=1；右边的不是AVL树，其根节点的左子树高度为3，而右子树高度为1；

那么根据这两图，我们可以发现AVL有效解决了上面二叉搜索树会“线性增加”的问题，比如动图中插入6的时候，二叉树的平衡被打破，那么就会进行旋转来达到新的平衡，具体旋转步骤为：

• 将根节点的右节点作为新根节点。
• 将新根节点的左节点作为原根节点的右节点。
• 将原根节点作为新根节点的左节点。

也是有这种特性，AVL树也可以当做排序树应用在其它方面，而它的时间复杂度为O（logn），支持范围查找，不存在极端性能情况也大大降低了搜索时间。

而同样是有自旋的定义，之后又提出了红黑树的概念，在时间复杂度与性质都与AVL树相似的情况下，红黑树的特性为：

1. 每个节点或者是黑色，或者是红色。
2. 根节点是黑色。
3. 每个叶子节点（NIL）是黑色。 [注意：这里叶子节点，是指为空(NIL或NULL)的叶子节点！]
4. 如果一个节点是红色的，则它的子节点必须是黑色的。
5. 从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。

具体的理解我就不引申了，因为我也不会，我这里再引用一篇帖子关于AVL和BR-Tree的区别，那么就很清晰了：红黑树与AVL树，各自的优缺点总结

1. 红黑树不追求"完全平衡"，即不像AVL那样要求节点的 |balFact| <= 1，它只要求部分达到平衡，但是提出了为节点增加颜色，红黑是用非严格的平衡来换取增删节点时候旋转次数的降低，任何不平衡都会在三次旋转之内解决，而AVL是严格平衡树，因此在增加或者删除节点的时候，根据不同情况，旋转的次数比红黑树要多。

2. AVL的结构相较于RB-Tree更为平衡，插入和删除引起失衡，如2所述，RB-Tree复衡效率更高；当然，由于AVL高度平衡，因此AVL的Search效率更高啦。

3. 针对插入和删除节点导致失衡后的rebalance操作，红黑树能够提供一个比较"便宜"的解决方案，降低开销，是对search，insert ，以及delete效率的折衷，总体来说，RB-Tree的统计性能高于AVL.

故引入RB-Tree是功能、性能、空间开销的折中结果。

• AVL更平衡，结构上更加直观，时间效能针对读取而言更高；维护稍慢，空间开销较大。
• 红黑树，读取略逊于AVL，维护强于AVL，空间开销与AVL类似，内容极多时略优于AVL，维护优于AVL。

Btree

啥叫B树？有别于二叉树的头结点只能存储单节点的问题，Btree / B+Tree都做了横向扩展，叶子节点具有相同的深度，叶节点的指针为空；所有索引元素不重复；一个节点可以存储多个元素，节点中的数据索引从左到右递增排列，B树的查询过程和二叉排序树比较类似，从根节点依次比较每个结点，因为每个节点中的关键字和左右子树都是有序的，所以只要比较节点中的关键字，或者沿着指针就能很快地找到指定的关键字，如果查找失败，则会返回叶子节点，即空指针。

下面这个 B 树，每个节点限制最多存储两个 key，一个节点如果超过两个 key 就会自动分裂。比如下面这个存储了 8 个数据 B 树，只需要查询两个节点就可以知道 id=8 这数据的具体位置，也就是两次磁盘 IO 就可以查询到指定数据，优于 AVL 树。

B树的特点可以总结为如下：

• 关键字集合分布在整颗树中。
• 任何一个关键字出现且只出现在一个节点中。
• 搜索有可能在非叶子节点结束。
• 其搜索性能等价于在关键字集合内做一次二分查找。
• B树在插入删除新的数据记录会破坏B-Tree的性质，因为在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质。

但B树对磁盘I / O的查询效率依然是比较慢的，我们可以由上面的图

B+Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

• 非叶子节点只存储键值信息。
• 所有叶子节点之间都有一个链指针。
• 数据记录都存放在叶子节点中。

所以，这也是为什么索引最终选择B+树的原因:

1. hash很快，但每次IO只能取一个数
2. AVL和红黑树，在大量数据的情况下，IO操作还是太多
3. B树每个节点内存储的是数据，因此每个节点存储的分支太少
   4.B+节点存储的是索引+指针(引用指向下一个节点)，可以存储大量索引，同时最终数据存储在叶子节点，并且有引用横向链接，可以在2-3次的IO操作内完成千万级别的表操作。
4. 建议索引是是自增长数字，这样适合范围查找

这也是从没有索引、hash、二叉排序树、AVL树、B树、B+树而最终选择B+树的原因。

MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。另外这里补充一点的是，不论是索引还是锁，都是基于表结构的而不是库结构，这里需要理清一下这个概念。

mysiam索引实现

MyISAM 虽然数据查找性能极佳，但是不支持事务处理。Innodb 最大的特色就是支持了 ACID 兼容的事务功能，而且他支持行级锁。如果我们创建完以mysiam引擎的表后，在数据库的文件夹下会出现三个文件，比如这里的user2表：

(参考自参考文献3)

MyISAM 存储引擎的一个表有3个文件： *.frm 文件存储的表的结构； *.MYD 文件存储表的数据； *.MYI 文件存储表中的索引数据；

所以MyISAM存储引擎的索引文件和数据文件是分离的，这有个名词，为非聚集索引。 MYISAM 存储引擎的索引的叶子节点的data中存储的是索引所在行的磁盘指针，主键索引和非主键索引的存储是差不多的，InnoDB 存储引擎的 主键索引和非主键索引存储是不一样的。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

InnoDB 存储引擎的1个表有2个文件： *.frm 文件存储表的结构； *.ibd 文件存储的是索引和数据；

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键：

1. 如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

2. 因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:
   

这也是第二个问题关于InnoDB底层的索引结构的概述。

参考与推荐：

[1]. MySQL索引背后的数据结构及算法原理

[2]. MySQL索引底层实现原理

[3]. 深入理解 Mysql 索引底层原理