【MySQL优化(二)】性能监控分析 - Show Profile

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

1. 查看最近的每条SQL总执行时间，默认15条

mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00033900 | select * from films limit 10 |
+----------+------------+---------------------------------+

2. 设置显示数量

# 查看当前监控数量
show variables like 'profiling_history_size';
# 设置监控数量, 最大100, 最小为0(相当于禁用)
set profiling_history_size = 100;

show profile

1. 查看最近一条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)

2. 列字段含义

列	含义
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	服务器正在写一个网络数据包

3. 诊断结果优化

结果	说明
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的使用及分析