MySQL的rollback（總結分享）

本篇文章給大家帶來了關於的相關知識，其中主要整理了rollback的相關問題，主要介紹了書屋回滾以及回滾機制等內容，下面一起來看一下，希望對大家有幫助。

推薦學習：

經常會遇到操作一張大表，發現操作時間過長或影響線上業務，想要回退大表操作的場景。在停止大表操作之後，等待回滾是一個很漫長的過程，儘管可能對知道一些縮短時間的方法，處於對生產環境資料完整性的敬畏，也會選擇不做介入。

事務回滾

事務是關係型資料庫裡的執行單位，可以通過最後階段控制選擇提交或回滾。在各種無法保證完整性的場景下進行回滾操作。MySQL裡回滾是通過Undo紀錄檔完成，Undo紀錄檔記錄包含關於如何撤消事務相關的最新更改的資訊。Undo紀錄檔存在於Undo紀錄檔段中，Undo紀錄檔段包含在回滾段中。回滾段位於undo表空間和全域性Temporary表空間中。
關係如下：

undo檔案

mysql > show variables like '%undo%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| innodb_max_undo_log_size | 1073741824         |
| innodb_undo_directory    | /opt/data8.0/mysql |
| innodb_undo_log_encrypt  | OFF                |
| innodb_undo_log_truncate | ON                 |
| innodb_undo_tablespaces  | 2                  |
+--------------------------+--------------------+
5 rows in set (0.00 sec)

全域性Temporary所指的一個臨時表空間(ibtmp1)，用於儲存對使用者建立的臨時表所做更改的回滾段。

mysql > SELECT @@innodb_temp_data_file_path;
+-------------------------------+
| @@innodb_temp_data_file_path  |
+-------------------------------+
| ibtmp1:128M:autoextend:max:30G |
+-------------------------------+

理解了回滾包含的檔案都有那些，繼續往下看。

回滾機制：

MySQL回滾控制是內部innodb引擎協調解決，不提供人為控制的機制。目前提供的MySQL回滾引數如下：

mysql> SHOW VARIABLES LIKE  '%ROLL%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
| innodb_rollback_segments   | 128   |
+----------------------------+-------+

innodb_rollback_on_timeout：

InnoDB預設只在事務超時時回滾最後一條語句。如果指定了——InnoDB -rollback-on-timeout，事務超時將導致InnoDB中止並回滾整個事務。預設下是關閉的，一旦指定時間，如回滾失敗。可以想象到資料會存在不一致的問題。這個方式不可取。

Innodb_rollback_segments（1~128）：

定義了分配給每個undo表空間的回滾段的數量，以及為生成undo記錄的事務分配的全域性臨時表空間的數量。
回滾段支援的事務數量：取決於回滾段中的復原slot數量以及每個事務所需的復原紀錄檔數量

官方提供的回滾段中undo槽的數量根據InnoDB頁面大小有關:

從最新的MySQL8.0.27原始碼實現中 storage\innobase\include\trx0rseg.h:

/* Number of undo log slots in a rollback segment file copy 
這裡 UNIV_PAGE_SIZE正常頁面的大小  即 1024*/
#define TRX_RSEG_N_SLOTS (UNIV_PAGE_SIZE / 16)

/* Maximum number of transactions supported by a single rollback segment 
單個回滾段支援的最大事務數1024/2=512
*/
#define TRX_RSEG_MAX_N_TRXS (TRX_RSEG_N_SLOTS / 2)

在預設情況下page中又劃分了1024個slot槽(TRX_RSEG_N_SLOTS)，每個slot又對應到一個undo log物件，因此理論上InnoDB可以支援 128 * 512=65536個普通事務。
原理部分參考MySQL · 引擎特性 · InnoDB undo log 漫遊
官方提供undbo回滾並行讀寫場景：

從上訴的原理中回到實際應用場景中：
對於回滾段支援的能力，還是可觀的,但往往執行大批次的回滾的時候非常慢。特別是線上處理過程中發現10w行回滾有可能10分鐘這樣的情況。甚至更長時間。
下面通過sysbench準備5000w的單表資料，在無負載下，大概刪除1分鐘，之後通過kill -9，強制停止方式回滾事務：

明顯重新啟動效果更加。
但kill -9 方式容易把資料頁損壞，存在很大的風險。日常當中資料庫也有負載，可想而知，大事務回滾的代價非常大。

總結

應儘量避免大的回退操作，非常消耗資料庫資源和效能，生產環境下會導致重大生產事故。避免不了大事務回滾，可以採取以下方式：

對於批次操作，可以分批提交比如1000行 ~5000行之類的
undo空間和全域性臨時表空間可以適當的調整。建議4個undo檔案，全域性ibtmp1初始化1G
高可用環境下，能確資料的一致性下，可以把從提升新主，提供服務，等待大事務回滾。
極端情況下，可以通過 kill -9 重新啟動操作會因為資料量非常大，導致mysql恢復緩慢，此時需要等待mysql進行崩潰恢復，根據資料量的不同，等待的時間也不同
如重新啟動過程中，存在資料頁損壞或跳過回滾，可通過innodb_force_recovery=3（不執行事務回滾操作。）

推薦學習：

以上就是MySQL的rollback（總結分享）的詳細內容，更多請關注TW511.COM其它相關文章！