MySQL 索引的型別——《高效能MySQL》

Super_time發表於2020-11-23
MySql索引型別

讀《高效能MySQL》第三版，筆記。
官方文件：https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

索引有很多種型別，可以為不同的場景提供更好的效能。

在 MySQL 中，索引是在儲存引擎層而不是伺服器層實現的。

所以，並沒有統一的索引標準：不同儲存引擎的索引的工作方式並不一樣，也不是所有的儲存引擎都支援所有型別的索引。

即使多個儲存引擎支援同一種型別的索引，其底層的實現也可能不同。

下面我們看看 MySQL 支援的索引型別，以及他們的優點和缺點。

B-Tree 索引

當人們談論索引的時候，如果沒有特別指明型別，那多半說的是 B-Tree 索引，它們使用 B-Tree 資料結構來儲存資料。

實際上很多儲存引擎使用的是 B+Tree ，即每一個葉子節點都包含指向下一個葉子節點的指標，從而方便葉子節點的範圍遍歷。

我們使用術語 “ B-Tree ”，是因為 MySQL 在 CREATE TABLE 和其他語句中使用該關鍵字。 不過，底層的儲存引擎也可能使用不同的儲存結構。
例如，NDB 叢集儲存引擎內部實際上使用了 T-Tree 結構儲存這種索引，即使其名字是 BTREE；InnoDB 則使用的是 B+Tree。

儲存引擎以不同的方式使用 B-Tree 索引，效能也各有不同，各有優劣。
例如：

MyISAM使用字首壓縮技術使得索引更小索引通過資料的物理位置引用被索引的行
InnoDB按照原資料格式進行儲存則根據主鍵引用被索引的行

B-Tree 通常譯為著所有的值都是按順序儲存的，並且每一個葉子頁到根的距離相同。

B-Tree 索引能夠加塊訪問資料的速度，因為儲存引擎不再需要進行全表掃描來獲取需要的資料，取而代之的是從索引的根節點開始進行搜尋。根節點的槽中存放了指向子節點的指標，儲存引擎根據這些指標向下查詢。通過比較節點頁的值和要查詢的值可以找到合適的指標進入下層子節點，這些指標實際上定義了子節點頁中值的上限和下限。最終儲存引擎要麼是找到對應的值，要麼該記錄不存在。

葉子節點比較特別，它們的指標指向的是被索引的資料，而不是其他的節點頁（不同引擎的 “ 指標 ” 型別不同）。根節點和葉子節點之間可能又很多層節點頁，這和樹的深度與表的大小直接相關。

B-Tree 對索引列是順序組織儲存的，所以很適合查詢範圍資料。

可以使用 B-Tree 索引的查詢型別。
B-Tree 索引適用於全鍵值、鍵值範圍或鍵字首查詢。
其中鍵字首查詢只適用於根據最左字首的查詢。前面所述的索引對如下型別的查詢有效。

  • 全值匹配，和索引中的所有列進行匹配。
  • 匹配最左字首。
  • 匹配列字首，只匹配某一列的值的開頭部分。
  • 匹配範圍值。
  • 精確匹配某一列並範圍匹配另外一列。
  • 只訪問索引的查詢。

因為索引樹中的節點是有序的，所以除了按值查詢之外，索引還可以用於查詢中的 ORDER BY 操作（按順序查詢）。

下面是一些關於 B-Tree 索引的限制：

  • 如果不是按照索引的最左列開始查詢，則無法使用索引。
  • 不能跳過索引中的列。
  • 如果查詢中有某個列的範圍查詢，則其右邊所有列都無法使用所有優化查詢。

到這裡讀者應該可以明白，前面提到的索引列的順序是多麼的重要：這些限制都和索引列的順序有關。
在優化效能的時候，可能需要使用相同的列但順序不同的索引來滿足不同型別的查詢需求。

雜湊索引

雜湊索引（hash index）基於雜湊表實現。
只有精度匹配索引所有列的查詢才有效。
對於每個一行資料，儲存引擎都會對所有的索引列計算一個雜湊碼（hash code）。
雜湊碼是一個較小的值，並且不同的鍵值的行計算出來的雜湊碼頁不一樣。
雜湊索引將所有的雜湊碼儲存在索引中，同時在雜湊表中儲存指向每個資料行的指標。

