2-3-4-2、MySQL的查询成本解析

简介

MySQL 执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询，其实在 MySQL 中一条查询语句的执行成本是由I/O成本和CPU成本组成的

I/O成本

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

CPU 成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本
对于 InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL 规定读取一个页面花费的成本默认是 1.0（MySQL 8 对于这个参数进行了优化，如果在内存中就可以读到，则默认花费的成本为 0.25），读取以及检测一条记录是否符合搜索条件的成本默认是 0.1(MySQL 8 之前为 0.2)。1.0、0.1 这些数字称之为成本常数，当然还有其他的成本常数
注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是 0.1

单表查询的成本

示例表

DROP TABLE IF EXISTS `test_cost_user`;
CREATE TABLE `test_cost_user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_no` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户编号',
  `user_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `user_sex` tinyint unsigned NOT NULL COMMENT '用户性别',
  `user_mobile` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户手机号',
  `user_state` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '用户状态',
  `check_time` datetime DEFAULT NULL COMMENT '审核时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `delete_flag` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_user_no` (`user_no`) USING BTREE COMMENT '用户编号索引',
  KEY `idx_user_name` (`user_name`) USING BTREE COMMENT '用户名称索引',
  KEY `idx_check_time` (`check_time`) USING BTREE COMMENT '审核时间索引',
  KEY `idx_create_time` (`create_time`) USING BTREE COMMENT '创建时间索引',
  KEY `idx_mobile_state` (`user_mobile`,`user_state`) USING BTREE COMMENT '手机号有效用户索引'
) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
drop procedure if exists insert_user;
delimiter;
create procedure insert_user() 
    begin 
        declare i int; 
        set i=1; 
        while(i<=10000)do 
            INSERT INTO `test`.`test_cost_user` (`user_no`, `user_name`, `user_sex`, `user_mobile`, `user_state`, `check_time`) VALUES (concat('U', i), concat(substr(replace(UUID(), '-', ''), 20), i), i % 2, cast('13333333333' as UNSIGNED) + i, i % 2, if(i % 2 = 1, now(), null));
            set i=i+1; 
        end while; 
    end;; 
delimiter;
call insert_user(); 

基于实战的成本计算

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

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

示例sql

select * from test_cost_user where user_no in ('U100', 'U109', 'U140') and create_time >= '2022-11-08 05:36:50' and create_time <= '2022-11-08 05:36:59' and check_time <= create_time and user_name like '%2ac120%' and user_state = 1;

根据上面的单表执行计划步骤进行分析上面的sql

根据搜索条件，找出所有可能使用的索引

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

• user_no in (‘U100’, ‘U109’, ‘U140’)，可以使用二级索引 idx_user_no
• create_time >= ‘2022-11-08 05:36:50’ and create_time <= ‘2022-11-08 05:36:59’，可以使用二级索引 idx_create_time
• check_time <= create_time，由于没有和常数进行对比，因此不能使用索引
• user_name like ‘%2ac120%’，由于 like 关键字使用了前后模糊查询，因此不能使用索引
• user_state = 1，由于 user_state 所在的索引是复合索引，而当前字段并不是索引第一个字段，因此不满足最左前缀原则，因此不能使用索引

综上，当前 sql 可能使用到的索引有 idx_user_no 和 idx_create_time
通过执行计划也可得到验证：

计算全表扫描的代价

对于 InnoDB 存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=I/O 成本+CPU 成本，所以计算全表扫描的代价需要两个信息：

• 聚簇索引占用的页面数
• 该表中的记录数

MySQL 为每个表维护了一系列的统计信息，关于这些统计信息是如何收集起来的，在之后会有专门的文章来进行说明，目前直接获取相应的统计值用于成本计算即可
MySQL 提供了 SHOW TABLE STATUS 语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应的 LIKE 语句就好了，例如：

show table status like 'test_cost_user';

对着这些数值，目前只需要关注 Rows 和 Data_length 这两个即可

Rows

本选项表示表中的记录条数。对于使用 MyISAM 存储引擎的表来说，该值是准确的，对于使用 InnoDB 存储引擎的表来说，该值是一个估计值。从查询结果也可以看出来，由于 test_cost_user 表是使用 InnoDB 存储引擎的，所以虽然实际上表中有 10000 条记录，但是 SHOW TABLE STATUS 显示的 Rows 值只有 9927 条记录

Data_length

本选项表示表占用的存储空间字节数。使用 MyISAM 存储引擎的表来说，该值就是数据文件的大小，对于使用 InnoDB 存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：

Data_length = 聚簇索引的页面数量 x 每个页面的大小

test_cost_user 使用默认 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 成本

9927 x 0.1 + 1.0 = 993.7

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

总成本

98.1 + 993.7 = 1091.8

综上所述，对于 test_cost_user 的全表扫描所需的总成本就是 1091.8

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

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

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

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

idx_create_time 对应的搜索条件是：create_time >= ‘2022-11-08 05:36:50’ and create_time <= ‘2022-11-08 05:36:59’ ，也就是说对应的范围区间就是： [‘2022-11-08 05:36:50’ , ‘2022-11-08 05:36:59’]
使用 idx_create_time 搜索会使用用二级索引 + 回表方式的查询，MySQL 计算这种查询的成本依赖两个方面的数据：

• 范围区间数量
• 需要回表的记录数
  

范围区间数量

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

1 x 1.0 = 1.0

需要回表的记录数

优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算 idx_create_time 在 [‘2022-11-08 05:36:50’ , ‘2022-11-08 05:36:59’] 这个范围区间中包含多少二级索引记录，计算过程是这样的：

