性能分析工具：EXPLAIN，trace，sys schema的使用

目录

一、EXPLAIN

每列属性

EXPLAIN四种输出格式

SHOW WARNINGS的使用

二、trace 

三、MySQL监控分析视图-sys schema

索引相关

表相关

语句相关

IO 相关

Innodb 相关

---

一、EXPLAIN

每列属性

• 1.table：每一行记录都对应一个单表
• 2.id：每个SELECT关键字都对应一个唯一的id（有几个select就有几个id）
• 3. select_type
• 4. partitions (可略)
• 5.type(重)：结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext >                                   ref_or_null > index_merge > unique_subquery > index_subquery > range >                             index > ALL                                                                                                                                 SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts                     级别。（阿里巴巴开发手册要求）
• 6. possible_keys和key
• 7. key_len(重)：主要针对联合索引
• 8. ref：当使用索引列等值查询时，与索引进行等值匹配的对象信息
• 9. rows （重）：预估的需要读取的记录条数，值越小越好
• 10. filtered：经过搜索过滤条件后剩余记录条数百分比，越大越好
• 11. Extra （重）：更准确理解MySQL到底如何执行给定的查询语句

小结

• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• 部分统计信息是估算的，并非精确值

EXPLAIN四种输出格式

1. 传统格式：EXPLAIN SELECT…
2. JSON格式：EXPLAIN FORMAT=JSON SELECT ... 相较于传统格式，增加了成本查询
3. TREE格式：EXPLAIN FORMAT=tree SELECT… TREE格式是8.0.16版本之后引入的新格式，主要根据查询的 各个部分之间的关系 和 各部分的      执行顺序 来描述如何查询。
4. 可视化输出：可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标，即可生成可视化的查询计划。

SHOW WARNINGS的使用

在我们使用EXPLAIN 语句连看了某个查询的执行计划后，紧接着还可以使用 SHOW WARNINGS 语句查看与这个查询的执行计划有关的一些扩展信息（可查子查询改造成多表连接）

二、trace 

首先开启trace功能，设置内存大小

SET optimizer_trace="enabled=on",end_markers_in_json=on

set optimizer_trace_max_mem_size=1000000;

然后测试：执行SQL语句

select * from student where id < 10;

最后， 查询 information_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的 ：

select * from information_schema.optimizer_trace\G

三、MySQL监控分析视图-sys schema

sys schema视图使用场景：

索引相关

1. 查询冗余索引：

select * from sys.schema_redundant_indexes;

2. 查询未使用过的索引：

select * from sys.schema_unused_indexes;

 3.查询索引的使用情况

select * from sys.schema_unused_indexes;

表相关

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 * from sys.statements_with_full_table_scans
where db='dbname';

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