微信登录

监控 - 执行性能监控 - Show Profile

监控 - 执行性能监控 - Show Profile

Show Profile 分析 SQL 执行性能

MySQL 是在 5.0.37 版本之后才支持 Show Profile
Show Profile官方文档地址：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

  1. # 查看有没有开启，yes有
  2. select @@have_profiling
  4. # 查看有没有开启，空就是关闭
  5. show profiles;
  7. # 查看有没有开启，OFF就是关闭
  8. mysql> show variables like "profil%";
  9. +------------------------+-------+
  10. | Variable_name          | Value |
  11. +------------------------+-------+
  12. | profiling              | OFF   |
  13. | profiling_history_size | 15    |
  14. +------------------------+-------+
  15. 2 rows in set (0.01 sec)
  17. # 设置开启，0是关闭
  18. set profiling=1;
  20. # 列表的大小，默认是15，最大是100
  21. set profiling_history_size=100;

show profiles 全部SQL

  1. # 查看Query_ID、总时间、sql
  2. show profiles;

show profile 单条SQL详情 组合查语句

  1. # SHOW PROFILE 类型 FOR QUERY 第几条sql 
  2. SHOW PROFILE [type [, type] ... ]
  3.     [FOR QUERY n]
  4.     [LIMIT row_count [OFFSET offset]]
  6. type: {
  7.     ALL                显示所有信息
  8.   | BLOCK IO           显示块输入和输出操作的数量
  9.   | CONTEXT SWITCHES   显示自愿上下文切换和非自愿上下文切换的数量
  10.   | CPU                显示用户和系统的CPU使用时间
  11.   | IPC                显示已发送和已接收消息（messages）的数量
  12.   | MEMORY             -- 尚未生效
  13.   | PAGE FAULTS        显示主要和次要页面错误的数量
  14.   | SOURCE             显示源代码中函数名称以及该函数所在文件的名称和行号
  15.   | SWAPS              显示SWAP数量
  16. }
  18. 例子：
  19. # 查看 SQL 语句在执行过程中线程的每个状态所消耗的时间
  20. show profile ALL for query ID;
  21. show profile ALL for query 1;
  22. show profile ALL for query 2;
QUERY_ID STATE DURATION CPU_USER CPU_SYSTEM
语句ID 状态 持续时间，单位s 用户态CPU时间，单位s 系统态CPU时间，单位s
CONTEXT_VOLUNTARY CONTEXT_INVOLUNTARY
自愿上下文切换次数 非自愿上下文切换次数
BLOCK_OPS_IN BLOCK_OPS_OUT
块输入次数 块输出次数
MESSAGES_SENT MESSAGES_RECEIVED
发送的消息数量 接收的消息数量
PAGE_FAULTS_MAJOR PAGE_FAULTS_MINOR
主要页面错误数量 次要页面错误数量
SWAPS SOURCE_FUNCTION SOURCE_FILE SOURCE_LINE
交换次数 源代码函数 源代码文件 源代码行数
  • 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表 线程将一个内部临时表转换为磁盘上的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 index 线程正在对一个MyISAM表执行ALTER TABLE … ENABLE KEYS语句。
Creating sort index SELECT操作 线程正在使用内部临时表处理一个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 线程正在执行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 服务器正在写一个网络数据包
监控 - 执行性能监控 - Show Profile