【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来完成监控、巡检等工作。
1619
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
