上QQ阅读APP看本书，新人免费读10天
设备和账号都新为新人
7.3 sys系统库的进度报告功能
从MySQL 5.7.9开始，sys系统库视图提供了查看长时间运行的事务的进度报告，通过processlist和session以及带“x$”前缀的视图进行查看，其中processlist包含了后台线程和前台线程当前的事件信息；session不包含后台线程和command为Daemon的线程。视图列表如下：
processlist session x$processlist x$session
session视图直接调用processlist视图过滤了后台线程和command为Daemon的线程（所以两个视图输出结果的字段相同），而processlist线程联结查询了threads、events_waits_current、events_stages_current、events_statements_current、events_transactions_current、sys.x$memory_by_thread_by_current_bytes、session_connect_attrs表，因此需要打开相应的instruments和consumers，否则其对应的信息字段值就为NULL。对于trx_state字段值为ACTIVE的线程，progress可以输出百分比进度信息（只有支持进度的事件才会被统计并打印进来）。
查询示例如下：
# 查看当前正在执行的语句进度信息 mysql> select ＊ from session where conn_id! =connection_id（）and trx_state='ACTIVE'\G ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 1. row ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ thd_id: 47 conn_id: 5 user: admin@localhost db: sbtest command: Query state: alter table（merge sort） time: 29 current_statement: alter table sbtest1 add index i_c（c） statement_latency: 29.34 s progress: 49.70 lock_latency: 4.34 ms rows_examined: 0 rows_sent: 0 rows_affected: 0 tmp_tables: 0 tmp_disk_tables: 0 full_scan: NO last_statement: NULL last_statement_latency: NULL current_memory: 4.52 KiB last_wait: wait/io/file/innodb/innodb_temp_file last_wait_latency: 369.52 us source: os0file.ic:470 trx_latency: 29.45 s trx_state: ACTIVE trx_autocommit: YES pid: 4667 program_name: mysql 1 row in set（0.12 sec） # 查看已经执行完的语句相关统计信息 mysql> select ＊ from session where conn_id! =connection_id（）and trx_state='COMMITTED'\G ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 1. row ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ thd_id: 47 conn_id: 5 user: admin@localhost db: sbtest command: Sleep state: NULL time: 372 current_statement: NULL statement_latency: NULL progress: NULL lock_latency: 4.34 ms rows_examined: 0 rows_sent: 0 rows_affected: 0 tmp_tables: 0 tmp_disk_tables: 0 full_scan: NO last_statement: alter table sbtest1 add index i_c（c） last_statement_latency: 1.61 m current_memory: 4.52 KiB last_wait: idle last_wait_latency: Still Waiting source: socket_connection.cc:69 trx_latency: 1.61 m trx_state: COMMITTED trx_autocommit: YES pid: 4667 program_name: mysql 1 row in set（0.12 sec）
对于stage事件进度报告，要求必须启用events_stages_current consumers，启用需要查看与进度相关的instruments。例如：
stage/sql/Copying to tmp table stage/innodb/alter table（end） stage/innodb/alter table（flush） stage/innodb/alter table（insert） stage/innodb/alter table（log apply index） stage/innodb/alter table（log apply table） stage/innodb/alter table（merge sort） stage/innodb/alter table（read PK and internal sort） stage/innodb/buffer pool load
对于不支持进度的stage事件，或者未启用所需的instruments或consumers的stage事件，则对应的进度信息字段值为NULL。