MySQL查询成本

---

MySQL学习系列

---

什么是成本

MySQL 执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模糊的，其实在 MySQL 中一条查询语句的执行成本是由下边这两个方面组成的：

• I/O 成本

我们的表经常使用的 MyISAM、InnoDB 存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。

• CPU 成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本。

对于 InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是 1.0，读取以及检测一条记录是否符合搜索条件的成本默认是 0.2。1.0、0.2 这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成本常数。注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是 0.2。

---

单表查询的成本

基于成本的优化步骤实战

在一条单表查询语句真正执行之前，MySQL 的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，这个过程总结一下就是这样：

1. 根据搜索条件，找出所有可能使用的索引
2. 计算全表扫描的代价
3. 计算使用不同索引执行查询的代价
4. 对比各种执行方案的代价，找出成本最低的那一个

下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：

SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') 
AND expire_time > '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09' 
AND insert_time > expire_time AND order_note LIKE '%7 排1%' AND order_status = 0;

乍看上去有点儿复杂，我们一步一步分析一下。
我们分析一下上边查询中涉及到的几个搜索条件：

order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) ，这个搜索条件可以使用二级索引 idx_order_no。
expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’，这个搜索条件可以使用二级索引 idx_expire_time。
insert_time> expire_time，这个搜索条件的索引列由于没有和常数比较，所以并不能使用到索引。
order_note LIKE ‘%hello%’，order_note 即使有索引，但是通过 LIKE 操作符和以通配符开头的字符串做比较，不可以适用索引。order_status = 0，由于该列上只有联合索引，而且不符合最左前缀原则，所以不会用到索引。

综上所述，上边的查询语句可能用到的索引，也就是 possible keys 只有idx_order_no,idx_expire_time。

• 计算全表扫描的代价

对于 InnoDB 存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=I/O 成本+CPU 成本，所以计算全表扫描的代价需要两个信息：
聚簇索引占用的页面数
该表中的记录数
这两个信息从哪来呢？MySQL 为每个表维护了一系列的统计信息，关于这些统计信息是如何收集起来的我们放在后边再说，现在看看怎么查看这些统计信息。MySQL 给我们提供了 SHOW TABLE STATUS 语句来查看表的统计信息，如果
要看指定的某个表的统计信息，在该语句后加对应的 LIKE 语句就好了，比方说我们要查看 order_exp 这个表的统计信息可以这么写：

SHOW TABLE STATUS LIKE 'order_exp'\G

mysql> SHOW TABLE STATUS LIKE 'order_exp'\G
*************************** 1. row ***************************
           Name: order_exp
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 10668
 Avg_row_length: 148
    Data_length: 1589248
Max_data_length: 0
   Index_length: 1212416
      Data_free: 4194304
 Auto_increment: 10819
    Create_time: 2022-01-03 04:10:18
    Update_time: 2022-01-03 04:11:17
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment:
1 row in set (0.00 sec)

出现了很多统计选项，但我们目前只需要两个：
Rows
本选项表示表中的记录条数。对于使用 MyISAM 存储引擎的表来说，该值是准确的，对于使用 InnoDB 存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的 order_exp 表是使用 InnoDB 存储引擎的，所以虽然实际上表中有 10567 条记录，但是 SHOW TABLE STATUS 显示的 Rows 值只有10350 条记录。

Data_length
本选项表示表占用的存储空间字节数。使用 MyISAM 存储引擎的表来说，该值就是数据文件的大小，对于使用 InnoDB 存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：
Data_length = 聚簇索引的页面数量 x 每个页面的大小
我们的 order_exp 使用默认 16KB 的页面大小，而上边查询结果显示Data_length 的值是 1589248，所以我们可以反向来推导出聚簇索引的页面数量：聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97
我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所以就可以计算全表扫描成本了。
现在可以看一下全表扫描成本的计算过程：

I/O 成本
97 x 1.0 + 1.1 = 98.1
97 指的是聚簇索引占用的页面数，1.0 指的是加载一个页面的成本常数，后边的 1.1 是一个微调值。

TIPS：MySQL 在真实计算成本时会进行一些微调，这些微调的值是直接硬编码到代码里的，没有注释而且这些微调的值十分的小，并不影响我们分析。

CPU 成本
10350x 0.2 + 1.0 = 2071
10350 指的是统计数据中表的记录数，对于 InnoDB 存储引擎来说是一个估计值，0.2 指的是访问一条记录所需的成本常数，后边的 1.0 是一个微调值。

