本文主要涉及兩點(diǎn)：

1. InnoDB 與 MyISAM 區(qū)別；
2. InnoDB 架構(gòu)分析；
3. InnoDB 特性；
   注 原文來自架構(gòu)之路公眾號

緩沖池(buffer pool)

緩存表數(shù)據(jù)與索引數(shù)據(jù)，把磁盤上的數(shù)據(jù)加載到緩沖池，避免每次訪問都進(jìn)行磁盤IO，起到加速訪問的作用。
（1）緩沖池(buffer pool)是一種常見的降低磁盤訪問的機(jī)制；
（2）緩沖池通常以頁(page)為單位緩存數(shù)據(jù)；
（3）緩沖池的常見管理算法是LRU，memcache，OS，InnoDB都使用了這種算法；
（4）InnoDB對普通LRU針對以下問題進(jìn)行了優(yōu)化：

預(yù)讀失效

由于預(yù)讀(Read-Ahead)，提前把頁放入了緩沖池，但最終MySQL并沒有從頁中讀取數(shù)據(jù)，稱為預(yù)讀失效。

• 將緩沖池分為老生代和新生代，入緩沖池的頁，優(yōu)先進(jìn)入老生代，頁被訪問，才進(jìn)入新生代，以解決預(yù)讀失效的問題

MySQL緩沖池污染

當(dāng)某一個SQL語句，要批量掃描大量數(shù)據(jù)時，可能導(dǎo)致把緩沖池的所有頁都替換出去，導(dǎo)致大量熱數(shù)據(jù)被換出，MySQL性能急劇下降，這種情況叫緩沖池污染

• 頁被訪問，且在老生代停留時間超過配置閾值的，才進(jìn)入新生代，以解決批量數(shù)據(jù)訪問，大量熱數(shù)據(jù)淘汰的問題

對應(yīng)InnoDB里哪些參數(shù)？

參數(shù)：innodb_buffer_pool_size
介紹：配置緩沖池的大小，在內(nèi)存允許的情況下，DBA往往會建議調(diào)大這個參數(shù)，越多數(shù)據(jù)和索引放到內(nèi)存里，數(shù)據(jù)庫的性能會越好。

show variables  like '%innodb_buffer_pool_size%';

參數(shù)：innodb_old_blocks_pct
介紹：老生代占整個LRU鏈長度的比例，默認(rèn)是37，即整個LRU中新生代與老生代長度比例是63:37。

• 畫外音：如果把這個參數(shù)設(shè)為100，就退化為普通LRU了。

show variables  like '%innodb_old_blocks_pct%';

參數(shù)：innodb_old_blocks_time
介紹：老生代停留時間窗口，單位是毫秒，默認(rèn)是1000，即同時滿足“被訪問”與“在老生代停留時間超過1秒”兩個條件，才會被插入到新生代頭部。

show variables  like '%innodb_old_blocks_time%';

一 MySQL-寫緩沖(change buffer)

原文

什么是InnoDB的寫緩沖

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

它是一種應(yīng)用在非唯一普通索引頁(non-unique secondary index page)不在緩沖池中，對頁進(jìn)行了寫操作，并不會立刻將磁盤頁加載到緩沖池，而僅僅記錄緩沖變更(buffer changes)，等未來數(shù)據(jù)被讀取時，再將數(shù)據(jù)合并(merge)恢復(fù)到緩沖池中的技術(shù)。寫緩沖的目的是降低寫操作的磁盤IO，提升數(shù)據(jù)庫性能。
畫外音：R了狗了，這個句子，好長。

為什么寫緩沖優(yōu)化，僅適用于非唯一普通索引頁呢？

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

除了數(shù)據(jù)頁被訪問，還有哪些場景會觸發(fā)刷寫緩沖中的數(shù)據(jù)呢？

還有這么幾種情況，會刷寫緩沖中的數(shù)據(jù)：
（1）有一個后臺線程，會認(rèn)為數(shù)據(jù)庫空閑時；
（2）數(shù)據(jù)庫緩沖池不夠用時；
（3）數(shù)據(jù)庫正常關(guān)閉時；
（4）redo log寫滿時；
畫外音：幾乎不會出現(xiàn)redo log寫滿，此時整個數(shù)據(jù)庫處于無法寫入的不可用狀態(tài)。

對應(yīng)InnoDB里哪些參數(shù)

參數(shù)：innodb_change_buffer_max_size
介紹：配置寫緩沖的大小，占整個緩沖池的比例，默認(rèn)值是25%，最大值是50%。
畫外音：寫多讀少的業(yè)務(wù)，才需要調(diào)大這個值，讀多寫少的業(yè)務(wù)，25%其實(shí)也多了。

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

二 double write buffer

MySQL的buffer一頁的大小是16K，文件系統(tǒng)一頁的大小是4K，也就是說，MySQL將buffer中一頁數(shù)據(jù)刷入磁盤，要寫4個文件系統(tǒng)里的頁。（如圖所示，MySQL里page=1的頁，物理上對應(yīng)磁盤上的1+2+3+4四個格。）

page

問題：這個操作并非原子，如果執(zhí)行到一半斷電，會不會出現(xiàn)問題呢？ 會，這就是所謂的“頁數(shù)據(jù)損壞”

power-failure

