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)

参数备注：

type	说明
ALL	显示所有的开销信息
BLOCKIO	显示块℃相关开销
CONTEXTSWITCHES	上下文切换相关开销
CPU	显示CPU相关开销信息
IPC	显示发送和接收相关开销信息
MEMORY	显示内存相关开销信息
PAGE FAULTS	显示页面错误相关开销信息
SOURCE	显示和Sourcefunction，Source蒯e，Source
SWAPS	显示交换次数相关开销的信息

四、开发需要注意的事项

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