Json格式的执行计划
上篇说的EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性 —— 成本。不过 MySQL 为我们提供了一种查看某个执行计划花费的成本的方式：
在 EXPLAIN 单词和真正的查询语句中间加上 FORMAT=JSON 。
eg：
json：
{
"query_block": {
"select_id": 1, // 整个查询语句只有1个SELECT关键字，该关键字对应的id号为1
"cost_info": {
"query_cost": "17277.84" // 整个查询的执行成本预计为3197.16
},
"nested_loop": [ // 几个表之间采用嵌套循环连接算法执行
{
// 以下是参与嵌套循环连接算法的各个表的信息
"table": {
"table_name": "db2", //db2表是驱动表
"access_type": "ALL", //访问方法为ALL，意味着使用全表扫描访问
"possible_keys": [ // 可能使用的索引
"key1"
],
"rows_examined_per_scan": 99410, //查询一次db2表大致需要扫描99410条记录
"rows_produced_per_join": 9941, //驱动表db2的扇出是9941
"filtered": "10.00", //condition filtering代表的百分比
"cost_info": {
"read_cost": "9115.15", //稍后解释
"eval_cost": "994.10", //稍后解释
"prefix_cost": "10109.25", //单次查询db2表总共的成本
"data_read_per_join": "29M" //读取的数据量
},
"used_columns": [ //执行查询中涉及到的列
"id",
"name",
"key1",
"key2",
"key3",
"age"
],
// 对db2表访问时针对单表查询的条件
"attached_condition": "((`mysqltest`.`db2`.`age` = 11) and (`mysqltest`.`db2`.`key1` is not null))"
}
},
{
"table": {
"table_name": "db1", //db1表是被驱动表
"access_type": "eq_ref", //访问方法为eq_ref，意味着使用索引等值匹配的方式访问
"possible_keys": [ //可能使用的索引
"key1"
],
"key": "key1", //实际使用的索引
"used_key_parts": [ //使用到的索引列
"key1"
],
"key_length": "1023", //key_len
"ref": [ //与key2列进行等值匹配的对象
"mysqltest.db2.key1"
],
"rows_examined_per_scan": 1, //查询一次db1表大致需要扫描1条记录
"rows_produced_per_join": 9941, //被驱动表db1的扇出是9941（由于后边没有多余的表进行连接，所以这个值也没啥用）
"filtered": "100.00", //condition filtering
// db1表使用索引进行查询的搜索条件
"index_condition": "(`mysqltest`.`db1`.`key1` = `mysqltest`.`db2`.`key1`)",
"cost_info": {
"read_cost": "6174.49",
"eval_cost": "994.10",
"prefix_cost": "17277.84", //单次查询db2、多次查询db1表总共的成本
"data_read_per_join": "58M" //读取的数据量
},
"used_columns": [ //执行查询中涉及到的列
"id",
"name",
"age",
"sfzh",
"key1",
"key2",
"key3",
"key4"
]
}
}
]
}
}
cost_info
db2表:
"cost_info": {
"read_cost": "9115.15",
"eval_cost": "994.10",
"prefix_cost": "10109.25",
"data_read_per_join": "29M"
},
read_cost 是由下边这两部分组成的：
1、IO 成本
检测 rows × (1 - filter) 条记录的 CPU 成本
在JSON格式的执行计划中，rows相当于rows_examined_per_scan，filtered名称不变。
2、eval_cost 是这样计算的：
检测 rows × filter 条记录的成本。
prefix_cost 就是单独查询 db2 表的成本，也就是：read_cost + eval_cost
data_read_per_join 表示在此次查询中需要读取的数据量
我们关注prefix_cost是查询s1表的成本就好了。
对于 db1 表的 “cost_info” 部分是这样的：
"cost_info": {
"read_cost": "6174.49",
"eval_cost": "994.10",
"prefix_cost": "17277.84",
"data_read_per_join": "58M"
},
由于 db1 表是被驱动表，所以可能被读取多次，这里的 read_cost 和 eval_cost 是访问多次 db1 表后累加起来的
值，大家主要关注里边儿的 prefix_cost 的值代表的是整个连接查询预计的成本，也就是单次查询 db2表和多次
查询 db1 表后的成本的和，也就是：
10109.25+7194.49+994.10=17277.84
Extented EXPLAIN
使用 EXPLAIN 语句查看了某个查询的执行计划后，紧接着还可以使用 SHOW WARNINGS 语句查看与这个查询的执行计划有关的一些扩展信息。
SHOW WARNINGS 展示出来的信息有三个字段，分别是 Level 、 Code 、 Message 。
我们最常见的就是 Code 为 1003 的信息，当 Code 值为 1003 时， Message 字段展示的信息类似于查询优化器将我们的查询语句重写后的语句。
原：SELECT * FROM db1 INNER JOIN db2 ON db1.key1 = db2.key1 WHERE db2.age =11
重写后：
select `mysqltest`.`db1`.`id` AS `id`,`mysqltest`.`db1`.`name` AS `name`,`mysqltest`.`db1`.`age` AS `age`,`mysqltest`.`db1`.`sfzh` AS `sfzh`,`mysqltest`.`db1`.`key1` AS `key1`,`mysqltest`.`db1`.`key2` AS `key2`,`mysqltest`.`db1`.`key3` AS `key3`,`mysqltest`.`db1`.`key4` AS `key4`,`mysqltest`.`db2`.`id` AS `id`,`mysqltest`.`db2`.`name` AS `name`,`mysqltest`.`db2`.`key1` AS `key1`,`mysqltest`.`db2`.`key2` AS `key2`,`mysqltest`.`db2`.`key3` AS `key3`,`mysqltest`.`db2`.`age` AS `age`
from `mysqltest`.`db1`
join `mysqltest`.`db2`
where ((`mysqltest`.`db2`.`age` = 11) and (`mysqltest`.`db1`.`key1` = `mysqltest`.`db2`.`key1`))
optimizer trace表
我们可以通过 EXPLAIN 语句查看到最后优化器决定使用的执行计划，却无法知道它为什么做这个决策。这对于一部分喜欢刨根问底的小伙伴来说简直是灾难：“我就觉得使用其他的执行方案比 EXPLAIN 输出的这种方案强，凭什么优化器做的决定和我想的不一样呢？”
在 MySQL 5.6 以及之后的版本中，MySQL 贴心的为这部分小伙伴提出了一个 optimizer trace 的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程，这个功能的开启与关闭由系统变量optimizer_trace 决定。
SHOW VARIABLES LIKE 'optimizer_trace';
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 功能的步骤总结如下：
# 1. 打开optimizer trace功能 (默认情况下它是关闭的):
SET optimizer_trace="enabled=on";
# 2. 这里输入你自己的查询语句
SELECT ...;
# 3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程
SELECT * FROM information_schema.OPTIMIZER_TRACE;
# 4. 可能你还要观察其他语句执行的优化过程，重复上边的第2、3步
...
# 5. 当你停止查看语句的优化过程时，把optimizer trace功能关闭
SET optimizer_trace="enabled=off";
eg：
sql：
SELECT * FROM db1 WHERE key1 > 'z' AND
key2 < 1000000 AND
key3 IN ('a', 'b', 'c') AND
name = 'abc';
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G;
*************************** 1. row ***************************
// 分析的查询语句是什么
QUERY: SELECT * FROM db1 WHERE key1 > 'z' AND
key2 < 1000000 AND
key3 IN ('a', 'b', 'c') AND
name = 'abc'
//优化的具体过程
TRACE: {
"steps": [
{
"join_preparation": { //prepare阶段
"select#": 1,
"steps": [
{
"IN_uses_bisection": true
},
{
"expanded_query": "/* select#1 */ select `db1`.`id` AS `id`,`db1`.`name` AS `name`,`db1`.`age` AS `age`,`db1`.`sfzh` AS `sfzh`,`db1`.`key1` AS `key1`,`db1`.`key2` AS `key2`,`db1`.`key3` AS `key3`,`db1`.`key4` AS `key4` from `db1` where ((`db1`.`key1` > 'z') and (`db1`.`key2` < 1000000) and (`db1`.`key3` in ('a','b','c')) and (`db1`.`name` = 'abc'))"
}
]
}
},
{
"join_optimization": { // optimize阶段
"select#": 1,
"steps": [
{
"condition_processing": { //处理搜索条件
"condition": "WHERE",
//原始搜索条件
"original_condition": "((`db1`.`key1` > 'z') and (`db1`.`key2` < 1000000) and (`db1`.`key3` in ('a','b','c')) and (`db1`.`name` = 'abc'))",
"steps": [
{
//等值传递转换
"transformation": "equality_propagation",
"resulting_condition": "((`db1`.`key1` > 'z') and (`db1`.`key2` < 1000000) and (`db1`.`key3` in ('a','b','c')) and multiple equal('abc', `db1`.`name`))"
},
{
//常量传递转换
"transformation": "constant_propagation",
"resulting_condition": "((`db1`.`key1` > 'z') and (`db1`.`key2` < 1000000) and (`db1`.`key3` in ('a','b','c')) and multiple equal('abc', `db1`.`name`))"
},
{
//去除没用的条件
"transformation": "trivial_condition_removal",
"resulting_condition": "((`db1`.`key1` > 'z') and (`db1`.`key2` < 1000000) and (`db1`.`key3` in ('a','b','c')) and multiple equal('abc', `db1`.`name`))"
}
]
}
},
{
//替换虚拟生成列
"substitute_generated_columns": {
}
},
{
//表的依赖信息
"table_dependencies": [
{
"table": "`db1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
//预估不同单表访问方法的访问成本
"rows_estimation": [
{
"table": "`db1`",
"range_analysis": {
//全表扫描的行数以及成本
"table_scan": {
"rows": 907051,
"cost": 96331.7
},
//分析可能使用的索引
"potential_range_indexes": [
{
"index": "PRIMARY", //主键不可用
"usable": false,
"cause": "not_applicable"
},
{
"index": "key1", //key1可能被使用
"usable": true,
"key_parts": [
"key1"
]
},
{
"index": "key2", //key2可能被使用
"usable": true,
"key_parts": [
"key2",
"key3",
"key4"
]
},
{
"index": "sfzh", //sfzh可能被使用
"usable": true,
"key_parts": [
"key2",
"id"
]
},
{
"index": "key3", //key3可能被使用
"usable": true,
"key_parts": [
"key3",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "key1",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "key2",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "sfzh",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "key3",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
//分析各种可能使用的索引的成本
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
//使用key1的成本分析
"index": "key1",
//使用key1的范围区间
"ranges": [
"'z' < key1"
],
"index_dives_for_eq_ranges": true, //是否使用index dive
"rowid_ordered": false, //使用该索引获取的记录是否按照主键排序
"using_mrr": false, //是否使用mrr
"index_only": false, //是否是索引覆盖访问
"in_memory": 0.0286288,
"rows": 1, //使用该索引获取的记录条数
"cost": 1.57911, //使用该索引的成本
"chosen": true //是否选择该索引
},
//以下的字段意思同上
{
"index": "key2",
"chosen": false,
"cause": "no_valid_range_for_this_index"
},
{
"index": "key3",
"ranges": [
"key3 = 'a'",
"key3 = 'b'",
"key3 = 'c'"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 0,
"rows": 3,
"cost": 4.71732,
"chosen": false,
"cause": "cost" //因为成本太大所以不选择该索引
}
],
//分析使用索引合并的成本
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
// 对于上述单表查询db1最优的访问方法
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "key1",
"rows": 1,
"ranges": [
"'z' < key1"
]
},
"rows_for_plan": 1,
"cost_for_plan": 1.57911,
"chosen": true
}
}
}
]
},
{
//分析各种可能的执行计划
//对多表查询这可能有很多种不同的方案，单表查询的方案上边已经分析过了，直接选取key1就好）
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`db1`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 1,
"access_type": "range",
"range_details": {
"used_index": "key1"
},
"resulting_rows": 1,
"cost": 1.67911,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 1.67911,
"chosen": true
}
]
},
{
//尝试给查询添加一些其他的查询条件
"attaching_conditions_to_tables": {
"original_condition": "((`db1`.`name` = 'abc') and (`db1`.`key1` > 'z') and (`db1`.`key2` < 1000000) and (`db1`.`key3` in ('a','b','c')))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`db1`",
"attached": "((`db1`.`name` = 'abc') and (`db1`.`key1` > 'z') and (`db1`.`key2` < 1000000) and (`db1`.`key3` in ('a','b','c')))"
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "`db1`",
"original_table_condition": "((`db1`.`name` = 'abc') and (`db1`.`key1` > 'z') and (`db1`.`key2` < 1000000) and (`db1`.`key3` in ('a','b','c')))",
"final_table_condition ": "((`db1`.`name` = 'abc') and (`db1`.`key1` > 'z') and (`db1`.`key2` < 1000000) and (`db1`.`key3` in ('a','b','c')))"
}
]
},
{
//再稍稍的改进一下执行计划
"refine_plan": [
{
"table": "`db1`",
"pushed_index_condition": "(`db1`.`key1` > 'z')",
"table_condition_attached": "((`db1`.`name` = 'abc') and (`db1`.`key2` < 1000000) and (`db1`.`key3` in ('a','b','c')))"
}
]
}
]
}
},
{
"join_execution": { //execute阶段
"select#": 1,
"steps": [
]
}
}
]
}
//因优化过程文本太多而丢弃的文本字节大小，值为0时表示并没有丢弃
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
//权限字段
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)
优化过程大致分为了三个阶段：
prepare 阶段
optimize 阶段
execute 阶段
我们所说的基于成本的优化主要集中在 optimize 阶段，对于单表查询来说，我们主要关注 optimize 阶段的 “rows_estimation” 这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；对于多表连接查询来说，我们更多需要关注 “considered_execution_plans” 这个过程，这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终会选择成本最低的那种方案来作为最终的执行计划，也就是我们使用 EXPLAIN 语句所展现出的那种方案。