学习笔记：MySQL索引（一）

原创已于 2022-03-13 21:37:52 修改 · 1.5k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #b树

于 2022-03-06 23:38:16 首次发布

mysql 专栏收录该内容

3 篇文章

订阅专栏

索引的定义

索引（Index）是帮助MySQL高效获取数据的数据结构。
注：索引的本质是数据结构。

在开始了解索引之前，我们先简单的了解一下磁盘IO和树的概念。

磁盘IO

磁盘相关的几个概念（以机械硬盘为例）：

扇区：磁盘存储的最小单位，扇区一般大小为512Byte
磁盘块：文件系统与磁盘交互的最小单位（计算机系统读写磁盘的最小单位），磁盘块一般大小为4KB
寻道时间：磁臂移动到指定磁道所需要的时间。主流磁盘一般在5ms以下
旋转延迟：磁盘旋转一周所需时间的一半。例：转速为7200转/分,则它的平均旋转延迟为60/7200*0.5≈0.00417s=4.17ms
传输时间：磁盘读写需要的时间。一般零点几毫秒，相较于寻道时间和旋转延迟可以忽略不计
磁盘IO时间：由寻道时间、旋转延迟和传输时间三部分组成

以上述硬盘为例，一次磁盘IO大概需要9毫秒，看似挺快的，如果我们不对数据存储方式做任何优化，假设一张表只存储一个int类型的字段，int占用4Byte，则每个磁盘块可以存储1000条数据，假设数据量为100万，则需要1000个磁盘块来存储。此时我们每次检索都需要读取这1000个磁盘块的数据，一次磁盘IO时间为9毫秒，1000次就是9秒，意味着在百万级数据量的情况下，我们任何一个查询都至少需要9秒。显然这种情况我们是无法接受的。
数据页：页是MySQL中磁盘与内存交互的基本单位，默认是16KB，相当于4个磁盘块。意味着MySQL每读取一次数据就是16KB，这个值由innodb_page_size控制，可以更改。
预读：当发生一次磁盘IO时，不光是读取当前磁盘地址的数据，还会把相邻的数据也读取到内存缓冲区中。

二叉树

二叉树的特点：

1、每个结点包含1个元素及n个子树(0 ≤ n ≤ 2)；
2、次序不能颠倒，左子树的元素值 < 父结点的元素值 < 右子树的元素值。

例一：

[4,2,1,3,6,5,7]使用二叉树存储如下：

二叉树

上述树结构检索任何一个数据，最好情况是1次，最坏情况是3次，最坏情况的时间复杂度为O(logN)。

例二：

[1,2,3,4,5,6,7]使用二叉树存储如下：

二叉树退化为链表
可以看到，当我们插入有序的数据时，二叉树便退化成了链表，最坏情况的时间复杂度为O(N)。

二叉树的优缺点：

1、查询效率不稳定，当二叉树结构趋于平衡时，最坏情况的时间复杂度为O(logN)；当插入有序数据时，二叉树退化为链表，最坏情况的时间复杂度变成了O(N)。

2、数据量大的情况下，会导致树的高度变高，如果每个结点对应一个磁盘块，那么磁盘IO次数会大量增加，因此用此结构来存储数据是不可取的。

平衡二叉树（AVL树）

平衡二叉树是一种特殊的二叉树，所以拥有上述二叉树的两个特点，除此之外还有一个特性：

平衡二叉树的左右子树高度差的绝对值不超过1。

优缺点：

1、相较于二叉树，平衡二叉树的查询效率比较稳定，不会出现退化成链表的情况；
2、数据量大的情况下，一样会导致磁盘IO次数大量增加，因此也是不可取的。

B树

B树是在平衡二叉树上演变而来，平衡二叉树每个结点只能存放一个元素，而B树每个结点可以存放多个元素，主要是为了降低树的高度，减少磁盘IO的次数。
B树是一种多路平衡查找树，它的每一个结点最多包含m个孩子，m被称为B树的阶。（m的大小取决于磁盘页的大小）

