MySQL调优之show profile 应用

show profile是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以帮助进行SQL问题的诊断。默认情况下处于关闭状态，并保存最近15次的运行结果。本文测试环境为MySQL8.0.26版本。

查看配置

show variables like '%profiling%'

# 本文这里如下
have_profiling	YES   		//是否支持profile
profiling	OFF       		// OFF表示关闭，默认关闭
profiling_history_size	15   // 记录最近15条语句
1
2
3
4
5
6

可以使用命令set global profiling=ON进行临时开启，如果需要永久生效则在MySQL的配置文件中进行配置。需要注意的是，你可能遇到当前会话配置不生效的情况，那么建议你进行如下开启：

# 全局配置，但是对当前会话不生效
set global profiling=ON;

# 对当前会话生效
set  profiling=ON;
1
2
3
4
5

show profiles查看最近SQL

用来查看最近15条SQL，如下所示主要有Query_ID、Duration以及Query三项。其中Query_ID将在下面详细分析时使用。Duration则表示该条语句执行的时间。

show profile默认查看最新一条SQL执行情况

# Query_ID = 3 
mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000096 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000009 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000043 |
| init                           | 0.000007 |
| System lock                    | 0.000010 |
| optimizing                     | 0.000012 |
| statistics                     | 0.000020 |
| preparing                      | 0.000023 |
| executing                      | 0.000606 |
| end                            | 0.000006 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000009 |
| closing tables                 | 0.000010 |
| freeing items                  | 0.000015 |
| cleaning up                    | 0.000011 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

其与下根据ID查询效果一样的：

mysql> show profile cpu,block io for query 3;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000096 | 0.000086 |   0.000010 |            0 |             0 |
| Executing hook on transaction  | 0.000005 | 0.000005 |   0.000001 |            0 |             0 |
| starting                       | 0.000009 | 0.000008 |   0.000001 |            0 |             0 |
| checking permissions           | 0.000007 | 0.000006 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.000043 | 0.000038 |   0.000005 |            0 |             0 |
| init                           | 0.000007 | 0.000006 |   0.000000 |            0 |             0 |
| System lock                    | 0.000010 | 0.000009 |   0.000001 |            0 |             0 |
| optimizing                     | 0.000012 | 0.000011 |   0.000001 |            0 |             0 |
| statistics                     | 0.000020 | 0.000018 |   0.000003 |            0 |             0 |
| preparing                      | 0.000023 | 0.000020 |   0.000002 |            0 |             0 |
| executing                      | 0.000606 | 0.000606 |   0.000000 |            0 |             0 |
| end                            | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
| query end                      | 0.000005 | 0.000005 |   0.000000 |            0 |             0 |
| waiting for handler commit     | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
| closing tables                 | 0.000010 | 0.000010 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000015 | 0.000015 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000011 | 0.000011 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

查看SQL详细情况

show profile [OPTIONS]  for query Query_ID;

# 如下
show profile cpu,block io   for query 2;
1
2
3
4

选项注释

日常开发需要注意的地方

即当你从上面详细跟踪中看到下面几个，要特别注意。

convertiong heap to myisam : 查询结果太大，内存不够用，数据往磁盘上搬了。

creating tmp table : 创建临时表（拷贝数据到临时表，用完临时表后删除临时表）。

copying to tmp table on disk : 把内存中临时表复制到磁盘，这是一个危险的动作。

locked ： 无需多言，锁的时间比较长

注意，show profile命令将被弃用，我们可以从 information_schema中的profiling数据表进行查看。

Navicat中的应用

其实这一块在Navicat也有简要提示，我们也可以通过Navicat这个图形化界面工具来使用。