背景
在旧版本的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花费的时间占比,如何优化以提升执行效率。