【MYSQL索引与数据结构】

---

前言

innoDB存储引擎支持以下索引：

• B+树索引
• 全文索引
• 哈希索引

B+树索引是目前关系型数据库系统中查找最为常用和最有效的索引。B+树中的B不是代表二叉（binary），而是代表平衡（balance），因为B+树是从平衡二叉树演化而来，但B+树不是一个二叉树。

---

一、数据结构与算法

了解B+树前，先复习下相关算法和数据结构。

二分查找法

二分查找又叫折半查找，是一种简单又快速的查找算法；它对要查找的序列有两个要求，一是该序列必须是有序的（即该序列中的所有元素都是按照大小关系排好序的，升序和降序都可以），二是该序列必须是顺序存储的。

树形结构

树形结构指的是数据元素之间存在一对多关的树形关系的数据结构，是非线性数据结构。
术语

• 节点（Node）表示树中的数据元素，由数据项和数据元素之间的关系组成。
• 节点的度（Degree of Node）节点所拥有的子树的个数
• 叶子节点（Leaf Node） 度为0的结点，也叫终端节点。
• 孩子（Child）结点子树的根
• 双亲（Parent）结点的上层结点叫该结点的双亲
• 祖先（Ancestor）从根到该结点所经分支上的所有结点。

二叉树

二叉树是指树中节点的不大于2的有序树。二叉树递归定义：二叉树是一个空树，或者是一棵由一个根节点和两棵互不相交的，分别称作根的左子树和右子树组成的非空树；左子树和右子树又同样都是二叉树。
形态
1、空二叉树
2、只有一个根节点
3、只有左子树
4、只有右子树
5、完全二叉树

二叉查找树

又叫二叉排序树，二叉搜索树，一般情况下，查找效率比链表结构要高。
具有以下性质的二叉树成为二叉查找树：

• 若左子树不空，则左子树上所有节点的值均小于它的根节点的值
• 若右子树不空，则右子树上所有节点的值均大于它的根节点的值
• 左右子树也分别为二叉排序树

查找

若根节点的关键字值等于查找的关键字，成功
否则，查找的关键字小于根节点的值，递归查左子树
若大于根节点的关键字，递归查右子树
若子树为空，查找不成功

插入

在查找过程中，当树中不存在关键字等于给定值的结点时再进行插入。新插入的结点一定是一个新添加的叶子结点，并且是查找不成功时查找路径上访问的最后一个结点的左孩子或右孩子。

平衡二叉树（AVL）

也叫高度平衡树，是基于二分法的策略提高数据的查找速度的二叉树数据结构。
特点
非叶子结点最多拥有两个子节点
非叶子值（双亲）大于左边子节点，小于右边子节点
任意节点对应的两个子树的最大高度差为1
没有值相等重复的结点

由来：
当二叉查找树，插入序列有序时，二叉树会退化成单链表，搜索效率降低为O（n）

红黑树（自平衡二叉查找树）

红黑树是平衡二叉树的变体，它的左右子树高度有可能大于1，所以红黑树不是严格意义上的平衡二叉树（AVL），但对之平衡的代价较低，其平均统计性能要强于AVL。由于每一棵红黑树都是一棵二叉排序树，在查找时，采用普通二叉排序树上的查找算法，在查找过程中不需要颜色信息。
特点：
红黑树是每个结点都带有颜色属性的二叉查找树，颜色或红色或黑色。具有二叉查找树的要求外，还有：
结点是红色或黑色
根节点是黑色
所有叶子都是黑色
每个红色结点的两个子结点都是黑色（从每个叶子到根的所有路径上不能有两个连续的红色结点）
从任一结点到其每个叶子的所有路径都包含相同数据的黑色结点。

这些约束决定了，从根到叶子的最长的可能路径不多于最短的可能路径的两倍长。
红黑树是一种特化的二叉查找树，所以红黑树上的只读操作与普通的二叉查找树相同。
查找效率是O（log n）,n是树中元素的个数。

一个node节点只能存储一个key和一个value

B树

多路搜索树，比二叉搜索树高度更低，一个Node节点可以存储更多信息
B树在非叶子结点存储数据，遍历时跨层检索

B+树

多路搜索树，比二叉搜索树高度更低，一个Node节点可以存储更多信息
相对于B树，又多两种特性。

• B+树非叶子结点不存储数据，在相同的数据量下，B+树更加矮壮，数据都存储在叶子结点上，非叶子结点能存储更多的索引，所以整棵树就更加矮壮。
• B+树叶子结点之间组成一个链表，方便遍历查询。

B+树高度如何计算

公式：
表记录条数N，每个BTREE节点平均有B个索引KEY
高度=logNB(logN/logB)
例如
3000W条记录，索引列占8位字节，高度多少
2^25 = 33554432
假如每个节点保存64个索引KEY
高度=(log2^25)/log64≈25/6≈4.17
通常，一个千万量级，且存储引擎是MyISAM或者InnoDB的表，其索引树的高度在3~5之间。

二、索引

介绍下Mysql InnoDB的索引

每创建一个索引，相当于生成了一棵B+树，如果索引是【聚集（聚簇）索引】，那当前B+树的叶子结点存储主键和当前行数据。如果索引是【非聚簇索引】，那叶子结点存储主键和当前索引列值（一个字段的值），比如select * from user where id>=10,只要定位到id为10的记录，然后再叶子结点之间通过遍历链表（叶子结点组成的链表），即可往后查找记录。由于B树在非叶子结点也存储数据，要遍历的时候需要跨层，基于树的层级和以及业务使用场景，Mysql选择了B+树作为索引的底层数据结构，对于哈希结构，其实InnoDB引擎是自适应哈希索引的，我们干预不了。

什么是回表

回表是当前索引无法检索出完成的内容，需要通过主键二次查询。比如使用索引查询数据时可能包含其他列，但走的索引树叶子结点只能查到当前列值以及主键id，所以需要主键id再去查一遍数据，这种操作就是回表，想要避免回表可以使用覆盖索引，覆盖索引（联合索引）就是你想要查出的列刚好在叶子结点上都存在，就避免了回表操作。

什么是最左匹配原则

如果有覆盖索引（a,b,c,d），查询条件a=1 and b =2 and c>3 and d=4 ,则会在每个结点上命中a、b、c，无法命中d，
先匹配最左边的，索引只能用于查找Key是否存在（相等）,遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查询，这就是最左匹配原则。

不使用Mysql自增主键，有哪些问题

主键需要保证唯一性，空间尽可能短，索引的特性（有序），如果生成uuid插入的性能比自增的要差，在插入时，可能需要移动磁盘块，块内的空间在当前时刻满了，新生成的uuid需要插入已满的块内，需要移动块的数据。

---

总结

• 为什么使用B+树，数据无法一次load到内存，B+树是多路搜索树，只有叶子结点才存储数据，叶子结点之间链表进行关联，树矮，容易遍历。
• 什么是回表，非聚簇索引在叶子结点只存储列值以及主键id,有条件下尽可能用覆盖索引避免回表操作，提高查询速度。
• 什么是最左匹配原则，从最左边为起点开始连续匹配，遇到范围查询中止。
• 主键非自增会有什么问题，插入效率下降，存在移动块的数据问题。

参考：
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
https://blog.csdn.net/qq_36756682/article/details/114483362
http://javainterview.gitee.io/luffy/2021/08/19/08-MySQL/01.%20MySQL%E7%B4%A2%E5%BC%95/