前言

本文內容主要參考自《高性能MySQL》第5章以及《MySQL DBA 修煉之道》書中的第三章，算是原書的實踐與補充。 上次主要講了MySQL的基本操作，這次來談談索引與EXPLAIN。

I. 什么是索引？

想要深入的學習MySQL相關技術，而不僅僅停留在簡單CURD，能夠寫出百萬數據中分分鐘查出需要數據的SQL，首先就需要掌握索引技術。那么什么是索引呢？

要理解MySQL中索引是如何工作的，最簡單的方法就是去看看一本書的“索引”部分：如果想在一本書中找到某個特定主題，一般會先看書的“索引”，找到對應的頁碼。所以當數據表中的數據越來越多時，挨個查找記錄將會越來越慢，我們需要像查“字典”一樣建立一種“目錄”，來幫我們仍然能夠快速的查找想要的記錄。這種“目錄”一樣的存在便是索引。在MySQL中，存儲引擎用類似的方法使用索引，其先在索引中找到對應值，然后根據匹配的索引記錄找到數據庫表中對應的數據行。

索引，在MySQL中也叫做“鍵(key)”，是存儲引擎用于快速找到記錄的一種數據結構。為什么是數據結構？因為本身索引是為了解決查找問題，查找排序在算法中是經常遇到的，實現查找我們通常有一些對應的算法，遍歷、二分、二叉搜索樹、紅黑樹、散列表等（詳細可以看橙色的那本《算法》書）。而一些快速的查找算法都有其對應的數據結構來實現，索引就是存儲引擎實現的一種數據結構能夠快速用于查找數據庫中記錄。后面我們會知道數據結構具體可能是 B-Tree、哈希索引、R-Tree、全文索引等。

索引優化應該是對查詢性能優化最有效的手段了。索引能夠輕易將查詢性能提高幾個數量級，“最優”的索引有時比一個“好的”索引性能要好兩個數量級。創建一個真正“最優”的索引經常需要重寫查詢。

同一個表，可以創建多個索引。就像新華字典的索引，不僅僅只有拼音，還有筆畫、偏旁部首等。除了允許不同的字段添加索引之外，還可以將字段組合添加索引，組合的先后順序也影響到查詢速度。甚至其實MySQL允許同一個字段上重復創建索引，但這并不可取。

II. 索引類型

對于數據庫索引相關問題來說，有許多計算機操作系統底層的名詞需要了解并通曉其含義，下面將本文需要提前了解的概念列出如下：

• CPU密集型：CPU密集型也叫計算密集型，指的是系統的硬盤、內存性能相對CPU要好很多，此時，系統運作大部分的狀況是CPU Loading 100%，CPU要讀/寫I/O(硬盤/內存)，I/O在很短的時間就可以完成，而CPU還有許多運算要處理，CPU Loading很高。

• IO密集型：IO密集型指的是系統的CPU性能相對硬盤、內存要好很多，此時，系統運作，大部分的狀況是CPU在等I/O (硬盤/內存) 的讀/寫操作，沒有充分利用處理器能力。

• 操縱系統頁：磁盤的讀寫速度比主存慢很多，所以為了提高效率，要盡量減少磁盤I/O，減少讀寫操作。為了達到這個目的，磁盤往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個字節，磁盤也會從這個位置開始，順序向后讀取一定長度的數據放入內存，這樣做的理論依據是計算機科學中著名的局部性原理。預讀的長度一般為頁(page)的整倍數，頁是計算機管理存儲器的邏輯塊，硬件及操作系統往往將主存和磁盤存儲區分割為連續的大小相等的塊，每個存儲塊稱為一頁(在許多操作系統中，頁得大小通常為4k)，主存和磁盤以頁為單位交換數據。當程序要讀取的數據不在主存中時，會觸發一個缺頁異常，此時系統會向磁盤發出讀盤信號，磁盤會找到數據的起始位置并向后連續讀取一頁或幾頁載入內存中，然后異常返回，程序繼續運行。

• 數據庫頁：數據庫文件存儲是頁為存儲單元，一個頁可以存放N行數據。我們常用的頁類型就是數據頁和索引頁。以InnoDB引擎為例，其邏輯存儲結構如下圖所示。所有數據都被邏輯地存放在一個稱之為表空間 (tablespace)的空間中。表空間又由段(segment) 、區(extent) 、頁(page) 組成。表空間是由各個段組成的，常見的段有數據段、索引段、回滾段等。InnoDB存儲引擎表是索引組織的，因此數據即索引，索引即數據。那么數據段即為B+樹的葉子節點 (leaf node segment)，索引段即為B+樹的非葉子節點 (non-leaf node segment)。區是由64個連續的頁組成的，每個頁大小為16KB，即每個區的大小為1MB。頁是InnoDB磁盤管理的最小單位。InnoDB存儲引擎是面向行的，也就是說數據的存放按行進行存放。每個頁存放的行記錄數目也是有硬性定義的，最多允許存放 16KB/2-200 行的記錄，即7992行記錄。
  

• 順序IO：每次訪問磁盤的一個目標塊時，磁臂就需移動到正確的磁道上，耗費的這段時間為尋址時間；然后盤片就需旋轉到正確的扇區上，這段時間又為旋轉時延。很明顯總共耗費的時間依賴于磁頭的初使位置，還有要訪問的扇區的位置。如果目標塊剛好就在磁頭下方，那不需要等待；如果剛剛經過磁頭，那就不得不等上一個周期時間。 找到上一個目標塊后，下一個目標塊就在剛才訪問的那一個磁盤塊的后面，磁頭能立刻遇到，不需等待，這種IO就叫順序IO。

