MySQL中的profile性能分析工具

最新推荐文章于 2024-05-07 15:36:41 发布

职场江湖指北

最新推荐文章于 2024-05-07 15:36:41 发布

阅读量623

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/BlueLotuss/article/details/108245081

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL中的profile性能分析工具

profile简述

profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量。默认情况下，保存最近15次的运行结果。其默认是关闭，使用前需要开启，可以通过SET profiling = 1命令进行开启。

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

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

使用show profiles命令会显示出最近15次执行的语句，显示的数量可以通过修改profiling_history_size这个会话变量，能够设置的最大值为100，如果这个值为0的话就相当于关上了profiling功能。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NsvjdDCs-1598436866271)(C:\Users\jiangzhe\AppData\Roaming\Typora\typora-user-images\image-20200824165806251.png)]$

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或者show profiles…这类的命令，其他所有执行的所有命令都会被记录下来。比如说show profiling这是一条错误的命令但是也会在profiling list中记录下来。

命令格式

show profile的完整命令格式为：

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
}

直接使用show profile命令显示的结果是最近一条执行命令的资源消耗情况，如果我们加上后面的参数for query n 就会显示执行语句n的具体情况。n所对应的是show profiles结果中的query id。


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)

limit字段时用来限制显示的行数，同时可以加上offset这个可选字段来指定偏移的行数。

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 FOR QUERY 1 limit 2;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| query end          | 0.000107 |
| freeing items      | 0.000008 |
+--------------------+----------+
2 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1 limit 2 offset 2;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
2 rows in set (0.00 sec)

默认情况下，show profile命令为显示状态和所花费的时间这俩部分信息。其中状态和show processlist中显示的状态基本相同，大致有以下几个方面。

General Thread States常用状态

The following list describes thread State values that are associated with general query processing and not more specialized activities such as replication. Many of these are useful only for finding bugs in the server.

下面的列表详细描述了与查询处理有关的线程状态值，这些信息对我们提高查询效率、寻找问题有很大的帮助。

After create

This occurs when the thread creates a table (including internal temporary tables), at the end of the function that creates the table. This state is used even if the table could not be created due to some error.

当创建一张表(包括临时表)之后所处于的状态，即使由于某些错误导致表没有成功创建也会进入这种状态。
altering table

The server is in the process of executing an in-place ALTER TABLE.

线程正在就地修改一张表的内容
Analyzing

The thread is calculating a MyISAM table key distributions (for example, for ANALYZE TABLE).

正在计算MyISAM表中键的分布
checking permissions

The thread is checking whether the server has the required privileges to execute the statement.

线程在进行检查客户机是否有执行该命令的权限。
Checking table

The thread is performing a table check operation.

线程在进行表检查的操作
cleaning up

The thread has processed one command and is preparing to free memory and reset certain state variables.

线程已经完成了一条命令，正准备做一些清理内存和重置状态变量的操作。
closing tables

The thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, verify that you do not have a full disk and that the disk is not in very heavy use.

线程正在将更改后的表数据更新到磁盘并关闭已使用的表。
converting HEAP to ondisk

The thread is converting an internal temporary table from a MEMORY table to an on-disk table.

由于查询过程中形成的临时表太大，线程正在进行将临时表从内存向硬盘上搬的操作。(需要优化)
copy to tmp table

The thread is processing an ALTER TABLE statement. This state occurs after the table with the new structure has been created but before rows are copied into it.

此状态发生在alter table语句中，在创建具有新结构的表之后，但在将行复制到其中之前。
Copying to group table

If a statement has different ORDER BY and GROUP BY criteria, the rows are sorted by group and copied to a temporary table.

如果语句具有不同的“ORDER BY”和“GROUP BY”条件，则按组对行进行排序并将其复制到临时表中。
Copying to tmp table

The server is copying to a temporary table in memory.

线程正在将数据拷贝到一个内存中的临时表。
Copying to tmp table on disk

The server is copying to a temporary table on disk. The temporary result set has become too large (see Section 8.4.4, “Internal Temporary Table Use in MySQL”). Consequently, the thread is changing the temporary table from in-memory to disk-based format to save memory.

线程正在将临时表拷贝到磁盘上，这是因为生成的临时表太大。
Creating index

The thread is processing ALTER TABLE ... ENABLE KEYS for a MyISAM table.

正在为MyISAM表执行ALTER TABLE … ENABLE KEYS操作
Creating sort index

The thread is processing a SELECT that is resolved using an internal temporary table.

正在利用临时表来进行select操作
creating table

The thread is creating a table. This includes creation of temporary tables.

线程正在创建表，包括创建临时表。
Creating tmp table

