深究MySQL索引数据结构及算法原理

一、索引数据结构类型

索引的本质：MySQL官方对索引的定义是，索引是帮助MySQL高效获取数据的数据结构。那么提取这句话的主干，就可以得到索引的本质：索引是数据结构。
下面介绍几种数据结构（重点多去理解B-Tree和B+Tree）：

1、二叉树
规则：二叉树结构，存放数据时，比根节点小的放在左侧，比根节点大的放在右侧。
缺点：二叉树高度没有限制，当字段类型为int/bigint且递增时，树的高度也会递增。此时查询某个数据则会由根节点开始遍历，遍历次数和树的高度有关，当数据量过大，查询效率因此降低。

如上图所示，存入1到10，树的高度也为10，当要查找第10个数据，就需要从跟节点1开始遍历7次才能取到。如果存入1到10000，则要遍历10000次才能取到10000，效率过低。

2、红黑树（二叉平衡树）
红黑树是对二叉平衡树做了一些优化，又叫二叉平衡树。对根节点进行了一些平衡处理，但数据量大时，树的高度也会很大，结构如下：

3、hash结构
hash数据结构的应用有很多场景，例如Java中map集合底层数组+链表（jdk>1.8版本+红黑树），存入数据的key、value时，要对key进行hash计算，算出key的索引下标（在数组中的位置）。因为hash值为随机的（还有可能出现hash冲突，那么要以链表形式存储），所以依次存入的数据位置是不连贯的，因此MySQL中的hash索引类型虽然可以快速定位数据位置并取出数据，很多时候hash索引要比B+Tree索引更高效，但它只能满足“=”、“IN”，不满足范围查找。

4、B-Tree

上图为简化的B-Tree结构示意图，那么B-Tree的特点：
叶子节点具有相同的深度，叶子节点的指针为空；
所有索引元素不重复；
节点中的数据索引从左到右递增排列；
所有键值分布在整个树中；
查询可能在非叶子节点结束。

来模拟下查找文件29的过程：
(1) 根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。【磁盘IO操作1次】
(2) 此时内存中有两个文件名17，35和三个存储其他磁盘页面地址的数据。根据算法我们发现17<29<35，因此我们找到指针p2。
(3) 根据p2指针，我们定位到磁盘块3，并将其中的信息导入内存。【磁盘IO操作2次】 (4) 此时内存中有两个文件名26，30和三个存储其他磁盘页面地址的数据。根据算法我们发现26<29<30，因此我们找到指针p2。
(5) 根据p2指针，我们定位到磁盘块8，并将其中的信息导入内存。【磁盘IO操作3次】
(6) 此时内存中有两个文件名28，29。根据算法我们查找到文件29，并定位了该文件内存的磁盘地址。
5、B+Tree

上图为简化的B+Tree结构图，是B-Tree的变种，与B-Tree的不同在于：
（1）所有关键字data存储在叶子节点，非叶子节点不存储真正的data
（2）所有的叶子节点增加了一个链指针
（3）关键在于B+Tree最后的高度不超过3；

二、B-Tree和B+Tree结构和性能分析

1、为什么用B-Tree/B+Tree这种结构来实现索引呢？
红黑树结构也可以用来实现索引，但是系统及数据库系统普遍使用B-Tree/B+Tree结构来实现索引。MySQL是基于磁盘的数据库，索引是以索引文件的形式存储在磁盘中，索引的查找过程涉及到磁盘IO，磁盘IO的消耗相比较内存IO的消耗要高几个数量级，所以索引的组织结构设计，要在查找关键字时尽量减少磁盘IO的次数，那么选用B-Tree/B+Tree作为索引就和磁盘的存储原理有关。

局部性原理与磁盘预读：为了提升效率，要尽量减少磁盘IO的次数，即实际操作过程中，磁盘并不是每次严格按需读取，而是每次都会预读。磁盘读取完需要的数据后，会按顺序再多读一部分数据到内存中，依据是计算机科学中注明的局部性原理：

• 当一个数据被用到时，其附近的数据也通常会马上被使用
• 程序运行期间所需要的数据通常比较集中

（1）由于磁盘顺序读取的效率很高(不需要寻道时间，只需很少的旋转时间)，
因此对于具有局部性的程序来说，预读可以提高I/O效率.预读的长度一般为页(page)的整倍数。
（2）MySQL(默认使用InnoDB引擎),将记录按照页的方式进行管理,每页大小默认为16K(这个值可以修改)。linux 默认页大小为4K

B-Tree借助计算机磁盘预读的机制，并使用如下技巧：
每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个结点只需一次I/O。
假设 B-Tree 的高度为 h,B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存）。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3，索引B+Tree层次一般不超过三层，所以查找效率很高）。
而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

2、为什么MySQL的索引使用B+Tree而不是BTree呢？
（1）B-Tree每个节点都存储了data数据，而MySQL把记录按照页的方式进行管理，每页分配16K的大小，以bigint类型索引为例，每个索引字段占8个字节，然后加上后面一个指向数据页的空间，大约是6字节，再加上data数据约为1kb，那么B-tree每页数据可以存储16kb/(8bit+6bit+1kb)，约等于16个。假如有2千万条数据，计算下来2千万=16的n次方，n就是B-Tree的高度，查找一次最大为n-1（根节点常驻内存）次磁盘IO。而B+Tree只有根节点存储了data数据，那么B+Tree结构的非叶子节点中存储量提升到16kb/(8bit+6bit)，约等于1170个，因此树的高度大大降低，从而磁盘IO次数降低，查询效率大大提升。
（2）B+树叶子节点磁盘页之间相互指向，当查询范围时，B+Tree可以通过节点之间的指向直接查找相邻的磁盘页，一次性把符合范围的数据查询出来，效率高；而B-Tree叶子节点磁盘页没有指针，当查询范围时，效率不如B+Tree。

三、MySQL中存储引擎索引的实现

1、MyISAM存储引擎索引的实现（非聚集索引）
MyISAM索引文件和数据文件是分离的（MySQL安装目录data目录下可查看表信息）。
MyISAM存储引擎会把索引生成B+树结构存储到以.MYI结尾的文件中去，就是下图中的数据结构，查找到对应data后，拿到磁盘地址再去以.MYD结尾的数据文件中定位到索引所在行数据的。

2、InnoDB存储引擎索引的实现（聚集索引）
索引文件和数据文件不分离，索引数据结构和数据存储在以.ibd结尾的文件中，其中索结构叶子节点不存储data，直接存储索引所在行的其他字段数据。这种方式的叫做聚集索引（或聚簇索引）——叶子节点包含了完整的数据记录。

上图是InnoDB存储引擎下的主键索引结构，叶子节点直接存储了索引所在行的其他字段数据，而不是data地址值（和MyISAM存储引擎的有所区别）。

然而InnoDB存储引擎下，辅助索引的叶子节点和主键索引又有所区别，辅助索引叶子节点存储了主键的值。查询时：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

以上为工作学习所记录心得，如果片面的地方或不合理的，欢迎批评指正~~~
最后再告诉大家一个秘密：键盘不敲烂，薪资不过万！