MySql学习笔记（五）- 使用ShowProfile分析Sql

上期文档中我们让mysql开启慢查询日志，收集查询时间比较长的sql，进而通过explain指令查询sql的执行过程，explain能够查看sql是那种数据查询方式以及索引情况。但是有时候使用explain还是无法定位到问题sql，所以在mysql5.037版本之后新增了show profiles和show profile语句的支持，通过hava_profileing指令可以查看当前sql是否支持profiles。

profiles默认是关闭的，可以通过set语句在session级别开启profiling

set profiling=1;

通过profle，我们能够更清楚的了解sql的执行过程，例如我们知道myisam表有表元数据的缓存 ，那么对一个myisam表的count（*）就不需要消耗太多的资源。但是对于innodb来说，就没有这种元数据的缓存，count(*)照顾徐不够的就比较慢。

这里我们可以做个实验。

通过show profile for query语句能够看到执行过程中线程的每个状态和消耗的时间。

注意这里的sending data状态表示mysql线程开始访问数据并把结果返回给客户端，而不仅仅是返回结果给客户端。由于在seding data状态下，mysql往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最行的状态。

通过仔细观察，我们发现count(*)的过程中，时间主要消耗在sending data这个状态，为了更清晰的看到排序结果，可以查询information_schema.profiling表并按照时间做倒序；

set @query_id:=9;
select state,
       sum(duration) as total_r,
       round(100*sum(duration)/(select sum(duration) from information_schema.profiling where query_id=@query_id),2) as pct_r,
       count(*) as calls,
       sum(duration)/count(*) as 'r/call'
from information_schema.profiling
where query_id=@query_id
group by state
order  by total_r desc;

在获得最消耗时间的线程之后，mysql进一步选择all、cpu、block io、context switch、page faults等明确类型来查看mysql在使用资源上消耗了过高的时间。例如查看cpu消耗的时间

show profile cpu for query 9;