MySQL--SHOW PROFILE Syntax

最新推荐文章于 2021-12-04 21:39:25 发布

小曦阳哟

最新推荐文章于 2021-12-04 21:39:25 发布

阅读量521

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql show profile

本文链接：https://blog.youkuaiyun.com/myNameIssls/article/details/79555031

MySQL 专栏收录该内容

7 篇文章

订阅专栏

本文介绍MySQL中的show profile和show profiles命令的使用方法，包括如何启用性能剖析功能、查看SQL语句的资源消耗情况以及如何通过不同参数获取详细的性能信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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”

语法

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type:
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS

使用方法

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

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

mysql>

图示：
这里写图片描述
设置profiling的值：

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

mysql>

图示：
这里写图片描述
由于当前会话已经将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>

图示：
这里写图片描述

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>

图示：
这里写图片描述

也可以通过在INFORMATION_SCHEMA中的PROFILING表来获取profiling的信息，可以参考：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>