vlambda博客
学习文章列表

1. 前言

我们知道，全表扫描适用于任何查询，这是最简单也是最笨拙的一种查询方式，它的缺点是当表中数据量较大时性能就会非常差，因为需要扫描整棵聚簇索引 B+树的叶子节点，涉及到大量的磁盘 IO 和 CPU 计算。为了解决全表扫描的性能问题，我们可以给条件列加上索引，这样就可以形成一个较小的扫描区间，过滤掉绝大部分的记录，从而提高查询效率。如果过滤条件十分复杂，涉及到多个列，我们还可以给多个列都加上索引，MySQL 会判断可能会使用到哪些索引，以及使用这些索引对应的执行成本是多少，最终选择成本最低的索引方案，这就是所谓的执行计划。最后一步根据执行计划调用存储引擎提供的接口，读取数据并返回给客户端了。

流程我们是清楚了，但是这里还有几个问题。

• 使用不同索引的执行成本里的「成本」到底是什么？
• MySQL 又是如何去计算这些所谓的「成本」的？
• 计算的「成本」是否准确？开销大不大？
• 优化器为何如此智能，可以自动选择更优的索引？
• 优化器会做出错误的决策吗？
• 明明使用索引 A 效率更高，为什么 MySQL 错误的使用了索引 B？
• 我们可以修改优化器的决策吗？

大家可以思考一下这些问题，本篇文章重点分析「成本」的计算规则，相信看完会解决你的大部分疑惑，但有的问题还是需要你们自己去思考。

2. 执行成本

在了解成本是如何被计算出来的之前，我们先知晓一下，成本到底是什么？一条查询语句在 MySQL 中的执行成本，主要由两部分组成：

• IO 成本：记录存储在磁盘上，检索记录首先需要将记录从磁盘加载到内存，然后才能进行操作。
• CPU 成本：记录加载到内存后，CPU 负责读取记录、判断是否符合过滤条件、对结果集排序等等操作。

对于 InnoDB 引擎，有两个非常重要的成本常数，大家要牢记在心。「页」是磁盘和内存交互的基本单位，MySQL 默认读取一个页的成本是1.0，读取以及检测一条记录是否符合搜索条件的成本是0.2。

2.1 单表查询成本

我们先看简单的单表查询成本是如何计算的，再看复杂的多表查询。

SELECT *
FROM T
WHERE a BETWEEN 1 AND 100
  AND b < 100
  AND c LIKE '%xx%';

现有如上 SQL 语句，假设 a、b、c 列均有索引。MySQL 在真正执行它之前，首先需要经过优化器找出所有可以执行的方案，最终选择成本最低的方案，这个最终的方案就是执行计划，然后根据执行计划去调用存储引擎层提供的接口执行真正的查询。过程是这样的：

1. 根据搜索条件，找出可能使用的索引。
2. 计算全表扫描的成本。
3. 计算使用不同索引查询的成本。
4. 选择成本最低的方案，执行查询。

我们按照这个步骤来分析一下，MySQL 的大体流程。

1、根据搜索条件，找出可能使用的索引

列 a 和列 b 均可以使用到索引，但是列 c 由于使用了左侧模糊匹配，所以是不能应用到索引的，所以该查询可能使用的索引是idx_a和idx_b，执行方案一共有三种：

• 全表扫描
• 使用 idx_a索引找到符合条件的记录 id，回表查询判断其它条件是否符合。
• 使用 idx_b索引找到符合条件的记录 id，回表查询判断其它条件是否符合。

2、计算全表扫描的成本

全表扫描的本质是扫描整棵聚簇索引 B+树，再准确点应该是扫描 B+树的所有叶子节点，内节点是不需要扫描的，InnoDB 只需要从最左侧的叶子节点顺序的往后扫描即可。但是 MySQL 在计算成本时简单粗暴，直接将扫描整棵 B+树的成本算进去了，这里大家要注意一下。要计算全表扫描的成本，首先我们必须要知道两件事：1、聚簇索引 B+树占用了多少页面？2、表中有多少条记录？

MySQL 如何知道这些数据呢？不还得全表扫描吗？非也，执行成本只是一个预估值，没必要精确计算。MySQL 通过表的统计数据就可以计算出来。查看表统计信息的语法如下：

mysql> SHOW TABLE STATUS LIKE 'T';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| T    | InnoDB |      10 | Dynamic    | 9981 |             34 |      344064 |               0 |       376832 |  14680064 |         160008 | 2022-03-06 11:47:20 | 2022-03-13 11:36:55 | NULL       | utf8_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+

信息太多了，我们重点关注Rows和Data_length这两列。

• Rows：表的记录数量，对于 MYISAM 引擎它是一个准确值，对于 InnoDB 引擎，很可惜它只是一个预估值，实际表 T 有一万多条记录。
• Data_length：表占用的存储空间字节数，对于 InnoDB 引擎，它代表聚簇索引占用的空间字节数。

