一、插入緩沖（Insert Buffer）

1.1 Insert Buffer

Insert Buffer 和資料頁一樣，是實體頁的一個組成部分。

通常在使用InnoDB的過程中，主鍵是表中一行資料的唯一辨別，通常是按照主鍵遞增的順序插入的。是以，插入主鍵或者說聚集索引是順序的，不需要磁盤的随機讀取。頁中的記錄會随着主鍵的順序逐個記錄，這時候的速度還是很快的。

注意：如果插入的是随機資料，比如UUID，那麼無論主鍵是否自增，都将導緻資料的寫入是随機的，并非連續的。

通常情況每張表會有除聚集索引以外的其他輔助索引，這時聚集索引的存放仍然是順序的，但是在輔助索引的葉子節點存儲的資料則是離散的，由于随機讀取的操作而導緻了寫入性能的下降。

在某些特定情況下，非聚集索引的插入仍然可以保證是順序的，例如訂單表的下單時間字段。

為了提高非聚集索引的寫入性能，InnoDB開創性的設計了Insert Buffer：對于非聚集索引的插入和更新操作，會先去查詢緩沖池，看非聚集索引的頁是否在緩沖池中，若存在，則直接寫入；若不存在，則先存放到一個Insert Buffer對象中，然後以一定的頻率使Inser Buffer和輔助索引葉子結點merge（合并）。這是通常是多個寫入操作合并到一個操作中（在一個索引頁中），這樣就大大提高了非聚集索引插入的性能。

使用條件：

索引是輔助索引
索引不是唯一的，插入時，資料庫不判斷資料的唯一性。

1.2 Change Buffer

Insert Buffer的更新，對于Insert，Update，Delete都進行緩沖，他們分别是：Insert Buffer、Delete Buffer、Purge Buffer。

仍然作用于非唯一的輔助索引。

對資料Update分為兩個步驟：

1）将資料标為已删除。對應Delete Buffer。

2）執行删除。對應Purge Buffer。

通過以下指令控制:

sql複制代碼sql
複制代碼
mysql> show variables like 'innodb_change_buffering';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_change_buffering | all   |
+-------------------------+-------+
1 row in set (0.00 sec)

預設是all，表示啟動所有。此外還有inserts，deletes，purges，changes，none；changes表示啟用inserts和deletes；none表示都不啟用。

通過以下參數控制最大使用記憶體量：

sql複制代碼sql
複制代碼
mysql> show variables like 'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set (0.00 sec)

表示最多使用1/4的緩沖池空間，最大為50.

使用以下指令可以檢視change buffer的狀态：

sql複制代碼sql
複制代碼
mysql> show engine innodb status\G;

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0

seg size：表示目前Inser Buffer的大小2*16k

free list len：表示空閑清單的長度。

size：表示已合并頁數

merged operations：表示每個change Buffer操作的次數

insert：表示Insert Buffer

delete mark：表示Delete Buffer

delete：表示Purge Buffer

discarded operations：表示當Change Buffer進行merge時，表進行了删除。此時無需将記錄合并到輔助索引中了。

1.3 Insert Buffer 的實作

Insert Buffer的資料結構是一棵B+樹。

在mysql4.1版本之前，每張表都有一棵B+樹，在其之後，全局隻有一個用于Insert Buffer的B+樹，負責對所有表的輔助索引進行insert buffer。

這顆B+樹存放在共享表空間當中，預設是ibdata1。是以，如果想通過獨立表空間恢複資料，往往會導緻check table失敗。因為輔助索引的資料可能還在Inser Buffer中，即共享表空間中，在通過ibd檔案恢複後，還需要進行repair table來重建表上的所有輔助索引。

Insert Buffer的非葉子節點存儲的是查詢的key（search key），其構造如下：

searck key一共9個位元組，space表示要待插入記錄所在表的表空間id，每個表都有一個唯一的space id。marker一個位元組，用來相容老版本的Insert buffer。offset表示頁所在偏移量，4個位元組。

