MySQL — 索引的慢查询优化与 B+ 树

Bertha 。 2023-10-05 11:17 30阅读 0赞

## 目录 ##

#### 文章目录 ####

*  目录
 *  索引目的
 *  索引原理
 *  B+ 树
 *  MySQL 中的 B+ 树
 *  B+ 树的查找过程
 *  B+ 树性质
 *  慢查询优化
 *   *  建索引的几大原则
 *  参考文章

## 索引目的 ##

索引的目的在于提高查询效率。类比红宝字典，如果要查 mysql 这个单词，我们首先定位到 m 字母，然后往下找到 y 字母，再找到剩下的 sql 字母。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。

除了词典，生活中随处可见索引的例子，如：火车站的车次表、图书的目录等。

## 索引原理 ##

它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，即：我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询，例如：>、<、between、in，模糊查询（like），并集查询（or）等等。那么，数据库应该选择怎么样的方式（同一种查找方式）来应对所有的问题呢？

稍有算法基础的同学会想到搜索树，其平均复杂度是 lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

## B+ 树 ##

B+ 树是一种树型数据结构，通常用于数据库和操作系统的文件系统中。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入，这与二叉树恰好相反。

B+ 树的创造者 Rudolf Bayer 没有解释 B 代表什么。最常见的观点是 B 代表平衡（balanced），因为所有的叶子节点在树中都在相同的级别上。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ptaWxr_size_16_color_FFFFFF_t_70_pic_center]

如上图，是一颗 B+ 树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如：磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3。P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。真实的数据存在于叶子节点，即：3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如：17、35 并不真实存在于数据表中。

## MySQL 中的 B+ 树 ##

MySQL 的默认存储引擎 InnoDB 实现了 B+ 树，MySQL 中存储引擎的主要作用是负责数据的存储和提取。

无论是表中的数据（主键索引）还是辅助索引最终都会使用 B+ 树来存储数据，其中前者在表中会以 <id, row> 的方式存储，而后者会以 <index, id> 的方式进行存储。

*  在主键索引中，id 是主键，我们能够通过 id 找到该行的全部列；
 *  在辅助索引中，索引中的几个列构成了键，我们能够通过索引中的列找到 id，如果有需要的话，可以再通过 id 找到当前数据行的全部内容；

对于 InnoDB 来说，所有的数据都是以键值对的方式存储的，主键索引和辅助索引在存储数据时会将 id 和 index 作为键，将所有列和 id 作为键对应的值。

为什么 InnoDB 选择使用 B+ 树呢？因为：

*  InnoDB 需要支持的场景和功能需要在特定查询上拥有较强的性能。
 *  CPU 将磁盘上的数据加载到内存中需要花费大量的时间，这使得 B+ 树成为了非常好的选择。

MySQL 作为 OLTP（Online Transaction Processing，在线事务处理）的数据库不仅需要具备事务的处理能力，而且要保证数据的持久化并且能够有一定的实时数据查询能力，这些需求共同决定了 B+ 树的选择。

作为支撑 OLTP 数据库的存储引擎，我们经常会使用 InnoDB 完成以下的一些工作：

*  通过 INSERT、UPDATE 和 DELETE 语句对表中的数据进行增加、修改和删除；
 *  通过 UPDATE 和 DELETE 语句对符合条件的数据进行批量的删除；
 *  通过 SELECT 语句和主键查询某条记录的全部列；
 *  通过 SELECT 语句在表中查询符合某些条件的记录并根据某些字段排序；
 *  通过 SELECT 语句查询表中数据的行数；
 *  通过唯一索引保证表中某个字段或者某几个字段的唯一性；

如果我们使用 B+ 树作为底层的数据结构，那么所有只会访问或者修改一条数据的 SQL 的时间复杂度都是 O(log n)，也就是树的高度。

相对的，如果我们使用哈希作为底层的数据结构，遇到上述的场景时，使用哈希构成的主键索引或者辅助索引可能就没有办法快速处理了，**哈希对于处理范围查询或者排序性能会非常差，只能进行全表扫描并依次判断是否满足条件**。全表扫描对于数据库来说是一个非常糟糕的结果，这其实也就意味着我们使用的数据结构对于这些查询没有其他任何效果，最终的性能可能都不如从日志中顺序进行匹配。

