MySQL innodb引擎的事務執行過程

透過這篇文章可以瞭解到下面幾個問題

問題1：MySQL innodb引擎的update的流程；

問題2：以及寫redo,undo,binlog的順序，然後刷盤的順序又是什麼呢？

問題3：以及重新整理redo和髒資料的相關程式；

總結以上的三個問題，其實就是關於MySQL innodb事務的流程；那麼接下來，我將詳細總結下一一一：MySQL innodb的事務流程：

1.接下來我就以update為例，講解下MySQL5.6的innodb的事務流程，總結起來就是：

鎮對update he set name='liuwenhe' where id=5;

1）事務開始

2）對id=5這條資料上排他鎖，並且給5兩邊的臨近範圍加gap鎖，防止別的事務insert新資料；

3）將需要修改的資料頁PIN到innodb_buffer_cache中；

4）記錄id=5的資料到undo log. 5）記錄修改id=5的資訊到redo log.

6）修改id=5的name='liuwenhe'.

7）重新整理innodb_buffer_cache中髒資料到底層磁碟，這個過程和commit無關；

8）commit，觸發page cleaner執行緒把redo從redo buffer cache中重新整理到底層磁碟，並且重新整理innodb_buffer_cache中髒資料到底層磁碟也會觸發對redo的重新整理；

9）記錄binlog （記錄到binlog_buffer_cache中）

10）事務結束；

2.關於事務的四大特性ACID

事務的原子性(Atomicity)：事務中的所有操作，要麼全部完成，要麼不做任何操作，不能只做部分操作。如果在執行的過程中發生了錯誤，要回滾(Rollback)到事務開始前的狀態，就像這個事務從來沒有執行過。

事務的永續性(Durability)：事務一旦完成，該事務對資料庫所做的所有修改都會持久的儲存到資料庫中。為了保證永續性，資料庫系統會將修改後的資料完全的記錄到持久的儲存上。

事務的隔離性：多個事務併發訪問時，事務之間是隔離的，一個事務不應該影響其它事務執行效果

事務的一致性：一致性是指在事務開始之前和事務結束以後，資料庫的完整性約束沒有被破壞。這是說資料庫事務不能破壞關係資料的完整性以及業務邏輯上的一致性。

二：redo和undo保證MySQL innodb事務的原子性和永續性：

總起來概述可以認為：

undo用來儲存資料更改之前的資料；保證原子性

redo用來儲存資料更改之後的資料（注意是物理的修改資訊），保證永續性

1）首先介紹Undo Log

Undo Log 主要是為了實現事務的原子性，在MySQL資料庫InnoDB儲存引擎中，還用Undo Log來實現多版本併發控制(簡稱：MVCC)，之後的文章將會介紹mvcc；

Undo Log的原理很簡單，為了滿足事務的原子性，在操作任何資料之前，首先將資料備份到一個地方

也就是 Undo Log，然後進行資料的修改。如果出現了錯誤或者使用者執行了ROLLBACK語句，系統可以利用Undo Log中的備份將資料恢復到事務開始之前的狀態。

需要注意在MySQL 5.6之前，undo log是放在了共享表空間 ibdata1中的，MySQL5.6中開始支援把undo log分離到獨立的表空間，並放到單獨的檔案目錄下；採用獨立undo表空間，再也不用擔心undo會把 ibdata1 檔案搞大。

undo log是為回滾而用，具體內容就是copy事務前的資料庫內容（行）到innodb_buffer_pool中的undo buffer（或者叫undo page)，在適合的時間把undo buffer中的內容重新整理到磁碟。undo buffer與redo buffer一樣，也是環形緩衝，但當緩衝滿的時候，undo buffer中的內容也會被重新整理到磁碟；並且innodb_purge_threads後臺執行緒會清空undo頁、清理“deleted”page，InnoDB將Undo Log看作資料，因此記錄Undo Log的操作也會記錄到redo log中。這樣undo log就可以象資料一樣快取起來

2）接下來介紹 Redo Log,注意是先寫redo,然後才修改buffer cache中的頁，因為修改是以頁為單位的，所以先寫redo才能保證一個大事務commit的時候，redo已經重新整理的差不多了。反過來說假如是先改buffer cache中的頁，然後再寫redo，就可能會有很多的redo需要寫，因為一個頁可能有很多資料行；而很多資料行產生的redo也可能比較多，那麼commit的時候，就可能會有很多redo需要寫；

和Undo Log相反，Redo Log記錄的是新資料的備份。在事務提交前，只要將Redo Log持久化即可，

