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