當一個輔助索引要插入到頁時，首先查詢緩沖池，如果不在，那麼存儲引擎會構造一個search key，查詢Inser buffer 這棵B+樹，再将記錄插入到這個樹的葉子節點當中。

1.4 Merge Insert Buffer

哪些情況可能發生合并？如下所示：

輔助索引頁被讀取到緩沖池時；當輔助索引頁被讀取到緩沖池時，檢查Insert Buffer Bitmap頁，确認該輔助索引頁是否有資料存放于Insert Buffer 的B+樹中，有則将該資料插入到輔助索引頁中。使原本多次操作通過一次合并插入到了輔助索引頁中，提高了性能。
Insert Buffer Bitmap頁追蹤到該輔助索引頁已無可用空間時； Insert Buffer Bitmap用來追蹤輔助索引頁在緩沖區中可用的空間。并且至少有1/32的空間。如果小于1/32，則進行合并。
Master Thread。每一秒或10秒都進行合并。

二、兩次寫（Double Write）

Insert Buffer帶給InnoDB的是性能上的提升，而Double Write帶給InnoDB的則是資料頁的可靠性。

2.1 存在的原因

當資料發生當機的同時，資料正在寫入，16k的頁資料，可能隻寫入了4k資料，剩下的12k資料就丢失了。

當然可以通過重做日志進行恢複，但是如果被寫入的頁已經被損壞的話，那麼這部分資料就無法寫入了。

解決問題的方式是在重做日志進行重做之前，需要這個頁的一個副本，當發生寫入失效時，先通過這個副本恢複該頁，之後在進行重做。這就是Double Write。

2.2 Double Write體系架構

如上圖所示：doublewrite分為兩個部分，一部分是記憶體中的doublewrite buffer ，大小為2M，另一部分是磁盤上共享表空間中的連續128個頁，分為兩個區，大小也是2M。

當緩沖池對髒頁重新整理時，先将髒頁通過memcpy（記憶體拷貝）函數指派到doublewrite buffer中，然後doublewrite buffer分兩次，每次1M将資料寫入磁盤共享表空間的doublewrite中，完成後迅速調用fsync函數同步磁盤。doublewrite頁是連續的，效率快，開銷小。

通過下面的指令檢視mysql的雙寫資訊：

sql複制代碼sql
複制代碼
mysql> show status like "%InnoDB_dblwr%" ;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Innodb_dblwr_pages_written | 22    |
| Innodb_dblwr_writes        | 6     |
+----------------------------+-------+
2 rows in set (0.00 sec)

Innodb_dblwr_pages_written：一共寫入頁數。 Innodb_dblwr_writes：實際寫入頁數。

通過如下參數控制開啟和關閉：

sql複制代碼sql
複制代碼
mysql> show variables like 'innodb_doublewrite';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

三、自适應哈希索引（Adaptive Hash Index）

哈希是一種非常快的查找方法，在一般情況下，這種查找方式的時間複雜度為O(1)，即一次就能定位到資料。而B+樹的查找次數根據其高度而定。通常是3或4層，也就是需要3到4次的查詢。

InnoDB會監控表上的個索引頁的查詢。如果觀察到建立哈希索引可以帶來效率的提升，則會自動建立哈希索引，稱之為自适應哈希索引（Adaptive Hash Index，AHI）。AHI是通過緩沖池的B+樹頁建立，速度很快，不需要整張表建立哈希索引。InnoDB會根據通路的頻率和模式來自動為某些熱點頁建立哈希索引。

建立AHI的要求：對這個也的連續通路必須是一樣的。例如(a,b)這樣的聯合索引，其通路模式如下： where a = 1 where a = 1 and b = 1 如果以上兩種查詢交替進行，則不會對該頁構造AHI。

另外兩種建立AHI的條件：

1）同一通路模式通路100次

2）通路次數 = 資料記錄數 * 1/16

AHI是資料庫自優化的，不需要人為進行幹預。使用下面的方式可以檢視AHI的資料：

sql複制代碼sql
複制代碼
show engine innodb status\G;

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 27 buffer(s)
41.07 hash searches/s, 13.31 non-hash searches/s

每秒哈希查詢41.07次，非哈希查詢13.31次。

