MySQL使用 performance schema 剖析单个查询语句

背景

在旧版本的MySQL中（5.5及以前）基本上都使用 SHOW PROFILES 来检查某个SQL执行时的各项操作时间占比，在5.7之后的版本逐步被 performance schema 所替代，在新版本中使用 PROFILE 的相关操作会看到对应的已弃用提示。

show profiles;
set profiling = 1;

下面简单记录下如何使用 performance schema 来得到类似 PROFIEL 的效果。

启用检查

performance schema 是一个存储引擎，先查看当前版本是否支持该存储引擎。

show engines;

可以看到对应的 PERFORMANCE_SCHEMA 是否支持。

 performance schema 在 MySQL 5.7 及之后的版本默认都是开启的，之前的则没有，可以使用以下命令查看是否启用。

show variables like 'performance_schema';

如果该值未显示ON，则可以在启动文件中加上 " performance_schema=ON " 然后重新启动 MySQL 服务来启动 performance schema。

设置初始化

performance schema 默认的配置不足以满足查看对应查询语句各项操作占比的需求，所以下面将更新该引擎下一些表的配置。

setup_actors

        该表用于判断是否对当前进入服务的线程进行监控，默认对所有的用户线程都进行监控。为避免其它线程的干扰，可以禁用默认配置，然后插入新的用户配置数据。

update performance_schema.setup_actors set ENABLED = 'NO', HISTORY = 'NO' 
where HOST = '%';
insert into performance_schema.setup_actors (HOST, USER, ROLE, ENABLED, HISTORY) 
VALUES ('localhost', 'root', '%', 'YES', 'YES');

setup_instruments

        监控开关，控制监控哪些事件，语句执行主要关注 statement/* 和 stage/*，分别表示统计语句维度的信息以及语句执行各阶段的信息，需全部启用。

update performance_schema.setup_instruments set ENABLED = 'YES', TIMED = 'YES' 
where name like 'statement/%' or name like 'stage/%';

setup_consumers

        收集的运行信息是否写入对应表格开关，与 setup_instruments 一样将 statement 和 stage 相关的表格配置为写入。

update performance_schema.setup_consumers set ENABLED = 'YES' 
where name like 'events_statements%' or name like 'events_stages%';

设置到这里就结束了，接着开始执行SQL，查看执行记录。

执行和收集

执行要剖析的SQL

select u.username, d.dept_name, p.position_name
from t_user u
left join t_dept d using (dept_id)
left join t_position p using (position_id);

查询执行的事件，可以对执行语句添加过滤条件以快速定位刚刚的执行语句。

select EVENT_ID, EVENT_NAME, format(TIMER_WAIT / 1000000000000, 8) as time_wait, SQL_TEXT 
from performance_schema.events_statements_history_long where SQL_TEXT like '%t_user%';

其中原始数据的时间都是以皮秒为单位（万亿分之一秒），SQL_TEXT 为执行的语句。

取到 EVENT_ID 后再从 events_stages_history_long 中查询对应的详细执行操作信息。

select EVENT_ID, EVENT_NAME, format(TIMER_WAIT / 1000000000000, 8) as time_wait 
from performance_schema.events_stages_history_long where NESTING_EVENT_ID = 1136;

通过上图可以查看到对应的执行步骤以及对应操作占用的时间，以此可以分析SQL花费的时间占比，如何优化以提升执行效率。