MySQL--SHOW PROFILE Syntax

客官°小女子只卖身不卖艺 2022-05-29 01:18 145阅读 0赞

# MySQL–SHOW PROFILE Syntax #

## 概述 ##

`show profile` 和 `show profiles` 语句是用来显示在当前会话过程中执行的SQL语句的资源使用情况的概要信息。

注意：`show profile` 和 `show profiles` 在将来的MySQL版本中被弃用，并将被删除。会被`Performance Schema` 所取代，详细信息可以参考：[Section 25.18.1, “Query Profiling Using Performance Schema”][Section 25.18.1_ _Query Profiling Using Performance Schema]

## 语法 ##

## 使用方法 ##

`profiling` 是由 `profiling` 会话变量控制的，它的默认值为0（OFF）。通过将`profiling` 设置为1或者ON来启用`profiling`。  
查看`profiling`的值：

mysql> select @@profiling; +-------------+
    | @@profiling | +-------------+
    | 1 | +-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql>

图示：  
![这里写图片描述][Image 1]  
设置`profiling`的值 ：

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

图示：  
![这里写图片描述][Image 1]  
由于当前会话已经将`profiling`的值设置为1了，所以结果显示0行受影响。

`SHOW PROFILES`显示发送到服务器的最近语句的列表。列表的大小由`profiling_history_size`会话变量控制，该变量的缺省值为15。最大值是100。将值设置为0具有禁用`profiling`的实际效果。  
除了`SHOW PROFILES`和`SHOW PROFILE`两条语句外，其它的语句都会在`profiles`列表中找到。  
畸形的语句是异形。例如：`SHOW PROFILING`是非法语句，如果您尝试执行它，会出现语法错误，但是它会出现在`profiles`列表中。  
示例：

mysql> show profiling;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use near 'profi ling' at line 1
    mysql> show profiles; +----------+------------+------------------------+
    | Query_ID | Duration | Query | +----------+------------+------------------------+
    |        1 | 0.00087725 | show tables            |
    |        2 | 0.00279900 | select * from customer |
    |        3 | 0.00008525 | show profiling         |
    |        4 | 0.00014550 | select @@profiling     |
    |        5 | 0.00063125 | set profiling=1        |
    | 6 | 0.00007825 | show profiling | +----------+------------+------------------------+
    6 rows in set, 1 warning (0.00 sec)
    
    mysql>

图示：  
![这里写图片描述][Image 1]

`show profile`显示了关于单个语句的详细信息。没有使用 `for query n`语句，那么输出的是最近执行的语句。如何使用`for query n`语句，就会显示出语句n的执行信息。n的值对应通过`show profiles`显示出来的`Query_ID`的值。

可以指定可选类型值来显示特定的附加信息类型

*  ALL 显示所有的信息
 *  BLOCK IO 显示块输入 、输出操作的计数值
 *  CONTEXT SWITCHES 显示上下文切换的计数值
 *  CPU 显示用户和系统CPU使用时间
 *  IPC 显示对发送和接收的消息的计数
 *  MEMORY 目前还没有实现
 *  PAGE FAULTS显示主要和次要页面错误的计数
 *  SOURCE 显示来自源代码的函数名，以及函数发生的文件的名称和行号。
 *  SWAPS 显示交换数量值

`profiling`启用一个会话，当会话结束的时候，`profiling`信息也将会丢失。  
示例：

