Innodb三大特性之insertbuffer_MySQL

insert buffer是一種特殊的數(shù)據(jù)結(jié)構(gòu)（B+ tree）并不是緩存的一部分，而是物理頁(yè)，當(dāng)受影響的索引頁(yè)不在buffer pool時(shí)緩存 secondary index pages的變化，當(dāng)buffer page讀入buffer pool時(shí)，進(jìn)行合并操作，這些操作可以是 INSERT, UPDATE, or DELETE operations (DML)

最開始的時(shí)候只能是insert操作，所以叫做insert buffer，現(xiàn)在已經(jīng)改叫做change buffer了

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

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

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

所以只對(duì)非唯一索引有效

二、insert buffer的原理

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

具體流程

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

a、若在，則直接插入；

b、若不在，則將index page 存入Insert Buffer，按照Master Thread的調(diào)度規(guī)則來合并非唯一索引和索引頁(yè)中的葉子結(jié)點(diǎn)

Master Thread的調(diào)度規(guī)則

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

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

1）主線程發(fā)出async io請(qǐng)求，async讀取需要被merge的索引頁(yè)面

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

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

1） insert操作，導(dǎo)致頁(yè)面空間不足，需要分裂(split)。由于insert buffer只針對(duì)單個(gè)頁(yè)面，不能buffer page split[頁(yè)已經(jīng)在內(nèi)存里]，因此引起頁(yè)面的被動(dòng)meger。同理，update操作導(dǎo)致頁(yè)面空間不 足；purge導(dǎo)致頁(yè)面為空等?？傊?若當(dāng)前操作引起頁(yè)面split or merge，那么就會(huì)導(dǎo)致被動(dòng)merge；

2） insert操作，由于其它各種原因，insert buffer優(yōu)化返回false，需要真正讀取page時(shí)，要進(jìn)行被動(dòng)merge。與一不同的是，頁(yè)在disk上，需要讀取到內(nèi)存里；

3）在進(jìn)行insert buffer操作，發(fā)現(xiàn)insert buffer太大，需要壓縮insert buffer，這時(shí)需要強(qiáng)制被動(dòng)merge，不允許 insert 操作進(jìn)行。

三、insert buffer的內(nèi)部實(shí)現(xiàn)

1、insert buffer的數(shù)據(jù)結(jié)構(gòu)是一棵B+樹，在MySQL4.1之前的版本中每張表都有一棵insert buffer B+樹

MySQL4.1之后，全局只有一棵insert buffer B+樹，負(fù)責(zé)對(duì)所有的表的輔助索引進(jìn)行 insert buffer。這棵B+樹存放在共享表空間中，默認(rèn)也就是ibdata1中。因此，試圖通過獨(dú)立表空間ibd文件恢復(fù)表中數(shù)據(jù)時(shí)，往往會(huì)導(dǎo)致check table 失敗。這是因?yàn)楸淼妮o助索引中的數(shù)據(jù)可能還在insert buffer中，也就是共享表空間中。所以通過idb文件進(jìn)行恢復(fù)后，還需要進(jìn)行repair table 操作來重建表上所有的輔助索引

2、insert buffer的非葉子節(jié)點(diǎn)存放的是查詢的search key（鍵值），

其構(gòu)造包括三個(gè)字段：space （4 byte）+ marker（1byte） + offset（4byte） = search key （9 byte ）

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

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

offset表示頁(yè)所在的偏移量。

3、當(dāng)一個(gè)輔助索引需要插入到頁(yè)（space， offset）時(shí)，如果這個(gè)頁(yè)不在緩沖池中，那么InnoDB首先根據(jù)上述規(guī)則構(gòu)造一個(gè)search key，接下來查詢insert buffer這棵B+樹，然后再將這條記錄插入到insert buffer B+樹的葉子節(jié)點(diǎn)中

4、對(duì)于插入到insert buffer B+樹葉子節(jié)點(diǎn)的記錄，需要根據(jù)如下規(guī)則進(jìn)行構(gòu)造：

space | marker | offset | metadata | secondary index record

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

四、insert buffer的缺點(diǎn)

1、可能導(dǎo)致數(shù)據(jù)庫(kù)宕機(jī)后實(shí)例恢復(fù)時(shí)間變長(zhǎng)。如果應(yīng)用程序執(zhí)行大量的插入和更新操作，且涉及非唯一的聚集索引，一旦出現(xiàn)宕機(jī)，這時(shí)就有大量?jī)?nèi)存中的插入緩沖區(qū)數(shù)據(jù)沒有合并至索引頁(yè)中，導(dǎo)致實(shí)例恢復(fù)時(shí)間會(huì)很長(zhǎng)

2、在寫密集的情況下，插入緩沖會(huì)占用過多的緩沖池內(nèi)存（innodb_buffer_pool），默認(rèn)情況下最大可以占用1/2，這在實(shí)際應(yīng)用中會(huì)帶來一定的問題

3、insert buffer 無法進(jìn)行控制，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.代表了空閑頁(yè)的數(shù)量

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

merges: The total number of change buffer merges.表示合并次數(shù)

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

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

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

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.

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com