在資料庫中，如果索引太多，應用程式的效能可能會受到影響，如果索引太少，又會對查詢效能產生影響。所以，我們要追求兩者的一個平衡點，足夠多的索引帶來查詢效能提高，又不因為索引過多導致修改資料等操作時負載過高。 文章會從，B+樹索引，索引的分類，雜湊索引，全文索引，這個幾個方面講解

B+樹索引

• 索引的查詢
• 索引的插入
• 索引的刪除

索引的分類

• 聚集索引
• 輔助索引
• 聯合索引
• 覆蓋索引

雜湊索引

• 雜湊演算法
• 自適應雜湊索引

全文索引

• 倒排索引
• 全文檢索索引快取
• 全文索引的一些限制

InnoDB支援3種常見索引，我們接下來要詳細講解的就是 B+ 樹索引，雜湊索引，全文索引。

B+樹索引

1、B+樹中的B不是代表的二叉（Binary） ，而是代表平衡（Balance），因為B+樹是從最早的平衡二叉樹演化而來，但是B+樹不是一個二叉樹。

2、B+樹是為磁碟或其他直接存取輔助裝置設計的一種平衡查詢樹，在B+樹中，所有的記錄節點都是按照鍵值大小順序存在同一層的葉子節點，由葉子節點指標進行相連。

3、B+樹在資料庫中的特點就是高扇出，因此在資料庫中B+樹的高度一般都在2~4層，這也就是說查詢一個鍵值記錄時，最多隻需要2到4次IO,當前的機械硬碟每秒至少可以有100次IO，2~4次IO意味著查詢時間只需要0.02~0.04秒。

4、B+樹索引並不能找到一個給定鍵值的具體行，B+樹索引能找到的只是被查詢的鍵值所在行的頁，然後資料庫把頁讀到記憶體，再記憶體中進行查詢，最後找到要查詢的資料。

5、資料庫中B+樹索引可以分為，聚集索引和非聚集索引，但是不管是聚集索引還是非聚集索引，其內部都是B+樹實現的，即高度是平衡的，葉子節點存放著所有的資料，聚集索引和非聚集索引不同的是，葉子節點是否儲存的是一整行資訊。每張表只能有一個聚集索引。

6、B+樹的每個資料頁（葉子節點）是通過一個雙向連結串列進行連結，資料頁上的資料的順序是按照主鍵順序儲存的。

先來看一個B+樹，其高度為2，每頁可以放4條記錄，扇出為5。

圖：一顆高度為2的B+樹

索引的查詢

B+樹索引使用二分法查詢，也稱折半查詢法，基本思想就是：將記錄有序化（遞增或遞減）排列，在超找過程中採用跳躍式方式查詢，既先以有序數列的中心點位置比較物件，如果要查詢的元素小於該元素的中心點元素，則將待查詢的元素縮小為左半部分，否則為右半部分，通過一次比較，將查詢區間縮小一半。

如圖所示，從有序列表中查詢 48，只需要3步：

圖：二分法查詢

索引的插入

B+樹的查詢速度很快，但是維護一顆平衡的B+樹代價就是非常大的，通常來說，需要1次或者多次左旋右旋來保證插入後樹的平衡性。

B+樹的插入為了保持樹的平衡，需要做大量的頁（葉子節點）的拆分，頁的儲存基本都在磁碟，頁的拆分意味著磁碟的操作，所以應該儘量減少頁的拆分，在採用自增長ID，作為主鍵，會大量的減少頁的拆分，提升的效能。

B+樹 插入的三種情況

Leaf Page滿	Index Page滿	操作
No	No	直接將記錄插入葉子節點
Yes	No	1、拆分Leaf Page 2、將中間的節點放入到Index Page中 3、小於中間節點的記錄放左邊 4、大於或等於中間節點的記錄放右邊
Yes	Yes	1、拆分Leaf Page 2、小於中間節點的記錄放左邊 3、大於或等於中間節點的記錄放右邊 4、拆分Index Page 5、小於中間節點的記錄放左邊 6、大於中間節點的記錄放右邊 7、中間節點放入上一層 Index Page

圖：一顆高度為2的B+樹

我們用例項來分析B+樹的插入。

（1）我們插入28這個鍵值，發現當前Leaf Page和Index Page都沒有滿，我們直接插入就可以了。

（2）這次我們再插入一條70這個鍵值，這時原先的Leaf Page已經滿了，但是Index Page還沒有滿，符合表（B+樹 插入的三種情況）的第二種情況，這時插入Leaf Page後的情況為50、55、60、65、70。我們根據中間的值60拆分葉節點。將中間節點放入到Index Page中。

