方法1、Use INFORMATION_SCHEMA
information_schema.innodb_trx表的trx_rows_modified列显示事务处理了多少行
<code >(Sat Sep 2 11:21:53 2023)[root@GreatSQL][(none)]>select trx_id, trx_state,trx_started, trx_rows_modified from information_schema.innodb_trx order by trx_rows_modified desc limit 20;
+---------+-----------+---------------------+-------------------+
| trx_id | trx_state | trx_started | trx_rows_modified |
+---------+-----------+---------------------+-------------------+
| 1337785 | RUNNING | 2023-09-02 11:12:11 | 6 |
+---------+-----------+---------------------+-------------------+
1 row in set (0.00 sec)
方法2、Use INNODB STATUS
在INNODB状态的事务部分中，在“undo log entries”附近，也显示了按事务修改的行数，例如
<code >mysql> SHOW ENGINE INNODB STATUS \G
---TRANSACTION 1339048, ACTIVE 35 sec
2 lock struct(s), heap size 1128, 7 row lock(s), undo log entries 6
MySQL thread id 2819484, OS thread handle 139997412484864, query id 28178770 localhost root
TABLE LOCK table `andy`.`andy` trx id 1339048 lock mode IX
RECORD LOCKS space id 15 page no 4 n bits 80 index PRIMARY of table `andy`.`andy` trx id 1339048 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
方法3、Monitoring progress of ALTER commands in MySQL Server 5.7 using PERFORMANCE_SCHEMA
Read more in online documentation at https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html
方法4、 解析binlog
可参考 如何获取MySQL中的查询和事务大小 的方法
