1、是什么

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优测量，默认情况下，参数处于关闭状态，并保存最近15次的运行结果。它比执行计划（Explain）更加细粒度的对sql进行分析。

2、怎么玩

2.1、查看状态

默认是关闭的，需要查看当前MySQL是否支持该功能，使用下面命令查看：

1
show variables like 'profiling%'

图片

其中profiling_history_size是记录最近多少次，默认是15次。

2.2、开启功能

使用下面命令，开启profiling功能：

1
set profiling=on

图片

2.3、执行sql

我们使用《批量插入脚本》这一章的创建的库，然后执行下面两条sql（注意每条sql执行多次）：

1
2
select * from emp group by id%10 limit 10000
select * from emp group by id%20 order by 5

2.4、分析sql

2.4.1、查询执行结果

查看只开启该功能后，所有sql的执行结果：

1
show profiles

图片
可以看到结果，我们在开启功能后，执行的12条sql都在此，其中Query_ID是执行sql的id，Duration表当前sql执行所花费的时间；而Query表示当前执行的sql语句。

2.4.2、诊断sql

我们可以对Duration话费时间长的sql做进一步的诊断，如上图我们看到第5条sql执行的时间最长（当然这里虽然是大数据表，我这里插入的数据也不多，所有总体时间看起来还算可以），使用下面命令进一步诊断（语句最后的5是sql的Query_ID）：

1
show profile cpu,block io for query 5

图片

这就是这条sql执行的全过程，一览无余，细心的小伙伴也许注意到了其中有Creating tmp table 、Coping to tmp table、removing tmp table 。在执行这条sql的时候创建临时表，复制到临时表，然后又删除临时表。哈哈，其中大部分时间都花在了Coping to tmp table上，主要看Duration上的值，当然其他的CPU_user(用户cpu)、CPU_system（系统cpu）、Block_ops_in/Block_ops_out（I/O的输入输出）也是一个重要的指标。
当然除了cpu,block io两个参数外，还有以下这些参数：

ALL – 显示所有开销信息，如show profile all for query 5
BLOCK IO – 显示块IO相关开销
CONTEXT SWITCHES – 上下问切换相关开销
CPU – 显示CPU相关开销信息
IPC – 显示发生和接收相关开销
MEMORY –显示内存错误相关开销信息
PAGE FAULTS –显示页面错误相关开销信息
SOURCE –显示和Source_function,Source_file,Source_line相关开销信息
SWAPS –显示交换次数相关开销的信息

2.4.3、结论

可能大家和我一样，看到2.4.2对第5条sql的执行结果，看着有22行，也就是22个执行过程，这还是其中一部分，这么多怎么记得住，其实大家也不必记住那么多，只需要记住以下几个就可以，如果出现下面这几个，那么恭喜你“中奖”了，赶紧偷偷优化你的sql吧，别被老板或者项目经理逮到，否则有你好受的：

①、converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了；
②、Create tmp table 创建临时表，往往伴随着Coping to tmp table、removing tmp table的存在，一般会出现在group by语句中。
③、Copying to tmp on disk 把内存中临时表复制到磁盘，这是非常危险，危险，危险的，重要的事情说三遍。
④、locked 锁

3、全局查询日志

除了profile,myql还提供了一个好玩的东西，就是全局查询日志，切记，切记，不要在生产环境中使用，因为它也会对系统性能照成一定的影响，开启方式有两种：
①、在my.cnf（Windows下是my.ini）配置文件中[mysqld]下加上如下两个配置：

1
2
3
4
5
6
#开启全局查询日志
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式为文件
log_output=FILE

②、使用命令的形式，此后，我们编写的sql语句将会记录到mysql库里的general_log表：

1
2
set global general_log=1
set global log_output='TABLE'

这里我们使用第②中方式：
图片

然后我们随便执行几条sql语句，然后使用下面sql查看结果：

1
select * from mysql.general_log

图片
最后在啰嗦一句，切记不要在生产上开启全局查询日志，在测试环境中可以使用来抓取一些常用的sql进行分析。

最后更新： 2019年07月08日 09:17

原始链接： https://www.sunnymaple.cn/2019/07/08/show profile/

赏
× 请我吃糖~
打赏二维码