MySQL调优利器【show profiles】

show profiles

是mysql提供可用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优测量。

默认情况下，参数处于关闭状态，并保存最近15次的运行结果

分析步骤

1、查看当前版本sql是否支持show profiles

mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+

2、开启该功能

mysql> set global profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

之后重新连接mysql或新建会话生效。

3、随便执行下sql

## sql语句本身没太大含义 只是为了演示
select * from emp group by id limit 150000;
select * from emp group by id order by 5;

4、查看结果show profiles

mysql> show profiles;
+----------+------------+-------------------------------------------------------------+
| Query_ID | Duration   | Query                                                       |
+----------+------------+-------------------------------------------------------------+
|        1 | 0.00492775 | select @@version_comment limit 1                            |
|        2 | 0.00012475 | ues bigdata                                                 |
|        3 | 0.00019650 | SELECT DATABASE()                                           |
|        4 | 0.00989825 | show databases                                              |
|        5 | 0.00027950 | show tables                                                 |
|        6 | 0.00820250 | show variables like 'profiling%'                            |
|        7 | 0.00034350 | select * from emp group by id%10 limit 150000               |
|        8 | 0.00031925 | select id,empno,ename  from emp group by id%10 limit 150000 |
|        9 | 0.13237350 | select id,empno,ename  from emp group by id limit 150000    |
|       10 | 0.00033525 | select id,empno,ename  from emp group by id limit 5         |
+----------+------------+-------------------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)

5、诊断sql

show profile 参数 for query x(x表示show Profiles得到的query_id)

参数介绍:

1、ALL 显示所有的开销信息

2、BLOCK IO 显示块IO相关开销

3、CONTEXT SWITCHES 上下文切换相关开销

4、CPU 显示CPU相关开销信息

5、IPC 显示发送和接收相关开销信息

6、MEMORY 显示内存相关开销信息

7、PAGE FAULTS 显示页面错误相关开销信息

8、SOURCE 显示和Source_function、Source_file、Source_line相关的开销信息

9、SWAPS 显示交换次数相关开销的信息

举例：诊断Query_ID=9的SQL

show profile all for query 9;

mysql> show profile cpu,block io for query 9;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000084 | 0.000054 |   0.000009 |            0 |             0 |
| checking permissions | 0.000007 | 0.000005 |   0.000001 |            0 |             0 |
| Opening tables       | 0.000016 | 0.000014 |   0.000002 |            0 |             0 |
| init                 | 0.000020 | 0.000018 |   0.000003 |            0 |             0 |
| System lock          | 0.000009 | 0.000007 |   0.000001 |            0 |             0 |
| optimizing           | 0.000004 | 0.000004 |   0.000001 |            0 |             0 |
| statistics           | 0.000030 | 0.000025 |   0.000004 |            0 |             0 |
| preparing            | 0.000014 | 0.000012 |   0.000002 |            0 |             0 |
| Sorting result       | 0.000004 | 0.000003 |   0.000000 |            0 |             0 |
| executing            | 0.000002 | 0.000002 |   0.000001 |            0 |             0 |
| Sending data         | 0.132000 | 0.004110 |   0.127747 |            0 |             0 |
| end                  | 0.000018 | 0.000009 |   0.000001 |            0 |             0 |
| query end            | 0.000011 | 0.000009 |   0.000002 |            0 |             0 |
| closing tables       | 0.000011 | 0.000009 |   0.000002 |            0 |             0 |
| freeing items        | 0.000129 | 0.000000 |   0.000129 |            0 |             0 |
| cleaning up          | 0.000016 | 0.000000 |   0.000015 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
16 rows in set, 1 warning (0.00 sec)

其中status表示sql执行生命周期，Duration表示该步骤的耗时

可以看出Sending data耗时较大。

注意:如果status中出现了以下内容，说明sql出现了性能问题

1、converting heap to MyISAM 查询结果太大，内存都不够用了 往磁盘上放

2、Creating tmp table 创建临时表，用完再删(group by 有时也会创建临时表)

3、copying to tmp table on disk 把内存中临时表复制到磁盘

4、locked 表被锁了