（一）深入理解Mysql索引底层数据结构和算法

目录

一、索引的定义

二、索引数据结构选型

2.1、二叉树

2.2、红黑树

2.3、B-Tree

2.4、B+Tree

2.5、Hash

三、拓展说明

3.1、myisam引擎

3.2、innodb引擎

---

一、索引的定义

索引可以直白理解成书的目录，是帮助MySQL高效获取数据的排好序的数据结构。

以 select * from table where col2=5;为例

没有建立索引：是逐行从磁盘读取（I/O），再去比较是否是要查找的行，这样就需要每行都I/O交互一次，效率就很低，那么col=5就需要I/O交互6次才能取到数据；

col2建立索引：索引数据结构以二叉树为例，从根节点34开始向下查找，只需要经过两次I/O即可取到数据，效率就提高了；

col1建立索引：索引数据结构以二叉树为例，从根节点1开始向下查找，要经过6次I/O才能取到数据（单列递增的索引已经退化成链表），效率和链表一样，需要遍历全部。

二、索引数据结构选型

具备充当索引的数据结构有如下几种：二叉树、红黑树、Hash表、B树（B-Tree、B+Tree），而数据库最终使用的是B+Tree

数据结构可视化的网址：Data Structure Visualization

2.1、二叉树

图1

图2

二叉树特点是每个节点最多只能有两个子节点，大于父元素挂于右侧，小于父元素挂于左侧。故对于单边增长的情况，二叉树呈现出来数据结构和链表一样（如图2），那么查找其中的元素7时，就需要遍历全部，显然不适合作为数据库的索引。

2.2、红黑树

图3

图4

图5

图6

红黑树其实就是一种平衡的二叉树。相比二叉树，添加节点的时候，节点之间会动态平衡不会退化成链表（如图3增加节点5，动态平衡后为图4；同样图5增加节点7后，动态平衡后变成图6），可以有效降低树的高度。但红黑树的本质还是二叉树，避免不了表数据大的情况下，树的高度依然会很高。

2.3、B-Tree

图7

图8

图9

图10

B树是一种自平衡的树状数据结构（图7自动平衡为图8，图9自动平衡为图10），它的子节点可以有多个，使得整棵树更加的扁平。所有的节点从左到右按递增排好序。

B树每个节点上都存储了一个键值对，键是索引元素得索引值，值是索引元素的磁盘地址（可以理解成该索引对应记录的磁盘地址）

图11

但MySQL没有选择B-Tree作为索引数据结构，只要是有以下两个问题：

1）树的每一层中的节点都包含了索引值和数据data，导致每层能存储的节点数就会降低，从而间接导致B树的高度增加；

2）叶子节点之间没有指针进行连接，这样一来，在范围查询时候，如果查询范围较大，跨越多个节点的话，需要重新从根节点向下查找，从而导致I/O操作次数增加，降低查询效率。

2.4、B+Tree

图12

B+Tree是B-Tree一个变种，只在叶子节点存放索引和对应数据值，这样大大增加了每层能存放的索引数量，相对B-Tree来说更充分利用的空间，查询效率更高效。叶子节点从左到右，索引都是排好序递增，且左边结尾的数据都会保存右边节点开始数据的指针，这样也就解决了范围查询当存在跨节点时时，不需要重新从根节点向下查询。

2.5、Hash

图13

col2作为索引，hash数据结构

对于索引key进行hash计算就可以定位出数据存储的位置，如aaa存储在0x11；

很多时候Hash索引要比B+树更加高效；

Hash冲突问题：如aaa,bbb；

Hash支持等值"="，"IN"查询较好，但不支持范围查询，这个也是为什么MySQL索引数据结构选择的是B+Tree的原因。

问题：为什么MySQL索引数据结构选择的是B+Tree而不是B-Tree?

1、B+树的中间节点只用来索引，也就是每层可以存放更多的索引，也就是B+树更多扁平，所以I/O次数就会少一些；

示例：以3阶B+树为例，默认情况下，MySQL的页大小默认是16kb，假设索引是bigint类型（8字节），加上内存地址大小6字节，也就是每层可以存放索引个数：16kb/(8+6)B = 16x1024/14 = 1170 个，而叶子节点会包含data，如果一条数据按1kb计算，叶子节点一页可以存放16kb/1kb = 16个元素，也就是叶子节点能存放的总数为：1170 x 1170 x 16 = 21902400，差不多是2千万数据，换句话说，3阶B+Tree，能存储的索引数量大概是2千万。

同样的数据2千万用B-Tree存放，因为每层数据和索引都是在一起，故每页可以存放的索引数 = 16kb/1kb，故16的n次方=2千万，其中n就是树的高度，也就是如果查找的数据在叶子节点，需要I/O交互n次才能取到数据，而这个n>>3。

2、B-Tree中间节点也存储data数据，所以它查询效率不是很稳定，最快的是根节点就直接查到数据，最慢的情况是叶子节点才查到数据。当范围查找时，刚好符合条件的数据落在叶子节点，且跨越节点时，加上叶子节点间没有关系，就会导致跨节点时，需要重新从根节点开始向下查找，也就增加了I/O次数。

三、拓展说明

3.1、myisam引擎

Myisam引擎索引实现是非聚集索引：索引和数据是分开存放（.frm 表结构、.fri索引、.frd数据）

先查.fri找到索引的内存地址，再去.frd查数据

3.2、innodb引擎

Innodb引擎索引实现是聚集索引：索引和数据都在同一个文件（.frm表结构、.ibd数据索引同一个文件）

主键聚集索引：叶节点包含了完整的数据记录，也就是聚集索引的时候直接在ibd文件一次找到数据；

非主键聚集索引：叶子节点存储的是主键值，也就是非主键聚集索引查找到的是主键聚集索引的值，再从主键聚集索引取到数据。--主要是保证数据一致性和节省存储空间