MySQL如何使用profile分析语句性能消耗

共计 20015 个字符，预计需要花费 51 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 MySQL 如何使用 profile 分析语句性能消耗，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

MySQL 使用 profile 分析语句性能消耗

MySQL 可以使用 profile 分析 SQL 语句的性能消耗情况。例如，查询到 SQL 会执行多少时间，并看出 CPU、内存使用量，执行过程中系统锁及表锁的花费时间等信息。

通过 have_profiling 参数可以查看 MySQL 是否支持 profile，通过 profiling 参数可以查看当前系统 profile 是否开启：

查看 profile 是否开启：

mysql show variables like %profil%

+————————+——-+

Variable_name  | Value |

+————————+——-+

have_profiling  | YES  | 
– 当前 MySQL 是否支持 profile

profiling  | OFF  | 
– 开启 SQL 语句剖析功能

profiling_history_size | 15  |  – 设置保留 profiling 的数目，缺省为 15，范围为 0 至 100，为 0 时将禁用 profiling

+————————+——-+

以下是有关 profile 的一些常用命令：

l  set profiling = 1;  #基于会话级别开启，关闭则用 set profiling = off

l  show profile for query 1; #1 是 query_id

l  show profile cpu for query 1; 
#查看 CPU 的消耗情况

l  show profile memory for query 1; 
#查看内存消耗情况

l  show profile block io,cpu for query 1;  #查看 I / O 及 CPU 的消耗情况

命令“show profile for query”的结果中有 Sending data，该状态表示 MySQL 线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。由于在 Sending data 状态下，MySQL 线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

可以使用如下的语句查询 SQL 的整体消耗百分比：

SELECT STATE,

 
SUM(DURATION) AS TOTAL_R,

 
ROUND(100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM
INFORMATION_SCHEMA.PROFILING  WHERE
QUERY_ID = 1),2) AS PCT_R,

 
COUNT(*) AS CALLS,

 
SUM(DURATION) / COUNT(*) AS R/Call

  FROM
INFORMATION_SCHEMA.PROFILING

 WHERE
QUERY_ID = 1

 GROUP BY
STATE

 ORDER BY
TOTAL_R DESC;

profile 是一个非常量化的指标，可以根据这些量化指标来比较各项资源的消耗，有利于对 SQL 语句的整体把控。在获取到最消耗时间的线程状态后，MySQL 支持进一步选择 all、cpu、block io、context switch、page faults 等明细类型来查看 MySQL 在使用什么资源上耗费了过高的时间。

可以通过 show profile source for query 查看 SQL 解析执行过程中每个步骤对应的源码的文件、函数名以及具体的源文件行数：

mysql  
show profile source for query 1;

+—————-+———-+———————–+————–+————-+

Status  | Duration |
Source_function  | Source_file  | Source_line |

+—————-+———-+———————–+————–+————-+

starting   | 0.000118 | NULL  | NULL  |  NULL |

query end  | 0.000008 |
mysql_execute_command | sql_parse.cc | 
4967 |

closing tables | 0.000004 | mysql_execute_command | sql_parse.cc |  5019 |

freeing items  | 0.000010 |
mysql_parse  | sql_parse.cc
|  5593 |

cleaning up  | 0.000012 |
dispatch_command  | sql_parse.cc
|  1902 |

+—————-+———-+———————–+————–+————-+

5 rows in set, 1 warning (0.01 sec)

show profile 能够在做 SQL 优化时帮助 DBA 了解时间都耗费到哪里去了，从 MySQL
5.6 开始，可以通过 trace 文件进一步获取优化器时如何选择执行计划的。

使用示例如下所示：

mysql SELECT @@profiling;

+————-+

| @@profiling |

+————-+

|  0
|

+————-+

1 row in set (0.00 sec)

mysql SET profiling = 1;

Query OK, 0 rows affected (0.00 sec)

mysql DROP TABLE IF EXISTS t1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql CREATE TABLE T1 (id INT);

Query OK, 0 rows affected (0.01 sec)

mysql SHOW PROFILES;

+———-+———-+————————–+

| Query_ID | Duration | Query  |

+———-+———-+————————–+

|  0 | 0.000088 |
SET PROFILING = 1  |

|  1 | 0.000136 |
DROP TABLE IF EXISTS t1  |

|  2 | 0.011947 |
CREATE TABLE t1 (id INT) |

+———-+———-+————————–+

3 rows in set (0.00 sec)

mysql SHOW PROFILE;

+———————-+———-+