使用 B+ 树其实能够保证数据按照键的顺序进行存储，也就是相邻的所有数据其实都是按照自然顺序排列的，使用哈希却无法达到这样的效果，因为哈希函数的目的就是让数据尽可能被分散到不同的桶中进行存储，所以在遇到可能存在相同键 或者排序以及范围查询时，由哈希作为底层数据结构的表可能就会面对数据库查询的噩梦 —— 全表扫描。

另外，B 树和 B+ 树在数据结构上其实有一些类似，它们都可以按照某些顺序对索引中的内容进行遍历，对于排序和范围查询等操作，B 树和 B+ 树相比于哈希会带来更好的性能。当然，如果索引建立不够好或者 SQL 查询非常复杂，依然会导致全表扫描。

B 树和 B+ 树相比，哈希作为底层的数据结构的表能够以 O(1) 的速度处理单个数据行的增删改查，但是面对范围查询或者排序时就会导致全表扫描的结果，而 B 树和 B+ 树虽然在单数据行的增删查改上需要 O(log n) 的时间，但是它会将索引列相近的数据按顺序存储，所以能够避免全表扫描。

那么为什么我们不选择 B 树呢？这个原因其实非常简单，计算机在读写文件时会以页为单位将数据加载到内存中。页的大小可能会根据操作系统的不同而发生变化，不过在大多数的操作系统中，页的大小都是 4KB。

当我们需要在数据库中查询数据时，CPU 会发现当前数据位于磁盘而不是内存中，这时就会触发 I/O 操作将数据加载到内存中进行访问，数据的加载都是以页的维度进行加载的，然而将数据从磁盘读取到内存中所需要的成本是非常大的，普通磁盘（非 SSD）加载数据需要经过队列、寻道、旋转以及传输的这些过程，大概要花费 10ms 左右的时间。

我们在估算 MySQL 的查询时就可以使用 10ms 这个数量级对随机 I/O 占用的时间进行估算，这里想要说的是随机 I/O 对于 MySQL 的查询性能影响会非常大，而顺序读取磁盘中的数据时速度可以达到 40MB/s，这两者的性能差距有几个数量级，由此我们也应该尽量减少随机 I/O 的次数，这样才能提高性能。

B 树与 B+ 树的最大区别就是，B 树可以在非叶结点中存储数据，但是 B+ 树的所有数据其实都存储在叶子节点中，当一个表底层的数据结构是 B 树时，假设我们需要访问所有大于 4，并且小于 9 的数据。如果不考虑任何优化，在上面的简单 B 树中我们需要进行 4 次磁盘的随机 I/O 才能找到所有满足条件的数据行：

1.  加载根节点所在的页，发现根节点的第一个元素是 6，大于 4；
2.  通过根节点的指针加载左子节点所在的页，遍历页面中的数据，找到 5；
3.  重新加载根节点所在的页，发现根节点不包含第二个元素；
4.  通过根节点的指针加载右子节点所在的页，遍历页面中的数据，找到 7 和 8；

当然我们可以通过各种方式来对上述的过程进行优化，不过 B 树能做的优化 B+ 树基本都可以，所以我们不需要考虑优化 B 树而带来的收益，直接来看看什么样的优化 B+ 树可以做，而 B 树不行。

**由于所有的节点都可能包含目标数据，我们总是要从根节点向下遍历子树查找满足条件的数据行，这个特点带来了大量的随机 I/O，也是 B 树最大的性能问题**。

B+ 树中就不存在这个问题了，因为所有的数据行都存储在叶节点中，而这些叶节点可以通过指针依次按顺序连接，当我们在 B+ 树遍历数据时可以直接在多个子节点之间进行跳转，这样能够节省大量的磁盘 I/O 时间，也不需要在不同层级的节点之间对数据进行拼接和排序；通过一个 B+ 树最左侧的叶子节点，我们可以像链表一样遍历整个树中的全部数据，我们也可以引入双向链表保证倒序遍历时的性能。

