《MySQL 基礎篇》十一：索引的儲存結構

Author: ACatSmiling

Since: 2024-09-21

為什麼使用索引

索引是儲存引擎用於快速找到資料記錄的一種資料結構。 常將索引比作教科書的目錄部分（實際上不是完全一樣），透過目錄找到對應文章的頁碼，便可快速定位到需要的文章。MySQL 中也是一樣的道理，進行資料查詢時，首先檢視查詢條件是否命中某條索引，符合則透過索引查詢相關資料，如果不符合則需要全表掃描，即一條一條的查詢記錄，直到找到與條件符合的記錄。

如上圖所示，資料庫沒有索引的情況下，資料分佈在硬碟不同的位置上面，讀取資料時，擺臂需要前後擺動查詢資料，這樣操作非常耗時。如果資料順序擺放，也需要從 1 到 6 行按順序讀取，這樣就相當於進行了 6 次 I/O 操作，依舊非常耗時。如果不借助任何索引結構來幫助快速定位資料，假設查詢 Col2 = 89 這條記錄，就需要逐行查詢、比較。從 Col2 = 34 開始，進行比較，發現不是，繼續下一行。當前的表只有不到 10 行資料，但是如果表很大，有上千萬條資料，就意味著要做很多次磁碟 I/O 才能找到。因為要查詢 Col2 = 89 這條記錄，CPU 必須先去磁碟查詢這條記錄，找到之後載入到記憶體，再對資料進行處理。這個過程最耗時間的就是磁碟 I/0（涉及到磁碟的旋轉時間（速度較快）和磁碟的尋道時間（速度慢，耗時））。

假如給資料使用二叉樹這樣的資料結構進行儲存，如下圖所示：

對欄位 Col2 新增索引，就相當於在磁碟上為 Col2 維護了一個索引的資料結構，即這個二叉搜尋樹。二叉搜尋樹的每個節點儲存的是(K, V) 結構，key 是 Col2，value 是該 key 所在行的檔案指標（地址）。比如：該二叉搜尋樹的根節點就是 (34, 0x07)。對 Col2 新增了索引之後，這時再去查詢 Col2 = 89 這條記錄，會先去查詢該二叉搜尋樹（二叉樹的遍歷查詢）。讀 34 到記憶體，因為 89 > 34，繼續右側資料，讀 89 到記憶體，因為 89 = 89，找到資料並返回。然後，根據找到的節點的 value 快速定位到要查詢的記錄對應的地址。透過上述過程，可以發現，只需要查詢兩次（兩次磁碟 I/O），就可以定位到記錄的地址，查詢速度也就提高了。

這就是為什麼要使用索引的原因，其目的就是為了減少磁碟 I/O 的次數，加快查詢的速度。

索引及其優缺點

索引概述

MySQL 官方對索引的定義為：索引（Index）是幫助 MySQL 高效獲取資料的資料結構。

索引的本質：索引是資料結構。可以簡單理解為"排好序的快速查詢資料結構"，滿足特定查詢演算法。這些資料結構以某種方式指向資料， 這樣就可以在這些資料結構的基礎上實現高階查詢演算法。

索引是在儲存引擎中實現的，因此，每種儲存引擎的索引，不一定完全相同，並且每種儲存引擎不一定支援所有索引型別。同時，儲存引擎可以定義每個表的最大索引數和最大索引長度。所有儲存引擎支援每個表至少 16 個索引，總索引長度至少為 256 位元組。有些儲存引擎支援更多的索引數和更大的索引長度。

優點

• 類似大學圖書館建書目索引，提高資料檢索的效率，降低資料庫的 I/O 成本，這也是建立索引最主要的原因。
• 透過建立唯一索引，可以保證資料庫表中每一行資料的唯一性。
• 在實現資料的參考完整性方面，可以加速表和表之間的連線。換句話說，對於有依賴關係的子表和父表聯合查詢時，可以提高查詢速度。
• 在使用分組和排序子句進行資料查詢時，可以顯著減少查詢中分組和排序的時間，降低 CPU 的消耗。

缺點

增加索引也有許多不利的方面，主要表現在如下幾個方面：

• 建立索引和維護索引要耗費時間，並且隨著資料量的增加，所耗費的時間也會增加。
• 索引需要佔磁碟空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物理空間， 儲存在磁碟上 ，如果有大量的索引，索引檔案就可能比資料檔案更快達到最大檔案尺寸。
• 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度。當對錶中的資料進行增加、刪除和修改的時候，索引也要動態地維護，這樣就降低了資料的維護速度。

因此，選擇使用索引時，需要綜合考慮索引的優點和缺點。

Tips：索引可以提高查詢的速度，但是會影響插入記錄的速度。這種情況下，最好的辦法是先刪除表中的索引，然後插入資料，插入完成後再建立索引。

InnoDB 中索引的推演

索引之前的查詢

先來看一個精確匹配的例子：

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx

在一個頁中的查詢

假設目前表中的記錄比較少，所有的記錄都可以被存放到一個頁中，在查詢記錄的時候，可以根據搜尋條件的不同分為兩種情況：

• 以主鍵作為搜尋條件：
  • 可以在頁目錄中使用二分法快速定位到對應的槽，然後再遍歷該槽對應分組中的記錄，即可快速找到指定的記錄。
• 以其他列作為搜尋條件：
  • 因為再資料頁中沒有對非主鍵列建立所謂的頁目錄，所以無法透過二分法快速定位相應的槽。這種情況下，只能從最小記錄開始，依次遍歷單連結串列中的每條記錄，然後對比每條記錄，是不是符合搜尋條件。很顯然，這種查詢的效率是非常低的。

在很多頁中的查詢

大部分情況下，表中存放的記錄都是非常多的，需要很多的資料頁來儲存這些記錄。在很多頁中查詢記錄，可以分為兩個步驟：

• 第一步：定位到記錄所在的頁。
• 第二步：從記錄所在的頁中查詢相應的記錄。

在沒有索引的情況下，不論是根據主鍵列或者其他列的值進行查詢，由於並不能快速的定位到記錄所在的頁，所以只能從第一個頁沿著雙向連結串列一直往下找，然後在每一個頁中根據上面的查詢方式去查詢指定的記錄。因為要遍歷所有的資料頁，所以這種方式顯然是超級耗時的。如果一個表有一億條記錄呢？此時索引應運而生。相比一本書的目錄，索引更像是圖書館中的書架結構，每一個書架相當於一個資料頁，書架上的每一本書相當於表中的一條記錄。

設計索引

新建表：

mysql> CREATE TABLE index_demo(
    -> c1 INT,
    -> c2 INT,
    -> c3 CHAR(1),
    -> PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.03 sec)

這個新建的 index_demo 表中有 2 個 INT 型別的列，1 個 CHAR(1) 型別的列，而且規定了 c1 列為主鍵，這個表使用 Compact 行格式來實際儲存記錄。這裡簡化了 index_demo 表的行格式示意圖：

只在示意圖裡展示記錄的這幾個部分：

• record_type：記錄頭資訊的一項屬性，表示記錄的型別，0 表示普通記錄、2 表示最小記錄、3 表示最大記錄、1 暫時還沒用過，後面講。
• next_record：記錄頭資訊的一項屬性，表示下一條地址相對於本條記錄的地址偏移量，後面演示用箭頭來表明下一條記錄是誰。
• 各個列的值 ：這裡只記錄在 index_demo 表中的三個列，分別是 c1 、 c2 和 c3。
• 其他資訊 ：除了上述 3 種資訊以外的所有資訊，包括其他隱藏列的值以及記錄的額外資訊。

將記錄格式示意圖的其他資訊項暫時去掉並把它豎起來的效果就是這樣：

把一些記錄放到頁裡的示意圖就是：

一個簡單的索引設計方案

