InnoDB關(guān)鍵特性包括：

• 插入緩沖（Inster Buffer）
• 兩次寫（Double Write）
• 自適應(yīng)哈希索引（Adaptive Hash Index）
• 異步IO（Async IO）
• 刷新鄰接頁（Flush Neighbor Page）

一、插入緩沖

• 下面介紹的插入緩沖有：
  • Insert Buffer
  • Change Buffer

聚集索引與輔助(非聚集/二級)索引

• 在介紹Insert Buffer之前，先介紹一些聚集索引與輔助
• 在InnoDB中，主鍵是行唯一的標識符。通常應(yīng)用程序中行記錄的插入順序是按照主鍵遞增的順序進行插入的。因此，插入聚集索引（Primary Key）一般是順序的，不需要磁盤的隨機讀取。比如按下列SQL定義表：

create table t(a int auto_increment,b varchar(30),primary key(a));

• 其中a列是自增長的，若對a列插入NULL值，則由于其具有auto_increment屬性，其值會自動增長。同時頁中的行記錄按a的值進行順序存放。在一般情況下，不需要隨機讀取另一個頁中的記錄。因此，對于這類情況下的插入操作，速度時非常快的

• 但是不可能每張表上只有一個聚集索引，更多情況下，一張表上有多個非聚集的輔助索引（secondary index）。比如，用戶需要按照b這個字段進行查找，并且b這個字段不是唯一的，即表按照下面的SQL語句進行定義：

create table t(a int auto_increment,b varchar(30),primary key(a),key(b));

• 在這種情況下產(chǎn)生了一個非聚集的且不是唯一的索引。在進行插入操作時，數(shù)據(jù)頁的存放還是按主鍵a進行順序存放的，但是對于非聚集索引葉子節(jié)點的插入不再是順序的了，這時就需要離散地訪問非聚集索引頁，由于隨機讀取的存在而導(dǎo)致了插入操作性能下降。當然這并不是這個b字段上索引的錯誤，而是因為B+樹的特性決定了非聚集索引插入的離散性
• 需要注意的是，在某些情況下，輔助索引的插入依然是順序的，或者說是比較順序的，比如用戶購買表中的時間字段。在通常情況下，用戶購買時間是一個輔助索引，用來根據(jù)時間條件進行查詢。但是在插入時卻是根據(jù)時間的遞增而插入的，因此插入也是“較為”順序的

①Insert Buffer

• InnoDB存儲引擎開創(chuàng)性地設(shè)計了Insert Buffer
• 工作原理：于非聚集索引的插入或更新操作，不是每一次直接插入到索引頁中，而是先判斷插入的非聚集索引頁是否在緩沖池中。若在，則直接插入；若不在，則先放入到一個Insert Buffer對象中
• 使用者認為這個非聚集的索引已經(jīng)插到葉子節(jié)點，而實際并沒有，只是存放到另一個位置。然后再以一定的頻率和情況進行Insert Buffer和輔助索引頁子節(jié)點的merge（合并）操作，這時通常能將多個插入合并到一個操作中（因為在一個索引頁中），這就大大提高了對于非聚集索引插入的性能
• Insert Buffer的使用需要同時滿足以下兩個條件：
  • 索引是輔助索引（secondary index）
  • 索引不是唯一的
• 一個缺陷：應(yīng)用程序進行大量的插入操作，這些都涉及了不唯一的非聚集索引，也就是使用了Insert Buffer。若此時MySQL數(shù)據(jù)庫發(fā)生了宕機，這時勢必有大量的Insert Buffer并沒有合并到實際的非聚集索引中去。因此這時恢復(fù)可能需要很長的時間，在極端情況下甚至需要幾個小時

• 輔助索引不能使唯一的，因為在插入緩沖時，數(shù)據(jù)庫并不去查找索引頁來判斷插入的記錄的唯一性。如果去查找肯定又會有離散讀取的情況發(fā)生，從而導(dǎo)致Insert Buffer失去了意義

