本篇文章帶大家了解一下MySQL的相關(guān)知識(shí)，深入聊聊MySQL基礎(chǔ)架構(gòu)與日志系統(tǒng)，希望對(duì)大家有所幫助！

一、MySQL基礎(chǔ)架構(gòu)

MySQL可以分為Server層和存儲(chǔ)引擎層兩部分

Server層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋MySQL的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過程、觸發(fā)器、視圖等

存儲(chǔ)引擎負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持InnoDB、MyISAM、Memory等多個(gè)存儲(chǔ)引擎?，F(xiàn)在最常用的存儲(chǔ)引擎是InnoDB，它從MySQL 5.5.5版本開始成為了默認(rèn)存儲(chǔ)引擎。可以通過在SQL語句中使用engin=memory來指定使用內(nèi)存引擎執(zhí)行

不同的存儲(chǔ)引擎共用一個(gè)Server層

1、連接器

連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。連接命令一般是：

mysql -h$ip -P$port -u$user -p

連接命令中的mysql是客戶端工具，用來跟服務(wù)端建立連接。在完成TCP握手后，連接器就要開始認(rèn)證身份

• 如果用戶名或密碼不對(duì)，就會(huì)收到一個(gè)"Access denied for user"的錯(cuò)誤，然后客戶端程序結(jié)束執(zhí)行
• 如果用戶名密碼認(rèn)證通過，連接器回到權(quán)限表里面查出你擁有的權(quán)限。之后，這個(gè)連接里面的權(quán)限判斷邏輯，都將依賴于此時(shí)讀到的權(quán)限

這就意味著，一個(gè)用戶成功建立連接后，即使用管理員帳號(hào)對(duì)這個(gè)用戶的權(quán)限做了修改，也不會(huì)影響已經(jīng)存在連接的權(quán)限。修改完成后，只有再新建的連接才會(huì)使用新的權(quán)限設(shè)置

連接完成后，如果你沒有后續(xù)的動(dòng)作，這個(gè)連接就處于空閑狀態(tài)，可以在show processlist命令中看到它

Command為Sleep表示此連接是一個(gè)空閑連接

客戶端如果太長時(shí)間沒動(dòng)靜，連接器就會(huì)自動(dòng)將它斷開。這個(gè)時(shí)間是由參數(shù)wait_timeout控制的。默認(rèn)值是8小時(shí)

如果在連接被斷開之后，客戶端再次發(fā)送請(qǐng)求的話，就會(huì)收到一個(gè)錯(cuò)誤提示：Lost connection to MySQL server during query。這時(shí)候就需要重新連接，然后在執(zhí)行請(qǐng)求了

數(shù)據(jù)庫里面，長連接是指連接成功后，如果客戶端持續(xù)有請(qǐng)求，則一直使用同一個(gè)連接。短連接則是指每次執(zhí)行完很少的幾次查詢就斷開連接，下次查詢?cè)僦匦陆⒁粋€(gè)

建立連接的過程通常是比較復(fù)雜的，所以建議盡量使用長連接

但是全部使用長連接后，有些時(shí)候MySQL占用內(nèi)存漲得特別快，這是因?yàn)镸ySQL在執(zhí)行過程中臨時(shí)使用的內(nèi)存是管理在連接對(duì)象里面的。這些資源會(huì)在連接斷開的時(shí)候才釋放。所以如果長連接累計(jì)下來，可能導(dǎo)致內(nèi)存占用太大，被系統(tǒng)強(qiáng)行殺掉（OOM），從現(xiàn)象看就是MySQL異常重啟了

可以通過以下兩種方案解決這個(gè)問題：

1.定期斷開長連接。使用一段時(shí)間，或者程序里面判斷執(zhí)行過一個(gè)占用內(nèi)存的大查詢后，斷開連接，之后要查詢?cè)僦剡B

2.如果使用的是MySQL5.7或更新版本，可以在每次執(zhí)行一個(gè)比較大的操作后，通過執(zhí)行mysql_reset_connection來重新初始化連接資源。這個(gè)過程不需要重連和重新做權(quán)限驗(yàn)證，但是會(huì)將連接恢復(fù)到剛剛創(chuàng)建完時(shí)的狀態(tài)