在根據某個搜尋條件查詢一些記錄時為什麼要遍歷所有的資料頁呢？因為各個頁中的記錄並沒有規律，所以並不知道搜尋條件會匹配哪些頁中的記錄，因此不得不依次遍歷所有的資料頁。所以如果想快速的定位到需要查詢的記錄在哪些資料頁中該咋辦？此時，可以為快速定位記錄所在的資料頁建立一個目錄，而建這個目錄必須完成下邊這些事：

1. 下一個資料頁中使用者記錄的主鍵值，必須大於上一個頁中使用者記錄的主鍵值。

假設：每個資料頁最多能存放 3 條記錄（實際上一個資料頁非常大，可以存放很多記錄），有了這個假設之後，向 index_demo 表插入 3 條記錄：

mysql> INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

那麼這些記錄已經按照主鍵值的大小，串聯成一個單向連結串列了，如圖所示：

從圖中可以看出，index_demo 表中的 3 條記錄，都被插入到編號為 10 的資料頁中了。此時，再插入一條記錄：

mysql> INSERT INTO index_demo VALUES(4, 4, 'a');
Query OK, 1 row affected (0.01 sec)

因為頁 10 最多隻能放 3 條記錄，所以不得不再分配一個新頁：

注意，新分配的資料頁編號可能並不是連續的。它們只是透過維護著上一個頁和下一個頁的編號而建立了連結串列關係。另外，頁 10 中使用者記錄最大的主鍵值是 5，而頁 28 中有一條記錄的主鍵值是 4，因為 5 > 4，所以這就不符合下一個資料頁中使用者記錄的主鍵值必須大於上一個頁中使用者記錄的主鍵值的要求，所以在插入主鍵值為 4 的記錄的時候，需要伴隨著一次記錄移動，也就是把主鍵值為 5 的記錄移動到頁 28 中，然後再把主鍵值為 4 的記錄插入到頁 10 中，這個過程的示意圖如下：

這個過程表明了在對頁中的記錄進行增刪改操作的過程中，必須透過一些諸如記錄移動的操作，來始終保證這個狀態一直成立：下一個資料頁中使用者記錄的主鍵值，必須大於上一個頁中使用者記錄的主鍵值。而這個過程，稱為頁分裂。

2. 給所有的頁建立一個目錄項。

由於資料頁的編號可能是不連續的，所以在向 index_demo 表中插入許多條記錄後，可能是這樣的效果：

因為這些16 KB的頁在物理儲存上是不連續的，所以如果想從這麼多頁中根據主鍵值快速定位某些記錄所在的頁，需要給它們做個目錄，每個頁對應一個目錄項，每個目錄項包括下邊兩個部分：

• 頁的使用者記錄中最下的主鍵值，用key表示。
• 頁號，用page_no表示。

所以，為上邊幾個頁做好的目錄，就像這個樣子：

以頁 28 為例，它對應目錄項 2，這個目錄項中包含著該頁的頁號 28，以及該頁中使用者記錄的最小主鍵值 5。只需要把幾個目錄項在物理儲存器上連續儲存（比如：陣列），就可以實現根據主鍵值快速查詢某條記錄的功能了。比如，查詢主鍵為 20 的記錄，具體查詢過程分為兩步：

• 第一步，先從目錄項中根據二分法快速確定出主鍵值為 20 的記錄在目錄項 3 中（因為 12 < 20 < 209），它對應的頁是頁 9。
• 第二步，再根據前邊說的在頁中查詢記錄的方式，去頁 9 中定位具體的記錄。

至此，針對資料頁做的簡易目錄就搞定了。這個目錄有一個別名，稱為索引。

InnoDB 中的索引方案

1. 迭代一次：目錄項紀錄的頁。

前面稱為一個簡單的索引設計方案，是因為為了在根據主鍵值進行查詢時，使用二分法快速定位具體的目錄，而假設所有目錄項都可以在物理儲存器上連續儲存，但是這樣做有幾個問題：

• InnoDB 是使用頁來作為管理儲存空間的基本單位，最多能保證16 KB的連續儲存空間，而隨著表中記錄數量的增多，需要非常大的連續的儲存空間，才能把所有的目錄項都放下，這對記錄數量非常多的表是不現實的。
• 同時，也會時常對記錄進行增刪操作，假設把頁 28 中的記錄都刪除了，那意味著目錄項 2 也就沒有存在的必要了，這就需要把目錄項 2 後面的目錄項都向前移動一下，這樣牽一髮而動全身的操作效率很差。

所以，需要一種可以靈活管理所有目錄項的方式。而目錄項實際上跟普通的使用者記錄差不多，只不過目錄項中的兩個列是主鍵和頁號而已，為了和使用者記錄做一下區分，把這些用來表示目錄項的記錄稱為目錄項記錄。那 InnoDB 是怎麼區分一條記錄是普通的使用者記錄還是目錄項記錄呢？答案是使用記錄頭資訊裡的record_type屬性，它的各個取值含義如下：

• 0：普通的使用者記錄。
• 1：目錄項記錄。
• 2：最小記錄。
• 3：最大記錄。

然後，把前邊使用到的目錄項放到資料頁中的樣子就是這樣：

從圖中可以看出來，新分配了一個編號為 30 的頁來專門儲存目錄項記錄。

目錄項記錄和普通的使用者記錄的對比：

• 不同點：
  • record_type 值不同：目錄項記錄的 record_type 值是1，而普通使用者記錄的 record_type 值是0。
  • 列數不同：目錄項記錄只有主鍵值和頁的編號兩個列，而普通的使用者記錄的列是使用者自己定義的，可能包含很多列，另外還有 InnoDB 自己新增的隱藏列。
  • 瞭解：記錄頭資訊裡還有一個叫min_rec_mask的屬性，只有在儲存目錄項記錄的頁中的主鍵值最小的目錄項記錄的 min_rec_mask 值為 1，其他別的記錄的 min_rec_mask 值都是 0。
• 相同點：
  • 兩者用的是一樣的資料頁，都會為主鍵值生成Page Directory（頁目錄），從而在按照主鍵值進行查詢時可以使用二分法來加快查詢速度。

現在以查詢主鍵為 20 的記錄為例，根據某個主鍵值去查詢記錄的步驟，可以大致拆分成下邊兩步：

• 第一步：先到儲存目錄項記錄的頁，也就是頁 30 中透過二分法快速定位到對應目錄項，因為 12 < 20 < 209 ，所以定位到對應的記錄所在的頁就是頁 9。
• 第二步：再到儲存使用者記錄的頁 9 中根據二分法快速定位到主鍵值為 20 的使用者記錄。

2. 迭代兩次：多個目錄項紀錄的頁。

雖然說目錄項記錄中只儲存主鍵值和對應的頁號，比使用者記錄需要的儲存空間小多了，但是畢竟一個頁只有 16 KB 大小，能存放的目錄項記錄也是有限的，如果表中的資料太多，以至於一個資料頁不足以存放所有的目錄項記錄，該如何處理呢？

這裡假設一個儲存目錄項記錄的頁最多隻能存放 4 條目錄項記錄，所以如果此時再向上圖中插入一條主鍵值為 320 的使用者記錄，那就需要分配一個新的儲存目錄項記錄的頁：

從圖中可以看出，插入了一條主鍵值為 320 的使用者記錄之後，需要兩個新的資料頁：

• 為儲存該使用者記錄而新生成了頁 31。
• 因為原先儲存目錄項記錄的頁 30 的容量已滿（前邊假設只能儲存 4 條目錄項記錄），所以不得不需要一個新的頁 32 來存放頁 31 對應的目錄項。

現在因為儲存目錄項記錄的頁不止一個，所以如果想根據主鍵值查詢一條使用者記錄大致需要 3 個步驟，以查詢主鍵值為 20 的記錄為例：