在 MySQL 中，只有 Memory 引擎顯式支援雜湊索引。這也是 Memory 引擎表的預設索引型別，Memory 引擎同時也支援 B-Tree 索引。值得一提的是，Memory 引擎是支援非唯一雜湊索引的，這在資料庫世界裡面是比較與眾不同的。
如果多個列的雜湊值相同，索引會以連結串列的方式存放多個記錄指標到同一個雜湊條目中。

因為索引自身只需儲存對應的雜湊值，所以索引的結構十分緊湊，這也讓雜湊索引查詢的速度非常快。
然後，雜湊索引也有它的限制：

  • 雜湊索引只包含雜湊值和行指標，而不儲存欄位值，所以不能使用索引中的值來避免讀取行。不過，訪問記憶體中的行的速度很快，所以大部分情況下這一點對效能的影響並不明顯。
  • 雜湊索引資料並不是按照索引值順序儲存的，所以也就無法用於排序。
  • 雜湊索引頁不支援部分索引列匹配查詢，因為雜湊索引時鐘是使用索引列的全部內容來計算雜湊值的。
  • 雜湊索引只支援等值比較查詢，不支援任何範圍查詢。
  • 訪問雜湊索引的資料非常快，除非有很多雜湊衝突。當出現雜湊衝突的時候，儲存引擎必須遍歷連結串列中所有的行指標，逐行進行比較，直到找到所有符合條件的行。
  • 如果雜湊衝突很多的話，一些索引維護操作的代價也會很高。

因為這些限制，雜湊索引只適用於某些特定的場合。而一旦適合雜湊索引，則它帶來的效能提升將非常顯著。

InnoDB 引擎有一個特殊的功能叫做 “ 自適應雜湊索引（adaptive hash index） ”。當 InnoDB 注意到某些索引值被使用得非常頻繁時，它會在記憶體中基於 B-Tree 索引之上再建立一個雜湊索引，這樣就讓 B-Tree 索引也具有雜湊索引的一些優點，比如快速的雜湊查詢。這是一個完全自動的、內部的行為，使用者無法控制或者配置，不過如果有必要完全可以關閉該功能。

建立自定義雜湊索引。 如果儲存引擎不支援雜湊索引，則可以模擬像 InnoDB 一樣建立雜湊索引，這可以享受一些雜湊索引的便利，例如值需要很小的索引就可以為超長的鍵建立索引。

思路很簡單：在 B-Tree 基礎之上建立一個偽雜湊索引。這和真正的雜湊索引不是一回事，因為還是使用 B-Tree 進行查詢，但是它使用雜湊值而不是鍵本身進行索引查詢。你需要做的就是在查詢的 WHERE 子句中手動指定使用雜湊函式。

空間資料索引（R-Tree）

MyISAM 表支援空間索引，可以用作地理資料儲存。和 B-Tree 索引不同，這類索引無須字首查詢。空間索引會從所有維度來所有資料。查詢時，可以有效地使用任意維度來組合查詢。必須使用 MySQL 的 GIS 相關函式如 MBRCONTAINS() 等來維護資料。MySQL 的 GIS 支援並不完善，所以大部分人都不會使用這個特性。開源關聯式資料庫系統中對 GIS 的解決方案做得比較好的是 PostgreSQL 的 PostGIS。

全文索引

全文索引是一種特殊型別的索引，它查詢的是文字中的關鍵詞，而不是直接比較索引中的值。全文搜尋和其他幾類索引的匹配方式完全不一樣。它有很多需要注意的細節，如停用詞、詞幹和複數、布林搜尋等。全文索引更類似於搜尋引擎做到的事情，而不是簡單的 WHERE 條件匹配。

在相同的列上同時建立全文索引和基於值得 B-Tree 索引不會有衝突，全文索引適用於 MATCH AGAINST 操作，而不是普通的 WHERE 條件操作。

其他索引型別

還有很多第三方的儲存引擎使用不同型別的資料結構來儲存索引。
例如： ToKuDB 使用分形樹索引（fractal tree index），這是一類較新開發的資料結構，既有 B-Tree 的很多優點，也避免了 B-Tree 的一些缺點。

索引的優點

  1. 索引大大減少了伺服器需要掃描的資料量。
  2. 索引可以幫助伺服器避免排序和臨時表。
  3. 索引可以將隨機 I/O 變為順序 I/O。

相關文章