Innodb三大特性之insertbuffer_MySQL

insert buffer是一種特殊的數據結構（B+ tree）并不是緩存的一部分，而是物理頁，當受影響的索引頁不在buffer pool時緩存 secondary index pages的變化，當buffer page讀入buffer pool時，進行合并操作，這些操作可以是 INSERT, UPDATE, or DELETE operations (DML)

最開始的時候只能是insert操作，所以叫做insert buffer，現在已經改叫做change buffer了

insert buffer 只適用于 non-unique secondary indexes 也就是說只能用在非唯一的索引上，原因如下

1、primary key 是按照遞增的順序進行插入的，異常插入聚族索引一般也順序的，非隨機IO

2 寫唯一索引要檢查記錄是不是存在，所以在修改唯一索引之前,必須把修改的記錄相關的索引頁讀出來才知道是不是唯一、這樣Insert buffer就沒意義了，要讀出來(隨機IO)

所以只對非唯一索引有效

二、insert buffer的原理

對于為非唯一索引，輔助索引的修改操作并非實時更新索引的葉子頁，而是把若干對同一頁面的更新緩存起來做，合并為一次性更新操 作，減少IO，轉隨機IO為順序IO,這樣可以避免隨機IO帶來性能損耗，提高數據庫的寫性能

具體流程

先判斷要更新的這一頁在不在緩沖池中

a、若在，則直接插入；

b、若不在，則將index page 存入Insert Buffer，按照Master Thread的調度規則來合并非唯一索引和索引頁中的葉子結點

Master Thread的調度規則

a、主動merger[innodb主線程定期完成，用戶線程無感知]

主動merge通過innodb主線程(svr_master_thread）判斷：若過去1s之內發生的I/O小于系統I/O能力的5%，則主動進行一次insert buffer的merge操作。merge的頁面數為系統I/O能力的5%，讀取采用async io模式。每10s,必定觸發一次insert buffer meger操作。meger的頁面數仍舊為系統 I/O能力的5%。

1）主線程發出async io請求，async讀取需要被merge的索引頁面

2）I/O handler 線程，在接受到完成的async I/O之后，進行merge

b 、被動merge[用戶線程完成，用戶能感受到meger操作帶來的性能影響]

1） insert操作，導致頁面空間不足，需要分裂(split)。由于insert buffer只針對單個頁面，不能buffer page split[頁已經在內存里]，因此引起頁面的被動meger。同理，update操作導致頁面空間不 足；purge導致頁面為空等。總之,若當前操作引起頁面split or merge，那么就會導致被動merge；

2） insert操作，由于其它各種原因，insert buffer優化返回false，需要真正讀取page時，要進行被動merge。與一不同的是，頁在disk上，需要讀取到內存里；

3）在進行insert buffer操作，發現insert buffer太大，需要壓縮insert buffer，這時需要強制被動merge，不允許 insert 操作進行。

三、insert buffer的內部實現

1、insert buffer的數據結構是一棵B+樹，在MySQL4.1之前的版本中每張表都有一棵insert buffer B+樹

MySQL4.1之后，全局只有一棵insert buffer B+樹，負責對所有的表的輔助索引進行 insert buffer。這棵B+樹存放在共享表空間中，默認也就是ibdata1中。因此，試圖通過獨立表空間ibd文件恢復表中數據時，往往會導致check table 失敗。這是因為表的輔助索引中的數據可能還在insert buffer中，也就是共享表空間中。所以通過idb文件進行恢復后，還需要進行repair table 操作來重建表上所有的輔助索引

2、insert buffer的非葉子節點存放的是查詢的search key（鍵值），

其構造包括三個字段：space （4 byte）+ marker（1byte） + offset（4byte） = search key （9 byte ）

space表示待插入記錄所在的表空間id，InnoDB中，每個表有一個唯一的space id，可以通過space id查詢得知是哪張表；

marker是用來兼容老版本的insert buffer；

offset表示頁所在的偏移量。

3、當一個輔助索引需要插入到頁（space， offset）時，如果這個頁不在緩沖池中，那么InnoDB首先根據上述規則構造一個search key，接下來查詢insert buffer這棵B+樹，然后再將這條記錄插入到insert buffer B+樹的葉子節點中

4、對于插入到insert buffer B+樹葉子節點的記錄，需要根據如下規則進行構造：

space | marker | offset | metadata | secondary index record

啟用insert buffer索引后，輔助索引頁（space、page_no）中的記錄可能被插入到insert buffer B+樹中，所以為了保證每次merge insert buffer頁必須成功，還需要有一個特殊的頁來標記每個輔助索引頁（space、page_no）的可用空間，這個頁的類型為insert buffer bitmap。

四、insert buffer的缺點

1、可能導致數據庫宕機后實例恢復時間變長。如果應用程序執行大量的插入和更新操作，且涉及非唯一的聚集索引，一旦出現宕機，這時就有大量內存中的插入緩沖區數據沒有合并至索引頁中，導致實例恢復時間會很長

2、在寫密集的情況下，插入緩沖會占用過多的緩沖池內存（innodb_buffer_pool），默認情況下最大可以占用1/2，這在實際應用中會帶來一定的問題

3、insert buffer 無法進行控制，for different workloads and hardware configuration，特別是在SSD盛行的今天

五、查看insert buffer

mysql> show engine innodb status \G
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
41 inserts, 41 merged recs, 499 merges
Hash table size 3984403, node heap has 967 buffer(s)
14.66 hash searches/s, 64.65 non-hash searches/s
---
LOG
---
Log sequence number 27233311008
Log flushed up to 27233311008
Last checkpoint at 27233310593
0 pending log writes, 0 pending chkp writes
37848626 log i/o's done, 1.00 log i/o's/second

size: The number of pages used within the change buffer. Change buffer size is equal to seg size - (1 + free list len). The 1 + value represents the change buffer header page.

free list len: The number of pages free within the change buffer.代表了空閑頁的數量

seg size: The size of the change buffer, in pages. 插入緩沖的大小為2 *16KB

merges: The total number of change buffer merges.表示合并次數

merged operations - insert: The number of inserted records merged.merged插入的記錄數

merged operations - delete mark: The number of deleted records merged.merged刪除記錄數

merged operations - delete: The number of purge records merged.merged清除記錄數

discarded operations - insert: The number of insert merge operations discarded.

discarded operations - delete mark: The number of delete merge operations discarded.

discarded operations - delete: The number of purge merge operations discarded.

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com