mysql 索引

为什么mysql使用B+树作为索引

索引的出现其实就是为了提高数据查询的效率.

就像书的目录一样。一本500页的书，如果你想快速找到其中的某一个知识点，我们肯定要先根据目录找到某个章节。同样，对于数据库的表而言，索引就是目录。

对于一个数据库索引来说， 一个好的索引结构完成一次查询需要有以下优点：

• 尽可能少的磁盘 I/O 操作
• 高效地查询，可以支持范围查询

为什么呢？

由于索引是保存到磁盘上的，当通过索引查找数据时，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入到内存，也就是说查询过程中会发生多次磁盘 I/O，而磁盘 I/O 次数越多，所消耗的时间也就越大。 所以，我们希望尽可能少的磁盘 I/O。

另外mysql是支持范围查询的， 所以也需要一个高效的范围查询。

那现在我们尝试看看哪一个索引结构可以满足这些需求。

🧩数组

我们最开始接触的数据结构就是数组。 假如我们用有序数组来储存索引，看看结果如何。

为什么选择是有序呢？ 因为我们可以用二分查找在有序数组中快速地找出目标索引，时间复杂度可以下降到O（logn）。

二分查找法每次都把查询的范围减半, 时间复杂度也不算高。 似乎看起来是一个不错的选择。

但是插入新元素的时候性能太低

想想看这么一个例子， 我们有几十万条数据， 在中间某个位置插入一个元素，为了让数组保持有序，需要将这个元素之后的所有元素后移一位。

如果这个操作发生在磁盘中，这必然是灾难性的。因为磁盘的速度比内存慢几十万倍，所以我们不能用一种线性结构将磁盘排序。

这样揭示了数组的一个特点： 查询快而增删慢。

所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2020 年某个城市的所有人口信息，这类不会再修改的数据。

🌲二叉树

有一种天然适合二分查找的数据结构， 那就是二叉树。

把所有二分查找中用到的所有中间节点，把他们用指针连起来，并将最中间的节点作为根节点,
那么就获得了一个二叉查找树。

二叉查找树的特点是 一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点。

它可以解决了插入新节点的问题

二叉树不会像线性结构那样插入一个元素，所有元素都需要向后排列。

因此，二叉查找树解决了连续结构插入新元素开销很大的问题，同时又保持着天然的二分结构。

但是又带来了新的问题：在极端情况下，二叉查找树会退化成链表。

因为每次插入的值，都比节点的值要大. 查询的时间复杂度退化为O（n）

由于树是存储在磁盘中的，访问每个节点，都可能会访问一个新的数据块，所以树的高度越高，就会影响查询性能。

为了解决这种情况，平衡二叉查找树（AVL 树）🌲 诞生了。

它的特点是： 每个节点的左子树和右子树的高度差不能超过 1

插入示例如下， 可以看到它会维持自平衡.

但是平衡二叉树还是解决不了，因树变高而影响查询效率的问题。

可以想象一下一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个数据块。

在机械硬盘时代，从磁盘随机读一个数据块大概可能需要10 ms左右的寻址时间。也就是说，对于一个100万行的表，如果使用二叉树来存储，单独访问一个行可能需要20个10 ms的时间，这个查询可慢到家了。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N叉”树。

B/B+树 🎄

B树

B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。

一棵 3 阶的 B 树的查询 节点值为9 的过程:

1. 与根节点的索引(4，8）进行比较，9 大于 8，往右边走；
2. 到节点索引为（10，12）， 9 小于 10，往左边走；
3. 找到了索引值 9 的节点

树高3，最多会发生 3 次磁盘 I/O 操作。

而相同的节点数量，平衡二叉树的树高更大，访问I/O次数也更多.

在mysql的InnoDB引擎中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

假如用B树作为索引，结构如下：

• 图中的p节点为指向子节点的指针，
• 图中的每个节点称为页，页就是我们上面说的磁盘块，在mysql中数据读取的基本单位都是页

假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下：

1. 先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。
2. 将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。
3. 将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

但是这个数据结构存在什么问题呢？

B 树的每个节点都包含数据（索引+记录）。

比如我们要访问 id 为 28 的用户, 在我们查询过程中，访问路径上的数据会从磁盘加载到内存，比如我们要访问页3的索引数据来和28做对比， 这时候页3的数据会全部加载到内存(读取的单位是页), 但是这些记录数据是没用的，我们只是想读取这些节点的索引数据来做比较查询。

当用户的记录数据的大小远远超过了索引数据的大小时， 这种数据无疑是一种累赘。

B+ 树

B+ 树就是对 B 树做了一个升级，MySQL 中索引的数据结构就是采用了 B+ 树，B+ 树结构如下图：

和B树区别如下：

• 叶子节点才会存放实际数据，非叶子节点只会存放索引；
• 所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表

优点如下：

• 非叶节点不再需要存放实际数据，可以存放更多索引，子节点数可以更多， 即更加 “矮胖”， 减少 I/O次数
• 所有叶子节点间还有一个链表进行连接，方便了范围查找。比如查找 12 月 1 日和 12 月 12 日之间的订单，这个时候可以先查找到 12 月 1 日所在的叶子节点，然后利用链表向右遍历，直到找到 12 月12 日的节点

实际上 innoDB的b+树做了一些改动：

• 叶子节点之间是用「双向链表」进行连接，既能向右遍历，也能向左遍历。
• B+ 树点节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB。

综上所述， B+树 成为了 MySQL 默认的存储引擎 InnoDB 作为索引的数据结构

聚簇索引 和 二级索引

下面来了解一些概念

每一个索引在InnoDB里面对应一棵B+树。

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。并插入了几个值.

这个表的建表语句是：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

产生的两个 B+ 树如下：

索引类型分为主键索引和非主键索引。左边的树就是主键索引

• 主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引
• 非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索

那么基于主键索引和普通索引的查询有什么区别？

比如 select * from T where ID=300，即主键查询方式，则只需要搜索ID这棵B+树,这很简单

但如果语句是 select * from T where k=3，即普通索引查询方式, 执行流程是什么呢？

1. 在k索引树上找到k=3的记录，取得 ID = 300；
2. 再到ID索引树查到ID=300对应的R3；

也就是说，基于非主键索引的查询需要多扫描一棵索引树。该过程称为 回表

索引优化

覆盖索引

如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

如果你要查的是所有名字第一个字是“张”的人，你的SQL语句的条件是"where name like ‘张%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。

参考资料：
https://cloud.tencent.com/developer/article/1543335
https://www.xiaolincoding.com/mysql/index/why_index_chose_bpu...
https://funnylog.gitee.io/mysql45/