2、查詢緩存

建立連接完成后，可以執(zhí)行select語句了。MySQL拿到一個(gè)查詢請(qǐng)求后，會(huì)先到查詢緩存看看，之前是不是執(zhí)行過這條語句。之前執(zhí)行過的語句及其結(jié)果可能會(huì)以key-value對(duì)的形式，被直接緩存在內(nèi)存中。key是查詢的語句，value是查詢的結(jié)果。如果查詢能夠直接在這個(gè)緩存中找到key，那么這個(gè)value就會(huì)被直接返回給客戶端

如果語句不在查詢緩存中，就會(huì)繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后，執(zhí)行結(jié)果會(huì)被存入查詢緩存中。如果查詢命中緩存，MySQL不需要執(zhí)行后面的復(fù)雜操作，就可以直接返回結(jié)果，這個(gè)效率很高

但是大多數(shù)情況下不建議使用查詢緩存，因?yàn)椴樵兙彺娴氖Х浅ｎl繁，只要對(duì)一個(gè)表的更新，這個(gè)表上所有的查詢緩存都會(huì)被清空。對(duì)于更新壓力大的數(shù)據(jù)庫來說，查詢緩存的命中率會(huì)非常低

可以將參數(shù)query_cache_type設(shè)置成DEMAND，這樣對(duì)于默認(rèn)的SQL語句都不使用查詢緩存。而對(duì)于確定要是查詢緩存的語句，可以用SQL_CACHE顯示指定，如下面這條語句一樣：

select SQL_CACHE * from T where ID=10；

MySQL8.0版本直接將查詢緩存的整塊功能刪掉了

3、分析器

如果沒有命中查詢緩存，就要開始真正執(zhí)行語句了。MySQL首先要對(duì)SQL語句做解析

分析器會(huì)先做詞法分析。輸入的是由多個(gè)字符串和空格組成的一條SQL語句，MySQL需要識(shí)別出里面的字符串分別是什么，代表什么

select * from T where ID=10；

MySQL從輸入的select這個(gè)關(guān)鍵字識(shí)別出來，這是一個(gè)查詢語句。它也要把字符串T識(shí)別成表名T，把字符串ID識(shí)別成列ID

做完了這些識(shí)別以后，就要做語法分析。根據(jù)詞法分析的結(jié)果，語法分析器會(huì)根據(jù)語法規(guī)則，判斷這個(gè)SQL語句是否滿足MySQL語法。如果語法不對(duì)，就會(huì)收到"You have an error in your SQL syntax"的錯(cuò)誤提示

4、優(yōu)化器

經(jīng)過了分析器，在開始執(zhí)行之前，還要先經(jīng)過優(yōu)化器的處理

優(yōu)化器是在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引；或者在一個(gè)語句有多表關(guān)聯(lián)的時(shí)候，決定各個(gè)表的連接順序

5、執(zhí)行器

優(yōu)化器階段完成后，這個(gè)語句的執(zhí)行方案就確定下來了，然后進(jìn)入執(zhí)行器階段，開始執(zhí)行語句

開始執(zhí)行的時(shí)候，要先判斷一下你對(duì)這個(gè)表T有沒有執(zhí)行查詢的權(quán)限，如果沒有，就會(huì)返回沒有權(quán)限的錯(cuò)誤，如下所示

mysql> select * from T where ID=10; ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有權(quán)限，就打開表繼續(xù)執(zhí)行。打開表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的引擎定義，去使用這個(gè)引擎提供的接口

比如在表T中，ID字段沒有索引，那么執(zhí)行器的執(zhí)行流程是這樣的：

1.調(diào)用InnoDB引擎接口取這個(gè)表的第一行，判斷ID值是不是10，如果不是則跳過，如果是則將這個(gè)行存在結(jié)果集中

2.調(diào)用引擎接口取下一行，重復(fù)相同的判斷邏輯，直到取到這個(gè)表的最后一行

