跳槽必看MySQL索引：B+樹原理揭秘與索引優缺點分析

發表於2024-03-01
MySql索引
金三銀四跳槽季，不知道你準備的怎麼樣了？

前段時間我分享了兩篇文章，粉絲股東們紛紛表示有用，有啟發：，之前沒看的話可以先看看：

程式設計師金三銀四跳槽指南：時間線&經典面試16問

這才開工沒幾天就收到喜報了，簡歷改了是真有用！

今天再給大家分享一下資料庫索引的詳解文章，這基本是必考的知識點。

一、索引介紹

1、索引定義

索引是儲存引擎中，用於快速找到記錄的一種資料結構。索引能夠幫助儲存引擎快速獲取資料，形象的說就是索引是資料的目錄。

所謂的儲存引擎，通俗的來說就是如何儲存資料、如何為儲存的資料建立索引和如何更新、查詢資料等 技術的實現方法。

MySQL儲存引擎有MyISAM、InnoDB、Memory，其中InnoDB是在MySQL 5.5之後成為預設的存 儲引擎。

在實際場景中，索引對於良好的效能起到非常關鍵的作用。或許在資料量小且負載較低時，索引的不恰當使用可能對效能的影響可能不會太明顯，但是當表中的資料量越來越大的時候，索引對效能的影響就愈發重要，不恰當的索引會讓效能急劇的下降。

2、索引的查詢方式

在MySQL的InnoDB儲存引擎中

若沒有索引的情況下進行資料查詢

a) 在一個資料頁中查詢

當表中的記錄比較少時，所有記錄可以存放到一個資料頁中。當查詢記錄時，根據搜尋條件的不同查詢分為兩種情況：

  • 以主鍵為搜尋條件：在一個資料頁內的記錄會根據主鍵值的大小從小到大的順序組成一個單向連結串列。每個資料頁都會為儲存在它裡面的記錄生成一個頁目錄。透過主鍵查詢某條記錄可以在頁目錄中使用二分法快速定位到對應的槽，然後再遍歷該槽對應分組的記錄，即可快速找到指定的記錄。
  • 以其他列作為搜尋條件：對於非主鍵列的查詢，由於沒有為非主鍵列建立對應的目錄頁，即未建立索引。無法用二分法快速定位相應的槽，只能從Infimum記錄開始依次遍歷單向連結串列中的每條記錄，然後對比每條記錄是否符合搜尋條件，即全表掃描，因此效率非常低。

b) 在多個資料頁中查詢

在很多情況下，表中存放的記錄是非常多的，需要查詢到的資料可能分佈在多個資料頁中，在多個頁中查詢記錄可以分為兩個步驟：

  • 定位到記錄所在的頁
  • 從所在的頁內查詢相應的記錄

在沒有索引的情況下，無論是根據主鍵列還是其他列的值查詢，都不能快速定位到記錄所在的頁，因此只能從第一頁沿著雙向連結串列一直往下找，因而非常耗時。

若存在索引的情況下進行資料查詢

在建立索引的情況下，每個資料頁都會為儲存在它裡面的記錄生成一個目錄項，在透過索引查詢某條記錄時可以在頁目錄中使用二分法快速定位到對應的槽，然後再遍歷該槽對應分組中的記錄，快速找到指定的記錄，確定記錄後，即可向下尋找當前記錄對應的下一個頁節點，直到尋找到存在目標記錄的葉子結點。

二、索引分類

1、按資料結構分類

Hash索引

雜湊表是一種以鍵-值（key-value）儲存資料的結構，輸入待查詢的鍵，即key，就可以找到其對應的值，即value。

雜湊的思路很簡單，把值放在陣列裡，用一個雜湊函式把key換算成一個確定的位置，然後把value放在陣列的這個位置。

不可避免地，多個key值經過雜湊函式的換算，會出現同一個值的情況，即雜湊碰撞，處理這種情況的一種方法是，拉出一個連結串列。

但是，在雜湊表中，資料的儲存不是按順序存放的，所以雜湊索引做區間查詢的速度是很慢的。

所以，雜湊表這種結構適用於只有等值查詢的場景，比如Memcached及其他一些NoSQL引擎。