• 隨機IO：如果下一個目標塊在磁盤的另一個地方，訪問它會有同樣的尋道和旋轉時延，我們就把這種方式的IO叫做隨機IO。數據庫的很多設計也都是盡量充分利用順序IO，傳統的數據庫架構對隨機IO幾乎沒有還手之力，隨機IO幾乎令所有DBA談虎色變，MySQL InnoDB則利用事務日志把隨機I/O轉成順序I/O。

• 主存存取過程：從抽象角度看，主存是一系列的存儲單元組成的矩陣，每個存儲單元存儲固定大小的數據。每個存儲單元有唯一的內存地址。當系統需要讀取主存時，則將地址信號放到地址總線上傳給主存，主存讀到地址信號后，解析信號并定位到指定存儲單元，然后將此存儲單元數據放到數據總線上，供其它部件讀取。寫主存的過程類似，系統將要寫入單元地址和數據分別放在地址總線和數據總線上，主存讀取兩個總線的內容，做相應的寫操作。這里可以看出，主存存取的時間僅與存取次數有關，和存取的數據的位置沒什么關系，因為讀寫數據相當于直接根據地址定位坐標。

• 磁盤存取過程：當需要從磁盤讀取數據時，系統會將數據邏輯地址傳給磁盤，磁盤的控制電路按照尋址邏輯將邏輯地址翻譯成物理地址，即確定要讀的數據在哪個磁道，哪個扇區。這個過程耗費的時間包括尋道和旋轉時間。由于存儲介質的特性，磁盤本身存取就比主存慢很多，再加上機械運動耗費，磁盤的存取速度往往是主存的幾百分之一，因此為了提高效率，要盡量減少磁盤I/O。

索引是一中數據結構，那么索引類型自然是指不同類型的數據結構。索引有很多種類型，可以為不同的場景提供更好的性能。在MySQL中，索引是在存儲引擎層而不是服務器層實現的。所以，并沒有統一的索引標準：不同存儲引擎的索引的工作方式并不一樣，也不是所有的存儲引擎都支持所有類型的索引。即使多個存儲引擎支持同一種類型的索引，其底層的實現也可能不同。我們主要來研究MySQL支持的索引類型。

B-Tree索引

當談索引的時候，如果沒有特別指明類型，那多半說的是B-Tree索引，它使用B-Tree數據結構來存儲數據。那么首先我們先來了解B-Tree數據結構。

① B樹，B-樹，B+樹

首先需要明確的是，B樹就是B-(減)樹，本文中凡是出現的B-Tree，都是B橫杠Tree，而非減號，如果想要表示B減樹則直接用漢字減或用B樹代表相同含義。

B樹

二叉查找樹是非平衡樹，極端情況下查找性能可能非常低，所以才有了紅黑樹這類平衡二叉樹。B樹也是一種平衡樹，相比于紅黑樹之類的2-3平衡樹而言，B樹的階，或者說是節點的最大出度不僅僅局限于2或3。從查找效率來說，一般階大于等于3，用 m 表示。假設一個非空的B樹，滿足以下性質：

• 根結點至少有兩個子女；
• 每個中間節點都包含 k-1 個元素和 k 個孩子，k 屬于 [ceil(m/2)，m]；
• 每個葉子節點都包含 k-1 個元素，k 屬于 [ceil(m/2)，m]；
• 所有的葉子節點都位于同一層（平衡樹）；
• 每個節點中的元素從小到大排列，節點當中 k-1 個元素正好是 k 個孩子包含的元素的值域劃分；
• 每個節點包含了 k-1 個元素，以及 k 個孩子的指針。

一個標準的B樹如下圖：

可以看出當我們查找某一個元素時，最多需要讀取 h (B樹的高度) 次數即可。如果需要插入一條數據，B樹為了維護上面的性質，需要對樹的結構做一些調整。如果插入元素后某一節點的元素數目大于 m，則在插入前需要進行分裂。同樣，如果刪除一條數據，刪除后節點的元素數目小于 ceil(m/2)，也要進行相應的合并操作。

利用B樹的數據結構來進行存儲數據，我們可以將數據與對應的索引信息定義為一個組合[key, data]，key是data的索引。那么一個簡單的B樹可以表示為：

每個節點中包含了 k-1 個索引值、 k-1 個對應的數據 (除去了索引值之外的數據)以及 k 個指針指向子節點。

B+樹

B+樹其實是B樹的一種變種，MySQL普遍使用B+Tree的數據結構來實現索引，當然包括主要存儲引擎MyISAM和InnoDB。B+樹與B樹相比，主要有以下不同：

• 每個中間節點都包含 k 個元素和 k 個孩子，相當于指針數目也是 k；
• 非葉子節點不存儲數據data，只存儲key；
• 所有的中間節點元素都同時存在于子節點，在子節點元素中是最大（或最小）元素。

如下是一個B+樹的示意圖，可以看到完全滿足上面的三條性質。

帶有順序訪問指針的B+樹

一般在數據庫系統或文件系統中使用的B+樹結構都在經典B+樹的基礎上進行了優化，增加了順序訪問指針。下圖所示的帶有順序訪問指針的B+樹就是我們經常看到的B+樹模樣。

對比上一幅圖，主要區別在于每個葉子節點增加一個指向相鄰葉子節點的指針，這樣就形成了帶有順序訪問指針的B+樹。這樣優化的目的是為了提高區間訪問的性能，如果要查詢key為某個范圍內的所有數據記錄，當找到第一個數據后，只需順著節點和指針順序遍歷就可以一次性訪問到所有數據節點，極大提到了區間查詢效率。

