欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL调优利器【show profiles】

程序员文章站 2022-05-08 12:50:34
...

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 表被锁了