总成本：
98.1 + 2071 = 2169.1

综上所述，对于 order_exp 的全表扫描所需的总成本就是 2169.1。

TIPS：我们前边说过表中的记录其实都存储在聚簇索引对应 B+树的叶子节点中，所以只要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。也就是说全表扫描这个过程其实有的 B+树非叶子节点是不需要访问的，但是 MySQL 在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算 I/O成本的依据，是不区分非叶子节点和叶子节点的。

• 计算使用不同索引执行查询的代价

从第 1 步分析我们得到，上述查询可能使用到 idx_order_no，idx_expire_time这两个索引，我们需要分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并。这里需要提一点的是，MySQL 查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本，我们这里两个索引都是普通索引，先算哪个都可以。我们也先分析 idx_expire_time 的成本，然后再看使用idx_order_no 的成本。

使用 idx_expire_time 执行查询的成本分析

idx_expire_time 对应的搜索条件是：expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’ ，也就是说对应的范围区间就是：
(‘2021-03-22 18:28:28’ , ‘2021-03-22 18:35:09’ )。

思考题：扫描区间怎么样从我们复杂的 SQL 语句里提取出来？前面已经讲过了，不记得的同学回看一下章节《3.2.3.深入思考索引在查询中的使用》。
使用 idx_expire_time 搜索会使用用二级索引 + 回表方式的查询，MySQL 计算这种查询的成本依赖两个方面的数据：
1、范围区间数量
不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索引的一个范围区间的 I/O 成本和读取一个页面是相同的。本例中使用idx_expire_time 的范围区间只有一个，所以相当于访问这个范围区间的二级索引
付出的 I/O 成本就是：1 x 1.0 = 1.0

2、需要回表的记录数
优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算 idx_expire_time 在(‘2021-03-22 18:28:28’ ，‘2021-03-22 18:35:09’)这个范围区间中包含多少二级索引记录，计算过程是这样的：

步骤 1：先根据 expire_time> ‘2021-03-22 18:28:28’这个条件访问一下idx_expire_time 对应的 B+树索引，找到满足 expire_time> ‘2021-03-22 18:28:28’ 这个条件的第一条记录，我们把这条记录称之为区间最左记录。我们前头说过在
B+数树中定位一条记录的过程是很快的，是常数级别的，所以这个过程的性能消耗是可以忽略不计的。

步骤 2：然后再根据 expire_time<= ‘2021-03-22 18:35:09’这个条件继续从idx_expire_time 对应的 B+树索引中找出最后一条满足这个条件的记录，我们把这条记录称之为区间最右记录，这个过程的性能消耗也可以忽略不计的。

步骤 3：如果区间最左记录和区间最右记录相隔不太远（在 MySQL 5.7 这个版本里，只要相隔不大于 10 个页面即可），那就可以精确统计出满足 expire_time>‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’条件的二级索引记录条数。否则只沿着区间最左记录向右读 10 个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少
个页面呢？解决这个问题还得回到 B+树索引的结构中来。我们假设区间最左记录在页 b 中，区间最右记录在页 c 中，那么我们想计算区间最左记录和区间最右记录之间的页面数量就相当于计算页b和页 c 之间有多少页面，而它们父节点中记录的每一条目录项记录都对应一个数据页，所以计算页 b 和页 c 之间有多少页面就相当于计算它们父节点（也就是页 a）中对应的目录项记录之间隔着几条记录。在一个页面中统计两条记录之间有几条记录的成本就很小了。

不过还有问题，如果页 b 和页 c 之间的页面实在太多，以至于页 b 和页 c 对应的目录项记录都不在一个父页面中怎么办？既然是树，那就继续递归，之前我们说过一个 B+树有 4 层高已经很了不得了，所以这个统计过程也不是很耗费性能。

知道了如何统计二级索引某个范围区间的记录数之后，就需要回到现实问题中来，MySQL 根据上述算法测得 idx_expire_time 在区间(‘2021-03-22 18:28:28’ ，‘2021-03-22 18:35:09’)之间大约有 39 条记录。

explain SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09';

mysql> explain SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: range
possible_keys: idx_expire_time
          key: idx_expire_time
      key_len: 5
          ref: NULL
         rows: 39
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

读取这 39 条二级索引记录需要付出的 CPU 成本就是：
39 x 0.2 + 0.01 = 7.81

其中 39 是需要读取的二级索引记录条数，0.2 是读取一条记录成本常数，0.01是微调。

在通过二级索引获取到记录之后，还需要干两件事儿：