• 用戶可以在下面命令的輸出信息中查看插入緩沖的信息：
  • seg size：顯示當前Insert Buffer的大小為2*16KB
  • free list len：代表了空閑列表的長度
  • size：代表了已經(jīng)合并記錄頁的數(shù)量

show engine innodb status\G;

• Insert Buffer的另一個問題：
  • 在寫密集的情況下，插入緩沖會占用過多的緩沖池內(nèi)存（inoodb_buffer_pool），默認最大可以占用到1/2的緩沖池內(nèi)存
  • 下圖是InnoDB存儲引擎源代碼中對于Insert Buffer的初始化操作：
  • 這對于其他的操作可能會帶來一定的影響。Percona上發(fā)布一些補丁來修正插入緩沖占用太多緩沖池內(nèi)存的情況。具體可以到Percona官網(wǎng)進行查找。簡單地說，修改IBUF_POOL_SIZE_PER_MAX_SIZE就可以對插入緩沖的大小進行控制。比如將IBUF_POOL_SIZE_PER_MAX_SIZE改為3，則最大只能使用1/3的緩沖池內(nèi)存

②Change Buffer

• InnoDB從1.0.x版本開始引入了Change Buffer，可將其視為Insert Buffer的升級。從這個版本開始，InnoDB可以對DML操作——insert、delete、update都進行緩沖，它們分別是：Insert Buffer、Delete Buffer、Purge Buffer
• 和之前的Insert Buffer一樣，Change Buffer適用的對象依然是非唯一的輔助索引
• 對一條記錄進行update操作可能分為以下兩個過程：
  • 將記錄標記為已刪除
  • 真正將記錄刪除
• 因此Delete Buffer對應(yīng)update操作的第一個過程，即將記錄標記為刪除。Purge Buffer對應(yīng)update操作的第二個過程，即將記錄真正的刪除
• innodb_change_buffering參數(shù)：
  • 用來開啟各種Buffer的選項，默認值為all
  • 可選的值有：inserts、deletes、purges、changes、all、none
    • inserts、deletes、purges就是前面討論過的三種情況
    • changes表示啟用inserts和deletes
    • all表示啟用所有
    • none表示都啟用

show variables like 'innodb_change_buffering'\G;

• innodb_change_buffer_max_size參數(shù)：通過該參數(shù)來控制change buffer最大使用內(nèi)存的數(shù)量
• 查看Change Buffer的信息：
  • merged operations、discarded operation：表示合并與清理操作
  • 下面顯示Change Buffer中每個操作的次數(shù)：insert表示Insert Buffer；delete mark表示Delete Buffer；delete表示Purge Buffer
  • discarded operation：表示當Change Buffer發(fā)生merge時，表已經(jīng)被刪除，此時就無需再將記錄合并到輔助索引中了

show engine innodb status\G;

③Insert/Change?Buffer的內(nèi)部實現(xiàn)

• Insert Buffer的數(shù)據(jù)結(jié)構(gòu)是一棵B+樹
  • 在MySQL 4.1之前每張表有一顆Insert Buffer B+樹
  • 而現(xiàn)在的版本中，全局只有一顆Insert Buffer B+樹，負責(zé)對所有的表的輔助索引進行Insert Buffer
• 這棵B+樹存放在共享表空間中，默認也就是ibdata1中。因此，視圖通過獨立表空間ibd文件恢復(fù)表中數(shù)據(jù)時，往往會導(dǎo)致CHECK TABLE失敗。這是因為表的輔助索引中的數(shù)據(jù)可能還在Insert Buffer中，也就是共享表空間中，所以通過ibd文件進行恢復(fù)后，還需要進行REPAIR TABLE操作來重建表上所有的輔助索引
• Insert Buffer是一棵B+樹，非葉子節(jié)點存放的是查詢的search key（鍵值），構(gòu)造如下：
  • space：表示待插入記錄所在表的表空間id（在InnoDB中，每個表有唯一的space id，可以通過該id查詢得知是哪張表）。占用4字節(jié)
  • marker：用來兼容老版本的Insert Buffer。占用1字節(jié)
  • offset：表示頁所在的偏移量。占用4字節(jié)