• 第一步：確定目錄項記錄頁。現在的儲存目錄項記錄的頁有兩個，即頁 30 和頁 32，又因為頁 30 表示的目錄項的主鍵值的範圍是 [1, 320)，頁 32 表示的目錄項的主鍵值不小於 320 ，所以主鍵值為 20 的記錄對應的目錄項記錄在頁 30 中。
• 第二步：透過目錄項記錄頁確定使用者記錄真實所在的頁。在一個儲存目錄項記錄的頁中透過主鍵值定位一條目錄項記錄的方式說過了。
• 第三步：在真實儲存使用者記錄的頁中定位到具體的記錄。

3. 迭代三次：目錄項記錄頁的目錄頁。

現在問題來了，在上面這個查詢步驟的第一步中，需要定位儲存目錄項記錄的頁，但是這些頁是不連續的，如果表中的資料非常多則會產生很多儲存目錄項記錄的頁，那怎麼根據主鍵值快速定位一個儲存目錄項記錄的頁呢？答案是為這些目錄項記錄的頁再生成一個更高階的目錄，就像是一個多級目錄一樣，大目錄裡巢狀小目錄，小目錄裡才是實際的資料，所以現在各個頁的示意圖就是這樣子：

如圖，生成了一個儲存更高階目錄項的頁 33，這個頁中的兩條記錄分別代表頁 30 和頁 32，如果使用者記錄的主鍵值在 [1, 320) 之間，則到頁 30 中查詢更詳細的目錄項記錄，如果主鍵值不小於 320 的話，就到頁 32 中查詢更詳細的目錄項記錄。

隨著表中記錄的增加，這個目錄的層級會繼續增加，如果簡化一下，可以用下邊這個圖來描述它：

這個資料結構，它的名稱是B+Tree。

4. B+Tree

不論是存放普通的使用者記錄的資料頁，還是存放目錄項記錄的資料頁，現在都把它們存放到 B+Tree 這個資料結構中了，所以也稱這些資料頁為節點。從圖中可以看出，實際的普通的使用者記錄其實都存放在 B+Tree 的最底層的節點上，這些節點也稱為葉子節點，其餘用來存放目錄項記錄的節點稱為非葉子節點或者內節點，其中 B+Tree 最上邊的那個節點也成為根節點。

一個 B+Tree 的節點其實可以分成好多層，規定最下邊的那層，也就是存放普通的使用者記錄的那層為第 0 層，之後依次往上加。之前做了一個非常極端的假設：存放普通的使用者記錄的頁最多存放 3 條記錄，存放目錄項記錄的頁最多存放 4 條記錄。其實真實環境中一個頁存放的記錄數量是非常大的，假設所有存放使用者記錄的葉子節點代表的資料頁可以存放 100 條普通的使用者記錄，所有存放目錄項記錄的內節點代表的資料頁可以存放 1000 條目錄項記錄，那麼：

• 如果 B+Tree只有 1 層，也就是隻有 1 個用於存放普通的使用者記錄的節點，則最多能存放 100 條普通的使用者記錄。
• 如果 B+Tree有 2 層，則最多能存放 1000 × 100 = 10,0000 條普通的使用者記錄。
• 如果 B+Tree有 3 層，則最多能存放 1000 × 1000 × 100 = 1,0000,0000 條普通的使用者記錄。
• 如果 B+Tree有 4 層，則最多能存放 1000 × 1000 × 1000 × 100 = 1000,0000,0000 條普通的使用者記錄。這是相當多的記錄！！！

你的表裡能存放 1000,0000,0000 條記錄嗎？所以一般情況下，我們用到的 B+Tree 都不會超過 4 層，那透過主鍵值去查詢某條記錄最多隻需要在 4 個頁面內查詢（查詢 3 個目錄項頁和 1 個使用者記錄頁），又因為在每個頁面內有所謂的Page Directory（頁目錄），所以在頁面內也可以透過二分法實現快速定位記錄。

常見的索引概念

索引按照物理實現方式，索引可以分為 2 種：聚簇（聚集）索引和非聚簇（非聚集）索引。我們也把非聚集索引稱為二級索引或者輔助索引。

聚簇索引

聚簇索引並不是一種單獨的索引型別，而是一種資料儲存方式（所有的普通的使用者記錄都儲存在葉子節點），也就是所謂的索引即資料，資料即索引。

術語 "聚簇" 表示資料行和相鄰的鍵值聚簇的儲存在一起。

特點：

• 使用記錄主鍵值的大小進行記錄和頁的排序，這包括三個方面的含義：

  • 頁內的記錄是按照主鍵的大小順序排成一個單向連結串列。
  • 各個存放普通的使用者記錄的頁，也是根據頁中使用者記錄的主鍵大小順序排成一個雙向連結串列。
  • 存放目錄項記錄的頁分為不同的層次，在同一層次中的頁也是根據頁中目錄項記錄的主鍵大小順序排成一個雙向連結串列。

• B+Tree 的葉子節點儲存的是完整的使用者記錄。

  • 所謂完整的使用者記錄，就是指這個記錄中儲存了所有列的值（包括隱藏列）。

我們把具有這兩種特性的 B+Tree 稱為聚簇索引，所有完整的普通的使用者記錄都存放在這個聚簇索引的葉子節點上。這種聚簇索引並不需要在 MySQL 語句中顯式的使用 INDEX 語句去建立，InnoDB 儲存引擎會自動的建立聚簇索引。

優點：

• 資料訪問更快，因為聚簇索引將索引和資料儲存在同一個 B+Tree 中，因此從聚簇索引中獲取資料比非聚簇索引更快。
• 聚簇索引對於主鍵的排序查詢和範圍查詢速度非常快。
• 按照聚簇索引排列順序，查詢顯示一定範圍資料的時候，由於資料都是緊密相連，資料庫不用從多個資料塊中提取資料，所以節省了大量的 I/O 操作。

缺點：

• 插入速度嚴重依賴於插入順序，按照主鍵的順序插入是最快的方式，否則將會出現頁分裂，嚴重影響效能。因此，對於 InnoDB 表，一般都會定義一個自增的 ID 列為主鍵。
• 更新主鍵的代價很高，因為將會導致被更新的行移動。因此，對於 InnoDB 表，一般定義主鍵為不可更新。
• 二級索引訪問需要兩次索引查詢，第一次找到主鍵值，第二次根據主鍵值找到行資料。

限制：

• 對於 MySQL 資料庫目前只有 InnoDB 儲存引擎支援聚簇索引，MyISAM 儲存引擎不支援聚簇索引。
• 由於資料物理儲存排序方式只能有一種，所以每個 MySQL 的表只能有一個聚簇索引。一般情況下，就是該表的主鍵。
• 如果沒有定義主鍵，InnoDB 會選擇非空的唯一索引代替。如果沒有這樣的索引，InnoDB 會隱式的定義一個主鍵來作為聚簇索引。
• InnoDB 只聚集在同一個頁面中的記錄。包含相鄰鍵值的頁面可能相距甚遠。如果已經設定了主鍵為聚簇索引，必須先刪除主鍵，然後才能新增想要的聚簇索引，最後恢復設定主鍵即可。
• 為了充分利用聚簇索引的聚簇特性，所以 InnoDB 表的主鍵儘量選用有序的順序 ID，而不建議使用無序的 ID，比如 UUID，MD5，HASH，字串列等這種無法保證資料的順序增長的欄位作為主鍵。

非聚簇索引（二級索引、輔助索引）

上邊介紹的聚簇索引，只能在搜尋條件是主鍵值時才能發揮作用，因為 B+Tree 中的資料都是按照主鍵進行排序的。那如果需要以其他的列作為搜尋條件該怎麼辦呢？肯定不能是從頭到尾沿著連結串列依次遍歷使用者記錄一遍。

答案：可以多建幾顆 B+Tree，不同的 B+Tree 中的資料採用不同的排序規則。比方說用 c2 列的大小作為資料頁、頁中記錄的排序規則，再建一顆 B+Tree，效果如下圖所示：

