MySQL 日志(redo log 和 undo log) 都是什么鬼？
點擊關注公眾號，Java干貨及時送達
作者：駿馬金龍 出處：https://www.cnblogs.com/f-ck-need-u/
undo log不是redo log的逆向過程，其實它們都算是用來恢復的日志：
1.redo log通常是物理日志，記錄的是數(shù)據(jù)頁的物理修改，而不是某一行或某幾行修改成怎樣怎樣，它用來恢復提交后的物理數(shù)據(jù)頁(恢復數(shù)據(jù)頁，且只能恢復到最后一次提交的位置)。
2.undo用來回滾行記錄到某個版本。undo log一般是邏輯日志，根據(jù)每行記錄進行記錄。
1.redo log
1.1 redo log和二進制日志的區(qū)別
redo log不是二進制日志。雖然二進制日志中也記錄了innodb表的很多操作，也能實現(xiàn)重做的功能，但是它們之間有很大區(qū)別。MySQL 系列知識點我都整理成面試題了帶全部答案，關注公眾號Java技術?；貜兔嬖嚝@取。
二進制日志是在存儲引擎的上層產(chǎn)生的，不管是什么存儲引擎，對數(shù)據(jù)庫進行了修改都會產(chǎn)生二進制日志。而redo log是innodb層產(chǎn)生的，只記錄該存儲引擎中表的修改。并且二進制日志先于redo log被記錄。具體的見后文group commit小結。 二進制日志記錄操作的方法是邏輯性的語句。即便它是基于行格式的記錄方式，其本質也還是邏輯的SQL設置，如該行記錄的每列的值是多少。而redo log是在物理格式上的日志，它記錄的是數(shù)據(jù)庫中每個頁的修改。 二進制日志只在每次事務提交的時候一次性寫入緩存中的日志"文件"(對于非事務表的操作，則是每次執(zhí)行語句成功后就直接寫入)。而redo log在數(shù)據(jù)準備修改前寫入緩存中的redo log中，然后才對緩存中的數(shù)據(jù)執(zhí)行修改操作；而且保證在發(fā)出事務提交指令時，先向緩存中的redo log寫入日志，寫入完成后才執(zhí)行提交動作。 因為二進制日志只在提交的時候一次性寫入，所以二進制日志中的記錄方式和提交順序有關，且一次提交對應一次記錄。而redo log中是記錄的物理頁的修改，redo log文件中同一個事務可能多次記錄，最后一個提交的事務記錄會覆蓋所有未提交的事務記錄。例如事務T1，可能在redo log中記錄了 T1-1,T1-2,T1-3，T1* 共4個操作，其中 T1* 表示最后提交時的日志記錄，所以對應的數(shù)據(jù)頁最終狀態(tài)是 T1* 對應的操作結果。而且redo log是并發(fā)寫入的，不同事務之間的不同版本的記錄會穿插寫入到redo log文件中，例如可能redo log的記錄方式如下：T1-1,T1-2,T2-1,T2-2,T2*,T1-3,T1* 。 事務日志記錄的是物理頁的情況，它具有冪等性，因此記錄日志的方式極其簡練。冪等性的意思是多次操作前后狀態(tài)是一樣的，例如新插入一行后又刪除該行，前后狀態(tài)沒有變化。而二進制日志記錄的是所有影響數(shù)據(jù)的操作，記錄的內容較多。例如插入一行記錄一次，刪除該行又記錄一次。
1.2 redo log的基本概念
redo log包括兩部分：一是內存中的日志緩沖(redo log buffer)，該部分日志是易失性的；二是磁盤上的重做日志文件(redo log file)，該部分日志是持久的。
在概念上，innodb通過force log at commit機制實現(xiàn)事務的持久性，即在事務提交的時候，必須先將該事務的所有事務日志寫入到磁盤上的redo log file和undo log file中進行持久化。
為了確保每次日志都能寫入到事務日志文件中，在每次將log buffer中的日志寫入日志文件的過程中都會調用一次操作系統(tǒng)的fsync操作(即fsync()系統(tǒng)調用)。因為MariaDB/MySQL是工作在用戶空間的，MariaDB/MySQL的log buffer處于用戶空間的內存中。要寫入到磁盤上的log file中(redo:ib_logfileN文件,undo:share tablespace或.ibd文件)，中間還要經(jīng)過操作系統(tǒng)內核空間的os buffer，調用fsync()的作用就是將OS buffer中的日志刷到磁盤上的log file中。
也就是說，從redo log buffer寫日志到磁盤的redo log file中，過程如下：
在此處需要注意一點，一般所說的log file并不是磁盤上的物理日志文件，而是操作系統(tǒng)緩存中的log file，官方手冊上的意思也是如此(例如：With a value of 2, the contents of theInnoDB log buffer are written to the log fileafter each transaction commit andthe log file is flushed to disk approximately once per second)。但說實話，這不太好理解，既然都稱為file了，應該已經(jīng)屬于物理文件了。所以在本文后續(xù)內容中都以os buffer或者file system buffer來表示官方手冊中所說的Log file，然后log file則表示磁盤上的物理日志文件，即log file on disk。
另外，之所以要經(jīng)過一層os buffer，是因為open日志文件的時候，open沒有使用O_DIRECT標志位，該標志位意味著繞過操作系統(tǒng)層的os buffer，IO直寫到底層存儲設備。不使用該標志位意味著將日志進行緩沖，緩沖到了一定容量，或者顯式fsync()才會將緩沖中的刷到存儲設備。使用該標志位意味著每次都要發(fā)起系統(tǒng)調用。比如寫abcde，不使用o_direct將只發(fā)起一次系統(tǒng)調用，使用o_object將發(fā)起5次系統(tǒng)調用。
MySQL支持用戶自定義在commit時如何將log buffer中的日志刷log file中。這種控制通過變量 innodb_flush_log_at_trx_commit 的值來決定。該變量有3種值：0、1、2，默認為1。但注意，這個變量只是控制commit動作是否刷新log buffer到磁盤。
當設置為1的時候，事務每次提交都會將log buffer中的日志寫入os buffer并調用fsync()刷到log file on disk中。這種方式即使系統(tǒng)崩潰也不會丟失任何數(shù)據(jù)，但是因為每次提交都寫入磁盤，IO的性能較差。 當設置為0的時候，事務提交時不會將log buffer中日志寫入到os buffer，而是每秒寫入os buffer并調用fsync()寫入到log file on disk中。也就是說設置為0時是(大約)每秒刷新寫入到磁盤中的，當系統(tǒng)崩潰，會丟失1秒鐘的數(shù)據(jù)。 當設置為2的時候，每次提交都僅寫入到os buffer，然后是每秒調用fsync()將os buffer中的日志寫入到log file on disk。
注意，有一個變量 innodb_flush_log_at_timeout 的值為1秒，該變量表示的是刷日志的頻率，很多人誤以為是控制 innodb_flush_log_at_trx_commit 值為0和2時的1秒頻率，實際上并非如此。測試時將頻率設置為5和設置為1，當 innodb_flush_log_at_trx_commit 設置為0和2的時候性能基本都是不變的。關于這個頻率是控制什么的，在后面的"刷日志到磁盤的規(guī)則"中會說。
在主從復制結構中，要保證事務的持久性和一致性，需要對日志相關變量設置為如下：
如果啟用了二進制日志，則設置sync_binlog=1，即每提交一次事務同步寫到磁盤中。 總是設置innodb_flush_log_at_trx_commit=1，即每提交一次事務都寫到磁盤中。
上述兩項變量的設置保證了：每次提交事務都寫入二進制日志和事務日志，并在提交時將它們刷新到磁盤中。
選擇刷日志的時間會嚴重影響數(shù)據(jù)修改時的性能，特別是刷到磁盤的過程。下例就測試了 innodb_flush_log_at_trx_commit 分別為0、1、2時的差距。
#創(chuàng)建測試表
drop table if exists test_flush_log;
create table test_flush_log(id int,name char(50))engine=innodb;
#創(chuàng)建插入指定行數(shù)的記錄到測試表中的存儲過程
drop procedure if exists proc;
delimiter $$
create procedure proc(i int)
begin
declare s int default 1;
declare c char(50) default repeat('a',50);
while s<=i do
start transaction;
insert into test_flush_log values(null,c);
commit;
set s=s+1;
end while;
end$$
delimiter ;
當前環(huán)境下， innodb_flush_log_at_trx_commit 的值為1，即每次提交都刷日志到磁盤。測試此時插入10W條記錄的時間。
mysql> call proc(100000);
Query OK, 0 rows affected (15.48 sec)
結果是15.48秒。
再測試值為2的時候，即每次提交都刷新到os buffer，但每秒才刷入磁盤中。
mysql> set @@global.innodb_flush_log_at_trx_commit=2;
mysql> truncate test_flush_log;
mysql> call proc(100000);
Query OK, 0 rows affected (3.41 sec)
結果插入時間大減，只需3.41秒。
最后測試值為0的時候，即每秒才刷到os buffer和磁盤。
mysql> set @@global.innodb_flush_log_at_trx_commit=0;
mysql> truncate test_flush_log;
mysql> call proc(100000);
Query OK, 0 rows affected (2.10 sec)
結果只有2.10秒。
最后可以發(fā)現(xiàn)，其實值為2和0的時候，它們的差距并不太大，但2卻比0要安全的多。它們都是每秒從os buffer刷到磁盤，它們之間的時間差體現(xiàn)在log buffer刷到os buffer上。因為將log buffer中的日志刷新到os buffer只是內存數(shù)據(jù)的轉移，并沒有太大的開銷，所以每次提交和每秒刷入差距并不大?？梢詼y試插入更多的數(shù)據(jù)來比較，以下是插入100W行數(shù)據(jù)的情況。從結果可見，值為2和0的時候差距并不大，但值為1的性能卻差太多。
盡管設置為0和2可以大幅度提升插入性能，但是在故障的時候可能會丟失1秒鐘數(shù)據(jù)，這1秒鐘很可能有大量的數(shù)據(jù)，從上面的測試結果看，100W條記錄也只消耗了20多秒，1秒鐘大約有4W-5W條數(shù)據(jù)，盡管上述插入的數(shù)據(jù)簡單，但卻說明了數(shù)據(jù)丟失的大量性。更好的插入數(shù)據(jù)的做法是將值設置為1，然后修改存儲過程，將每次循環(huán)都提交修改為只提交一次，這樣既能保證數(shù)據(jù)的一致性，也能提升性能，修改如下：
drop procedure if exists proc;
delimiter $$
create procedure proc(i int)
begin
declare s int default 1;
declare c char(50) default repeat('a',50);
start transaction;
while s<=i DO
insert into test_flush_log values(null,c);
set s=s+1;
end while;
commit;
end$$
delimiter ;
測試值為1時的情況。
mysql> set @@global.innodb_flush_log_at_trx_commit=1;
mysql> truncate test_flush_log;
mysql> call proc(1000000);
Query OK, 0 rows affected (11.26 sec)
1.3 日志塊(log block)
innodb存儲引擎中，redo log以塊為單位進行存儲的，每個塊占512字節(jié)，這稱為redo log block。所以不管是log buffer中還是os buffer中以及redo log file on disk中，都是這樣以512字節(jié)的塊存儲的。
每個redo log block由3部分組成：日志塊頭、日志塊尾和日志主體。其中日志塊頭占用12字節(jié)，日志塊尾占用8字節(jié)，所以每個redo log block的日志主體部分只有512-12-8=492字節(jié)。
因為redo log記錄的是數(shù)據(jù)頁的變化，當一個數(shù)據(jù)頁產(chǎn)生的變化需要使用超過492字節(jié)()的redo log來記錄，那么就會使用多個redo log block來記錄該數(shù)據(jù)頁的變化。
日志塊頭包含4部分：
log_block_hdr_no：(4字節(jié))該日志塊在redo log buffer中的位置ID。 log_block_hdr_data_len：(2字節(jié))該log block中已記錄的log大小。寫滿該log block時為0x200，表示512字節(jié)。 log_block_first_rec_group：(2字節(jié))該log block中第一個log的開始偏移位置。 lock_block_checkpoint_no：(4字節(jié))寫入檢查點信息的位置。
關于log block塊頭的第三部分 log_block_first_rec_group ，因為有時候一個數(shù)據(jù)頁產(chǎn)生的日志量超出了一個日志塊，這是需要用多個日志塊來記錄該頁的相關日志。例如，某一數(shù)據(jù)頁產(chǎn)生了552字節(jié)的日志量，那么需要占用兩個日志塊，第一個日志塊占用492字節(jié)，第二個日志塊需要占用60個字節(jié)，那么對于第二個日志塊來說，它的第一個log的開始位置就是73字節(jié)(60+12)。如果該部分的值和 log_block_hdr_data_len 相等，則說明該log block中沒有新開始的日志塊，即表示該日志塊用來延續(xù)前一個日志塊。
日志尾只有一個部分：log_block_trl_no ，該值和塊頭的 log_block_hdr_no 相等。
上面所說的是一個日志塊的內容，在redo log buffer或者redo log file on disk中，由很多l(xiāng)og block組成。如下圖：
1.4 log group和redo log file
log group表示的是redo log group，一個組內由多個大小完全相同的redo log file組成。組內redo log file的數(shù)量由變量 innodb_log_files_group 決定，默認值為2，即兩個redo log file。這個組是一個邏輯的概念，并沒有真正的文件來表示這是一個組，但是可以通過變量 innodb_log_group_home_dir 來定義組的目錄，redo log file都放在這個目錄下，默認是在datadir下。
mysql> show global variables like "innodb_log%";
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| innodb_log_buffer_size | 8388608 |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
+-----------------------------+----------+
[root@xuexi data]# ll /mydata/data/ib*
-rw-rw---- 1 mysql mysql 79691776 Mar 30 23:12 /mydata/data/ibdata1
-rw-rw---- 1 mysql mysql 50331648 Mar 30 23:12 /mydata/data/ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar 30 23:12 /mydata/data/ib_logfile1
可以看到在默認的數(shù)據(jù)目錄下，有兩個ib_logfile開頭的文件，它們就是log group中的redo log file，而且它們的大小完全一致且等于變量 innodb_log_file_size 定義的值。第一個文件ibdata1是在沒有開啟 innodb_file_per_table 時的共享表空間文件，對應于開啟 innodb_file_per_table 時的.ibd文件。
在innodb將log buffer中的redo log block刷到這些log file中時，會以追加寫入的方式循環(huán)輪訓寫入。即先在第一個log file（即ib_logfile0）的尾部追加寫，直到滿了之后向第二個log file（即ib_logfile1）寫。當?shù)诙€log file滿了會清空一部分第一個log file繼續(xù)寫入。
由于是將log buffer中的日志刷到log file，所以在log file中記錄日志的方式也是log block的方式。
在每個組的第一個redo log file中，前2KB記錄4個特定的部分，從2KB之后才開始記錄log block。除了第一個redo log file中會記錄，log group中的其他log file不會記錄這2KB，但是卻會騰出這2KB的空間。如下：
redo log file的大小對innodb的性能影響非常大，設置的太大，恢復的時候就會時間較長，設置的太小，就會導致在寫redo log的時候循環(huán)切換redo log file。
1.5 redo log的格式
因為innodb存儲引擎存儲數(shù)據(jù)的單元是頁(和SQL Server中一樣)，所以redo log也是基于頁的格式來記錄的。默認情況下，innodb的頁大小是16KB(由 innodb_page_size 變量控制)，一個頁內可以存放非常多的log block(每個512字節(jié))，而log block中記錄的又是數(shù)據(jù)頁的變化。
其中l(wèi)og block中492字節(jié)的部分是log body，該log body的格式分為4部分：
redo_log_type：占用1個字節(jié)，表示redo log的日志類型。 space：表示表空間的ID，采用壓縮的方式后，占用的空間可能小于4字節(jié)。 page_no：表示頁的偏移量，同樣是壓縮過的。 redo_log_body表示每個重做日志的數(shù)據(jù)部分，恢復時會調用相應的函數(shù)進行解析。例如insert語句和delete語句寫入redo log的內容是不一樣的。
如下圖，分別是insert和delete大致的記錄方式。
1.6 日志刷盤的規(guī)則
log buffer中未刷到磁盤的日志稱為臟日志(dirty log)。
在上面的說過，默認情況下事務每次提交的時候都會刷事務日志到磁盤中，這是因為變量 innodb_flush_log_at_trx_commit 的值為1。但是innodb不僅僅只會在有commit動作后才會刷日志到磁盤，這只是innodb存儲引擎刷日志的規(guī)則之一。
刷日志到磁盤有以下幾種規(guī)則：
1.發(fā)出commit動作時。已經(jīng)說明過，commit發(fā)出后是否刷日志由變量 innodb_flush_log_at_trx_commit 控制。
2.每秒刷一次。這個刷日志的頻率由變量 innodb_flush_log_at_timeout 值決定，默認是1秒。要注意，這個刷日志頻率和commit動作無關。
3.當log buffer中已經(jīng)使用的內存超過一半時。
4.當有checkpoint時，checkpoint在一定程度上代表了刷到磁盤時日志所處的LSN位置。
1.7 數(shù)據(jù)頁刷盤的規(guī)則及checkpoint
內存中(buffer pool)未刷到磁盤的數(shù)據(jù)稱為臟數(shù)據(jù)(dirty data)。由于數(shù)據(jù)和日志都以頁的形式存在，所以臟頁表示臟數(shù)據(jù)和臟日志。
上一節(jié)介紹了日志是何時刷到磁盤的，不僅僅是日志需要刷盤，臟數(shù)據(jù)頁也一樣需要刷盤。
在innodb中，數(shù)據(jù)刷盤的規(guī)則只有一個：checkpoint。但是觸發(fā)checkpoint的情況卻有幾種。不管怎樣，checkpoint觸發(fā)后，會將buffer中臟數(shù)據(jù)頁和臟日志頁都刷到磁盤。
innodb存儲引擎中checkpoint分為兩種：
sharp checkpoint：在重用redo log文件(例如切換日志文件)的時候，將所有已記錄到redo log中對應的臟數(shù)據(jù)刷到磁盤。 fuzzy checkpoint：一次只刷一小部分的日志到磁盤，而非將所有臟日志刷盤。有以下幾種情況會觸發(fā)該檢查點： master thread checkpoint：由master線程控制，每秒或每10秒刷入一定比例的臟頁到磁盤。 flush_lru_list checkpoint：從MySQL5.6開始可通過 innodb_page_cleaners 變量指定專門負責臟頁刷盤的page cleaner線程的個數(shù)，該線程的目的是為了保證lru列表有可用的空閑頁。 async/sync flush checkpoint：同步刷盤還是異步刷盤。例如還有非常多的臟頁沒刷到磁盤(非常多是多少，有比例控制)，這時候會選擇同步刷到磁盤，但這很少出現(xiàn)；如果臟頁不是很多，可以選擇異步刷到磁盤，如果臟頁很少，可以暫時不刷臟頁到磁盤 dirty page too much checkpoint：臟頁太多時強制觸發(fā)檢查點，目的是為了保證緩存有足夠的空閑空間。too much的比例由變量 innodb_max_dirty_pages_pct 控制，MySQL 5.6默認的值為75，即當臟頁占緩沖池的百分之75后，就強制刷一部分臟頁到磁盤。
由于刷臟頁需要一定的時間來完成，所以記錄檢查點的位置是在每次刷盤結束之后才在redo log中標記的。
MySQL停止時是否將臟數(shù)據(jù)和臟日志刷入磁盤，由變量innodb_fast_shutdown=0控制，默認值為1，即停止時只做一部分purge，忽略大多數(shù)flush操作(但至少會刷日志)，在下次啟動的時候再flush剩余的內容，實現(xiàn)fast shutdown。
1.8 LSN超詳細分析
LSN稱為日志的邏輯序列號(log sequence number)，在innodb存儲引擎中，lsn占用8個字節(jié)。LSN的值會隨著日志的寫入而逐漸增大。
根據(jù)LSN，可以獲取到幾個有用的信息：
1.數(shù)據(jù)頁的版本信息。
2.寫入的日志總量，通過LSN開始號碼和結束號碼可以計算出寫入的日志量。
3.可知道檢查點的位置。
實際上還可以獲得很多隱式的信息。
LSN不僅存在于redo log中，還存在于數(shù)據(jù)頁中，在每個數(shù)據(jù)頁的頭部，有一個fil_page_lsn記錄了當前頁最終的LSN值是多少。通過數(shù)據(jù)頁中的LSN值和redo log中的LSN值比較，如果頁中的LSN值小于redo log中LSN值，則表示數(shù)據(jù)丟失了一部分，這時候可以通過redo log的記錄來恢復到redo log中記錄的LSN值時的狀態(tài)。
redo log的lsn信息可以通過 show engine innodb status 來查看。MySQL 5.5版本的show結果中只有3條記錄，沒有pages flushed up to。
mysql> show engine innodb stauts
---
LOG
---
Log sequence number 2225502463
Log flushed up to 2225502463
Pages flushed up to 2225502463
Last checkpoint at 2225502463
0 pending log writes, 0 pending chkp writes
3201299 log i/o's done, 0.00 log i/o's/second
其中：
log sequence number就是當前的redo log(in buffer)中的lsn； log flushed up to是刷到redo log file on disk中的lsn； pages flushed up to是已經(jīng)刷到磁盤數(shù)據(jù)頁上的LSN； last checkpoint at是上一次檢查點所在位置的LSN。
innodb從執(zhí)行修改語句開始：
(1).首先修改內存中的數(shù)據(jù)頁，并在數(shù)據(jù)頁中記錄LSN，暫且稱之為data_in_buffer_lsn；
(2).并且在修改數(shù)據(jù)頁的同時(幾乎是同時)向redo log in buffer中寫入redo log，并記錄下對應的LSN，暫且稱之為redo_log_in_buffer_lsn；
(3).寫完buffer中的日志后，當觸發(fā)了日志刷盤的幾種規(guī)則時，會向redo log file on disk刷入重做日志，并在該文件中記下對應的LSN，暫且稱之為redo_log_on_disk_lsn；
(4).數(shù)據(jù)頁不可能永遠只停留在內存中，在某些情況下，會觸發(fā)checkpoint來將內存中的臟頁(數(shù)據(jù)臟頁和日志臟頁)刷到磁盤，所以會在本次checkpoint臟頁刷盤結束時，在redo log中記錄checkpoint的LSN位置，暫且稱之為checkpoint_lsn。
(5).要記錄checkpoint所在位置很快，只需簡單的設置一個標志即可，但是刷數(shù)據(jù)頁并不一定很快，例如這一次checkpoint要刷入的數(shù)據(jù)頁非常多。也就是說要刷入所有的數(shù)據(jù)頁需要一定的時間來完成，中途刷入的每個數(shù)據(jù)頁都會記下當前頁所在的LSN，暫且稱之為data_page_on_disk_lsn。
詳細說明如下圖：
上圖中，從上到下的橫線分別代表：時間軸、buffer中數(shù)據(jù)頁中記錄的LSN(data_in_buffer_lsn)、磁盤中數(shù)據(jù)頁中記錄的LSN(data_page_on_disk_lsn)、buffer中重做日志記錄的LSN(redo_log_in_buffer_lsn)、磁盤中重做日志文件中記錄的LSN(redo_log_on_disk_lsn)以及檢查點記錄的LSN(checkpoint_lsn)。
假設在最初時(12:0:00)所有的日志頁和數(shù)據(jù)頁都完成了刷盤，也記錄好了檢查點的LSN，這時它們的LSN都是完全一致的。
假設此時開啟了一個事務，并立刻執(zhí)行了一個update操作，執(zhí)行完成后，buffer中的數(shù)據(jù)頁和redo log都記錄好了更新后的LSN值，假設為110。這時候如果執(zhí)行 show engine innodb status 查看各LSN的值，即圖中①處的位置狀態(tài)，結果會是：
log sequence number(110) > log flushed up to(100) = pages flushed up to = last checkpoint at
之后又執(zhí)行了一個delete語句，LSN增長到150。等到12:00:01時，觸發(fā)redo log刷盤的規(guī)則(其中有一個規(guī)則是 innodb_flush_log_at_timeout 控制的默認日志刷盤頻率為1秒)，這時redo log file on disk中的LSN會更新到和redo log in buffer的LSN一樣，所以都等于150，這時 show engine innodb status ，即圖中②的位置，結果將會是：
log sequence number(150) = log flushed up to > pages flushed up to(100) = last checkpoint at
再之后，執(zhí)行了一個update語句，緩存中的LSN將增長到300，即圖中③的位置。
假設隨后檢查點出現(xiàn)，即圖中④的位置，正如前面所說，檢查點會觸發(fā)數(shù)據(jù)頁和日志頁刷盤，但需要一定的時間來完成，所以在數(shù)據(jù)頁刷盤還未完成時，檢查點的LSN還是上一次檢查點的LSN，但此時磁盤上數(shù)據(jù)頁和日志頁的LSN已經(jīng)增長了，即：
log sequence number > log flushed up to 和 pages flushed up to > last checkpoint at
但是log flushed up to和pages flushed up to的大小無法確定，因為日志刷盤可能快于數(shù)據(jù)刷盤，也可能等于，還可能是慢于。但是checkpoint機制有保護數(shù)據(jù)刷盤速度是慢于日志刷盤的：當數(shù)據(jù)刷盤速度超過日志刷盤時，將會暫時停止數(shù)據(jù)刷盤，等待日志刷盤進度超過數(shù)據(jù)刷盤。
等到數(shù)據(jù)頁和日志頁刷盤完畢，即到了位置⑤的時候，所有的LSN都等于300。
隨著時間的推移到了12:00:02，即圖中位置⑥，又觸發(fā)了日志刷盤的規(guī)則，但此時buffer中的日志LSN和磁盤中的日志LSN是一致的，所以不執(zhí)行日志刷盤，即此時 show engine innodb status 時各種lsn都相等。
隨后執(zhí)行了一個insert語句，假設buffer中的LSN增長到了800，即圖中位置⑦。此時各種LSN的大小和位置①時一樣。
隨后執(zhí)行了提交動作，即位置⑧。默認情況下，提交動作會觸發(fā)日志刷盤，但不會觸發(fā)數(shù)據(jù)刷盤，所以 show engine innodb status 的結果是：
log sequence number = log flushed up to > pages flushed up to = last checkpoint at
最后隨著時間的推移，檢查點再次出現(xiàn)，即圖中位置⑨。但是這次檢查點不會觸發(fā)日志刷盤，因為日志的LSN在檢查點出現(xiàn)之前已經(jīng)同步了。假設這次數(shù)據(jù)刷盤速度極快，快到一瞬間內完成而無法捕捉到狀態(tài)的變化，這時 show engine innodb status 的結果將是各種LSN相等。
1.9 innodb的恢復行為
在啟動innodb的時候，不管上次是正常關閉還是異常關閉，總是會進行恢復操作。
因為redo log記錄的是數(shù)據(jù)頁的物理變化，因此恢復的時候速度比邏輯日志(如二進制日志)要快很多。而且，innodb自身也做了一定程度的優(yōu)化，讓恢復速度變得更快。
重啟innodb時，checkpoint表示已經(jīng)完整刷到磁盤上data page上的LSN，因此恢復時僅需要恢復從checkpoint開始的日志部分。例如，當數(shù)據(jù)庫在上一次checkpoint的LSN為10000時宕機，且事務是已經(jīng)提交過的狀態(tài)。啟動數(shù)據(jù)庫時會檢查磁盤中數(shù)據(jù)頁的LSN，如果數(shù)據(jù)頁的LSN小于日志中的LSN，則會從檢查點開始恢復。
還有一種情況，在宕機前正處于checkpoint的刷盤過程，且數(shù)據(jù)頁的刷盤進度超過了日志頁的刷盤進度。這時候一宕機，數(shù)據(jù)頁中記錄的LSN就會大于日志頁中的LSN，在重啟的恢復過程中會檢查到這一情況，這時超出日志進度的部分將不會重做，因為這本身就表示已經(jīng)做過的事情，無需再重做。
另外，事務日志具有冪等性，所以多次操作得到同一結果的行為在日志中只記錄一次。而二進制日志不具有冪等性，多次操作會全部記錄下來，在恢復的時候會多次執(zhí)行二進制日志中的記錄，速度就慢得多。例如，某記錄中id初始值為2，通過update將值設置為了3，后來又設置成了2，在事務日志中記錄的將是無變化的頁，根本無需恢復；而二進制會記錄下兩次update操作，恢復時也將執(zhí)行這兩次update操作，速度比事務日志恢復更慢。
1.10 和redo log有關的幾個變量
innodb_flush_log_at_trx_commit=0# 指定何時將事務日志刷到磁盤，默認為1。 0表示每秒將"log buffer"同步到"os buffer"且從"os buffer"刷到磁盤日志文件中。 1表示每事務提交都將"log buffer"同步到"os buffer"且從"os buffer"刷到磁盤日志文件中。 2表示每事務提交都將"log buffer"同步到"os buffer"但每秒才從"os buffer"刷到磁盤日志文件中。 innodb_log_buffer_size：# log buffer的大小，默認8M innodb_log_file_size：#事務日志的大小，默認5M innodb_log_files_group =2：# 事務日志組中的事務日志文件個數(shù)，默認2個 innodb_log_group_home_dir =./：# 事務日志組路徑，當前目錄表示數(shù)據(jù)目錄 innodb_mirrored_log_groups =1：# 指定事務日志組的鏡像組個數(shù)，但鏡像功能好像是強制關閉的，所以只有一個log group。在MySQL5.7中該變量已經(jīng)移除。
2.undo log
2.1 基本概念
undo log有兩個作用：提供回滾和多個行版本控制(MVCC)。
在數(shù)據(jù)修改的時候，不僅記錄了redo，還記錄了相對應的undo，如果因為某些原因導致事務失敗或回滾了，可以借助該undo進行回滾。
undo log和redo log記錄物理日志不一樣，它是邏輯日志。可以認為當delete一條記錄時，undo log中會記錄一條對應的insert記錄，反之亦然，當update一條記錄時，它記錄一條對應相反的update記錄。
當執(zhí)行rollback時，就可以從undo log中的邏輯記錄讀取到相應的內容并進行回滾。有時候應用到行版本控制的時候，也是通過undo log來實現(xiàn)的：當讀取的某一行被其他事務鎖定時，它可以從undo log中分析出該行記錄以前的數(shù)據(jù)是什么，從而提供該行版本信息，讓用戶實現(xiàn)非鎖定一致性讀取。
undo log是采用段(segment)的方式來記錄的，每個undo操作在記錄的時候占用一個undo log segment。
另外，undo log也會產(chǎn)生redo log，因為undo log也要實現(xiàn)持久性保護。
2.2 undo log的存儲方式
innodb存儲引擎對undo的管理采用段的方式。rollback segment稱為回滾段，每個回滾段中有1024個undo log segment。
在以前老版本，只支持1個rollback segment，這樣就只能記錄1024個undo log segment。后來MySQL5.5可以支持128個rollback segment，即支持128*1024個undo操作，還可以通過變量 innodb_undo_logs (5.6版本以前該變量是 innodb_rollback_segments )自定義多少個rollback segment，默認值為128。
undo log默認存放在共享表空間中。
[root@xuexi data]# ll /mydata/data/ib*
-rw-rw---- 1 mysql mysql 79691776 Mar 31 01:42 /mydata/data/ibdata1
-rw-rw---- 1 mysql mysql 50331648 Mar 31 01:42 /mydata/data/ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar 31 01:42 /mydata/data/ib_logfile1
如果開啟了 innodb_file_per_table ，將放在每個表的.ibd文件中。
在MySQL5.6中，undo的存放位置還可以通過變量 innodb_undo_directory 來自定義存放目錄，默認值為"."表示datadir。
默認rollback segment全部寫在一個文件中，但可以通過設置變量 innodb_undo_tablespaces 平均分配到多少個文件中。該變量默認值為0，即全部寫入一個表空間文件。該變量為靜態(tài)變量，只能在數(shù)據(jù)庫示例停止狀態(tài)下修改，如寫入配置文件或啟動時帶上對應參數(shù)。但是innodb存儲引擎在啟動過程中提示，不建議修改為非0的值，如下：
2017-03-31 13:16:00 7f665bfab720 InnoDB: Expected to open 3 undo tablespaces but was able
2017-03-31 13:16:00 7f665bfab720 InnoDB: to find only 0 undo tablespaces.
2017-03-31 13:16:00 7f665bfab720 InnoDB: Set the innodb_undo_tablespaces parameter to the
2017-03-31 13:16:00 7f665bfab720 InnoDB: correct value and retry. Suggested value is 0
2.3 和undo log相關的變量
undo相關的變量在MySQL5.6中已經(jīng)變得很少。如下：它們的意義在上文中已經(jīng)解釋了。
mysql> show variables like "%undo%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_undo_directory | . |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
+-------------------------+-------+
2.4 delete/update操作的內部機制
當事務提交的時候，innodb不會立即刪除undo log，因為后續(xù)還可能會用到undo log，如隔離級別為repeatable read時，事務讀取的都是開啟事務時的最新提交行版本，只要該事務不結束，該行版本就不能刪除，即undo log不能刪除。
但是在事務提交的時候，會將該事務對應的undo log放入到刪除列表中，未來通過purge來刪除。并且提交事務時，還會判斷undo log分配的頁是否可以重用，如果可以重用，則會分配給后面來的事務，避免為每個獨立的事務分配獨立的undo log頁而浪費存儲空間和性能。
通過undo log記錄delete和update操作的結果發(fā)現(xiàn)：(insert操作無需分析，就是插入行而已)
delete操作實際上不會直接刪除，而是將delete對象打上delete flag，標記為刪除，最終的刪除操作是purge線程完成的。 update分為兩種情況：update的列是否是主鍵列。 如果不是主鍵列，在undo log中直接反向記錄是如何update的。即update是直接進行的。 如果是主鍵列，update分兩部執(zhí)行：先刪除該行，再插入一行目標行。
3.binlog和事務日志的先后順序及group commit
如果事務不是只讀事務，即涉及到了數(shù)據(jù)的修改，默認情況下會在commit的時候調用fsync()將日志刷到磁盤，保證事務的持久性。
但是一次刷一個事務的日志性能較低，特別是事務集中在某一時刻時事務量非常大的時候。innodb提供了group commit功能，可以將多個事務的事務日志通過一次fsync()刷到磁盤中。
因為事務在提交的時候不僅會記錄事務日志，還會記錄二進制日志，但是它們誰先記錄呢？二進制日志是MySQL的上層日志，先于存儲引擎的事務日志被寫入。
在MySQL5.6以前，當事務提交(即發(fā)出commit指令)后，MySQL接收到該信號進入commit prepare階段；進入prepare階段后，立即寫內存中的二進制日志，寫完內存中的二進制日志后就相當于確定了commit操作；然后開始寫內存中的事務日志；最后將二進制日志和事務日志刷盤，它們如何刷盤，分別由變量 sync_binlog 和 innodb_flush_log_at_trx_commit 控制。
但因為要保證二進制日志和事務日志的一致性，在提交后的prepare階段會啟用一個prepare_commit_mutex鎖來保證它們的順序性和一致性。但這樣會導致開啟二進制日志后group commmit失效，特別是在主從復制結構中，幾乎都會開啟二進制日志。
在MySQL5.6中進行了改進。提交事務時，在存儲引擎層的上一層結構中會將事務按序放入一個隊列，隊列中的第一個事務稱為leader，其他事務稱為follower，leader控制著follower的行為。雖然順序還是一樣先刷二進制，再刷事務日志，但是機制完全改變了：刪除了原來的prepare_commit_mutex行為，也能保證即使開啟了二進制日志，group commit也是有效的。
MySQL5.6中分為3個步驟：flush階段、sync階段、commit階段。
flush階段：向內存中寫入每個事務的二進制日志。 sync階段：將內存中的二進制日志刷盤。若隊列中有多個事務，那么僅一次fsync操作就完成了二進制日志的刷盤操作。這在MySQL5.6中稱為BLGC(binary log group commit)。 commit階段：leader根據(jù)順序調用存儲引擎層事務的提交，由于innodb本就支持group commit，所以解決了因為鎖 prepare_commit_mutex 而導致的group commit失效問題。
在flush階段寫入二進制日志到內存中，但是不是寫完就進入sync階段的，而是要等待一定的時間，多積累幾個事務的binlog一起進入sync階段，等待時間由變量 binlog_max_flush_queue_time 決定，默認值為0表示不等待直接進入sync，設置該變量為一個大于0的值的好處是group中的事務多了，性能會好一些，但是這樣會導致事務的響應時間變慢，所以建議不要修改該變量的值，除非事務量非常多并且不斷的在寫入和更新。
進入到sync階段，會將binlog從內存中刷入到磁盤，刷入的數(shù)量和單獨的二進制日志刷盤一樣，由變量 sync_binlog 控制。
當有一組事務在進行commit階段時，其他新事務可以進行flush階段，它們本就不會相互阻塞，所以group commit會不斷生效。當然，group commit的性能和隊列中的事務數(shù)量有關，如果每次隊列中只有1個事務，那么group commit和單獨的commit沒什么區(qū)別，當隊列中事務越來越多時，即提交事務越多越快時，group commit的效果越明顯。
往 期 推 薦
1、靈魂一問：你的登錄接口真的安全嗎？ 2、HashMap 中這些設計，絕了~ 3、在 IntelliJ IDEA 中這樣使用 Git，賊方便了！ 4、計算機時間到底是怎么來的？程序員必看的時間知識！ 5、這些IDEA的優(yōu)化設置趕緊安排起來，效率提升杠杠的！ 6、21 款 yyds 的 IDEA插件 7、真香！用 IDEA 神器看源碼，效率真高！ 點分享
點收藏
點點贊
點在看