mysql> use sakila;
    Database changed
    mysql> select @@profiling; +-------------+
    | @@profiling | +-------------+
    | 0 | +-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> set profiling = 1;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> select count(*) from customer; +----------+
    | count(*) | +----------+
    | 599 | +----------+
    1 row in set (0.01 sec)
    
    mysql> select count(*) from payment; +----------+
    | count(*) | +----------+
    | 16049 | +----------+
    1 row in set (0.01 sec)
    
    mysql> show profiling;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use near 'profi ling' at line 1
    mysql> show profiles; +----------+------------+-------------------------------+
    | Query_ID | Duration | Query | +----------+------------+-------------------------------+
    |        1 | 0.01002050 | select count(*) from customer | | 2 | 0.00543075 | select count(*) from payment  |
    | 3 | 0.00009625 | show profiling | +----------+------------+-------------------------------+
    3 rows in set, 1 warning (0.00 sec)
    
    mysql> show profile query for query 2;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use near 'query for query 2' at line 1
    mysql> show profile for query 2; +----------------------+----------+
    | Status | Duration | +----------------------+----------+
    | starting             | 0.000055 |
    | checking permissions | 0.000005 |
    | Opening tables       | 0.001799 |
    | init                 | 0.000021 |
    | System lock          | 0.000009 |
    | optimizing           | 0.000005 |
    | statistics           | 0.000015 |
    | preparing            | 0.000011 |
    | executing            | 0.000002 |
    | Sending data         | 0.003402 |
    | end                  | 0.000005 |
    | query end            | 0.000006 |
    | closing tables       | 0.000007 |
    | freeing items        | 0.000079 |
    | cleaning up | 0.000011 | +----------------------+----------+
    15 rows in set, 1 warning (0.00 sec)
    
    mysql> show profile cpu for query 2; +----------------------+----------+----------+------------+
    | Status | Duration | CPU_user | CPU_system | +----------------------+----------+----------+------------+
    | starting             | 0.000055 | 0.000000 |   0.000000 |
    | checking permissions | 0.000005 | 0.000000 |   0.000000 |
    | Opening tables       | 0.001799 | 0.000000 |   0.015600 |
    | init                 | 0.000021 | 0.000000 |   0.000000 |
    | System lock          | 0.000009 | 0.000000 |   0.000000 |
    | optimizing           | 0.000005 | 0.000000 |   0.000000 |
    | statistics           | 0.000015 | 0.000000 |   0.000000 |
    | preparing            | 0.000011 | 0.000000 |   0.000000 |
    | executing            | 0.000002 | 0.000000 |   0.000000 |
    | Sending data         | 0.003402 | 0.000000 |   0.000000 |
    | end                  | 0.000005 | 0.000000 |   0.000000 |
    | query end            | 0.000006 | 0.000000 |   0.000000 |
    | closing tables       | 0.000007 | 0.000000 |   0.000000 |
    | freeing items        | 0.000079 | 0.000000 |   0.000000 |
    | cleaning up | 0.000011 | 0.000000 | 0.000000 | +----------------------+----------+----------+------------+
    15 rows in set, 1 warning (0.00 sec)
    
    mysql>

图示：  
![这里写图片描述][Image 1]  
![这里写图片描述][Image 1]  
![这里写图片描述][Image 1]  
![这里写图片描述][Image 1]  
![这里写图片描述][Image 1]

也可以通过在`INFORMATION_SCHEMA`中的`PROFILING`表来获取`profiling`的信息，可以参考：[Section 24.18, “The INFORMATION\_SCHEMA PROFILING Table”][Section 24.18_ _The INFORMATION_SCHEMA PROFILING Table]

示例：

mysql> show profile for query 2;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting | 0.000055 |
    | checking permissions | 0.000005 |
    | Opening tables       | 0.001799 |
    | init | 0.000021 |
    | System lock          | 0.000009 |
    | optimizing | 0.000005 |
    | statistics | 0.000015 |
    | preparing | 0.000011 |
    | executing | 0.000002 |
    | Sending data         | 0.003402 |
    | end | 0.000005 |
    | query end | 0.000006 |
    | closing tables | 0.000007 |
    | freeing items | 0.000079 |
    | cleaning up | 0.000011 |
    +----------------------+----------+
    15 rows in set, 1 warning (0.00 sec)
    
    mysql> select state, format(duration,6) as duration from information_schema.prof
    iling where query_id = 2 order by seq;
    +----------------------+----------+
    | state | duration |
    +----------------------+----------+
    | starting | 0.000055 |
    | checking permissions | 0.000005 |
    | Opening tables       | 0.001799 |
    | init | 0.000021 |
    | System lock          | 0.000009 |
    | optimizing | 0.000005 |
    | statistics | 0.000015 |
    | preparing | 0.000011 |
    | executing | 0.000002 |
    | Sending data         | 0.003402 |
    | end | 0.000005 |
    | query end | 0.000006 |
    | closing tables | 0.000007 |
    | freeing items | 0.000079 |
    | cleaning up | 0.000011 |
    +----------------------+----------+
    15 rows in set, 1 warning (0.01 sec)
    
    mysql>

图示：  
![这里写图片描述][Image 1]  
![这里写图片描述][Image 1]

参考链接：[https://dev.mysql.com/doc/refman/5.7/en/show-profile.html][https_dev.mysql.com_doc_refman_5.7_en_show-profile.html]

[Section 25.18.1_ _Query Profiling Using Performance Schema]: https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html
[Image 1]: 
[Section 24.18_ _The INFORMATION_SCHEMA PROFILING Table]: https://dev.mysql.com/doc/refman/5.7/en/profiling-table.html
[https_dev.mysql.com_doc_refman_5.7_en_show-profile.html]: https://dev.mysql.com/doc/refman/5.7/en/show-profile.html