Profiling in MYSQL

本文介绍MySQL中性能剖析功能的使用方法,包括如何开启和关闭性能剖析,如何查看最近执行的SQL语句及其资源消耗详情。

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

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


13.7.5.29. SHOW PROFILES 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 PROFILES and SHOW PROFILE statements display profiling information that indicates resource usage for statements executed during the course of the current session.

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 master. 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 PROFILES and SHOW PROFILE, 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 8.10, “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.

SHOW PROFILES and SHOW PROFILE were added in MySQL 5.0.37.

You can also get profiling information from the PROFILING table in INFORMATION_SCHEMA. See Section 19.7, “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;
Important

Please note that the SHOW PROFILE and SHOW PROFILES functionality is part of the MySQL 5.0 Community Server only.



<think>好的,用户想了解如何配置和使用MySQLprofiling功能来进行性能分析。首先,我需要确认用户提到的profiling是什么,以及它在不同MySQL版本中的支持情况。根据引用[4],ProfilingMySQL自5.0.37版本起支持的工具,可以分析SQL的执行时间、锁时间等资源消耗。不过引用[3]提到,从MySQL8.0开始,INFORMATION_SCHEMA.PROFILING表被废弃,建议改用performance_schema中的相关表,但暂时用户可能还在使用旧版本,所以需要涵盖新旧版本的方法。 接下来,配置步骤。用户需要先启用profiling。根据引用[1]和[4],可以通过设置session级别的profiling参数为1来开启。可能需要指导用户如何检查当前版本,因为不同版本的操作可能不同。例如,在旧版本中使用SHOW VARIABLES LIKE '%profiling%';查看状态,然后SET profiling = 1;启用。 然后是具体使用步骤,比如执行SQL语句后用SHOW PROFILES;查看所有已分析的查询,再通过SHOW PROFILE FOR QUERY 1;来查看具体细节。引用[5]中提到const和system类型的优化情况,可能需要在分析结果时结合EXPLAIN来理解执行计划。 还需要注意,在MySQL8.0及以上版本,profiling相关的命令会被废弃,所以需要提醒用户如果使用新版本,可能需要转向performance_schema,如引用[3]所述。可能需要简单介绍如何在新版本中使用替代工具,比如设置setup_actors表或使用performance_schema中的事件监控。 另外,用户可能想知道如何解读profiling的结果,例如哪些指标是关键,比如CPU时间、IO等待、锁时间等。引用[2]提到profiling可以统计CPU、I/O等资源消耗,需要解释这些指标的意义,帮助用户定位瓶颈。 最后,生成相关问题时要覆盖用户可能进一步的问题,比如如何分析结果、优化方法、版本差异等,确保用户有后续的学习路径。</think>### 如何配置和使用 MySQL profiling 进行性能分析? #### 1. **配置 MySQL Profiling** MySQL Profiling 功能默认关闭,需手动开启: ```sql -- 查看当前 profiling 状态 SHOW VARIABLES LIKE '%profiling%'; -- 启用 profiling(会话级别) SET SESSION profiling = 1; ``` - 参数说明: - `profiling_history_size`:控制保存的 SQL 分析记录数量,默认 15[^4]。 #### 2. **使用 Profiling 分析 SQL 性能** **步骤 1:执行目标 SQL** ```sql SELECT * FROM users WHERE id = 1; ``` **步骤 2:查看已记录的 SQL 列表** ```sql SHOW PROFILES; -- 显示所有已分析的 SQL 及其执行时间 ``` 输出示例: | Query_ID | Duration | Query | |----------|------------|----------------| | 1 | 0.00012300 | SELECT ... | **步骤 3:查看具体 SQL 的详细性能数据** ```sql SHOW PROFILE FOR QUERY 1; -- 查看 Query_ID=1 的详细资源消耗 ``` 输出包含以下关键指标: - `CPU_user`:用户态 CPU 时间 - `Block_ops_in`:磁盘读取次数 - `Context_switches`:上下文切换次数 - `System_lock`:表锁等待时间[^2] #### 3. **结合 EXPLAIN 进一步优化** Profiling 可定位耗时环节,而 `EXPLAIN` 可分析执行计划: ```sql EXPLAIN SELECT * FROM users WHERE id = 1; ``` - 重点关注 `type` 字段(如 `const` 表示通过主键快速定位,效率高)[^5]。 #### 4. **注意事项** - **MySQL 8.0+ 版本**:`SHOW PROFILE` 和 `INFORMATION_SCHEMA.PROFILING` 已废弃,需改用 `performance_schema` 中的事件分析表[^3]。 - **生产环境慎用**:Profiling 会增加服务器负载,建议在测试环境使用。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值