• 當一個輔助索引要插入到頁（space,offset）時，如果這個頁不在緩沖池中，那么InnoDB首先根據(jù)上述規(guī)則構(gòu)造一個search key，接下來查詢Insert Buffer這棵B+樹，然后再將這條記錄插入到Insert Buffer B+樹的葉子節(jié)點中。對于插入到Insert Buffer B+樹葉子節(jié)點的記錄（如下圖所示），并不是直接將待插入的記錄插入，而是需要根據(jù)如下的規(guī)則進行構(gòu)造：
  • space、marker、page_no字段和之前非葉子節(jié)點的含義相同，一共占用9個字節(jié)
  • metadata字段占用4字節(jié)，其存儲內(nèi)容如下下圖所示
    • IBUF_REC_OFFSET_COUNT：是保存兩個字節(jié)的整數(shù)，用來排序每個記錄進入Insert Buffer的順序。因為從InnoDB 1.0.x開始支持Change Buffer，所以這個值同樣記錄進入Insert Buffer的順序。通過這個順序回放（replay）才能得到記錄的正確值
• 從Insert Buffer葉子節(jié)點的第5列開始，就是實際插入記錄的各個字段了。因此較之原插入記錄，Insert Buffer B+樹的葉子節(jié)點記錄需要額外13字節(jié)的開銷

• Insert Buffer Bitmap頁
  • 因為啟動Insert Buffer索引后，輔助索引頁（space，page_no）中的記錄可能被插入到Insert Buffer B+樹中，所以為了保證每次Merge?Insert Buffer頁必須成功，還需要有一個特殊的頁來標記每個輔助索引頁（space，page_no）的可用空間。這個頁的類型為Insert Buffer Bitmap
  • 每個Insert Buffer Bitmap頁用來追蹤16384個輔助索引頁，也就是256個區(qū)（Extent）。每個Insert Buffer Bitmap頁都在16384個頁的第二個頁中。關(guān)于Insert Buffer Bitmap頁的作用會在后面介紹
  • 每個輔助索引頁在Insert Buffer Bitmap頁中占用4位（bit），由下表中的三個部分組成

④插入緩沖何時進行合并？

• 通過上面我們知道Insert/Change Buffer是一棵B+樹。若需要實現(xiàn)插入記錄的輔助索引頁不在緩沖池中，那么需要將輔助索引記錄首先插入到這棵B+樹中。但是Insert Buffer中的記錄何時合并（merge）到真正的輔助索引中呢？這是下面要關(guān)注的重點
• Merge?Insert Buffer的操作可能發(fā)生在以下幾種情況：
  • ①輔助索引頁被讀到緩沖池時
    • 當輔助索引頁被讀取到緩沖池中時，例如這在執(zhí)行正常的select查詢操作，這時需要檢查Insert Buffer Bitmap頁，然后確認該輔助索引頁是否有記錄存放于Insert Buffer B+樹中。若有，則將Insert Buffer B+樹該頁的記錄插入到該輔助索引頁中。可以看到對該頁多次的記錄操作通過一次操作合并到了原有的輔助索引頁中，因此性能會有大幅提高
  • ②Insert Buffer Bitmap頁追蹤到該輔助索引頁已無可用空間時
    • Insert Buffer Bitmap頁用來追蹤每個輔助索引頁的可用空間，并至少有1/32頁的空間。若插入輔助索引記錄時檢測到插入記錄后可用空間會小于1/32頁，則會強制進行一個合并操作，即強制讀取輔助索引頁，將Insert Buffer B+樹中該頁的記錄及待插入的記錄插入到輔助索引頁中
  • ③Mastert Thread
    • 在前面介紹過，Master Thread線程中每秒或每10秒會進行一次Merge Insert Buffer的操作，不同之處在于每次進行merge操作的頁的數(shù)量不同
    • 在Master Thread中，執(zhí)行merge操作的不止是一個頁，而是根據(jù)srv_innodb_io_capacity的百分比來決定真正要合并多少個輔助索引頁。但InnoDB又是根據(jù)怎么樣的算法來得知需要合并的輔助索引頁呢？