這個 B+Tree 與上邊介紹的聚簇索引有幾處不同：

• 使用記錄 c2 列的大小進行記錄和頁的排序，這包括三個方面的含義：
  • 頁內的記錄是按照 c2 列的大小順序排成一個單向連結串列。
  • 各個存放使用者記錄的頁，也是根據頁中記錄的 c2 列的大小順序排成一個雙向連結串列。
  • 存放目錄項記錄的頁分為不同的層次，在同一層次中的頁也是根據頁中目錄項記錄的 c2 列的大小順序排成一個雙向連結串列。
• B+Tree的葉子節點儲存的並不是完整的使用者記錄，而只是c2 列 + 主鍵這兩個列的值。
• 目錄項記錄中不再是主鍵 + 頁號的搭配，而是c2 列 + 頁號的搭配。

所以，如果現在想透過 c2 列的值查詢某些記錄的話，就可以使用剛剛建好的這個 B+Tree 了。以查詢 c2 列的值為 4 的用使用者記錄為例，查詢過程如下：

• 確定目錄項記錄。
  • 根據根頁面，也就是頁 44，可以快速定位到目錄項記錄所在的頁為頁 42（因為 2 < 4 < 9）。
• 透過目錄項記錄頁確定使用者記錄真實所在的頁。
  • 在頁 42 中，可以快速定位到實際儲存使用者記錄的頁，但是由於 c2 列並沒有唯一性約束，所以 c2 列的值為 4 的記錄可能分佈在多個資料頁中，又因為 2 < 4 ≤ 4，所以確定實際儲存使用者記錄的頁在頁 34 和頁 35 中。
• 在真實儲存使用者記錄的頁中定位到具體的記錄。
  • 到頁 34 和頁 35 中定位到具體的記錄。
• 因為這個 B+Tree 的葉子節點中的記錄只儲存了 c2 和 c1（即主鍵）這兩個列，所以必須再根據主鍵值去聚簇索引中再查詢一邊完整的使用者記錄。

概念：回表。

根據這個以 c2 列大小排序的 B+Tree 只能確定要查詢記錄的主鍵值，所以如果想根據 c2 列的值查詢到完整的使用者記錄的話，仍然需要到聚簇索引中再查一遍，這個過程稱為回表。也就是根據 c2 列的值查詢一條完整的使用者記錄需要使用到 2 棵 B+Tree！

問題：為什麼還需要一次回表操作呢？直接把完整的使用者記錄放到葉子節點不行嗎？

回答：如果把完整的使用者記錄放到葉子節點確實可以不用回表，但是這樣會佔據大量儲存空間，相當於每建立一顆 B+Tree 都需要把所有的使用者記錄再複製一份，有點太浪費儲存空間。

因為這種按照非主鍵列建立的 B+Tree 需要一次回表操作才可以定位到完整的使用者記錄，所以這種 B+Tree 也被稱為二級索引（Secondary Index），或者輔助索引。由於使用的是 c2 列的大小作為 B+Tree 的排序規則，所以也稱這個 B+Tree 是為 c2 列建立的索引。

非聚簇索引的存在不影響資料在聚簇索引中的組織，所以一張表可以有多個非聚簇索引。

聚簇索引 VS 非聚簇索引

聚簇索引與非聚簇索引的原理不同，在使用上也有一些區別：

• 聚簇索引的葉子節點儲存的就是使用者的資料記錄，非聚簇索引的葉子節點儲存的是資料位置。非聚簇索引不會影響資料表的物理儲存順序。
• 一個表只能有一個聚簇索引，因為只能有一種排序儲存的方式，但可以有多個非聚簇索引，也就是多個索引目錄提供資料檢索。
• 使用聚簇索引的時候，資料的查詢效率高。聚簇索引列進行更新、插入或刪除操作，那麼整個簇將被更新。這意味著聚簇索引對於需要頻繁修改其資料的表可能不太適合。

聯合索引

我們也可以同時以多個列的大小作為排序規則，也就是同時為多個列建立索引，比如想讓 B+Tree 按照 c2 和 c3 列的大小進行排序，這個包含兩層含義：

• 先把各個記錄和頁按照 c2 列進行排序。
• 在記錄的 c2 列相同的情況下，採用 c3 列進行排序。

為 c2 和 c3 列建立的索引的示意圖如下：

如圖所示，需要注意以下幾點：

• 每條目錄項記錄都是由 c2、c3 和頁號這三個部分組成，各條記錄先按照 c2 列的值進行排序，如果記錄的 c2 列相同，則按照 c3 列的值進行排序。
• B+Tree葉子節點處的使用者記錄由 c2、c3 和主鍵 c1 列組成。

注意一點，以 c2 和 c3 列的大小為排序規則建立的 B+Tree稱為聯合索引，本質上也是一個二級索引。它的意思與為 c2 和 c3 列分別建立索引的表述是不同的，不同點如下：

• 建立聯合索引只會建立如上圖一樣的 1 棵 B+Tree。
• 為 c2 和 c3 列分別建立索引，會分別以 c2 和 c3 列的大小為排序規則建立 2 棵 B+Tree。

InnoDB 的 B+Tree 索引的注意事項

根頁面位置萬年不動

上面介紹 B+Tree 索引的時候，為了理解上的方便，先把儲存使用者記錄的葉子節點畫出來，然後接著畫儲存目錄項記錄的內節點，實際上 B+Tree 的形成過程是這樣的：

• 每當為每個表建立一個 B+Tree 索引（聚簇索引不是人為建立的，預設就有）的時候，都會為這個索引建立一個根節點頁面。最開始表中沒有資料的時候，每個 B+Tree 索引對應的根節點中既沒有使用者記錄，也沒有目錄項記錄。
• 隨後向表中插入使用者記錄時，先把使用者記錄儲存到這個根節點中。
• 當根節點中的可用空間用完並繼續插入記錄時，此時會將根節點中的所有記錄複製到一個新分配的頁，比如頁 a 中，然後對這個新頁進行頁分裂操作，得到另一個新頁，比如頁 b。這時，新插入的記錄根據鍵值（也就是聚簇索引中的主鍵值，二級索引中對應的索引列的值）的大小就會被分配到頁 a 或者頁 b 中，而根節點便升級為儲存目錄項記錄的頁。

這個過程特別注意的是：一個 B+Tree 索引的根節點自誕生之日起，便不會再移動。這樣只要對某個表建立一個索引，那麼它的根節點的頁號便會被記錄到某個地方，然後凡是 InnoDB 儲存引擎需要用到這個索引的時候，都會從那個固定的地方取出根節點的頁號，從而來訪問這個索引。

內節點中目錄項記錄的唯一性

我們知道 B+Tree 索引的內節點中目錄項記錄的內容是索引列 + 頁號的搭配，但是這個搭配對於二級索引來說有點不嚴謹。以 index_demo 表為例，假設這個表中的資料是這樣的：

c1	c2	c3
1	1	'u'
3	1	'd'
5	1	'y'
7	1	'a'

如果二級索引中目錄項記錄的內容只是索引列 + 頁號的搭配的話，那麼為 c2 列建立索引後的 B+Tree 應該長這樣：

如果想新插入一行記錄，其中 c1、c2 和 c3 的值分別是：9、1、'c'，那麼在修改這個為 c2 列建立的二級索引對應的 B+Tree 時便碰到了大問題：由於頁 3 中儲存的目錄項記錄是由 c2 列 + 頁號的值構成的，頁 3 中的兩條目錄項記錄對應的 c2 列的值都是 1，而新插入的這條記錄的 c2 列的值也是 1，那這條新插入的記錄到底應該放到頁 4 中，還是應該放到頁 5 中呢？

為了讓新插入的記錄能找到自己在哪個頁裡，需要保證在 B+Tree 的同一層內節點的目錄項記錄除頁號這個欄位以外是唯一的。所以對於二級索引的內節點的目錄項記錄的內容，實際上是由三個部分構成的：

