圖解｜從根上徹底理解MySQL的索引

蟬沐風發表於2022-03-11
圖解MySql索引

這是圖解MySQL的第4篇文章，這篇文章會讓你

  • 明白什麼是索引，徹底理解B+樹和索引的關係；
  • 徹底理解主鍵索引、普通索引、聯合索引；
  • 瞭解什麼是HASH索引，InnoDB和MyISAM索引的不同實現方式；
  • 輕鬆理解後續的索引使用規則。

img

1. 準備工作

為了更好地解釋索引，我們先建個表。

CREATE TABLE `user_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我建立了一個儲存引擎為InnoDB的表user_innodb，其中包含主鍵id、姓名欄位（name）、性別欄位（gender，用0，1表示不同性別）、手機號欄位（phone），並批量初始化了500W+條資料。

注：資料全部是模擬產生的，性別不做嚴格區分；手機號如有雷同，純屬巧合

mysql> SELECT COUNT(*) FROM user_innodb;
+----------+
| COUNT(*) |
+----------+
|  5283424 |
+----------+
1 row in set (0.31 sec)

image-20220306204532065

例1：為name建立索引之前

mysql> SELECT * FROM user_innodb WHERE name = "蟬沐風";
+---------+-----------+--------+-------------+
| id      | name      | gender | phone       |
+---------+-----------+--------+-------------+
| 1099999 | 蟬沐風    |      0 | 13203398311 |
+---------+-----------+--------+-------------+
1 row in set (0.96 sec)

例2：為name建立索引之後

mysql> SELECT * FROM user_innodb WHERE name = "蟬沐風";
+---------+-----------+--------+-------------+
| id      | name      | gender | phone       |
+---------+-----------+--------+-------------+
| 1099999 | 蟬沐風    |      0 | 13203398311 |
+---------+-----------+--------+-------------+
1 row in set (0.03 sec)

例3：根據主鍵id進行查詢

mysql> select * from user_innodb where id = 1099999;
+---------+-----------+--------+-------------+
| id      | name      | gender | phone       |
+---------+-----------+--------+-------------+
| 1099999 | 蟬沐風    |      0 | 13203398311 |
+---------+-----------+--------+-------------+
1 row in set (0.00 sec)

可以看到，建立索引之前搜尋name為蟬沐風的記錄花費時間為0.96秒，為name欄位建立索引後，搜尋時間僅為0.03秒，可見索引的作用之大。

但是我們沒有顯式為主鍵建立索引，為什麼主鍵查詢也這麼快？我在上一篇文章中解釋了主鍵查詢快的原因，但是隻解釋了一半，現在我來解釋另一半。

雖然我希望每一篇文章都講述一個獨立的知識點，但是對於MySQL這種複雜的軟體，各種細節之間盤根錯節，想深入理解一個知識點很多時候需要其他知識點的加持，在繼續閱讀之前，強烈推薦你花10分鐘先讀一下這篇文章。

如果你實在不想看，我會簡單總結一下之前講的內容。

強烈推薦閱讀：圖解｜12張圖解釋MySQL主鍵查詢為什麼這麼快

2. 前置知識

現在我們已經知道了，InnoDB儲存引擎為我們提供了4種不同的行格式來儲存我們向MySQL中插入的資料，在這裡我們統一稱之為記錄。

記錄是儲存在InnoDB頁中的，InnoDB儲存引擎將資料劃分為若干個頁，以頁作為磁碟和記憶體之間互動的最小單位。InnoDB中頁的大小預設為16KB。也就是預設情況下，一次最少從磁碟中讀取16KB的資料到記憶體中，一次最少把記憶體中16KB的內容重新整理到磁碟上。儲存使用者記錄的頁我們統一叫做資料頁，它只是眾多型別的InnoDB頁中的一種而已，其他型別的頁我們無需關注。

非常非常重要的一點是，在一個資料頁中，使用者記錄是按照主鍵由小到大的順序串聯而成的單向連結串列。

但是一個資料頁中的記錄可能非常多，為了逃避低效的遍歷，InnoDB引擎的設計者想出了一種絕妙的搜尋方法，把資料頁中的所有記錄（包括偽記錄）分成若干個小組（並對每個小組內的組員數量做了規定），每個小組選出組內最大的一條記錄作為“小組長”，接著把所有小組長的地址拿出來，編成目錄。

舉個例子，下面的圖片展示了一個資料頁中的所有記錄被分組的情況：

image

上圖中的所有記錄（包括偽記錄）分成了4個小組，每個小組的“組長”被單獨提拔，單獨編製成“目錄”，InnoDB官方稱之為「槽」。槽在物理空間中是連續的，意味著通過一個槽可以很輕鬆地找到它的上一個和下一個，這一點非常重要。

槽的編號從0開始，我們查詢資料的時候先找到對應的槽，然後再到小組中進行遍歷即可，因為一個小組內的記錄數量並不多，遍歷的效能損耗可以忽略。而且每個槽代表的“組長”的主鍵值也是從小到大進行排列的，所以我們可以用二分法進行槽的快速查詢。

圖中包含4個槽，分別是0、1、2、3，二分法查詢之前，最低的槽low=0，最高的槽high=3。現在我們再來看看在這個資料頁中，我們查詢id為7的記錄，過程是怎樣的。

  1. 使用二分法，計算中間槽的位置，(0+3)/2=1，檢視槽1對應的“組長”的主鍵值為4，因為4<7，所以設定low=1，high保持不變；
  2. 再次使用二分法，計算中間槽的位置，(1+3)/2=2，檢視槽2對應的“組長”的主鍵值為8，因為8>7，所以設定high=2，low保持不變；
  3. 現在high=2，low=1，兩者相差1，已經沒有必要繼續進行二分了，可以確定我們的記錄就在槽2中，並且我們也能知道槽2對應的“組長”的主鍵是8，但是記錄之間是單向連結串列，我們無法向前遍歷。上文提到過，我們可以通過槽2找到槽1，進而找到它的“組長”，然後沿著“組長”向下遍歷直到找到主鍵為7的記錄就可以了。

當使用者記錄多到一個資料頁裝不下的時候，就再申請一個資料頁，各個資料頁在邏輯上使用雙向連結串列進行連線，因此新分配的資料頁編號就沒必要非得按照從小到大的順序進行排列了，如下圖所示：

image-20220307132351376

因此，雖然在一個資料頁內能夠做到主鍵的快速查詢，但是InnoDB儲存引擎不知道你要查詢的記錄所在的頁號，那也只能從第一頁開始沿著雙向連結串列一直進行查詢，遍歷每一頁，至於在每一個資料頁中是怎麼查詢的，你已經很清楚了。

很顯然，InnoDB引擎有辦法能夠快速定位到你要的主鍵資料所在的資料頁，而不是從第一頁開始遍歷，否則不可能有例3那樣的查詢速度。

那麼，InnoDB是怎麼做到的呢？

3. InnoDB索引

3.1 主鍵索引登場

為了方便描述，我們假設一個資料頁最多隻能放3條使用者記錄，那麼user_innodb表的前12條資料的儲存形式如下圖：

image-20220307221311840

大家看這些連線起來的資料頁像不像組成一本書的每一章？自然，資料頁中的每一條記錄就是章中的每一個小節了。

Linux核心完全註釋章節

那麼為了加快檢索，我們可以模擬書籍章節目錄，給資料頁新增一個目錄。

image-20220308131643763

如上圖，我們為4個資料頁建立了一個目錄，每個資料頁對應了一條記錄，為了區別於使用者記錄，我們稱之為目錄項記錄，目錄項記錄同樣是按照主鍵從小到大的順序進行單向連結的。

不同於使用者記錄中包含了完整的資料，目錄項記錄只包含了資料頁的最小主鍵值和對應的資料頁號。既然都是記錄，InnoDB的設計者直接用資料頁來儲存目錄項記錄了，所以上圖中頁32的頁面結構和其他資料頁是完全一樣的。

接下來我們看看加了個目錄是如何提高我們的查詢效率的，以查詢主鍵id為8的記錄為例，步驟大致如下：

  1. 先找到儲存目錄項的資料頁32，通過二分法快速定位到對應的目錄項記錄，因為7<8<10，所以定位到對應的記錄所在的頁應該是頁14；
  2. 然後在頁14中進行查詢就可以了，查詢的方法我們之前介紹過了。

目前的頁面並不多，所以對查詢效率的提升並不十分明顯，但是一旦資料頁的數量飛速增長，這種通過新增目錄的方式帶來的查詢優勢會被無限放大！但是同時有個問題，資料頁多了，目錄項記錄在一個資料頁中不夠用了怎麼辦？

再加一個資料頁。我們再新增2條使用者記錄，看一下新增之後的樣子：

注：實際上一個頁面中能夠存放的記錄（使用者記錄/目錄項記錄）數目是非常多的，為了方便畫圖，我只是假設了資料頁最多存放3條使用者記錄，最多存放4條目錄項記錄

image-20220308140238471

現在假設要查詢主鍵ID為14的記錄，我們還是先得找到儲存目錄項的資料頁，可是現在有2個這種資料頁，分別是頁32、頁124，我怎麼知道要定位到哪一個目錄項資料頁呢？從頁32開始遍歷嗎？別開玩笑了，我們做這麼多就是為了不想遍歷。這樣吧，我們為儲存目錄項的資料頁再生成一個目錄。我們來捋一捋關係。

前面舉過例子，儲存使用者記錄的資料頁相當於章，使用者記錄相當於小節，為章節生成目錄就得到了儲存目錄項記錄的資料頁（頁32和頁124），相當於是一本書，然後再為書編一個目錄，就相當於是個書架。

image-20220308161025368

對應到儲存結構上那就是下圖：

image-20220310074318288

按照上圖，我們又新增了一個資料頁99，用來儲存頁32和頁124對應的2條目錄，現在要查詢主鍵ID為14的記錄，需要經歷這幾個步驟：

  1. 就從頁99中，快速檢索到對應的目錄項資料頁124；
  2. 在頁124中，快速檢索到對應的資料頁27；
  3. 在頁27中，快速檢索到主鍵為14的記錄。

到這裡為止，你已經悄悄地掌握了B+樹了。沒錯，上面我們一步步推匯出來的搜尋結構就是大名鼎鼎的B+樹，而MySQL給它起了一個更響亮的名字——索引。

B+樹最底層的節點（對應圖中儲存使用者記錄的資料頁）被稱為葉子節點，其他的節點自然叫做非葉子節點了，更特殊地，B+樹最頂部的節點叫做根節點。

有一個值得我們關注的細節，這棵B+樹的葉子節點儲存了我們完整的使用者記錄（就是我們插入表的所有資料），而且，這是使用者記錄在InnoDB引擎中的唯一儲存方式。也就是所謂的“索引即資料，資料即索引”。

更方便地一點是，這個關於主鍵的索引完全是由InnoDB儲存引擎自動生成的，不需要我們顯式地書寫建立索引的語句。這個索引叫做主鍵索引，又叫做聚簇索引。

主鍵索引有兩個特點：

  1. 按照主鍵的大小對使用者記錄和資料頁進行排序，記錄用單向連結串列連線，資料頁使用雙向連結串列連線；
  2. B+樹的葉子節點儲存了使用者的完整記錄。

現在終於解釋完為什麼主鍵查詢這麼快了，搞明白主鍵索引之後，普通索引和聯合索引就太簡單了！

3.2 普通索引

主鍵索引是在搜尋條件為主鍵的時候才會發揮作用，但是我要以name='蟬沐風'為搜尋條件怎麼辦？通過主鍵索引的講解，我們首先會想到這麼一個方案：再建立一個B+樹（我們稱為name索引），其中使用者記錄和資料頁按照name欄位進行排序，B+樹的葉子節點保留完整的使用者資料，這樣就可以實現對name列的快速搜尋了。

但是如此一來，表中資料就被完整記錄了2次（主鍵索引的葉子節點和name索引的葉子節點），要是我們為其他欄位再建立索引，磁碟空間可想而知。因此，我們得想個其他的辦法。

我們已經知道根據主鍵查詢使用者記錄是非常快的了，那我們可以想個辦法根據name欄位來迅速找到主鍵，然後再根據主鍵查詢使用者記錄啊。這個辦法同樣離不開B+樹。

image-20220310075128895

這棵B+樹和聚簇索引的B+樹有點區別：

  1. 葉子節點存放的不再是完整的使用者記錄，而是隻記錄name列和主鍵值；
  2. 資料頁中存放的使用者記錄和目錄項記錄由原本的按照主鍵排序變為按照name列排序；
  3. 目錄項記錄除了儲存索引列（name）和頁號之外，同時還儲存了主鍵值；（大家可以想一想，為什麼要儲存主鍵值）

有了這棵B+樹，你就可以通過name列快速找到主鍵值了，查詢的方式和根據主鍵值查詢使用者記錄的方式完全一樣，只不過前者查到的是主鍵值，後者查詢到的是一條完整的使用者記錄罷了。

你可能對字串進行二分法感到有點奇怪，甚至沒有接觸過的相關知識的讀者連對字串進行排序都會覺得很詫異。其實在建立表的時候我們可以對字串欄位指定字符集和比較規則，如果你不指定，MySQL會預設給你設定，總之，MySQL總會找到一個方式對字串進行排序。

現在得到主鍵的id了，然後根據主鍵id到主鍵索引中查詢到完整的使用者記錄，這個過程叫做回表。如果沒有為name列設定唯一性約束，那就可能找到多個符合條件的主鍵id，多回幾次表就可以了。

對name這種單個列新增的索引叫做普通索引，也叫二級索引。

如果同時對多個列建立索引，那B+樹的儲存又會是什麼樣子呢？這就是聯合索引了，理解了上面的內容，再理解聯合索引只是水到渠成的事罷了。

3.3 聯合索引

假設我們為name列和phone列建立聯合索引（注意我描述的順序），自然也是建立一棵B+樹，這棵B+樹和之前又稍微有點不同：

  1. 葉子節點存放的是name列、phone列和主鍵值；
  2. 目錄項記錄除了儲存索引列（name、phone）和頁號之外，同時還儲存了主鍵值；（大家可以想一想，為什麼要儲存主鍵值）
  3. 資料頁中存放的使用者記錄和目錄項記錄由原本的按照主鍵排序變為按照name列排序，如果name列相同，那就按照phone列排序；（如果phone列再一樣呢？你現在明白為什麼要儲存主鍵值了嗎？）

再畫個圖吧（有點偷懶了哈，資料頁號沒換）：

image-20220310111236577

還是和二級索引一樣，利用B+樹快速定位到資料頁，然後頁內快速定位到記錄，找到記錄中的主鍵id，再回表，如果找到多條符合條件的記錄，就多回幾次表。

4. InnoDB其他的索引方式

以上介紹的是B+樹索引，它其實是InnoDB儲存引擎提供的眾多索引中的一種而已，但卻是使用最多、面試中最常被問到的一種索引。除此之外，還提供了其他的索引方式，例如我的TablePlus工具（Mac上的MySQL連線工具）提供了4種。

image-20220310121417860

4.1 HASH

如果你用過Java的HashMap或者Python的字典，你對這個概念就應該很清楚了。

雜湊表是一種採用鍵值對（Key-Value）儲存資料的結構，它會根據索引欄位生成雜湊碼和指標，指標指向表中的資料。不可避免地，多個索引欄位值經過雜湊函式的換算，會出現同一個值的情況，處理這種情況的一種方法就是建立一個單向連結串列。如下圖所示，我們為name欄位建立HASH索引：

image-20220310181503085

雜湊索引有3個重要特點：

  1. 查詢速度非常非常快，時間複雜度是O(1)，因為雜湊索引中的資料不是按照順序儲存的，所以不能用於排序；
  2. 查詢資料的時候要根據鍵值計算雜湊碼，所以它只能支援等值查詢（=、IN），不支援範圍查詢（>、<、>=、<=、BETWEEN、AND）；
  3. 如果雜湊衝突，就得采用新增單向連結串列的方法解決，會造成效率下降。

另外，雖然提供了HASH的索引方法，但是在InnoDB中無法顯式建立一個HASH索引，所謂地支援雜湊索引其實指的是自適應雜湊索引（AHI），是InnoDB自動為BufferPool中的熱點頁建立的索引。雖然TablePlus在建立索引的時候能夠選擇HASH，但是實際建立完之後顯示型別仍然是BTREE。

4.2 FULLTEXT

如果你的資料表有一個大文字欄位，你想查詢這個欄位中包含「蟬沐風」的所有記錄，你可能會採用LIKE '%蟬沐風%'的方式進行查詢，但是索引的最左匹配原則告訴你這樣的查詢效率太低了，這時候全文索引就出現了。

為了說明問題，我們假設一個文字欄位儲存了這樣一段文字：

我叫蟬沐風，歡迎大家關注我的微信公眾號

想要快速根據某個詞進行查詢，首先要對這段文字進行分詞，得到下列分詞結果：

我/叫/蟬/沐/風/，/歡迎/大家/關注/我/的/微信/公眾號

然後建立每個分詞和使用者記錄（在搜尋領域中的專業術語叫做文件）的對應關係，生成一個單詞文件矩陣

image-20220206153142984

然後就可以根據某個單詞進行查詢了，這也是現代搜尋引擎的基本原理，感興趣的話可以搜尋一下倒排索引，再感興趣可以瞭解一下Elastic Search。

4.3 SPATIAL

是對空間資料的索引，我沒使用過，就暫時解釋這麼多了。

5. MyISAM的索引方案

image (1)
不同的儲存引擎存放資料的方式不一樣，產生的檔案數量和格式也不一樣，InnoDB檔案包含2個，MEMORY檔案包含1個，MyISAM檔案包含3個。我們接下來關注的就是MyISAM中的檔案。

  • .MYD檔案，D代表Data，是MyISAM的資料檔案，存放使用者記錄，也就是我們插入的表資料；
  • .MYI檔案，I代表Index，是MyISAM的索引檔案。一個索引就會有一棵B+樹，所有的B+樹都儲存在這個檔案當中。

也就是說，不同於InnoDB的“索引即資料”的思想，MyISAM儲存引擎中的索引和資料是分開儲存的。

MyISAM中的B+樹長啥樣子呢？其實樣子和InnoDB差不多，區別就是MyISAM的B+樹的葉子節點儲存的是使用者記錄對應的磁碟地址，所以從索引檔案.MYI中找到對應的索引鍵（建立索引的列的值）後，會到.MYD中找到對應的使用者記錄。以主鍵為例我再再再畫個圖：

image-20220310221013325

下期見！

相關文章