② 數據庫為什么使用B+樹？

數據庫的索引數據量也是很大的，所以它存儲在磁盤中，而非內存。那么當進行增刪改查數據時，需要讀取索引內容，就進行了磁盤I/O。通過前面的相關概念介紹，磁盤I/O的耗時操作越少越好，所以磁盤I/O次數可以評價索引數據結構的優劣。

先從二叉查找樹以及紅黑樹說起，這兩種樹本身的階數是固定的，每個節點的子節點數很小，導致了如果存在很多索引時，樹的深度非常深，對應查找需要比較的次數也會非常多，性能必然受到嚴重影響。

再說B樹，因為它的階數是 m，可以設置的較大，這樣可以使的決定查詢比較次數的因素——樹的深度可以很淺。根據B樹的定義，可知檢索一次最多需要訪問 h 個節點。數據庫系統的設計者巧妙利用了磁盤預讀原理，將樹的每個節點的大小設為等于一個頁，這樣每個節點只需要一次磁盤I/O就可以完全載入。為了達到這個目的，在實際實現B樹時還需要使用如下技巧：

• 每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也存儲在一個頁里，加之計算機存儲分配都是按頁對齊的，就實現了一個節點只需一次磁盤I/O；
• B樹中一次查詢最多需要 h-1 次磁盤I/O，因為根節點常駐于內存，漸進復雜度為 $O(h)=O(lo{g}_{d}N)$。一般實際應用中，出度 d 是非常大的數字，通常超過100，因此 h 非常小 (通常不超過3，3已經是$10^6$級別數據量)。

所以用B樹作為數據庫的索引效率遠遠高于紅黑樹等。

然而，MySQL的MyISAM和InnoDB都采用的是帶有順序訪問指針的B+樹去實現索引 ，這又是為何呢？比較B+樹和B樹的區別，除了葉子節點有順序訪問指針幫助范圍查詢之外，主要就是非葉子節點上B+樹只存有索引(key)，沒有額外再存(data)。之前我們已經說過，一般樹的每個節點的大小等于一個頁的大小，容量固定的情況下，由于B樹需要保存數據記錄所以一個節點能包含的索引數目比B+樹要小。也就是說，一個非葉子節點的出度 d，上限取決于節點內 key 和 data 的大小。具體的公式如下：

$d_{max}=一個節點中能容納的索引數目=floor(pagesize/(keysize+datasize+pointsize))$

由于B+樹非葉子節點去掉了 data，因此可以擁有更大的出度，擁有更好的性能。

③ MySQL中的B-Tree索引

《高性能MySQL》中一直使用的是B-Tree索引這樣的描述，從技術實現角度，其實是B+樹。

假設我們現在存在一個數據表，包含三個字段：主鍵Col1、輔助索引Col2以及字段Col3。

MyISAM索引實現

從上面對于B+樹的描述，我們可以大概的推測出索引的結構。我們先來看MyISAM對于主鍵索引的原理圖：

可以看出MyISAM的B+樹中，非葉子節點僅僅保存了主鍵值，葉子節點上保存的是數據庫對應記錄的地址。通過地址我們可以定位到每一條記錄。我們知道每個節點對應一頁，每個節點中包含多行數據庫記錄 (圖中為2個)，需要注意的是邏輯上相鄰的記錄，物理上可能并不在同一頁中，比如表中的第2行和第3行數據，它們在不同的頁中。

我們再來看看輔助索引Col2的結構。輔助索引的葉子結點除了包含鍵值以外，每個葉子結點中的索引行還包含了一個書簽，該書簽用來告訴存儲引擎可以在哪找到相應的數據行，MyISAM存儲引擎的輔助索引的書簽就是地址，其實和主鍵索引沒什么差別。

同樣也是一顆B+樹，data域保存數據庫記錄的地址。因此，MyISAM中索引檢索的算法為首先按照B+樹搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址，讀取相應數據記錄。

InnoDB索引實現

雖然InnoDB也使用B+樹作為索引結構，但具體實現方式卻與MyISAM截然不同。

首先來看主鍵索引的實現方式。

對比MyISAM的主鍵索引，最顯著的區別就是在于葉子節點的保存內容。MyISAM索引文件和數據文件是分離的，索引文件僅保存數據記錄的地址。而在InnoDB中，表數據文件本身就是按B+樹組織的一個索引結構，這棵樹的葉節點data域保存了完整的數據記錄，這個索引的key是數據表的主鍵，那么InnoDB引擎的數據文件本身就是主索引文件。這種數據與索引在一起的結構叫做聚簇索引，或者叫聚集索引。因為InnoDB的數據文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵。如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵。如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含字段作為主鍵，這個字段長度為6個字節，類型為長整型。

我們再來看看InnoDB的輔助索引實現結構，我們在表的Col3字段上添加上輔助索引。

與MyISAM索引的不同是InnoDB的輔助索引data域存儲相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域，因為通過主鍵我們同樣可以查詢到整個數據庫記錄。聚簇索引這種實現方式使得按主鍵的搜索十分高效，但是輔助索引查詢需要二次查詢：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。

④ 正確使用和優化索引

知道了索引的實現方式對我們理解索引的正確使用方式和優化原理有著莫大的幫助。下面列舉一些使用索引的常見策略。

假設現在有一個表：

CREATE TABLE People (last_name varchar(50) not null,first_name varchar(50) not null,birth date not null,gender enum('m', 'f') not null,key(last_name, first_name, birth) );

表中定義了四個字段，包含姓、名、出生日期以及性別，同時建立了一個組合索引包含了姓、名、出生日期三個字段。該索引的B+樹結構某一小部分如下：