• 索引列的值。
• 主鍵值。
• 頁號。

也就是把主鍵值也新增到二級索引內節點中的目錄項記錄了，這樣就能保證 B+Tree 每一層節點中各條目錄項記錄除頁號這個欄位外是唯一的，所以為 c2 列建立二級索引後的示意圖實際上應該是這樣子的：

這樣再插入 (9, 1, 'c') 時，由於頁 3 中儲存的目錄項記錄是由 c2 列 + 主鍵 + 頁號的值構成的，可以先把新紀錄的 c2 列的值和頁 3 中各目錄項記錄的 c2 列的值作比較，如果 c2 列的值相同的話，可以接著比較主鍵值，因為 B+Tree 同一層中不同目錄項記錄的 c2 列 + 主鍵的值肯定是不一樣的，所以最後肯定能定位唯一的一條目錄項記錄。在本例中，最後確定新紀錄應該被插入到頁 5 中。

一個頁面最少儲存兩條記錄

一個 B+Tree 只需要很少的層級就可以輕鬆儲存數億條記錄，查詢效率也高。這是因為 B+Tree 本質上是一個大的多層級目錄，沒經過一個目錄時都會過濾掉許多無效的子目錄，直到最後訪問到儲存真實資料的目錄。那如果一個大的目錄中只存放一個子目錄是什麼效果呢？那就是目錄層級變的非常多，而且最後的那個存放真實資料的目錄中只能存放一條記錄。因此，InnoDB 的一個資料頁至少應儲存兩條記錄。

MyISAM 中的索引方案

使用 B+Tree 索引的儲存引擎如表所示：

索引/儲存引擎	MyISAM	InnoDB	Memory
B-Tree 索引	支援	支援	支援

上表 MySQL 官方文件提到的 B-Tree，實際上就是指 B+Tree。

實際上，即使多個儲存引擎都支援同一種型別的索引，但它們的實現原理也是不同的。InnoDB 和 MyISAM 預設的索引是 B-Tree 索引，而 Memory 預設的索引是 Hash 索引。

MyISAM 儲存引擎使用 B+Tree 作為索引結構，葉子節點的 data 域存放的是資料記錄的地址。

MyISAM 索引的原理

在 InnoDB 中索引即資料，也就是聚簇索引的那棵 B+Tree的葉子節點中已經把所有完整的使用者記錄都包含了，而 MyISAM 的索引方案雖然也使用樹形結構，但是卻將索引和資料分開儲存。下圖是 MyISAM 索引的原理圖：

• 將表中的記錄按照記錄的插入順序單獨儲存到一個檔案中，稱為資料檔案。這個檔案並不劃分為若干個資料頁，有多少記錄就往這個檔案中塞多少記錄。由於在插入資料時候並沒有刻意按照主鍵大小排序，所以不能在這些資料上使用二分法進行查詢。
• 使用 MyISAM 儲存引擎的表會把索引資訊另外儲存到一個稱為索引檔案的檔案中，MyISAM 會單獨為表的主鍵建立一個索引，只不過在索引的葉子節點中儲存的不是完整的使用者記錄，而是主鍵值 + 資料記錄地址的組合。

這裡設表一共有三列，假設以 Col1 為主鍵，上圖是一個 MyISAM 表的主索引（Primary Key）示意。可以看出 MyISAM 的索引檔案僅僅儲存資料記錄的地址。在 MyISAM 中，主鍵索引和二級索引（Secondary Key）在結構上沒有任何區別，只有主鍵索引要求 key 是唯一的，而二級索引的 key 可以重複。如果在 Col2 上建立一個二級索引，則此索引的結構如下圖：

同樣也是一棵 B+Tree，data 域儲存資料記錄的地址。因此，MyISAM 中索引檢索的演算法為：首先按照 B+Tree 搜尋演算法搜尋索引，如果指定的 Key 存在，則取出其 data 域的值，然後以 data 域的值為地址，讀取相應資料記錄。

MyISAM VS InnoDB

MyISAM 的索引方式都是 "非聚簇" 的，與 InnoDB 包含 1 個聚簇索引是不同的。小結兩種引擎中索引的區別：

• 在 InnoDB 儲存引擎中，我們只需要根據主鍵值對聚簇索引進行一次查詢就能找到對應的記錄，而在 MyISAM 中卻需要進行一次回表操作，意味著 MyISAM 中建立的索引相當於全部都是二級索引。
• InnoDB 的資料檔案本身就是索引檔案，而 MyISAM 索引檔案和資料檔案是分離的，索引檔案僅儲存資料記錄的地址。
• InnoDB 的非聚簇索引 data 域儲存相應記錄主鍵的值，而 MyISAM 索引記錄的是地址。換句話說，InnoDB 的所有非聚簇索引都引用主鍵作為 data 域。
• MyISAM 的回表操作是十分快速的，因為是拿著地址偏移量直接到檔案中取資料的，反觀 InnoDB 是透過獲取主鍵之後再去聚簇索引裡找記錄，雖然說也不慢，但還是比不上直接用地址去訪問。
• InnoDB 要求表必須有主鍵（MyISAM 可以沒有）。如果沒有顯式指定，則 MySQL 系統會自動選擇一個可以非空且唯一標識資料記錄的列作為主鍵。如果不存在這種列，則 MySQL 自動為 InnoDB 表生成一個隱含欄位作為主鍵，這個欄位長度為 6 個位元組，型別為長整型。

瞭解不同儲存引擎的索引實現方式，對於正確使用和最佳化索引都非常有幫助。比如：

• 示例 1：知道了 InnoDB 的索引實現方式後，就很容易明白為什麼不建議使用過長的欄位作為主鍵，因為所有二級索引都引用主鍵索引，過長的主鍵索引會令二級索引變的過大。
• 示例 2：用非單調的欄位作為主鍵在 InnoDB 中不是個好主意，因為 InnoDB 資料檔案本身是一顆 B+Tree，非單調的主鍵會造成在插入新紀錄時，資料檔案為了維持 B+Tree 的特性而頻繁的分裂調整，十分低效，而使用自增欄位作為主鍵則是一個很好的選擇。

索引的代價

索引是個好東西，可不能亂建，因為它在空間和時間上都會有消耗：

• 空間上的代價：每建立一個索引都要為它建立一棵 B+Tree，每一棵 B+Tree 的每一個節點都是一個資料頁，一個頁預設會佔用 16 KB 的儲存空間，一棵很大的 B+Tree 由許多資料頁組成，那就是很大的一片儲存空間。
• 時間上的代價：每次對錶中的資料進行 增、刪、改 操作時，都需要去修改各個 B+Tree 索引。而且 B+Tree 每層節點都是按照索引列的值從小到大的順序排序組成雙向連結串列。不論是葉子節點中的記錄，還是內節點中的記錄（也就是不論是使用者記錄還是目錄項記錄），都是按照索引列的值從小到大的順序而形成了一個單向連結串列。而增、刪、改操作可能會對節點和記錄的排序造成破壞，所以儲存引擎需要額外的時間進行一些記錄移位，頁面分裂、頁面回收等操作來維護好節點和記錄的排序。如果建了許多索引，每個索引對應的 B+Tree 都要進行相關的維護操作，會給效能拖後腿。

一個表上索引建的越多，就會佔用越多的儲存空間，在增刪改記錄的時候效能就越差，為了能建立又好又少的索引，需要了解這些索引在什麼條件下起作用。

MySQL 資料結構選擇的合理性

從 MySQL 的角度講，不得不考慮一個現實的問題就是磁碟 I/O，如果能讓索引的資料結構儘量減少磁碟的 I/O 操作，所消耗的時間也就越小。可以說，磁碟的 I/O 操作次數對索引的使用效率至關重要。

查詢都是索引操作，一般來說索引非常大，尤其是關係型資料庫，當資料量較大的時候，索引的大小有可能幾個 G 甚至更大，為了減少索引在記憶體的佔用，資料庫索引是儲存在外部磁碟上的。當利用索引查詢的時候，不可能把整個索引全部載入到記憶體，只能逐一載入，那麼 MySQL 衡量查詢效率的標準就是磁碟 I/O 次數。

全表遍歷

即從表的第一條資料，逐一檢視，直到找到目標記錄。因為要載入所有頁到記憶體，進行遍歷查詢，非常耗時，效能極差。

Hash 結構

Hash 本身是一個函式，又被稱為雜湊函式，它可以幫助我們大幅提升檢索資料的效率。

Hash 演算法是透過某種確定性的演算法（比如 MD5、SHA1、SHA2、SHA3）將輸入變為輸出，相同的輸入永遠可以得到相同的輸出。假設輸入內容有微小偏差，在輸出中通常會有不同的結果。

舉例：如果想要驗證兩個檔案是否相同，那麼不需要把兩份檔案直接拿來比對，只需要讓對方把 Hash 函式計算得到的結果告知即可，然後在本地同樣對檔案進行 Hash 函式的運算，最後透過比較這兩個 Hash 函式的結果是否相同，就可以知道這兩個檔案是否相同。

加快查詢速度的資料結構，常見的有兩類：

• 樹：例如平衡二叉搜尋樹，查詢/插入/修改/刪除的平均時間複雜度都是 O(log2N)。
• 雜湊：例如 HashMap，查詢/插入/修改/刪除的平均時間複雜度都是 O(1)。

採用 Hash 進行檢索的效率非常高，基本上一次檢索就可以找到資料，而 B+Tree 需要自頂向下依次查詢，多次訪問節點才能找到資料，中間需要多次 I/O 操作，從效率來說 Hash 比 B+Tree 更快。

在雜湊的方式下，一個元素 k 處於 h(k) 中，即利用雜湊函式 h，根據關鍵字 k 計算出槽的位置，函式 h 將關鍵字域對映到雜湊表 T[0...m-1] 的槽位上。

上圖中雜湊函式 h 有可能將兩個不同的關鍵字對映到相同的位置，這叫做碰撞，在資料庫中一般採用連結法來解決。在連結法中，將雜湊到同一槽位的元素放在一個連結串列中，如下圖所示：

體會陣列和 Hash 表的查詢方面的效率區別：

// 演算法複雜度為O(n)
public static void main(String[] args) {
    int[] arr = new int[100000];
    for (int i = 0; i < arr.length; i++) {
        arr[i] = i + 1;
    }
    long start = System.currentTimeMillis();
    for (int j = 1; j <= 100000; j++) {
        for (int k : arr) {
            if (j == k) {
                break;
            }
        }
    }
    long end = System.currentTimeMillis();
    System.out.println("time： " + (end - start)); // time： 970
}

public static void main(String[] args) {
    HashSet<Integer> set = new HashSet<>(100000);
    for (int i = 0; i < 100000; i++) {
        set.add(i + 1);
    }
    long start = System.currentTimeMillis();
    for (int j = 1; j <= 100000; j++) {
        boolean contains = set.contains(j);
    }
    long end = System.currentTimeMillis();
    System.out.println("time： " + (end - start)); //time： 5
}

既然 Hash 結構效率高，那為什麼索引結構要設計成樹型呢？

• 原因 1：Hash 索引僅能滿足（=）、（<>）和 IN 查詢，如果進行範圍查詢，雜湊型的索引，時間複雜度會退化為 O(n)，而樹型的 "有序" 特性，依然能夠保持 O(log2N) 的高效率。
• 原因 2：Hash 索引還有一個缺陷，資料的儲存是沒有順序的，在 ORDER BY 的情況下，使用 Hash 索引還需要對資料重新排序。
• 原因 3：對於聯合索引的情況，Hash 值是將聯合索引鍵合併後一起來計算的，無法對單獨的一個鍵值或者幾個索引鍵進行查詢。
• 原因 4：對於等值查詢來說，通常 Hash 索引的效率更高，不過也存在一種情況，就是索引列的重複值如果很多，效率就會降低。這是因為遇到 Hash 衝突時，需要遍歷桶中的行指標來進行比較，進而找到查詢的關鍵字，非常耗時。所以，Hash 索引通常不會用到重複值多的列上，比如性別、年齡等。

Hash 索引適用儲存引擎如表所示：

索引/儲存引擎	MyISAM	InnoDB	Memory
HASH 索引	不支援	不支援	支援

Hash 索引的適用性：

Hash 索引存在著很多限制，相比之下在資料庫中 B+Tree 索引的使用面會更廣，不過也有一些場景採用 Hash 索引效率更高，比如在鍵值型（Key-value）資料庫中，Redis 儲存的核心就是 Hash 表。

MySQL 中的 Memory 儲存引擎支援 Hash 儲存，如果需要用到查詢的臨時表時，就可以選擇 Memory 儲存引擎，把某個欄位設定為 Hash 索引，比如字串型別的欄位，進行 Hash 計算之後長度可以縮短到幾個位元組。當欄位的重複度低，而且經常需要進行等值查詢的時候，採用 Hash 索引是個不錯的選擇。

另外，雖然 InnoDB 本身不支援 Hash 索引，但是提供自適應 Hash 索引（Adaptive Hash Index）。什麼情況下才會使用自適應 Hash 索引呢？如果某個資料經常被訪問，當滿足一定條件的時候，MySQL 就會把這個資料頁的地址存放到 Hash 表中，這樣下次查詢的時候，就可以直接找到這個頁面的所在位置，這樣 B+Tree 也具備了 Hash 索引的優點。

採用自適應 Hash 索引的目的是方便根據 SQL 的查詢條件加速定位到葉子節點，特別是當 B+Tree 比較深的時候，透過自適應 Hash 索引可以明顯提高資料的檢索效率。

可以透過innodb_adaptive_hash_index變數來檢視是否開啟了自適應 Hash，比如：

mysql> SHOW VARIABLES LIKE '%adaptive_hash_index%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index       | ON    |
| innodb_adaptive_hash_index_parts | 8     |
+----------------------------------+-------+
2 rows in set (0.00 sec)

二叉搜尋樹

如果利用二叉樹作為索引結構，那麼磁碟的IO次數和索引樹的高度是相關的。

二叉搜尋樹的特點：

• 一個節點只能有兩個子節點，也就是一個節點度不能超過 2。
• 左子節點 < 本節點 ≤ 右子節點。

查詢規則：

先看下最基礎的二叉搜尋樹（Binary Search Tree），搜尋某個節點和插入節點的規則一樣，假設搜尋插入的數值為 key：

• 如果 key 大於根節點，則在右子樹中進行查詢；
• 如果 key 小於根節點，則在左子樹中進行查詢；
• 如果 key 等於根節點，也就是找到了這個節點，直接返回根節點即可。

舉個例子，我們對數列 (34, 22, 89, 5, 23, 77, 91) 創造出來的二分搜尋樹，如下圖所示：

但是存在特殊的情況，會導致二叉樹的深度非常大。比如數列 (5, 22, 23, 34, 77, 89, 91)，創造出來的二分搜尋樹，就如下圖所示：

上面這棵樹也屬於二分搜尋樹，但是效能上已經退化成了一條連結串列，查詢資料的時間複雜度變成了 O(n)。同時，可以看出，第一棵樹的深度是 3，也就是說最多隻需要 3 次比較，就可以找到節點，而第二棵樹的深度是 7，最多需要 7 次比較才能找到節點。

為了提高查詢效率，就需要減少磁碟 I/O 次數。為了減少磁碟 I/O 的次數，就需要儘量降低樹的高度，需要把原來 "瘦高" 的樹結構變的 "矮胖"，樹的每層的分叉越多越好。

AVL 樹

為了解決上面二叉搜尋樹退化成連結串列的問題，人們提出了平衡二叉樹（Balanced Binary Tree），又稱為AVL 樹（有別於 AVL 演算法），它在二叉搜尋樹的基礎上增加了約束，具有以下性質：它是一棵空樹，或者它的左右兩個子樹的高度差的絕對值不超過 1，並且左右兩個子樹都是一顆平衡二叉樹。

Tips：

常見的平衡二叉樹有很多種，包括了平衡二叉搜尋樹、紅黑樹、數堆、伸展樹。平衡二叉搜尋樹是最早提出來的自平衡二叉搜尋樹，當提到平衡二叉樹時，一般指的就是平衡二叉搜尋樹。事實上，上面的第一棵樹就屬於平衡二叉搜尋樹，搜尋時間複雜度是 O(log2n)。

資料查詢的時間主要取決於磁碟 I/O 的次數，如果採用二叉樹的形式，即使透過平衡二叉搜尋樹進行了改進，樹的深度也是 O(log2n)，當 n 比較大時，深度也是比較高的。比如下圖的情況：

每訪問一次節點就需要程序一次磁碟 I/0 操作，對於上面的樹來說，需要進行 5 次 I/O 操作。雖然平衡二叉樹的效率高，但是樹的深度也同樣高，這就意味著磁碟 I/O 操作次數多，會影響整體資料查詢的效率。

針對同樣的資料，如果把二叉樹改成M 叉樹（M > 2）呢？當 M = 3 時，同樣的 31 個節點可以由下面的三叉樹來進行儲存：

可以看到，此時樹的高度降低了，當資料量 N 大的時候，以及樹的分叉數 M 大的時候，M 叉樹的高度會遠小於二叉樹的高度（M > 2）。這樣，就把樹從 "瘦高" 變為 "矮胖"。

B-Tree

B-Tree 的英文是 Balance Tree，也就是多路平衡查詢樹，簡寫為 B-Tree（橫槓表示這兩個單詞相連，不是減號），它的高度遠小於平衡二叉樹的高度。

B-Tree 的結構如下圖所示：

詳細說明：

B-Tree 作為多路平衡查詢樹，它的每一個節點最多可以包括 M 個子節點，M 稱為 B-Tree 的階 （上圖 B-Tree 的階是 3，左邊小，右邊大，中間的是二者之間）。B-Tree 的每個磁碟塊中包括了關鍵字和子節點的指標。如果一個磁碟塊中包括了 x 個關鍵字，那麼指標數就是 x + 1。對於一個 100 階的 B-Tree 來說，如果有 3 層的話，最多可以儲存約 100 萬的索引資料。對於大量的索引資料來說，採用 B-Tree 的結構是非常適合的，因為樹的高度要遠小於二叉樹的高度。

一個 M 階的 B-Tree（M > 2）有以下的特性：

• 根節點的兒子數的範圍是 [2, M]。
• 每個中間節點包含 k - 1 個關鍵字和 k 個孩子，孩子的數量 = 關鍵字的數量 + 1，k 的取值範圍為 [ceil(M/2), M]。
• 葉子節點包括 k - 1 個關鍵字（葉子節點沒有孩子），k 的取值範圍為 [ceil(M/2), M]。
• 假設中間節點節點的關鍵字為：Key[1], Key[2], …, Key[k-1]，且關鍵字按照升序排序，即 Key[i] < Key[i+1]。此時 k - 1 個關鍵字相當於劃分了 k 個範圍，也就是對應著 k 個指標，即為：P[1], P[2], …, P[k]，其中 P[1] 指向關鍵字小於 Key[1] 的子樹，P[i] 指向關鍵字屬於 (Key[i-1], Key[i]) 的子樹，P[k] 指向關鍵字大於 Key[k-1] 的子樹。
• 所有葉子節點位於同一層。

上面那張圖所表示的 B-Tree 就是一棵 3 階的 B 樹。可以看下磁碟塊 2，裡面的關鍵字為 (8, 12)，它有 3 個孩子 (3, 5)，(9, 10) 和 (13, 15)，能看到 (3, 5) 小於 8，(9, 10) 在 8 和 12 之間，而 (13, 15) 大於 12，剛好符合剛才給出的特徵。

然後來看下如何用 B-Tree 進行查詢。假設想要查詢的關鍵字是 9，那麼步驟可以分為以下幾步：

• 與根節點的關鍵字 (17, 35) 進行比較，9 小於 17 那麼得到指標 P1；
• 按照指標 P1 找到磁碟塊 2，關鍵字為 (8, 12)，因為 9 在 8 和 12 之間，所以得到指標 P2；
• 按照指標 P2 找到磁碟塊 6，關鍵字為 (9, 10)，然後找到了關鍵字 9。

可以看出來在 B-Tree 的搜尋過程中，比較的次數並不少，但如果把資料讀取出來然後在記憶體中進行比較，這個時間就是可以忽略不計的。而讀取磁碟塊本身需要進行 I/O 操作，消耗的時間比在記憶體中進行比較所需要的時間要多，是資料查詢用時的重要因素。B-Tree 相比於平衡二叉樹來說磁碟 I/O 操作要少，在資料查詢中比平衡二叉樹效率要高。所以只要樹的高度足夠低，I/O 次數足夠少，就可以提高查詢效能。

小結：

• B-Tree 在插入和刪除節點的時候，如果導致樹不平衡，就透過自動調整節點的位置來保持樹的自平衡。
• 關鍵字集合分佈在整棵樹中，即葉子節點和非葉子節點都存放資料，搜尋有可能在非葉子節點結束。
• 其搜尋效能等價於在關鍵字全集內做一次二分查詢。

B+Tree

B+Tree 也是一種多路搜尋樹，基於 B-Tree 做了改進，主流的 DBMS 都支援 B+Tree 的索引方式，比如 MySQL。相比於 B-Tree，B+Tree 更適合檔案索引系統。

B+Tree 和 B-Tree 的差異：

• B+Tree 有 k 個孩子的節點就有 k 個關鍵字，也就是孩子數量 = 關鍵字數，而 B-Tree 中，孩子數量 = 關鍵字數 + 1。
• B+Tree 非葉子節點的關鍵字也會同時存在在子節點中，並且是在子節點中所有關鍵字的最大（或最小）。
• B+Tree 非葉子節點僅用於索引，不儲存資料記錄，跟記錄有關的資訊都放在葉子節點中。而 B-Tree 中，非葉子節點既儲存索引，也儲存資料記錄。
• B+Tree 所有關鍵字都在葉子節點出現，葉子節點構成一個有序連結串列，而且葉子節點本身按照關鍵字的大小從小到大順序連結。

下圖就是一棵 B+Tree，階數為 3，根節點中的關鍵字 1、18、 35 分別是子節點 (1, 8, 14) ，(18, 24, 31) 和 (35, 41, 53) 中的最小值，每一層父節點的關鍵字都會出現在下一層的子節點的關鍵字中，因此在葉子節點中包括了所有的關鍵字資訊，並且每一個葉子節點都有一個指向下一個節點的指標，這樣就形成了一個連結串列。

比如，想要查詢關鍵字 16，B+Tree 會自頂向下逐層進行查詢：

• 16 與根節點的關鍵字 (1, 18, 35) 進行比較，16 在 1 和 18 之間，得到指標 P1，指向磁碟塊 2；
• 找到磁碟塊 2，16 與關鍵字 (1, 8, 14) 進行比較，16 大於 14，得到指標 P2，指向磁碟塊 7；
• 找到磁碟塊 7，關鍵字為 (14, 16, 17)，找到關鍵字 16，繼而找到對應的資料。

