MYSQL自带工具Query Profiler的使用（转载）

转自：https://my.oschina.net/jiyayun/blog/85010

介绍

Query Profiler是MYSQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方。通常我们是使用的explain,以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MYSQL 5.0.37以及以上版本中才有实现。

如何打开？

默认的情况下，MYSQL的该功能没有打开，需要自己手动启动。可以通过如下方法查看当前mysql服务器是否开启了该功能。
mysql> show variables like '%profiling%';

 profiling参数值为OFF，说明没有打开该功能。profiling_history_size参数值为15表示，记录最近15次的查询历史。该值可以修改。

打开profiling功能：

 mysql> SET profiling=1;

检验是否打开：

 

如何使用show profiles

1.首先执行一查询语句：
mysql> select * from user;

2.通过show profiles命令查看系统中多个query的概要信息：
mysql> show profiles;

其中Query_ID表示查询ID，也就是个编号，Duration表示对应的query语句执行的时间，单位是秒，query表示具体的query语句。

可以看到刚才执行的select * from user语句执行的时间。

 

3.获取单个query的详细profile信息，可以通过如下语句：

mysql> show profile all for query 26;
mysql> show profile cpu,block io for query 26;//指定看cpu，block，io

+--------------------+----------+----------+------------+--------------+---------------+
| Status             | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------+----------+----------+------------+--------------+---------------+
| starting           | 0.014438 |     NULL |       NULL |         NULL |          NULL |
| Opening tables     | 0.042860 |     NULL |       NULL |         NULL |          NULL |
| System lock        | 0.000007 |     NULL |       NULL |         NULL |          NULL |
| Table lock         | 0.000012 |     NULL |       NULL |         NULL |          NULL |
| init               | 0.000019 |     NULL |       NULL |         NULL |          NULL |
| optimizing         | 0.000005 |     NULL |       NULL |         NULL |          NULL |
| statistics         | 0.000018 |     NULL |       NULL |         NULL |          NULL |
| preparing          | 0.000011 |     NULL |       NULL |         NULL |          NULL |
| executing          | 0.000004 |     NULL |       NULL |         NULL |          NULL |
| Sending data       | 0.000232 |     NULL |       NULL |         NULL |          NULL |
| end                | 0.000007 |     NULL |       NULL |         NULL |          NULL |
| query end          | 0.000005 |     NULL |       NULL |         NULL |          NULL |
| freeing items      | 0.000073 |     NULL |       NULL |         NULL |          NULL |
| logging slow query | 0.000003 |     NULL |       NULL |         NULL |          NULL |
| cleaning up        | 0.000004 |     NULL |       NULL |         NULL |          NULL |
+--------------------+----------+----------+------------+--------------+---------------+
15 rows in set (0.02 sec)
总结：Query Profiler对于SQL性能分析和诊断费用有用。另外，该命令还有如下参数可以选择：

• ALL - displays all information 
• BLOCK IO - displays counts for block input and output operations 
• CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches 
• IPC - displays counts for messages sent and received 
• MEMORY - is not currently implemented 
• PAGE FAULTS - displays counts for major and minor page faults 
• SOURCE - displays the names of functions from the source code, together with the name and line number of the file in which the function occurs 
• SWAPS - displays swap counts 

 

使用SQLyog等工具可以直接看到Profiler页面，输入 Show profiles;语句后会显示更为详细的信息。

 

项目案例：

案例1：一条查询SQL使用此分析，发现Sending data项时间占用很长，基本占到了95%。这项时间长代表的是？

 

实战：MySQL Sending data导致查询很慢的问题详细分析

“Sending data”状态的含义，"正在处理SELECT查询的记录，同时正在把结果发送给客户端"，所谓的“Sending data”并不是单纯的发送数据，而是包括“收集 + 发送 数据”。

这里的关键是为什么要收集数据，原因在于：mysql使用“索引”完成查询结束后，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到“数据行”上将需要返回的数据读取出来返回个客户端。

将目标聚焦在查询语句的返回列上面。

这里的关键信息是：当Innodb的存储格式是 ROW_FORMAT=COMPACT (or ROW_FORMAT=REDUNDANT)的时候，Innodb只会存储前768字节的长度，剩余的数据存放到“溢出页”中。案例中平均一行大约1.5K，也就说大约1/10行会使用“溢出存储”，一旦采用了这种方式存储，返回数据的时候本来是顺序读取的数据，就变成了随机读取了，所以导致性能急剧下降。

 

用MySQL的show processlist;语句查看的时候，发现有好几个sending data出现，都是查询语句引起的。

1）有可能由于key_buffer设置引起的；

2）select语句，怀疑和select有关，查询字段没有建索引，建索引解决；