MySQL索引结构详细解析

一 、理解索引的特性

索引是帮助MySQL高效获取数据的排好序的数据结构
索引存储在文件里

二 、索引的各种存储结构及其优缺点

在开始讲这一小节之前，我们先来看一下在数据库没有加索引的情况下，SQL中的where字句是如何查找目标记录的。

我们先看下左边表格第二列Col2列的数据时如何查找的，如果我们希望查找where Col2 = 22的记录，我们在没加索引的情况下是按顺序从第一条记录查找，由此可知需要查找5次才能找到；

如果对Col2字段加上索引后，我们假设使用最简单的二叉树作为索引存储方式，再次查找where Col2 = 22的记录这次只需要查找2次就能找到目标记录，效率提高十分明显。

1、二叉树

优点：

二叉树是一种比顺序结构更加高效地查找目标元素的结构，它可以从第一个父节点开始跟目标元素值比较，如果相等则返回当前节点，如果目标元素值小于当前节点，则移动到左侧子节点进行比较，大于的情况则移动到右侧子节点进行比较，反复进行操作最终移动到目标元素节点位置。

缺点：

在大部分情况下，我们设计索引时都会在表中提供一个自增整形字段作为建立索引的列，在这种场景下使用二叉树的结构会导致我们的索引总是添加到右侧，在查找记录时跟没加索引的情况是一样的，如下图所示：

2、红黑树

优点：

红黑树也叫平衡二叉树，它不仅继承了二叉树的优点，而且解决了上面二叉树遇到的自增整形索引的问题，从下面的动态图中可以看出红黑树会左旋、右旋对结构进行调整，始终保证左子节点数 < 父节点数 < 右子节点数的规则。

缺点：

在数据量大的时候，深度也很大。从图中可以看出每个父节点只能存在两个子节点，如果我们有很多数据，那么树的深度依然会很大，可能就会超过十几二十层以上，对我们的磁盘寻址不利，依然会花费很多时间查找。

3、Hash

优点：

对数据进行Hash（散列）运算，主流的Hash算法有MD5、SHA256等等，然后将哈希结果作为文件指针可以从索引文件中获得数据的文件指针，再到数据文件中获取到数据，按照这样的设计，我们在查找where Col2 = 22的记录时只需要对22做哈希运算得到该索引所对应那行数据的文件指针，从而在MySQL的数据文件中定位到目标记录，查询效率非常高。

缺点：

无法解决范围查询（Range）的场景，比如 select count(id) from sus_user where id >10；因此Hash这种索引结构只能针对字段名=目标值的场景使用。不适合模糊查询（like）的场景。

4、B tree 和B+ tree

1、初始化介绍

一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3。P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

注意：

真实的数据只存在于叶子节点，即3、5、9、10、13、15、28、29、36、60、75、79、90、99。（而且是多条数据组成的数据区间：3~ 5，… … ，90~ 99）

非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

2、查找过程

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
由于mysql通常将数据存放在磁盘中，读取数据就会产生磁盘IO消耗。而B+树的非叶子节点中不保存数据，B树中非叶子节点会保存数据，通常一个节点大小会设置为磁盘页大小，这样B+树每个节点可放更多的key，B树则更少。这样就造成了，B树的高度会比B+树更高，从而会产生更多的磁盘IO消耗。
B+树叶子节点构成链表，更利用范围查找和排序。而B树进行范围查找和排序则要对树进行递归遍历