在 Mysql 中通过 EXPLAIN 语句可以查看到最后优化器决定使用的执行计划，却无法知道它为什么做这个决策。我们可能有这样的疑问“我觉得使用其他的执行方案比 EXPLAIN 输出的这种方案强，凭什么优化器做的决定和我想的不一样呢？”，“为什么 MySQL 一定要全文扫描，不用索引呢？”

今天我们就来了解一下 mysql 内部查询成本是如何计算的，下面是我们后续需要用到的一张表。

CREATE TABLE `order_exp`  (
  `id` bigint(255) NOT NULL AUTO_INCREMENT COMMENT '订单主键',
  `order_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号',
  `order_note` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单描述',
  `insert_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '订单插入时间',
  `expire_duration` bigint(255) NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime(0) NOT NULL COMMENT '订单过期时间',
  `order_status` smallint(6) NOT NULL COMMENT '订单的状态,0:未支付;:已支付;-1:已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `u_idx_day_status`(`insert_time`, `expire_time`, `order_status`) USING BTREE,
  INDEX `idx_order_no`(`order_no`) USING BTREE,
  INDEX `idx_expire_time`(`expire_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

上表中我们建立了三个索引
微信截图_20211218151149.png

Optimizer Trace

在MySQL 5.6以及之后的版本中，MySQL 提出了一个 optimizer trace 的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程。开启 trace 工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭。

比如我们执行以下查询：

SET optimizer_trace="enabled=on"; 

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; 

SELECT * FROM information_schema.OPTIMIZER_TRACE

我们找到 “row_estimation” 字段，里面就是关于 MySQL 计算查询成本的信息。对于上面查询语句，其全表信息如下。可以看出，全表的记录行数是 10354，全表扫描计算得到的成本是 2169.9。
微信截图_20211218162028.png

关于索引的使用情况如下：
微信截图_20211218153356.png
可以看出，只有 “idx_order_no”，”idx_expire_tiem” 索引生效，下面就是使用这两个索引查询的成本：
微信截图_20211218165453.png
使用全表扫描的成本为 2169.9，使用 “idx_order_no” 索引的成本为 72.61，使用 “idx_expire_tiem” 索引的成本为 47.81，所以mysql最终选择 “idx_expire_tiem” 索引来进行查询，和 EXPLAIN 打印出来的结果是一样的。
微信截图_20211218170008.png

什么查询成本？

MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。什么是执行成本呢？其实在MySQL中一条查询语句的执行成本是由下边这两个方面组成的：

I/O 成本：我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。
CPU 成本：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。
1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成
本常数。注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。在一条单表查询语句真正执行之前，MySQL 的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，这个过程总结就是这样：

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

查询成本实战

我们依然以前面的查询语句为例，按照上面四个步骤逐一分析。

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：由于该列上只有联合索引，而且不符合最左前缀原则，所以不会用到索引。

综上所述，上边的查询语句可能用到的索引，“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

微信截图_20211218175642.png

出现了很多统计选项，但我们目前只需要两个：

Rows：本选项表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。
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指的是加载一个页面的IO成本常数，后边的1.1是
一个微调值（MySQL 在真实计算成本时会进行一些微调，这些微调的值是直接硬编码到代码里的，没有注释而且这些微调的值十分的小，并不影响我们大方向的分析）

CPU成本：

10354x 0.2 + 1.0 = 2071.8

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

总成本：

98.1 + 2071.8= 2169.9

综上所述，对于order_exp的全表扫描所需的总成本就是2169.9，对比看一看是不是和 trace 工具打印出来的值一样。

计算不同索引查询成本

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

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’ )。使用 idx_expire_time 搜索会使用二级索引 + 回表方式的查询，MySQL计算这种查询的成本依赖两个方面的数据：

范围区间数量

不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索引的一个范围区间的 I/O 成本和读取一个页面是相同的。本例中使用 idx_expire_time 的范围区间只有一个，所以相当于访问这个范围区间的二级索引付出的 I/O 成本就是：1 x 1.0 = 1.0

需要回表的记录数

优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算 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+树索引的结构中来。
微信截图_20211218185647.png

我们假设区间最左记录在页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’

读取这39条二级索引记录需要付出的 CPU 成本就是：39 x 0.2 + 0.01 = 7.81，其中39是需要读取的二级索引记录条数，0.2是读取一条记录成本常数，0.01是微调。
微信截图_20211218190407.png

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

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

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

检测其他搜索条件是否成立

回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录，然
后再检测除 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_order_no 时有3个单点区间，所以每个单点区间都需要查找一遍对应的
二级索引记录数，三个单点区间总共需要回表的记录数是58。

explain SELECT * FROM order_exp WHERE order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)

读取这些二级索引记录的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

找出成本最低的方案

下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来：

全表扫描的成本：2169.9
使用idx_expire_time的成本：55.61
使用idx_order_no的成本：84.21

很显然，使用 idx_expire_time 的成本最低，所以当然选择 idx_expire_time 来执行查
询。来和 Tracer 中的比较一下：
微信截图_20211218165453.png
嗯？除了全表扫描，其他的怎么好像有点对不上呢？请注意：在MySQL的实际计算中，在和全文扫描比较成本时，使用索引的成本会去除读取并检测回表后聚簇索引记录的成本，也就是说，我们通过MySQL看到的成本将会是：

idx_expire_time为 47.81 = 55.61-7.8
idx_order_no为 72.61 = 84.21-11.6

但是 MySQL 比较完成本后，会再计算一次使用索引的成本，此时就会加上前面去除的成本，也就是我们计算出来的值。MySQL 的源码中对成本的计算实际要更复杂，但是基本思想和算法是没错的。

Mysql专题

MySQL 查询成本是如何计算的？