深入理解Mysql索引底层数据结构与算法

一、MySQL索引的介绍

1.1、索引：

(1)、索引是帮助MySQL高效获取数据的排好序的数据结构。索引存储在磁盘文件里，也就是说会有IO操作。

常用的索引数据结构：二叉查找树、红黑树、hash、B-Tree、B+Tree。

在以下的分析中，我会说明为什么MySQL会选择hash以及B+Tree作为索引的数据结构，而不是其他的几种数据结构。

二、磁盘的读写原理

(1)、硬盘的组成

所有的盘片都固定在一个主轴上，并且所有盘片之间是绝对平行的，在每个盘片的存储面上都有一个磁头，所有的磁头连在一个磁头控制器上，由磁头控制器负责各个磁头的运动。磁头可沿盘片的半径方向移动，每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的。而盘片以每分钟数千转到上万转的速度在高速旋转，这样的话磁头就能对盘片上的指定位置进行数据的读写操作。

(2)、硬盘的工作原理
硬盘在逻辑上被划分为磁道、柱面以及扇区。
磁头靠近主轴接触的表面，即线速度最小的地方，是一个特殊的区域，它不存放任何数据，称为启停区，启停区外就是数据区。在最外圈，离主轴最远的地方是“0”磁道，硬盘数据的存放就是从最外圈开始的。那么，磁头是如何找到“0”磁道的位置的呢？在硬盘中还有一个叫“0”磁道检测器的构件，它是用来完成硬盘的初始定位。“0”磁道是如此的重要，以致很多硬盘仅仅因为“0”磁道损坏就报废，这是非常可惜的。
早期的硬盘在每次关机之前需要运行一个被称为Parking的程序，其作用是让磁头回到启停区。现代硬盘在设计上已摒弃了这个虽不复杂却很让人不愉快的小缺陷。硬盘不工作时，磁头停留在启停区，当需要从硬盘读写数据时，磁盘开始旋转。旋转速度达到额定的高速时，磁头就会因盘片旋转产生的气流而抬起， 这时磁头才向盘片存放数据的区域移动。
盘片旋转产生的气流相当强，足以使磁头托起，并与盘面保持一个微小的距离。这个距离越小，磁头读写数据的灵敏度就越高，当然对硬盘各部件的要求也越高。早期设计的磁盘驱动器使磁头保持在盘面上方几微米处飞行。稍后一些设计使磁头在盘面上的飞行高度降到约0.1μm～0.5μm，现在的水平已经达到 0.005μm～0.01μm，这只是人类头发直径的千分之一。
气流既能使磁头脱离开盘面，又能使它保持在离盘面足够近的地方，非常紧密地跟随着磁盘表面呈起伏运动，使磁头飞行处于严格受控状态。磁头必须飞行在盘面上方，而不是接触盘面，这种位置可避免擦伤磁性涂层，而更重要的是不让磁性涂层损伤磁头。但是，磁头也不能离盘面太远，否则，就不能使盘面达到足够强的磁化，难以读出盘上的磁化翻转。

访盘请求完成过程 ：
当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。
为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点：
第一：首先必须找到柱面，即磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间。
第二：然后目标扇区旋转到磁头下，即磁盘旋转将目标扇区旋转到磁头下。这个过程耗费的时间叫做旋转时间。
即一次访盘请求（读/写）完成过程由三个动作组成：
1）寻道时间：磁头移动定位到指定磁道 。
2）旋转延迟时间：等待指定扇区从磁头下旋转经过。
3）数据传输时间：数据在磁盘与内存之间的实际传输。
因此在磁盘上读取扇区数据所需时间：Ti/o=tseek +tla + n *twm
其中: tseek 为寻道时间、tla为旋转时间、twm 为传输时间。

三、在没有索引的情况下，数据如何被访问

通过上面的分析，我们知道，在没有索引的情况下，是全表顺序扫描。这种情况下是非常耗时的。

四、索引算法的分析

常用的索引数据结构：二叉查找树、红黑树、hash、B-Tree、B+Tree。
4.1、二叉查找树

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

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

4.2、红黑树

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

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

4.3、hash

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

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

4.4、B-Tree

既然红黑树存在缺点，那么我们可以在红黑树的基础上构思一种新的储存结构。解决的思路也很简单，既然觉得树的深度太深，就只需要适当地增加每个树节点能存储的数据个数即可，但是数据个数也必须要设定一个合理的阈值，不然一个节点数据个数过多会产生多余的消耗。按照这样的思路，我们先来了解下关于B-Tree的一些知识点：