The thread is creating a temporary table in memory or on disk. If the table is created in memory but later is converted to an on-disk table, the state during that operation will be Copying to tmp table on disk.

在内存或者磁盘上创建临时表
committing alter table to storage engine

The server has finished an in-place ALTER TABLE and is committing the result.

完成对表的就地修改，正在提交结果。
deleting from main table

The server is executing the first part of a multiple-table delete. It is deleting only from the first table, and saving columns and offsets to be used for deleting from the other (reference) tables.

正在进行多表删除的第一部分
deleting from reference tables

The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.

-服务器正在执行多表删除的第二部分，并从其他表中删除匹配的行。
discard_or_import_tablespace

The thread is processing an ALTER TABLE ... DISCARD TABLESPACE or ALTER TABLE ... IMPORT TABLESPACE statement.

执行ALTER TABLE … DISCARD或者ALTER TABLE … IMPORT TABLESPACE语句
end

This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements.

这种状态发生在alter table,clean view,delete,insert,select或者update语句的清理过程发生之前

For the end state, the following operations could be happening:
- Removing query cache entries after data in a table is changed
- Writing an event to the binary log
- Freeing memory buffers, including for blobs
executing

The thread has begun executing a statement.

线程正在开始执行语句。
Execution of init_command

The thread is executing statements in the value of the init_command system variable.

线程正在执行“init_command”系统变量中的语句。
freeing items

The thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up.

线程已执行语句，在此状态下执行的某些项目释放涉及到查询缓存。这种状态之后通常是“cleaning up”。
FULLTEXT initialization

The server is preparing to perform a natural-language full-text search.

正在进行全文本扫描
init

This occurs before the initialization of ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements. Actions taken by the server in this state include flushing the binary log, the InnoDB log, and some query cache cleanup operations.

在alter table,delete,insert,select,update操作初始化之前，在这种状态下执行的操作包括刷新二进制日志、“InnoDB”日志和一些查询缓存清理操作。
Killed

Someone has sent a KILL statement to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. If the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock.

对线程执行kill命令，但是其他拥有该线程锁的线程释放了之后才能起作用
logging slow query

The thread is writing a statement to the slow-query log.

将执行语句写入慢查询日志。
login

The initial state for a connection thread until the client has been authenticated successfully.

客户机登陆成功
manage keys

The server is enabling or disabling a table index.

启用或者关闭索引
Opening tables

The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent opening a table until the statement is finished. It is also worth checking that your table_open_cache value is large enough.

打开表
optimizing

The server is performing initial optimizations for a query.

对查询语句进行优化
preparing

This state occurs during query optimization.

发生在查询优化当中的状态
Purging old relay logs

The thread is removing unneeded relay log files.

清除掉不用的日志文件
query end

This state occurs after processing a query but before the freeing items state.

这种状态发生在处理一条语句之后但是在freeing items状态之前。
Receiving from client

The server is reading a packet from the client.

正在读取来自客户机的数据包
Removing duplicates

The query was using SELECT DISTINCT in such a way that MySQL could not optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.

发生在select distinct语句当中的去重阶段
removing tmp table

The thread is removing an internal temporary table after processing a SELECT statement. This state is not used if no temporary table was created.

清除掉select语句中间生成临时表
rename

The thread is renaming a table.

重命名表
rename result table

The thread is processing an ALTER TABLE statement, has created the new table, and is renaming it to replace the original table.

发生在alter table语句当中，已经创建了新的表，准备将其重新命名来代替旧的表
Reopen tables

The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is trying to reopen it.

线程获得了表的锁，但在获得锁之后，注意到底层表结构发生了变化。于是释放了锁，关闭了表，并试图重新打开它-
Repair by sorting

The repair code is using a sort to create indexes.

修复代码正在使用排序来创建索引
preparing for alter table

The server is preparing to execute an in-place ALTER TABLE.

准备执行alter table的操作
Repair done

The thread has completed a multithreaded repair for a MyISAM table.

已完成对MyISAM表的多线程修复
Repair with keycache

The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.

-修复代码使用通过key cache逐个创建key。这比Repair by sorting慢得多。
Rolling back

The thread is rolling back a transaction.

进行事务的回滚
Saving state

For MyISAM table operations such as repair or analysis, the thread is saving the new table state to the .MYI file header. State includes information such as number of rows, the AUTO_INCREMENT counter, and key distributions.

-对于“MyISAM”表操作（如修复或分析），线程将新表状态保存到“.MYI”文件头中
Searching rows for update

The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.

update语句中寻找满足条件的行
Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

发送数据给客户机，一般是语句中最耗时的阶段
Sending to client

