MySQL之 show profile 相关总结

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