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;