7. EXPLAIN的进一步使用
7.1 EXPLAIN四种输出格式
这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式： 传统格式 ， JSON格式 ， TREE格式 以及可视化输出 。用户可以根据需要选择适用于自己的格式
1. 传统格式
传统格式简单明了，输出是一个表格形式，概要说明查询计划
EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | s2 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 90.00 | Using where | | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | atguigudb.s2.key1 | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ */
2. JSON格式
第1种格式中介绍的EXPLAIN语句输出中缺少了一个衡量执行计划好坏的重要属性——成本。而JSON格式是四种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。
- JSON格式：在EXPLAIN单词和真正的查询语句中间加上 FORMAT=JSON
EXPLAIN FORMAT=JSON SELECT ...
- EXPLAIN的column与JSON的对应关系:〔来源于MySQL 5.7文档)
这样就可以得到一个json格式的执行计划，里面包含该计划花费的成本，比如这样:
#json格式的explain EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'; /* +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, #整个查询语句只有1个SELECT关键字，该关键字对应的id号为1 "cost_info": { "query_cost": "1360.07" #整个查询的执行成本预计为3197.16 }, "nested_loop": [#几个表之间采用版套循环连按算法执行 #以下是参与嵌套循环连接算法的各个表的信息 { "table": { "table_name": "s1", #s1是驱动表 "access_type": "ALL",#访问方法为ALL，意味着使用全表扫描访问 "possible_keys": [ #可能使用的索引 "idx_key1" ], "rows_examined_per_scan": 9688, #查询一次s1表大致需要9895条记录 "rows_produced_per_join": 968, #驱动表s1的扇出是968 "filtered": "10.00", #condition filtering代表的百分比 "cost_info": { "read_cost": "914.80", #稍后解释 "eval_cost": "98.95", #稍后解释 "prefix_cost": "1013.75", #单次查询s1表总共的成本 "data_read_per_join": "1M" #读取的数据量 }, "used_columns": [ #执行查询中涉及的列 "id", "key1", "key2", "key3", "key_part1", "key_part2", "key_part3", "common_field" ], #对s1表访问时针对单表查询的条件 "attached_condition": "((`atguigudb`.`s1`.`common_field` = 'a') and (`atguigudb`.`s1`.`key1` is not null))" } }, { "table": { "table_name": "s2", #s2是被驱动表 "access_type": "ref",#访问方法为ref，意味着使用索引等值匹配的方式访问 "possible_keys": [ #可能使用的索引 "idx_key2" ], "key": "idx_key2", #实际使用的索引 "used_key_parts": [ #实际使用的索引列 "key2" ], "key_length": "5", #key_len "ref": [ #与key2列等值匹配的对象 "atguigudb.s1.key1" ], "rows_examined_per_scan": 1, #查询一次s1表大致需要扫描1条记录 "rows_produced_per_join": 968, #被驱动表s1的扇出是968（由于后面没有多余的表进行连接，所以这个值也没啥用） "filtered": "100.00", #condition filtering代表的百分比 #对s1表访问时针对单表查询的条件 "index_condition": "(cast(`atguigudb`.`s1`.`key1` as double) = cast(`atguigudb`.`s2`.`key2` as double))", "cost_info": { "read_cost": "247.38", #稍后解释 "eval_cost": "98.95", #稍后解释 "prefix_cost": "1360.08", #单次查询s2表总共的成本 "data_read_per_join": "1M" #读取的数据量 }, "used_columns": [ #执行查询中涉及的列 "id", "key1", "key2", "key3", "key_part1", "key_part2", "key_part3", "common_field" ] } } ] } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ */
我们使用#后边跟随注释的形式为大家解释了 EXPLAIN FORMAT=JSON 语句的输出内容，但是大家可能有疑问 “cost_info” 里边的成本看着怪怪的，它们是怎么计算出来的？先看 s1 表的 “cost_info” 部分：
"cost_info": { "read_cost": "914.80", "eval_cost": "98.95", "prefix_cost": "1013.75", "data_read_per_join": "1M" }
read_cost 是由下边这两部分组成的：
IO 成本
检测 rows × (1 - filter) 条记录的 CPU 成本
小贴士：
rows和filter都是我们前边介绍执行计划的输出列，在JSON格式的执行计划中，rows
相当于rows_examined_per_scan，filtered名称不变。
eval_cost 是这样计算的：检测 rows × filter 条记录的成本
prefix_cost 就是单独查询 s1 表的成本，也就是：read_cost + eval_cost
data_read_per_join 表示在此次查询中需要读取的数据量:
对于 s2 表的 “cost_info” 部分是这样的：
“cost_info”: { “read_cost”: “968.80”, “eval_cost”: “193.76”, “prefix_cost”: “3197.16”, “data_read_per_join”: “1M” }
由于 s2 表是被驱动表，所以可能被读取多次，这里的 read_cost 和 eval_cost 是访问多次 s2 表后累加起来的值，大家主要关注里边儿的 prefix_cost 的值代表的是整个连接查询预计的成本，也就是单次查询 s1 表和多次查询 s2 表后的成本的和，也就是：
968.80 + 193.76 + 2034.60 = 3197.16
3. TREE格式
TREE格式是8.0.16版本之后引入的新格式，主要根据查询的 各个部分之间的关系 和 各部分的执行顺序 来描述如何查询
EXPLAIN FORMAT=tree SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G /* *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=1360.08 rows=990) -> Filter: ((s1.common_field = 'a') and (s1.key1 is not null)) (cost=1013.75 rows=990) -> Table scan on s1 (cost=1013.75 rows=9895) -> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double)) (cost=0.25 rows=1) */
4. 可视化输出
可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标，即可生成可视化的查询计划。
上图按从左到右的连接顺序显示表。红色框表示 全表扫描 ，而绿色框表示使用 索引查找 。对于每个表，显示使用的索引。还要注意的是，每个表格的框上方是每个表访问所发现的行数的估计值以及访问该表的成本
7.2 SHOW WARNINGS的使用
在使用EXPLAIN语句查看了某个查询的执行计划后，紧接着还可以使用SHOW WARNINGS语句查看与这个查询的执行计划有关的一些扩展信息，比如这样:
EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL; SHOW WARNINGS\G #Message后的“语句”可以近似看作真正执行的情况 #通常通过SHOW WARNINGS可以看到优化器对查询语句的重写和优化 /* *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `atguigudb`.`s1`.`key1` AS `key1`,`atguigudb`.`s2`.`key1` AS `key1` from `atguigudb`.`s1` join `atguigudb`.`s2` where ((`atguigudb`.`s1`.`key1` = `atguigudb`.`s2`.`key1`) and (`atguigudb`.`s2`.`common_field` is not null)) */
可以看到 SHOW WARNINGS 展示出来的信息有三个字段，分别是Level、Code 、Message。最常见的就是code为1003的信息，当Code信为1003时,Message字段展示的信息类似于查询优化器将查询语句重写后的语句。比如上边的查询本来是一个左(外）连接查询，但是有一个s2.common_field IS NOT NULL的条件，这就会导致查询优化器把左（外〉连接查询优化为内连接查询，从SHOW WARNINGS的 Message字段也可以看出来，原本的LEFTJOIN已经变成了JOIN。
小结：
通过SHOW WARNINGS可以看到优化器对查询语句的重写和优化
8. 分析优化器执行计划：trace
OPTIMIZER_TRACE 是MySQL 5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策〈比如访问表的方法、各种开销计算、各种转换等)，并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。
此功能默认关闭。开启trace，并设置格式为JSON，同时设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示
#开启trace SET optimizer_trace="enabled=on",end_markers_in_json=on; #设置trace最大能够使用的内存大小： set optimizer_trace_max_mem_size=1000000;
开启后，可分析如下语句：
- SELECT
- INSERT
- REPLACE
- UPDATE
- DELETE
- EXPLAIN
- SET
- DECLARE
- CASE
- IF
- RETURN
- CALL
测试：执行如下SQL语句
select * from student where id < 10; /* +----+--------+--------+------+---------+ | id | stuno | name | age | classId | +----+--------+--------+------+---------+ | 1 | 100002 | tolqVJ | 47 | 158 | | 2 | 100003 | Ahispv | 29 | 811 | | 3 | 100004 | uEKNLo | 32 | 397 | | 4 | 100005 | lXcxdY | 68 | 310 | | 5 | 100006 | FeFNbQ | 15 | 829 | | 6 | 100007 | XoAMhC | 38 | 927 | | 7 | 100008 | IEUKTn | 71 | 593 | | 8 | 100009 | VQtuTi | 27 | 494 | | 9 | 100010 | SQBkvx | 13 | 825 | +----+--------+--------+------+---------+ 9 rows in set (0.00 sec) */
最后， 查询 information_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的 ：
select * from information_schema.optimizer_trace\G ###输出结果： *************************** 1. row *************************** //第1部分：查询语句 QUERY: select * from student where id < 10 //第2部分：QUERY字段对应语句的跟踪信息 TRACE: { "steps": [ { "join_preparation": { //预备工作 "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `student`.`id` AS `id`,`student`.`stuno` AS `stuno`,`student`.`name` AS `name`,`student`.`age` AS `age`,`student`.`classId` AS `classId` from `student` where (`student`.`id` < 10)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { //进行优化 "select#": 1, "steps": [ { "condition_processing": { //条件处理 "condition": "WHERE", "original_condition": "(`student`.`id` < 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`student`.`id` < 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`student`.`id` < 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`student`.`id` < 10)" } ] /* steps */ } /* condition_processing ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { //替换生成的列 } /* substitute_generated_columns */ }, { "table_dependencies": [ //表的依赖关系 { "table": "`student`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ //使用键 ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ //行判断 { "table": "`student`", "range_analysis": { "table_scan": { "rows": 3973767, "cost": 408558 } /* table_scan */, //扫描表 "potential_range_indexes": [ //潜在的范围索引 { "index": "PRIMARY", "usable": true, "key_parts": [ "id" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ //设置范围条件 ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { //分析范围选项 "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "id < 10" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, " using_mrr": false, "index_only": false, "rows": 9, "cost": 1.91986, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { //选择范围访问摘要 "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 9, "ranges": [ "id < 10" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 9, "cost_for_plan": 1.91986, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ //考虑执行计划 { "plan_prefix": [ ] /* plan_prefix */, "table": "`student`", "best_access_path": { //最佳访问路径 "considered_access_paths": [ { "rows_to_scan": 9, "access_type": "range", "range_details": { "used_index": "PRIMARY" } /* range_details */, "resulting_rows": 9, "cost": 2.81986, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, //行过滤百分比 "rows_for_plan": 9, "cost_for_plan": 2.81986, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { //将条件附加到表上 "original_condition": "(`student`.`id` < 10)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ //附加条件概要 { "table": "`student`", "attached": "(`student`.`id` < 10)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`student`", "original_table_condition": "(`student`.`id` < 10)", "final_table_condition ": "(`student`.`id` < 10)" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ //精简计划 { "table": "`student`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { //执行 "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } //第3部分：跟踪信息过长时，被截断的跟踪信息的字节数。 MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 //丢失的超出最大容量的字节 //第4部分：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一般在 #调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。 INSUFFICIENT_PRIVILEGES: 0 //缺失权限
9. MySQL监控分析视图-sys schema
关于MySQL的性能监控和问题诊断，一般都从performance_schema中去获取想要的数据，在MySQL5.7.7版本中新增sys schema，它将performance_schema和information_schema中的数据以更容易理解的方式总结归纳为"视图"，其目的就是为了降低查询performance_schema的复杂度，让DBA能够快遇的定位问题。下面看看这些库中都有哪些监控表和视图，学握了这些，在开发和运维的过程中就起到了事半功倍的效果。
9.1 Sys schema视图摘要
1. 主机相关：以host_summary开头，主要汇总了IO延迟的信息。
2. Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。
3. I/o相关：以io开头，汇总了等待I/O、I/O使用量情况。
4. 内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况
5. 连接与会话信息：processlist和session相关视图，总结了会话相关信息。
6. 表相关：以schema_table开头的视图，展示了表的统计信息。
7. 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。
8. 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。
9. 用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。
10. 等待事件相关信息：以wait开头，展示等待事件的延迟情况。
9.2 Sys schema视图使用场景
索引情况
#1. 查询冗余索引 select * from sys.schema_redundant_indexes; #2. 查询未使用过的索引 select * from sys.schema_unused_indexes; #3. 查询索引的使用情况 select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='dbname' ;
表相关
# 1. 查询表的访问量 select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; # 2. 查询占用bufferpool较多的表 select object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10; # 3. 查看表的全表扫描情况 select * from sys.statements_with_full_table_scans where db='dbname';
语句相关
#1. 监控SQL执行的频率 select db,exec_count,query from sys.statement_analysis order by exec_count desc; #2. 监控使用了排序的SQL select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1; #3. 监控使用了临时表或者磁盘临时表的SQL select db,exec_count,tmp_tables,tmp_disk_tables,query from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;
IO相关
#1. 查看消耗磁盘IO的文件 select file,avg_read,avg_write,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_read limit 10;
Innodb 相关
#1. 行锁阻塞情况 select * from sys.innodb_lock_waits
风险提示:
通过sys库去查询时，MySQL会消耗大量资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。建议生产上不要频繁的去查询sys或者performance_schema、information_schema来完成监控、巡检等工作。
10.小结
查询是数据库中最频繁的操作，提高查询速度可以有效地提高MysQL数据库的性能。通过对查询语句的分析可以了解查询语句的执行能力。找出查询语句的瓶颈，从而优化查询语句。
最后
2022/8/4 15:47
p134~p140
Markdown 91583 字数 2760 行数
HTML 89405 字数 2017 段落