• 在Insert Buffer B+樹中，輔助索引頁根據(jù)（space，offset）都已排序好，故可以根據(jù)（space，offset）的排列順序進行頁的選擇。然而，對Insert Buffer頁的選擇，InnoDB并非采用這個方式，它隨機地選擇Insert Buffer B+樹的一個頁，讀取該頁中的space及之后所需要數(shù)量的頁。該算法在復(fù)雜情況下應(yīng)有更好的公平性。同時，若進行merge時，要進行merge的表已經(jīng)被刪除，此時可以直接丟棄已經(jīng)被Insert/Change Buffer的數(shù)據(jù)記錄

二、兩次寫（doublewrite）

• 上面介紹的Insert/Change Buffer帶給InnoDB的是性能上的提升，那么兩次寫（doublewrite）帶給InnoDB的是數(shù)據(jù)頁的可靠性

設(shè)計doublewrite的初衷

• 當數(shù)據(jù)庫宕機時，可能InnoDB正在寫入某個頁到表中，而這個頁只寫了一部分，比如16KB的頁，只寫了前4KB，之后就發(fā)生了宕機，這種情況被稱為“部分寫失效”。在InnoDB未使用doublewrite技術(shù)前，曾經(jīng)出現(xiàn)過因為部分寫失效而導(dǎo)致數(shù)據(jù)丟失的情況
• 在doublewrite被設(shè)計出之前，如果發(fā)生寫失效，可以通過重做日志進行恢復(fù)。這是一個辦法，但是必須知道，重做日志中記錄的是對頁的物理操作，如偏移量800，寫'aaaa'記錄。如果這個頁本身已經(jīng)發(fā)生了損壞，再次對其進行重做是沒有意義的

• 什么是doublewrite：在應(yīng)用重做日志前，用戶需要一個頁的副本，當寫入失效發(fā)生時，先通過頁的副本來還原該頁，再進行重做，這就是doublewrite

• doublewrite由兩個部分組成：

  • 一部分是內(nèi)存中的doublewrite buffer，大小為2MB

  • 另一部分是物理磁盤上共享表空間中連續(xù)的128頁，即2個區(qū)（extent），大小同樣為2MB

doublewrite工作原理：

• 工作原理如下：
  • 在對緩沖池的臟頁進行刷新時，并不直接寫磁盤，而是會通過memcpy函數(shù)將臟頁先復(fù)制到內(nèi)存中的doublewrite buffer
  • 之后通過doublewrite buffer再分兩次，每次1MB順序地寫入共享空間的物理磁盤上
  • 然后馬上調(diào)用fsync函數(shù)，同步磁盤，避免緩沖寫帶來的問題
• 在這個過程中，因為doublewrite頁是連續(xù)的，因此這個過程是順序?qū)懙?/strong>，開銷并不大。在完成doublewrite頁的寫入后，再將doublewrite buffer中的頁寫入各個表空間文件中，此時的寫入則是離散的
• 如果操作系統(tǒng)將頁寫入磁盤的過程中發(fā)生了崩潰，在恢復(fù)過程中，InnoDB存儲引擎可以從共享表空間中的doublewrite中找到該頁的一個副本，將其復(fù)制到表空間文件，再應(yīng)用重做日志
• 下面顯示了一個由doublewrite進行恢復(fù)的情況：

• 可以通過以下命令觀察到doublewrite運行的情況：
  • Innodb_dblwr_pages_written：是doublewrite一共寫了多少頁
  • Innodb_dblwr_writes：是實際的寫入次數(shù)

show global status like 'innodb_dblwr%'\G;

MySQL 5.5.24版本之前的一個bug

• “Innodb_buffer_pool_pages_flushed”變量表示當前從緩沖池刷新到磁盤頁的數(shù)量

show global status like 'Innodb_buffer_pool_pages_flushed'\G;

