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

技术点

一、索引数据结构红黑树，Hash，B+树
二、千万级数据表如何用 B+树索引快速查找
三、聚集索引&聚簇索引&稀疏索引
四、为什么 DBA 总推荐使用子自增主键做索引
五、联合索引底层数据结构
六、MySQL 最左前缀优化原则

技术分析

一、索引数据结构红黑树，Hash，B+树

1.索引数据结构
（1）二叉树：如果用二叉树去存储索引，那么 key 记录的是索引字段的值，value 记录的是索引所在数据行的磁盘文件的地址。假如我们不建立索引，直接去查询，如果要查询 Col2 字段值为 89 的数据，那么正常需要进行磁盘交互（I/O）次数 6 次才能查询到；如果使用二叉树作为索引结构（二叉树的特点是右边的元素大于其父元素，左边的元素小于其父元素），那么仅仅需要 2 次查询就可以查询到

优点：相对于没有索引的字段查询效率会高一些
缺点：如果是在递增的字段比如 Col1 上去建立二叉树结构的索引，也会带来一些问题：虽然递增的字段建立的索引是二叉树结构，但更像是一条链表，我们去查询 Col1 为 6 的值仍然需要去查询 6 次。所以在递增字段上使用二叉树结构作为索引实质上没有作用

（2 ）红黑树（Red Black Tree）：也叫二叉平衡树，在一侧数据比较大的时候，红黑树会自己做一次平衡调整（调整原理可参考https://blog.csdn.net/huangwen001/article/details/119799167）

优点：解决了普通二叉树存储自增字段索引引发的链表问题
缺点：当数据量很多的时候，几百万甚至上千万的时候，红黑树结构的索引高度会非常高，比如高度可能达到 20，那么如果需要的查询的数据在叶子节点，那么至少需要查询 20 次。
那么就引发了我们的思考：只需要将树的高度控制比较低，那么查询次数就会减少，随之效率就会提升了
（3）B 树：相对于红黑树，B 树横向拉长了索引存储的空间，从而缩短的高度

（4）B+树（B 树的变种）：mysql 采用的索引数据结构，我们可以看到相对于 B 树，B+树将非叶子节点的 data（行数据所在磁盘的地址）全部移到了叶子节点上，也就是说 B+树的叶子节点存储了这个表的所有字段；
非叶子节点存储冗余索引（冗余索引取自子集的磁盘页的第一个元素，第二层的 15 取自第三层的 15 所在磁盘页的第一个元素，也就是 15,第二层的 20 取自第三层 20 所以在磁盘页的第一个元素20，那第二层 15 和 20 之间存储什么呢？存储的是第三层 15-18 磁盘页的地址；依次往上类推。。。。）
节点中的数据索引 都是递增排序的（磁盘页之间以及磁盘页内）；
叶子节点中的指针：指针存储的是相邻磁盘页在磁盘中的位置，那么查找磁盘页位置就很容易了，提高了区间访问的性能（范围查询）
在实际的查询中，耗费性能主要是 I/O 操作（将磁盘页放入 内存（RAM）中），在内存中去比对数据的时间可以忽略不计，那么问题来了，我们为什么不将索引数据放在一个磁盘页中？
如果数据量几千万，索引几千万个同时加载到内存中，会直接干爆内存…
mysql 建议的一个磁盘页的大小为：16383 字节，大约 16kb，为什么用 16kb， 假如我们某个字段存储的 bigint 类型（8 字节），在 C 语言底层，存储地址分配的空间是 6 字节，那么 16k 可以放 16k/（6+8） = 1170，也就是一个磁盘页能放 1170 个索引；叶子节点会特殊一点，data 如果存储的是一样数据的值，顶多 1kb 左右，所以一个叶子节点磁盘页可以放 16 个索引；那么算出来就是 1170117016 = 2000 多万的索引，足以存储；2000 多万的数据存储高度为 3，查询只需要 3 次（一般 mysql 会把非叶子节点会常驻内存，那么查询次数还会减少）

