【1】分析优化器执行计划：trace
OPTIMIZER_TRACE是MySQL5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策(比如访问表的方法、各种开销计算、各种转换等)，并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。
此功能默认关闭，需要手动开启。开启trace，并设置格式为json，同时设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
set optimizer_trace='enabled=on',end_markers_in_json=on; set optimizer_trace_max_mem_size=1000000;
使用命令查看开启后的状态：
show VARIABLES like '%optimizer_trace%'
开启后，可以分析如下语句：select、insert、replace、update、delete、explain、set、declare、case、if、return、call。
测试如下：
select * from s1 where id < 10005; explain select * from s1 where id < 10005;
其explain执行结果如下：
SELECT* from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
我们会得到如下结果：
- Query：我们执行的查询语句。
- MISSING_BYTES_BEYOND_MAX_MEM_SIZ ： 跟踪信息过长时，被截断的跟踪信息的字节数。
- INSUFFICIENT_PRIVILEGES：执行跟踪语句的用户是否有查看对象的权限，当不具有权限时，该列信息为1且trace字段为空。一般在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。
- TRACE：也是核心部分，QUERY字段对应语句的跟踪信息。
我们将TRACE拷贝出来，如下所示。
{ "steps": [ { "join_preparation": { # 准备工作 "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `s1`.`id` AS `id`,`s1`.`key1` AS `key1`,`s1`.`key2` AS `key2`,`s1`.`key3` AS `key3`,`s1`.`key_part1` AS `key_part1`,`s1`.`key_part2` AS `key_part2`,`s1`.`key_part3` AS `key_part3`,`s1`.`common_field` AS `common_field` from `s1` where (`s1`.`id` < 10005)" } ]/*steps*/ }/*join_preparation*/ }, { "join_optimization": { # 进行优化 "select#": 1, "steps": [ { "condition_processing": { # 条件处理 "condition": "WHERE", "original_condition": "(`s1`.`id` < 10005)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`s1`.`id` < 10005)" }, { "transformation": "constant_propagation", "resulting_condition": "(`s1`.`id` < 10005)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`s1`.`id` < 10005)" } ]/*steps*/ }/*condition_processing*/ }, { "substitute_generated_columns": {# 替换生成的列 }/*substitute_generated_columns*/ }, { "table_dependencies": [# 表的依赖关系 { "table": "`s1`", "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": "`s1`", "range_analysis": { "table_scan": { "rows": 9895, "cost": 1015.85 }/*table_scan*/, # 扫描表 # 潜在的范围索引 "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "id" ]/*key_parts*/ }, { "index": "idx_key2", "usable": true, "key_parts": [ "key2" ]/*key_parts*/ }, { "index": "idx_key1", "usable": true, "key_parts": [ "key1", "id" ]/*key_parts*/ }, { "index": "idx_key3", "usable": true, "key_parts": [ "key3", "id" ]/*key_parts*/ }, { "index": "idx_key_part", "usable": true, "key_parts": [ "key_part1", "key_part2", "key_part3", "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" }, { "index": "idx_key2", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "idx_key1", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "idx_key3", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "idx_key_part", "usable": false, "cause": "query_references_nonkey_column" } ]/*potential_skip_scan_indexes*/ }/*skip_scan_range*/, # 分析范围选项 "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "id < 10005" ]/*ranges*/, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 3, "cost": 0.561603, "chosen": true }, { "index": "idx_key1", "chosen": false, "cause": "no_valid_range_for_this_index" }, { "index": "idx_key3", "chosen": false, "cause": "no_valid_range_for_this_index" }, { "index": "idx_key_part", "chosen": false, "cause": "no_valid_range_for_this_index" } ]/*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": 3, "ranges": [ "id < 10005" ]/*ranges*/ }/*range_access_plan*/, "rows_for_plan": 3, "cost_for_plan": 0.561603, "chosen": true }/*chosen_range_access_summary*/ }/*range_analysis*/ } ]/*rows_estimation*/ }, { # 考虑执行计划 "considered_execution_plans": [ { "plan_prefix": [ ]/*plan_prefix*/, "table": "`s1`", "best_access_path": { # 最棒访问路径 "considered_access_paths": [ { "rows_to_scan": 3, "access_type": "range", "range_details": { "used_index": "PRIMARY" }/*range_details*/, "resulting_rows": 3, "cost": 0.861603, "chosen": true } ]/*considered_access_paths*/ }/*best_access_path*/, # 行过滤百分比 "condition_filtering_pct": 100, "rows_for_plan": 3, "cost_for_plan": 0.861603, "chosen": true } ]/*considered_execution_plans*/ }, { # 将条件附加到表上 "attaching_conditions_to_tables": { "original_condition": "(`s1`.`id` < 10005)", "attached_conditions_computation": [ ]/*attached_conditions_computation*/, "attached_conditions_summary": [ # 附加条件摘要 { "table": "`s1`", "attached": "(`s1`.`id` < 10005)" } ]/*attached_conditions_summary*/ }/*attaching_conditions_to_tables*/ }, { "finalizing_table_conditions": [ { "table": "`s1`", "original_table_condition": "(`s1`.`id` < 10005)", "final_table_condition ": "(`s1`.`id` < 10005)" } ]/*finalizing_table_conditions*/ }, { "refine_plan": [# 精简计划 { "table": "`s1`" } ]/*refine_plan*/ } ]/*steps*/ }/*join_optimization*/ }, { "join_execution": {# 执行 "select#": 1, "steps": [ ]/*steps*/ }/*join_execution*/ } ]/*steps*/ }
【2】MySQL监控分析视图
关于MySQL的性能监控和问题诊断，我们一般都从performance_schema中去获取想要的数据。在MySQL5.7.7版本中新增sys schema，它将performance_schema 和 information_schema中的数据以更容易理解的方式总结归纳为“视图”，其目的就是为了降低查询performance_schema的复杂度，让DBA能够快速的定位问题。
① 基本介绍
主机相关 ： 以host_summary开头，主要汇总了IO延迟的信息。
InnoDB相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息
IO相关：以IO开头，汇总了等待IO、IO使用量情况。
内存使用情况 ：以memory开头，从主机、线程、事件等角度展示内存的使用情况。
连接与会话信息：processlist和session相关视图，总结了会话相关信息。
表相关：以schema_table开头的视图，展示表的统计信息。
索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。
语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。
用户相关：以user开头的视图，统计了用户使用的文件IO、执行语句统计信息。
等待事件相关信息：以wait开头，展示等待事件的延迟情况。
在sys数据库的视图里面我们可以看到这些具体视图信息。
② Sys schema视图使用场景
① 索引情况
查询冗余索引
select * from sys.schema_redundant_indexes
查询未使用过的索引
select * from sys.schema_unused_indexes
查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='dbname';
② 表相关
查询表的访问量
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;
查询占用bufferpool较多的表
select object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10;
查询表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';
③ 语句相关
监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis order by exec_count desc;
监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 10;
监控使用了临时表或者磁盘临时表的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相关
查看消耗磁盘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相关
行所阻塞情况
select * from sys.innodb_lock_waits;
通过sys库去查询时，MySQL会消耗大量资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。建议生产上不要频繁的去查询sys或者performance_schema information_schame来完成监控、巡检等工作。
