Physical Structure of an InnoDB Index

所有的innodb索引都是btree索引，索引記錄保存在葉子上，默認的索引頁大小是16K。當(dāng)有新的記錄插入時，innodb出于對將來的insert和update操作的考慮，會嘗試留下1/16的空閑頁大小。

如果索引記錄是完全按照索引記錄的大小順序插入的，那么索引也將填滿整個頁大小的15/16，如果插入順序完全隨機，那么索引頁基本上填充為1/2至15/16自建。如果填充因子低于1/2,innodb會嘗試重建b-tree。

Mysql5.6以后，可以通過innodb_page_size參數(shù)設(shè)置當(dāng)前實例下每個索引頁的大小，一旦設(shè)定，無法再更改回來。推薦的配置一般是16K，8K或者4K。另外假如一個Mysql實例設(shè)置了不同于默認值的innodb_page_size A，那么將無法使用其他不同于A值的實例上的文件（比如做一個物理備份和恢復(fù)）

Insert Buffering

數(shù)據(jù)庫應(yīng)用通常按照主鍵順序插入的，在這種情況下，因為聚集索引的順序和這個主鍵值的順序完全一致，insert操作將會減少很多的隨機IO。

另一方面，二級索引通常不是唯一的，那么在二級索引中插入數(shù)據(jù)時是一個相對隨機的順序。同樣的，delete和update操作在影響數(shù)據(jù)頁時，涉及到索引的變更，在二級索引上也并不是緊挨著的。這就導(dǎo)致了大量的隨機IO。

當(dāng)插入一條記錄，或者從非唯一的二級索引刪除一條記錄，innodb首先會去檢查該二級索引頁是否在緩沖池中。如果在緩沖池，innodb將會直接在內(nèi)存中修改這個索引頁。如果該索引也不在緩沖池，那么innodb將會將這個修改記錄到插入緩沖，也就是insertbuffer。Insert buffer通常都比較小，所以能夠保證全部在緩沖池中，并且更新非常頻繁。這個修改的進程就是change buffering（通常情況下，它只會只作用于insert操作，所以也被稱為insertbuffering，而該數(shù)據(jù)結(jié)構(gòu)就是insert buffer）

Disk I/O for Flushing the Insert Buffer

那么插入緩沖如何減少隨機IO的呢？每個一段時間，insert buffer會去合并在insertbuffer中的二級非唯一索引。通常情況下，它會合并N個修改到同一個btree索引的索引頁中，從而節(jié)約了很多IO操作。經(jīng)測試，insertbuffer可以提高15倍的插入速度。

在事務(wù)提交后，insert buffer可能還在合并寫入。所以，假如當(dāng)DB異常重啟，reovery階段，當(dāng)有非常多的二級索引需要更新或插入時，insert buffer將可能花費很長時間，甚至幾個小時。在這個階段，磁盤IO將會增加，那么就會導(dǎo)致disk-bound類型的查詢有顯著的性能下滑。

Adaptive Hash Indexes

自適應(yīng)哈希索引（AHI）使得innodb在緩沖池擁有足夠的內(nèi)存和某些工作負載下，看起來更像一個內(nèi)存數(shù)據(jù)庫，并且不會犧牲任何事務(wù)的特點和穩(wěn)定性。這個特色由參數(shù)innodb_adaptive_hash_index控制，動態(tài)參數(shù)，默認為on表示打開自適應(yīng)哈希索引，關(guān)閉AHI后內(nèi)置哈希表將會被立馬清空，而正常的操作依舊可以繼續(xù)，只是直接通過訪問B-TREE索引。重新使能AHI后哈希表又會被重建。

通過觀察搜素模式，mysql會利用index key的前綴建立哈希索引，這個前綴可以是任意長度，并且它可能僅僅是B-tree上的某些值，而不是整個b-tree。哈希索引通過檢測，會在經(jīng)常被訪問的index pages上建立哈希索引。

如果一個表幾乎大部分都在緩沖池中，那么建立一個哈希索引能夠加快等值查詢，通過將btree的索引值轉(zhuǎn)換成一個排序指針。Innodb有這個機制，可以監(jiān)控索引的搜索情況，如果它注意到有些查詢通過建立哈希索引可以優(yōu)化查詢，那么它會自動建立，所以說它是“自適應(yīng)的”。

在某些工作負載下，通過哈希索引查找?guī)淼男阅芴嵘齼r值遠大于這個額外的監(jiān)控索引搜索情況和保持這個哈希表結(jié)構(gòu)所帶來的開銷。但某些時候，在負載較高的情況下，自適應(yīng)哈希索引中添加的read/write 鎖也會帶來競爭，比如高并發(fā)的join操作。Like操作和%的通配符同樣不適用于AHI。如果工作負載不適合AHI，建議將它關(guān)閉，以免帶來不必要的性能開銷。因為mysql內(nèi)部很難預(yù)示在一個特定的場合下AHI到底是否合適，推薦做一個實際工作負載的壓測（有無AHI兩種情況）。在5.6及以后版本中將會考慮讓越來越多的工作負載最好disable掉自適應(yīng)哈希索引，盡管目前而言它默認還是開啟的。

哈希索引的創(chuàng)建往往是基于現(xiàn)有的b-tree，innodb可以通過觀察b-tree的搜索情況建立任意長度的b-tree索引前綴的方式建立哈希索引。一個哈希索引可以只是部分的，僅包括b-tree index中最經(jīng)常被訪問到的頁。

你可以通過觀察show engine innodb status結(jié)果中的SEMAPHORES部分來決定是否使用自適應(yīng)哈希索引。如果你看到很多線程都在btr0sea.c文件上創(chuàng)建rw-latch上waiting，那么建議關(guān)閉掉自適應(yīng)哈希索引。本人曾經(jīng)碰到過的一個case截圖如下，典型的高并發(fā)模式下AHI引起的競爭，需要關(guān)閉AHI

以上這篇淺談innodb的索引頁結(jié)構(gòu),插入緩沖,自適應(yīng)哈希索引就是小編分享給大家的全部內(nèi)容了，希望能給大家一個參考，也希望大家多多支持腳本之家。