Show Profile
show profile 是MySQL提供的分析sql的工具之一,它的作用是记录最近执行的15条sql语句,并记录sql执行时详细的耗时、CPU、内存消耗,可以帮助我们快速定位到sql语句的问题并优化。
show profile默认是关闭的,并且开启后只存活于当前会话,也就说每次使用前都需要开启。
注意:MySQL官方文档声明SHOW PROFILE已被废弃,并建议使用 Performance Schema作为替代品。
语法
SHOW PROFILE [type [, type] ...]
[FOR QUERY n]
[Limit row_count [OFFSET offset]]
type:{
ALL 显示所有的开销信息
BLOCK IO 显示阻塞的输入输出次数(块IO开销)
CONTEXT SWITCHES 显示自愿及非自愿的上下文切换次数
CPU 显示用户与系统CPU使用时间
IPC 显示消息发送与接收的次数
MEMORY 显示内存相关的开销,目前未实现此功能
PAGE FAULTS 显示页错误相关开销信息
SOURCE 列出相应操作对应的函数名及其在源码中的位置(行)
SWAP 显示swap交换次数
查看当前会话是否开启了profile
1表示开启,0表示未开启
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
开启profile
# 查看是否开启了profiling
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.00 sec)
# 开启profiling
mysql> set profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看监控信息
show profiles
- 查看最近的每条SQL总执行时间,默认15条
mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------+
| 1 | 0.00033900 | select * from films limit 10 |
+----------+------------+---------------------------------+
- 设置显示数量
# 查看当前监控数量
show variables like 'profiling_history_size';
# 设置监控数量, 最大100, 最小为0(相当于禁用)
set profiling_history_size = 100;
show profile
- 查看最近一条SQL详细执行时间
mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000043 | 开始
| checking permissions | 0.000006 | 检查权限
| Opening tables | 0.000013 | 打开表
| init | 0.000024 | 初始化
| System lock | 0.000005 | 系统锁
| optimizing | 0.000004 | 查询优化
| statistics | 0.000009 | 统计
| preparing | 0.000008 | 准备
| executing | 0.000002 | 执行
| Sending data | 0.000101 | 发送数据
| end | 0.000003 | 结束
| query end | 0.000005 | 查询结束
| closing tables | 0.000006 | 关闭表
| freeing items | 0.000013 | 释放
| cleaning up | 0.000008 | 清理
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
- 列字段含义
列 | 含义 |
---|---|
Sending data | 线程正在读取和处理一条SELECT语句的行,并且将数据发送至客户端。由于在此期间会执行大量的磁盘访问(读操作),这个状态在一个指定查询的生命周期中经常是耗时最长的,这个字段才是SQL真正运行采集+相应数据的时间,而非executing |
After create | 这个状态当线程创建一个表(包括内部临时表)时,在这个建表功能结束时出现。即使某些错误导致建表失败,也会使用这个状态 |
Analyzing | 当计算MyISAM表索引分布时。(比如进行ANALYZE TABLE时) |
checking permissions | 这个线程检查服务器是否有具有执行该语句的所需权限 |
Checking table | 线程正在执行表检查操作 |
cleaning up | 线程处理一个命令,并正准备释放内存和重置某些状态变量 |
closing tables | 线程正在将变更的表中的数据刷新到磁盘上并正在关闭使用过的表。这应该是一个快速的操作。如果不是这样的话则应该检查硬盘空间是否已满或者硬盘IO是否达到瓶颈 |
converting HEAP to MyISAM | 线程将一个内部临时表转换为磁盘上的MyISAM表 |
copy to tmp table | 线程正在处理一个ALTER TABLE语句。这个状态发生在新的表结构已经创建之后,但是在数据被复制进入之前 |
Copying to group table | 如果一个语句有不同的ORDER BY和GROUP BY条件,数据会被复制到一个临时表中并且按组排序 |
Copying to tmp table | 线程将数据写入内存中的临时表。 正在创建临时表以存放部分查询结果 |
Copying to tmp table on disk | 线程正在将数据写入磁盘中的临时表。临时表的结果集过大。所以线程将临时表由基于内存模式改为基于磁盘模式,以节省内存。但是这个过程会异常的缓慢!! |
Creating index | 线程正在对一个MyISAM表执行ALTER TABLE … ENABLE KEYS语句 |
Creating sort index | 线程正在使用内部临时表处理一个SELECT操作 |
creating table | 线程正在创建一个表,包括创建临时表 |
Creating tmp table | 线程正在创建一个临时表在内存或者磁盘上。如果这个表创建在内存上但是之后被转换到磁盘上,这个状态在运行Copying to tmp table on disk 的时候保持。 |
deleting from main table | 线程正在执行多表删除的第一部分,只从第一个表中删除。并且保存列和偏移量用来从其他(参考)表删除。 |
deleting from reference tables | 线程正在执行多表删除的第一部分,只从第一个表中删除。并且保存列和偏移量用来从其他(参考)表删除。 |
discard_or_import_tablespace | 线程正在执行ALTER TABLE … DISCARD TABLESPACE 或 ALTER TABLE … IMPORT TABLESPACE语句。 |
end | 这个状态出现在结束时,但是在对ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, 或者 UPDATE 语句进行清理之前。 |
executing | 该线程已开始执行一条语句。 |
Execution of init_command | 线程正在执行处于init_command系统变量的值中的语句。 |
freeing items | 线程已经执行了命令。在这个状态中涉及的查询缓存可以得到一些释放。这个状态通常后面跟随cleaning up状态。 |
Flushing tables | 线程正在执行FLUSH TABLES 并且等待所有线程关闭他们的表 |
FULLTEXT initialization | 服务器正在准备进行自然语言全文检索 |
init | 这个状态出现在线程初始化ALTER TABLE, DELETE, INSERT, SELECT, 或 UPDATE语句之前。服务器在这种状态下进行的操作,包括:刷新全日志、InnoDB日志,和一些查询缓存清理操作 |
Killed | 程序对线程发送了KILL语句,并且它应该放弃下一次对KILL标记的检查。这个标记在每一个MySQL的主要循环中被检查,但在某些情况下,它可能需要令线程在很短的时间内死亡。如果这个线程被其他线程锁住了,这个KILL操作会在其他线程释放锁的瞬时执行 |
logging slow query | 这个线程正在将语句写入慢查询日志 |
NULL | 没有操作的状态 |
login | 线程连接的初始状态。直到客户端已经成功验证 |
manage keys | 服务器启用或禁用表索引 |
Opening tables, Opening table | 线程正试图打开一张表 |
optimizing | 服务器执行查询的初步优化 |
preparing | 在查询优化过程中出现这个状态 |
Purging old relay logs | 线程正在移除不必要的中继日志文件 |
query end | 这个状态出现在处理一个查询之后,但是在freeing items状态之前 |
Reading from net | 服务器正在从网络阅读数据包 |
Removing duplicates | 查询正在使用SELECT DISTINCT,这种情况下MySQL不能在早期阶段优化掉一些distinct操作。 因此,MySQL需要一个额外的阶段,在将结果发送到客户端之前删除所有重复的行 |
removing tmp table | 线程正在移除一个内置临时表,在执行一条SELECT语句之后。 如果没有临时表产生,那么这个状态不被使用 |
rename | 线程正在重命名一张表 |
rename result table | 线程正在处理ALTER TABLE语句,创建新的表,并且重命名它来代替原有的表 |
Reopen tables | 线程获得了表锁,但是在取得表锁之后才发现该表的底层结构已经发生了变化。线程释放这个锁,关闭表,并试图重新打开该表 |
Repair by sorting | 修复代码正在使用一个分类来创建索引 |
Repair done | 线程完成一个多线程的MyISAM表的修复 |
Repair with keycache | 修复代码正在通过索引缓存一个接一个地使用创建索引。这比通过分类修复要慢很多 |
Rolling back | 线程正在回滚一个事务 |
Searching rows for update | 线程正在进行第一阶段,在更新前寻找所有匹配的行。如果update正在更改用于查找相关行的索引,则必须这么做 |
setup | 线程正开始进行一个ALTER TABLE操作 |
Sorting for group | 线程正在执行一个由GROUP BY指定的排序 |
Sorting for order | 线程正在执行一个由ORDER BY指定的排序 |
Sorting index | 线程正在对索引页进行排序,为了对MyISAM表进行操作时获得更优的性能 |
Sorting result | 对于一个SELECT语句,这与创建排序索引相似,但是是对非临时表 |
statistics | 服务器计算统计去规划一个查询。如果一个线程长时间处于这个状态,这个服务器的磁盘可能在执行其他工作 |
System lock | 这个线程正在请求或者等待一个内部的或外部的系统表锁。如果这个状态是由于外部锁的请求产生的,并且你没有使用多个正在访问相同的表的MySQL服务器 |
Waiting for table level lock | 系统锁定后的下一个线程状态。线程已获得外部锁并且将请求内部表锁 |
Updating | 线程寻找更新匹配的行并进行更新 |
updating main table | 线程正在执行多表更新的第一部分,只从第一个表中更新。并且保存列和偏移量用来从其他(参考)表更新 |
updating reference tables | 线程正在执行多表更新的第二部分,并从其他表中更新匹配的行 |
User lock | 线程正在请求或等待一个GET_LOCK()调用所要求的咨询锁。对于SHOW PROFILE,这个状态意味这线程正在请求锁。(而非等待) |
User sleep | 线程调用了一个SLEEP() |
Waiting for commit lock | 一个显式或隐式语句在提交时等待释放读锁 |
Waiting for global read lock | 等待全局读锁 |
Waiting for release of readlock | 等待释放读锁 |
Waiting for tables, Waiting for table, Waiting for table flush | 线程获得一个通知,底层表结构已经发生变化,它需要重新打开表来获取新的结构。然而,重新打开表,它必须等到所有其他线程关闭这个有问题的表。这个通知产生通常因为另一个线程对问题表执行了FLUSH TABLES或者以下语句之一:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE |
Waiting for lock_type lock | 等待各个种类的表锁 |
Waiting on cond | 一个普通的状态,线程正在等待一个条件为真。没有特定的状态信息可用 |
Writing to net | 服务器正在写一个网络数据包 |
- 诊断结果优化
结果 | 说明 |
---|---|
ruguoconverting HEAP to MyISAM | 表示查询结果太大,内存不够,数据往磁盘上搬了 |
Creating tmp table | 表示创建临时表。先拷贝数据到临时表,用完后再删除临时表 |
Copying to tmp table on disk | 把内存中临时表复制到磁盘上,危险!!! |
locked | 表示执行时表被锁了 |
如果在show profile诊断结果中出现了以上4条结果中的任何一条,则说明sql语句需要优化。
系列文章
上一篇:【MySQL优化(一)】MySQL的整体架构及SQL的执行过程
下一篇:【MySQL优化(三)】性能监控分析 - Performance Schema
【参考文章】:mysql优化之show profile的使用及分析