Mysql 如何预估后台回滚事务的时长

没有停机时间和潜在的数据丢失，就不能简单地停止回滚。

建议等待回滚自行完成。要估计回滚需要多长时间，请登录mysql并执行几次以监控其进度:

<code style="text-align: left;">mysql> select now(),trx_state,trx_operation_state,trx_weight,trx_rows_locked,trx_rows_modified from information_schema.innodb_trx where trx_state='ROLLING BACK';do sleep(10);
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| now()               | trx_state    | trx_operation_state | trx_weight | trx_rows_locked | trx_rows_modified |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| 2017-11-15 15:20:19 | ROLLING BACK | rollback            |   17028388 |        64342541 |          16672912 |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (10.00 sec)

mysql> select now(),trx_state,trx_operation_state,trx_weight,trx_rows_locked,trx_rows_modified from information_schema.innodb_trx where trx_state='ROLLING BACK';do sleep(10);
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| now()               | trx_state    | trx_operation_state | trx_weight | trx_rows_locked | trx_rows_modified |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| 2017-11-15 15:20:29 | ROLLING BACK | rollback            |   16245596 |        64342541 |          15906465 |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (10.00 sec)

mysql> select now(),trx_state,trx_operation_state,trx_weight,trx_rows_locked,trx_rows_modified from information_schema.innodb_trx where trx_state='ROLLING BACK';do sleep(10);
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| now()               | trx_state    | trx_operation_state | trx_weight | trx_rows_locked | trx_rows_modified |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| 2017-11-15 15:20:39 | ROLLING BACK | rollback            |   15463244 |        64342541 |          15140438 |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (10.00 sec)

在上面的输出中，我们可以看到回滚正在以如下速度进行:

16672912 - 15906465 = 766447 rows per 10 seconds,  or 76644 rows per second.

因此，预计回滚可能需要16672912 / 76644 = 218秒才能完成。

​注意：这只是一个估计，它取决于数据大小、当前服务器负载和配置