所以，我们可以推导出聚簇索引占用的页面数，公式是Data_length/(16*1024)，结果是 21 个页面。因此全表扫描的成本计算是这样的：

• IO 成本： 21 * 1.0 + 1.1 = 22.1
• CPU 成本： 9981 * 0.2 + 1.0 = 1997.2
• 总成本： 22.1 + 1997.2 = 2019.3

  IO 成本里最后的的 1.1 和 CPU 成本里最后的 1.0 是 MySQL 规定的微调值，不必在意。

3、计算使用不同索引查询的成本可能使用到的索引有idx_a和idx_b，使用二级索引来帮助查询，涉及到的成本有：

• 二级索引页的 IO 成本+二级索引记录的 CPU 成本
• 回表的 IO 成本+回表记录的 CPU 成本

我们先来看使用idx_a索引的执行成本，列 a 的搜索条件是a BETWEEN 1 AND 100，形成的扫描区间就是[1,100]。优化器规定，读取二级索引的一个扫描区间的 IO 成本，和读取一个页面的 IO 成本相同，无论它占用多少页面。这个是规定，大家记住就好了，因此二级索引页的 IO 成本就是1.0。接下来就是估算二级索引过滤后的记录数量了，也就是满足a BETWEEN 1 AND 100的记录数量。MySQL 是这样预估的：

1. 找到 idx_aB+树中 a=1的第一条记录，称为该区间的最左记录，这个过程是极快的。
2. 找到 idx_aB+树中 a=100的最后一条记录，称为该区间的最右记录，这个过程也是极快的。
3. 从最左记录向右最多读 10 个页面，如果读到了最右记录，则精确计算区间的记录数。
4. 如果读不到最右记录，说明中间记录比较多，则采用预估法。对 10 个页面中的记录数取平均值，用平均值乘以区间的页面数量即可。

   索引页的 Page Header 部分有PAGE_N_RECS属性记录了页中的记录数，因此不用遍历每个页里的记录。

又带来一个新的问题，如何计算这个区间的页面数量呢？还记得 B+树的结构吗？该区间的第 0 层的叶子节点数虽然很多，难以统计，但是我们可以看它们的父节点啊，这两个索引页的目录项大概率是会在同一个父节点页中的，在父节点页中统计区间内有多少页面就非常容易了，其实就是统计两个目录项之间隔了多少个目录项记录。这里，我们假设满足a BETWEEN 1 AND 100的记录数是 50 个，则二级索引记录的 CPU 成本是50 * 0.2 + 0.01 = 10.01。

最后的 0.01 是 MySQL 规定的微调值，不必在意。

接下来就是这 50 条记录回表的 IO 成本了，MySQL 规定，每次回表的 IO 成本相当于读取一个页面的 IO 成本，二级索引过滤出的记录数量就是回表的次数。因此，回表的 IO 成本是50 * 1.0 = 50.0。回表后读取到的完整的用户记录，还需要判断是否符合其它搜索条件，因此还有一个 CPU 成本是50 * 0.2 = 10.0。综上所述，使用idx_a索引的执行成本是：

• IO 成本： 1.0 + 50.0 = 51.0。
• CPU 成本： 10.01 + 10.0 = 20.01。
• 总成本： 51.0 + 20.01 = 71.01。

很显然，使用idx_a索引的执行成本比全表扫描要低的多，接下来就要看idx_b是否会更低了，否则会采用idx_a索引方案。

使用idx_b索引的成本计算和idx_a类似，就不再细说了。b<100会形成一个扫描区间[-∞,100)，对应着读取一个页面的 IO 成本，然后预估这个区间的记录数量，最后计算回表的 IO 成本和 CPU 成本。我们假设最终使用idx_b的总成本是80.0，那么，三种方案对应的成本分别是：

因此，MySQL 最终会采用idx_a索引来完成查询。

2.2 基于索引统计数据

我们已经知道，对于二级索引的一个扫描区间，它的 IO 成本是1.0，在评估区间的记录数量时，最差的情况下，InnoDB 需要读取 10 个页面求平均值来预估。MySQL 把这种通过访问二级索引 B+树来预估区间记录数的方式叫作「Index dive」。可以看出，这个操作其实开销并不小。有时候，我们的查询 SQL 往往会导致大量的扫描区间，例如往IN条件里疯狂塞参数，如下：

SELECT * FROM T WHERE a IN (1,2,3,4......10000);

假设IN里面有一万个参数，那么 MySQL 在预估这些区间的记录数量时，极端情况下至少需要读取10000 * 10=100000个页面才能得到结果，这个开销是非常大的，甚至会超过全表扫描本身的成本。这就太糟糕了，优化器光是分析一个执行方案的成本就要花费这么大的开销，显然是不能被接受的。这种场景下，Index dive 就不能被使用，MySQL 转而使用另一种方式来快速预估区间内的记录数。

MySQL 除了会为每张表维护一份统计数据外，也会为每个索引维护一份统计数据，查看索引统计数据的语法如下：

