MySQL 的执行原理（二）

5.3. MySQL 的查询成本

MySQL 执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者
说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模
糊的，其实在 MySQL 中一条查询语句的执行成本是由下边这两个方面组成的：
I/O 成本
我们的表经常使用的 MyISAM、InnoDB 存储引擎都是将数据和索引都存储到
磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然
后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。
CPU 成本
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作
损耗的时间称之为 CPU 成本。

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

5.3.2. 单表查询的成本

5.3.2.1. 基于成本的优化步骤实战

在一条单表查询语句真正执行之前，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;

乍看上去有点儿复杂，我们一步一步分析一下。

根据搜索条件，找出所有可能使用的索引
我们前边说过，对于 B+树索引来说，只要索引列和常数使用=、<=>、IN、
NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写
成<>）或者 LIKE 操作符连接起来，就可以产生一个所谓的范围区间（LIKE 匹配字
符串前缀也行），MySQL 把一个查询中可能使用到的索引称之为 possible keys。
我们分析一下上边查询中涉及到的几个搜索条件：

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。

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

SHOW TABLE STATUS LIKE 'order_exp'\G

在这里插入图片描述
出现了很多统计选项，但我们目前只需要两个：
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
成本的依据，是不区分非叶子节点和叶子节点的。

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

从第 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';

在这里插入图片描述
读取这 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');

在这里插入图片描述
读取这些二级索引记录的 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 查询优化器计算索引合并成本的算法也
比较麻烦，所以我们也不会细说。
4. 对比各种方案，找出成本最低的那一个
下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来：
全表扫描的成本：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 比较完成本后，会再计算一次使用索引的成本，
此时就会加上去除读取并检测回表后聚簇索引记录的成本，也就是我们计算出来
的值。

5.3.2.2. 基于索引统计数据的成本计算

index dive
有时候使用索引执行查询时会有许多单点区间，比如使用 IN 语句就很容易
产生非常多的单点区间，比如下边这个查询（下边查询语句中的…表示还有很多
参数）：

SELECT * FROM order_exp WHERE order_no IN ('aa1', 'aa2', 'aa3', ... , 'zzz');

很显然，这个查询可能使用到的索引就是 idx_order_no，由于这个索引并不
是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多
少，需要我们去计算。就是先获取索引对应的 B+树的区间最左记录和区间最右
记录，然后再计算这两条记录之间有多少记录（记录条数少的时候可以做到精确
计算，多的时候只能估算）。MySQL 把这种通过直接访问索引对应的 B+树来计
算某个范围区间对应的索引记录条数的方式称之为 index dive。

有零星几个单点区间的话，使用 index dive 的方式去计算这些单点区间对应
的记录数也不是什么问题，如果 IN 语句里 20000 个参数怎么办？

这就意味着 MySQL 的查询优化器为了计算这些单点区间对应的索引记录条
数，要进行 20000 次 index dive 操作，这性能损耗就很大，搞不好计算这些单点
区间对应的索引记录条数的成本比直接全表扫描的成本都大了。MySQL 考虑到了
这种情况，所以提供了一个系统变量 eq_range_index_dive_limit，我们看一下在
MySQL 5.7.21 中这个系统变量的默认值：

show variables like '%dive%';

在这里插入图片描述
也就是说如果我们的 IN 语句中的参数个数小于 200 个的话，将使用 index
dive 的方式计算各个单点区间对应的记录条数，如果大于或等于 200 个的话，可
就不能使用 index dive 了，要使用所谓的索引统计数据来进行估算。怎么个估算
法？
像会为每个表维护一份统计数据一样，MySQL 也会为表中的每一个索引维护
一份统计数据，查看某个表中索引的统计数据可以使用 SHOW INDEX FROM 表名
的语法，比如我们查看一下 order_exp 的各个索引的统计数据可以这么写：

show index from order_exp;

在这里插入图片描述
属性名描述

