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
FROM
    events_stages_current stage,
    events_statements_current stmt
WHERE
    stage.THREAD_ID = stmt.THREAD_ID
        AND 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
FROM
    performance_schema.events_stages_history_long
WHERE
    thread_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;