The server is writing a packet to the client. This state is called Writing to net prior to MySQL 5.7.8.

-服务器正在向客户端写入数据包
setup

The thread is beginning an ALTER TABLE operation.

开始alter table的操作
Sorting for group

The thread is doing a sort to satisfy a GROUP BY.

进行group by语句中的排序过程
Sorting for order

The thread is doing a sort to satisfy an ORDER BY.

进行order by语句中的排序过程
Sorting index

The thread is sorting index pages for more efficient access during a MyISAM table optimization operation.

线程正在对索引页进行排序
Sorting result

For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.

对结果进行排序
starting

The first stage at the beginning of statement execution.

语句执行的最开始的阶段
statistics

The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk-bound performing other work.

统计计算查询执行计划
System lock

The thread has called mysql_lock_tables() and the thread state has not been updated since. This is a very general state that can occur for many reasons.

线程调用了“mysql_lock_tables”，此后线程状态一直没有更新。这是一种非常普遍的状态，它的发生有很多原因

For example, the thread is going to request or is waiting for an internal or external system lock for the table. This can occur when InnoDB waits for a table-level lock during execution of LOCK TABLES. If this state is being caused by requests for external locks and you are not using multiple mysqld servers that are accessing the same MyISAM tables, you can disable external system locks with the --skip-external-locking option. However, external locking is disabled by default, so it is likely that this option will have no effect. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it).
update

The thread is getting ready to start updating the table.

线程正在准备更新表的操作
Updating

The thread is searching for rows to update and is updating them.

线程正在检查每一行进行更新数据
updating main table

The server is executing the first part of a multiple-table update. It is updating only the first table, and saving columns and offsets to be used for updating the other (reference) tables.

服务器正在执行多表更新的第一部分。它只更新第一个表，并保存用于更新其他（引用）表的列和偏移量-
updating reference tables

The server is executing the second part of a multiple-table update and updating the matched rows from the other tables.

-服务器正在执行多表更新的第二部分，并从其他表更新匹配的行。
User lock

The thread is going to request or is waiting for an advisory lock requested with a GET_LOCK() call. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it).

请求锁
User sleep

The thread has invoked a SLEEP() call.

线程在进行sleep语句的执行
Waiting for commit lock

FLUSH TABLES WITH READ LOCK is waiting for a commit lock.

等待事务锁
Waiting for global read lock

FLUSH TABLES WITH READ LOCK is waiting for a global read lock or the global read_only system variable is being set.

等待全局读锁
Waiting for tables

The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

线程收到一个通知，表的底层结构已更改，需要重新打开该表以获取新结构。但是，要重新打开表，它必须等到所有其他线程都关闭了该表。
Waiting for table flush

The thread is executing FLUSH TABLES and is waiting for all threads to close their tables, or the thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

线程正在执行[FLUSH TABLES]，并等待所有线程关闭其表，或者线程收到一个通知，表示某个表的基础结构已更改，需要重新打开该表以获取新结构。但是，要重新打开表，它必须等到所有其他线程都关闭了该表。

This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES *tbl_name*, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.
Waiting for *lock_type* lock

服务器正在等待从元数据锁定子系统获取“THR_LOCK”锁或一个锁，其中*LOCK_type*表示锁的类型。

The server is waiting to acquire a THR_LOCK lock or a lock from the metadata locking subsystem, where lock_type indicates the type of lock.

This state indicates a wait for a THR_LOCK:
- Waiting for table level lock
These states indicate a wait for a metadata lock:
- Waiting for event metadata lock
- Waiting for global read lock
- Waiting for schema metadata lock
- Waiting for stored function metadata lock
- Waiting for stored procedure metadata lock
- Waiting for table metadata lock
- Waiting for trigger metadata lock
Waiting on cond

A generic state in which the thread is waiting for a condition to become true. No specific state information is available.

线程等待条件变为真的一种泛型状态。没有可用的特定状态信息。
Writing to net

The server is writing a packet to the network. This state is called Sending to client as of MySQL 5.7.8.

服务器正在向网络写入数据包。

可选字段

可选字段type是用来限制显示的额外信息。常用的有cpu，block io等选项。

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 显示CPU的使用时间
IPC displays counts for messages sent and received 显示消息发送和接收所占整体时间的百分比
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 显示进行页面交换的次数

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)

职场江湖指北

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL中的profile性能分析工具

MySQL中的profile性能分析工具profile简述profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量。默认情况下，保存最近15次的运行结果。其默认是关闭，使用前需要开启，可以通过SET profiling = 1命令进行开启。mysql> SELECT @@profiling;+-------------+| @@profiling |+-------------+| 0 |+-------------+1
复制链接

扫一扫