首頁  >  文章  >  資料庫  >  MySQL的rollback(總結分享)

MySQL的rollback(總結分享)

WBOY
WBOY轉載
2022-06-24 12:54:455954瀏覽

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

MySQL的rollback(總結分享)

推薦學習:mysql影片教學

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

交易回滾

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

MySQL的rollback(總結分享)

  • undo檔案

    MySQL的rollback(總結分享)

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的rollback(總結分享)

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頁面大小有關:

MySQL的rollback(總結分享)

從最新的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回溯並發讀寫場景:

MySQL的rollback(總結分享)

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

MySQL的rollback(總結分享)

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

總結

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

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

推薦學習: mysql影片教學

以上是MySQL的rollback(總結分享)的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:csdn.net。如有侵權,請聯絡admin@php.cn刪除