上篇《緩沖池(buffer pool)，徹底懂了!》介紹了InnoDB緩沖池的工作原理。

簡(jiǎn)單回顧一下：

• MySQL數(shù)據(jù)存儲(chǔ)包含內(nèi)存與磁盤(pán)兩個(gè)部分;
• 內(nèi)存緩沖池(buffer pool)以頁(yè)為單位，緩存最熱的數(shù)據(jù)頁(yè)(data page)與索引頁(yè)(index page);
• InnoDB以變種LRU算法管理緩沖池，并能夠解決“預(yù)讀失效”與“緩沖池污染”的問(wèn)題;

畫(huà)外音：細(xì)節(jié)詳見(jiàn)《緩沖池(buffer pool)，徹底懂了!》。

毫無(wú)疑問(wèn)，對(duì)于讀請(qǐng)求，緩沖池能夠減少磁盤(pán)IO，提升性能。問(wèn)題來(lái)了，那寫(xiě)請(qǐng)求呢?

情況一

假如要修改頁(yè)號(hào)為4的索引頁(yè)，而這個(gè)頁(yè)正好在緩沖池內(nèi)。

如上圖序號(hào)1-2：

• 直接修改緩沖池中的頁(yè)，一次內(nèi)存操作;
• 寫(xiě)入redo log，一次磁盤(pán)順序?qū)懖僮?

這樣的效率是***的。

畫(huà)外音：像寫(xiě)日志這種順序?qū)?，每秒幾萬(wàn)次沒(méi)問(wèn)題。

是否會(huì)出現(xiàn)一致性問(wèn)題呢?

并不會(huì)。

• 讀取，會(huì)***緩沖池的頁(yè);
• 緩沖池LRU數(shù)據(jù)淘汰，會(huì)將“臟頁(yè)”刷回磁盤(pán);
• 數(shù)據(jù)庫(kù)異常奔潰，能夠從redo log中恢復(fù)數(shù)據(jù);

什么時(shí)候緩沖池中的頁(yè)，會(huì)刷到磁盤(pán)上呢?

定期刷磁盤(pán)，而不是每次刷磁盤(pán)，能夠降低磁盤(pán)IO，提升MySQL的性能。

畫(huà)外音：批量寫(xiě)，是常見(jiàn)的優(yōu)化手段。

情況二

假如要修改頁(yè)號(hào)為40的索引頁(yè)，而這個(gè)頁(yè)正好不在緩沖池內(nèi)。

此時(shí)麻煩一點(diǎn)，如上圖需要1-3：

• 先把需要為40的索引頁(yè)，從磁盤(pán)加載到緩沖池，一次磁盤(pán)隨機(jī)讀操作;
• 修改緩沖池中的頁(yè)，一次內(nèi)存操作;
• 寫(xiě)入redo log，一次磁盤(pán)順序?qū)懖僮?

沒(méi)有***緩沖池的時(shí)候，至少產(chǎn)生一次磁盤(pán)IO，對(duì)于寫(xiě)多讀少的業(yè)務(wù)場(chǎng)景，是否還有優(yōu)化的空間呢?

這即是InnoDB考慮的問(wèn)題，又是本文將要討論的寫(xiě)緩沖(change buffer)。

畫(huà)外音：從名字容易看出，寫(xiě)緩沖是降低磁盤(pán)IO，提升數(shù)據(jù)庫(kù)寫(xiě)性能的一種機(jī)制。

什么是InnoDB的寫(xiě)緩沖?

在MySQL5.5之前，叫插入緩沖(insert buffer)，只針對(duì)insert做了優(yōu)化;現(xiàn)在對(duì)delete和update也有效，叫做寫(xiě)緩沖(change buffer)。

它是一種應(yīng)用在非唯一普通索引頁(yè)(non-unique secondary index page)不在緩沖池中，對(duì)頁(yè)進(jìn)行了寫(xiě)操作，并不會(huì)立刻將磁盤(pán)頁(yè)加載到緩沖池，而僅僅記錄緩沖變更(buffer changes)，等未來(lái)數(shù)據(jù)被讀取時(shí)，再將數(shù)據(jù)合并(merge)恢復(fù)到緩沖池中的技術(shù)。寫(xiě)緩沖的目的是降低寫(xiě)操作的磁盤(pán)IO，提升數(shù)據(jù)庫(kù)性能。

畫(huà)外音：R了狗了，這個(gè)句子，好長(zhǎng)。

InnoDB加入寫(xiě)緩沖優(yōu)化，上文“情況二”流程會(huì)有什么變化?

假如要修改頁(yè)號(hào)為40的索引頁(yè)，而這個(gè)頁(yè)正好不在緩沖池內(nèi)。

加入寫(xiě)緩沖優(yōu)化后，流程優(yōu)化為：

• 在寫(xiě)緩沖中記錄這個(gè)操作，一次內(nèi)存操作;
• 寫(xiě)入redo log，一次磁盤(pán)順序?qū)懖僮?

其性能與，這個(gè)索引頁(yè)在緩沖池中，相近。

畫(huà)外音：可以看到，40這一頁(yè)，并沒(méi)有加載到緩沖池中。

是否會(huì)出現(xiàn)一致性問(wèn)題呢?

