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

您所在的位置：网站首页 › eq全称 › 性能分析工具：EXPLAIN，trace，sys schema的使用

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

2023-06-26 09:19| 来源: 网络整理| 查看: 265

一、EXPLAIN

每列属性

EXPLAIN四种输出格式

SHOW WARNINGS的使用

二、trace

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

索引相关

表相关

语句相关

IO 相关

Innodb 相关

一、EXPLAIN 每列属性 1.table：每一行记录都对应一个单表2.id：每个SELECT关键字都对应一个唯一的id（有几个select就有几个id）3. select_type4. 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和key7. key_len(重)：主要针对联合索引8. ref：当使用索引列等值查询时，与索引进行等值匹配的对象信息9. rows （重）：预估的需要读取的记录条数，值越小越好10. filtered：经过搜索过滤条件后剩余记录条数百分比，越大越好11. Extra （重）：更准确理解MySQL到底如何执行给定的查询语句

小结

EXPLAIN不考虑各种CacheEXPLAIN不能显示MySQL在执行查询时所作的优化工作EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况部分统计信息是估算的，并非精确值 EXPLAIN四种输出格式传统格式：EXPLAIN SELECT…JSON格式：EXPLAIN FORMAT=JSON SELECT ... 相较于传统格式，增加了成本查询TREE格式：EXPLAIN FORMAT=tree SELECT… TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。可视化输出：可以通过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

【本文地址】

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

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

今日新闻

推荐新闻