总结：mysql 为什么选择 B+树
影响查询效率的是树的高度，我们刚才已经看见了，B+树存储 2000 多万条数据只需要高度为 3 的树即可；如果个换成 B 树，B 树的非叶子节点也存储的 data 数据，也就是说一个磁盘页只能放 16 个 索引，那么需要
n个 16的次方 =2000w，n 就是树的高度，肯定远远超过 3，所以为啥把 data 数据移动到叶子节点；
树的高度取决于非叶子节点所储存的索引的数量，存储的越多，树越矮
杠精们：如果数据量超过 2000 多万怎么办，那就分库分表或者就再加一层
（5）Hash
我们字段建立索引可以选择 B+树也可以选择 Hash 索引结构。
存储索引的时候会对这一列的值进行一次 Hash 算法，得到的结果定位到一个 Hash 桶里，比如 Alice 在进行 Hash 运算之后得到的散列值是 2，就会在 Hash 桶2 的位置存储这个列的值（可能会存在多个相同的散列值---- Hash 碰撞）以及 索引所在行的磁盘文件地址。
在根据字段进行查询的时候，会先对值进行依次 Hash 运算得到的散列值去 Hash 桶里找，找到匹配的散列值之后如果有多个那就遍历这个链表直到匹配上

问题来了，我们很可能只需要 IO 一次就能定位到我们所需要的元素，而 B+树有时候需要 2-3 次，效率在某种情况下可能比 B+树高，但为什么不会采用 Hash 呢？
有两点原因：
一、Hash 索引不支持范围查询，假如要查询 大于 Alice 的值，Hash 索引无法查询 最终导致全表扫描
二、存在 Hash 冲突
而B+树的叶子节点之间存在指针关系且是排好顺序依次递增的，在进行范围查询的时候通过指针很容易定位下一个磁盘页的磁盘位置

插个常识：我们所熟知的 MyISAM 和 InnDB 数据库存储引擎是形容数据库表的，存储引擎是在表级别生效的
MyISAM： 如果表的存储引擎是 MyISAM， 那么这张表会产生三个文件，比如 A 库下一张表名为 test，那么会在 mysql 的安装目录下 data/A 下有三个文件（test.frm,test.MYD,test.MYI）；
frm： 存储的是数据表结构相关的信息
MYD：存储的数据
MYI：存储的索引
在查询的数据的时候，首先从 MYI 文件中定位索引元素，在匹配到索引元素之后获取到 data 数据行所在的磁盘地址，拿着磁盘地址去 MYD 文件中定位这一行数据。

InnDB：只有两个文件 frm文件 和 ibd文件，ibd 文件本身也是按 B+树组织的 一个索引结构文件，与 MyISAM 不同的是，叶子节点的 data 存储的不再是数据的地址而是其他数据的值，也就是索引存储的区别

总结：MyISAM 的索引文件和数据文件是分离的（非聚集（簇）索引），InnDB 的叶子节点包含了完整的数据记录（聚集索引），聚集索引的查询效率就会略高于非聚集索引。
引发思考：为什么建议 InnDB 表建主键，并且推荐使用整形自增主键？
因为有了主键，那么 mysql 会根据这个主键去维护整张表的索引数据形成上图中的 B+树，如果不建主键，那么 mysql 会在所有这张表的所有字段中查找一列数据不重复的字段作为索引去维护索引结构；那么如果没有符合情况的字段，mysql 会自增一个隐藏列，类似于 rownum 去维护这个索引结构。 这样可以减少 MySQL 的工作；
至于用整形自增，是因为我们的 在查询数据的时候，B+树索引结构中会经历很多次比较大小，那么整形比较大小会更快一些（相对于 UUID 字符串，字符串比较大小还需要按照每一位的 ASCII 码去比较）；整型比字符占用的空间也会小一些（节约固态硬盘空间）
为什么要自增？
mysql 在维护索引的时候 会自动帮我们排序，如果不是自增的，mysql 在维护索引树的时候会导致磁盘页的分裂和整体 B+树的自己平衡；如果是自增的，在磁盘页满了之后会继续开辟一个新的空间作为下一个磁盘页

以上说的都是主键索引，那么非主键索引（辅助索引/二级索引）是如何存储的呢？
可以看到叶子节点存储的是辅助索引的值和主键索引的值，不存列数据是为了节省存储空间和保证一致性（假如插入数据的时候，那么两颗树都得去维护整张表的数据，但凡没有一个没插成功就会出错，减少复杂度）
二级索引也是非聚集索引（稀疏索引），在利用它进行查询的时候，定位到叶子节点的主键之后还需要再去主键索引的树去查询一下（回表）

联合索引（复合索引）：即多个字段共同存储成一个索引，按照最左边第一个字段依次进行比较大小去排列成树

索引最左前缀原理：用联合索引的条件就是查询的时候第一个字段必须要查询，否则用不了，以下三条查询语句只有第一条能使用到联合索引
为什么呢？因为联合索引的 B+树排序的时候就是按照字段从左到右的顺序去排列的