[转载]评估某个SQL回滚需要的耗时

保持热爱奔赴山海

发布于 2023-05-01 09:59:58

2310

发布于 2023-05-01 09:59:58

文章被收录于专栏：饮水机管理员饮水机管理员

原文地址 https://mydbops.wordpress.com/2022/02/07/estimating-time-for-rollback-operation/

回滚是一种操作，它将事务的当前状态更改为以前的状态。如果我们想回滚任何未提交的事务，通常需要undo logs ，并且它在隔离中起着重要作用。

对于事务期间所做的任何更改，都必须优先存储，因为如果我们选择回滚事务，这些更改是必需的。

数据修改完成后，将在撤消日志中创建条目。如果事务使用SQL命令修改数据，它将为每个操作创建离散的undo logs 。一旦事务被提交，MySQL就可以自由地清除在该事务中创建的 undo logs。

通常，回滚过程将比原始操作花费更多的时间。因为这是一个单线程进程。

案例：让我们考虑一个有1亿条记录的表sbtest1。我将根据id<=3000000的条件删除3000万条记录。因此，在这中间，我强行终止了删除操作。

手工处理的方法：

mysql> show processlist;
+—-+—————–+———–+———+———+——+————————+—————————————-+
| Id | User            | Host      | db      | Command | Time | State                  | Info                                   |
+—-+—————–+———–+———+———+——+————————+—————————————-+
|  5 | event_scheduler | localhost | NULL    | Daemon  | 7306 | Waiting on empty queue | NULL                                   |
| 17 | root            | localhost | sb_test | Killed  |  704 | query end              | delete from sbtest1 where id<=30000000 |
| 18 | root            | localhost | sb_test | Sleep   |  626 |                        | NULL                                   |
| 19 | root            | localhost | NULL    | Query   |    0 | init                   | show processlist                       |
+—-+—————–+———–+———+———+——+————————+—————————————-+
4 rows in set (0.00 sec)

其中id 为17的是正在回滚的事务会话。

mysql> pager grep -e 'trx_mysql_thread_id: 17' -e trx_rows_modified

mysql> select * from information_schema.innodb_trx\G select sleep(60); select * from information_schema.innodb_trx\G
         trx_rows_modified: 0
       trx_mysql_thread_id: 17
         trx_rows_modified: 18460230
2 rows in set (0.26 sec)


1 row in set (1 min 0.31 sec)


       trx_mysql_thread_id: 17
         trx_rows_modified: 17169927
1 row in set (0.09 sec)


mysql> \n


mysql> select SEC_TO_TIME(round((17169927*60)/(18460230–17169927))) as 'Estimation Time of Rollback';
+—————————–+
| Estimation Time of Rollback |
+—————————–+
| 00:13:18                     |
+—————————–+
1 row in set (0.18 sec)

也可以写个存储过程来做：

use mysql;  -- 随便切到一个库里也行

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 ;

执行效果，如下：

-- 参数1 是处于终止状态中的processlist id，参数2 是评估的时间间隔

mysql> select RollbackTimeCalc(18,5);  
+———————————————————————————————————-+
| RollbackTimeCalc(18,5)                                                   |
+———————————————————————————————————+
| Estimation Time of Rollback: 00:06:09 Pending rows to rollback 10341861 |
+———————————————————————————————————-+
1 row in set (5.37 sec)

使用上面创建的函数，我们可以轻松估计回滚操作的大致时间为 06 分 09 秒。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-04-25，如有侵权请联系 cloudcommunity@tencent.com 删除

sql