AHI隻能用于指定條件的查詢，如等于，對于範圍查找不能自動建立。non-hash就顯示了不是哈希查詢的次數。兩個參數對比可以檢視哈希查詢的效率。

使用如下參數可以控制AHI的狀态,預設是開啟的：

sql複制代碼sql
複制代碼
mysql> show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

四、異步IO（Async IO）

為了提高磁盤操作的性能問題，目前資料庫都是通過異步IO（Async IO）的方式來操作磁盤，InnoDB也是如此。

與AIO對應的是Sync IO，每進行一次IO，就要等待此次完成後才能進行下一次。當使用者發起一條查詢請求，這個請求可能需要查詢多個索引頁，則需要進行多次的IO，使用Sync IO的話，需要等待每一次IO執行後在執行下一次。當時用AIO時，使用者在發起一次IO請求後即可以發出下一次的IO請求，待所有請求完成後，會對所有的IO進行合并。

AIO的優勢除了異步速度快，還有另外的優勢就是其IO Merge（IO 合并），将多個IO合并為一個IO，可以提高性能。例如：如使用者連續通路頁的（space，pageNo）是（6,6），（6,7），（6,8），每個頁的大小是16k，如果是Sync IO的話，需要串行發送三次IO請求。而AIO則會判斷這三次的IO請求是否是連續的，如上面舉例得知page_no是連續的，則AIO會發送一個從（6,6）開始的48k的頁讀取請求。

使用下面的參數控制AIO的開啟,預設是ON：

sql複制代碼sql
複制代碼
mysql> show variables like 'innodb_use_native_aio';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_use_native_aio | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

目前InnoDB使用的AIO是基于作業系統核心級别支援的，使用的話需要libaio的支援，目前windows和linux都是支援的，而mac則暫時不支援。

五、重新整理鄰接頁（Flush Neighbor Page）

當重新整理一個髒頁時，InnoDB會檢測該頁所在區（extent，關于extent的内容看這篇文章blog.csdn.net/fu_zhongyua…）的所有頁，如果是髒頁，那麼一起重新整理到磁盤。這樣做通過AIO将多個IO操作合并為1個IO。在傳統的機械磁盤下有顯著的效果。

通過以下參數控制：

sql複制代碼sql
複制代碼
mysql> show variables like 'innodb_flush_neighbors';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_flush_neighbors | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

傳統機械硬碟建議開啟，固态硬碟建議關閉，即值設為0；

Mysql存儲-InnoDB關鍵特性

一、插入緩沖（Insert Buffer）

1.1 Insert Buffer

1.2 Change Buffer

1.3 Insert Buffer 的實作

1.4 Merge Insert Buffer

二、兩次寫（Double Write）

2.1 存在的原因

2.2 Double Write體系架構

三、自适應哈希索引（Adaptive Hash Index）

四、異步IO（Async IO）

五、重新整理鄰接頁（Flush Neighbor Page）

繼續閱讀

MySQL的varchar存儲原理：InnoDB記錄存儲結構

「資料庫」MySQL 的 varchar 存儲原理：InnoDB 記錄存儲結構

InnoDB存儲引擎的特點：行級鎖

故障分析 | innodb_thread_concurrency 導緻資料庫異常的問題分析

MyISAM 與 InnoDB 索引的差別有哪些？

基于對古羅馬複合柱曆史的研究複合柱是羅馬設計的柱式，結合了古希臘時代愛奧尼亞柱和科林斯柱的特點。複合柱具有高度裝飾的首都

mysql的innodb引擎是如何存儲資料的?

「資料庫」庖丁解InnoDB之REDO LOG

「資料庫」庖丁解InnoDB之UNDO LOG

MySQL InnoDB 行記錄存儲結構分析

「資料庫」庖丁解 InnoDB 之存儲結構

利用Inno Setup在VS編譯時自動建構安裝包

InnoDB緩沖池揭秘：MySQL中的資料緩存利器

揭秘InnoDB插入緩沖：提升非唯一輔助索引插入性能的秘密武器

InnoDB中的各種鎖及其應用