整個過程一共進行了 3 次 I/O 操作，看起來 B+Tree 和 B-Tree 的查詢過程差不多，但是 B+Tree 和 B-Tree 有個根本的差異在於，B+Tree 的中間節點不直接儲存資料，這樣做的好處是：

• 首先，B+Tree 查詢效率更穩定。因為 B+Tree 每次只有訪問到葉子節點才能找到對應的資料，而在 B-Tree 中，非葉子節點也會儲存資料，會造成查詢效率不穩定的情況，有時候訪問到了非葉子節點就可以找到關鍵字，有時候需要訪問到葉子節點才能找到關鍵字。
• 其次，B+Tree 的查詢效率更高。這是因為通常 B+Tree 比 B-Tree 更矮胖（階數更大，深度更低，因為 B+Tree 非葉子節點不儲存資料，佔據的儲存空間比 B-Tree 小，同樣大小的資料頁，B+Tree 能夠儲存更多的目錄項記錄，整棵樹也就更矮胖），查詢所需要的磁碟 I/O 次數也會更少。

不僅在對單個關鍵字的查詢上，在查詢範圍上，B+Tree 的效率也比 B-Tree 高。這是因為所有關鍵字都出現在 B+Tree 的葉子節點中，葉子節點之間會有指標，資料又是遞增的，這使得範圍查詢可以透過指標連線查詢。而在 B-Tree 中，則需要透過中序遍歷才能完成查詢範圍的查詢，效率要低很多。

B-Tree 和 B+Tree 都可以作為索引的資料結構，在 MySQL 中採用的是 B+Tree。

另外，B-Tree 和 B+Tree 各有自己的應用場景，不能說 B+Tree 完全比 B-Tree 好，反之亦然。

思考題：為了減少 I/O，索引樹會一次性載入嗎？

• 資料庫索引是儲存在磁碟上的，如果資料量很大，必然導致索引的大小也很大，甚至超過幾個 G。
• 當利用索引查詢的時候，是不可能將全部幾個 G 的索引都載入進記憶體的，能做的只能是：逐一載入每一個磁碟頁，因為磁碟頁對應著索引樹的節點。

思考題：B+Tree 的儲存能力如何？為何說一般查詢行記錄，最多隻需 1 ~ 3 次磁碟 I/O？

• InnoDB 儲存引擎中頁的大小預設為 16KB。一般表的主鍵型別為 INT，佔用 4 個位元組，或者 BIGINT，佔用 8 個位元組，指標型別也一般為 4 個或者 8 個位元組，也就是說一個頁（B+Tree 中的一個節點）中大概儲存 16KB / (8 B + 8 B) = 1024 個鍵值。也就是說一個深度為 3 的 B+Tree 索引，可以維護 1024 * 1024 * 1024，即超過 10 億條記錄。（此處為估算，並假定一個資料頁頁儲存 1024 條行記錄資料）
• 實際情況中，每個節點可能不會填滿，因此在資料庫中，B+Tree 的高度一般都在 2 ~ 4 層。MySQL 的 InnoDB 儲存引擎在設計時，是將根節點常駐記憶體的，也就是說查詢某一鍵值的行記錄，最多隻需要 1 ~ 3 次磁碟 I/O 操作。

思考題：為什麼說 B+Tree 比 B-Tree 更適合實際應用中作業系統的檔案索引和資料庫索引？

• B+Tree 的磁碟讀寫代價更低。
  • B+Tree 的內部節點並沒有指向關鍵字具體資訊的指標，因此其內部節點相對 B-Tree 更小。如果把所有同一內部節點的關鍵字存放在同一磁碟塊中，那麼磁碟塊所能容納的關鍵字數量也越多，一次性讀入記憶體中的需要查詢的關鍵字也就越多，相對來說 I/O 讀寫次數也就降低了。
• B+Tree 的查詢效率更加穩定。
  • B+Tree 的非葉子節點並不是最終指向檔案內容的節點，而只是葉子節點中關鍵字的索引。所以任何關鍵字的查詢，必須走一條從根節點到葉子節點的路，所有關鍵字查詢的路徑長度相同，因而每一個資料的查詢效率相當。

思考題：Hash 索引與 B+Tree 索引的區別。

• Hash 索引不能進行範圍查詢，而 B+Tree 可以。這是因為 Hash 索引指向的資料是無序的，而 B+Tree 的葉子節點是個有序的連結串列。
• Hash 索引不支援聯合索引的最左側原則，即聯合索引的部分索引無法使用，而 B+Tree 可以。對於聯合索引來說，Hash 索引在計算 Hash 值得時候，是將索引鍵合併後再一起計算 Hash 值，所以不會針對每個索引單獨計算 Hash 值。因此，如果用到聯合索引得一個或幾個索引時，聯合索引無法被使用。
• Hash 索引不支援 ORDER BY 排序，而 B+Tree 可以。因為 Hash 索引指向得資料是無序的，因此無法起到排序最佳化的作用，而 B+Tree 的資料是有序的，可以起到對該欄位 ORDER BY 排序最佳化的作用。
• Hash 索引不支援模糊查詢，而 B+Tree 可以。理由同上，當 B+Tree 使用 LIKE 進行模糊查詢的時候，LIKE 後面後模糊查詢（比如 % 結尾）可以起到最佳化左右。
• InnoDB 儲存引擎不支援雜湊索引。

思考題：Hash 索引與 B+Tree 索引是在建索引的時候手動指定的嗎？

MySQL 8.0 文件：https://dev.mysql.com/doc/refman/8.0/en/create-index.html

• 如果使用的是 MySQL，針對 InnoDB 和 MyISAM 儲存引擎，預設會採用 B+Tree，無法使用 Hash 索引。InnoDB 提供的自適應 Hash 不需要手動指定，預設開啟。如果是 Memory/Heap 和 NDB 儲存引擎，可以選擇使用 Hash 索引。

R-Tree

R-Tree 在 MySQL 很少使用，僅支援 geometry 資料型別 ，支援該型別的儲存引擎只有 MyISAM、BDB、InnoDB、NDB、Archive 幾種。舉個 R-Tree 在現實領域中能夠解決的例子：查詢 20 英里以內所有的餐廳。如果沒有 R-Tree 你會怎麼解決？一般情況下我們會把餐廳的座標 (x, y) 分為兩個欄位存放在資料庫中，一個欄位記錄經度，另一個欄位記錄緯度。這樣的話我們就需要遍歷所有的餐廳獲取其位置資訊，然後計算是否滿足要求。如果一個地區有 100 家餐廳的話，我們就要進行 100 次位置計算操作了，如果應用到谷歌、百度地圖這種超大資料庫中，這種方法便必定不可行了。R-Tree 就很好的解決了這種高維空間搜尋問題。它把 B-Tree 的思想很好的擴充套件到了多維空間，採用了 B-Tree 分割空間的思想，並在新增、刪除操作時採用合併、分解結點的方法，保證樹的平衡性。因此，R-Tree 就是一棵用來儲存高維資料的平衡樹。相對於 B-Tree，R-Tree 的優勢在於範圍查詢。

索引/儲存引擎	MyISAM	InnoDB	Memory
R-Tree	支援	支援	不支援

小結

使用索引可以幫助我們從海量的資料中快速定位想要查詢的資料，不過索引也存在一些不足，比如佔用儲存空間、降低資料庫寫操作的效能等，如果有多個索引，還會增加索引選擇的時間。當我們使用索引時，需要平衡索引的利（提升查詢效率）和弊（維護索引所需的代價）。

在實際工作中，我們還需要基於需求和資料本身的分佈情況，來確定是否需要使用索引，儘管索引不是萬能的，但資料量大的時候不使用索引是不可想象的，畢竟索引的本質，是幫助我們提升資料檢索的效率。

附錄：演算法的時間複雜度

同一問題可用不同演算法解決，而一個演算法的質量優劣將影響到演算法乃至程式的效率。演算法分析的目的在於選擇合適演算法和改進演算法。

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql.md