淺談innodb的索引頁(yè)結(jié)構(gòu),插入緩沖,自適應(yīng)哈希索引

Physical Structure of an InnoDB Index

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

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

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

Insert Buffering

數(shù)據(jù)庫(kù)應(yīng)用通常按照主鍵順序插入的，在這種情況下，因?yàn)榫奂饕捻樞蚝瓦@個(gè)主鍵值的順序完全一致，insert操作將會(huì)減少很多的隨機(jī)IO。

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

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

Disk I/O for Flushing the Insert Buffer

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

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

Adaptive Hash Indexes

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

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

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

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

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

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

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