3.執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結(jié)果集返回給客戶端

在數(shù)據(jù)庫的慢查詢?nèi)罩局锌吹揭粋€(gè)rows_examined的字段，表示這個(gè)語句執(zhí)行過程掃描了多少行。這個(gè)值就是在執(zhí)行器每次調(diào)用引擎獲取數(shù)據(jù)行的時(shí)候累加的

在有些場景下，執(zhí)行器調(diào)用一次，在引起內(nèi)部則掃描了多行，因此引擎掃描行數(shù)跟rows_examined并不是完全相同的

二、日志系統(tǒng)

表T的創(chuàng)建語句如下，這個(gè)表有一個(gè)主鍵ID和一個(gè)整型字段c：

create table T(ID int primary key, c int);

如果要將ID=2這一行的值加1，SQL語句如下：

update T set c=c+1 where ID=2;

1、redo log（重做日志）

在MySQL中，如果每次的更新操作都需要寫進(jìn)磁盤，然后磁盤也要找到對(duì)應(yīng)的那條記錄，然后再更新，整個(gè)過程IO成本、查找成本都很高。MySQL里常說的WAL技術(shù)，全稱是Write-Ahead Logging，它的關(guān)鍵點(diǎn)就是先寫日志，再寫磁盤

當(dāng)有一條記錄需要更新的時(shí)候，InnoDB引擎就會(huì)把記錄寫到redo log里面，并更新buffer pool的page，這個(gè)時(shí)候更新就算完成了

buffer pool是物理頁的緩存，對(duì)InnoDB的任何修改操作都會(huì)首先在buffer pool的page上進(jìn)行，然后這樣的頁面將被標(biāo)記為臟頁并被放到專門的flush list上，后續(xù)將由專門的刷臟線程階段性的將這些頁面寫入磁盤

InnoDB的redo log是固定大小的，比如可以配置為一組4個(gè)文件，每個(gè)文件的大小是1GB，從頭開始寫，寫到末尾就又回到開頭循環(huán)寫

write pos是當(dāng)前記錄的位置，一邊寫一邊后移，寫到第3號(hào)文件末尾后就回到0號(hào)文件開頭。check point是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件

write pos和check point之間空著的部分，可以用來記錄新的操作。如果write pos追上check point，這時(shí)候不能再執(zhí)行新的更新，需要停下來擦掉一些記錄，把check point推進(jìn)一下

有了redo log，InnoDB就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為crash-safe

2、binlog（歸檔日志）

MySQL整體來看就有兩塊：一塊是Server層，主要做的是MySQL功能層面的事情；還有一塊是引擎層，負(fù)責(zé)存儲(chǔ)相關(guān)的具體事宜。redo log是InnoDB引擎特有的日志，而Server層也有自己的日志，稱為binlog

為什么會(huì)有兩份日志？

因?yàn)樽铋_始MySQL里并沒有InnoDB引擎。MySQL自帶的引擎是MyISAM，但是MyISAM沒有crash-safe的能力，binlog日志只能用于歸檔。而InnoDB是以插件形式引入MySQL的，既然只依靠binlog是沒有crash-safe能力的，所以InnoDB使用redo log來實(shí)現(xiàn)crash-safe能力

binlog的日志格式：

binlog的格式有三種：STATEMENT，ROW，MIXED

1）、STATEMENT模式

binlog里面記錄的就是SQL語句的原文。優(yōu)點(diǎn)是并不需要記錄每一行的數(shù)據(jù)變化，減少了binlog日志量，節(jié)約IO，提高性能。缺點(diǎn)是在某些情況下會(huì)導(dǎo)致master-slave中的數(shù)據(jù)不一致(如sleep()函數(shù)， last_insert_id()，以及user-defined functions(udf)等會(huì)出現(xiàn)問題)

2）、ROW模式

不記錄每條SQL語句的上下文信息，僅需記錄哪條數(shù)據(jù)被修改了，修改成什么樣了。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過程或function或trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題。缺點(diǎn)是會(huì)產(chǎn)生大量的日志，尤其是alter table的時(shí)候會(huì)讓日志暴漲

