重要，知識點：InnoDB的插入緩衝

架構技術專欄發表於2020-10-29

世界上最快的捷徑，就是腳踏實地，本文已收錄【架構技術專欄】關注這個喜歡分享的地方。

InnoDB引擎有幾個重點特性，為其帶來了更好的效能和可靠性：

  • 插入緩衝（Insert Buffer）
  • 兩次寫（Double Write）
  • 自適應雜湊索引（Adaptive Hash Index）
  • 非同步IO（Async IO）
  • 重新整理鄰接頁（Flush Neighbor Page）

今天我們的主題就是 插入緩衝（Insert Buffer）,由於InnoDB引擎底層資料儲存結構式B+樹，而對於索引我們又有聚集索引和非聚集索引。

在進行資料插入時必然會引起索引的變化，聚集索引不必說，一般都是遞增有序的。而非聚集索引就不一定是什麼資料了，其離散性導致了在插入時結構的不斷變化，從而導致插入效能降低。

所以為了解決非聚集索引插入效能的問題，InnoDB引擎 創造了Insert Buffer。

Insert Buffer 的儲存

看到上圖，可能大家會認為Insert Buffer 就是InnoDB 緩衝池的一個組成部分。

重點：其實對也不對，InnoDB 緩衝池確實包含了Insert Buffer的資訊，但Insert Buffer 其實和資料頁一樣，也是物理存在的（以B+樹的形式存在共享表空間中）。

Insert Buffer 的作用

先說幾個點：

  • 一張表只能有一個主鍵索引，那是因為其物理儲存是一個B+樹。（別忘了聚集索引葉子節點儲存的資料，而資料只有一份）

  • 非聚集索引葉子節點存的是聚集索引的主鍵

聚集索引的插入

首先我們知道在InnoDB儲存引擎中，主鍵是行唯一的識別符號（也就是我們常叨叨的聚集索引）。我們平時插入資料一般都是按照主鍵遞增插入，因此聚集索引都是順序的，不需要磁碟的隨機讀取。

比如表：

CREATE TABLE test(
	id INT AUTO_INCREMENT,
	name VARCHAR(30),
	PRIMARY KEY(id)
);

如上我建立了一個主鍵 id,它有以下的特性：

  • Id列是自增長的
  • Id列插入NULL值時，由於AUTO_INCREMENT的原因，其值會遞增
  • 同時資料頁中的行記錄按id的值進行順序存放

一般情況下由於聚集索引的有序性，不需要隨機讀取頁中的資料，因為此類的順序插入速度是非常快的。

但如果你把列 Id 插入UUID這種資料，那你插入就是和非聚集索引一樣都是隨機的了。會導致你的B+ tree結構不停地變化，那效能必然會受到影響。

非聚集索引的插入

很多時候我們的表還會有很多非聚集索引，比如我按照b欄位查詢，且b欄位不是唯一的。如下表：

CREATE TABLE test(
	id INT AUTO_INCREMENT,
	name VARCHAR(30),
	PRIMARY KEY(id),
	KEY(name)
);

這裡我建立了一個x表，它有以下特點：

  • 有一個聚集索引 id
  • 有一個不唯一的非聚集索引 name
  • 在插入資料時資料頁是按照主鍵id進行順序存放
  • 輔助索引 name的資料插入不是順序的

非聚集索引也是一顆B+樹，只是葉子節點存的是聚集索引的主鍵和name 的值。

因為不能保證name列的資料是順序的，所以非聚集索引這棵樹的插入必然也不是順序的了。

當然如果name列插入的是時間型別資料，那其非聚集索引的插入也是順序的。

Insert Buffer 的到來

可以看出非聚集索引插入的離散性導致了插入效能的下降，因此InnoDB引擎設計了 Insert Buffer來提高插入效能 。

我來看看使用Insert Buffer 是怎麼插入的：

首先對於非聚集索引的插入或更新操作，不是每一次直接插入到索引頁中，而是先判斷插入的非聚集索引頁是否在緩衝池中。

若在，則直接插入；若不在，則先放入到一個Insert Buffer物件中。

給外部的感覺好像是樹已經插入非聚集的索引的葉子節點，而其實是存放在其他位置了

以一定的頻率和情況進行Insert Buffer和輔助索引頁子節點的merge（合併）操作，通常會將多個插入操作一起進行merge，這就大大的提升了非聚集索引的插入效能。

Insert Buffer的使用要求：

  • 索引是非聚集索引
  • 索引不是唯一（unique）的

只有滿足上面兩個必要條件時，InnoDB儲存引擎才會使用Insert Buffer來提高插入效能。

那為什麼必須滿足上面兩個條件呢？

第一點索引是非聚集索引就不用說了，人家聚集索引本來就是順序的也不需要你

第二點必須不是唯一（unique）的，因為在寫入Insert Buffer時，資料庫並不會去判斷插入記錄的唯一性。如果再去查詢肯定又是離散讀取的情況了，這樣InsertBuffer就失去了意義。

Insert Buffer資訊檢視

我們可以使用命令SHOW ENGINE INNODB STATUS來檢視Insert Buffer的資訊：

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 7545, free list len 3790, seg size 11336, 
8075308 inserts,7540969 merged sec, 2246304 merges
...

使用命令後，我們會看到很多資訊，這裡我們只看下INSERT BUFFER 的:

  • seg size 代表當前Insert Buffer的大小 11336*16KB

  • free listlen 代表了空閒列表的長度

  • size 代表了已經合併記錄頁的數量

  • Inserts 代表了插入的記錄數

  • merged recs 代表了合併的插入記錄數量

  • merges 代表合併的次數，也就是實際讀取頁的次數

merges：merged recs大約為1∶3，代表了Insert Buffer 將對於非聚集索引頁的離散IO邏輯請求大約降低了2/3

Insert Buffer的問題

說了這麼多針對於Insert Buffer的好處，但目前Insert Buffer也存在一個問題：

即在寫密集的情況下，插入緩衝會佔用過多的緩衝池記憶體（innodb_buffer_pool），預設最大可以佔用到1/2的緩衝池記憶體。

佔用了過大的緩衝池必然會對其他緩衝池操作帶來影響

Insert Buffer的優化

MySQL5.5之前的版本中其實都叫做Insert Buffer，之後優化為 Change Buffer 可以看做是 Insert Buffer 的升級版。

插入緩衝（ Insert Buffer）這個其實只針對 INSERT 操作做了緩衝，而Change Buffer 對INSERT、DELETE、UPDATE都進行了緩衝，所以可以統稱為寫緩衝，其可以分為：

  • Insert Buffer

  • Delete Buffer

  • Purgebuffer

總結：

Insert Buffer到底是個什麼？

  • 其實Insert Buffer的資料結構就是一棵B+樹。

  • 在MySQL 4.1之前的版本中每張表有一棵Insert Buffer B+樹

  • 目前版本是全域性只有一棵Insert Buffer B+樹，負責對所有的表的輔助索引進行Insert Buffer

  • 這棵B+樹存放在共享表空間ibdata1中

以下幾種情況下 Insert Buffer會寫入真正非聚集索引，也就是所說的Merge Insert Buffer

  • 當輔助索引頁被讀取到緩衝池中時
  • Insert Buffer Bitmap頁追蹤到該輔助索引頁已無可用空間時
  • Master Thread執行緒中每秒或每10秒會進行一次Merge Insert Buffer的操作

一句話概括下：

Insert Buffer 就是用於提升非聚集索引頁的插入效能的，其資料結構類似於資料頁的一個B+樹，物理儲存在共享表空間ibdata1中 。

相關文章