mysql语句执行耗时分析

Performance Schema查看SQL执行各阶段耗时

配置收集哪些用户的SQL执行信息

查看搜集哪些用户的SQL执行历史信息：

select * from performance_schema.setup_actors;

限制搜集SQL执行历史信息的用户为本地root用户连接（根据实际需求设置）：

update performance_schema.setup_actors 
set enabled='NO', history='NO' 
where host='%' and user='%';

insert into performance_schema.setup_actors (host,user,role,enabled,history) 
values('localhost','root','%','YES','YES');

select * from performance_schema.setup_actors;

开启SQL执行信息收集的相关特性

有些选项是默认打开的，生产改动会影响性能，若改动请注意如何还原。

 确保setup_instruments中的相关特性已开启：

启用性能监控相关的仪器（instruments）并开启计时功能，以便对语句和阶段进行性能分析。

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

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

确保setup_consumers中的相关特性已开启：

启用性能监控相关的消费者（consumers），以便收集和消费性能事件数据。

update performance_schema.setup_consumers 
set enabled='YES' where name like '%events_statements_%';

update performance_schema.setup_consumers 
set enabled='YES' where name like '%events_stages_%';

执行目标SQL

SELECT * FROM employees.employees WHERE emp_no = 10001;

获取SQL执行的EVENT_ID

 从events_statements_history_long中获取执行SQL的EVENT_ID：

select event_id, truncate(timer_wait/1000000000000,6) as duration, sql_text 
from performance_schema.events_statements_history_long 
where sql_text like 'SELECT%';

　　

获取SQL执行各阶段耗时

 从events_stages_history_long中获取SQL执行各阶段的耗时：

--以nesting_event_id匹配上面得到的event_id
select event_name as stage, truncate(timer_wait/1000000000000,6) as duration 
from performance_schema.events_stages_history_long 
where nesting_event_id=299;

stage/sql/starting: 查询开始阶段，表示查询的开始。
stage/sql/Executing hook on transaction begin.: 在事务开始时执行的钩子操作。
stage/sql/checking permissions: 检查权限阶段，用于验证查询执行权限。
stage/sql/Opening tables: 打开数据表阶段，表示打开查询中涉及的数据表。
stage/sql/init: 初始化阶段，执行相关初始化操作。
stage/sql/System lock: 系统锁定阶段，可能涉及对系统资源的锁定。
stage/sql/optimizing: 优化阶段，执行查询优化操作。
stage/sql/statistics: 统计信息阶段，可能涉及收集查询执行统计信息。
stage/sql/preparing: 准备阶段，准备执行查询。
stage/sql/executing: 执行阶段，实际执行查询操作。
stage/sql/end: 结束阶段，表示查询结束。
stage/sql/query end: 查询结束阶段，标志着查询完全结束。
stage/sql/waiting for handler commit: 等待处理程序提交阶段，等待数据处理程序提交操作完成。
stage/sql/closing tables: 关闭数据表阶段，关闭查询使用的数据表。
stage/sql/freeing items: 释放项目阶段，释放与查询相关的资源。
stage/sql/cleaning up: 清理阶段，清理查询执行过程中产生的临时数据或资源

 

🐬See more in https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html