MySQL的索引有很多種型別，可以為不同的場景提供更好的效能。而B-Tree索引是最為常見的MySQL索引型別，一般談論MySQL索引時，如果沒有特別說明，就是指B-Tree索引。本文就詳細講解一下B-Tree索引的的底層結構，使用原則和特性。 為了節約你的時間，本文的主要內容如下：
- B-Tree索引的底層結構
- B-Tree索引的使用規則
- 聚簇索引
- InnoDB和MyISAM引擎索引的差異
- 鬆散索引
- 覆蓋索引
B-Tree索引
B-Tree索引使用B-Tree來儲存資料，當然不同儲存引擎的實現方式不同。B-Tree通常意味著所有的值都是按順序儲存的，並且每一個葉子頁到根的距離相同，圖1展示了B-Tree索引的抽象表示，由此可以看出MySQL的B-Tree索引的大致工作機制。
B-Tree索引的底層資料結構一般是B+樹，其具體資料結構和優勢這裡就不作詳細描述，下圖展示了B-樹索引的抽象表示，大致反應了MyISAM索引是如何工作的，而InnoDB使用的結構有所不同。
MySQL可以在單獨一列上新增B-Tree索引，也可以在多列資料上新增B-Tree索引，多列的資料按照新增索引宣告的順序組合起來，儲存在B-Tree的頁中。假設有如下資料表：
CREATE TABLE People (
last_name varchar(50) not null,
first_name varchar(50) not null,
birthday date not null,
gender enum('m','f') not null
key(last_name, first_name, birthday)
);
複製程式碼對於表中的每一行資料，索引中包含了last_name，first_name和birthday列的值，下圖展示了該索引是如何組織資料的儲存的。
B-Tree索引使用B-Tree作為其儲存資料的資料結構，其使用的查詢規則也由此決定。一般來說，B-Tree索引適用於全鍵值、鍵值範圍和鍵字首查詢，其中鍵字首查詢只適用於根據最左字首查詢。B-Tree索引支援的查詢原則如下所示：
-
全值匹配：全值匹配指的是和索引中的所有列進行匹配，
-
匹配最左字首：前邊提到的索引可以用於查詢所有姓Allen的人，即只使用索引中的第一列。
-
匹配列字首：也可以只匹配某一列的值的開頭部分。例如前面提到的索引可用於查詢所有以J開頭的姓的人。這裡也只用到了索引的第一列。
-
匹配範圍值：例如前邊提到的索引可用於查詢姓在Allen和Barrymore之間的人。這裡也只使用了索引的第一列。
-
精確匹配某一列並範圍匹配另外一列：前邊提到的索引也可用於查詢所有姓為Allen，並且名字是字母K開頭(比如Kim,Karl等)的人。即第一列last_name全匹配，第二列first_name範圍匹配。
因為索引樹的節點是有序的，所以除了按值查詢之外，索引還可以用於查詢中的ORDER BY操作(按順序查詢)，如果ORDER BY子句滿足前面列出的幾種查詢型別，則這個索引也可以滿足對應的排序需求。
下面是一些關於B-Tree索引的限制：
- 如果不是按照索引的最左列開始查詢，則無法使用索引。例如上面例子中的索引無法查詢名字為Bill的人，也無法查詢某個特定生日的日，因為這兩列都不是最左資料列。
- 如果查詢中有某個列的範圍查詢，則其右側所有列都無法使用索引優化查詢。
聚簇索引
聚簇索引並不是一種單獨的索引型別，而是一種資料儲存方式。具體的細節依賴於其實現方式，但是InnoDB的聚簇索引實際上在同一個結構中儲存了B-Tree索引和資料行。
當表有聚簇索引時，它的資料行實際上存放在索引的葉子頁中，這也就是說資料行和相鄰的鍵值緊湊地儲存在一起。
下圖展示了聚簇索引中的記錄是如何存放的。注意到，葉子頁包含了行的全部資料行，但是節點頁只包含了索引列。
聚簇索引可能對效能有幫助，但也可能導致嚴重的效能問題。聚簇的資料是有一些重要的優點：
- 資料訪問更快，聚簇索引將索引和資料儲存在同一個B-Tree中，因此從聚簇索引中獲取資料通常比在非聚簇索引中查詢要快。
- 使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值。
如果在設計表和查詢時能充分利用上面的優點，那麼就能極大地提升效能。同時，聚簇索引也有一些缺點：
- 插入順序嚴重依賴插入順序。按照主鍵的順序插入是向InnoDB表中插入資料速度最快的方式，需要避免主鍵鍵值隨機的(不連續且值得分佈範圍非常大)聚簇索引，比如使用UUID作為主鍵，而應該使用類似AUTO_INCREMENT的自增列。
- 更新聚簇索引列的代價很高，因為會強制InnoDB將每個被更新的行移動位置到新的位置。
- 基於聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行時，可能面臨“頁分裂”的問題。當行的主鍵值要求必須將這行插入到某個已滿的頁中時，儲存引擎會將該頁分裂成兩個頁面來容納該行，這就是一次頁分裂操作。頁分裂會導致表佔用更多的磁碟空間
- 二級索引可能比想象的更大，因為在二級索引中的葉節點包含了引用行的主鍵列
- 二級索引訪問需要兩次索引查詢，而不是一次。
InnoDB和MyISAM的索引區別
聚簇索引和非聚簇索引的資料分佈有區別，以及對應的主鍵索引和二級索引的資料分佈也有區別，通常會讓人感到困惑和意外。下圖展示了MyISAM和InnoDB的不同索引和資料儲存方式。
MyISAM的資料分佈非常簡單，按照資料插入的順序儲存在磁碟上，主鍵索引和二級索引的葉節點儲存著指標，指向對應的資料行。
InnoDB中，聚簇索引“就是”表，所以不會像MyISAM那樣需要獨立的行儲存。聚簇索引的每個葉節點都包含了主鍵值和所有的剩餘列(在此例中是col2)。
InnoDB的二級索引和聚簇索引很不同。InnoDB二級索引的葉節點中儲存的不是“行指標”，而是主鍵值，並以此作為指向行的“指標”。
鬆散索引掃描
MySQL並不支援鬆散索引掃描，也就是無法按照不連續的方式掃描一個索引。通常，MySQL的索引掃描需要先定義一個起點和終點，即使需要的資料只是這段索引中很少數的幾個，MySQL仍然需要掃描這段索引中的每個條目。
下面，我們通過一個示例說明這點，假設我們有如下索引(a,b)，有下面的查詢：
mysql>SELECT * FROM tb1 WHERE b BETWEEN 2 AND 3;
複製程式碼因為索引的前導欄位是列a，但是在查詢中只指定了欄位b，MySQL無法使用這個索引，從而只能通過全表掃描找到匹配的行，如下圖所示。
瞭解索引的物理結構的話，不難發現還可以有一個更快的辦法執行上面的查詢。索引的物理結構(不是儲存引擎的API)是的可以先掃描a列第一個值對應的b列的範圍，然後再跳到a列第二個不不同值掃描對應的b列的範圍。下圖展示瞭如果由MySQL來實現這個過程會怎樣。
注意到，這時就無須再使用WHERE子句過濾，因為鬆散索引掃描已經跳過了所有不需要的記錄。
MySQL 5.0之後的版本，在某些特殊的場景下是可以使用鬆散索引掃描的，例如，在一個分組查詢中需要找到分組的最大值和最小值：
mysql> EXPLAIN SELECT actor_id, MAX(film_id)
-> FROM sakila.film.film_actor
-> GROUP BY actor_id;
********************************************* 1. row ***********************************
id: 1
select_type: SIMPLE
table: film_actor
type: range
possible_keys: NULL
key: PRIMARY
key_len: 2
ref: NULL
rows: 396
Extra: Using index for group-by
複製程式碼在EXPLAIN中的Extra欄位顯示"Using index for group-by"，表示這裡將使用鬆散索引掃描。
覆蓋索引
索引除了是一種查詢資料的高效方式之外，也是一種列資料的直接獲取方式。MySQL可以使用索引來直接獲取列的資料，這樣就不需要讀取資料行。如果一個索引包含所有需要查詢的欄位的值，我們就稱之為“覆蓋索引”。 覆蓋索引是非常有用的工具，能夠極大地提高效能。SQL查詢只需要掃描索引而無需回表，會帶來很多好處：
- 索引條目數量和大小通常遠小於資料行的條目和大小，所以如果只需要讀取索引，那麼MySQL就會極大地減少資料訪問量。
- 因為索引是按照列順序儲存的，所以對於I/O密集型的範圍查詢會比隨機從磁碟讀取每一行資料的I/O要少的多。
- 由於InnoDB的聚簇索引，覆蓋索引對InnoDB表特別有用。InnoDB的二級索引在葉子節點中儲存了行的主鍵，索引如果二級主鍵能夠覆蓋查詢，則避免對主鍵索引的第二次查詢。
當發起一個被覆蓋索引的查詢(也叫索引覆蓋查詢)時，在EXPLAIN的Extra列可以看到"Using Index"的資訊。例如，表sakila.inventory有一個多列索引(store_id, film_id)。MySQL如果只需要訪問這兩列，就可以使用這個索引做覆蓋索引，如下所示：
mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory
*********************************1.row***************************************
id:1
select_type:SIMPLE
table:inventory
type:index
possible_keys:NULL
key:idx_store_id_film_id
key_len:3
ref:NULL
rows:4673
Extra:Using Index
複製程式碼訂閱最新文章，歡迎關注我的微信公眾號
參考：
- MySQL索引背後的資料結構及演算法原理
- 《高效能MySQL》