【MySQL】Insert buffer 漫談

 我們知道在進行插入操作時，資料頁的存放還是按主鍵id的執行順序存放， 但是對于非聚集索引，葉子節點的插入不再是順序的了。

例如,對于如下表結構進行insert操作

create table tab ( 

  id int auto_increment, 

  name varchar(30),

  primary key (id),

  key(name)

 ) engine=innodb  default charset=utf8;

nanme 為非唯一字段，這時就需要離散地通路非聚集索引頁，插入性能在這裡變低了。然而這并不是這個name字段上索引的錯誤，因為B+樹的特性決定了非聚集索引插入的離散性。

為了解決非聚族索引的随機寫性能差,InnoDB 存儲引擎開發了 innsert-buffer pool (5.5 中做了加強,稱之為 change buffer pool)

一 什麼是 innsert-buffer pool

innodb使用insert buffer"欺騙"資料庫:對于為非唯一索引，輔助索引的修改操作并非實時更新索引的葉子頁,而是把若幹對同一頁面的更新緩存起來做合并為一次性更新操作,轉化随機IO 為順序IO,這樣可以避免随機IO帶來性能損耗，提高資料庫的寫性能。

1.1 原理:

   a 先判斷要更新的這一頁在不在記憶體中。

   b 如果不在,則讀取index page 存入Insert Buffer，按照Master Thread的排程規則來合并非唯一索引和索引頁中的葉子結點.

1.2 Master Thread的排程規則

  a 主動merger[innodb主線程定期完成，使用者線程無感覺]

    主動merger：

    原理:主動merge通過innodb主線程(svr_master_thread）判斷：若過去1s之内發生的I/O小于系統I/O能力的5%，

        則主動進行一次insert buffer的meger操作。meger的頁面數為系統I/O能力的5%，讀取采用async io模式。

        每10s,必定觸發一次insert buffer meger操作。meger的頁面數仍舊為系統 I/O能力的5%。

    步驟:

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

        2.I/O handler 線程，在接受到完成的async I/O之後，進行merger

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

    被動merge:

      情況一：

      insert操作，導緻頁面空間不足，需要分裂(split)。由于insert buffer隻針對單個頁面，不能buffer page split[頁已經在記憶體裡]，是以引起頁面的被動meger。同理，update操作導緻頁面空間不 足；purge導緻頁面為空等。總之:若 目前操作引起頁面split or merge,那麼就會導緻被動merge。

      情況二:

      insert操作，由于其它各種原因，insert buffer優化傳回false，需要真正讀取page時，要進行被動merge。與一不同的是，頁在disk上，需要讀取到記憶體裡。

      情況三：

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

二 為什麼要求是非唯一索引呢？

因為

  1 主鍵是行唯一的标示符,當app 寫入行時，是按照主鍵遞增的順序進行插入的，異常插入聚族索引一般也順序的，不需要随機IO。

  2 寫唯一索引要檢查記錄是不是存在，是以在修改唯一索引之前,必須把修改的記錄相關的索引頁讀出來才知道是不是唯一,這樣Insert buffer就沒意義了，反正要讀出來(讀帶來随機IO)，是以隻對非唯一索引有效。

三 如何檢視insert buffer  

我們可以通過show engine innodb status \G 來檢視插入緩沖的資訊

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 0, seg size 2, 2920 merges

merged operations:

 insert 23858, delete mark 0, delete 0

discarded operations:

 insert 0, delete mark 0, delete 0

seg size顯示了目前插入緩沖的大小為2 *16KB，大約為32KB，free list len代表了空閑清單的長度，size代表了已經合并記錄頁的數量。merges 表示合并次數。

Inserts代表插入的記錄數，delete mark delete 次數均為0.

四 insert buffer 增強之 change buffering

    change buffering 是MySQL5.5加入的新特性，change buffering是insert buffer的加強，insert buffer隻針對insert有效，change buffering對insert、delete、update(delete+insert)、purge都有效。當修改一個索引塊(secondary index)時的資料時，索引塊在buffter pool中不存在，修改資訊就會被cache在change buffer中，當通過索引掃描把需要的索引塊讀取到buffer pool時，會和change buffer中修改資訊合并，再擇機寫回disk。目的還是為了減少随機IO帶來性能損耗，說明白了：把随機IO盡量變成順序IO。

五 小結

對于廉價的機械硬碟，這個參數還是能幫助提高性能的。在SSD盛行的今天，在SSD上随機通路和順序通路性能幾乎差不多的情況下，insert buffer/change buffering特性不會帶來多大的性能提升。

六 參考文章