3）、MIXED模式

以上兩種模式的混合使用，一般的復(fù)制使用STATEMENT模式保存binlog，對(duì)于STATEMENT模式無法復(fù)制的操作使用ROW模式保存binlog，MySQL會(huì)根據(jù)執(zhí)行的SQL語句選擇日志保存方式

3、redo log和binlog日志的不同

1.redo log是InnoDB引擎特有的；binlog是MySQL的Server層實(shí)現(xiàn)的，所有引擎都可以使用

2.redo log是物理日志，記錄的是在某個(gè)數(shù)據(jù)也上做了什么修改；binlog是邏輯日志，記錄的是這個(gè)語句的原始邏輯，比如給ID=2這一行的c字段加1

3.redo log是循環(huán)寫的，空間固定會(huì)用完；binlog是可以追加寫入的，binlog文件寫到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志

4、兩階段提交

執(zhí)行器和InnoDB引擎在執(zhí)行這個(gè)update語句時(shí)的內(nèi)部流程：

1.執(zhí)行器先找到引擎取ID=2這一行。ID是主鍵，引擎直接用樹搜索找到這一行。如果ID=2這一行所在的數(shù)據(jù)也本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回

2.執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個(gè)值加上1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)

3.引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到redo log里面，此時(shí)redo log處于prepare狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù)

4.執(zhí)行器生成這個(gè)操作的binlog，并把binlog寫入磁盤

5.執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的redo log改成提交狀態(tài)，更新完成

update語句的執(zhí)行流程圖如下，圖中淺色框表示在InnoDB內(nèi)部執(zhí)行的，深色框表示是在執(zhí)行器中執(zhí)行的

將redo log的寫入拆成了兩個(gè)步驟：prepare和commit，這就是兩階段提交

由于redo log和binlog是兩個(gè)獨(dú)立的邏輯，如果不用兩階段提交，要么就是先寫完redo log再寫binlog，或者先寫完binlog再寫redo log

1.先寫完redo log再寫binlog。如果在redo log寫完，binlog還沒有寫完的時(shí)候，MySQL進(jìn)程異常重啟。由于redo log寫完之后，系統(tǒng)即使崩潰，仍然能夠把數(shù)據(jù)恢復(fù)回來，所以恢復(fù)后這一行c的值是1。但是由于binlog還沒寫完就crash了，這時(shí)候binlog里面就沒有記錄這個(gè)語句，binlog中記錄的這一行c的值為0

2.先寫binlog后寫redo log。如果在binlog寫完之后crash，由于redo log還沒寫，崩潰恢復(fù)以后這個(gè)事務(wù)無效，所以這一行的c的值是0。但是binlog里面已經(jīng)記錄了把c從0改成1這個(gè)日志。所以，在之后binlog來恢復(fù)的時(shí)候就多了一個(gè)事務(wù)出來，恢復(fù)出來的這一行c的值就是1

如果不使用兩階段提交，那么數(shù)據(jù)庫的狀態(tài)就有可能和用它的日志恢復(fù)出來的庫的狀態(tài)不一致。redo log和binlog都可以用于表示事務(wù)的提交狀態(tài)，而兩階段提交就是讓這兩個(gè)狀態(tài)保持邏輯上的一致

redo log用于保證crash-safe能力。innodb_flush_log_at_trx_commit這個(gè)參數(shù)設(shè)置成1的時(shí)候，表示每次事務(wù)的redo log都直接持久化到磁盤，這樣可以保證MySQL異常重啟之后數(shù)據(jù)不丟失

sync_binlog這個(gè)參數(shù)設(shè)置成1的時(shí)候，表示每次事務(wù)的binlog都持久化到磁盤，這樣可以保證MySQL異常重啟之后binlog不丟失

三、MySQL刷臟頁

1、刷臟頁的場景