| Status  |
Duration |

+———————-+———-+

| checking permissions | 0.000040 |

| creating table  |
0.000056 |

| After create  |
0.011363 |

| query end  |
0.000375 |

| freeing items  |
0.000089 |

| logging slow query  |
0.000019 |

| cleaning up  |
0.000005 |

+———————-+———-+

7 rows in set (0.00 sec)

mysql SHOW PROFILE FOR QUERY 1;

+——————–+———-+

| Status  |
Duration |

+——————–+———-+

| query end  |
0.000107 |

| freeing items  |
0.000008 |

| logging slow query | 0.000015 |

| cleaning up  |
0.000006 |

+——————–+———-+

4 rows in set (0.00 sec)

mysql SHOW PROFILE CPU FOR QUERY 2;

+———————-+———-+———-+————+

| Status  | Duration | CPU_user |
CPU_system |

+———————-+———-+———-+————+

| checking
permissions | 0.000040 | 0.000038 | 
0.000002 |

| creating
table  | 0.000056 | 0.000028 |  0.000028 |

| After create  | 0.011363 | 0.000217 |  0.001571 |

| query end  | 0.000375 | 0.000013 |  0.000028 |

| freeing items  | 0.000089 | 0.000010 |  0.000014 |

| logging slow
query  | 0.000019 | 0.000009 |  0.000010 |

| cleaning up  | 0.000005 | 0.000003 |  0.000002 |

+———————-+———-+———-+————+

需要注意的是，INFORMATION_SCHEMA.PROFILING 和 SHOW PROFILES 在 MySQL
5.7.2 中已经标记为废除，警告信息为：

SHOW PROFILES is deprecated and will be removed
in a future release. Please use Performance Schema instead

INFORMATION_SCHEMA.PROFILING is deprecated and
will be removed in a future release. Please use Performance Schema instead

官网请参考：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html。

说明：

有关 profile 的更多内容可以参考我的博客：http://blog.itpub.net/26736162/viewspace-2135700/。

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html
mysql ? SHOW PROFILES
Name: SHOW PROFILES
Description:
Syntax:
SHOW PROFILES

The SHOW PROFILES statement, together with SHOW PROFILE, displays
profiling information that indicates resource usage for statements
executed during the course of the current session. For more
information, see [HELP SHOW PROFILE].

*Note*:

These statements are deprecated and will be removed in a future MySQL
release. Use the Performance Schema instead; see
http://dev.mysql.com/doc/refman/5.7/en/performance-schema.html.

URL: http://dev.mysql.com/doc/refman/5.7/en/show-profiles.html

mysql ? SHOW PROFILE
Name: SHOW PROFILE
Description:
Syntax:
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

The SHOW PROFILE and SHOW PROFILES statements display profiling
information that indicates resource usage for statements executed
during the course of the current session.

*Note*:

These statements are deprecated and will be removed in a future MySQL
release. Use the Performance Schema instead; see
http://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profili
ng.html.

Profiling is controlled by the profiling session variable, which has a
default value of 0 (OFF). Profiling is enabled by setting profiling to
1 or ON:

mysql SET profiling = 1;

SHOW PROFILES displays a list of the most recent statements sent to the
server. The size of the list is controlled by the
profiling_history_size session variable, which has a default value of
15. The maximum value is 100. Setting the value to 0 has the practical
effect of disabling profiling.

All statements are profiled except SHOW PROFILE and SHOW PROFILES, so
you will find neither of those statements in the profile list.
Malformed statements are profiled. For example, SHOW PROFILING is an
illegal statement, and a syntax error occurs if you try to execute it,
but it will show up in the profiling list.

SHOW PROFILE displays detailed information about a single statement.
Without the FOR QUERY n clause, the output pertains to the most
recently executed statement. If FOR QUERY n is included, SHOW PROFILE
displays information for statement n. The values of n correspond to the
Query_ID values displayed by SHOW PROFILES.

The LIMIT row_count clause may be given to limit the output to
row_count rows. If LIMIT is given, OFFSET offset may be added to begin
the output offset rows into the full set of rows.