不需要將資料持久化。當系統崩潰時，雖然資料沒有持久化，但是Redo Log已經持久化。系統可以根據Redo Log的內容，將所有資料恢復到最新的狀態。需要注意的是，事務過程中，先把redo寫進redo log buffer中，然後MySQL後臺程式page cleaner thread適當的去重新整理redo到低層磁碟永久儲存；

因為重新整理buffer pool的髒資料之前，必須要先重新整理redo（從redo log buffer到磁碟），所以觸發重新整理髒資料buffer pool的髒資料的條件也同時會觸發重新整理redo。還需要注意：MySQL 5.6版本之前都是master thread來完成刷髒資料的任務（包括buffer pool中的髒資料以及redo log buffer中的redo),MySQL 5.6版本，重新整理操作放入到了單獨的Page Cleaner Thread中；

Checkpoint（檢查點）技術目的是解決以下幾個問題：1、縮短資料庫的恢復時間；2、緩衝池不夠用時，將髒頁重新整理到磁碟；3、重做日誌不可用時，重新整理髒頁。

在InnoDB儲存引擎內部，有兩種Checkpoint

分別為：Sharp Checkpoint、Fuzzy Checkpoint

Sharp Checkpoint發生在資料庫關閉時將所有的髒頁都重新整理回磁碟，這是預設的工作方式，即引數innodb_fast_shutdown=1。但是若資料庫在執行時也使用Sharp Checkpoint，那麼資料庫的可用性就會受到很大的影響。故在InnoDB儲存引擎內部使用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列表中是否有足夠的可用空間操作發生在使用者查詢執行緒中，顯然這會阻塞使用者的查詢操作。倘若沒有100個可用空閒頁，那麼InnoDB儲存引擎會將LRU列表尾端的頁移除。如果這些頁中有髒頁，那麼需要進行Checkpoint，而這些頁是來自LRU列表的，因此稱為FLUSH_LRU_LIST Checkpoint。

而從MySQL 5.6版本，也就是InnoDB1.2.x版本開始，這個檢查被放在了一個單獨的Page Cleaner執行緒中進行，並且使用者可以透過引數innodb_lru_scan_depth控制LRU列表中可用頁的數量，該值預設為1024，如：

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lru_scan_depth';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_lru_scan_depth | 1024 |

+-----------------------+-------+

3、Async/Sync Flush Checkpoint

指的是重做日誌檔案不可用的情況，這時需要強制將一些頁重新整理回磁碟，而此時髒頁是從髒頁列表中選取的。若將已經寫入到重做日誌的LSN記為redo_lsn，將已經重新整理回磁碟最新頁的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。則：

當checkpoint_age<async_water_mark時，不需要重新整理任何髒頁到磁碟； </async_water_mark時，不需要重新整理任何髒頁到磁碟；<>

當async_water_mark<checkpoint_age<sync_water_mark時觸發async flush，從flush列表中重新整理足夠的髒頁回磁碟，使得重新整理後滿足checkpoint_age<async_water_mark； </checkpoint_age

checkpoint_age>sync_water_mark這種情況一般很少發生，除非設定的重做日誌檔案太小，並且在進行類似LOAD DA他的BULK INSERT操作。此時觸發Sync Flush操作，從Flush列表中重新整理足夠的髒頁回磁碟，使得重新整理後滿足checkpoint_age<async_water_mark。 </async_water_mark。<>

可見，Async/Sync Flush Checkpoint是為了保證重做日誌的迴圈使用的可用性。在InnoDB 1.2.x版本之前，Async Flush Checkpoint會阻塞發現問題的使用者查詢執行緒，而Sync Flush Checkpoint會阻塞所有的使用者查詢執行緒，並且等待髒頁重新整理完成。從InnoDB 1.2.x版本開始——也就是MySQL 5.6版本，這部分的重新整理操作同樣放入到了單獨的Page Cleaner Thread中，故不會阻塞使用者查詢執行緒。

解釋下為什麼重做日誌檔案不可用時，這時需要強制將一些髒頁重新整理回磁碟？

因為我們知道redo的作用是保證資料庫的一致性，當資料庫異常停機時，需要藉助redo+undo進行例項恢復，redo前滾---恢復出buffer pool中的髒資料（包括已經commit還沒有重新整理到磁碟的，也可能包括沒有commit，但是已經重新整理到磁碟的，）然後藉助undo完成回滾---將沒有commit，但是已經重新整理到磁碟的資料，回滾到之前的狀態。那麼為啥重做日誌檔案不可用時，這時需要強制將一些髒頁重新整理回磁碟？原因就在於，redo 是迴圈覆寫的，當redo log 檔案不可用，也就是說此時所有的redo 檔案裡面的redo都是例項恢復需要的，也就是不能被覆蓋的redo, 那麼什麼是例項恢復需要的redo呢？就是buffer pool中的的髒資料，還沒有重新整理到磁碟，而這些髒資料相關的redo是不能被覆蓋的，這些redo就是例項恢復需要的redo，所以沒有可用的重做日誌檔案，需要強制將一些髒頁重新整理回磁碟，這樣就會有一些redo是例項恢復不需要的了，也就可以被覆蓋了。