m阶B树的特点

1、若根节点不是叶子结点，则至少有两个孩子；

2、每个结点最多有m个孩子，m称为B树的阶；

3、每个中间结点都包含k-1个元素和k个孩子，其中(Ceil)m/2 ≤ k ≤ m；

4、每个叶子结点都包含k-1个元素，其中(Ceil)m/2 ≤ k ≤ m；

5、所有的叶子结点都在同一层；

6、每个结点中的元素从小到大排列，结点中的k-1个元素正好是k个孩子包含的元素的值域划分。

如下图（图源自网络）所示为一个3阶B树

3阶B树
如图所示，每个结点占用一个磁盘块，结点中的2个元素划分出三个值域，分别对应3个子树，假设我们要查询数值13，其过程为：

第一次磁盘IO：将磁盘块1读入内存，在内存中定位（与17，35比较）；

第二次磁盘IO：将磁盘块2读入内存，在内存中定位（与8，12比较）；

第三次磁盘IO：将磁盘块7读入内存，在内存中定位（与13，15比较）；

从上述流程不难看出，磁盘IO的次数是由索引树的高度决定的，最坏的情况下，磁盘IO次数等于索引树的高度。B树在查询中的比较次数其实不比二叉查找树少，尤其在单一节点元素数量很多的时候。不过相比于磁盘IO的速度，内存中的比较耗时几乎可以忽略不计，所以只要IO次数足够少（即树的高度足够低），就可以提升查找的性能。

优势：

相较于AVL树，B树通过增加结点的关键字个数来降低树的高度，减少IO的次数。相比之下，结点的内部元素多一点也没有关系，仅仅是多了几次内存的交互，只要不超过磁盘页的大小即可。

缺点：

B树不利于范围查询，比如上图中我们要查询区间[15,36]的数据，需要访问7个磁盘块，所以B树也不太适合在磁盘中存储需要检索的数据。

B+树

B+树结构如图（图源自网络）

B+树结构

B+树是基于B树的一种变体，有着比B树更高的查询性能。

m阶B+树的特点

1、有k个子树的中间结点包含k个元素，每个元素不保存数据，只用来索引，所有数据都保存在叶子结点；

2、所有的叶子结点中包含了全量元素的信息，及指向这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接，形成了一个有序链表；

3、所有的中间结点元素同时存在于子结点，是子结点元素中最大（或最小）的元素。

注：以后无论插入删除多少元素，始终要保持最大（或最小）元素在根结点中。

卫星数据（Satellite Information）

定义：索引元素指向的数据记录，比如数据库中的某一行

B树中的卫星数据：中间结点和叶子结点都带有卫星数据

B+树中的卫星数据：只有叶子结点带有卫星数据，中间结点仅仅是索引，没有数据关联

注：在数据库中的聚集索引（Clustered Index）中，叶子结点直接包含卫星数据。在非聚集索引（NonClustered Index）中，叶子结点带有指向卫星数据的指针。

优点

B+树的优点主要体现在查询性能上：

1、单元素查询时，B+树会自顶而下逐层查找结点，最终找到匹配的叶子结点。

与B树查询的不同点：
- 首先，B+树中间结点没有卫星数据，所以同样大小的磁盘页能够容纳更多的结点元素。这就意味着在数据量相同的情况下，B+树的结构会比B树更加“矮胖”，查询的IO次数也会更少。
- 其次，B+树的查询必须最终找到叶子结点；而B树只要找到匹配元素即可，无论是中间结点还是叶子结点。因此，B树的查找性能并不稳定（最好的情况是只查根节点，最坏的情况是查到叶子结点）。而B+树的每一次查询都是稳定的。

2、范围查询时，比如我们要查询3 - 8的元素，

（1）B树只能依靠繁琐的中序遍历；