可以看到非葉子節點上存儲了索引字段信息，B-Tree對索引列是順序組織存儲的，索引之間按照一定的排序規則進行有序的排序，這里就是按姓名的字母序以及日期的由小到大。依據這樣的一個結構，編寫合理的SQL語句，我們可以極為快速的尋找到我們需要的記錄。

• 全值匹配：全值匹配指的是和索引中的所有列進行匹配，也就是我們的查詢語句的條件完全和索引列中一一對應，不僅僅是內容，而且要求順序也一致。不難理解，這就是一個簡單的遞歸查找樹的過程。
• 匹配最左前綴：當然我們想要從當前的索引樹中獲得好處，查詢條件并不一定需要全值匹配，我們可以只包含索引列中的第一個字段，例如我們查找所有姓Allen的人。當然，如果只包含名或生日的查詢條件，就不能利用當前索引樹了。
• 匹配列前綴：同樣的，我們的查詢條件甚至可以連第一列字段的信息都不完全，比如只匹配第一列值的開頭部分。例如查找以姓All開頭的人。
• 精確匹配某一列并范圍匹配另外一列：由前面的經驗，我們自然就可以推演出，可以精確匹配前面部分的索引列，后面的索引列僅僅是最左前綴的形式。例如，我們要查找具體姓啥名啥但日期只要19xx年的人。
• 匹配范圍值：匹配范圍值不光光能夠從索引樹整個結構獲益，B+樹的葉子節點額外增加了順序訪問指針，使得速度能夠更快。這在之前我們已經有所提及。
• 覆蓋索引：覆蓋索引是指查詢只需要訪問索引，而無須訪問數據行。如果我們想要查詢的信息索引列已經完全包含，那么我們就不需要再去葉子節點找到主鍵或者是記錄的地址，然后再到對應的數據記錄中查詢信息。這樣一個過程其實叫二次查詢。
• ORDER BY與GROUP BY：索引樹中的節點是有序的，所以除了按值查找之外，索引還可以用于查詢中的 ORDER BY 與 GROUP BY 操作。

當然，我們從之前的索引實現方式也能想到一些關于B-Tree索引的限制：

• 如果不是按照索引的最左列開始查找，則無法使用索引。 例如上面例子中的索引無法用于直接查找名字為Bill的人，也無法直接查找某個特定生日的人，因為這兩列都不是最左前綴。類似地，也無法查找姓氏以某個字母結尾的人。
• 不能跳過索引中的列。 也就是說，前面所述的索引無法用于查找姓為Smith并且在某個特定日期出生的人。如果不指定名字，則MySQL只能使用索引的第一列——姓列。
• 如果查詢條件中有某個列是范圍查詢，則其右邊所有列都無法使用索引優化查找。 如果范圍查詢列值的數量有限，那么可以通過使用多個等于條件來代替范圍條件。

哈希索引

① 什么是哈希索引？

哈希索引 (hash index) 是基于哈希表實現的，只有精確匹配索引所有列的查詢才有效。對于每一行數據，存儲引擎都會對所有的索引列計算一個哈希碼 (hash code)，哈希碼是一個較小的值，并且不同鍵值的行計算出來的哈希碼也不一樣。哈希索引將所有的哈希碼存儲在索引中，同時在哈希表中保存指向每個數據行的指針。

在MySQL中，只有Memory引擎顯式支持哈希索引。這也是Memory引擎表的默認索引類型，Memory引擎同時也支持B-Tree索引。值得一提的是，Memory引擎是支持非唯一哈希索引的，這在數據庫世界里面是比較與眾不同的。如果多個列的哈希值相同，索引會以鏈表的方式存放多個記錄指針到同一個哈希條目中。

哈希索引自身只需存儲對應的哈希值，所以索引的結構十分緊湊，這也讓哈希索引查找的速度非常快。從實現原理上，我們可以將其類比為一個巨大的 HashMap 集合。所以哈希索引也自然就有它的限制：

• 哈希索引只包含哈希值和數據行指針，而不存儲字段值，所以不能使用索引中的值來避免讀取行，也就是不會有覆蓋索引了。不過，訪問內存中的行的速度很快，所以大部分情況下這一點對性能的影響并不明顯。
• 哈希索引數據并不是按照索引值順序存儲的，所以也就無法用于排序。
• 哈希索引也不支持部分索引列匹配查找，也就是必須全值匹配，因為哈希索引始終是使用索引列的全部內容來計算哈希值的。索引列少一點點哈希碼就不一樣，所以不可能進行部分匹配。
• 哈希索引只支持等值比較查詢，包括=、IN()、<=>，不支持任何范圍查詢。
• 訪問哈希索引的數據非常快，除非有很多哈希沖突。當出現哈希沖突的時候，存儲引擎必須遍歷鏈表中所有的行指針，逐行進行比較，直到找到所有符合條件的行。
• 如果哈希沖突很多的話，一些索引維護操作的代價也會很高。比如在性別列上添加哈希索引，由于只存在兩種常規性別，所以哈希沖突非常嚴重，這樣的哈希索引價值也不大。

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

② 利用自定義哈希索引提高性能

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

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

舉個例子，我們在數據庫中經常會插入一些鏈接，這些鏈接往往很長，選擇性也一般，如果使用B-Tree來存儲URL，存儲的內容就會很大。原來我們的查詢語句如下：

mysql> SELECT id FROM url WHERE url="http://www.mysql.com";

若刪除原來URL列上的索引，而新增一個被索引的 url_crc 字段，存放URL的哈希碼值，不僅僅能夠壓縮字符串的大小，性能也因此提升很快。

