写个function
代码语言：javascript复制DELIMITER $$
CREATE FUNCTION RollbackTimeCalc(processID INT, timeInterval INT)
RETURNS VARCHAR(225)
DETERMINISTIC
BEGIN
DECLARE RollbackModifiedBeforeInterval INT;
DECLARE RollbackModifiedAfterInterval INT;
DECLARE RollbackPendingRows INT;
DECLARE Result varchar(20);
SELECT trx_rows_modified INTO RollbackModifiedBeforeInterval from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = 'ROLLING BACK';
do sleep(timeInterval);
SELECT trx_rows_modified INTO RollbackModifiedAfterInterval from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = 'ROLLING BACK';
set Result=SEC_TO_TIME(round((RollbackModifiedAfterInterval*timeInterval)/(RollbackModifiedBeforeInterval-RollbackModifiedAfterInterval)));
SELECT trx_rows_modified INTO RollbackPendingRows from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = 'ROLLING BACK';
RETURN(CONCAT('Estimation Time of Rollback : ', Result, ' Pending rows to rollback ', RollbackPendingRows));
END$$
DELIMITER ;验证效果
代码语言：javascript复制可以看到下面有个会话在执行delete的大批量删除数据操作
[test]> show full processlist;
----- ------ ----------- --------------- --------- ------ ---------- -------------------------------------------------------------------- --------- ----------- ---------------
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
----- ------ ----------- --------------- --------- ------ ---------- -------------------------------------------------------------------- --------- ----------- ---------------
| 133 | root | localhost | test | Query | 0 | init | show full processlist | 0 | 0 | 0 |
| 135 | root | localhost | test | Sleep | 6 | | NULL | 5617 | 0 | 0 |
| 136 | root | localhost | dba_testdb | Query | 36 | updating | delete from tb_core_data where 1=1 | 36314 | 0 | 869139 |
----- ------ ----------- --------------- --------- ------ ---------- -------------------------------------------------------------------- --------- ----------- ---------------
3 rows in set (0.00 sec)
另开一个窗口，把id为136的会话给kill掉，触发MySQL的事务回滚动作。
然后，另开一个窗口 多次执行刚才创建的function， 入参2个，第一个是连接id，第二个是sleep的秒数
[test]> select RollbackTimeCalc(136,5);
------------------------------------------------------------------------
| RollbackTimeCalc(136,5) |
------------------------------------------------------------------------
| Estimation Time of Rollback : 00:00:33 Pending rows to rollback 915007 |
------------------------------------------------------------------------
1 row in set (5.02 sec)
[test]> select RollbackTimeCalc(136,5);
------------------------------------------------------------------------
| RollbackTimeCalc(136,5) |
------------------------------------------------------------------------
| Estimation Time of Rollback : 00:00:20 Pending rows to rollback 606635 |
------------------------------------------------------------------------
1 row in set (5.00 sec)
[test]> select RollbackTimeCalc(136,5);
------------------------------------------------------------------------
| RollbackTimeCalc(136,5) |
------------------------------------------------------------------------
| Estimation Time of Rollback : 00:00:14 Pending rows to rollback 414103 |
------------------------------------------------------------------------
1 row in set (5.00 sec)
[test]> select RollbackTimeCalc(136,5);
------------------------------------------------------------------------
| RollbackTimeCalc(136,5) |
------------------------------------------------------------------------
| Estimation Time of Rollback : 00:00:07 Pending rows to rollback 218920 |
------------------------------------------------------------------------
1 row in set (5.00 sec)
[test]> select RollbackTimeCalc(136,5);
-----------------------------------------------------------------------
| RollbackTimeCalc(136,5) |
-----------------------------------------------------------------------
| Estimation Time of Rollback : 00:00:02 Pending rows to rollback 51773 |
-----------------------------------------------------------------------
1 row in set (5.00 sec)
[test]> select RollbackTimeCalc(136,5);
-------------------------
| RollbackTimeCalc(136,5) |
-------------------------
| NULL |
-------------------------
1 row in set (5.00 sec)
可以看到 Estimation Time of Rollback （回滚需要的时间）在不断的变小，直到最后返回结果为NULL。 更详细的操作和实践，可以参考原文： https://mydbops.wordpress.com/2022/02/07/estimating-time-for-rollback-operation/