當(dāng)內(nèi)存數(shù)據(jù)頁跟磁盤數(shù)據(jù)頁不一致的時(shí)候，我們稱這個(gè)內(nèi)存頁為臟頁。內(nèi)存數(shù)據(jù)寫入到磁盤后，內(nèi)存和磁盤行的數(shù)據(jù)頁的內(nèi)容就一致了，稱為干凈頁

• 第一種場景是，InnoDB的redo log寫滿了，這時(shí)候系統(tǒng)會(huì)停止所有更新操作，把checkpoint往前推進(jìn)，redo log留出空間可以繼續(xù)寫
  
  checkpoint位置從CP推進(jìn)到CP’，就需要將兩個(gè)點(diǎn)之間的日志對(duì)應(yīng)的所有臟頁都flush到磁盤上。之后，上圖中從write pos到CP’之間就是可以再寫入的redo log的區(qū)域

• 第二種場景是，系統(tǒng)內(nèi)存不足。當(dāng)需要新的內(nèi)存頁，而內(nèi)存不夠用的時(shí)候，就要淘汰一些數(shù)據(jù)頁，空出內(nèi)存給別的數(shù)據(jù)頁使用。如果淘汰的是臟頁，就要先將臟頁寫到磁盤

這時(shí)候不能直接把內(nèi)存淘汰掉，下次需要請(qǐng)求的時(shí)候，從磁盤讀入數(shù)據(jù)頁，然后拿redo log出來應(yīng)用不就行了？

這里是從性能考慮的。如果刷臟頁一定會(huì)寫盤，就保證了每個(gè)數(shù)據(jù)頁有兩種狀態(tài)：一種是內(nèi)存里存在，內(nèi)存里就肯定是正確的結(jié)果，直接返回；另一種是內(nèi)存里沒有數(shù)據(jù)，就可以肯定數(shù)據(jù)文件上是正確的結(jié)果，讀入內(nèi)存后返回。這樣的效率最高

• 第三種場景是，MySQL認(rèn)為系統(tǒng)空閑的時(shí)候刷臟頁，當(dāng)然在系統(tǒng)忙的時(shí)候也要找時(shí)間刷一點(diǎn)臟頁
• 第四種場景是，MySQL正常關(guān)閉的時(shí)候會(huì)把內(nèi)存的臟頁都flush到磁盤上，這樣下次MySQL啟動(dòng)的時(shí)候，就可以直接從磁盤上讀數(shù)據(jù)，啟動(dòng)速度會(huì)很快

redo log寫滿了，要flush臟頁，出現(xiàn)這種情況的時(shí)候，整個(gè)系統(tǒng)就不能再接受更新了，所有的更新都必須堵住

內(nèi)存不夠用了，要先將臟頁寫到磁盤，這種情況是常態(tài)。InnoDB用緩沖池管理內(nèi)存，緩沖池中的內(nèi)存頁有三種狀態(tài)：

• 第一種是還沒有使用的
• 第二種是使用了并且是干凈頁
• 第三種是使用了并且是臟頁

InnoDB的策略是盡量使用內(nèi)存，因此對(duì)于一個(gè)長時(shí)間運(yùn)行的庫來說，未被使用的頁面很少

當(dāng)要讀入的數(shù)據(jù)頁沒有在內(nèi)存的時(shí)候，就必須到緩沖池中申請(qǐng)一個(gè)數(shù)據(jù)頁。這時(shí)候只能把最久不使用的數(shù)據(jù)頁從內(nèi)存中淘汰掉：如果要淘汰的是一個(gè)干凈頁，就直接釋放出來復(fù)用；但如果是臟頁，即必須將臟頁先刷到磁盤，變成干凈頁后才能復(fù)用

刷頁雖然是常態(tài)，但是出現(xiàn)以下兩種情況，都是會(huì)明顯影響性能的：

• 一個(gè)查詢要淘汰的臟頁個(gè)數(shù)太多，會(huì)導(dǎo)致查詢的響應(yīng)時(shí)間明顯變長
• 日志寫滿，更新全部堵住，寫性能跌為0，這種情況對(duì)敏感業(yè)務(wù)來說，是不能接受的