mysql> SELECT id FROM url WHERE url="http://www.mysql.com" AND url_crc=CRC32("http://www.mysql.com");

MySQL優化器會使用這個選擇性很高而體積很小的基于 url_crc 列的索引來完成查找。即使有多個記錄有相同的索引值，查找仍然很快，只需要根據哈希值做快速的整數比較就能找到索引條目，然后一一比較返回對應的行。而原本則是對完整的URL字符串做索引，那樣會非常慢。

當然，這樣實現的缺陷是需要維護哈希值。可以手動維護，也可以使用觸發器實現。除此以外，哈希算法的優劣也需要注意，因為它影響著哈希索引的選擇性。索引的選擇性是指索引列中不同值的數目與表中記錄總數的比值。

III. 使用EXPLAIN

EXPLAIN工具可以確認執行計劃是否良好，查詢是否走了合理的索引。不同版本的MySQL優化器各有不同，一些優化規則隨著版本的發展可能會有變化，查詢的執行計劃可能會隨著數據的變化而變化。對于這種情況，我們可以使用EXPLAIN工具驗證自己的判斷。

使用方式

語法形式為：

explain select ·····

除此之外還有兩種變體：

explain extended select ····· show warnings

加上 extend 可以將執行計劃反編譯成 select 語句，通過 show warnings 即可得到被MySQL優化后的查詢語句。

另一種變體是：

explain partitions select ·····

該命令用于分區表的EXPLAIN命令。分區是將數據分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器。分區后，表面上還是一張表，但數據散列到多個位置了。程序讀寫的時候操作的還是大表名字，MySQL服務器自動去組織分區的數據。

我們以MySQL官方文檔中提供的示例數據庫 employees 中的 titles 為例。首先先查看它的全部索引，可以看到前三列組成主鍵索引 <emp_no, title, from_date> ，同時單獨又創建了一個輔助索引 <emp_no>。考慮圖片寬度，下圖的索引信息部分列被刪除。

我們進行一個查詢，并用EXPLAIN進行分析。

表格中告訴我們MySQL訪問了哪些表，以及它是如何訪問數據的。里面包含很重要的索引使用信息，據此可以判斷出索引是否需要優化。

返回信息解讀

針對上面EXPLAIN返回的表格，我們對每一列的含義進行具體的研究。所有的信息大致可以用下面思維導圖表示：

MySQL EXPLAINid：表示查詢中SELECT子句或操作表的順序select_type：表示查詢中SELECT子句的類型table：表示從哪個表 (或查詢結果表) 中進行查詢type：表示MySQL在表中查找所需行的方式，也稱"訪問類型"possible_keys：指出MySQL能使用哪個索引在表中找到行，查詢涉及的字段上如果存在索引，則索引被列出，但不一定會被查詢使用key：顯示MySQL在查詢中實際使用的索引，如果沒有則為NULL。查詢中如果使用了覆蓋索引，則該索引僅出現在key的列表中key_len：表示經計算得到的索引最大可能使用的字節數，并非實際使用字節數。根據字節數可以推測索引最大使用長度ref：表示table中的表的連接匹配條件，即哪些列或常量被用于查找索引列上的值rows：表示MySQL根據表統計信息以及索引的選用情況，估算出找到目標記錄所需要讀取的行數Extra：展示那些不適合在其他列中顯示又十分重要的備注信息<dervied3> 3表示的是該查詢結果衍生自id為3的select<union1,4> 1和4表示對第1個和第4個select結果進行union操作1. SIMPLE：查詢中不包含子查詢或UNION2. PARIMARY：最外層查詢中包含任何復雜的子部分3. SUBQUERY：在select和where列表中包含的子查詢4. DERIVED (衍生)：在from列表中包含的子查詢5. UNION：出現在UNION之后的查詢select6. UNION RESULT：從union合并的表中select1. 相同則順序由上至下；2.?子查詢id序號會遞增，id越大越優先執行。All：MySQL通過遍歷全表以找到匹配的行index：只遍歷索引樹找到匹配行range：索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行ref：非唯一性索引掃描，將返回匹配某個單獨值的所有行。常用于使用非唯一索引或唯一索引的非唯一前綴進行的查找eq_ref：唯一索引掃描，對于每個索引鍵，表中只有一個記錄與之匹配。常見于主鍵索引或唯一索引掃描const：MySQL對查詢的某部分進行優化，并轉化為一個常量。如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量。system：是const類型的特例，當查詢的表只有一行的情況下，即可使用systemNULL：MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引Using?index：表示相應的select操作中使用了覆蓋索引Using?where：表示MySQL服務器在存儲引擎收到記錄后進行"后過濾"Using?temportary：表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢Using?filesort：文件排序，MySQL無法用索引完成的排序操作

① id

id 包含一組數字，表示查詢中執行 select 子句或操作表的順序。如果 id 相同，則為一組，執行順序由上至下，如果是子查詢，id 的序號會遞增，id 值越大優先級越高，越先被執行。

② select_type

select_type 表示查詢中每個select子句的類型，一共有如下幾種情況：

• SIMPLE：查詢中不包含子查詢或者 UNION；
• PRIMARY：查詢中若包含任何復雜的子部分，最外層查詢則被標記為 PRIMARY；
• SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查詢，該子查詢被標記為 SUBQUERY；
• DERIVED：用來表示包含在 FROM 子句中的子查詢的 SELECT，MySQL會遞歸執行并將結果放到一個臨時表中。服務器內部稱為"派生表"，因為該臨時表是從子查詢中派生出來的。
• UNION：若第二個 SELECT 出現在 UNION 之后，則被標記為 UNION；
• UNION RESULT：從 UNION 的結果表中進行的 SELECT 。