有些读者可能会认为使用 B+ 树这种数据结构会增加树的高度从而增加整体的耗时，然而高度为 3 的 B+ 树就能够存储千万级别的数据，实践中 B+ 树的高度最多也就 4 或者 5，所以这并不是影响性能的根本问题。

可见：

*  **哈希虽然能够提供 O(1) 的单数据行操作性能，但是对于范围查询和排序却无法很好地支持，最终导致全表扫描**；
 *  **B 树能够在非叶节点中存储数据，但是这也导致在查询连续数据时可能会带来更多的随机 I/O**；
 *  **而 B+ 树的所有叶节点可以通过指针相互连接，能够减少顺序遍历时产生的额外随机 I/O**；

如果想要追求各方面的极致性能也不是没有可能，只是会带来更高的复杂度，我们可以为一张表同时建 B+ 树和哈希构成的存储结构，这样不同类型的查询就可以选择相对更快的数据结构，但是会导致更新和删除时需要操作多份数据。

从今天的角度来看，B+ 树可能不是 InnoDB 的最优选择，但是它一定是能够满足当时设计场景的需要，从 B+ 树作为数据库底层的存储结构到今天已经过了几十年的时间。

## B+ 树的查找过程 ##

如上图所示，如果要查找数据项 29，那么：

1.  首先会把磁盘块 1 从磁盘加载到内存，此时发生一次 IO。
2.  在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短，所以可以忽略不计。通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO。
3.  29 在 26 和 30 之间，锁定磁盘块 3 的P2指针，通过指针加载磁盘块 8 到内存，发生第三次 IO。

同时内存中做二分查找找到 29，结束查询，总计三次 IO。

可见，3 层的 b+ 树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。

## B+ 树性质 ##

通过上面的分析，我们知道 IO 次数取决于 B+ 数的高度 h，假设当前数据表的数据为 N，每个磁盘块的数据项的数量是 m，则有 `h=㏒(m+1)N`，当数据量 N 一定的情况下，m 越大，h 越小；而 `m = 磁盘块的大小 / 数据项的大小`，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么：每个数据项，即索引字段要尽量的小，比如 int 占 4 字节，要比 bigint8 字节少一半。这也是为什么：B+ 树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于 1 时将会退化成线性表。

当 B+ 树的数据项是复合的数据结构，比如：`name,age,sex` 的时候，B+ 数是按照从左到右的顺序来建立搜索树的，比如当 `(张三,20,F)` 这样的数据来检索的时候，B+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但当 `(20,F)` 这样的没有 name 的数据来的时候，B+ 树就不知道下一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如当 `(张三,F)` 这样的数据来检索时，B+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了， 这个是非常重要的性质，即：索引的最左匹配特性。

## 慢查询优化 ##

### 建索引的几大原则 ###

1.  **最左前缀匹配原则（非常重要）**，MySQL 会一直向右匹配直到遇到范围查询：>、<、between、like，就停止匹配，比如：`a = 1 and b = 2 and c > 3 and d = 4`，如果建立 `(a,b,c,d)` 顺序的索引，d 是用不到索引的，如果建立 `(a,b,d,c)` 的索引则都可以用到，a,b,d 的顺序可以任意调整。
2.  **= 和 in 可以乱序**，比如：`a = 1 and b = 2 and c = 3` 建立 `(a,b,c)` 索引可以任意顺序，MySQL 的查询优化器会帮你优化成索引可以识别的形式。
3.  **尽量选择区分度高的列作为索引**，区分度的公式是 `count(distinct col)/count(*)`，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、sex 字段可能在大数据面前区分度就是 0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10 条记录。
4.  **索引列不能参与计算，保持列的 “干净”**。比如：`from_unixtime(create_time) = '2014-05-29'` 就不能使用到索引。原因很简单，B+ 树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 `create_time = unix_timestamp('2014-05-29')`。
5.  **尽量的扩展索引，不要新建索引**，比如：表中已经有 a 的索引，现在要加 `(a,b)` 的索引，那么只需要修改原来的索引即可。

## 参考文章 ##

https://draveness.me/mysql-innodb/

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ptaWxr_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20201008093710122.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ptaWxr,size_16,color_FFFFFF,t_70#pic_center