• 先根据 create_time >= ‘2022-11-08 05:36:50’ 这个条件访问一下 idx_create_time 对应的 B+树索引，找到满足 create_time >= ‘2022-11-08 05:36:50’ 这个条件的第一条记录，这条记录称之为区间最左记录。在 B+ 树中定位一条记录的过程是很快的，是常数级别的，所以这个过程的性能消耗是可以忽略不计的
• 然后再根据 create_time <= ‘2022-11-08 05:36:59’ 这个条件继续从 idx_create_time 对应的 B+树索引中找出最后一条满足这个条件的记录，这条记录称之为区间最右记录，这个过程的性能消耗也可以忽略不计的
• 如果区间最左记录和区间最右记录相隔不太远（在 MySQL 5.7 这个版本里，只要相隔不大于 10 个页面即可），那就可以精确统计出满足 create_time >= ‘2022-11-08 05:36:50’ and create_time <= ‘2022-11-08 05:36:59’ 条件的二级索引记录条数。否则只沿着区间最左记录向右读 10 个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了

假设区间最左记录在页 b 中，区间最右记录在页 c 中，那么想计算区间最左记录和区间最右记录之间的页面数量就相当于计算页b和页 c 之间有多少页面，而它们父节点中记录的每一条目录项记录都对应一个数据页，所以计算页 b 和页 c 之间有多少页面就相当于计算它们父节点（也就是页 a）中对应的目录项记录之间隔着几条记录。在一个页面中统计两条记录之间有几条记录的成本就很小了
如果页 b 和页 c 之间的页面实在太多，以至于页 b 和页 c 对应的目录项记录都不在一个父页面中，那就继续递归，一个 B+树有 4 层高已经很罕见了，因此这个统计过程也不是很耗费性能
MySQL 根据上述算法测得 idx_create_time 在区间 [‘2022-11-08 05:36:50’ , ‘2022-11-08 05:36:59’] 之间大约有 1001 条记录，如下通过执行计划可得到结果：

explain select * from test_cost_user where create_time >= '2022-11-08 05:36:50' and create_time <= '2022-11-08 05:36:59';

读取这 1001 条二级索引记录需要付出的 CPU 成本就是：

1001 x 0.1 + 0.01 = 100.11

其中 1001 是需要读取的二级索引记录条数，0.1 是读取一条记录成本常数，0.01 是微调数
在通过二级索引获取到记录之后，还需要做两件事儿：

• 根据这些记录里的主键值到聚簇索引中做回表操作
• 回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立
  

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

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

1001 x 1.0 = 1001.0

其中 1001 是预计的二级索引记录数，1.0 是一个页面的 I/O 成本常数

回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立

回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的数据记录，然后再检测除了 create_time >= ‘2022-11-08 05:36:50’ and create_time <= ‘2022-11-08 05:36:59’ 这个搜索条件以外的搜索条件是否成立
通过范围区间获取到二级索引记录共 1001 条，也就对应着聚簇索引 中 1001 条完整的数据记录，读取并检测这些完整的数据记录是否符合其余的搜索条件的 CPU 成本如下：

1001 x 0.1 = 100.1

其中 1001 是待检测记录的条数，0.1 是检测一条记录是否符合给定的搜索条件的成本常数

成本汇总

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

I/O 成本

1.0 + 1001 x 1.0 = 1002.0 (范围区间的数量 + 预估的二级索引记录条数)

CPU 成本

1001 x 0.1 + 0.01 + 1001 x 0.1 = 200.21 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）

综上所述，使用 idx_create_time 执行查询的总成本就是：

1002.0 + 200.21 = 1202.21

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

idx_user_no 对应的搜索条件是：user_no in (‘U100’, ‘U109’, ‘U140’) ，也就相当于 3 个单点区间

范围区间数量

3 x 1.0 = 3.0

需要回表的记录数

由于使用 idx_user_no 时有 3 个单点区间，所以每个单点区间都需要查找 一遍对应的二级索引记录数，三个单点区间总共需要回表的记录数是 3

explain select * from test_cost_user where user_no in ('U100', 'U109', 'U140');

读取这 3 条二级索引记录需要付出的 CPU 成本就是：

3 x 0.1 + 0.01 = 0.31

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

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

预计有 3 条二级索引记录需要进行回表操作，所以回表操作带来的 I/O 成本就是：

3 x 1.0 = 3.0

其中 3 是预计的二级索引记录数，1.0 是一个页面的 I/O 成本常数

回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立

通过范围区间获取到二级索引记录共 3 条，也就对应着聚簇索引 中 3 条完整的数据记录，读取并检测这些完整的数据记录是否符合其余的搜索条件的 CPU 成本如下：

3 x 0.1 = 0.6

其中 3 是待检测记录的条数，0.2 是检测一条记录是否符合给定的搜索条件的成本常数

成本汇总

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

I/O 成本

3.0 + 3 x 1.0 = 6.0 (范围区间的数量 + 预估的二级索引记录条数)

CPU 成本

3 x 0.1 + 0.01 + 3 x 0.1 = 0.61 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）

综上所述，使用 idx_user_no 执行查询的总成本就是：

6.0 + 0.61 = 6.61

对比各种执行方案的代价，找出成本最低的那一个

全表扫描：1091.8
使用 idx_create_time 执行查询的总成本：1202.21
使用 idx_user_no 执行查询的总成本：6.61
很显然，使用 idx_user_no 的成本最低，所以当然选择 idx_user_no 来执行查询，如下：

explain select * from test_cost_user where user_no in ('U100', 'U109', 'U140') and create_time >= '2022-11-08 05:36:50' and create_time <= '2022-11-08 05:36:59' and check_time <= create_time and user_name like '%2ac120%' and user_state = 1;

注意：
1、MySQL 的源码中对成本的计算实际要更复杂，但是基本思想和算法基本如上
2、在 MySQL 的实际计算中，在和全文扫描比较成本时，使用索引的成本会去除读取并检测回表后聚簇索引记录的成本，也就是说，通过 MySQL 看到的成本将会是：idx_create_time 为 1102.11(1302.31-200.2)，idx_user_no 为 6.31(6.91-0.6)。但是 MySQL 比较完成本后，会再计算一次使用索引的成本，此时就会加上去除读取并检测回表后聚簇索引记录的成本，也就是计算出来的值

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

index dive精确统计

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

select * from test_cost_user where user_no in('U1', 'U2', 'U10', 'U23', 'U43', 'U35', 'U76', ..., 'U870');

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

估算统计

MySQL 对于区间的多寡，提供了一个系统变量 eq_range_index_dive_limit 来区分，MySQL 5.7.21(8.0.27) 中这个系统变量的默认值：

show variables like 'eq_range_index_dive_limit';

也就是说如果的 IN 语句中的参数个数小于 200 个的话，将使用 index dive 的方式计算各个单点区间对应的记录条数，如果大于或等于 200 个的话，可就不能使用 index dive 了，要使用所谓的索引统计数据来进行估算

索引详情

MySQL 会为表中的每一个索引维护一份统计数据，查看某个表中索引的统计数据可以使用 SHOW INDEX FROM 表名 的语法，比如查看一下 test_cost_user 的各个索引的统计数据，如下：

show index from test_cost_user;

返回参数解释

返回字段	说明
Table	索引所属表的名称
Non_unique	索引列的值是否不是唯一的，聚簇索引和唯一二级索引的该列值为 0，普通二级索引该列值为 1
Key_name	索引的名称
Seq_in_index	索引列在索引中的位置，从 1 开始计数。比如对于联合索引 idx_mobile_state，来说，user_mobile, user_state对应的位置分别是 1、2
Column_name	索引列的名称
Collation	索引列中的值是按照何种排序方式存放的，值为 A 时代表升序存放，为 NULL 时代表降序存放
Cardinality	索引列中不重复值的数量
Sub_part	对于存储字符串或者字节串的列来说，有时候只想对这些串的前 n 个字符或字节建立索引，这个属性表示的就是那个 n 值。如果对完整的列建立索引的话，该属性的值就是 NULL
Packed	索引列如何被压缩，NULL 值表示未被压缩
Null	该索引列是否允许存储 NULL 值
Index_type	使用索引的类型，最常见的就是 BTREE，其实也就是 B+树索引
Comment	索引列注释信息
Index_comment	索引注释信息
Visible	索引是否可见，用于删除索引前的隐藏索引（MySQL8新特性）
Expression	MySQL 8.0.13及更高版本支持功能的关键部分,默认为Null

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

以 test_cost_user 表的 idx_user_no 索引为例，它的 Rows 值是 9927，它对应 的 Cardinality 值是 9870，所以可以计算 user_no 列平均单个值的重复次数就是：

9927 ÷ 9870 ≈ 1.005775075987842

此时假设上面的查询语句需要20000个 in 的参数：

select * from test_cost_user where user_no in('U1', 'U2', 'U10', 'U23', 'U43', 'U35', 'U76', ..., 'U870');

直接使用统计数据来估算这些参数需要单点区间对应的记录条数了，每个参数大约对应 1.005775075987842 条记录，所以总共需要回表的记录数就是：

20000 x 1.005775075987842 = 20115.50151975684

使用统计数据来计算单点区间对应的索引记录条数比 index dive 的方式简单，但是它的致命弱点就是：不精确！。使用统计数据算出来的查询成本与实际所需的成本可能相差非常大
注意：
在 MySQL 5.7.3 以及之前的版本中， eq_range_index_dive_limit 的默认值为 10，之后的版本默认值为 200。所以如果采用的是 5.7.3 以及之前的版本的话，很容易采用索引统计数据而不是 index dive 的方式来计算查询成本。当你的查询中使用到了 IN 查询，但是却实际没有用到索引，就应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的

基于成本深入EXPLAIN

EXPLAIN 输出成本

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

explain format=json select * from test_cost_user where user_no in ('U100', 'U109', 'U140') and create_time >= '2022-11-08 05:36:50' and create_time <= '2022-11-08 05:36:59' and check_time <= create_time and user_name like '%2ac120%' and user_state = 1;

执行结果如下：

{
  "query_block": {
    "select_id": 1,	//查询编号
    "cost_info": {
      "query_cost": "2.11"	//当前查询总成本
    },
    "table": {
      "table_name": "test_cost_user",
      "access_type": "range",
      "possible_keys": [
        "idx_user_no",
        "idx_create_time"
      ],
      "key": "idx_user_no",
      "used_key_parts": [
        "user_no"
      ],
      "key_length": "82",
      "rows_examined_per_scan": 3,
      "rows_produced_per_join": 0,
      "filtered": "1.67",
      "index_condition": "(`test`.`test_cost_user`.`user_no` in ('U100','U109','U140'))",
      "cost_info": {
        "read_cost": "2.10",	//I/O成本
        "eval_cost": "0.01",	//CPU成本
        "prefix_cost": "2.11",	//总成本
        "data_read_per_join": "12"	//与连接查询相关，这儿没用
      },
      "used_columns": [
        "id",
        "user_no",
        "user_name",
        "user_sex",
        "user_mobile",
        "user_state",
        "check_time",
        "create_time",
        "update_time",
        "delete_flag"
      ],
      "attached_condition": "((`test`.`test_cost_user`.`user_state` = 1) and (`test`.`test_cost_user`.`create_time` >= TIMESTAMP'2022-11-08 05:36:50') and (`test`.`test_cost_user`.`create_time` <= TIMESTAMP'2022-11-08 05:36:59') and (`test`.`test_cost_user`.`check_time` <= `test`.`test_cost_user`.`create_time`) and (`test`.`test_cost_user`.`user_name` like '%2ac120%'))"
    }
  }
}

上面对于 idx_user_no 的成本计算得到的结果是：

3.0 + 3 x 1.0 = 6.0 (范围区间的数量 + 预估的二级索引记录条数)	I/O
3 x 0.1 + 0.01 + 3 x 0.1 = 0.61 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）
6.0 + 0.91 = 6.61	总成本

但通过执行计划得到的结果是：2.11，这儿不是计算出了问题，而是mysql使用了索引条件下推，也就是说通过 idx_user_no 和 idx_create_time 两个索引得到的区间数量其实为 1，这个可以通过执行计划的 Filter 和 Extra 来验证，如下所示：

因此可以重新计算成本：

1.0 + 1 x 1.0 = 2.0 (范围区间的数量 + 预估的二级索引记录条数)	I/O
1 x 0.1 + 0.01 + 1 x 0.1 = 0.21 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）
2.0 + 0.21 - 0.1 = 2.11	（总成本 - 回表的成本）

mysql优化器是很复杂的，对于单表，简单优化还可以进行模拟计算，但要是涉及到太多的优化，如果不是对源码很熟，计算难免会有误差，但对整体结果的影响不会太大

成本单位的延伸（了解即可，没有官方说明）

对于成本的单位，官方没有提出，只知道它越小越好，对此偶然间发现了它和CPU的频率有关，有个比较有趣的公式：

当前sql的查询时间/1000 = 查询成本/CPU频率（换算为k）

例如我的CPU频率如下：

执行的sql为：

select a.*, b.*, c.* from test_cost_user a left join t1 b on a.id = b.id left join t2 c on a.id = c.id;

执行计划为：

执行成本为：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7920.25"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "a",
          "access_type": "ALL",
          "rows_examined_per_scan": 9870,
          "rows_produced_per_join": 9870,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "24.25",
            "eval_cost": "987.00",
            "prefix_cost": "1011.25",
            "data_read_per_join": "2M"
          },
          "used_columns": [
            "id",
            "user_no",
            "user_name",
            "user_sex",
            "user_mobile",
            "user_state",
            "check_time",
            "create_time",
            "update_time",
            "delete_flag"
          ]
        }
      },
      {
        "table": {
          "table_name": "b",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "4",
          "ref": [
            "test.a.id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 9870,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2467.50",
            "eval_cost": "987.00",
            "prefix_cost": "4465.75",
            "data_read_per_join": "154K"
          },
          "used_columns": [
            "id",
            "a",
            "b"
          ],
          "attached_condition": "<if>(is_not_null_compl(b), (`test`.`a`.`id` = `test`.`b`.`id`), true)"
        }
      },
      {
        "table": {
          "table_name": "c",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "4",
          "ref": [
            "test.a.id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 9870,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2467.50",
            "eval_cost": "987.00",
            "prefix_cost": "7920.25",
            "data_read_per_join": "154K"
          },
          "used_columns": [
            "id",
            "a",
            "b"
          ],
          "attached_condition": "<if>(is_not_null_compl(c), (`test`.`a`.`id` = `test`.`c`.`id`), true)"
        }
      }
    ]
  }
}

预估执行时间为：

7920.25 / (2.4 * 1000 * 1000 * 1000) * 1000 = 0.003300104166666667

实际执行时间为：

Optimizer Trace

基本使用

对于 MySQL5.6 之前的版本来说，只能通过 EXPLAIN 语句查看到最后优化器决定使用的执行计划，却无法知道它做这个决策的详细依据
在 MySQL 5.6 以及之后的版本中，MySQL 提出了一个 optimizer trace 的功能，这个功能可以让方便的查看优化器生成执行计划的整个过程，这个功能的开启与关闭由系统变量 optimizer_trace 决定：

SHOW VARIABLES LIKE 'optimizer_trace';

enabled 值为 off，表明这个功能默认是关闭的。one_line 的值是控制输出格式的，如果为 on 那么所有输出都将在一行中展示，就保持其默认值为 off
开启 optimizer_trace ：

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";

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

实战分析

在这依然使用上面的sql来举例：

select * from test_cost_user where user_no in ('U100', 'U109', 'U140') and create_time >= '2022-11-08 05:36:50' and create_time <= '2022-11-08 05:36:59' and check_time <= create_time and user_name like '%2ac120%' and user_state = 1;

它的执行计划如下：

可以看到当前 sql 可能会用到两个索引，分别是 idx_user_no 和 idx_create_time，但最后选择了 idx_user_no，此时通过 otpimzer trace 功能来查看优化器的具体工作过程：

1. 开启 optimizer trace 功能
2. 执行上面的 sql
3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程
4. 当你停止查看语句的优化过程时，把optimizer trace功能关闭

这儿着重分析第 3 步骤

从OPTIMIZER_TRACE表中查看上一个查询的优化过程

SET optimizer_trace="enabled=on";
select * from test_cost_user where user_no in ('U100', 'U109', 'U140') and create_time >= '2022-11-08 05:36:50' and create_time <= '2022-11-08 05:36:59' and check_time <= create_time and user_name like '%2ac120%' and user_state = 1;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

这儿把四个 sql 都放到一块执行，是因为我的测试实在navicat上进行的，如果一条一条执行 sql 则会有查不到的情况但如果使用命令行没有这个问题
此处着重看表 OPTIMIZER_TRACE 返回的结果

详细分析 TRACE 信息

从上图可以看出，TRACE结果是完整的，因为 MISSING_BYTE_BEYOND_MAX_MEM_SIZE 为 0，接着分析 TRACE 中都有什么：
因为输出的文本信息太多，因此仅仅对于重点在其后进行了注释（#）标注