By default, SHOW PROFILE displays Status and Duration columns. The
Status values are like the State values displayed by SHOW PROCESSLIST,
although there might be some minor differences in interpretion for the
two statements for some status values (see
http://dev.mysql.com/doc/refman/5.7/en/thread-information.html).

Optional type values may be specified to display specific additional
types of information:

o ALL displays all information

o BLOCK IO displays counts for block input and output operations

o CONTEXT SWITCHES displays counts for voluntary and involuntary
  context switches

o CPU displays user and system CPU usage times

o IPC displays counts for messages sent and received

o MEMORY is not currently implemented

o PAGE FAULTS displays counts for major and minor page faults

o SOURCE displays the names of functions from the source code, together
  with the name and line number of the file in which the function
  occurs

o SWAPS displays swap counts

Profiling is enabled per session. When a session ends, its profiling
information is lost.

URL: http://dev.mysql.com/doc/refman/5.7/en/show-profile.html

Examples:
mysql SELECT @@profiling;
+————-+
| @@profiling |
+————-+
|           0 |
+————-+
1 row in set (0.00 sec)

mysql SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql SHOW PROFILES;
+———-+———-+————————–+
| Query_ID | Duration | Query                    |
+———-+———-+————————–+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+———-+———-+————————–+
3 rows in set (0.00 sec)

mysql SHOW PROFILE;
+———————-+———-+
| Status               | Duration |
+———————-+———-+
| checking permissions | 0.000040 |
| creating table       | 0.000056 |
| After create         | 0.011363 |
| query end            | 0.000375 |
| freeing items        | 0.000089 |
| logging slow query   | 0.000019 |
| cleaning up          | 0.000005 |
+———————-+———-+
7 rows in set (0.00 sec)

mysql SHOW PROFILE FOR QUERY 1;
+——————–+———-+
| Status             | Duration |
+——————–+———-+
| query end          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+——————–+———-+
4 rows in set (0.00 sec)

mysql SHOW PROFILE CPU FOR QUERY 2;
+———————-+———-+———-+————+
| Status               | Duration | CPU_user | CPU_system |
+———————-+———-+———-+————+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating table       | 0.000056 | 0.000028 |   0.000028 |
| After create         | 0.011363 | 0.000217 |   0.001571 |
| query end            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow query   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+———————-+———-+———-+————+
7 rows in set (0.00 sec)

mysql 语句性能开销检测 profiling 详解

之前我介绍过 msyql 查询优化 explain 检查命令的使用，explain 主要是检查 sql 语句的基本性能，sql 是否优秀，但不能查看具体的涉及硬件资源的开销，今天要介绍的这个 profiling 工具可以更细节的查看资源的开销，比较详细。

首先这款性能检查工具是针对每个 session 生效的，session 结束了就要重要发起查询检测。

默认是关闭的，需要手动开启：

SET profiling = 1;

开启之后，发往 mysql 服务器的语句可以通过 SHOW PROFILES 显示出来，默认显示 15 条，最大设置为 100，通过设置变量 profiling_history_size 实现，设置为 0 将会禁用 profiling。

语法

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

关于 type 的定义英文也简单：

ALL displays all information

BLOCK IO displays counts for block input and output operations

CONTEXT SWITCHES displays counts for voluntary and involuntary context switches

CPU displays user and system CPU usage times

IPC displays counts for messages sent and received

MEMORY is not currently implemented

PAGE FAULTS displays counts for major and minor page faults

SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs

SWAPS displays swap counts

使用示例

查看有没有启用 profiling

mysql SELECT @@profiling;

+————-+

| @@profiling |

+————-+

| 0 |

+————-+

1 row in set (0.00 sec)

开启 profiling

mysql SET profiling = 1;

Query OK, 0 rows affected (0.00 sec)

运行要分析的 SQL 语句

mysql DROP TABLE IF EXISTS t1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql CREATE TABLE T1 (id INT);

Query OK, 0 rows affected (0.01 sec)

检查所有抓取到的分析语句性能指标

mysql SHOW PROFILES;

+———-+———-+————————–+

| Query_ID | Duration | Query |

+———-+———-+————————–+

| 0 | 0.000088 | SET PROFILING = 1 |

| 1 | 0.000136 | DROP TABLE IF EXISTS t1 |

| 2 | 0.011947 | CREATE TABLE t1 (id INT) |

+———-+———-+————————–+

3 rows in set (0.00 sec)

显示单个分析语句性能指标，指最近执行次数最多的那一条

mysql SHOW PROFILE;

+———————-+———-+

| Status | Duration |

+———————-+———-+

| checking permissions | 0.000040 |

| creating table | 0.000056 |

| After create | 0.011363 |

| query end | 0.000375 |

| freeing items | 0.000089 |

| logging slow query | 0.000019 |

| cleaning up | 0.000005 |

+———————-+———-+

7 rows in set (0.00 sec)

具体查看某条分析语句的性能

mysql SHOW PROFILE FOR QUERY 1;

+——————–+———-+

| Status | Duration |

+——————–+———-+

| query end | 0.000107 |

| freeing items | 0.000008 |

| logging slow query | 0.000015 |

| cleaning up | 0.000006 |

+——————–+———-+

4 rows in set (0.00 sec)

你也可以查看 CPU 或者其他资源消耗信息

mysql SHOW PROFILE CPU FOR QUERY 2;

+———————-+———-+———-+————+

| Status | Duration | CPU_user | CPU_system |

+———————-+———-+———-+————+

| checking permissions | 0.000040 | 0.000038 | 0.000002 |

| creating table | 0.000056 | 0.000028 | 0.000028 |

| After create | 0.011363 | 0.000217 | 0.001571 |

| query end | 0.000375 | 0.000013 | 0.000028 |

| freeing items | 0.000089 | 0.000010 | 0.000014 |

| logging slow query | 0.000019 | 0.000009 | 0.000010 |

| cleaning up | 0.000005 | 0.000003 | 0.000002 |

+———————-+———-+———-+————+

7 rows in set (0.00 sec)

其他使用方式

也可以通过查表的方式查看分析语句的性能，所有 show 能看到的都会记录在 INFORMATION_SCHEMA 表中，比如：

SELECT STATE, FORMAT(DURATION, 6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2 ORDER BY SEQ;

SHOW 与 INFORMATION_SCHEMA 对应关系表：

INFORMATION_SCHEMA Name
SHOW Name
Remarks
QUERY_ID
Query_ID
SEQ

STATE
Status
DURATION
Duration
CPU_USER
CPU_user
CPU_SYSTEM
CPU_system
CONTEXT_VOLUNTARY
Context_voluntary
CONTEXT_INVOLUNTARY
Context_involuntary
BLOCK_OPS_IN
Block_ops_in
BLOCK_OPS_OUT
Block_ops_out
MESSAGES_SENT
Messages_sent
MESSAGES_RECEIVED
Messages_received
PAGE_FAULTS_MAJOR
Page_faults_major
PAGE_FAULTS_MINOR
Page_faults_minor
SWAPS
Swaps
SOURCE_FUNCTION
Source_function
SOURCE_FILE
Source_file
SOURCE_LINE
Source_line

注意

INFORMATION_SCHEMA 这个表的使用方式已经在 mysql5.7.2 已经标记废除了，在未来的版本将会彻底删除掉，SHOW 的使用方式在未来的版本也会替代掉，替代使用方式为 MySQL Performance Schema，具体的参考官网的使用：https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html

以上 profiling 所有介绍翻译来源于官网，原版可以参考：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

14.7.5.30 SHOW PROFILE Syntax

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

The SHOW PROFILE and SHOW PROFILES statements display profiling information that indicates resource usage for statements executed during the course of the current session.

Note

These statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead; see Section 25.17.1,“Query Profiling Using Performance Schema”.

Profiling is controlled by the profiling session variable, which has a default value of 0 (OFF). Profiling is enabled by setting profiling to 1 or ON:

mysql  SET profiling = 1;

SHOW PROFILES displays a list of the most recent statements sent to the server. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling profiling.

All statements are profiled except SHOW PROFILE and SHOW PROFILES, so you will find neither of those statements in the profile list. Malformed statements are profiled. For example, SHOW PROFILING is an illegal statement, and a syntax error occurs if you try to execute it, but it will show up in the profiling list.

SHOW PROFILE displays detailed information about a single statement. Without the FOR QUERY n clause, the output pertains to the most recently executed statement. If FOR QUERY n is included, SHOW PROFILE displays information for statement n. The values of n correspond to the Query_ID values displayed by SHOW PROFILES.

The LIMIT row_count clause may be given to limit the output to row_count rows. If LIMIT is given, OFFSET offset may be added to begin the output offset rows into the full set of rows.

By default, SHOW PROFILE displays Status and Duration columns. The Status values are like the State values displayed by SHOW PROCESSLIST, although there might be some minor differences in interpretion for the two statements for some status values (see Section 9.14,“Examining Thread Information”).

Optional type values may be specified to display specific additional types of information:

ALL displays all information

BLOCK IO displays counts for block input and output operations

CONTEXT SWITCHES displays counts for voluntary and involuntary context switches

CPU displays user and system CPU usage times

IPC displays counts for messages sent and received

MEMORY is not currently implemented

PAGE FAULTS displays counts for major and minor page faults

SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs

SWAPS displays swap counts

Profiling is enabled per session. When a session ends, its profiling information is lost.

mysql  SELECT @@profiling; +-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql  SET profiling = 1; Query OK, 0 rows affected (0.00 sec)
mysql  DROP TABLE IF EXISTS t1; Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql  CREATE TABLE T1 (id INT); Query OK, 0 rows affected (0.01 sec)
mysql  SHOW PROFILES;

+----------+----------+--------------------------+
| Query_ID | Duration | Query |
+----------+----------+--------------------------+
| 0 | 0.000088 | SET PROFILING = 1 |
| 1 | 0.000136 | DROP TABLE IF EXISTS t1 |
| 2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)
mysql  SHOW PROFILE;

+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table | 0.000056 |
| After create | 0.011363 |
| query end | 0.000375 |
| freeing items | 0.000089 |
| logging slow query | 0.000019 |
| cleaning up | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)
mysql  SHOW PROFILE FOR QUERY 1;