下面舉個例子，SQL語句如下

select vt1.dept_no from (select emp_no, dept_no from dept_emp where emp_no < (select emp_no from employees where emp_no = 10010)) vt1 union (select vt2.emp_no from (select emp_no from dept_manager) vt2 where emp_no < 110300);

執行對應的 EXPLAIN 語句查看執行計劃。這里要關閉MySQL5.7開始的優化器引入 derived_merge，處理 from 語句中的派生表和視圖能更好地避免不必要的物化并能夠通過條件下放產生更有效的執行計劃。比如上面SQL中 union 后的一句話，子查詢 select emp_no from dept_manager 沒有條件，正常情況下需要全部遍歷輸出產生派生表，然后再從派生表的所有記錄中進行篩選 emp_no < 110300，這樣其實很慢，在產生派生表的時候就利用上篩選條件 emp_no < 110300，派生表的結果也會變小很多。

為了完整的顯示所有的查詢，我們將這種優化先關閉。

從上圖可以看出，一共有6個查詢，基本包含了幾種常見的 select_type，按順序分析：

• 最先執行 id=5 的查詢，看 table 列中指明的是從 dept_manager 中查詢，可以確定是 select emp_no from dept_manager，由于該子查詢在 from 中，所以為 DERIVED；
• 然后執行 id=4 的查詢，table 列中指明的是從 derived5 中查詢，5代表 id=5，所以確定就是 union 后面部分的查詢，外層查詢查的是派生表。由于其在 union 之后，所以該查詢標記為 UNION；
• 繼續執行 id=3 的查詢，table 列中指明的是從 employees 中查詢，可以確定是 select emp_no from employees where emp_no = 10010，該子查詢在 where 條件中，所以是個子查詢標記為 SUBQUERY；
• id=2 的查詢 table 列中指明的是從 dept_emp 中查詢，可以確定是 select emp_no, dept_no from dept_emp where ····，同樣它的結果也是一個派生表，所以標記為 DERIVED；
• union 前的查詢為復雜查詢，標記為 PRIMARY，其 table 列中指明的是從 derived2 中查詢；
• 最后將 union 前后的查詢結果合并，標記為 UNION RESULT。

③ type

MySQL中 explain 的 type 類型包括如下幾種，從上到下，由最差到最好。

Type含義

All	全表掃描， MySQL將遍歷全表以找到匹配的行。
index	索引全掃描，index 與 ALL 區別為 index 類型只遍歷索引樹。
range	索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行。顯而易見的索引范圍掃描是帶有between或者where子句里帶有<, >查詢。當MySQL使用索引去查找一系列值時，例如 IN() 和 OR 列表，也會顯示 range (范圍掃描)，這種情況查詢性能往往因為結果少性能更高。
ref	使用非唯一索引掃描或者唯一索引的前綴掃描，返回匹配某個單獨值的所有記錄行。
eq_ref	類似 ref，區別就在使用的索引是唯一索引，對于每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用主鍵或者唯一索引作為關聯條件。
const/system	當MySQL對查詢某部分進行優化，并轉換為一個常量時，使用這些類型訪問。如將主鍵置于where 列表中，MySQL就能將該查詢轉換為一個常量。system是const類型的特例，當查詢的表只有一條記錄的情況下，即可使用system。
NULL	MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨索引查找完成。

④ possible_keys和key

possible_keys 指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用。

key 顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為 NULL。查詢中如果使用了覆蓋索引，則該索引僅出現在 key 的列表中。例如上面的例子中演示 type 為 index 的查詢。possible_keys 為 NULL，key 為輔助索引 emp_no。

⑤ key_len

key_len 表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度。注意 key_len 顯示的值為索引字段的最大可能長度，并非實際使用長度，即 key_len 是根據表的定義計算而得，不是通過表內檢索出的。

舉個例子，如下圖所示，我們使用 titles 表的主鍵索引——一個組合索引，分別進行減少查詢使用的索引列，emp_no 為 INT 類型，占4個字節；title 為 VARCHAR 類型，50個字符，由于是utf-8字符集，每個字符3個字節，所以50個字符150個字節，加上2個字節存儲長度，所以占據了152字節；最后 from_date 是 DATE 類型占據了3個字節。

⑥ ref

表示表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。查看下面圖，表的內外連接使用了過濾匹配條件。先看外連接，被驅動表 (a left join b 的 b) 使用了主鍵索引，驅動表作為外層循環先執行 (id相同順序由上至下) ，需要全表掃描不走索引。對于內連接，MySQL以數據記錄少的表作為被驅動表 (笛卡爾積的內層循環)，所以后四列都一樣。ref 的含義則是指用于索引的值來源于哪里，即內存循環走的索引值是來源于外層循環的。

⑦ rows

表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數。

⑧ Extra

顯示那些不適合在其他列中顯示但十分重要的額外信息。可能包含四種信息，如表格所示。

Extra信息含義

Using index	該值表示相應的 select 操作中使用了覆蓋索引。
Using where	表示MySQL服務器將在存儲引擎檢索行后再進行過濾。許多 where 條件里涉及索引中的列，當(并且如果)它讀取索引時，就能被存儲引擎檢驗，因此不是所有帶 where 的查詢都會顯示"Using where"。有時"Using where"的出現就是一個暗示：查詢可受益于不同的索引。
Using temporary	表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢。
Using filesort	MySQL中無法利用索引完成的排序操作稱為“文件排序”。

IV. 高性能索引策略

正確地創建和使用索引是實現高性能查詢的基礎。前面已經著重介紹了MySQL的B-Tree索引，現在我們一起來看看如何真正地發揮索引的優勢。