2、InnoDB刷臟頁的控制策略

首先，要正確地告訴InnoDB所在主機(jī)的IO能力，這樣InnoDB才能知道需要全力刷臟頁的時(shí)候，可以刷多快。參數(shù)為innodb_io_capacity，建議設(shè)置成磁盤的IOPS

InnoDB的刷盤速度就是考慮臟頁比例和redo log寫盤速度。參數(shù)innodb_max_dirty_pages_pct是臟頁比例上限，默認(rèn)值是75%。臟頁比例是通過Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的，SQL語句如下：

mysql>  select VARIABLE_VALUE into @a from performance_schema.global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty'; select VARIABLE_VALUE into @b from performance_schema.global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'; select @a/@b;

四、日志相關(guān)問題

問題一：在兩階段提交的不同時(shí)刻，MySQL異常重啟會(huì)出現(xiàn)什么現(xiàn)象

如果在圖中時(shí)刻A的地方，也就是寫入redo log處于prepare階段之后、寫binlog之前，發(fā)生了崩潰，由于此時(shí)binlog還沒寫，redo log也還沒提交，所以崩潰恢復(fù)的時(shí)候，這個(gè)事務(wù)會(huì)回滾。這時(shí)候，binlog還沒寫，所以也不會(huì)傳到備庫

如果在圖中時(shí)刻B的地方，也就是binlog寫完，redo log還沒commit前發(fā)生崩潰，那崩潰恢復(fù)的時(shí)候MySQL怎么處理？

崩潰恢復(fù)時(shí)的判斷規(guī)則：

1）如果redo log里面的事務(wù)是完整的，也就是已經(jīng)有了commit標(biāo)識(shí)，則直接提交

2）如果redo log里面的事務(wù)只有完整的prepare，則判斷對(duì)應(yīng)的事務(wù)binlog是否存在并完整

a.如果完整，則提交事務(wù)

b.否則，回滾事務(wù)

時(shí)刻B發(fā)生崩潰對(duì)應(yīng)的就是2(a)的情況，崩潰恢復(fù)過程中事務(wù)會(huì)被提交

問題二：MySQL怎么知道binlog是完整的？

一個(gè)事務(wù)的binlog是有完整格式的：

• statement格式的binlog，最后會(huì)有COMMIT
• row格式的binlog，最后會(huì)有一個(gè)XID event

問題三：redo log和binlog是怎么關(guān)聯(lián)起來的？

它們有一個(gè)共同的數(shù)據(jù)字段，叫XID。崩潰恢復(fù)的時(shí)候，會(huì)按順序掃描redo log：

• 如果碰到既有prepare、又有commit的redo log，就直接提交
• 如果碰到只有prepare、而沒有commit的redo log，就拿著XID去binlog找對(duì)應(yīng)的事務(wù)

問題四：redo log一般設(shè)置多大？

如果是現(xiàn)在常見的幾個(gè)TB的磁盤的話，redo log設(shè)置為4個(gè)文件、每個(gè)文件1GB

問題五：正常運(yùn)行中的實(shí)例，數(shù)據(jù)寫入后的最終落盤，是從redo log更新過來的還是從buffer pool更新過來的呢？

redo log并沒有記錄數(shù)據(jù)頁的完整數(shù)據(jù)，所以它并沒有能力自己去更新磁盤數(shù)據(jù)頁，也就不存在數(shù)據(jù)最終落盤是由redo log更新過去的情況

1.如果是正常運(yùn)行的實(shí)例的話，數(shù)據(jù)頁被修改以后，跟磁盤的數(shù)據(jù)頁不一致，稱為臟頁。最終數(shù)據(jù)落盤，就是把內(nèi)存中的數(shù)據(jù)頁寫盤。這個(gè)過程，甚至與redo log毫無關(guān)系

