mysql profile - 书闪Bookchips

Show Profile 分析 SQL 执行性能

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

# 查看有没有开启，yes有
select @@have_profiling
# 查看有没有开启，空就是关闭
show profiles;
# 查看有没有开启，OFF就是关闭
mysql> show variables like "profil%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.01 sec)
# 设置开启，0是关闭
set profiling=1;
# 列表的大小，默认是15，最大是100
set profiling_history_size=100;

show profiles 全部SQL

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

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

# SHOW PROFILE 类型 FOR QUERY 第几条sql 
SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]
type: {
    ALL                显示所有信息
  | BLOCK IO           显示块输入和输出操作的数量
  | CONTEXT SWITCHES   显示自愿上下文切换和非自愿上下文切换的数量
  | CPU                显示用户和系统的CPU使用时间
  | IPC                显示已发送和已接收消息（messages）的数量
  | MEMORY             -- 尚未生效
  | PAGE FAULTS        显示主要和次要页面错误的数量
  | SOURCE             显示源代码中函数名称以及该函数所在文件的名称和行号
  | SWAPS              显示SWAP数量
}
例子：
# 查看 SQL 语句在执行过程中线程的每个状态所消耗的时间
show profile ALL for query ID;
show profile ALL for query 1;
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	服务器正在写一个网络数据包

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

Show Profile 分析 SQL 执行性能

show profiles 全部SQL

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

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