MySQL执行成本是如何计算的？

程序员小潘
635 阅读11分钟

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，那么，三种方案对应的成本分别是：

执行方案执行成本
全表扫描2019.3
idx_a71.01
idx_b80.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      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

每个列代表的含义如下：

列名说明
Table索引所属表的名称
Non_unique是否是非唯一索引，主键和唯一索引是0，其它是1
Key_name索引的名称
Seq_in_index索引列在索引中的位置，从1开始计数
Column_name索引列的名称
Collation排序方式，A是升序，NULL是降序
Cardinality索引列中不重复值的数量，基数
Sub_part字符串列的前缀索引的字符数
Packed索引列如何被压缩
Null是否允许存储 NULL
Index_type用索引的类型
Comment索引列注释信息
Index_comment索引注释信息

我们重点关注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会使用默认值。这些成本常数对应的含义如下：

cost_name默认值说明
disk_temptable_create_cost40.0创建基于磁盘的临时表的成本
disk_temptable_row_cost1.0向基于磁盘的临时表写入或读取一条记录的成本
key_compare_cost0.1两条记录做比较操作的成本，多用在排序上
memory_temptable_create_cost2.0创建基于内存的临时表的成本
memory_temptable_row_cost0.2向基于内存的临时表写入或读取一条记录的成本
row_evaluate_cost0.2检测一条记录是否符合搜索条件的成本

这些成本常数的修改应当要十分慎重，它会影响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表数据要少的多，只有两列：

cost_name默认值说明
io_block_read_cost1.0从磁盘上读取一个块对应的成本，对于InnoDB，一个块就是一个页
memory_block_read_cost1.0从内存中读取一个块对应的成本

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

3. 总结

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

avatar
文章
阅读
粉丝