MySQL之 show profile 相关总结
MySQL官网show profile介绍:https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
1. 简介
show profile 和 show profiles 命令用于展示SQL语句的资源使用情况,包括CPU的使用,CPU上下文切换,IO等待,内存使用等,这个命令对于分析某个SQL的性能瓶颈非常有帮助,借助于show profile的输出信息,能让我们知道一个SQL在哪个阶段耗时最长,消耗资源最多,从而为SQL优化,提高SQL性能提供重要的依据。
版本情况:
Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。听说MySQL 5.7 版本将会逐渐废弃这个功能,取而代之的是使用Performance Schema。试了一下MySQL8.0,现在也还是支持的。
2. 说明
-
show profiles展示的是简要的耗时信息,如果想了解某个SQL的具体耗时情况,执行show profile 查看。
-
使用show profile之前,先启用profiling, profiling是session级变量,session关闭,该session的profiling信息也会丢失。
-
开启profiling之后,执行几条SQL,然后执行 show profiles 展示最近执行的多个SQL的执行耗时情况,具体能收集多少个SQL,由参数 profiling_history_size 决定,默认值为15,最大值为100。如果设置为0,等同于关闭profiling。
3. show profile 分析sql的大致步骤:
- 确定这个 MySQL 版本是否支持 profile;
- 确定 profile 是否开启,如果未开启,需要手动开启;
- 在服务器端发送要执行的 SQL;
- 查看执行完 SQL 的 query id;
- 通过 query id 查看 SQL 的每个状态及耗时时间;
- 停止profile;
4. 具体使用:
查看是否开启
show variables like "%profiling%";
查询结果:
版本是否支持也可以使用以下命令:
select @@have_profiling;
查询结果:
如果 profiling 是 OFF,需要手动开启
set profiling = 1;
调整show profile单次查询的条数,默认15条,最大100条,设为0,默认关闭profiling
set profiling_history_size = 20
开始分析:
执行需要分析的sql语句:
示例:
SELECT * FROM `test0810`;
SELECT * FROM `test0810` where id = 10;
SELECT * FROM `test0810` group by phone;
执行 show profiles
结果如下:
分析此结果记录中某一条具体的sql的语法:
show profile type 选项:
- all:显示所有的性能开销信息
- block io:显示块 IO 相关的开销信息
- context switches: 上下文切换相关开销
- cpu:显示 CPU 相关的信息
- ipc:显示发送和接收相关的开销信息
- memory:显示内存相关的开销信息
- page faults:显示页面错误相关开销信息
- source:显示和 Source_function、Source_file、Source_line 相关的开销信息
- swaps:显示交换次数的相关信息
语法:
show profile type1,type2.. for query Query_ID
例如:查Query_ID 为188的 cpu,block io
show profile cpu,block io for query 188
查询结果:
返回结果列字段含义
- Status : sql 语句执行的状态
- Duration: sql 执行过程中每一个步骤的耗时
- CPU_user: 当前用户占有的 cpu
- CPU_system: 系统占有的 cpu
- Block_ops_in : I/O 输入
- Block_ops_out : I/O 输出
关于返回结果中不同status的相关说明:
System lock
确认是由于哪个锁引起的,通常是因为MySQL或InnoDB内核级的锁引起的。建议:如果耗时较大再关注即可,一般情况下都还好
Sending data
解释:从server端发送数据到客户端,也有可能是接收存储引擎层返回的数据,再发送给客户端,数据量很大时尤其经常能看见。
备注:Sending Data不是网络发送,是从硬盘读取,发送到网络是Writing to net。建议:通过索引或加上LIMIT,减少需要扫描并且发送给客户端的数据量
Sorting result
正在对结果进行排序,类似Creating sort index,不过是正常表,而不是在内存表中进行排序。
建议:创建适当的索引
Table lock
表级锁,没什么好说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表
create sort index
当前的SELECT中需要用到临时表在进行ORDER BY排序
建议:创建适当的索引
Creating tmp table
创建临时表。先拷贝数据到临时表,用完后再删除临时表。消耗内存,数据来回拷贝删除,消耗时间,建议:优化索引
converting HEAP to MyISAM
查询结果太大,内存不够,数据往磁盘上搬了。建议:优化索引,可以调整max_heap_table_size
Copying to tmp table on disk
把内存中临时表复制到磁盘上,危险!!!建议:优化索引,可以调整tmp_table_size参数,增大内存临时表大小。
.
.
.
.
.
参考内容:https://blog.csdn.net/hnjsjsac/article/details/107320055