+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| query end | 0.000107 |
| freeing items | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)
mysql  SHOW PROFILE CPU FOR QUERY 2;

+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 | 0.000002 |
| creating table | 0.000056 | 0.000028 | 0.000028 |
| After create | 0.011363 | 0.000217 | 0.001571 |
| query end | 0.000375 | 0.000013 | 0.000028 |
| freeing items | 0.000089 | 0.000010 | 0.000014 |
| logging slow query | 0.000019 | 0.000009 | 0.000010 |
| cleaning up | 0.000005 | 0.000003 | 0.000002 |
+----------------------+----------+----------+------------+
7 rows in set (0.00 sec)

Note

Profiling is only partially functional on some architectures. For values that depend on the getrusage() system call, NULL is returned on systems such as Windows that do not support the call. In addition, profiling is per process and not per thread. This means that activity on threads within the server other than your own may affect the timing information that you see.

You can also get profiling information from the PROFILING table in INFORMATION_SCHEMA. See Section 24.18,“The INFORMATION_SCHEMA PROFILING Table”. For example, the following queries produce the same result:

SHOW PROFILE FOR QUERY 2;
SELECT STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 2 ORDER BY SEQ;

24.18 The INFORMATION_SCHEMA PROFILING Table

The PROFILING table provides statement profiling information. Its contents correspond to the information produced by the SHOW PROFILES and SHOW PROFILE statements (see Section 14.7.5.31,“SHOW PROFILES Syntax”). The table is empty unless theprofiling session variable is set to 1.