獨立的列

“獨立的列”是指索引列不能是表達式的一部分，也不能是函數的參數。如下面的例子：

前綴索引vs選擇性

有時候需要索引很長的字符列，這會讓索引變得大且慢。通常可以索引開始的部分字符，這樣可以大大節約索引空間，從而提高索引效率，這樣做其實是犧牲了索引的選擇性。選擇性高的索引可以讓MySQL在查找時過濾掉更多的行。唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。

一般情況下某個列前綴的選擇性也是足夠高的，足以滿足查詢性能。對于BLOB、TEXT或者很長的VARCHAR類型的列，必須使用前綴索引，因為MySQL不允許索引這些列的完整長度。訣竅在于要選擇足夠長的前綴以保證較高的選擇性，同時又不能太長（以便節約空間）。前綴應該足夠長，以使得前綴索引的選擇性接近于索引整個列。

前綴長度到6時選擇性提升已經很微小了，基本接近0.0055。當然只看平均選擇性是不夠的，也有例外的情況，需要考慮最壞情況下的選擇性。比如雖然 count(distinct left(last_name, 6)) 較大，但不代表每一種 last_name 的記錄數量是均勻分布的，可以某些 last_name 數據特別多，那么這種特定的 last_name 查詢的選擇性就很低了。

前綴索引是一種能使索引更小、更快的有效辦法，但另一方面也有其缺點：MySQL無法使用前綴索引做 ORDER BY 和 GROUP BY，也無法使用前綴索引做覆蓋掃描。

多列索引與順序

多列索引并不是給每一個列創建一個索引，而是多個列創建一個組合索引，當然多個列的排列順序也很有講究。在多個列上建立獨立的單列索引大部分情況下并不能提高MySQL的查詢性能。正確的索引列順序依賴于使用該索引的查詢，并且同時需要考慮如何更好地滿足排序和分組的需要。

在一個多列B-Tree索引中，索引列的順序意味著索引首先按照最左列進行排序，其次是第二列，等等。所以，索引可以按照升序或者降序進行掃描，以滿足精確符合列順序的 ORDER BY 、GROUP BY和 DISTINCT 等子句的查詢需求，所以多列索引的列順序至關重要。

當不需要考慮排序和分組時，將選擇性最高的列放在前面通常是很好的。這時候索引的作用只是用于優化WHERE條件的查找。在這種情況下，這樣設計的索引確實能夠最快地過濾出需要的行，對于在WHERE子句中只使用了索引部分前綴列的查詢來說選擇性也更高。然而，性能不只是依賴于所有索引列的選擇性（整體基數），也和查詢條件的具體值有關，也就是和值的分布有關。這和前面介紹的選擇前綴的長度需要考慮的地方一樣。可能需要根據那些運行頻率最高的查詢來調整索引列的順序，讓這種情況下索引的選擇性最高。

聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種數據存儲方式。具體的細節依賴于其實現方式，但InnoDB的聚簇索引實際上在同一個結構中保存了B-Tree索引和數據行。

一個表只能有一個聚簇索引，因為無法同時把數據行存放在兩個不同的地方。MySQL不允許手動指定那個索引為聚簇索引，InnoDB主鍵是聚簇索引，如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB會隱式定義一個主鍵來作為聚簇索引。

聚簇主鍵可能對性能有幫助，但也可能導致嚴重的性能問題。

優點主要如下：

• 可以把相關數據保存在一起。聚簇索引本身就是包含數據的，而不需要在獲取其他數據的時候再去對應的磁盤地址進行讀取，發生磁盤IO。
• 數據訪問更快。聚簇索引將索引和數據保存在同一個B-Tree中，因此從聚簇索引中獲取數據通常比在非聚簇索引中查找要快。
• 使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值。也就是說，覆蓋索引默認包含了主鍵的字段，設計覆蓋索引的時候可以考慮不添加上主鍵字段，因為這是必然要添加的。

當然也存在缺點：

• 聚簇數據最大限度地提高了I/O密集型應用的性能，但如果數據全部都放在內存中，則訪問的順序就沒那么重要了，聚簇索引也就沒什么優勢了。
• 插入速度嚴重依賴于插入順序。按照主鍵的順序插入是加載數據到InnoDB表中速度最快的方式。但如果不是按照主鍵順序加載數據，那么在加載完成后最好使用OPTIMIZE TABLE命令重新組織一下表。
• 更新聚簇索引列的代價很高，因為會強制InnoDB將每個被更新的行移動到新的位置。基于聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能面臨“頁分裂”的問題。因為B-tree索引每個節點占一頁用來存放索引，當新來的一行數據記錄的主鍵值要求必須將這一行插入到某個已滿的頁中時，存儲引擎會將該頁分裂成兩個頁面來容納該行，這就是一次頁分裂操作。頁分裂會導致表占用更多的磁盤空間。
• 聚簇索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導致數據存儲不連續的時候。
• 二級索引（非聚簇索引）可能比想象的要更大，因為在二級索引的葉子節點包含了引用行的主鍵列。非覆蓋索引的二級索引訪問需要二次查詢。

在介紹MyISAM和InnoDB兩種存儲引擎的時候，我們了解到兩種引擎對于數據的組織方式。MyISAM葉子節點存放了“行指針”，指向具體的數據記錄地址。MyISAM按照數據插入的順序存儲在磁盤上，對應的地址被葉子節點記錄即可。反觀InnoDB引擎，數據本身就記錄在主鍵索引的葉子節點上，數據插入的順序完全依據主鍵在整個B-Tree樹該有的位置，如果主鍵是亂序的，那么插入數據的時候就會出現樹的左邊插一個，右邊插一個，這邊插一個，那邊插一個的現象。這么做有什么隱患呢，首先插入時間長，其次占據空間可能更大，碎片化嚴重。具體如下：