（3）因為圖片顯示的關係，這次我沒有能在各葉節點加上雙向連結串列指標。最後我們來插入記錄95，這時符合表（B+樹 插入的三種情況）討論的第三種情況，即Leaf Page和Index Page都滿了，這時需要做兩次拆分。

可以看到，不管怎麼變化，B+樹總是會保持平衡。但是為了保持平衡，對於新插入的鍵值可能需要做大量的拆分頁（split）操作，而B+樹主要用於磁碟，因此頁的拆分意味著磁碟的操作，應該在可能的情況下儘量減少頁的拆分。因此，B+樹提供了旋轉（rotation）的功能。

索引的刪除

B+樹使用填充因子（fill factor） 來控制樹的刪除變化，50%是填充因子可設的最小值，B+樹的刪除也同樣必須保證刪除後樹的平衡性，刪除的過程中會涉及，合併葉子節或兄弟節點，但是都是為了保持樹的平衡。

索引的分類

在瞭解B+樹索引的本質和實現後，我們看看索引分為幾類，聚集索引，輔助索引，聯合索引，覆蓋索引

聚集索引

就是按照每張表的主鍵構造一顆B+樹，同時葉子節點儲存整張表的行記錄數，也將聚集索引的葉子節點成為“資料頁”，聚集索引的特性決定了表中的行記錄資料也是索引的一部分。同B+樹資料結構一樣，每個資料頁都通過一個雙向連結串列進行連結。

資料頁只能按照一顆B+樹進行排序，因此每張表只能有一個聚集索引，由於資料頁定義了邏輯順序，聚集索引能夠很快的在資料頁訪問指標進行範圍的查詢資料。

聚集索引在物理上不是連續的，在邏輯上是連續的，前面已經說過是通過雙向連結串列進行維護，物理儲存可以不按照主鍵順序儲存。

輔助索引

輔助索引（也稱非聚集索引），葉子節點並不包含行記錄的全部資料。葉子節點除了包含鍵值外，每個葉子節點還包含了一個書籤，該書籤告訴InnoDB 儲存引擎可以從哪裡找到輔助索引相對應行的記錄。因此InnoDB 儲存引擎的輔助索引的書籤就是相應整行資料的聚集索引鍵。

一個表中可以有多個輔助索引。例如，一個輔助索引樹需要遍歷3次才能找到主鍵索引，如果聚集索引樹的高為同樣為3，那麼它還需要對聚集索引樹進行三次查詢，最終才能找到一個完整的資料頁，因此一共需要6次IO訪問才能得到最終的資料頁。

聯合索引

聯合索引是指對錶上多個列進行建立索引，聯合索引本質還是一顆B+樹，不同的是索引的鍵值數量不是1個，而是大於等於2。聯合索引的鍵值在B+樹中也是有序的，通過葉子節點可以在邏輯的順序上讀出所有資料。

覆蓋索引

InnoDB儲存引擎支援覆蓋索引（或稱索引覆蓋），就是從輔助索引中就可以直接得到查詢的記錄，而不需要再次查詢聚集索引中的記錄。使用覆蓋索引的好處就是，輔助索引不包括整行記錄的所有資訊，所以覆蓋索引的大小要小於聚集索引，因此可以減少IO操作。

通俗的解釋：

覆蓋索引是非聚集組合索引的一種形式，它包括在查詢裡的Select、Join和Where子句用到的所有列（即建立索引的欄位正好是覆蓋查詢語句[select子句]與查詢條件[Where子句]中所涉及的欄位，也就是索引包含了查詢正在查詢的所有資料）

雜湊索引

學習雜湊索引之前，我們先了解一些基礎的知識：雜湊演算法。雜湊演算法是一種常用的演算法，時間複雜度為O(1)。它不僅應用在索引上，各個資料庫應用中也都會使用。

雜湊演算法

InnoDB儲存引擎使用雜湊演算法來對字典進行查詢，雜湊碰撞採用轉連結串列解決，雜湊函式採用除法雜湊方式。

例如：當前引數InnoDB_buffer_pool_size大小為10M，則共有640個16k的頁，對於緩衝頁記憶體的雜湊表來說，需要分配640×2=1280個槽，但是由於1280不是質數，所以需要取比1280更大的一點的質數，應該是1399，所以啟動的時候，會分配1399個槽的雜湊表，用來雜湊查詢所在的緩衝池中的頁。

InnoDB儲存引擎是通過除法雜湊到1399個其中的一個槽中。

自適應雜湊索引

自適應雜湊索引採用之前說的雜湊表方式，不同的是雜湊索引對字典型別的等值查詢非常快，對範圍查詢就無能為力了。