如上圖所示，MySQL內(nèi)page=1的頁準(zhǔn)備刷入磁盤，才刷了3個文件系統(tǒng)里的頁，掉電了，則會出現(xiàn)：重啟后，page=1的頁，物理上對應(yīng)磁盤上的1+2+3+4四個格，數(shù)據(jù)完整性被破壞。
畫外音：redo無法修復(fù)這類“頁數(shù)據(jù)損壞”的異常，修復(fù)的前提是“頁數(shù)據(jù)正確”并且redo日志正常。
如何解決：如何解決這類“頁數(shù)據(jù)損壞”的問題呢？
很容易想到的方法是，能有一個“副本”，對原來的頁進(jìn)行還原，這個存儲“副本”的地方，就是Double Write Buffer。
Double Write Buffer，但它與傳統(tǒng)的buffer又不同，它分為內(nèi)存和磁盤的兩層架構(gòu)。
畫外音：傳統(tǒng)的buffer，大部分是內(nèi)存存儲；而DWB里的數(shù)據(jù)，是需要落地的。

Double-Write-Buffer

如上圖所示，當(dāng)有頁數(shù)據(jù)要刷盤時：（步驟2和步驟3要寫2次磁盤，這就是“Double Write”的由來。）
第一步：頁數(shù)據(jù)先memcopy到DWB的內(nèi)存里；
第二步：DWB的內(nèi)存里，會先刷到DWB的磁盤上；
第三步：DWB的內(nèi)存里，再刷到數(shù)據(jù)磁盤存儲上；
_畫外音：_DWB由128個頁構(gòu)成，容量只有2M。

為什么能解決 DWB為什么能解決“頁數(shù)據(jù)損壞”問題呢？

1. 假設(shè)步驟2掉電，磁盤里依然是1+2+3+4的完整數(shù)據(jù)。
   畫外音：只要有頁數(shù)據(jù)完整，就能通過redo還原數(shù)據(jù)。
2. 假如步驟3掉電，DWB里存儲著完整的數(shù)據(jù)。所以，一定不會出現(xiàn)“頁數(shù)據(jù)損壞”問題。
   畫外音：寫了2次，總有一個地方的數(shù)據(jù)是OK的。

網(wǎng)上找了一個“頁數(shù)據(jù)損壞”時，MySQL重啟過程利用DWB修復(fù)頁數(shù)據(jù)的圖。

Double-Write-Buffer-2

看到，啟動過程中：

1. InnoDB檢測到上一次為異常關(guān)閉；
2. 嘗試恢復(fù)ibd數(shù)據(jù)，失??；
3. 從DWB中恢復(fù)寫了一半的頁；

能夠通過DWB保證頁數(shù)據(jù)的完整性，但畢竟DWB要寫兩次磁盤，會不會導(dǎo)致數(shù)據(jù)庫性能急劇降低呢

分析DWB執(zhí)行的三個步驟：

第一步，頁數(shù)據(jù)memcopy到DWB的內(nèi)存，速度很快；
第二步，DWB的內(nèi)存fsync刷到DWB的磁盤，屬于順序追加寫，速度也很快；
第三步，刷磁盤，隨機(jī)寫，本來就需要進(jìn)行，不屬于額外操作；
另外，128頁（每頁16K）2M的DWB，會分兩次刷入磁盤，每次最多64頁，即1M的數(shù)據(jù)，執(zhí)行也是非常之快的。

綜上，性能會有所影響，但影響并不大。

結(jié)尾(double write buffer)

MySQL有很強(qiáng)的數(shù)據(jù)安全性機(jī)制：

1. 在異常崩潰時，如果不出現(xiàn)“頁數(shù)據(jù)損壞”，能夠通過redo恢復(fù)數(shù)據(jù)；
2. 在出現(xiàn)“頁數(shù)據(jù)損壞”時，能夠通過double write buffer恢復(fù)頁數(shù)據(jù)；

double write buffer：

1. 不是一個內(nèi)存buffer，是一個內(nèi)存/磁盤兩層的結(jié)構(gòu)，是InnoDB里On-Disk架構(gòu)里很重要的一部分；
2. 是一個通過寫兩次，保證頁完整性的機(jī)制；

三 自適應(yīng)哈希索引(Adaptive Hash Index, AHI)

對于InnoDB的哈希索引，確切的應(yīng)該這么說：

1. InnoDB用戶無法手動創(chuàng)建哈希索引，這一層上說，InnoDB確實(shí)不支持哈希索引；
2. InnoDB會自調(diào)優(yōu)(self-tuning)，如果判定建立自適應(yīng)哈希索引(Adaptive Hash Index, AHI)，能夠提升查詢效率，InnoDB自己會建立相關(guān)哈希索引，這一層上說，InnoDB又是支持哈希索引的；

自適應(yīng)哈希索引

無論是主鍵索引查詢還是普通索引查詢在MySQL運(yùn)行的過程中，如果InnoDB發(fā)現(xiàn)，有很多SQL存在這類很長的尋路，并且有很多SQL會命中相同的頁面(page)，InnoDB會在自己的內(nèi)存緩沖區(qū)(Buffer)里，開辟一塊區(qū)域，建立自適應(yīng)哈希所有AHI，以加速查詢。

為啥叫“自適應(yīng)****(adaptive)****”哈希索引？
系統(tǒng)自己判斷“應(yīng)該可以加速查詢”而建立的，不需要用戶手動建立，故稱“自適應(yīng)”。

系統(tǒng)會不會判斷失誤，是不是一定能加速？
不是一定能加速，有時候會誤判。
當(dāng)業(yè)務(wù)場景為下面幾種情況時：

• 很多單行記錄查詢（例如passport，用戶中心等業(yè)務(wù)）
• 索引范圍查詢（此時AHI可以快速定位首行記錄）
• 所有記錄內(nèi)存能放得下
  AHI往往是有效的。 畫外音：任何脫離業(yè)務(wù)的技術(shù)方案，都是耍流氓。

四 預(yù)讀

待補(bǔ)充