mysql存储过程和触发器的使用（mysqlexplainformat）

explain format=json 可以打印详细地执行计划成本，下面两个示例将告诉你如何查看成本输出，以及如何计算成本。

表结构如下：

mysql> show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '', `pad` varchar(90) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4316190 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ##注意sbtest3无主键 mySQL> show create table sbtest3\G *************************** 1. row *************************** Table: sbtest3 Create Table: CREATE TABLE `sbtest3` ( `id` int(11) NOT NULL, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '', `pad` varchar(66) COLLATE utf8mb4_bin DEFAULT NULL, KEY `k_3` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

mysql> explain format=json select * from sbtest3 where id<100 and k<200\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "26.21" ##查询总成本 }, "table": { "table_name": "sbtest3", ##表名 "access_type": "range", ##访问数据的方式是range，即索引范围查找 "possible_keys": [ "k_3" ], "key": "k_3", ##使用索引 "used_key_parts": [ "k" ], "key_length": "4", "rows_examined_per_scan": 18, ##扫描 k_3 索引的行数：18（满足特定条件时使用index dive可得到真实行数） "rows_produced_per_join": 5, ##扇出。在扫描索引后估算满足id<100条件的行数：5 "filtered": "33.33", ##在扫描索引后估算满足其他条件id<100的数据行占扫描行数的比重 "index_condition": "(`sbtest`.`sbtest3`.`k` < 200)", ##索引条件 "cost_info": { "read_cost": "25.01", ##这里包含了所有的IO成本 部分CPU成本 "eval_cost": "1.20", ##部分CPU成本：rows_produced_per_join*成本常数 "prefix_cost": "26.21", ##read_cost eval_cost "data_read_per_join": "4K" }, "used_columns": [ "id", "k", "c", "pad" ], "attached_condition": "(`sbtest`.`sbtest3`.`id` < 100)" } } }

字面意思是每次连接产生的行，这里是个单表查询，无所谓连接，估计是为了统一所以这样命名了（因为SQL有可能是连接查询，这样就不必再取一个名字）。从单表查询来看，这个就是预估的最终满足条件的行数，即 rows_examined_per_scan*filtered，18*33.33%四舍五入应该是 6 才对，这里是向下取整所以显示5。这个值也叫做扇出。

这个很简单，就是计算扇出的CPU成本。应用条件 k<200 时，需要扫描索引 18 行记录，这里18是精确值（index dive），然后优化器用了一种叫启发式规则（heuristic）的算法估算出其中满足条件 id<100 的比例为 33.33%，进行 18*33.33% 次计算的CPU成本等于 18*33.33%*0.2=1.2，这里 0.2 是成本常数（即 row_evaluate_cost ）。简化公示：eval_cost=rows_examined_per_scan*filtered*成本常数，由于 rows_produced_per_join 其实等于 rows_examined_per_scan*filtered，再简化可以得出：eval_cost=rows_produced_per_join*成本常数

注意：计算成本时扇出数准确来说应该用 rows_examined_per_scan*filtered 表示， rows_produced_per_join 是对其向下取整的

这里包含了所有的IO成本 （CPU成本-eval_cost）。我们先看下这个SQL的总成本应该怎么算：访问二级索引 k_3 的成本：

• IO成本=1*1.0查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的，这个SQL中 k 字段的筛选范围只有1个：k < 200，而读取一个页面的IO成本为1.0（即 io_block_read_cost）；
• CPU成本=18*0.2从 k 索引中取出 18行数据后，实际还要再计算一遍，每行计算的成本为 0.2。

然后因为 select * 以及 where id<100 需要的数据都不在索引 k_3 中，所以还需要回表，回表成本：

• IO成本=18*1.0从索引中取出满足 k<200 的数据一共是 18 行，所以=18*1.0；
• CPU成本=18*0.2从这18行完整的数据中计算满足 id<100 的数据，所以也需要计算 18 次。这里拆成了两部分：18*(66.67% 33.33%)*0.2，eval_cost 是其中 18*33.33%*0.2 的部分，即扇出的那部分。

总成本=1*1.0 18*0.2 18*1 18*0.2=26.2。

mysql> explain format=json select t1.id from sbtest1 t1 join sbtest3 t3 \ on t1.id=t3.id and t3.k<200 and t3.id<100\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "33.41" ##查询总成本 }, "nested_loop": [ ##join算法：NLJ { "table": { "table_name": "t3", ##t3是驱动表 "access_type": "range", ##访问数据的方式是range，即索引范围查找 "possible_keys": [ "k_3" ], "key": "k_3", ##使用的索引：k_3 "used_key_parts": [ ##索引字段：k "k" ], "key_length": "4", "rows_examined_per_scan": 18, ##k_3索引扫描行数：18 "rows_produced_per_join": 5, ##扇出。18*33.33% 向下取整了所以是5，实际算eval_cost时应该四舍五入得6 "filtered": "33.33", ##（估算值）扫描索引18行后，满足条件id<200的数据占扫描行数的比例，即驱动表扇出 "index_condition": "(`sbtest`.`t3`.`k` < 200)", "cost_info": { "read_cost": "25.01", ##这里包含了所有的IO成本 部分CPU成本 "eval_cost": "1.20", ##部分CPU成本：rows_produced_per_join*成本常数 "prefix_cost": "26.21", ##驱动表的总成本：read_cost eval_cost "data_read_per_join": "4K" }, "used_columns": [ "id", "k" ], "attached_condition": "(`sbtest`.`t3`.`id` < 100)" } }, { "table": { "table_name": "t1", ##t1为被驱动表 "access_type": "eq_ref", ##关联查询时访问驱动表方式是通过主键或唯一索引的等值查询 "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", ##使用索引为主键 "used_key_parts": [ ##索引字段为id "id" ], "key_length": "4", "ref": [ "sbtest.t3.id" ], "rows_examined_per_scan": 1, ##关联查询时，每次扫描被驱动表1行数据（使用主键） "rows_produced_per_join": 5, ##被驱动表t1的扇出，即估算的满足join条件的行数 "filtered": "100.00", ##关联查询后，（估算）满足join条件的行数占被驱动表t1总扫描行数的比例 "using_index": true, ##t1总扫描行数=rows_examined_per_scan*驱动表扇出数=1*6=6 "cost_info": { "read_cost": "6.00", ##t1表总的IO成本：驱动表扇出数*单次查询被驱动表的IO成本=6*1*1.0=6 "eval_cost": "1.20", ##部分CPU成本：rows_produced_per_join*成本常数=6*0.2 "prefix_cost": "33.41", ##查询总成本=驱动表的总成本 被驱动表的(read_cost eval_cost) "data_read_per_join": "5K" }, "used_columns": [ "id" ] } } ] } }

eval_cost 是部分CPU成本，但是由于 filtered=100%，另一部分成本 rows_examined_per_scan*(1-filtered)*成本常数=0，所以 eval_cost 就相当于全部的CPU成本了。

join查询的总成本计算公式简化：连接查询总成本 = 访问驱动表的成本 访问被驱动表的成本。explain 执行计划详解1 中有解释 filtered 在关联查询中的重要性。

在上面示例中：访问驱动表的成本=26.21，被驱动表的成本=6 1.2=7.2总成本=26.21 7.2=33.41

被驱动表的 read_cost eval_cost 不等于被驱动表的总成本，除非 filtered 等于100%。可以简化被驱动表的总成本计算公式：被驱动表的总成本= read_cost eval_cost/filtered

感谢原作者：https://www.jianshu.com/p/f93b13323058