介绍

1、Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具,可用于sql调优的测量。默认情况下处于关闭状态,并保存最近15次的运行结果。

分析步骤

开启 show profile

默认情况下,show profile 是关闭的

1
2
3
4
5
6
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1
mysql> set profiling = ON;
1
2
3
4
5
6
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+

运行测试sql

1
2
3
mysql> select * from emp group by id%10;
mysql> select * from emp group by id%20;
mysql> select * from emp group by id%20 order by id;

执行 show profiles

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
mysql> show profiles;
+----------+------------+----------------------------------------------+
| Query_ID | Duration   | Query                                        |
+----------+------------+----------------------------------------------+
|        1 | 0.00201125 | show variables like 'profiling'              |
|        2 | 0.25422725 | select * from emp group by id%10             |
|        3 | 0.00019700 | select * from emp group by id%10 order by 5  |
|        4 | 0.26903550 | select * from emp group by id%20             |
|        5 | 0.26175775 | select * from emp group by id%20 order by id |
|        6 | 0.00027400 | select * from dept                           |
|        7 | 0.00012275 | select * from emp limt 1                     |
|        8 | 0.00025350 | select * from emp limit 1                    |
+----------+------------+----------------------------------------------+

找到目标的 query_id,然后用 show profile * for query query_id

 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
mysql> show profile cpu,block io for query 4;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000090 | 0.000079 |   0.000000 |            0 |             0 |
| checking permissions | 0.000009 | 0.000008 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000022 | 0.000021 |   0.000000 |            0 |             0 |
| init                 | 0.000028 | 0.000029 |   0.000000 |            0 |             0 |
| System lock          | 0.000009 | 0.000008 |   0.000000 |            0 |             0 |
| optimizing           | 0.000005 | 0.000005 |   0.000000 |            0 |             0 |
| statistics           | 0.000020 | 0.000020 |   0.000000 |            0 |             0 |
| preparing            | 0.000011 | 0.000011 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000031 | 0.000031 |   0.000000 |            0 |             0 |
| Sorting result       | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000002 |   0.000000 |            0 |             0 |
| Sending data         | 0.268645 | 0.264669 |   0.000000 |            0 |             0 |
| Creating sort index  | 0.000078 | 0.000068 |   0.000000 |            0 |             0 |
| end                  | 0.000006 | 0.000005 |   0.000000 |            0 |             0 |
| query end            | 0.000010 | 0.000010 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000007 | 0.000007 |   0.000000 |            0 |             0 |
| query end            | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
| closing tables       | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
| freeing items        | 0.000032 | 0.000032 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000017 | 0.000017 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

可以清楚的看到,Creating tmp table表示创建了临时表,然后发送数据消耗的时间最多。

其他常用参数

1
2
3
4
5
6
7
8
9
all : 显示所有的开销信息
block io : 显示块 io 相关开销
context switches : 上下文切换相关开销
cpu : 显示cpu 相关开销信息
ipc : 显示发送和接收文件相关开销信息
memory : 显示内存相关开销信息
page faults : 页面错误相关开销信息
source : 显示和 Source_function,Source_file和Source_line相关的开销信息
swaps : 显示交换次数相关的开销信息

日常注意事项

如果 status列出现以下内容,则说明 sql 有问题
1、converting HEAP to MyISAM:查询结果太大,内存不够,数据往磁盘上搬。
2、Creating tmp table:创建临时表(拷贝数据到临时表,用完再删除)。
3、Copying to tmp table on disk:把内存中的临时表赋值到磁盘上。 4、locked:

遇到的问题

1、group by 出错

1
2
mysql> select * from emp group by id%10 limit 150000;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'study.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
1
2
3
4
5
6
mysql> show global variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

经查询,是因为 sql_mode 开启了ONLY_FULL_GROUP_BY模式,所以MySQL不会识别选择列表、条件或顺序列表引用的查询。 所以修改下 sql_mode 就好了

1
set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

设置完以后,再次执行,确实好了 image-20200228105532705

参考文档

参考文档