所以說雜湊索引只能用於搜尋等值查詢，範圍查詢是不能使用雜湊索引。

全文索引

之前已經說過，B+樹索引的特點，對於使用如下sql，是支援B+樹索引的，只要content 加了B+樹索引，就能利用索引進項快速查詢。

我們通過 B+ 樹索引可以進行字首查詢，如：

select * from blog where content like 'xxx%';
複製程式碼

只要為content列新增了B+樹索引（聚集索引或輔助索引），就可快速查詢。但在更多情況下，我們在部落格或搜尋引擎中需要查詢的是某個單詞，而不是某個單詞開頭，如：

select * from blog where content like '%xxx%';
複製程式碼

此時如果使用B+樹索引依然是全表掃描，而全文檢索（Full-Text Search）就是將整本書或文章內任意內容檢索出來的技術。

根據B+樹索引的特點是不支援的，InnoDB儲存引擎從1.2.x開始支援全文索引技術，其特性支MyISAM的全部功能。

具體實現原理接下來會介紹

倒排索引

全文檢索使用倒排索引來實現，倒排索引同B+樹索引一樣，也是一種資料結構，它在輔助表中儲存了單詞與單詞自身在一個或多個文件中所在位置的對映，這通常利用關聯陣列實現。

倒排索引它需要將分詞（word）儲存在一個輔助表（Auxiliary Table）中，為了提高全文檢索的並行效能，共有6張輔助表。輔助表中儲存了單詞和單詞在各行記錄中位置的對映關係。它分為兩種：倒排檔案索引，詳細倒排索引

1、inverted file index（倒排檔案索引），表現為{單詞，單詞所在文件ID} 2、full inverted index（詳細倒排索引），表現為{單詞，(單詞所在文件ID, 文件中的位置)}

全文檢索表

DocumentID	Text 文件內容
1	Souyunku Technical team (搜雲庫技術團隊)
2	Go Technical stack (Go技術棧)

inverted file index（倒排檔案索引）-輔助表儲存為

倒排檔案索引型別的輔助表儲存為：

Number	Text 分詞	Documents (單詞所在文件ID)
1	Souyunku	1
2	Technical	1，2
3	team	1
4	Go	2
5	stack	2

full inverted index（ 詳細倒排索引）-輔助表儲存為

詳細倒排索引型別的輔助表儲存為，佔用更多空間，也更好的定位資料，比提供更多的搜尋特性：

Number	Text 分詞	Documents (單詞所在文件ID:文件中的位置)
1	Souyunku	1:1
2	Technical	1:2 ，2:2
3	team	1:3
4	Go	2:1
5	stack	2:3

全文檢索索引快取

輔助表是存在與磁碟上的持久化的表，由於磁碟I/O比較慢，因此提供FTS Index Cache（全文檢索索引快取）來提高效能。FTS Index Cache是一個紅黑樹結構，根據（word, list）排序，在有資料插入時，索引先更新到快取中，而後InnoDB儲存引擎會批量進行更新到輔助表中。

當資料庫當機時，尚未落盤的索引快取資料會自動讀取並儲存，配置引數innodb_ft_cache_size控制快取的大小，預設為32M，提高該值，可以提高全文檢索的效能，但在故障時，需要更久的時間恢復。

在刪除資料時，InnoDB不會刪除索引資料，而是儲存在DELETED輔助表中，因此一段時間後，索引會變得非常大，可以通過optimize table命令手動刪除無效索引記錄。如果需要刪除的內容非常多，會影響應用程式的可用性，引數innodb_ft_num_word_optimize控制每次刪除的分詞數量，預設為2000，使用者可以調整該引數來控制刪除幅度。

全文索引的一些限制

1、現在只支援myisam和innodb 2、不支援分割槽表 3、多列組合的全文檢索索引必須使用相同的字符集和字元序 4、象形文字不支援。需要ngram來分詞 5、建立全文索引的各個欄位必須統一 6、match（）裡的查詢列，必須是在fulltext索引裡定義過的 7、against（）必須為字串且為常量 8、索引提示會更差 9、在innodb中，所有涉及到全文索引列的DML操作（update，insert，delete），只會在事務提交的時候，執行。中間可能要分詞，標記等 10、不能用 % 萬用字元 11、不支援沒有單詞界定符（delimiter）的語言，如中文、日語、韓語等

參考資料：MySQL技術內幕 InnoDB儲存引擎 第2版

關注我們

作者：鵬磊
出處：微信公眾號「搜雲庫技術團隊」，微信ID：souyunku
版權歸作者所有，任何形式轉載請聯絡作者。