MySQL 8 查看 SQL 语句的执行进度

目录

1. 查询各阶段执行进度

（1）开启收集与统计汇总执行阶段信息的功能

（2）确定执行的SQL所属的thread_id

（3）查询各阶段的执行进度

2. 查询SQL语句的整体执行进度

---

1. 查询各阶段执行进度

（1）开启收集与统计汇总执行阶段信息的功能

update performance_schema.setup_instruments 
set enabled='yes', timed='yes' where name like 'stage/%';update performance_schema.setup_consumers 
set enabled='yes' where name like '%stage%';

（2）确定执行的SQL所属的thread_id

select sys.ps_thread_id(connection_id());

（3）查询各阶段的执行进度

-- 当前
SELECT stmt.SQL_TEXT AS sql_text, stage.EVENT_NAME,CONCAT(WORK_COMPLETED, '/', WORK_ESTIMATED) AS progress,(stage.TIMER_END - stmt.TIMER_START) / 1E12 AS current_seconds,(stage.TIMER_END - stmt.TIMER_START) / 1E12 * (WORK_ESTIMATED - WORK_COMPLETED) / WORK_COMPLETED AS remaining_seconds
FROMevents_stages_current stage,events_statements_current stmt
WHEREstage.THREAD_ID = stmt.THREAD_IDAND stage.NESTING_EVENT_ID = stmt.EVENT_ID;-- 历史
SELECT THREAD_ID,EVENT_NAME,SOURCE,sys.format_time(TIMER_WAIT) AS exec_time,WORK_COMPLETED,WORK_ESTIMATED
FROMperformance_schema.events_stages_history_long
WHEREthread_id = sys.ps_thread_id(CONNECTION_ID());

2. 查询SQL语句的整体执行进度

SELECT * FROM sys.session WHERE THREAD_ID = CONNECTION_ID() AND command = 'Query' AND trx_state='ACTIVE'\G;