4、Dirty Page too much

即髒頁的數量太多，導致InnoDB儲存引擎強制進行Checkpoint。其目的總的來說還是為了保證緩衝池中有足夠可用的頁。其可由引數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表示，當緩衝池中髒頁的數量佔據75%時，強制進行Checkpoint，重新整理一部分的髒頁到磁碟。在InnoDB 1.0.x版本之前，該引數預設值為90，之後的版本都為75，其可以透過引數innodb_max_dirty_pages_pct來設定；

總結下redo重新整理的條件（因為重新整理innodb_buffer_pool中的髒資料之前需要重新整理redo,所以觸發重新整理buffer_pool會同時觸發重新整理redo）：

1）當redo log buffer達到一定比值後，

2）重新整理innodb_buffer_pool中的髒資料之前，

3）redo log buffer滿的時候，沒有可用buffer；

4）每秒重新整理一次；

5）commit的時候；

6）資料庫關閉時發生harp Checkpoint，觸發將所有髒頁刷回磁碟

7）手工flush logs;

8）重做日誌不可用時，觸發重新整理innodb_buffer_pool中的髒資料，進而觸發redo重新整理；

三：MySQL binlog： 主從同步 主庫binlog先寫入到 binlog_buffer中，然後重新整理到磁層磁碟也就是binlog檔案，主庫dump程式讀取的binlog檔案，傳送給從庫；

binlog日誌是針對整個MySQL server而言的，前面介紹的redo和undo是針對innodb引擎而言的，binlog的存在就是方便那些不支援事務的引擎表來同步資料到slave；

那麼到底是先重新整理redo還是先寫binlog呢？

伴隨著這個問題，我重點說下，MySQL innodb 引擎事務commit的過程：

MySQL為了保證master和slave的資料一致性，就必須保證binlog和InnoDB redo日誌的一致性，為此MySQL引入二階段提交（two phase commit or 2pc），MySQL透過兩階段提交(內部XA的兩階段提交)很好地解決了這一問題，兩階段提交關鍵在於保證redo刷盤之後才能重新整理binlog到底層檔案，以 binlog 的寫入與否作為事務提交成功與否的標誌，最後判斷 binlog中是否有 redo裡的xid,MySQL5.6以前，為了保證資料庫上層二進位制日誌的寫入順序和InnoDB層的事務提交順序一致，MySQL資料庫內部使用了prepare_commit_mutex鎖。但是持有這把鎖之後，會導致組提交失敗；直到MySQL5.6之後，才解決了這個問題，藉助序列來保證binlog重新整理也可以組提交；關於redo 和binlog組提交，請看下一篇文章，

事務崩潰恢復過程如下：

1.崩潰恢復時，掃描最後一個Binlog檔案，提取其中的xid；

2.InnoDB維持了狀態為Prepare的事務連結串列（commit兩階段提交中的第一階段，為Prepare階段，會把事務設定為Prepare狀態）將這些事務的xid和Binlog中記錄的xid做比較，如果在Binlog中存在，則提交，否則回滾事務。

透過這種方式，可以讓InnoDB redo 和Binlog中的事務狀態保持一致。

四：簡單介紹下MySQL的後臺程式：

InnoDB儲存引擎是多執行緒模型，因此其後臺有多個不同的後臺執行緒，負責處理不同的任務：

1）Master Thread

Master Thread是一個非常核心的後臺執行緒，主要負責將緩衝池中的資料非同步重新整理到磁碟，保證資料的一致性。

2）IO Thread

InnoDB儲存引擎中大量使用了Async IO來處理寫IO請求，這樣可以極大提高資料庫的效能，而IO Thread的主要工作是負責這些IO請求的回撥處理，可以使用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，但是關於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 引數進行設定：

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

事務被提交後，其所使用的undo log可能不再需要，因此需要PurgeThread來回收已經使用並分配的undo頁。從InnoDB1.1版本開始，purge操作可以獨立到單獨的執行緒中進行，以此來減輕Master Thread的工作，從而提高CPU的使用率、提升儲存引擎的效能。可以透過在MySQL資料庫的配置檔案中新增相關的命令來啟用獨立的Purge Thread，如下引數：

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儲存引擎的效能。
然後回答最開始的問題: