mysql5.7的SQL执行成本计算，IO成本和CPU成本，单表查询成本，多表连接查询成本，执行成本决定mysql是否走索引，.OPTIMIZER_TRACE，cost_info

一、IO成本

mysql的innodb存储引擎会把数据存储到磁盘上，这时候无论怎么优化SQL，都是需要从磁盘中读取数据到内存，就是IO成本，每次读取磁盘，至少耗时0.01秒，至少读一页，innodb一个页的数据存储大小是16KB，这个磁盘的IO时间成本是1.0，这里的1.0没有单位，就是个比较值。

二、CPU成本

从磁盘读到数据后要放到内存中处理数据的过程，这是CPU成本。读取后并且检测可能的where条件，这个CPU的IO时间成本为0.2，这里的1.0和0.2被称之为成本常数。

三、单表查询成本计算步骤

3.1、根据搜索条件，找出所有可能使用的索引，也就是EXPLAIN的possible_keys。参考《mysql5.7版本的explain解析》

3.2、计算全表扫描的代价

3.3、计算使用不同索引执行查询的代价。尤其是可能的索引为多个的时候

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

四、例子

4.1、有这样一个表：

CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `username` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'user name',
  `age` int(4) NOT NULL DEFAULT 20 COMMENT 'user age',
  `birthday_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'user birthday',
  `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `remark` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'remark something',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
  `version` int(4) NOT NULL DEFAULT 0 COMMENT 'update version',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `idx_name`(`username`) USING BTREE,
  INDEX `idx_age_remark`(`age`, `remark`) USING BTREE,
  INDEX `idx_create_time`(`create_time`) USING BTREE,
  INDEX `idx_address`(`address`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10003 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC;

4.3【表的页数据、索引数据、行数等信息】

SHOW TABLE STATUS like 't_user'\G 

Row_format:  数据格式。

Rows 表数据行数，MyISAM的这个数据行数统计是准确的，而InnoDB是估值是不准确的。这里t_user表的预估行数是9964行。

Data_length: 该表所占用空间的字节数。 1589248 bytes = 1552 kb    

4.4【SQL执行成本分析】

现在我们需要根据“三、单表查询成本计算步骤”来逐个计算成本：

4.4.1 第一步：计算全表扫描成本

因为mysql每一页存储数据大小是16KB，所以InnoDB占用了 1552 （t_user的Data_length值） / 16 = 97 页磁盘来存储数据。

磁盘IO 成本 = 97 页 * 1.0 (磁盘IO成本) + 1.1(微调数)  = 98.1

CPU 成本 = 9964 (t_user的Rows数据) * 0.2 + 1.0(微调数) = 1993.8

全表扫描成本 = 98.1 + 1993.8 = 2091.9

4.4.2 第二步：计算走索引的查询成本——create_time索引列查询成本

mysql规定，当读取索引扫描的时候，每当读取一个扫描区间或者范围区间的IO成本，和读取一个页面的IO成本，是一样的，都是1.0。根据4.1章节的表结构分析，我们可能用到的索引列是address和create_time，由于这两个列都是普通索引，我们这里就选择create_time索引的range去计算索引成本。那么其他where条件直接置为true:

SELECT * FROM t_user WHERE

address in ('shanghaishi', 'beijingshi')     -- true
AND create_time > '2021-04-14 07:00:00' 
AND create_time < '2021-04-16 07:00:00'
AND birthday_date_time > create_time  -- true
AND remark like '%7%'   -- true
AND version = 0;  -- true      

最后简化成SQL：

SELECT * FROM t_user WHERE create_time > '2021-04-14 07:00:00' AND create_time < '2021-04-16 07:00:00';     

【IO成本】也就是说扫描区间只有一个，所以磁盘IO成本 =  1(个扫描区间) * 1.0(磁盘IO成本) = 1.0  

【CPU成本】那么在两个给定的日期参数这一个区间之内，大概有多少记录数呢？第一，mysql会计算一个页面大概有多少记录，第二mysql会计算这两个区间参数大概隔了多少个页面，然后使用上EXPLAIN里的rows值大概有几条记录，这里参考EXPLAIN的rows值是8条。 8 * 0.2 (cpu成本) + 0.01(微调成本) = 1.61

【回表IO成本】读取完数据后，由于查询的是*所以需要回表，回表涉及IO成本，mysql认为每回表一个聚簇索引，都需要回表一个页面，一个页面的IO成本是1.0， 参考EXPLAIN的rows结果是8条，8*1.0 = 8.0

【回表CPU成本】回表的CPU成本 8 * 0.2 = 1.6      这一步包含了Where条件中其他过滤条件的时间成本

【计算出最终的走索引查询成本】 1.0 + 1.61 + 8.0 + 1.6 = 12.21

4.4.3 第三步：计算走索引的查询成本——address索引列查询成本

EXPLAIN format = json SELECT * FROM t_user WHERE address in ('shanghaishi', 'beijingshi');

【IO成本】由于这里 where address in ('shanghaishi', 'beijingshi')  有两个参数，就代表有两个扫描区间，也就意味着访问 address 的IO成本是： 2 * 1.0 = 2.0

【CPU成本】使用上EXPLAIN里的rows值 291 * 0.2(cpu成本)  + 0.01(微调成本) = 58.21

【回表IO成本】291 * 1.0 = 291

【回表CPU成本】291 * 0.2 = 58.2       这一步包含了Where条件中其他过滤条件的时间成本

【计算出最终的走索引查询成本】 2.0 + 58.21 + 291 + 58.2 = 409.41      

这里计算出的409.41可以使用EXPLAIN的json输出格式进行验证： EXPLAIN format = json SELECT * FROM t_user WHERE address in ('shanghaishi', 'beijingshi');   可以看到query_cost确实是：409.41

上面的rows_examined_per_scan的291条 = 普通EXPLAIN视图的rows 数一致。比较重要的是cost_info里的信息：prefix_cost = 409.41 = read_cost + eval_cost，data_read_per_json = 145K 是指连接查询时的数据量

 

4.4.4 第四步：执行成本比较

根据之前三个步骤的执行成本计算结果进行比较：

全表扫描成本：2091.9 

create_time索引列查询成本：12.21

address索引列查询成本： 409.41

所以，mysql认为使用create_time索引列执行SQL是时间成本最低的。

4.5 EXPLAIN验证

根据4.4的执行成本结果分析，SQL应该实际走create_time列索引，最后我们用EXPLAIN来验证下key列，你看，mysql决定走idx_create_time索引：

EXPLAIN 
SELECT * FROM t_user 
WHERE address in ('shanghaishi', 'beijingshi')
AND create_time > '2021-04-14 07:00:00' 
AND create_time < '2021-04-16 07:00:00'
AND birthday_date_time > create_time
AND remark like '%7%'
AND version = 0;

五、多表连接查询成本

多表连接查询成本 = 一次驱动表成本 + 从驱动表查出的记录数 * 一次被驱动表的成本

六、mysql执行成本详细步骤展示

在上面第四步骤详细讲述了如何手动计算出各个索引列的执行成本以及全表扫描执行成本，但是这个过程太繁琐了，mysql提供了更简便直观的optimizer_trace功能来详细列出了各个步骤的执行成本明细。

set optimizer_trace = "enabled=on,one_line=off";    首先开启optimizer_trace的开关，默认是关闭的。这里的one_line是说结果集要不要一行输出来给你看，默认off就行，毕竟很大的一个json结果集一行显示出来肉眼很难观察。

show variables like 'optimizer_trace';    然后再验证下是否打开了 

执行一下以下这个query SQL，其中包含了3个索引：

SELECT * FROM mytest.t_user 
WHERE address in ('shanghaishi', 'beijingshi')  -- address 是普通索引列
AND create_time > '2021-04-14 07:00:00'         -- create_time 也是普通索引列
AND create_time < '2021-04-16 07:00:00'
AND birthday_date_time > create_time     
AND remark like '%7%'             -- remark是一个组合索引的第二顺序位索引：idx_age_remark(age,remark)
AND version = 0;

 select * from information_schema.OPTIMIZER_TRACE;         mysql使用optimizer_trace来记录刚刚那个query sql，mysql是如何详细计算出各个索引的执行成本的。输出的json结果集比较大，分query和trace，query列出了select sql，trace列出了详细成本计算步骤，只要分3个阶段：1）join_preparation 准备阶段    2）join_optimization 优化阶段     3）join_execution 执行阶段，我们关注的重点是优化阶段，我这里挑优化阶段的几个重点的讲：

substitute_generated_columns:   这是mysql为了方便计算什么值时临时添加的列，不在table里保存。

table_dependencies:  分析表的依赖信息

【index dive】在两个区间之间计算有多少条记录的方式，在mysql中被称为index dive。如果一个SQL 用了 IN (2万个参数，或者一个子查询SQL结果集非常多的)，那么mysql很有可能认为走全表扫描更快。参考《mysql5.7决定SQL中IN条件是否走索引的成本计算，mysql中的index dive是在两个区间之间计算有多少条记录的方式》

【mrr】当mysql读取一批二级索引时，会将根据这些二级索引拿到的主键id进行排好序，去回表到主键索引拿，这个优化过程由Mysql自行控制，我们无法干预，这就是MRR技术，多范围查询技术。当然，实现这个条件比较苛刻。

多表连接查询参数：

rows_examined_per_scan: 321     表示从驱动表结果集预估有321条记录会对被驱动表进行扫描

rows_produced_per_join: 321     如果这两个数值一样，表示filter =100 即过滤100%的数据，但是如果这个值比上一个值少，则可能使用了覆盖索引等进行了优化，那么filter也会少于100%

filtered: 100

prefix_cost是总成本

 

七、执行成本计算优化：提前结束

比如abcd四个表进行多表连接查询，当mysql计算出按照a,b,c,d顺序计算出查询成本是100时，又在计算按照a,c,b,d这个顺序执行成本的过程中，发现仅仅ac这两个表的连接成本110就已经超越了100，就会提前结束放弃对a,c,b,d顺序的计算。多表连接成本计算的计算次数 = 几个表的N次方，四个表就是16次成本计算:  abcd,  acbd, abdc 等等各种排列。

但是如果多表连接中，表个数太多，mysql也不会穷举各种排列，mysql有一个表数量设定   show variables like 'Optimizer_search_depth';  -- 默认62张表。小于62张表的，穷举算法，大于62张表的，按照62种算法计算。

八、mysql成本cost成本参数设定

mysql有两种成本计算engine_cost引擎成本和server_cost服务端成本， show tables from mysql like '%cost%';    

select * from mysql.server_cost;          查看服务端成本计算参数都有哪些，cost_value列为null值的表示使用的是mysql的默认值。

disk_temptable_create_cost  创建基于磁盘的临时表成本，默认值40

disk_temptable_row_cost     往磁盘的临时表里写入或读取一条数据的成本：1

key_compare_cost                两条记录做值比较的成本：0.1，排序操作时用到这个值

memory_temptable_create_cost           创建基于内存的临时表成本，默认值2

memory_temptable_row_cost              往内存中的临时表里写入或读取一条数据的成本：0.2

row_evaluate_cost                           读取一条记录过滤where条件看是否满足我们的搜索条件，默认成本： 0.2

备注：如果你想修改以上默认值，可以update它，如果想设置回默认值，只要设置为null即可。

select * from mysql.engine_cost;             存储引擎的成本

engine_name = default      表示对于所有引擎全部适用，也可能指定存储引擎比如innodb

device_type = 0                  存储引擎所用的设备类型，为了支持机械硬盘或者固态硬盘

cost_name = io_block_read_cost                  从磁盘里读取一个块的成本，默认值1

cost_name = memory_block_read_cost       从内存里读取一个块的成本，默认值1。从磁盘里读取和从内存中读取成本一样，是因为mysql不知道要读取的数据是在磁盘中还是在内存中，所以mysql简单的认为都是1。

备注：如果你修改了成本参数，可能会造成一定后果，比如把row_evaluate_cost调大，mysql会更倾向于使用索引，而不是全表扫描，比如你把memory_temptable_create_cost调的比disk_temptable_create_cost还大，mysql会更倾向于从磁盘中创建临时表，而不是内存中。

九、mysql对于表和索引的统计

show tables from mysql like 'innodb%;

desc mysql.innodb_table_stats;       表统计。database_name 数据库名；table_name 表名；last_update 最后更新时间；n_rows 行数；clustered_index_size 表聚簇索引占用页面数量； sum_of_other_index_sizes 其他索引占用页面数量；  其中n_rows 这个预估数是mysql用20页（默认页数可调整）的数据平均下来的采样数量乘以多少个页得出来的预估数量，不是精确数值。

desc mysql.innodb_index_stats;       索引统计。stat_name 索引名称；stat_value 不重复数量；mysql默认在数据有10%的更新量下，会对索引统计进行更新。我们也可以手动更新索引统计： analyze table t_order;   对于t_order表进行手动索引统计更新。手动更新统计要少做，是一个对表的阻塞操作，尽量让mysql默认执行就行。

end.