{
  "steps": [
    {
      "join_preparation": {			# prepare阶段
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          {
            "expanded_query": "/* select#1 */ select `test_cost_user`.`id` AS `id`,`test_cost_user`.`user_no` AS `user_no`,`test_cost_user`.`user_name` AS `user_name`,`test_cost_user`.`user_sex` AS `user_sex`,`test_cost_user`.`user_mobile` AS `user_mobile`,`test_cost_user`.`user_state` AS `user_state`,`test_cost_user`.`check_time` AS `check_time`,`test_cost_user`.`create_time` AS `create_time`,`test_cost_user`.`update_time` AS `update_time`,`test_cost_user`.`delete_flag` AS `delete_flag` from `test_cost_user` where ((`test_cost_user`.`user_no` in ('U100','U109','U140')) and (`test_cost_user`.`create_time` >= '2022-11-08 05:36:50') and (`test_cost_user`.`create_time` <= '2022-11-08 05:36:59') and (`test_cost_user`.`check_time` <= `test_cost_user`.`create_time`) and (`test_cost_user`.`user_name` like '%2ac120%') and (`test_cost_user`.`user_state` = 1))"
          }
        ]
      }
    },
    {
      "join_optimization": {		# optimize阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": {		# 处理搜索条件
              "condition": "WHERE",
              "original_condition": "((`test_cost_user`.`user_no` in ('U100','U109','U140')) and (`test_cost_user`.`create_time` >= '2022-11-08 05:36:50') and (`test_cost_user`.`create_time` <= '2022-11-08 05:36:59') and (`test_cost_user`.`check_time` <= `test_cost_user`.`create_time`) and (`test_cost_user`.`user_name` like '%2ac120%') and (`test_cost_user`.`user_state` = 1))",		# 原始搜索条件
              "steps": [
                {
                  "transformation": "equality_propagation",		# 等值传递转换
                  "resulting_condition": "((`test_cost_user`.`user_no` in ('U100','U109','U140')) and (`test_cost_user`.`create_time` >= '2022-11-08 05:36:50') and (`test_cost_user`.`create_time` <= '2022-11-08 05:36:59') and (`test_cost_user`.`check_time` <= `test_cost_user`.`create_time`) and (`test_cost_user`.`user_name` like '%2ac120%') and multiple equal(1, `test_cost_user`.`user_state`))"
                },
                {
                  "transformation": "constant_propagation",		# 常量传递转换
                  "resulting_condition": "((`test_cost_user`.`user_no` in ('U100','U109','U140')) and (`test_cost_user`.`create_time` >= '2022-11-08 05:36:50') and (`test_cost_user`.`create_time` <= '2022-11-08 05:36:59') and (`test_cost_user`.`check_time` <= `test_cost_user`.`create_time`) and (`test_cost_user`.`user_name` like '%2ac120%') and multiple equal(1, `test_cost_user`.`user_state`))"
                },
                {
                  "transformation": "trivial_condition_removal",	# 去除没用的条件
                  "resulting_condition": "((`test_cost_user`.`user_no` in ('U100','U109','U140')) and (`test_cost_user`.`create_time` >= TIMESTAMP'2022-11-08 05:36:50') and (`test_cost_user`.`create_time` <= TIMESTAMP'2022-11-08 05:36:59') and (`test_cost_user`.`check_time` <= `test_cost_user`.`create_time`) and (`test_cost_user`.`user_name` like '%2ac120%') and multiple equal(1, `test_cost_user`.`user_state`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {		# 替换虚拟生成列
            }
          },
          {
            "table_dependencies": [			# 表的依赖信息
              {
                "table": "`test_cost_user`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [			# 预估不同单表访问方法的访问成本
              {
                "table": "`test_cost_user`",
                "range_analysis": {
                  "table_scan": {			# 全表扫描的行数以及成本
                    "rows": 9870,
                    "cost": 1013.35
                  },
                  "potential_range_indexes": [		# 分析可能使用的索引
                    {
                      "index": "PRIMARY",		# 主键不可用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_user_no",		# idx_user_no 可能被使用
                      "usable": true,
                      "key_parts": [
                        "user_no",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_user_name",		# idx_user_name 不可用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_check_time",		# idx_check_time 不可用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_create_time",		# idx_create_time 可能被使用
                      "usable": true,
                      "key_parts": [
                        "create_time",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_mobile_state",		# idx_mobile_state 不可用
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "idx_user_no",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      },
                      {
                        "index": "idx_create_time",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ]
                  },
                  "analyzing_range_alternatives": {		# 分析各种可能使用的索引的成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_user_no",		# 使用 idx_user_no 的成本分析
                        "ranges": [			# 使用 idx_user_no 的范围区间
                          "U100 <= user_no <= U100",
                          "U109 <= user_no <= U109",
                          "U140 <= user_no <= U140"
                        ],
                        "index_dives_for_eq_ranges": true,	# 是否使用index dive
                        "rowid_ordered": false,		# 使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,				# 是否使用mrr
                        "index_only": false,			# 是否是索引覆盖访问
                        "in_memory": 0.1,
                        "rows": 3,		# 使用该索引获取的记录条数
                        "cost": 1.81,	# 使用该索引的成本
                        "chosen": true	# 是否选择该索引
                      },
                      {
                        "index": "idx_create_time",		# 使用 idx_create_time 的成本分析
                        "ranges": [		# 使用 idx_create_time 的范围区间
                          "0x99ae505932 <= create_time <= 0x99ae50593b"
                        ],
                        "index_dives_for_eq_ranges": true,		# 是否使用index dive
                        "rowid_ordered": false,		# 使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,				# 是否使用mrr
                        "index_only": false,			# 是否是索引覆盖访问
                        "in_memory": 0.111111,
                        "rows": 1001,			# 使用该索引获取的记录条数
                        "cost": 350.61,		# 使用该索引的成本
                        "chosen": false,	# 是否选择该索引
                        "cause": "cost"		# 因为成本太大所以不选择该索引
                      }
                    ],
                    "analyzing_roworder_intersect": {		# 分析使用索引合并的成本
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {		# 对于上述单表 test_cost_user 查询最优的访问方法
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_user_no",
                      "rows": 3,
                      "ranges": [
                        "U100 <= user_no <= U100",
                        "U109 <= user_no <= U109",
                        "U140 <= user_no <= U140"
                      ]
                    },
                    "rows_for_plan": 3,
                    "cost_for_plan": 1.81,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [			# 分析各种可能的执行计划 (对多表查询这可能有很多种不同的方案，单表查询的方案上边已经分析过了，直接选择 idx_user_no 即可)
              {
                "plan_prefix": [
                ],
                "table": "`test_cost_user`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 3,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_user_no"
                      },
                      "resulting_rows": 3,
                      "cost": 2.11,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 2.11,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {		# 尝试给查询添加一些其他的查询条件
              "original_condition": "((`test_cost_user`.`user_state` = 1) and (`test_cost_user`.`user_no` in ('U100','U109','U140')) and (`test_cost_user`.`create_time` >= TIMESTAMP'2022-11-08 05:36:50') and (`test_cost_user`.`create_time` <= TIMESTAMP'2022-11-08 05:36:59') and (`test_cost_user`.`check_time` <= `test_cost_user`.`create_time`) and (`test_cost_user`.`user_name` like '%2ac120%'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`test_cost_user`",
                  "attached": "((`test_cost_user`.`user_state` = 1) and (`test_cost_user`.`user_no` in ('U100','U109','U140')) and (`test_cost_user`.`create_time` >= TIMESTAMP'2022-11-08 05:36:50') and (`test_cost_user`.`create_time` <= TIMESTAMP'2022-11-08 05:36:59') and (`test_cost_user`.`check_time` <= `test_cost_user`.`create_time`) and (`test_cost_user`.`user_name` like '%2ac120%'))"
                }
              ]
            }
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`test_cost_user`",
                "original_table_condition": "((`test_cost_user`.`user_state` = 1) and (`test_cost_user`.`user_no` in ('U100','U109','U140')) and (`test_cost_user`.`create_time` >= TIMESTAMP'2022-11-08 05:36:50') and (`test_cost_user`.`create_time` <= TIMESTAMP'2022-11-08 05:36:59') and (`test_cost_user`.`check_time` <= `test_cost_user`.`create_time`) and (`test_cost_user`.`user_name` like '%2ac120%'))",
                "final_table_condition   ": "((`test_cost_user`.`user_state` = 1) and (`test_cost_user`.`user_no` in ('U100','U109','U140')) and (`test_cost_user`.`create_time` >= TIMESTAMP'2022-11-08 05:36:50') and (`test_cost_user`.`create_time` <= TIMESTAMP'2022-11-08 05:36:59') and (`test_cost_user`.`check_time` <= `test_cost_user`.`create_time`) and (`test_cost_user`.`user_name` like '%2ac120%'))"
              }
            ]
          },
          {
            "refine_plan": [		# 简单优化一下执行计划
              {
                "table": "`test_cost_user`",
                "pushed_index_condition": "(`test_cost_user`.`user_no` in ('U100','U109','U140'))",
                "table_condition_attached": "((`test_cost_user`.`user_state` = 1) and (`test_cost_user`.`create_time` >= TIMESTAMP'2022-11-08 05:36:50') and (`test_cost_user`.`create_time` <= TIMESTAMP'2022-11-08 05:36:59') and (`test_cost_user`.`check_time` <= `test_cost_user`.`create_time`) and (`test_cost_user`.`user_name` like '%2ac120%'))"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {		# execute阶段
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

经过对其分析，优化过程大致分为三个阶段：

• prepare 阶段
• optimize 阶段
• execute 阶段

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

连接查询的成本

将 test_cost_user 表复制一份为 test_cost_user1 作为示例

Condition filtering

MySQL 中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：

• 单次查询驱动表的成本
• 多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）

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

select * from test_cost_user a inner join test_cost_user1 b;

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

select * from test_cost_user a inner join test_cost_user1 b where a.create_time > '2022-11-01 00:00:00' and a.create_time < '2022-11-20 00:00:00';

仍然假设 a 表是驱动表的话，很显然对驱动表的单表查询可以使用 idx_create_time 索引执行查询。此时范围区间( ‘2022-11-01 00:00:00’, ‘2022-11-20 00:00:00’)中有多少条记录，那么扇出值就是多少
但是有的时候扇出值的计算就变得很棘手，比如：
查询三：

select * from test_cost_user a inner join test_cost_user1 b where a.sex = 1;

由于 sex 字段并没有索引，因此对于优化器来说，在没有真正执行之后是不会知道有多少数据满足条件，因此只能靠猜，这也对应着操作可能不会很合理
也就是说，在这两种情况下计算驱动表扇出值时需要靠猜：

• 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条
• 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条

MySQL 把这个猜的过程称之为 condition filtering

在 MySQL 5.7 之前的版本中，查询优化器在计算驱动表扇出时，如果是使用全表扫描的话，就直接使用表中记录的数量作为扇出值，如果使用索引的话，就直接使用满足范围条件的索引记录条数作为扇出值
在 MySQL 5.7 中，MySQL 引入了这个 condition filtering 的功能，就是还要猜测剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确，MySQL 将这种猜测称之为启发式规则

两表连接的成本分析

连接查询的成本计算公式：

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要分别为驱动表和被驱动表选择成本最低的访问方法
可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考 虑两个方面的问题：

• 不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序
• 然后分别为驱动表和被驱动表选择成本最低的访问方法

很显然，计算内连接查询成本的方式更麻烦一些，下边就以内连接为例来看看如何计算出最优的连接查询方案。当然在某些情况下，左（外）连接和右（外）连接查询在某些特殊情况下可以被优化为内连接查询
在这儿基于下面的查询进行表连接成本的分析：

select * from test_cost_user a inner join test_cost_user1 b where a.create_time > '2022-11-01 00:00:00' and a.create_time < '2022-11-20 00:00:00' and b.create_time > '2022-11-01 00:00:00' and b.create_time < '2022-11-20 00:00:00';

可以选择的连接顺序有两种：

• a 连接 b，也就是 a 作为驱动表，b 作为被驱动表
• b 连接 a，也就是 b 作为驱动表，a 作为被驱动表

查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划
假设使用 a 作为驱动表，对其进行定性分析，因为对于当前 sql 来说，哪个表作为驱动表，结果没有什么变化
首先，分析 a 表单表搜索条件：

a.create_time > '2022-11-01 00:00:00' and a.create_time < '2022-11-20 00:00:00'

所以这个查询可能使用到 idx_create_time 索引，从全表扫描和使用 idx_create_time 这两个方案中选出成本最低的那个，很显然使用 idx_create_time 执行查询的成本更低些
然后分析对于被驱动表的成本最低的执行方案，此时涉及被驱动表 b 的搜索条件就是：

b.create_time > '2022-11-01 00:00:00' and b.create_time < '2022-11-20 00:00:00';

此时访问 b 表时可用的方案也是全表扫描和使用 idx_create_time 两种，假设使用 idx_create_time 的成本更小
所以此时使用 a 作为驱动表时的总成本就是（暂时不考虑使用 join buffer 对成本的影响）：

使用 idx_create_time 访问 a 的成本 + a 的扇出 × 使用 idx_create_time 访问 b 的成本

其实，在实际业务中由于表的不同，还会分析以 b 表作为驱动表的情况，然后计算出各自的成本，最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。从上边的计算过程也可以看出来，一般来讲，连接查询成本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本，所以优化的重点其实是下边这两个部分：

• 尽量减少驱动表的扇出
• 对被驱动表的访问成本尽量低

实际编写连接语句时，需要尽量在被驱动表的连接列上建立索引，这样就可以使用 ref 访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了

EXPLAIN 输出连接成本

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

explain format=json select * from test_cost_user a inner join test_cost_user1 b where a.create_time > '2022-11-01 00:00:00' and a.create_time < '2022-11-20 00:00:00' and b.create_time > '2022-11-01 00:00:00' and b.create_time < '2022-11-20 00:00:00';

执行成本为：

{
  "query_block": {
    "select_id": 1,	#整个查询语句只有 1 个 SELECT 关键字，该关键字对应的 id 号为 1
    "cost_info": {
      "query_cost": "9742950.11"	# 整个查询的执行成本
    },
    "nested_loop": [	# 几个表之间采用嵌套循环连接算法执行
      {
        "table": {
          "table_name": "a",	# a 表是驱动表
          "access_type": "ALL",	# 访问方法为 ALL
          "possible_keys": [
            "idx_create_time"
          ],
          "rows_examined_per_scan": 9870,	# 查询 a 表大致需要扫描 9870 条记录
          "rows_produced_per_join": 9870, # 驱动表 a 的扇出是 9870
          "filtered": "100.00",	# condition filtering 代表的百分比
          "cost_info": {
            "read_cost": "24.25",
            "eval_cost": "987.00",
            "prefix_cost": "1011.25",	# 查询 a 表总共的成本，read_cost + eval_cost
            "data_read_per_join": "2M"	# 读取的数据量
          },
          "used_columns": [
            "id",
            "user_no",
            "user_name",
            "user_sex",
            "user_mobile",
            "user_state",
            "check_time",
            "create_time",
            "update_time",
            "delete_flag"
          ],
          "attached_condition": "((`test`.`a`.`create_time` > TIMESTAMP'2022-11-01 00:00:00') and (`test`.`a`.`create_time` < TIMESTAMP'2022-11-20 00:00:00'))"
        }
      },
      {
        "table": {
          "table_name": "b",	# b 表是被驱动表
          "access_type": "ALL",
          "possible_keys": [
            "idx_create_time"
          ],
          "rows_examined_per_scan": 9870,	# 查询一次 b 表大致需要扫描 9870 条记录
          "rows_produced_per_join": 97416900,	# 被驱动表 b 的扇出是 97416900	（由于没有多余的表进行连接，所以这个值无用）
          "filtered": "100.00",	# condition filtering 代表的百分比
          "using_join_buffer": "hash join",
          "cost_info": {
            "read_cost": "248.86",
            "eval_cost": "9741690.00",
            "prefix_cost": "9742950.11",	# 单次查询 a、多次查询 b 表总共的成本
            "data_read_per_join": "22G"
          },
          "used_columns": [
            "id",
            "user_no",
            "user_name",
            "user_sex",
            "user_mobile",
            "user_state",
            "check_time",
            "create_time",
            "update_time",
            "delete_flag"
          ],
          "attached_condition": "((`test`.`b`.`create_time` > TIMESTAMP'2022-11-01 00:00:00') and (`test`.`b`.`create_time` < TIMESTAMP'2022-11-20 00:00:00'))"
        }
      }
    ]
  }
}

重点数据的解释在后面使用#+注释的方式注明

多表连接的成本分析

首先要考虑一下多表连接时可能产生出多少种连接顺序：

• 对于两表连接，比如表 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!

不过 MySQL 用了很多 办法减少计算非常多种连接顺序的成本的方法：

1. 提前结束某种顺序的成本评估

MySQL 在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就不对该连接顺序继续往下分析了。比方说 A、B、C 三个表进行连接，已经得到连接顺序 ABC 是当前的最小连接成本，比方说 10.0，在计算连接顺序 BCA 时，发现 B 和 C 的连接成本就已经大于 10.0 时，就不再继续往后分析 BCA 这个连接顺序的成本了

2. 系统变量 optimizer_search_depth

为了防止无穷无尽的分析各种连接顺序的成本，MySQL 提出了 optimizer_search_depth 系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与 optimizer_search_depth 值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到不是很好的执行计划，但可以省掉很多分析连接成本的时间

3. 根据某些规则不考虑某些连接顺序

即使是有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很长，所以 MySQL 干脆提出了一些所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量 optimizer_prune_level 来控制到底是不是用这些启发式规则

调节成本常数

前边已经提到了两个成本常数：

1. 读取一个页面花费的成本默认是 1.0
2. 检测一条记录是否符合搜索条件的成本默认是 0.2

其实除了这两个成本常数，MySQL 还支持很多，它们被存储到了 MySQL 数据库的两个表中：

SHOW TABLES FROM mysql LIKE '%cost%';

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

mysql.server_cost 表

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

SELECT * FROM mysql.server_cost;

返回参数解释：

返回参数	说明
cost_name	成本常数的名称
cost_value	成本常数对应的值。如果该列的值为 NULL 的话，意味着对应的成本常数会采用默认值
last_update	最后更新记录的时间
comment	注释
default_value	成本常数的默认值

从 server_cost 中的内容可以看出来，目前在 server 层的一些操作对应的成本常数有以下几种：

成本常数名称	默认值	说明
disk_temptable_create_cost	20，（MySQL 5.7及之前是 40）	创建基于磁盘的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表
disk_temptable_row_cost	0.5，（MySQL 5.7及之前是 1.0）	向基于磁盘的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表
key_compare_cost	0.05，（MySQL 5.7及之前是 0.1）	两条记录做比较操作的成本，多用在排序操作上，如果增大这个值的话会提升 filesort 的成本，让优化器可能更倾向于使用索引完成排序而不是 filesort
memory_temptable_create_cost	1，（MySQL 5.7及之前是 2.0）	创建基于内存的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表
memory_temptable_row_cost	0.1，（MySQL 5.7及之前是 0.2）	向基于内存的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表
row_evaluate_cost	0.1，（MySQL 5.7及之前是 0.2）	这个就是之前一直使用的检测一条记录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描

MySQL 在执行诸如 DISTINCT 查询、分组查询、Union 查询以及某些特殊条件下的排序查询都可能在内部先创建一个临时表，使用这个临时表来辅助完成查询（比如对于 DISTINCT 查询可以建一个带有 UNIQUE 索引的临时表，直接把需要去重的记录插入到这个临时表中，插入完成之后的记录就是结果集了）。在数据量大的情况下可能创建基于磁盘的临时表，也就是为该临时表使用MyISAM、InnoDB 等存储引擎，在数据量不大时可能创建基于内存的临时表，也就是使用 Memory 存储引擎。由上面的参数可以看到，创建临时表和对这个临时表进行写入和读取的操作代价还是很高的就行了
这些成本常数在 server_cost 中的初始值都是 NULL，意味着优化器会使用它们的默认值来计算某个操作的成本，如果想修改某个成本常数的值的话，需要做两个步骤：

1. 成本常数做 update 更新操作

update mysql.server_cost set cost_value = 19 where cost_name = 'disk_temptable_create_cost';

2. 刷新成本参数

FLUSH OPTIMIZER_COSTS;

修改完某个成本常数后想把它们再改回默认值的话，可以直接把 cost_value 的值设置为 NULL，再使用 FLUSH OPTIMIZER_COSTS 语句让系统重新加载

mysql.engine_cost 表

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

SELECT * FROM mysql.engine_cost;

与 server_cost 相比，engine_cost 多了两个列，返回参数解释如下：

返回参数	说明
engine_name	成本常数适用的存储引擎名称。如果该值为 default，意味着对应的成本常数适用于所有的存储引擎
device_type	存储引擎使用的设备类型，这主要是为了区分常规的机械硬盘和固态硬盘，不过在 MySQL 5.7.X 这个版本中并没有对机械硬盘的成本和固态硬盘的成本作区分，所以该值默认是 0
cost_name	成本常数的名称
cost_value	成本常数对应的值。如果该列的值为 NULL 的话，意味着对应的成本常数会采用默认值
last_update	最后更新记录的时间
comment	注释
default_value	成本常数的默认值

从 engine_cost 表中的内容可以看出来，目前支持的存储引擎成本常数只有两个：

适用存储引擎	存储引擎设备类型	成本常数名称	默认值	说明
default	0	io_block_read_cost	1，（MySQL 5.7及之前是 1.0）	从磁盘上读取一个块对应的成本。对于 InnoDB 存储引擎来说，一个页就是一个块，不过对于 MyISAM 存储引擎来说，默认是以 4096 字节作为一个块的。增大这个值会加重 I/O 成本，可能让优化器更倾向于选择使用索引执行查询而不是执行全表扫描
default	0	memory_block_read_cost	0.25，（MySQL 5.7及之前是 1.0）	与上一个参数类似，只不过衡量的是从内存中读取一个块对应的成本

与更新 server_cost 表中的记录一样，也可以通过更新 engine_cost 表中的记录来更改关于存储引擎的成本常数，做法一样