扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MySQL如何使用profile分析语句性能消耗
  • 696

0
分享到

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

2024-04-02 19:04:59 696人浏览 安东尼
摘要

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

小编给大家分享一下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分析语句性能消耗”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注编程网数据库频道！

您可能感兴趣的文档:

--结束END--

本文标题: MySQL如何使用profile分析语句性能消耗

本文链接: https://lsjlt.com/news/62439.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • MySQL如何使用profile分析语句性能消耗
    小编给大家分享一下MySQL如何使用profile分析语句性能消耗，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！MySQL使用p...
    99+
    2024-04-02
  • MySQL性能分析工具之PROFILE怎么用
    小编给大家分享一下MySQL性能分析工具之PROFILE怎么用，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ ...
    99+
    2024-04-02
  • Mysql如何使用profile分析sql开销
    小编给大家分享一下Mysql如何使用profile分析sql开销，希望大家阅读完这篇文章后大所收获，下面让我们一起去探讨吧！Mysql使用profile分析sql开销1.使用之前先查看当前数据库的版本信息,...
    99+
    2024-04-02
  • 对mysql语句的性能分析与优化
    1.使用explain，查看查询计划 2.使用show processlist查看查询过程（处于哪个状态），完整命令如下 mysql -uroot -p -e ‘show processlist \G’ |...
    99+
    2022-05-25
    mysql
  • 如何使用MYSQL性能分析器EXPLAIN
    这篇文章主要讲解了如何使用MYSQL性能分析器EXPLAIN，内容清晰明了，对此有兴趣的小伙伴可以学习一下，相信大家阅读完之后会有帮助。使用方法：EXPLAIN SELECT * FROM user...
    99+
    2024-04-02
  • 怎么对MySQL SQL语句性能进行分析
    本篇文章为大家展示了怎么对MySQL SQL语句性能进行分析，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。MySQL SQL语句性能分析 set prof...
    99+
    2024-04-02
  • MyBatis的SQL语句性能分析工具怎么使用
    MyBatis的SQL语句性能分析工具可以帮助开发人员查看和分析SQL语句的性能，以找出潜在的性能问题并进行优化。要使用MyBati...
    99+
    2024-05-08
    MyBatis SQL
  • Oracle常用性能分析语句有哪些
    本篇内容主要讲解“Oracle常用性能分析语句有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Oracle常用性能分析语句有哪些”吧!1、查看数据库中过去1...
    99+
    2024-04-02
  • 如何使用Go语言分析性能瓶颈
    使用 go 语言分析性能瓶颈：使用 pprof 进行 cpu、内存和阻塞分析。通过交互式 web 界面或火焰图可视化分析数据。实践案例：分析 cpu 性能（使用 slowfunction...
    99+
    2024-05-08
    go 性能瓶颈 golang go语言 可视化数据
  • 如何使用VisualVM分析性能
    这篇文章主要讲解了“如何使用VisualVM分析性能”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“如何使用VisualVM分析性能”吧！ 这里需要使用一个...
    99+
    2024-04-02
  • mysql中如何使用show profiles分析sql性能
    这篇文章主要介绍了mysql中如何使用show profiles分析sql性能，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。Show pro...
    99+
    2024-04-02
  • Mysql中如何使用分页limit语句
    Mysql中如何使用分页limit语句，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。已经为我们提供了这样一个功能。 ...
    99+
    2024-04-02
  • MySQL如何利用profiling分析SQL查询语句
    查看profiling是否开启: mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | &...
    99+
    2024-04-02
  • 如何使用pt-stalk分析MySQL的性能波动
    小编给大家分享一下如何使用pt-stalk分析MySQL的性能波动，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 简介 ...
    99+
    2024-04-02
  • 如何使用Go语言测量和分析API性能
    使用 go 语言测量和分析 api 性能的方法：使用 net/http/pprof 测量 http 性能。使用 pprof 工具分析性能剖析。禁用生产环境中的性能剖析。使用适当的采样率。...
    99+
    2024-05-08
    go语言 api性能
  • MySQL 如何分析查询性能
    查询优化、索引优化和表设计优化是环环相扣的。如果你有丰富的编写MySQL查询语句的经验，你就会知道如何设计表和索引来支持有效的查询。同样的，知晓表设计同样有助于了解表结构如何对查询语句产生影响。因此，即便表设计和索...
    99+
    2022-05-12
    MySQL 查询性能 MySQL 查询性能分析
  • MySQL DQL语句如何使用
    本篇内容主要讲解“MySQL DQL语句如何使用”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL DQL语句如何使用”吧!DQLDQL英文全称是Data Query ...
    99+
    2023-07-05
  • 如何使用systemtap调试工具分析MySQL的性能
    这篇文章将为大家详细讲解有关如何使用systemtap调试工具分析MySQL的性能，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。一、 分析SQL语句在执行各个阶段的消耗比...
    99+
    2024-04-02
  • 性能分析工具Systrace如何使用
    这篇“性能分析工具Systrace如何使用”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“性能分析工具Systrace如何使用...
    99+
    2023-07-05
  • SQL如何使用分类语句
    这期内容当中小编将会给大家带来有关SQL如何使用分类语句，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。SQL的分类　　Ø数据定义语言：简称DDL(DataDefiniti...
    99+
    2024-04-02
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作