1、根据这些记录里的主键值到聚簇索引中做回表操作

MySQL 评估回表操作的 I/O 成本依旧很简单粗暴，他们认为每次回表操作都相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面 I/O。我们上边统计了使用idx_expire_time 二级索引执行查询时，预计有 39 条二级索引记录需要进行回表操作，所以回表操作带来的 I/O 成本就是：
39 x 1.0 = 39 .0
其中 39 是预计的二级索引记录数，1.0 是一个页面的 I/O 成本常数。

2、回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录，然后再检测除 expire_time> ‘2021-03-22 18:28:28’ AND expire_time< '2021-03-22 18:35:09’这个搜索条件以外的搜索条件是否成立。
因为我们通过范围区间获取到二级索引记录共 39 条，也就对应着聚簇索引中 39 条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索条件的 CPU 成本如下：
39 x 0.2 =7.8
其中 39 是待检测记录的条数，0.2 是检测一条记录是否符合给定的搜索条件的成本常数。

所以本例中使用 idx_expire_time 执行查询的成本就如下所示：

I/O 成本：
1.0 + 39 x 1.0 = 40 .0 (范围区间的数量 + 预估的二级索引记录条数)
CPU 成本：
39 x 0.2 + 0.01 + 39 x 0.2 = 15.61 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）
综上所述，使用 idx_expire_time 执行查询的总成本就是：
40 .0 + 15.61 = 55.61

• 使用 idx_order_no 执行查询的成本分析

idx_order_no 对应的搜索条件是：order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)，也就是说相当于 3 个单点区间。
与使用 idx_expire_time 的情况类似，我们也需要计算使用 idx_order_no 时需要访问的范围区间数量以及需要回表的记录数，计算过程与上面类似，我们不详列所有计算步骤和说明了。
范围区间数量
使用 idx_order_no 执行查询时很显然有 3 个单点区间，所以访问这 3 个范围区间的二级索引付出的 I/O 成本就是：
3 x 1.0 = 3.0
需要回表的记录数
由于使用 idx_expire_time 时有 3 个单点区间，所以每个单点区间都需要查找一遍对应的二级索引记录数，三个单点区间总共需要回表的记录数是 58。

explain SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S');

mysql> explain SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: range
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: NULL
         rows: 58
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

读取这些二级索引记录的 CPU 成本就是：58 x 0.2 + 0.01 = 11.61
得到总共需要回表的记录数之后，就要考虑：
根据这些记录里的主键值到聚簇索引中做回表操作，所需的 I/O 成本就是：
58 x 1.0 = 58.0
回表操作后得到的完整用户记录，然后再比较其他搜索条件是否成立
此步骤对应的 CPU 成本就是：
58 x 0.2 = 11.6
所以本例中使用 idx_order_no 执行查询的成本就如下所示：
I/O 成本：
3.0 + 58 x 1.0 = 61.0 (范围区间的数量 + 预估的二级索引记录条数)
CPU 成本：
58 x 0.2 + 58 x 0.2 + 0.01 = 23.21 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）

综上所述，使用 idx_order_no 执行查询的总成本就是：
61.0 + 23.21 = 84.21

• 是否有可能使用索引合并（Index Merge）

本例中有关 order_no 和 expire_time 的搜索条件是使用 AND 连接起来的，而对于 idx_order_no 和 idx_expire_time 都是范围查询，也就是说查找到的二级索引记录并不是按照主键值进行排序的，并不满足使用 Intersection 索引合并的条件，
所以并不会使用索引合并。而且 MySQL 查询优化器计算索引合并成本的算法也比较麻烦，所以我们也不会细说。

• 对比各种方案，找出成本最低的那一个

下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来：
全表扫描的成本：2148.7
使用 idx_expire_time 的成本：55.61
使用 idx_order_no 的成本：84.21
很显然，使用 idx_expire_time 的成本最低，所以当然选择 idx_expire_time来执行查询。

请注意：1、MySQL 的源码中对成本的计算实际要更复杂，但是基本思想和算法是没错的。
2、在 MySQL 的实际计算中，在和全文扫描比较成本时，使用索引的成本会去除读取并检测回表后聚簇索引记录的成本，也就是说，我们通过 MySQL 看到的成本将会是：idx_expire_time 为 47.81(55.61-7.8)，idx_order_no 为72.61(84.21-11.6)。但是 MySQL 比较完成本后，会再计算一次使用索引的成本，此时就会加上去除读取并检测回表后聚簇索引记录的成本，也就是我们计算出来的值。