• 根據(jù)之前的介紹，用戶應(yīng)該了解到，在默認情況下所以頁的刷新首先都需要放入到doublewrite中，因此該變量應(yīng)該和“Innodb_dblwr_pages_written”一致。然而在MySQL 5.5.24版本之前，Innodb_buffer_pool_pages_flushed總是Innodb_dblwr_pages_written的2倍，此bug直到MySQL 5.5.24才修復(fù)
• 因此用戶若需要統(tǒng)計數(shù)據(jù)庫在生產(chǎn)環(huán)境中寫入的量，最安全的方法還是根據(jù)Innodb_dblwr_pages_written來進行統(tǒng)計

是否開啟doublewrite功能（skip_innodb_doublewrite）

• skip_innodb_doublewrite參數(shù)可以開啟/禁止使用doublewrite功能，如果禁止之后可能會發(fā)生前面提到過的寫失效問題
• 如果用戶有多個從服務(wù)器，需要提供較快的性能，禁止該參數(shù)也是一個辦法。不過對于需要提供數(shù)據(jù)高可靠性的主服務(wù)器，任何時候用戶都應(yīng)該確保開啟doublewrite功能

三、自適應(yīng)哈希索引（AHI）

• 哈希與B+樹的復(fù)雜度對比：
  • 哈希（hash）是一種非常快的查找方法，在一般情況下這種查找的時間復(fù)雜度為O(1)
  • B+樹的查找次數(shù)，取決于B+樹的高度，在生產(chǎn)環(huán)境中，B+樹的高度一般為3~4次，故需要3~4次的查詢

AHI簡介

• InnoDB會監(jiān)控對表上各索引頁的查詢。如果觀察到建立哈希索引可以帶來速度提升，則建立哈希索引，稱之為自適應(yīng)哈希索引（AHI）
• AHI是通過緩沖池的B+樹頁構(gòu)造而來，因此建立的速度很快，而且不需要對整張表構(gòu)建哈希索引
• InnoDB會自動根據(jù)訪問的頻率和模式來自動地為某些熱點頁建立哈希索引

AHI的使用要求

• AHI有一個要求，即對這個頁的連續(xù)訪問模式必須是一樣的。例如對于(a,b)這樣的聯(lián)合索引頁，其訪問模式可以是以下情況：
  • where a=XXX
  • where a=XXX?and b=XXX
• 訪問模式一樣指的是查詢的條件一樣，若交替進行上述兩種查詢，那么InnoDB存儲引擎不會對該頁構(gòu)造AHI
• 此外AHI還有如下的要求：
  • 以該模式訪問了100次
  • 頁通過該模式訪問了N次，其中N=頁中記錄*1/16

• 根據(jù)InnoDB存儲引擎官方的文檔顯示，啟用AHI后，讀取和寫入速度可以提高2倍，輔助索引的連接操作性能可以提高5倍。毫無疑問，AHI是非常好的優(yōu)化模式，其設(shè)計設(shè)計思想是數(shù)據(jù)庫自優(yōu)化，即無需DBA對數(shù)據(jù)庫進行人為調(diào)整
• 通過下面的命令可以看到當前AHI的使用狀況：
  • 下圖顯示了AHI的大小、使用情況、每秒使用AHI搜索的情況
  • 需要注意的是，哈希索引只能用來搜索等值的查詢，如select * from table where index_col='xxx';。而對于其他查找類型，如范圍查找，是不能使用哈希索引的，因此這里出現(xiàn)了non-hash searches/s的情況
  • 通過hash searches:non-hash searches可以大概了解使用哈希索引后的效率

show engine innodb status\G;

• innodb_adaptive_hash_index參數(shù)：該參數(shù)可以用來控制AHI的開啟/關(guān)閉，默認為開啟狀態(tài)

show variables like 'innodb_adaptive_hash_index'\G;

四、異步IO（AIO）

• 為了提高磁盤操作性能，當前的數(shù)據(jù)庫都采用異步IO（Asynchronous IO，AIO）的方式來處理磁盤操作。InnoDB也是如此