有序陣列

有序陣列在等值查詢和範圍查詢場景中的效能就都非常優秀。

在查詢資料方面，有序陣列可以透過二分查詢的方式快速找到，時間複雜度是 O(log(N))。

同樣，有序陣列的索引結構支援範圍查詢，透過二分法找到需要查詢的範圍的首元素，然後向後遍歷，直到找到第一個不滿足條件的元素為止。

如果僅僅看查詢效率，有序陣列就是最好的資料結構了。但是，在需要更新資料的時候就麻煩了，往中間插入一個記錄就必須得挪動後面所有的記錄，成本太高。

所以，有序陣列索引一般適用於靜態儲存引擎。

B+樹（InnoDB索引結構）

在MySQL 5.5之後，InnoDB成為預設的MySQL儲存引擎，B+Tree索引型別也是MySQL儲存引擎採用最多的索引型別。

在InnoDB資料頁中，各個資料頁可以組成一個雙向連結串列，而每個資料頁中的記錄會按照主鍵值從小到大的順序組成一個單向連結串列。

在介紹B+樹時，我們以主鍵索引為例，來看看InnoDB是如何構建主鍵索引的B+樹。其他欄位所建立的索引與主鍵索引相似，只是將主鍵欄位替換成指定的索引欄位來構建B+樹。

主鍵策略

在建立表時，InnoDB儲存引擎會根據不同的場景選擇不同的列作為主鍵索引：

  • 如果有指定主鍵，預設會使用主鍵作為聚簇索引的索引鍵
  • 如果沒有指定主鍵，則選擇第一個不包含NULL值的唯一列作為聚簇索引的索引鍵
  • 在上面兩個都沒有的情況下，InnoDB將自動生成一個隱式自增id列作為聚簇索引的索引鍵

除主鍵索引外，其它索引都屬於輔助索引（Secondary Index），也被稱為二級索引或非聚簇索引。建立的主鍵索引和二級索引預設使用的是B+Tree索引。

建立B+樹索引的條件

條件一：下一個資料頁中記錄的主鍵值必須大於上一個資料頁中記錄的主鍵值

我們知道，在MySQL中，新分配的資料頁編號可能並不是連續的，即這些資料頁在磁碟上並非緊挨著儲存。需要透過維護上一下和下一頁的編號，因此，在InnoDB中，每個資料頁組成了一個雙向連結串列來維護每個資料頁之間的上下關係。

為什麼構建B+樹需要滿足條件一呢？

原因在於為了提高範圍查詢的效率，B+樹要求葉子節點中的資料記錄按照主鍵值的順序進行排列。

當進行範圍查詢時，如果葉子節點中的資料記錄不按照主鍵值的順序排列，就會增加查詢的複雜度。如果下一個資料頁中記錄的主鍵值小於上一個資料頁中記錄的主鍵值，那麼在進行範圍查詢時就需要在不同的葉子節點之間來回跳轉，這樣會增加IO操作次數和查詢時間。

因此，為了保證範圍查詢的效率，B+樹要求葉子節點中記錄的主鍵值必須按照順序排列，即下一個資料頁中記錄的主鍵值必須大於上一個資料頁中記錄的主鍵值。這樣可以確保在進行範圍查詢時可以順利地按照主鍵值的順序進行遍歷，提高查詢效率，同樣MySQL中的預載入頁功能也可以減少IO操作次數。

在InnoDB中，在對頁中的記錄進行增刪改操作時，必須透過一些記錄移動的操作來始終保證：下一個資料頁中使用者的記錄的主鍵值必須大於上一個頁中使用者記錄的主鍵值，則這個過程也成為頁分裂操作，即在一個資料頁中插入記錄，而該資料頁在插入之前已經滿了，則需要申請一個新的資料頁，然後挪動部分資料過去。

條件二：需要給所有的資料頁建立一個目錄頁

由於每個資料頁的編號可能並不連續，因此需要為這些資料頁建立一個目錄。

比如當我們看一本書的時候，書的目錄可以幫助我們快速定位到我們想看的內容，而目錄標題對應的頁號可以比作每個資料頁的頁號，透過書的目錄我們可以快速定位到我們想看的內容，同樣的道理，透過為資料頁建立目錄，在目錄中儲存資料頁的編號，即可透過目錄快速定位到相應的資料頁。