2.在崩潰恢復(fù)場景中，InnoDB如果判斷到一個(gè)數(shù)據(jù)頁可能在崩潰恢復(fù)的時(shí)候丟失了更新，就會(huì)將它對(duì)到內(nèi)存，然后讓redo log更新內(nèi)存內(nèi)容。更新完成后，內(nèi)存頁變成臟頁，就回到了第一種情況的狀態(tài)

問題六：redo log buffer是什么？是先修改內(nèi)存，還是先寫redo log文件？

在一個(gè)事務(wù)的更新過程中，日志是要寫多次的。比如下面這個(gè)事務(wù)：

begin;insert into t1 ...insert into t2 ...commit;

這個(gè)事務(wù)要往兩個(gè)表中插入記錄，插入數(shù)據(jù)的過程中，生成的日志都得先保存起來，但又不能在還沒commit的時(shí)候就直接寫到redo log文件里

所以，redo log buffer就是一塊內(nèi)存，用來先存redo日志的。也就是說，在執(zhí)行第一個(gè)insert的時(shí)候，數(shù)據(jù)的內(nèi)存被修改了，redo log buffer也寫入了日志。但是，真正把日志寫到redo log文件，是在執(zhí)行commit語句的時(shí)候做的

五、MySQL是怎么保證數(shù)據(jù)不丟的？

只要redo log和binlog保證持久化到磁盤，就能確保MySQL異常重啟后，數(shù)據(jù)可以恢復(fù)

1、binlog的寫入機(jī)制

事務(wù)執(zhí)行過程中，先把日志寫到binlog cache，事務(wù)提交的時(shí)候，再把binlog cache寫到binlog文件中。一個(gè)事務(wù)的binlog是不能被拆開的，因此不論這個(gè)事務(wù)多大，也要確保一次性寫入

系統(tǒng)給binlog cache分配了一片內(nèi)存，每個(gè)線程一個(gè)，參數(shù)binlog_cache_size用于控制單個(gè)線程內(nèi)binlog cache所占內(nèi)存的大小。如果超過了這個(gè)參數(shù)規(guī)定的大小，就要暫存到磁盤

事務(wù)提交的時(shí)候，執(zhí)行器把binlog cache里的完整事務(wù)寫入到binlog中，并清空binlog cache

每個(gè)線程有自己binlog cache，但是共用一份binlog文件

• 圖中的write，指的就是把日志寫入到文件系統(tǒng)的page cache，并沒有把數(shù)據(jù)持久化到磁盤，所以速度比較快
• 圖中的fsync，才是將數(shù)據(jù)持久化到磁盤的操作。一般情況下認(rèn)為fsync才占磁盤的IOPS

write和fsync的時(shí)機(jī)，是由參數(shù)sync_binlog控制的：

• sync_binlog=0的時(shí)候，表示每次提交事務(wù)都只write，不fsync
• sync_binlog=1的時(shí)候，表示每次提交事務(wù)都會(huì)執(zhí)行fsync
• sync_binlog=N（N>1）的時(shí)候，表示每次提交事務(wù)都write，但累積N個(gè)事務(wù)后才fsync

因此，在出現(xiàn)IO瓶頸的場景中，將sync_binlog設(shè)置成一個(gè)比較大的值，可以提升性能，對(duì)應(yīng)的風(fēng)險(xiǎn)是：如果主機(jī)發(fā)生異常重啟，會(huì)丟失最近N個(gè)事務(wù)的binlog日志

2、redo log的寫入機(jī)制

事務(wù)在執(zhí)行過程中，生成的redo log是要先寫到redo log buffer的。redo log buffer里面的內(nèi)容不是每次生成后都要直接持久化到磁盤，也有可能在事務(wù)還沒提交的時(shí)候，redo log buffer中的部分日志被持久化到磁盤

redo log可能存在三種狀態(tài)，對(duì)應(yīng)下圖的三個(gè)顏色塊

這三張狀態(tài)分別是：

• 存在redo log buffer中，物理上是在MySQL進(jìn)程內(nèi)存中，就是圖中紅色的部分
• 寫到磁盤，但是沒有持久化，物理上是在文件系統(tǒng)的page cache里面，也就是圖中黃色的部分
• 持久化到磁盤，對(duì)應(yīng)的是hard disk，也就是圖中的綠色部分

