MySQL innodb引擎的事務(wù)執(zhí)行過程

2024-07-24 12:34:31

字體：大中小

供稿：網(wǎng)友

　　通過這篇文章可以了解到下面幾個問題
　　問題1：MySQL innodb引擎的update的流程；
　　問題2：以及寫redo,undo,binlog的順序，然后刷盤的順序又是什么呢？
　　問題3：以及刷新redo和臟數(shù)據(jù)的相關(guān)進程；
　　總結(jié)以上的三個問題，其實就是關(guān)于MySQL innodb事務(wù)的流程；那么接下來，我將詳細總結(jié)下一一一：MySQL innodb的事務(wù)流程：
　　1.接下來我就以update為例，講解下MySQL5.6的innodb的事務(wù)流程，總結(jié)起來就是：
　　鎮(zhèn)對update he set name='liuwenhe' where id=5;
　　1）事務(wù)開始
　　2）對id=5這條數(shù)據(jù)上排他鎖，并且給5兩邊的臨近范圍加gap鎖，防止別的事務(wù)insert新數(shù)據(jù)；
　　3）將需要修改的數(shù)據(jù)頁PIN到innodb_buffer_cache中；
　　4）記錄id=5的數(shù)據(jù)到undo log. 5）記錄修改id=5的信息到redo log.
　　6）修改id=5的name='liuwenhe'.
　　7）刷新innodb_buffer_cache中臟數(shù)據(jù)到底層磁盤，這個過程和commit無關(guān)；
　　8）commit，觸發(fā)page cleaner線程把redo從redo buffer cache中刷新到底層磁盤，并且刷新innodb_buffer_cache中臟數(shù)據(jù)到底層磁盤也會觸發(fā)對redo的刷新；
　　9）記錄binlog （記錄到binlog_buffer_cache中）
　　10）事務(wù)結(jié)束；
　　2.關(guān)于事務(wù)的四大特性ACID
　　事務(wù)的原子性(Atomicity)：事務(wù)中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在執(zhí)行的過程中發(fā)生了錯誤，要回滾(Rollback)到事務(wù)開始前的狀態(tài)，就像這個事務(wù)從來沒有執(zhí)行過。
　　事務(wù)的持久性(Durability)：事務(wù)一旦完成，該事務(wù)對數(shù)據(jù)庫所做的所有修改都會持久的保存到數(shù)據(jù)庫中。為了保證持久性，數(shù)據(jù)庫系統(tǒng)會將修改后的數(shù)據(jù)完全的記錄到持久的存儲上。
　　事務(wù)的隔離性：多個事務(wù)并發(fā)訪問時，事務(wù)之間是隔離的，一個事務(wù)不應(yīng)該影響其它事務(wù)運行效果
　　事務(wù)的一致性：一致性是指在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性約束沒有被破壞。這是說數(shù)據(jù)庫事務(wù)不能破壞關(guān)系數(shù)據(jù)的完整性以及業(yè)務(wù)邏輯上的一致性。
　　二：redo和undo保證MySQL innodb事務(wù)的原子性和持久性：
　　總起來概述可以認為：
　　undo用來保存數(shù)據(jù)更改之前的數(shù)據(jù)；保證原子性
　　redo用來保存數(shù)據(jù)更改之后的數(shù)據(jù)（注意是物理的修改信息），保證持久性
　　1）首先介紹Undo Log
　　Undo Log 主要是為了實現(xiàn)事務(wù)的原子性，在MySQL數(shù)據(jù)庫InnoDB存儲引擎中，還用Undo Log來實現(xiàn)多版本并發(fā)控制(簡稱：MVCC)，之后的文章將會介紹mvcc；
　　Undo Log的原理很簡單，為了滿足事務(wù)的原子性，在操作任何數(shù)據(jù)之前，首先將數(shù)據(jù)備份到一個地方
　　也就是 Undo Log，然后進行數(shù)據(jù)的修改。如果出現(xiàn)了錯誤或者用戶執(zhí)行了ROLLBACK語句，系統(tǒng)可以利用Undo Log中的備份將數(shù)據(jù)恢復(fù)到事務(wù)開始之前的狀態(tài)。
　　需要注意在MySQL 5.6之前，undo log是放在了共享表空間 ibdata1中的，MySQL5.6中開始支持把undo log分離到獨立的表空間，并放到單獨的文件目錄下；采用獨立undo表空間，再也不用擔(dān)心undo會把 ibdata1 文件搞大。
　　undo log是為回滾而用，具體內(nèi)容就是copy事務(wù)前的數(shù)據(jù)庫內(nèi)容（行）到innodb_buffer_pool中的undo buffer（或者叫undo page)，在適合的時間把undo buffer中的內(nèi)容刷新到磁盤。undo buffer與redo buffer一樣，也是環(huán)形緩沖，但當(dāng)緩沖滿的時候，undo buffer中的內(nèi)容也會被刷新到磁盤；并且innodb_purge_threads后臺線程會清空undo頁、清理“deleted”page，InnoDB將Undo Log看作數(shù)據(jù)，因此記錄Undo Log的操作也會記錄到redo log中。這樣undo log就可以象數(shù)據(jù)一樣緩存起來
　　2）接下來介紹 Redo Log,注意是先寫redo,然后才修改buffer cache中的頁，因為修改是以頁為單位的，所以先寫redo才能保證一個大事務(wù)commit的時候，redo已經(jīng)刷新的差不多了。反過來說假如是先改buffer cache中的頁，然后再寫redo，就可能會有很多的redo需要寫，因為一個頁可能有很多數(shù)據(jù)行；而很多數(shù)據(jù)行產(chǎn)生的redo也可能比較多，那么commit的時候，就可能會有很多redo需要寫；
　　和Undo Log相反，Redo Log記錄的是新數(shù)據(jù)的備份。在事務(wù)提交前，只要將Redo Log持久化即可，
　　不需要將數(shù)據(jù)持久化。當(dāng)系統(tǒng)崩潰時，雖然數(shù)據(jù)沒有持久化，但是Redo Log已經(jīng)持久化。系統(tǒng)可以根據(jù)Redo Log的內(nèi)容，將所有數(shù)據(jù)恢復(fù)到最新的狀態(tài)。需要注意的是，事務(wù)過程中，先把redo寫進redo log buffer中，然后MySQL后臺進程page cleaner thread適當(dāng)?shù)娜ニ⑿聄edo到低層磁盤永久保存；
　　因為刷新buffer pool的臟數(shù)據(jù)之前，必須要先刷新redo（從redo log buffer到磁盤），所以觸發(fā)刷新臟數(shù)據(jù)buffer pool的臟數(shù)據(jù)的條件也同時會觸發(fā)刷新redo。還需要注意：MySQL 5.6版本之前都是master thread來完成刷臟數(shù)據(jù)的任務(wù)（包括buffer pool中的臟數(shù)據(jù)以及redo log buffer中的redo),MySQL 5.6版本，刷新操作放入到了單獨的Page Cleaner Thread中；
　　Checkpoint（檢查點）技術(shù)目的是解決以下幾個問題：1、縮短數(shù)據(jù)庫的恢復(fù)時間；2、緩沖池不夠用時，將臟頁刷新到磁盤；3、重做日志不可用時，刷新臟頁。
　　在InnoDB存儲引擎內(nèi)部，有兩種Checkpoint
　　分別為：Sharp Checkpoint、Fuzzy Checkpoint
　　Sharp Checkpoint發(fā)生在數(shù)據(jù)庫關(guān)閉時將所有的臟頁都刷新回磁盤，這是默認的工作方式，即參數(shù)innodb_fast_shutdown=1。但是若數(shù)據(jù)庫在運行時也使用Sharp Checkpoint，那么數(shù)據(jù)庫的可用性就會受到很大的影響。故在InnoDB存儲引擎內(nèi)部使用Fuzzy Checkpoint進行頁的刷新，即只刷新一部分臟頁，而不是刷新所有的臟頁回磁盤。
　　Fuzzy Checkpoint：
　　1、Master Thread Checkpoint；
　　2、FLUSH_LRU_LIST Checkpoint；
　　3、Async/Sync Flush Checkpoint；
　　4、Dirty Page too much Checkpoint
　　1、Master Thread Checkpoint
　　以每秒或每十秒的速度從緩沖池的臟頁列表中刷新一定比例的頁回磁盤，這個過程是異步的，此時InnoDB存儲引擎可以進行其他的操作，用戶查詢線程不會阻塞。
　　2、FLUSH_LRU_LIST Checkpoint
　　因為InnoDB存儲引擎需要保證LRU列表中需要有差不多100個空閑頁可供使用。在InnoDB1.1.x版本之前，需要檢查LRU列表中是否有足夠的可用空間操作發(fā)生在用戶查詢線程中，顯然這會阻塞用戶的查詢操作。倘若沒有100個可用空閑頁，那么InnoDB存儲引擎會將LRU列表尾端的頁移除。如果這些頁中有臟頁，那么需要進行Checkpoint，而這些頁是來自LRU列表的，因此稱為FLUSH_LRU_LIST Checkpoint。
　　而從MySQL 5.6版本，也就是InnoDB1.2.x版本開始，這個檢查被放在了一個單獨的Page Cleaner線程中進行，并且用戶可以通過參數(shù)innodb_lru_scan_depth控制LRU列表中可用頁的數(shù)量，該值默認為1024，如：
　　mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lru_scan_depth';
　　+-----------------------+-------+
　　| Variable_name | Value |
　　+-----------------------+-------+
　　| innodb_lru_scan_depth | 1024 |
　　+-----------------------+-------+
　　3、Async/Sync Flush Checkpoint
　　指的是重做日志文件不可用的情況，這時需要強制將一些頁刷新回磁盤，而此時臟頁是從臟頁列表中選取的。若將已經(jīng)寫入到重做日志的LSN記為redo_lsn，將已經(jīng)刷新回磁盤最新頁的LSN記為checkpoint_lsn，則可定義：
　　checkpoint_age（可以理解臟頁，或者待刷新的臟頁） = redo_lsn - checkpoint_lsn
　　再定義以下的變量：
　　async_water_mark = 75% * total_redo_log_file_size
　　sync_water_mark = 90% * total_redo_log_file_size
　　若每個重做日志文件的大小為1GB，并且定義了兩個重做日志文件，則重做日志文件的總大小為2GB。那么async_water_mark=1.5GB，sync_water_mark=1.8GB。則：
　　當(dāng)checkpoint_age<async_water_mark時，不需要刷新任何臟頁到磁盤； </async_water_mark時，不需要刷新任何臟頁到磁盤；<>
　　當(dāng)async_water_mark<checkpoint_age<sync_water_mark時觸發(fā)async flush，從flush列表中刷新足夠的臟頁回磁盤，使得刷新后滿足checkpoint_age<async_water_mark； </checkpoint_age
　　checkpoint_age>sync_water_mark這種情況一般很少發(fā)生，除非設(shè)置的重做日志文件太小，并且在進行類似LOAD DATA的BULK INSERT操作。此時觸發(fā)Sync Flush操作，從Flush列表中刷新足夠的臟頁回磁盤，使得刷新后滿足checkpoint_age<async_water_mark。 </async_water_mark。<>
　　可見，Async/Sync Flush Checkpoint是為了保證重做日志的循環(huán)使用的可用性。在InnoDB 1.2.x版本之前，Async Flush Checkpoint會阻塞發(fā)現(xiàn)問題的用戶查詢線程，而Sync Flush Checkpoint會阻塞所有的用戶查詢線程，并且等待臟頁刷新完成。從InnoDB 1.2.x版本開始——也就是MySQL 5.6版本，這部分的刷新操作同樣放入到了單獨的Page Cleaner Thread中，故不會阻塞用戶查詢線程。
　　解釋下為什么重做日志文件不可用時，這時需要強制將一些臟頁刷新回磁盤？
　　因為我們知道redo的作用是保證數(shù)據(jù)庫的一致性，當(dāng)數(shù)據(jù)庫異常停機時，需要借助redo+undo進行實例恢復(fù)，redo前滾---恢復(fù)出buffer pool中的臟數(shù)據(jù)（包括已經(jīng)commit還沒有刷新到磁盤的，也可能包括沒有commit，但是已經(jīng)刷新到磁盤的，）然后借助undo完成回滾---將沒有commit，但是已經(jīng)刷新到磁盤的數(shù)據(jù)，回滾到之前的狀態(tài)。那么為啥重做日志文件不可用時，這時需要強制將一些臟頁刷新回磁盤？原因就在于，redo 是循環(huán)覆寫的，當(dāng)redo log 文件不可用，也就是說此時所有的redo 文件里面的redo都是實例恢復(fù)需要的，也就是不能被覆蓋的redo, 那么什么是實例恢復(fù)需要的redo呢？就是buffer pool中的的臟數(shù)據(jù)，還沒有刷新到磁盤，而這些臟數(shù)據(jù)相關(guān)的redo是不能被覆蓋的，這些redo就是實例恢復(fù)需要的redo，所以沒有可用的重做日志文件，需要強制將一些臟頁刷新回磁盤，這樣就會有一些redo是實例恢復(fù)不需要的了，也就可以被覆蓋了。
　　4、Dirty Page too much
　　即臟頁的數(shù)量太多，導(dǎo)致InnoDB存儲引擎強制進行Checkpoint。其目的總的來說還是為了保證緩沖池中有足夠可用的頁。其可由參數(shù)innodb_max_dirty_pages_pct控制：
　　mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_max_dirty_pages_pct' ;
　　+----------------------------+-------+
　　| Variable_name | Value |
　　+----------------------------+-------+
　　| innodb_max_dirty_pages_pct | 75 |
　　+----------------------------+-------+
　　innodb_max_dirty_pages_pct值為75表示，當(dāng)緩沖池中臟頁的數(shù)量占據(jù)75%時，強制進行Checkpoint，刷新一部分的臟頁到磁盤。在InnoDB 1.0.x版本之前，該參數(shù)默認值為90，之后的版本都為75，其可以通過參數(shù)innodb_max_dirty_pages_pct來設(shè)置；
　　總結(jié)下redo刷新的條件（因為刷新innodb_buffer_pool中的臟數(shù)據(jù)之前需要刷新redo,所以觸發(fā)刷新buffer_pool會同時觸發(fā)刷新redo）：
　　1）當(dāng)redo log buffer達到一定比值后，
　　2）刷新innodb_buffer_pool中的臟數(shù)據(jù)之前，
　　3）redo log buffer滿的時候，沒有可用buffer；
　　4）每秒刷新一次；
　　5）commit的時候；
　　6）數(shù)據(jù)庫關(guān)閉時發(fā)生harp Checkpoint，觸發(fā)將所有臟頁刷回磁盤
　　7）手工flush logs;
　　8）重做日志不可用時，觸發(fā)刷新innodb_buffer_pool中的臟數(shù)據(jù)，進而觸發(fā)redo刷新；
　　三：MySQL binlog：主從同步主庫binlog先寫入到 binlog_buffer中，然后刷新到磁層磁盤也就是binlog文件，主庫dump進程讀取的binlog文件，發(fā)送給從庫；
　　binlog日志是針對整個MySQL server而言的，前面介紹的redo和undo是針對innodb引擎而言的，binlog的存在就是方便那些不支持事務(wù)的引擎表來同步數(shù)據(jù)到slave；
　　那么到底是先刷新redo還是先寫binlog呢？
　　伴隨著這個問題，我重點說下，MySQL innodb 引擎事務(wù)commit的過程：
　　MySQL為了保證master和slave的數(shù)據(jù)一致性，就必須保證binlog和InnoDB redo日志的一致性，為此MySQL引入二階段提交（two phase commit or 2pc），MySQL通過兩階段提交(內(nèi)部XA的兩階段提交)很好地解決了這一問題，兩階段提交關(guān)鍵在于保證redo刷盤之后才能刷新binlog到底層文件，以 binlog 的寫入與否作為事務(wù)提交成功與否的標(biāo)志，最后判斷 binlog中是否有 redo里的xid,MySQL5.6以前，為了保證數(shù)據(jù)庫上層二進制日志的寫入順序和InnoDB層的事務(wù)提交順序一致，MySQL數(shù)據(jù)庫內(nèi)部使用了prepare_commit_mutex鎖。但是持有這把鎖之后，會導(dǎo)致組提交失敗；直到MySQL5.6之后，才解決了這個問題，借助序列來保證binlog刷新也可以組提交；關(guān)于redo 和binlog組提交，請看下一篇文章，
　　事務(wù)崩潰恢復(fù)過程如下：
　　1.崩潰恢復(fù)時，掃描最后一個Binlog文件，提取其中的xid；
　　2.InnoDB維持了狀態(tài)為Prepare的事務(wù)鏈表（commit兩階段提交中的第一階段，為Prepare階段，會把事務(wù)設(shè)置為Prepare狀態(tài)）將這些事務(wù)的xid和Binlog中記錄的xid做比較，如果在Binlog中存在，則提交，否則回滾事務(wù)。
　　通過這種方式，可以讓InnoDB redo 和Binlog中的事務(wù)狀態(tài)保持一致。
　　四：簡單介紹下MySQL的后臺進程：
　　InnoDB存儲引擎是多線程模型，因此其后臺有多個不同的后臺線程，負責(zé)處理不同的任務(wù)：
　　1）Master Thread
　　Master Thread是一個非常核心的后臺線程，主要負責(zé)將緩沖池中的數(shù)據(jù)異步刷新到磁盤，保證數(shù)據(jù)的一致性。
　　2）IO Thread
　　InnoDB存儲引擎中大量使用了Async IO來處理寫IO請求，這樣可以極大提高數(shù)據(jù)庫的性能，而IO Thread的主要工作是負責(zé)這些IO請求的回調(diào)處理，可以使用show engine innodb status命令查看InnoDB存儲引擎中的IO進程：
　　mysql> show engine innodb status/g;
　　I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
　　I/O thread 1 state: waiting for completed aio requests (log thread)
　　I/O thread 2 state: waiting for completed aio requests (read thread)
　　I/O thread 3 state: waiting for completed aio requests (read thread)
　　I/O thread 4 state: waiting for completed aio requests (read thread)
　　I/O thread 5 state: waiting for completed aio requests (read thread)
　　I/O thread 6 state: waiting for completed aio requests (read thread)
　　I/O thread 7 state: waiting for completed aio requests (read thread)
　　I/O thread 8 state: waiting for completed aio requests (write thread)
　　I/O thread 9 state: waiting for completed aio requests (write thread)
　　I/O thread 10 state: waiting for completed aio requests (write thread)
　　I/O thread 11 state: waiting for completed aio requests (write thread)
　　Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
　　ibuf aio reads:, log i/o’s:, sync i/o’s:
　　Pending flushes (fsync) log: 0; buffer pool: 0
　　451 OS file reads, 54 OS file writes, 7 OS fsyncs
　　3.77 reads/s, 16384 avg bytes/read, 1.05 writes/s, 0.13 fsyncs/s
　　如上顯示的是6個io read thread和4個io write thread，但是關(guān)于log的io thread 和insert buffer thread的io thread 只有一個；從MySQL 5.6開始默認是四個io read thread和4個io write thread，并且可以通過innodb_read_io_threads 和innodb_write_io_threads 參數(shù)進行設(shè)置：
　　mysql> show variables like '%io_threads%';
　　+-------------------------+-------+
　　| Variable_name | Value |
　　+-------------------------+-------+
　　| innodb_read_io_threads | 6 |
　　| innodb_write_io_threads | 4 |
　　+-------------------------+-------+
　　2 rows in set (0.00 sec)
　　3）Purge Thread
　　事務(wù)被提交后，其所使用的undo log可能不再需要，因此需要PurgeThread來回收已經(jīng)使用并分配的undo頁。從InnoDB1.1版本開始，purge操作可以獨立到單獨的線程中進行，以此來減輕Master Thread的工作，從而提高CPU的使用率、提升存儲引擎的性能。可以通過在MySQL數(shù)據(jù)庫的配置文件中添加相關(guān)的命令來啟用獨立的Purge Thread，如下參數(shù)：
　　mysql> show variables like 'innodb_purge_threads';
　　+----------------------+-------+
　　| Variable_name | Value |
　　+----------------------+-------+
　　| innodb_purge_threads | 1 |
　　+----------------------+-------+
　　1 row in set (0.00 sec)
　　Page Cleaner Thread
　　4）Page Cleaner Thread
　　是在InnoDB 1.2.x版本中引入的，其作用是將之前版本中的臟頁的刷新操作都放入到單獨的進程中來完成，目的就是為了減輕原Master Thread的工作及對于用戶查詢線程的阻塞，進一步提高InnoDB存儲引擎的性能。
　　然后回答最開始的問題:
　　在內(nèi)存中先寫undo,然后寫redo,至于redo和binlog順序不確定, 刷盤是先刷undo,然后刷redo,最后刷新binlog;

（編輯：武林網(wǎng)）

上一篇：實例解讀 MySQL并行復(fù)制如何解決特定的主從問題？

下一篇：MySQL 5.7搭建多源復(fù)制