什么是異步IO

• 先與同步IO做個比較：同步IO是指每進行一次IO操作，需要等待此次操作結(jié)束才能繼續(xù)接下來的IO操作
• 但是如果用戶發(fā)出的是一條索引掃描的查詢，那么這條SQL查詢語句可能需要掃描多個索引頁，也就是需要進行多次的IO操作。在每掃描一個頁并等待其完成后再進行下一次的掃描，這是沒有必要的。用戶可以在發(fā)出一個IO請求后立即再發(fā)出另一個IO請求，當全部IO請求發(fā)送完畢后，等待所有IO操作的完成，這就是AIO

演示說明

• AIO的另一個優(yōu)勢是可以進行IO Merge操作，也就是將多個IO合并為1個IO，這樣可以提高IOPS的性能。例如用戶需要訪問頁的(space，page_no)為：
  • 其中每個頁的大小為16KB，那么同步IO需要進行3次IO操作。而AIO會判斷這三個頁是連續(xù)的（顯然可以通過(space，page_no)得知），因此AIO底層會發(fā)送一個IO請求，從(8，6)開始，讀取48KB的頁

• 例如，在Linux操作系統(tǒng)下通過iostat命令，可以觀察rrqm/s和wrqm/s，例如：

MySQL中AIO的發(fā)展例程

• 在InnoDB 1.1.x之前，AIO的實現(xiàn)通過InnoDB存儲引擎中的代碼來模擬實現(xiàn)
• 而從InnoDB 1.1.x開始（InnoDB Plugin不支持），提高了內(nèi)核級別AIO的支持，稱為Native AIO。因此在編譯或運行該版本MySQL時，需要libaio的支持。若沒有則會出現(xiàn)如下的提示：

• 需要注意的是，Native AIO需要操作系統(tǒng)提供支持：
  • Windows系統(tǒng)和Linux系統(tǒng)都提供Native AIO
  • 而Mac OSX系統(tǒng)則未提供，因此在這些系統(tǒng)上，依舊只能使用原模擬的方式
  • 在選擇MySQL數(shù)據(jù)庫服務(wù)器的操作系統(tǒng)時，需要考慮這方面的因素

• innodb_use_native_aio參數(shù)：該參數(shù)用來控制是否啟用Native AIO，在Linux操作系統(tǒng)上，默認和為ON
  • MySQL官方的測試顯示，啟用Native AIO，恢復(fù)速度可以提高75%

show variables like 'innodb_use_native_aio'\G;

• 在InnoDB存儲引擎中，read ahead方式的讀取都是通過AIO完成，臟頁的刷新，即磁盤的寫入操作則全部由AIO完成

五、刷新鄰接頁

• InnoDB還提供了Flush Neighbor Page（刷新鄰接頁）的特性
• 工作原理：當刷新一個臟頁時，InnoDB存儲引擎會檢測該頁所在區(qū)（extent）的所有頁，如果是臟頁，那么一起進行刷新
• 這樣做的好處顯而易見，通過AIO可以將多個IO寫入操作合并為一個IO操作，故該工作機制在傳統(tǒng)機械磁盤下有著顯著的優(yōu)勢
• 但是需要考慮下面兩個問題：
  • 是不是可能將不怎么臟的頁進行了寫入，而該頁之后又會很快變?yōu)榕K頁
  • 固態(tài)硬盤有著較高的IOPS，是否還需要這個特性？
• 為此，InnoDB存儲引擎從1.2.x版本開始提供了參數(shù)innodb_flush_neighbors，用來控制是否啟動該特性。對于傳統(tǒng)機械硬盤建議啟動該特性，而對于固態(tài)硬盤有著超高的IOPS性能的磁盤，則建議將該參數(shù)設(shè)置為0

總結(jié)

以上是生活随笔為你收集整理的MySQL(InnoDB剖析):---InnoDB关键特性（插入缓冲(Insert Buffer)、两次写(doublewrite)、自适应哈希索引(AHI)、异步IO(AIO)、刷新邻接页）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。