• 寫入的目標頁可能已經刷到磁盤上并從緩存中移除，或者是還沒有被加載到緩存中，InnoDB在插入之前不得不先找到并從磁盤讀取目標頁到內存中。這將導致大量的隨機I/O。
• 因為寫入是亂序的，InnoDB不得不頻繁地做頁分裂操作，以便為新的行分配空間。頁分裂會導致移動大量數據，一次插入最少需要修改三個頁而不是一個頁。
• 由于頻繁的頁分裂，頁會變得稀疏并被不規則地填充，所以最終數據會有碎片。

那么，如何利用好InnoDB主鍵順序插入數據的特點呢？

那就是有序的主鍵，比如自增長列。因為主鍵的值是順序的，所以InnoDB把每一條記錄都存儲在上一條記錄的后面。當達到頁的最大填充因子時（InnoDB默認的最大填充因子是頁大小的15/16，留出部分空間用于以后修改），下一條記錄就會寫入新的頁中。一旦數據按照這種順序的方式加載，主鍵頁就會近似于被順序的記錄填滿，這也正是所期望的結果。

覆蓋索引

使用覆蓋索引的好處在于：

• 索引字段數目通常遠小于數據記錄的所有字段數目，所以如果只需要讀取索引，那MySQL就可以極大地減少數據訪問量。這對緩存的負載非常重要，因為這種情況下響應時間大部分花費在數據拷貝上。覆蓋索引對于I/O密集型的應用也有幫助，因為索引比數據更小，更容易全部放入內存中（這對于MyISAM尤其正確，因為MyISAM能壓縮索引以變得更小）。
• 由于InnoDB的聚簇索引，覆蓋索引對InnoDB表特別有用。InnoDB的二級索引在葉子節點中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。

不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引列的值，而哈希索引、空間索引和全文索引等都不存儲索引列的值，所以MySQL只能使用B-Tree索引做覆蓋索引。

InnoDB的二級索引的葉子節點都包含了主鍵的值，這意味著InnoDB的二級索引可以有效地利用這些“額外”的主鍵列來覆蓋查詢。換句話說，二級索引查詢主鍵也是覆蓋索引。

利用索引掃描進行排序

通過B-Tree索引的實現原理，我們可以知道 ORDER BY 排序也是可以從索引獲益的。ORDER BY 子句和查找型查詢的限制是一樣的：需要滿足索引的最左前綴的要求；否則，MySQL都需要執行排序操作，而無法利用索引進行排序。

當然，有一種情況下 ORDER BY 子句可以不滿足索引的最左前綴的要求，就是前導列為常量的時候。如果 WHERE 子句或者 JOIN 子句中對這些列指定了常量，就可以“彌補”索引的不足。

如下圖舉出一些例子。

冗余重復不使用的索引

MySQL允許在相同列上創建多個索引，無論是有意的還是無意的。MySQL需要單獨維護重復的索引，并且優化器在優化查詢的時候也需要逐個地進行考慮，這會影響性能。

重復索引是指在相同的列上按照相同的順序創建的相同類型的索引。應該避免這樣創建重復索引，發現以后也應該立即移除。

冗余索引和重復索引有一些不同。如果創建了索引（A，B），再創建索引（A）就是冗余索引，因為這只是前一個索引的前綴索引。因此索引（A，B）也可以當作索引（A）來使用（這種冗余只是對B-Tree索引來說的）。但是如果再創建索引（B，A），則不是冗余索引，索引（B）也不是，因為B不是索引（A，B）的最左前綴列。

除了冗余索引和重復索引，可能還會有一些服務器永遠不用的索引。這樣的索引完全是累贅，建議考慮刪除。

V. 總結

在MySQL中，大多數情況下都會使用B-Tree索引。其他類型的索引大多只適用于特殊的目的。如果在合適的場景中使用索引，將大大提高查詢的響應時間。本章將不再介紹更多這方面的內容了，最后值得總的回顧一下這些特性以及如何使用B-Tree索引。

在選擇索引和編寫利用這些索引的查詢時，有如下三個原則始終需要記住：

• 單行訪問是很慢的。特別是在機械硬盤存儲中（SSD的隨機I/O要快很多，不過這一點仍然成立）。如果服務器從存儲中讀取一個數據塊只是為了獲取其中一行，那么就浪費了很多工作。最好讀取的塊中能包含盡可能多所需要的行。使用索引可以創建位置引用以提升效率。
• 按順序訪問范圍數據是很快的。這有兩個原因。第一，順序I/O不需要多次磁盤尋道，所以比隨機I/O要快很多，特別是對機械硬盤。第二，如果服務器能夠按需要順序讀取數據，那么就不再需要額外的排序操作，并且GROUP BY 查詢也無須再做排序和將行按組進行聚合計算了。
• 索引覆蓋查詢是很快的。如果一個索引包含了查詢需要的所有列，那么存儲引擎就不需要再回表查找行。這避免了大量的單行訪問。

除了這些，對于 EXPLAIN 的使用也是至關重要的。

參考閱讀

• 什么是CPU密集型、IO密集型?
• 淺談算法和數據結構: 十 平衡查找樹之B樹
• MySQL索引背后的數據結構及算法原理
• 《MySQL DBA修煉之道》
• 《高性能MySQL》

總結

以上是生活随笔為你收集整理的MySQL——索引与EXPLAIN的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。