目錄頁可以包括兩個內容：

  • 資料頁的記錄中最小的主鍵值，用key表示
  • 資料頁頁編號，用page_no表示

為了方便說明，我們可以定義一個資料表：

CREATE TABLE `index_demo` (
`a` int NOT NULL,
`b` int DEFAULT NULL,
`c` char(1) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT;

上述表定義中，使用了COMPACT行格式來儲存資料，COMPACT行格式的簡化儲存如下：

我們假設每個資料頁最多隻能存放2條記錄（實際一個資料頁非常大，可以存放許多的記錄）

當為資料頁編制其對應的目錄頁時，如下圖所示：

我們以頁10為例，為其編制的目錄項為目錄項1，該目錄項包含了該頁的頁號10以及該頁的資料記錄中主鍵值最小的值1。當我們把這些目錄項在物理儲存器中連續儲存，就可以實現根據主鍵快速查詢某條記錄的功能了。舉個例子，當我們想查詢主鍵值為100的記錄時，具體的查詢過程如下：

  • 透過目錄項，根據二分查詢快速定位到主鍵值為100的記錄在目錄項3中（因為12 < 100 < 209），其對應的資料頁頁號為9
  • 再根據上述聊到的在單個頁中查詢記錄的方式，即可在頁9中查詢到主鍵值為100的記錄。
B+樹的結構

上述我們聊到構建B+樹的條件，其核心是透過使用二分法的方式快速定位到具體的目錄項，再透過目錄項快速定位到我們所需要的資料頁。

我們在聊到給資料頁記錄建立其對應的目錄項時，資料頁內的每條記錄都會有record_type欄位，它的各個取值代表意思如下：

  • 0：普通資料記錄，即我們插入的資料記錄
  • 2：Infimum記錄，在B+樹索引中，Infimum記錄位於整個索引的最左邊，用於表示沒有更小值的情況。
  • 3：Supermun記錄，在B+樹索引中，Supremum記錄位於整個索引的最右邊，用於表示沒有更大值的情況。

你可能會好奇，取值有0、2、3，那取值1是什麼意思呢？

我們注意到，在目錄項中儲存兩個欄位，分別是最小主鍵值以及對應的頁號，事實上，存放目錄項的頁與實際存放資料的資料頁並無區別，目錄項中的兩個欄位（主鍵值以及頁號）完全可以看作是儲存的實際資料，因此，目錄項中記錄可以稱作為目錄項記錄，即目錄頁，那InnoDB是如何區別實際資料記錄與目錄頁記錄呢，答案就是record_type欄位的取值為1來表示目錄頁記錄。

  • 0：普通資料記錄，即資料頁記錄
  • 1: 目錄項記錄，即目錄頁記錄
  • 2：Infimum記錄
  • 3：Supermun記錄

透過上圖可以看到，我們使用頁30來存放目錄頁記錄，目錄頁記錄與資料頁記錄的不同點在於：

  • 目錄頁記錄的record_type=1，而資料頁記錄的record_type=0
  • 目錄頁記錄只包含主鍵值(索引值)和頁號，而資料頁記錄則可以由使用者來自定義，可以包含很多列。

除此之外，兩者並無實際的區別，在目錄頁中，同樣可以透過二分法來快速定位到目錄頁記錄，再透過目錄頁記錄向下查詢到其對應的資料頁。

當然，當目錄頁中的記錄過多，一個目錄頁難以容納時，我們可以再多分配一個儲存目錄項的頁即可。當同層存在多個目錄項時，我們則可以向上再分配更高一級的目錄項，即多級目錄。

我們可以觀察到，這樣多級的目錄，不就像一顆樹的資料結構了呢？其實，這就是InnoDB的B+樹結構。

B+樹的特點

無論是存放實際資料的資料頁，還是存放目錄項記錄的目錄頁，都可以把它們放到B+樹當中，這些頁稱為B+樹的節點。

其中，存放我們插入的實際資料的記錄存放在B+樹的最底層節點，這些節點稱為葉子節點。其餘非葉子節點則用來存放目錄項記錄。其中B+樹最上層的節點稱為根節點。

B+樹的葉子節點之間是用「雙向連結串列」進行連線，這樣的好處是既能向右遍歷，也能向左遍歷。

2、按物理儲存分類

聚簇索引（主鍵索引）

聚簇索引，又可以稱為主鍵索引，在建立表時，InnoDB會預設為主鍵建立一棵B+樹的主鍵索引。

聚簇索引的特點在於：

  • 使用記錄的主鍵值大小來對記錄和頁進行排序。
  • 頁（包括葉子節點和非葉子節點）內的記錄按照主鍵值的大小進行排序組成單向連結串列。頁內的記錄會被劃分為若干的組，每個組中主鍵值最大的記錄在頁內的偏移量會被當做該組的槽放在頁目錄中，以便後續可以透過二分法來查詢定位到需要查詢的記錄。
  • 存放實際資料的各個資料頁（葉子節點）同樣也根據主鍵大小排成雙向連結串列。
  • 存放目錄資料的各個目錄頁（非葉子節點）可以分為B+樹的多個層級，在同一層級的目錄頁頁根據也中儲存的主鍵值大小來排序成一個雙向連結串列。
  • B+樹的葉子節點存放的是完整的資料記錄，即儲存了該記錄的所有列的值。

包含以上兩個特點的B+樹稱為聚簇索引。

非聚簇索引（二級索引）

不同於聚簇索引，非聚簇索引儲存的並不是完整的資料，非聚簇索引的葉子節點存放的是指定的索引列+主鍵值。非聚簇索引的目錄頁儲存的記錄中不再是主鍵+頁號的搭配，而是指定的索引列+頁號。

以非主鍵列的大小為排序規則而建立的B+樹需要執行回表操作才可以定位到完整的使用者記錄，這種B+樹也稱為二級索引或輔助索引。

同樣非聚簇索引的B+樹的葉子節點也會按照索引列排序並組成雙向連結串列，同一層級的目錄頁也會根據索引列的大小來排序組成雙向連結串列。

回表

聚簇索引和非聚簇索引的查詢有什麼區別？

當使用二級索引進行查詢定位到符合條件的記錄時，當索引中並未儲存我們需要查詢的欄位時（非聚簇索引值的葉子節點只儲存主鍵值以及指定索引值，可能存在需要查詢的欄位並未儲存在索引B+樹中），需要根據二級索引中儲存的主鍵值，回表到主鍵索引查詢到對應的欄位才能夠返回。

根據該記錄中的主鍵資訊回到聚簇索引中查詢到完整的使用者記錄。透過攜帶主鍵資訊到聚簇索引中重新定位完整的使用者記錄的過程也成為回表。

在查詢時，回表是有代價的，我們知道，在使用二級索引進行範圍查詢的時候，二級索引對應的主鍵值的大小是毫無規律的，每讀取一條二級索引記錄，就需要根據該二級索引記錄的主鍵值到聚簇索引中執行回表操作，如果對應的聚簇索引記錄所在的頁面不在記憶體中，就需要將該頁面從磁碟載入到記憶體中由於要讀取很多主鍵值並不連續的聚簇索引記錄，而這些聚簇索引記錄分佈在不同的資料頁中，這些資料頁的頁號也毫無規律，因此會造成大量的隨機I/O。

需要執行回表操作的記錄越多，使用二級索引進行查詢的效能就越低，因此在某些查詢場景下，MySQL最佳化器寧願使用全表掃描也不使用二級索引，而選擇全表掃描，還是二級索引+回表，這就是查詢最佳化器的工作了。

查詢最佳化器會事先針對表中的記錄計算一些統計資料，再利用這些統計資料或者訪問表中的少量記錄來計算回表操作的記錄數，如果需要執行回表操作的記錄數越多，就越傾向於使用全表掃描，反之則傾向於使用二級索引+回表。

既然回表有一定的代價，那為什麼還需要進行回表呢？ 在建立索引時直接就完整的資料記錄放入索引的葉子節點不就好了麼？

如果完整的資料放入到每個索引建立的B+樹的葉子節點中確實可以避免回表，但是取而代之的是需要更多的儲存空間，太佔地方，相當於每建立一棵B+樹都需要將所有的完整資料複製一遍。

3、按欄位特性分類

主鍵索引

主鍵索引，即聚簇索引，建立在主鍵欄位上的索引，通常在建立表的時候一起建立，一張表最多隻有一個主鍵索引，索引列的值不允許有空值。

在建立表時，建立主鍵索引的方式如下：

CREATE TABLE table_name (
...
PRIMARY KEY (index_column_1) USING BTREE
);

唯一索引

唯一索引建立在UNIQUE欄位上的索引，一張表可以有多個唯一索引，索引列的值必須唯一，但是允許有空值。

在建立表時，建立唯一索引的方式如下：

CREATE TABLE table_name (
....
UNIQUE KEY(index_column_1,index_column_2,...)
);

建表後，如果要建立唯一索引，可以使用這面這條命令：

CREATE UNIQUE INDEX index_name ON table_name(index_column_1,index_column_2,...);

普通索引

普通索引就是建立在普通欄位上的索引，既不要求欄位為主鍵，也不要求欄位為UNIQUE。

在建立表時，建立普通索引的方式如下：

CREATE TABLE table_name (
....
INDEX(index_column_1,index_column_2,...)
);

建表後，如果要建立普通索引，可以使用這面這條命令：

CREATE INDEX index_name ON table_name(index_column_1,index_column_2,...);

字首索引

字首索引是指對字元型別欄位的前幾個字元建立的索引，而不是在整個欄位上建立的索引。字首索引可以建立在欄位型別為char、varchar、binary、varbinary的列上。

使用字首索引的目的是為了減少索引佔用的儲存空間，提升查詢效率。

CREATE TABLE table_name(
....
INDEX(column_name(length))
);

建表後，如果要建立字首索引，可以使用這面這條命令：

CREATE INDEX index_name ON table_name(column_name(length));

4、按欄位個數分類

單列索引

當建立索引時，指定某一個欄位作為索引列，建立在單列上的索引稱為單列索引，比如主鍵索引。

聯合索引

聯合索引顧名思義，即指定多個欄位列來作為索引列，同時以多個列的大小作為排序規則，即同時為多個列建立索引。

例如建立聯合索引(a,b)，則在建立的B+樹中，記錄的排序方式為：

  • 先將各個記錄和頁按照a列進行排序
  • 在記錄的a列相同的情況下，再採用b列進行排序

在聯合索引(a,b)的B+樹中，儲存的內容為：

  • 非葉子節點中，每條目錄項記錄都有a列、b列、頁號3個部分組成。各條記錄先按照a列的值進行排序，如果記錄的a列相同，則按照b列的值進行排序
  • 葉子節點中，資料頁記錄由a列、b列和主鍵列三部分組成；

使用聯合索引時，存在最左匹配原則，即按照最左優先的方式進行索引的匹配。

舉個例子：

當我們建立聯合索引（name, age）時，如果你要查的是所有名字第一個字是"張"的人，你的SQL語句的條件是where name like '張%'。這時這條SQL能夠用上這個聯合索引。

從上述的例子可以看出，在對name欄位進行單列條件查詢時，同樣能夠使用上該聯合索引，即聯合索引（name, age）可以等效於單列索引（name）。

因此，在建立聯合索引的時候，如何安排索引內的欄位順序呢？

評估標準是索引的複用能力。如果透過調整順序，可以少維護一個索引，那麼這個順序往往就是需要優先考慮採用的。例如上述的例子，透過聯合索引（name，age）的順序，可以讓我們少維護一個單列索引（name）。當然age。

三、索引的優缺點

在瞭解了B+樹的索引結構後，我們知道恰當的索引使用可以為我們帶來極大的查詢效率，提高效能。但是索引也並非沒有缺點，瞭解好索引的優缺點，選擇使用索引時，需要綜合考慮索引的優點和缺點，才能讓我們在實際使用索引的過程中得心應手。

1、優點

  • 提高資料檢索的效率，降低資料庫的I/O成本，將隨機I/O轉變為順序I/O，這是建立索引最主要的原因；
  • 透過建立唯一索引，可以保證資料庫表中每一行資料的唯一性；
  • 由於索引中記錄的儲存順序，在使用分組和排序子句進行資料查詢時，可以顯著減少查詢中分組和排序的時間，即幫助伺服器避免排序和臨時表，降低了CPU的消耗。
  • 可以加速表和表之間的連線，即對於有依賴關係的子表和父表聯合查詢時，可以提高多表查詢的速度。

2、缺點

建立索引和維護索引要耗費時間 ，並且隨著資料量的增加，所耗費的時間也會增加。

  • 索引需要佔磁碟空間

每個索引在建立後，需要佔一定的物理空間儲存在磁碟上，因為每建立一個索引，都要為它建立一棵B+樹。每一棵B+樹的每一個節點都是一個資料頁。

一個資料頁預設佔用 16KB 的儲存空間，而一棵很大的B+樹由許多資料頁組成，這將會佔用很大的一片儲存空間。

如果有大量的索引，索引檔案就可能比資料檔案更快達到最大檔案尺寸。

  • 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度

當對錶中的資料進行增加、刪除和修改的時候，索引也要動態地維護，即都需要修改各個B+樹索引，這樣就降低了資料的維護速度。

增刪改操作可能會對節點和記錄的排序造成破壞，所以儲存引擎需要額外的時間進行頁面分裂、頁面回收等操作，以維護節點和記錄的排序。

  • 影響最佳化器執行效率

在執行查詢語句前，首先要生成一個執行計劃。

一般情況下，一條查詢語句在執行過程中最多使用一個二級索引，在生成執行計劃時需要計算使用不同索引執行查詢時所需的成本，最後選取成本最低的那個索引執行查詢，如果此時建了太多的索引，可能會導致成本分析過程耗時太多，從而影響查詢語句的執行效能。

四、索引使用場景

索引最大的好處是提高查詢速度，但是索引使用不當則會出現上述描述的缺點所帶來的影響。因此，索引不是萬能鑰匙，它也是根據場景來使用的。

那麼在什麼情況下，我們該使用索引呢？

1、適用索引的場景

  • 欄位需要唯一性限制

當業務中某個欄位需要唯一性限制時，除了在業務邏輯層面校驗，最後一道防線則是透過唯一索引來限制欄位唯一。

  • 經常用於WHERE查詢條件的欄位

對經常用於WHERE查詢條件的欄位建立索引，能夠提高整個表的查詢速度，尤其是在資料量大的情況下，建立索引就可以大幅提升資料查詢的效率。

  • 經常用於GROUP BY和ORDER BY的欄位

對於經常用於GROUP BY和ORDER BY的欄位，建立索引，利用索引其按索引欄位值順序儲存資料的特性，減少排序與臨時錶帶來的損耗。

  • 對DISTINCT欄位建立索引

有時候需要對某個欄位進行去重，使用DISTINCT，那麼對這個欄位建立索引，也會提升查詢效率。因為當去重欄位建立了索引後是按照順序遞增的，所以在去重的時候會快很多。

  • 在多個欄位都要建立索引的情況下，聯合索引優於單值索引

2、不適用索引的場景

  • WHERE條件、GROUP BY條件、ORDER BY條件中用不到的欄位

索引的價值是快速定位，如果起不到定位的欄位通常是不需要建立索引的，因為索引是會佔用物理空間的。

  • 表資料太少的時候，可以考慮不需要建立索引
  • 經常更新的欄位不用建立索引

經常更新的欄位不用建立索引，比如不要使用者餘額建立索引，因為索引欄位頻繁修改，由於要維護 B+Tree的有序性，那麼就需要頻繁的重建索引，這個過程是會影響資料庫效能的。

本文來自 Go就業訓練營 小韜同學的投稿。

又出成績啦

我們又出成績啦！大廠Offer集錦！遙遙領先！

這些朋友贏麻了！

這是一個專注程式設計師升職加薪の知識星球

答疑解惑

需要「簡歷最佳化」、「就業輔導」、「職業規劃」的朋友可以聯絡我。

加我微信：wangzhongyang1993

我的文章均首發在同名公眾號：王中陽Go，歡迎大家關注

相關文章