Table 索引所属表的名称。
Non_unique 索引列的值是否是唯一的，聚簇索引和唯一二级索引的该列
值为 0，普通二级索引该列值为 1。
Key_name 索引的名称。
Seq_in_index 索引列在索引中的位置，从 1 开始计数。比如对于联合索引
u_idx_day_status，来说，insert_time, order_status, expire_time对应的位置分
别是 1、2、3。
Column_name 索引列的名称。
Collation 索引列中的值是按照何种排序方式存放的，值为 A 时代表升序存
放，为 NULL 时代表降序存放。
Cardinality 索引列中不重复值的数量。后边我们会重点看这个属性的。
Sub_part 对于存储字符串或者字节串的列来说，有时候我们只想对这些串
的前 n 个字符或字节建立索引，这个属性表示的就是那个 n 值。如果对完整的列
建立索引的话，该属性的值就是 NULL。
Packed 索引列如何被压缩，NULL 值表示未被压缩。这个属性我们暂时不了
解，可以先忽略掉。
Null 该索引列是否允许存储 NULL 值。
Index_type 使用索引的类型，我们最常见的就是 BTREE，其实也就是 B+树索
引。
Comment 索引列注释信息。
Index_comment索引注释信息。
Cardinality 属性，Cardinality 直译过来就是基数的意思，表示索引列中不重
复值的个数。比如对于一个一万行记录的表来说，某个索引列的 Cardinality 属性
是 10000，那意味着该列中没有重复的值，如果 Cardinality 属性是 1 的话，就意
味着该列的值全部是重复的。不过需要注意的是，对于 InnoDB 存储引擎来说，
使用 SHOW INDEX 语句展示出来的某个索引列的 Cardinality 属性是一个估计值，
并不是精确的。
前边说道，当 IN 语句中的参数个数大于或等于系统变量
eq_range_index_dive_limit 的值的话，就不会使用 index dive 的方式计算各个单点
区间对应的索引记录条数，而是使用索引统计数据，这里所指的索引统计数据指
的是这两个值：

使用 SHOW TABLE STATUS 展示出的 Rows 值，也就是一个表中有多少条记录。
使用 SHOW INDEX 语句展示出的 Cardinality 属性。
结合上一个 Rows 统计数据，我们可以针对索引列，计算出平均一个值重复
多少次。
一个值的重复次数 ≈ Rows ÷ Cardinality
以 order_exp 表的 idx_order_no 索引为例，它的 Rows 值是 10350，它对应
的 Cardinality 值是 10220，所以我们可以计算 order_no 列平均单个值的重复次数
就是：10350÷ 10220≈ 1.012（条）
此时再看上边那条查询语句：

SELECT * FROM order_exp WHERE order_no IN ('aa1', 'aa2', 'aa3', ... , 'zzz');

假设 IN 语句中有 20000 个参数的话，就直接使用统计数据来估算这些参数
需要单点区间对应的记录条数了，每个参数大约对应 1.012 条记录，所以总共需
要回表的记录数就是：
20000 x 1.012= 21,730
使用统计数据来计算单点区间对应的索引记录条数比 index dive 的方式简单，
但是它的致命弱点就是：不精确！。使用统计数据算出来的查询成本与实际所需
的成本可能相差非常大。
大家需要注意一下，在 MySQL 5.7.3 以及之前的版本中，
eq_range_index_dive_limit 的默认值为 10，之后的版本默认值为 200。所以如果
大家采用的是 5.7.3 以及之前的版本的话，很容易采用索引统计数据而不是 index
dive 的方式来计算查询成本。当你的查询中使用到了 IN 查询，但是却实际没有
用到索引，就应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的

5.3.3. 再深入 Explain

5.3.3.1. EXPLAIN 输出成本

前面我们已经对 MySQL 查询优化器如何计算成本有了比较深刻的了解。但是 EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性 —— 成本。不过 MySQL 已经为我们提供了一种查看某个执行计划花费的成本的方式：
在 EXPLAIN 单词和真正的查询语句中间加上 FORMAT=JSON。
这样我们就可以得到一个 json 格式的执行计划，里边包含该计划花费的成
本，比如这样：

explain format=json 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\G