日志寫到redo log buffer和write到page cache都是很快的，但是持久化到磁盤的速度就慢多了

為了控制redo log的寫入策略，InnoDB提供了innodb_flush_log_at_trx_commit參數(shù)，它有三種可能取值：

• 設(shè)置為0的時(shí)候，表示每次事務(wù)提交時(shí)都只是把redo log留在redo log buffer中
• 設(shè)置為1的時(shí)候，表示每次事務(wù)提交時(shí)都將redo log直接持久化到磁盤
• 設(shè)置為2的時(shí)候，表示每次事務(wù)提交時(shí)都只是把redo log寫到page cache

InnoDB有一個(gè)后臺(tái)線程，每隔1秒，就會(huì)把redo log buffer中的日志，調(diào)用write寫到文件系統(tǒng)的page cache，然后調(diào)用fsync持久化到磁盤。事務(wù)執(zhí)行中間過程的redo log也是直接寫在redo log buffer中的，這些redo log也會(huì)被后臺(tái)線程一起持久化到磁盤。也就是說，一個(gè)沒有提交的事務(wù)的redo log也是可能已經(jīng)持久化到磁盤的

還有兩種場景會(huì)讓一個(gè)沒有提交的事務(wù)的redo log寫入到磁盤中

1.redo log buffer占用的空間即將達(dá)到innodb_log_buffer_size一半的時(shí)候，后臺(tái)線程會(huì)主動(dòng)寫盤。由于事務(wù)并沒有提交，所以這個(gè)寫盤動(dòng)作只是write，而沒有調(diào)用fsync，也就是只留在文件系統(tǒng)的page cache

2.并行的事務(wù)提交的時(shí)候，順帶將這個(gè)事務(wù)的redo log buffer持久化到磁盤。假設(shè)一個(gè)事務(wù)A執(zhí)行到一半，已經(jīng)寫了一些redo log到buffer中，這時(shí)候有另外一個(gè)線程的事務(wù)B提交，如果innodb_flush_log_at_trx_commit設(shè)置的是1，事務(wù)B要把redo log buffer里的日志全部持久化到磁盤。這時(shí)候，就會(huì)帶上事務(wù)A在redo log buffer里的日志一起持久化到磁盤

兩階段提交，時(shí)序上redo log先prepare，再寫binlog，最后再把redo log commit。如果把innodb_flush_log_at_trx_commit設(shè)置成1，那么redo log在prepare階段就要持久化一次

MySQL的雙1配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit都設(shè)置成1。也就是說，一個(gè)事務(wù)完整提交前，需要等待兩次刷盤，一次是redo log（prepare階段），一次是binlog

3、組提交機(jī)制

日志邏輯序列號(hào)LSN是單調(diào)遞增的，用來對(duì)應(yīng)redo log的一個(gè)個(gè)寫入點(diǎn)，每次寫入長度為length的redo log，LSN的值就會(huì)加上length。LSN也會(huì)寫到InnoDB的數(shù)據(jù)頁中，來確保數(shù)據(jù)頁不會(huì)被多次執(zhí)行重復(fù)的redo log

上圖是三個(gè)并發(fā)事務(wù)在prepare階段，都寫完redo log buffer，持久化到磁盤的過程，對(duì)應(yīng)的LSN分別是50、120和160

1.trx1是第一個(gè)到達(dá)的，會(huì)被選為這組的leader

2.等trx1要開始寫盤的時(shí)候，這個(gè)組里面已經(jīng)有了三個(gè)事務(wù)，這時(shí)候LSN也變成了160

3.trx1去寫盤的時(shí)候，帶的就是LSN=160，因此等trx1返回時(shí)，所有LSN小于等于160的redo log，都已經(jīng)被持久化到磁盤

4.這時(shí)候trx2和trx3就可以直接返回了

一個(gè)組提交里面，組員越多，節(jié)約磁盤IOPS的效果要好

為了讓一次fsync帶的組員