MySQL中show profile的使用与分析

一、开启show profile功能

1. 查看当前的状态
   是否启用，看看当前的MySQL版本是否支持

   show variables like ‘profiling‘;
   

   默认是关闭，使用前需要开启
   或者

   show variables like ‘profiling%‘;
   

   示例：

   mysql> show variables like ‘profiling‘;
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | profiling     | OFF   |
   +---------------+-------+
   1 row in set, 1 warning (0.00 sec)
   
   mysql> show variables like ‘profiling%‘;
   +------------------------+-------+
   | Variable_name          | Value |
   +------------------------+-------+
   | profiling              | OFF   |
   | profiling_history_size | 15    |
   +------------------------+-------+
   2 rows in set, 1 warning (0.00 sec)
   

2. 开启
   默认该功能是关闭的，使用前需要开启

   set profiling=on;
   

   示例：

   mysql> set profiling=on;
   Query OK, 0 rows affected, 1 warning (0.00 sec)
   
   mysql> show variables like ‘profiling%‘;
   +------------------------+-------+
   | Variable_name          | Value |
   +------------------------+-------+
   | profiling              | ON    |
   | profiling_history_size | 15    |
   +------------------------+-------+
   2 rows in set, 1 warning (0.00 sec)
   

   目前只是临时设置，退出当前连接之后，设置失效。这种很耗性能的操作，没有必要一直开启。

二、运行SQL并查看结果

1. 运行SQL语句
   运行需要测试SQL语句，比如这里我运行的SQL语句如下：

   select item_title from tb_orders group by item_title;
   select sleep(3);
   select * from tb_orders where id=1;
   

2. 查看结果

   mysql> show profiles;
   +----------+------------+------------------------------------------------------+
   | Query_ID | Duration   | Query                                                |
   +----------+------------+------------------------------------------------------+
   |        1 | 0.00151275 | select item_title from tb_orders group by item_title |
   |        2 | 3.00110125 | select sleep(3)                                      |
   |        3 | 0.00091525 | select * from tb_orders where id=1                   |
   +----------+------------+------------------------------------------------------+
   3 rows in set, 1 warning (0.00 sec)
   

   这里的记录了所有sql的执行记录的信息。Duration是该条SQL的运行时间。可以根据这个值来分辨哪个SQL比较耗时。

三、诊断SQL

在上面的粗略结果展示出来之后，我们能看出哪条SQL比较耗时。如果我们想知道耗时具体耗在哪里的话，可以使用SQL诊断获取更多更详细的信息。
我们以Query ID为1的那条语句为例。语法如下：

show profile [选项] for query [上一步前面的问题SQL数字号码];

示例：

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000073 |
| Executing hook on transaction  | 0.000003 |
| starting                       | 0.000004 |
| checking permissions           | 0.000004 |
| Opening tables                 | 0.000146 |
| init                           | 0.000004 |
| System lock                    | 0.000005 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000009 |
| preparing                      | 0.000010 |
| Creating tmp table             | 0.000022 |
| executing                      | 0.000002 |
| Sending data                   | 0.001181 |
| end                            | 0.000003 |
| query end                      | 0.000001 |
| waiting for handler commit     | 0.000005 |
| removing tmp table             | 0.000004 |
| waiting for handler commit     | 0.000002 |
| closing tables                 | 0.000005 |
| freeing items                  | 0.000022 |
| cleaning up                    | 0.000008 |
+--------------------------------+----------+
21 rows in set, 1 warning (0.00 sec)

这里列举了数据库处理你的SQL语句详细步骤，包括每一步骤的耗时。可以参照这些数据对比分析自己的SQL的问题具体出在哪里。
分析的时候，CUP消耗和阻塞IO也是很重要的参考标准，可以增加参数以展示数据，示例如下：

mysql> show profile cpu, block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000073 | 0.000000 |   0.000000 |         NULL |          NULL |
| Executing hook on transaction  | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| starting                       | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions           | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables                 | 0.000146 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                           | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock                    | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing                     | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics                     | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing                      | 0.000010 | 0.000000 |   0.000000 |         NULL |          NULL |
| Creating tmp table             | 0.000022 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing                      | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data                   | 0.001181 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                            | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end                      | 0.000001 | 0.000000 |   0.000000 |         NULL |          NULL |
| waiting for handler commit     | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| removing tmp table             | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| waiting for handler commit     | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables                 | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items                  | 0.000022 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up                    | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
+--------------------------------+----------+----------+------------+--------------+---------------+
21 rows in set, 1 warning (0.00 sec)

参数备注：

四、开发需要注意的事项

1. converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了。
2. Creating tmp table：
   创建临时表
   拷贝数据到临时表
   用完再删除
3. Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！！
4. locked
   出现以上情况的时候，表示你的SQL很耗性能，必须要优化处理了。

MySQL中show profile的使用与分析

原文：https://www.cnblogs.com/hxsen/p/13618842.html