mysql> SHOW INDEX FROM T;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |        9981 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | idx_a    |            1 | a           | A         |        6737 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | idx_b    |            1 | b           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

每个列代表的含义如下：

我们重点关注Cardinality属性，它代表了索引列中不重复的值的个数，通过表的统计信息中的Rows属性，我们就可以计算出该列的一个值在表中平均重复了多少次。计算方式是Rows/Cardinality，我们假设结果为 2，也就是列 a 的一个值平均重复了两次。那么上述 SQL 语句中的一万个扫描区间，也就对应着10000*2=20000条记录，也就意味着回表的次数是 2 万。

基于索引统计数据的方式预估二级索引区间内的记录数，比 Index dive 的方式简单的多，效率也高的多，缺点是不准确。所以，我们必须在效率和准确性上做权衡，MySQL 通过参数eq_range_index_dive_limit来控制单点扫描区间数量超过多少时，放弃 Index dive 转而用索引统计数据的方式来计算，MySQL5.7 默认值是 200。

mysql> SHOW VARIABLES LIKE 'eq_range_index_dive_limit';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+

其实理解了单表查询的计算成本，多表连接的查询成本计算就是依葫芦画瓢了，这里就先不赘述了。

2.3 修改成本常数

我们前面说过，MySQL 默认读取一个页面的 IO 成本常数是1.0，读取并检测一条记录是否符合搜索条件的 CPU 成本常数是0.2，除此之外，还有很多成本常数，这些成本常数是可以配置的，MySQL 把它们存储在mysql数据库里，有两张表分别是server_cost和engine_cost。MySQL 架构有 Server 层和存储引擎层，对于在 Server 层操作的成本常数就保存在server_cost里，对于在存储引擎层操作的成本常数就保存在engine_cost表里。

我们先看server_cost，如下：

mysql> SELECT * FROM mysql.server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |       NULL | 2018-12-15 19:52:11 | NULL    |
| disk_temptable_row_cost      |       NULL | 2018-12-15 19:52:11 | NULL    |
| key_compare_cost             |       NULL | 2018-12-15 19:52:11 | NULL    |
| memory_temptable_create_cost |       NULL | 2018-12-15 19:52:11 | NULL    |
| memory_temptable_row_cost    |       NULL | 2018-12-15 19:52:11 | NULL    |
| row_evaluate_cost            |       NULL | 2018-12-15 19:52:11 | NULL    |
+------------------------------+------------+---------------------+---------+

cost_name 表示成本常数的名称，cost_value 代表成本常数的值，为 NULL 的话 MySQL 会使用默认值。这些成本常数对应的含义如下：

这些成本常数的修改应当要十分慎重，它会影响 MySQL 优化器的决策，比如增大memory_temptable_create_cost的值，MySQL 将减少创建基于内存的临时表，更多的采用创建基于磁盘的临时表，修改不当反而会影响 MySQL 的效率。

表修改完成后，还需要调用FLUSH OPTIMIZER_COSTS让 MySQL 重新加载成本常数，才能生效。

再看engine_cost表，如下：

mysql> SELECT * FROM mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment |
+-------------+-------------+------------------------+------------+---------------------+---------+
| default     |           0 | io_block_read_cost     |       NULL | 2018-12-15 19:52:11 | NULL    |
| default     |           0 | memory_block_read_cost |       NULL | 2018-12-15 19:52:11 | NULL    |
+-------------+-------------+------------------------+------------+---------------------+---------+

• engine_name：成本常数适用的存储引擎， default代表适用于任何存储引擎。
• device_type：存储引擎使用的设备类型，主要是为了区分机械硬盘和固态硬盘。

engine_cost表数据要少的多，只有两列：

磁盘和内存的速度天差地别，为啥从磁盘和内存读取一个块的成本是一样的呢？这主要是因为，MySQL 会将读取到的页缓存起来，并不是用完就释放掉的，只有当内存不足时才会释放掉。如此一来，MySQL 其实就不能判断你要读取的页是在磁盘里，还是在内存里，所以目前它俩的成本常数是一样的。

3. 总结

MySQL 执行一条查询语句的流程是这样的，先找到所有可能用到的索引，然后计算全表扫描的成本，然后分别计算使用不同索引的成本，最终选择成本最低的方案来执行查询。这里说的成本其实是由 IO 成本和 CPU 成本组成的，对于 InnoDB 引擎来说，读取一个页的 IO 成本是1.0，读取一条记录并检测是否符合搜索条件的 CPU 成本是0.2。全表扫描的成本计算非常简单，根据表的统计数据即可预估出聚簇索引占用的页面数和表的总记录数。对于二级索引的辅助查询，除了过滤二级索引本身的 IO 成本+CPU 成本，还有回表的 IO 成本+CPU 成本，MySQL 会采用 Index dive 的方式来预估二级索引中符合条件的记录数，如果 Index dive 预估的开销太大，MySQL 不得不采用基于索引统计数据的方式来预估，开销极小但是结果不太准确。