也不會(huì)。

• 數(shù)據(jù)庫(kù)異常奔潰，能夠從redo log中恢復(fù)數(shù)據(jù);
• 寫(xiě)緩沖不只是一個(gè)內(nèi)存結(jié)構(gòu)，它也會(huì)被定期刷盤(pán)到寫(xiě)緩沖系統(tǒng)表空間;
• 數(shù)據(jù)讀取時(shí)，有另外的流程，將數(shù)據(jù)合并到緩沖池;

不妨設(shè)，稍后的一個(gè)時(shí)間，有請(qǐng)求查詢索引頁(yè)40的數(shù)據(jù)。

此時(shí)的流程如序號(hào)1-3：

• 載入索引頁(yè)，緩沖池未***，這次磁盤(pán)IO不可避免;
• 從寫(xiě)緩沖讀取相關(guān)信息;
• 恢復(fù)索引頁(yè)，放到緩沖池LRU里;

畫(huà)外音：可以看到，40這一頁(yè)，在真正被讀取時(shí)，才會(huì)被加載到緩沖池中。

還有一個(gè)遺漏問(wèn)題，為什么寫(xiě)緩沖優(yōu)化，僅適用于非唯一普通索引頁(yè)呢?

InnoDB里，聚集索引(clustered index)和普通索引(secondary index)的異同，《1分鐘了解MyISAM與InnoDB的索引差異》有詳盡的敘述，不再展開(kāi)。

如果索引設(shè)置了唯一(unique)屬性，在進(jìn)行修改操作時(shí)，InnoDB必須進(jìn)行唯一性檢查。也就是說(shuō)，索引頁(yè)即使不在緩沖池，磁盤(pán)上的頁(yè)讀取無(wú)法避免(否則怎么校驗(yàn)是否唯一?)，此時(shí)就應(yīng)該直接把相應(yīng)的頁(yè)放入緩沖池再進(jìn)行修改，而不應(yīng)該再整寫(xiě)緩沖這個(gè)幺蛾子。

除了數(shù)據(jù)頁(yè)被訪問(wèn)，還有哪些場(chǎng)景會(huì)觸發(fā)刷寫(xiě)緩沖中的數(shù)據(jù)呢?

還有這么幾種情況，會(huì)刷寫(xiě)緩沖中的數(shù)據(jù)：

• 有一個(gè)后臺(tái)線程，會(huì)認(rèn)為數(shù)據(jù)庫(kù)空閑時(shí);
• 數(shù)據(jù)庫(kù)緩沖池不夠用時(shí);
• 數(shù)據(jù)庫(kù)正常關(guān)閉時(shí);
• redo log寫(xiě)滿時(shí);

畫(huà)外音：幾乎不會(huì)出現(xiàn)redo log寫(xiě)滿，此時(shí)整個(gè)數(shù)據(jù)庫(kù)處于無(wú)法寫(xiě)入的不可用狀態(tài)。

什么業(yè)務(wù)場(chǎng)景，適合開(kāi)啟InnoDB的寫(xiě)緩沖機(jī)制?

先說(shuō)什么時(shí)候不適合，如上文分析，當(dāng)：

• 數(shù)據(jù)庫(kù)都是唯一索引;
• 或者，寫(xiě)入一個(gè)數(shù)據(jù)后，會(huì)立刻讀取它;

這兩類(lèi)場(chǎng)景，在寫(xiě)操作進(jìn)行時(shí)(進(jìn)行后)，本來(lái)就要進(jìn)行進(jìn)行頁(yè)讀取，本來(lái)相應(yīng)頁(yè)面就要入緩沖池，此時(shí)寫(xiě)緩存反倒成了負(fù)擔(dān)，增加了復(fù)雜度。

什么時(shí)候適合使用寫(xiě)緩沖，如果：

• 數(shù)據(jù)庫(kù)大部分是非唯一索引;
• 業(yè)務(wù)是寫(xiě)多讀少，或者不是寫(xiě)后立刻讀取;

可以使用寫(xiě)緩沖，將原本每次寫(xiě)入都需要進(jìn)行磁盤(pán)IO的SQL，優(yōu)化定期批量寫(xiě)磁盤(pán)。

畫(huà)外音：例如，賬單流水業(yè)務(wù)。

上述原理，對(duì)應(yīng)InnoDB里哪些參數(shù)?

有兩個(gè)比較重要的參數(shù)。

• 參數(shù)：innodb_change_buffer_max_size
• 介紹：配置寫(xiě)緩沖的大小，占整個(gè)緩沖池的比例，默認(rèn)值是25%，***值是50%。

畫(huà)外音：寫(xiě)多讀少的業(yè)務(wù)，才需要調(diào)大這個(gè)值，讀多寫(xiě)少的業(yè)務(wù)，25%其實(shí)也多了。

• 參數(shù)：innodb_change_buffering
• 介紹：配置哪些寫(xiě)操作啟用寫(xiě)緩沖，可以設(shè)置成all/none/inserts/deletes等。

希望大家有收獲，思路比結(jié)論重要。

【本文為51CTO專(zhuān)欄作者“58沈劍”原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)聯(lián)系原作者】

戳這里，看該作者更多好文