Note

This table is deprecated as of MySQL 5.7.2 and will be removed in a future MySQL release. Use the Performance Schema instead; see Chapter 25, MySQL Performance Schema.

INFORMATION_SCHEMA Name SHOW Name Remarks QUERY_ID Query_ID   SEQ
  STATE Status   DURATION Duration   CPU_USER CPU_user   CPU_SYSTEM CPU_system   CONTEXT_VOLUNTARY Context_voluntary   CONTEXT_INVOLUNTARY Context_involuntary   BLOCK_OPS_IN Block_ops_in   BLOCK_OPS_OUT Block_ops_out   MESSAGES_SENT Messages_sent   MESSAGES_RECEIVED Messages_received   PAGE_FAULTS_MAJOR Page_faults_major   PAGE_FAULTS_MINOR Page_faults_minor   SWAPS Swaps   SOURCE_FUNCTION Source_function   SOURCE_FILE Source_file   SOURCE_LINE Source_line  

Notes:

QUERY_ID is a numeric statement identifier.

SEQ is a sequence number indicating the display order for rows with the same QUERY_ID value.

STATE is the profiling state to which the row measurements apply.

DURATION indicates how long statement execution remained in the given state, in seconds.

CPU_USER and CPU_SYSTEM indicate user and system CPU use, in seconds.

CONTEXT_VOLUNTARY and CONTEXT_INVOLUNTARY indicate how many voluntary and involuntary context switches occurred.

BLOCK_OPS_IN and BLOCK_OPS_OUT indicate the number of block input and output operations.

MESSAGES_SENT and MESSAGES_RECEIVED indicate the number of communication messages sent and received.

PAGE_FAULTS_MAJOR and PAGE_FAULTS_MINOR indicate the number of major and minor page faults.

SWAPS indicates how many swaps occurred.

SOURCE_FUNCTION, SOURCE_FILE, and SOURCE_LINE provide information indicating where in the source code the profiled state executes.

以上是“MySQL 如何使用 profile 分析语句性能消耗”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！