*************************** 1. row ***************************
EXPLAIN: {
"query_block": { "select_id": 1,
 # 整个查询语句只有 1 个 SELECT 关键字，该关键字对应的 id 号为 1
"cost_info": { "query_cost": "55.61" # 整个查询的执行成本预计为 55.61},
"table": { "table_name": "order_exp", "access_type": "range", "possible_keys": [ "idx_order_no", "idx_expire_time"
],
"key": "idx_expire_time", "used_key_parts": [ "expire_time"
],"key_length": "5", "rows_examined_per_scan": 39, "rows_produced_per_join": 0, "filtered": "0.13", "index_condition": "((`mysqladv`.`order_exp`.`expire_time` > '2021-03-22 18:28:28')and (`mysqladv`.`order_exp`.`expire_time` <= '2021-03-22 18:35:09'))", 
"cost_info": { "read_cost": "55.60", "eval_cost": "0.01", "prefix_cost": "55.61", #单独查询表的成本，也就是：read_cost + eval_cost "data_read_per_join": "24" #和连接查询相关的数据量，单位字节，这里无用
},
"used_columns": [ "id", "order_no", "order_note", "insert_time", "expire_duration", "expire_time", "order_status"
],"attached_condition": "((`mysqladv`.`order_exp`.`order_status` = 0) and
(`mysqladv`.`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and
(`mysqladv`.`order_exp`.`insert_time` > `mysqladv`.`order_exp`.`expire_time`) and
(`mysqladv`.`order_exp`.`order_note` like '%7 排 1%'))"
		}
	}
}
1 row in set, 1 warning (0.00 sec)

5.3.3.2. Optimizer Trace

对于 MySQL5.6 之前的版本来说，只能通过 EXPLAIN 语句查看到最后优化器
决定使用的执行计划，却无法知道它为什么做这个决策。我们可能有这样的疑问：
“我就觉得使用其他的执行方案比 EXPLAIN 输出的这种方案强，凭什么优化器做
的决定和我想的不一样呢？为什么 MySQL 一定要全文扫描，不用索引呢？”
在 MySQL 5.6 以及之后的版本中，MySQL 提出了一个 optimizer trace 的功能，
这个功能可以让我们方便的查看优化器生成执行计划的整个过程，这个功能的开
启与关闭由系统变量 optimizer_trace 决定：

SHOW VARIABLES LIKE 'optimizer_trace';

在这里插入图片描述
可以看到 enabled 值为 off，表明这个功能默认是关闭的。one_line 的值是控制输出格式的，如果为 on 那么所有输出都将在一行中展示，我们就保持其默认值为off。

如果想打开这个功能，必须首先把 enabled 的值改为 on，就像这样：

SET optimizer_trace="enabled=on";

在这里插入图片描述
然后我们就可以输入我们想要查看优化过程的查询语句，当该查询语句执行
完成后，就可以到 information_schema 数据库下的 OPTIMIZER_TRACE 表中查看完
整的优化过程。这个 OPTIMIZER_TRACE 表有 4 个列，分别是：

QUERY：表示我们的查询语句。
TRACE：表示优化过程的 JSON 格式文本。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。
INSUFFICIENT_PRIVILEGES：表示是否没有权限查看优化过程，默认值是 0，
只有某些特殊情况下才会是 1，我们暂时不关心这个字段的值。
当停止查看语句的优化过程时，把 optimizer trace 功能关闭。

SET optimizer_trace="enabled=off";

注意：开启 trace 会影响 mysql 性能，所以只能临时分析 sql 使用，用完之
后立即关闭。

现在我们有一个搜索条件比较多的查询语句，它的执行计划如下：

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

在这里插入图片描述
可以看到该查询可能使用到的索引有 3 个
u_idx_day_status,idx_order_no,idx_expire_time，那么为什么优化器最终选择了
idx_order_no 而不选择其他的索引或者直接全表扫描呢？这时候就可以通过 otpimzer trace 功能来查看优化器的具体工作过程：
记得开启 optimizer trace 功能

SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S',
'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND insert_time>
'2021-03-22 18:35:09' AND order_note LIKE '%7 排 1%';
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

展示的内容极多，我们选择优化过程中的一些比较重要的点关注一下：

*************************** 1. row *************************** # 分析的查询语句是什么
QUERY: SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND insert_time> '2021-03-22 18:35:09' AND order_note LIKE '%7 排 1%' # 优化的具体过程
TRACE: { "steps": [
{ "join_preparation": { # prepare 阶段
"select#": 1, "steps": [
{ "IN_uses_bisection": true
},{# 类似于查询优化器将查询语句重写后的语句
"expanded_query": "/* select#1 */ select `order_exp`.`id` AS `id`,`order_exp`.`order_no` AS
`order_no`,`order_exp`.`order_note` AS `order_note`,`order_exp`.`insert_time` AS `insert_time`,`order_exp`.`expire_duration` AS
`expire_duration`,`order_exp`.`expire_time` AS `expire_time`,`order_exp`.`order_status` AS `order_status` from `order_exp` where
((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` > '2021-03-22 18:28:28') and
(`order_exp`.`insert_time` > '2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))"
}
]
}
},{ "join_optimization": { # optimize 阶段
"select#": 1, "steps": [
{ "condition_processing": { # 处理搜索条件
"condition": "WHERE", # 原始搜索条件
"original_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` >
'2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))", "steps": [
{ "transformation": "equality_propagation", # 等值传递转换
"resulting_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and
(`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') and
(`order_exp`.`order_note` like '%7 排 1%'))"
},{ "transformation": "constant_propagation", # 常量传递转换
"resulting_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and
(`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') and
(`order_exp`.`order_note` like '%7 排 1%'))"
},{
"transformation": "trivial_condition_removal", # 去除没用的条件
"resulting_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and
(`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') and
(`order_exp`.`order_note` like '%7 排 1%'))"
}
]
}
},{ "substitute_generated_columns": { # 替换虚拟生成列
}
},{ "table_dependencies": [ # 表的依赖信息
{ "table": "`order_exp`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [
]
}
]
},{ "ref_optimizer_key_uses": [
]
},{ "rows_estimation": [ # 预估不同单表访问方法的访问成本
{ "table": "`order_exp`", "range_analysis": { "table_scan": { # 全表扫描的行数以及成本
"rows": 10345, "cost": 2168.1
},
"potential_range_indexes": [ # 分析可能使用的索引
{ "index": "PRIMARY", # 主键
"usable": false, "cause": "not_applicable" # 主键不可用
},{ "index": "u_idx_day_status", # u_idx_day_status 可能被使用
"usable": true, "key_parts": [ "insert_time", "order_status", "expire_time"
]
},{ "index": "idx_order_no", # idx_order_no 可能被使用
"usable": true, "key_parts": [ "order_no", "id"
]
},{ "index": "idx_expire_time", # idx_expire_time 可能被使用
"usable": true, "key_parts": [ "expire_time", "id"
]
}
],"setup_range_conditions": [
],"group_index_range": {
"chosen": false, "cause": "not_group_by_or_distinct"
},"analyzing_range_alternatives": { # 分析各种可能使用的索引的成本
"range_scan_alternatives": [
{ "index": "u_idx_day_status", # 使用 u_idx_day_status 的成本分析
"ranges": [ # 使用 u_idx_day_status 的范围区间
"0x99a92d28c9 < insert_time"
],"index_dives_for_eq_ranges": true, # 是否使用 index dive
"rowid_ordered": false, # 使用该索引获取的记录是否按照主键排序
"using_mrr": false, # 是否使用 mrr
"index_only": false, # 是否是索引覆盖访问
"rows": 5172, # 使用该索引获取的记录条数
"cost": 6207.4, # 使用该索引的成本
"chosen": false, # 是否选择该索引
"cause": "cost" # 因为成本太大所以不选择该索引
},{ "index": "idx_order_no", "ranges": [ "DD00_10S <= order_no <= DD00_10S", "DD00_6S <= order_no <= DD00_6S", "DD00_9S <= order_no <= DD00_9S"
],"index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 58, "cost": 72.61, "chosen": true
},{
"index": "idx_expire_time", "ranges": [ "0x99a92d271c < expire_time"
],"index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 5172, "cost": 6207.4, "chosen": false, "cause": "cost"
}
],"analyzing_roworder_intersect": { # 分析使用索引合并的成本
"usable": false, "cause": "too_few_roworder_scans"
}
},"chosen_range_access_summary": { # 对于上述单表查询 s1 最优的访问方法
"range_access_plan": { "type": "range_scan", "index": "idx_order_no", "rows": 58, "ranges": [ "DD00_10S <= order_no <= DD00_10S", "DD00_6S <= order_no <= DD00_6S", "DD00_9S <= order_no <= DD00_9S"
]
},"rows_for_plan": 58, "cost_for_plan": 72.61, "chosen": true
}
}
}
]
},{
# 分析各种可能的执行计划,负责对比各可行计划的开销，并选择相对最优的执行计划。
#（对多表查询这可能有很多种不同的方案，单表查询的方案上边已经分析过了，直接选取
idx_order_no 即可）
"considered_execution_plans": [
{ "plan_prefix": [
],"table": "`order_exp`", "best_access_path": { "considered_access_paths": [
{ "rows_to_scan": 58, "access_type": "range", "range_details": { "used_index": "idx_order_no"
},"resulting_rows": 58, "cost": 84.21, "chosen": true
}
]
},"condition_filtering_pct": 100, "rows_for_plan": 58, "cost_for_plan": 84.21, "chosen": true
}
]
},{
# 尝试给查询添加一些其他的查询条件,增加主要是为了便于 ICP（索引条件下推）
"attaching_conditions_to_tables": {
"original_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` >
'2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))", "attached_conditions_computation": [
],"attached_conditions_summary": [
{ "table": "`order_exp`", "attached": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` >
'2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))"
}
]
}
},{
# 再稍稍的改进一下执行计划
"refine_plan": [
{ "table": "`order_exp`", "pushed_index_condition": "(`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S'))", "table_condition_attached": "((`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` >
'2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))"}]}]}},{ "join_execution": { # execute 阶段
"select#": 1, "steps": []}}]}

# 因优化过程文本太多而丢弃的文本字节大小，值为 0 时表示并没有丢弃
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
# 权限字段
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

优化过程大致分为了三个阶段：
prepare 阶段
optimize 阶段
execute 阶段
我们所说的基于成本的优化主要集中在 optimize 阶段，对于单表查询来说，
我们主要关注 optimize 阶段的"rows_estimation"这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；
对于多表连接查询来说，我们更多需要关注"considered_execution_plans"这个过程，这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终
会选择成本最低的那种方案来作为最终的执行计划，也就是我们使用 EXPLAIN 语句所展现出的那种方案。
如果对使用 EXPLAIN 语句展示出的对某个查询的执行计划很不理解，就可以
尝试使用 optimizer trace 功能来详细了解每一种执行方案对应的成本。

5.3.4. 连接查询的成本

5.3.4.1. Condition filtering 介绍

连接查询至少是要有两个表的，课程的讲述中可能使用 order_exp 表的派生表 s1、s2 和 order_exp2。
我们前边说过，MySQL 中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询
成本由下边两个部分构成：

单次查询驱动表的成本
多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集
中有多少条记录）
对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）。
很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值，有的时候扇出值的计算是很容易的，比如下边这两个查询：

查询一：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2;

假设使用 s1 表作为驱动表，很显然对驱动表的单表查询只能使用全表扫描的方式执行，驱动表的扇出值也很明确，那就是驱动表中有多少记录，扇出值就是多少。统计数据中s1表的记录行数是10573，也就是说优化器就直接会把10573 当作在 s1 表的扇出值。

查询二：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2
WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<=
'2021-03-22 18:35:09';

仍然假设 s1 表是驱动表的话，很显然对驱动表的单表查询可以使用
idx_expire_time 索引执行查询。此时范围区间( ‘2021-03-22 18:28:28’, ‘2021-03-2218:35:09’)中有多少条记录，那么扇出值就是多少。
但是有的时候扇出值的计算就变得很棘手，比方说下边几个查询：

查询三：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE
s1.order_note > 'xyz';

本查询和查询一类似，只不过对于驱动表 s1 多了一个 order_note > 'xyz’的搜索条件。查询优化器又不会真正的去执行查询，所以它只能猜这 10573 记录里有多少条记录满足 order_note > 'xyz’条件。

查询四：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE
s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09' AND s1.order_note > 'xyz';

本查询和查询二类似，只不过对于驱动表 s1 也多了一个 order_note > ‘xyz’ 的搜索条件。不过因为本查询可以使用 idx_expire_time 索引，所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合 order_note > 'xyz’条件，也就是只需要猜在 39 条记录中有多少符合 order_note > 'xyz’条件。
在这里插入图片描述

查询五：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09' AND s1.order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND s1.order_note >
'xyz';

本查询和查询四类似，不过在驱动表 s1 选取 idx_expire_time 索引执行查询后，优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个条件：

order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S')
order_note > 'xyz'

也就是优化器需要猜在 39 条记录中有多少符合上述两个条件的。
说了这么多，其实就是想表达在这两种情况下计算驱动表扇出值时需要靠猜：
如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条。
如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满
足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

MySQL 把这个猜的过程称之为 condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是 MySQL 单纯的瞎猜，整个评估过程非常复杂，所以我们不去细讲。
在 MySQL 5.7 之前的版本中，查询优化器在计算驱动表扇出时，如果是使用全表扫描的话，就直接使用表中记录的数量作为扇出值，如果使用索引的话，就直接使用满足范围条件的索引记录条数作为扇出值。
在 MySQL 5.7 中，MySQL 引入了这个 condition filtering 的功能，就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确。我们所说的纯粹瞎猜其实是很不严谨的，MySQL 称之为启发式规则。

5.3.4.2. 两表连接的成本分析

连接查询的成本计算公式是这样的：
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要分别为驱动表和被驱动表选择成本最低的访问方法。
可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：
不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。然后分别为驱动表和被驱动表选择成本最低的访问方法。
很显然，计算内连接查询成本的方式更麻烦一些，下边我们就以内连接为例来看看如何计算出最优的连接查询方案。当然在某些情况下，左（外）连接和右（外）连接查询在某些特殊情况下可以被优化为内连接查询。
我们来看看内连接，比如对于下边这个查询来说：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 ON s1.order_no=
s2.order_note WHERE s1.expire_time> '2021-03-22 18:28:28' AND
s1.expire_time<= '2021-03-22 18:35:09' AND s2.expire_time> '2021-03-22 18:35:09' AND s2.expire_time<= '2021-03-22 18:35:59';

可以选择的连接顺序有两种：
s1 连接 s2，也就是 s1 作为驱动表，s2 作为被驱动表。
s2 连接 s1，也就是 s2 作为驱动表，s1 作为被驱动表。

查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。我们定性的分析一下，不像分析单表查询那样定量的分析了：
使用 s1 作为驱动表的情况
分析对于驱动表的成本最低的执行方案，首先看一下涉及 s1 表单表的搜索条件有哪些：

s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09'

所以这个查询可能使用到 idx_expire_time 索引，从全表扫描和使用 idx_expire_time 这两个方案中选出成本最低的那个，很显然使用 idx_expire_time 执行查询的成本更低些。
然后分析对于被驱动表的成本最低的执行方案，此时涉及被驱动表 s2 的搜索条件就是：
1、s2.order_note = 常数（这是因为对驱动表 s1 结果集中的每一条记录，都需要进行一次被驱动表 s2 的访问，此时那些涉及两表的条件现在相当于只涉及
被驱动表 s2 了。）
2、s2.expire_time> ‘2021-03-22 18:35:09’ AND s2.expire_time<= ‘2021-03-22 18:35:59’ 很显然，第一个条件由于 order_note 没有用到索引，所以并没有什么用，
此时访问 s2 表时可用的方案也是全表扫描和使用 idx_expire_time 两种，假设使用 idx_expire_time 的成本更小。
所以此时使用 s1 作为驱动表时的总成本就是（暂时不考虑使用 join buffer 对成本的影响）：
使用 idx_expire_time 访问 s1 的成本 + s1 的扇出 × 使用 idx_expire_time 访问 s2 的成本。
使用 s2 作为驱动表的情况
分析对于驱动表的成本最低的执行方案
首先看一下涉及 s2 表单表的搜索条件有哪些：
s2.expire_time> ‘2021-03-22 18:35:09’ AND s2.expire_time<= ‘2021-03-22 18:35:59’ 所以这个查询可能使用到 idx_expire_time 索引，从全表扫描和使用 idx_expire_time 这两个方案中选出成本最低的那个，假设使用 idx_expire_time 执行查询的成本更低些。
然后分析对于被驱动表的成本最低的执行方案
此时涉及被驱动表 s1 的搜索条件就是：
1、s1.order_no = 常数
2、s1.expire_time> ‘2021-03-22 18:28:28’ AND s1.expire_time<= ‘2021-03-22 18:35:09’ 这时就很有趣了，使用 idx_order_no 可以进行 ref 方式的访问，使用 idx_expire_time 可以使用 range 方式的访问。
那么优化器需要从全表扫描、使用 idx_order_no、使用 idx_expire_time 这几个方案里选出一个成本最低的方案。
这里有个问题，因为 idx_expire_time 的范围区间是确定的，怎么计算使用 idx_expire_time 的成本我们上边已经说过了，可是在没有真正执行查询前，
s1.order_no = 常数中的常数值我们是不知道的，怎么衡量使用 idx_order_no 执行查询的成本呢？其实很简单，直接使用我们前面说过的索引统计数据就好了
（就是索引列平均一个值重复多少次）。一般情况下，ref 的访问方式要比 range 成本更低，这里假设使用 idx_order_no 进行对 s1 的访问。
所以此时使用 s2 作为驱动表时的总成本就是：
使用 idx_expire_time 访问 s2 的成本 + s2 的扇出 × 使用 idx_order_no 访问
s1 的成本
最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺
序去真正的执行查询。从上边的计算过程也可以看出来，一般来讲，连接查询成
本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本，所以我们的优化重点其实是下边这两个部分：

尽量减少驱动表的扇出
对被驱动表的访问成本尽量低
这一点对于我们实际书写连接查询语句时十分有用，我们需要尽量在被驱动表的连接列上建立索引，这样就可以使用 ref 访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。

5.3.4.3. EXPLAIN 输出连接成本

连接查询在输出成本时和单表查询稍有不同，如下：

explain format=json SELECT * FROM order_exp AS s1 INNER JOIN order_exp2
AS s2 ON s1.order_no= s2.order_note WHERE s1.expire_time> '2021-03-22
18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09' AND s2.expire_time>
'2021-03-22 18:35:09' AND s2.expire_time<= '2021-03-22 18:35:59'\G
*************************** 1. row ***************************
EXPLAIN: { "query_block": { "select_id": 1,# 整个查询语句只有 1 个 SELECT 关键字，该关键字对应的 id 号为 1 "cost_info": { "query_cost": "840.51" # 整个查询的执行成本
},"nested_loop": [ # 几个表之间采用嵌套循环连接算法执行
{ "table": { "table_name": "s2", # s2 表是驱动表
"access_type": "range", # 访问方法为 range
"possible_keys": [ "idx_expire_time"
],"key": "idx_expire_time", "used_key_parts": [ "expire_time"
],"key_length": "5", "rows_examined_per_scan": 321, # 查询 s2 表大致需要扫描 321 条记录
"rows_produced_per_join": 321, # 驱动表 s2 的扇出是 321 "filtered": "100.00", # condition filtering 代表的百分比
"index_condition": "((`mysqladv`.`s2`.`expire_time` > '2021-03-22
18:35:09') and (`mysqladv`.`s2`.`expire_time` <= '2021-03-22 18:35:59'))", "cost_info": { "read_cost": "386.21", "eval_cost": "64.20", "prefix_cost": "450.41", # 查询 s1 表总共的成本，read_cost + eval_cost "data_read_per_join": "152K" # 读取的数据量
},"used_columns": [ "id", "order_no", "order_note", "insert_time", "expire_duration", "expire_time", "order_status"
]
}
},{ "table": { "table_name": "s1", # s1 表是被驱动表
"access_type": "ref", "possible_keys": [ "idx_order_no",
"idx_expire_time"
],"key": "idx_order_no", "used_key_parts": [ "order_no"
],"key_length": "152", "ref": [ "mysqladv.s2.order_note"
],"rows_examined_per_scan": 1, # 查询一次 s1 表大致需要扫描 1 条记录
"rows_produced_per_join": 16, # 被驱动表 s2 的扇出是 16（由于没有多余的
表进行连接，所以这个值无用）
"filtered": "4.94", # condition filtering 代表的百分比
"index_condition": "(`mysqladv`.`s1`.`order_no` =
`mysqladv`.`s2`.`order_note`)", "cost_info": { "read_cost": "325.08", "eval_cost": "3.21", "prefix_cost": "840.51", # 单次查询 s2、多次查询 s1 表总共的成本
"data_read_per_join": "7K"
},"used_columns": [ "id", "order_no", "order_note", "insert_time", "expire_duration", "expire_time", "order_status"
],"attached_condition": "((`mysqladv`.`s1`.`expire_time` >
'2021-03-22 18:28:28') and (`mysqladv`.`s1`.`expire_time` <= '2021-03-22
18:35:09'))"
		}
	}
	]
	}
}
1 row in set, 1 warning (0.00 sec)

我们使用#后边跟随注释的形式为大家解释了EXPLAIN FORMAT=JSON语句的输出内容，s2 表的"cost_info"中 prefix_cost 就是单独查询 s2 表的成本。
对于 s1 表的"cost_info"中，由于 s1 表是被驱动表，所以可能被读取多次，这里的 read_cost 和 eval_cost 是访问多次 s2 表后累加起来的值，而 s1 表中的prefix_cost 的值代表的是整个连接查询预计的成本。
看完了上面的执行计划的输出，可能大家有疑惑，驱动表 S2 的查询成本为450.41，总查询成本为 840.51，也就是说对被驱动表 S1 的查询成本也就是 390左右，看起来用 S1 做驱动表好像更省一点。真的这样吗？我们把 SQL 语句改造一下，将 INNER JOIN 替换为 STRAIGHT_JOIN：

explain format=json SELECT * FROM order_exp AS s1 STRAIGHT_JOIN
order_exp2 AS s2 ON s1.order_no= s2.order_note WHERE s1.expire_time>
'2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09' AND
s2.expire_time> '2021-03-22 18:35:09' AND s2.expire_time<= '2021-03-22
18:35:59'\G

大家可以自行看看所需要的成本是多少，并自行解释一下原因

5.3.4.4. 多表连接的成本分析

首先要考虑一下多表连接时可能产生出多少种连接顺序：
对于两表连接，比如表 A 和表 B 连接只有 AB、BA 这两种连接顺序。其实相当于 2 × 1 = 2 种连接顺序。
对于三表连接，比如表 A、表 B、表 C 进行连接
有 ABC、ACB、BAC、BCA、CAB、CBA 这么 6 种连接顺序。其实相当于 3 × 2 × 1 = 6 种连接顺序。
对于四表连接的话，则会有 4 × 3 × 2 × 1 = 24 种连接顺序。
对于 n 表连接的话，则有 n × (n-1) × (n-2) × ··· × 1 种连接顺序，
就是 n 的阶乘种连接顺序，也就是 n!。
有 n 个表进行连接，MySQL 查询优化器要每一种连接顺序的成本都计算一
遍么？那可是 n!种连接顺序呀。其实真的是要都算一遍，不过 MySQL 用了很多办法减少计算非常多种连接顺序的成本的方法：
提前结束某种顺序的成本评估
MySQL 在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。比方说 A、B、C 三个表进行连接，已经得到连接顺序 ABC 是当前的最小连接成本，比方说 10.0，在计算连接顺序 BCA 时，发现 B 和 C 的连接成本就已经大于 10.0时，就不再继续往后分析 BCA 这个连接顺序的成本了。
系统变量 optimizer_search_depth
为了防止无穷无尽的分析各种连接顺序的成本，MySQL 提出了optimizer_search_depth 系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对optimizer_search_depth 值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到不是很好的执行计划，但可以省掉很多分析连接成本的时间。
根据某些规则压根儿就不考虑某些连接顺序
即使是有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很长，所以 MySQL 干脆提出了一些所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量optimizer_prune_level 来控制到底是不是用这些启发式规则。

5.3.5. 调节成本常数

我们前边已经介绍了两个成本常数：
读取一个页面花费的成本默认是 1.0
检测一条记录是否符合搜索条件的成本默认是 0.2
其实除了这两个成本常数，MySQL 还支持很多，它们被存储到了 MySQL 数据库的两个表中：

SHOW TABLES FROM mysql LIKE '%cost%';

在这里插入图片描述
因为一条语句的执行其实是分为两层的：server 层、存储引擎层。
在 server 层进行连接管理、查询缓存、语法解析、查询优化等操作，在存储引擎层执行具体的数据存取操作。也就是说一条语句在 server 层中执行的成本是和它操作的表使用的存储引擎是没关系的，所以关于这些操作对应的成本常数就存储在了 server_cost 表中，而依赖于存储引擎的一些操作对应的成本常数就存储在了 engine_cost 表中。

5.3.5.1. mysql.server_cost 表

server_cost 表中在 server 层进行的一些操作对应的成本常数，具体内容如下：

SELECT * FROM mysql.server_cost;

在这里插入图片描述
我们先看一下 server_cost 各个列都分别是什么意思：

cost_name
表示成本常数的名称。
cost_value
表示成本常数对应的值。如果该列的值为 NULL 的话，意味着对应的成本常数会采用默认值。
last_update
表示最后更新记录的时间。
comment
注释。
从 server_cost 中的内容可以看出来，目前在 server 层的一些操作对应的成
本常数有以下几种：
disk_temptable_create_cost 默认值 40.0 创建基于磁盘的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
disk_temptable_row_cost 默认值 1.0 向基于磁盘的临时表写入或读取一
条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
key_compare_cost 0.1 两条记录做比较操作的成本，多用在排序操作上，如果增大这个值的话会提升 filesort 的成本，让优化器可能更倾向于使用索引完成排序而不是 filesort。
memory_temptable_create_cost 默认值 2.0 创建基于内存的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
memory_temptable_row_cost 默认值 0.2 向基于内存的临时表写入或读
取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
row_evaluate_cost 默认值 0.2 这个就是我们之前一直使用的检测一条记
录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描。
MySQL 在执行诸如 DISTINCT 查询、分组查询、Union 查询以及某些特殊条件
下的排序查询都可能在内部先创建一个临时表，使用这个临时表来辅助完成查询
（比如对于 DISTINCT 查询可以建一个带有 UNIQUE 索引的临时表，直接把需要去重的记录插入到这个临时表中，插入完成之后的记录就是结果集了）。在数据量大的情况下可能创建基于磁盘的临时表，也就是为该临时表使用MyISAM、InnoDB等存储引擎，在数据量不大时可能创建基于内存的临时表，也就是使用 Memory存储引擎。大家可以看到，创建临时表和对这个临时表进行写入和读取的操作代价还是很高的就行了。
这些成本常数在 server_cost 中的初始值都是 NULL，意味着优化器会使用它们的默认值来计算某个操作的成本，如果我们想修改某个成本常数的值的话，需要做两个步骤：
对我们感兴趣的成本常数做 update 更新操作，然后使用下边语句即可：
FLUSH OPTIMIZER_COSTS;
当然，在你修改完某个成本常数后想把它们再改回默认值的话，可以直接把cost_value 的值设置为 NULL，再使用 FLUSH OPTIMIZER_COSTS 语句让系统重新加载。

5.3.5.2. mysql.engine_cost 表

engine_cost 表表中在存储引擎层进行的一些操作对应的成本常数，具体内容如下：

SELECT * FROM mysql.engine_cost;

在这里插入图片描述
与 server_cost 相比，engine_cost 多了两个列：

engine_name 列指成本常数适用的存储引擎名称。如果该值为 default，意味着对应的成本
常数适用于所有的存储引擎。
device_type 列指存储引擎使用的设备类型，这主要是为了区分常规的机械硬盘和固态硬盘，不过在 MySQL 5.7.X 这个版本中并没有对机械硬盘的成本和固态硬盘的成本作区分，所以该值默认是 0。
我们从 engine_cost 表中的内容可以看出来，目前支持的存储引擎成本常数只有两个：
io_block_read_cost 默认值 1.0 从磁盘上读取一个块对应的成本。请注意我使用的是块，而不是页这个词。对于 InnoDB 存储引擎来说，一个页就是一个块，不过对于 MyISAM 存储引擎来说，默认是以 4096 字节作为一个块的。增大这个值会加重 I/O 成本，可能让优化器更倾向于选择使用索引执行查询而不是执行全表扫描。
memory_block_read_cost 默认值 1.0 与上一个参数类似，只不过衡量的是从内存中读取一个块对应的成本。
怎么从内存中和从磁盘上读取一个块的默认成本是一样的？这主要是因为
在 MySQL 目前的实现中，并不能准确预测某个查询需要访问的块中有哪些块已经加载到内存中，有哪些块还停留在磁盘上，所以 MySQL 简单的认为不管这个块有没有加载到内存中，使用的成本都是 1.0。
与更新 server_cost 表中的记录一样，我们也可以通过更新 engine_cost 表中的记录来更改关于存储引擎的成本常数，做法一样。