SHOW PROFILE命令详解

SHOW PROFILE是mysql提供分析资源消耗情况的工具，开启它可以让MySQL收集在执行语句的时候所使用的资源。 SHOW PROFILE可以深入的查看服务器执行语句的工作情况，发现SQL耗时到底出现在哪个环节。

1 开启

默认的是关闭的，会话级别可以开启这个功能：

set profiling = 1

查看是否开启：

show variables like "%profil%";

2 show profiles 记录SQL

模拟SQL查询


> select count(*) from customer;  
+----------+  
| count(*) |  
+----------+  
|      199 |  
+----------+

show profiles查看当前session所有已产生的profile

> show profiles

+----------+------------+--------------------------------+  
| Query_ID | Duration   | Query                          |  
+----------+------------+--------------------------------+  
|        1 | 0.00253600 | show variables like '%profil%' |  
|        2 | 0.00138150 | select count(*) from customer  |  
+----------+------------+--------------------------------+  
2 rows in set, 1 warning (0.01 sec)

3 show profile 分析SQL

3.1 参数及使用

官方提供的profile语法

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL                显示所有信息
  | BLOCK IO           显示块IO开销
  | CONTEXT SWITCHES   显示自愿上下文切换和非自愿上下文切换的数量
  | CPU                显示用户和系统的CPU使用时间
  | IPC                显示已发送和已接收消息（messages）的数量
  | MEMORY             显示内存开销信息
  | PAGE FAULTS        显示主要和次要页面错误的数量
  | SOURCE             显示源代码中函数名称以及该函数所在文件的名称和行号
  | SWAPS              显示SWAP数量
}

分析SQL
根据 show profiles列表中的 Query_ID , 选择显示某条记录的性能分析信息

show profile for query 2;  

+----------------------+----------+  
| Status               | Duration |  
+----------------------+----------+  
| starting             | 0.000148 |  
| checking permissions | 0.000014 |  
| Opening tables       | 0.000047 |  
| init                 | 0.000023 |  
| System lock          | 0.000035 |  
| optimizing           | 0.000012 |  
| statistics           | 0.000019 |  
| preparing            | 0.000014 |  
| executing            | 0.000006 |  
| Sending data         | 0.000990 |  
| end                  | 0.000010 |  
| query end            | 0.000011 |  
| closing tables       | 0.000010 |  
| freeing items        | 0.000016 |  
| cleaning up          | 0.000029 |  
+----------------------+----------+

show profile 默认只显示Duration 列总消耗时间，如果要显示更多可以设置 type参数。 [type] = ALL时，显示所有信息。

show profile all返回的字段含义

字段名称	含义
Status	sql 语句执行的状态
DURATION	持续时间，单位s
CPU_USER	用户态CPU时间，单位s
CPU_SYSTEM	系统态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	源代码行数

3.2 根据status分析SQL性能问题

status对应的描述有很多。其中有些可能是SQL有性能问题需要关注。常见的有下列情形

System lock
确认是由于哪个锁引起的，通常是因为MySQL或InnoDB内核级的锁引起的。
建议：如果耗时较大再关注即可，一般情况下都还好

Sending data
解释：从server端发送数据到客户端，也有可能是接收存储引擎层返回的数据，再发送给客户端，数据量很大时尤其经常能看见。备注：Sending Data不是网络发送，是从硬盘读取，发送到网络是Writing to net。
建议：通过索引或加上LIMIT，减少需要扫描并且发送给客户端的数据量

Sorting result
正在对结果进行排序
建议：创建适当的索引

Table lock
表级锁，要么是因为MyISAM引擎表级锁，要么是其他情况显式锁表

create sort index
当前的SELECT中需要用到临时表在进行ORDER BY排序
建议：创建适当的索引

Creating tmp table
创建临时表。先拷贝数据到临时表，用完后再删除临时表。消耗内存，数据来回拷贝删除，消耗时间。
建议：优化索引

Copying to tmp table on disk
把内存中临时表复制到磁盘上
建议：优化索引，可以调整tmp_table_size参数，增大内存临时表大小

4 其他

show profile已经不建议使用，改用Performance Schema。

所有信息可以从INFORMATION_SCHEMA.PROFILING表获取：

SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = query_id;

SHOW PROFILE 官方文档 dev.mysql.com/doc/refman/…

MySQL｜SHOW PROFILE命令详解