• 度(Degree)-节点的数据存储个数，每个树节点中数据个数大于 15/16*Degree（未验证） 时会自动分裂，调整结构。
• 叶节点具有相同的深度，左子树跟右子树的深度一致，叶节点的指针为空，节点中的数据key从左到右递增排列。
  
  缺点：从上面得知，在查询单条数据是非常快的。但如果范围查的话，BTree结构每次都要从根节点查询一遍，效率会有所降低，因此在实际应用中采用的是另一种BTree的变种B+Tree（B+树）。当然有人可能会这样想，那我们为什么不把数据全部都存在一个节点，这样深度不就是1了吗？当然不行了！内存与硬盘的交互是有大小限制的，一页数据4k左右，所以不能把所有数据都放在一个节点来获取，一般来说节点会尽量预存4K容量。看到这里，我们知道（4K=节点；节点=小节点*小节点的容量）一个节点是4K，而节点内有几个小节点，那么也就是说，只要我们每个小节点的data容量越小，那么可以存的小节点也就可以更多。

4.5、B+Tree
为什么要对B-Tree继续做优化？
要解答这个疑问需要先了解B-Tree每个节点结构（上面已经说明）和MySQL数据库它是如何读取索引数据的，索引和表数据在不使用的时候是存储在文件中的，也就是磁盘，当我们执行查询操作时会DBMS（数据库管理系统）首先会先从内存中查找，如果找到直接使用，如果找不到则从磁盘文件中读取；操作系统储存数据的最小单位是页（page），一页假设是4K大小（由操作系统决定），对内存和磁盘读取数据是按一页的整数倍读取的。

这里我们假设数据库一次IO操作就读取1页4K的数据，再假设图中圈起来的元素就是一个大节点，内含多个小节点的索引和数据。假设这样的一个大节点是10MB，那么我们要从磁盘中读取完整个大节点需要进行 10M / 4K = 2500次IO操作，这样就可以看出如果大节点数据总量越大，需要执行的IO操作越多，花费的时间也越长，因此为了提高性能，数据库会建议我们一个大节点只存储一页4K大小的数据，这里的数据包含了索引和表记录，另外我们还能计算出树的度Degree应该设置成多大才合理：

Degree = 内存页大小（4K） / 单个索引值字节大小；

进一步分析，索引值的大小相对于整条记录的大小是很小的，如果我们需要查找的数据刚好是在最后，那么前面遍历过的节点中存储的记录数据是不是对我们来说是没用的，它会占用比索引大得多的空间，导致我们一个大节点里能遍历的索引数量大大减少，需要向下继续遍历的几率就更大，花费更多时间查找，那么有没有办法可以优化呢？看下一个个问题。

相对于B-Tree，B+Tree做了哪些优化？
新的B+tree结构没有在所有的节点里存储记录数据，而是只在最下层的叶子节点存储，上层的所有非叶子节点只存放索引信息，这样的结构可以让单个节点存放下更多索引值，增大度Degree的值，提高命中目标记录的几率。这种结构会在上层非叶子节点存储一部分冗余数据，但是这样的缺点都是可以容忍的，因为冗余的都是索引数据，不会对内存造成大的负担。

B+Tree通过把data不放在非叶子节点来增加度（小节点），一般会一百个以上使得深度是3~5，从而减少查询次数。并且，叶子节点之间会有指针，数据又是递增的，这使得我们范围查找可以通过指针连接查找，而不再从上面节点往下一个个找。

结论：B+Tree 既减少查询次数又提供了很好的范围查询。

五、MyISAM索引实现(非聚集)

(1)、MyISAM索引文件和数据文件是分离的。数据.MYD+结构.frm+索引.MYI三个文件。那MyISAM的索引是什么样的呢？

结论：MyISAM也是使用B+Tree算法存储索引的，但是MyISAM的索引文件和数据文件是分离的。叶子节点存储的是数据在数据文件上的指针地址。并且主键索引和非主键索引的实现方式都是一样的。先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。

六、InnoDB索引实现(聚集)

结论：
1）数据文件本身就是索引文件。
2）表数据文件本身就是按B+Tree组织的一个索引结构文件。
3）聚集索引-叶节点包含了完整的数据记录。
4）InnoDB表必须有主键，并且推荐使用整型的自增主键。
正如我们上面介绍InnoDB存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据，如果我们在设计表结构时没有显式指定索引列的话，MySQL会从表中选择数据不重复的列建立索引，如果没有符合的列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，并且这个字段长度为6个字节，类型为整型。
5）那为什么推荐使用整型自增主键而不是选择UUID？
UUID是字符串，比整型消耗更多的存储空间；
在B+tree中进行查找时需要跟经过的节点值比较大小，
整型数据的比较运算比字符串更快速；
自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续；
UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行where id > 5 && id < 20的条件查询语句。
在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；
UUID主键很容易出现这样的情况，B+tree为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。
6）为什么非主键索引结构叶子节点存储的是主键值？
保证数据一致性和节省存储空间，可以这么理解：商城系统订单表会存储一个用户ID作为关联外键，而不推荐存储完整的用户信息，因为当我们用户表中的信息（真是名称、手机号、收货地址···）修改后，不需要再次维护订单表的用户数据，同时也节省了存储空间。

参考文献：
1、深入理解Mysql索引底层数据结构与算法

2、深入理解MySQL索引底层数据结构与算法

3、MySQL存储引擎MyISAM和InnoDB底层索引结构