索引是存儲引擎用於快速查找記錄的一種數據結構，我們可以通過合理的使用數據庫索引以提高數據庫的訪問效率。接下來主要介紹在MySQL 數據庫中索引類型，以及如何創建出更加合理且高效的索引技巧。
MySQL數據庫的內部索引是由不同的存儲引擎實現的，本文主要介紹一下 InnoDB存儲引擎中的索引，InnoDB引擎中的索引是使用 B+樹 的結構來存儲的。
InnoDB引擎中的B+樹結構
接下來我們看一下B+ 樹結構，如下圖：
首先，說一下B+ 樹的特點：
- 葉子節點（即最下面的一層）存儲關鍵字（索引字段的值）信息及對應的整行數據記錄，即葉子節點存儲了所有記錄的關鍵字信息。
- 非葉子節點只存儲關鍵字的信息及子節點的指針。
- 每個葉子節點相當於MySQL中的一個數據頁，同層級的葉子節點以雙向鏈表的形式相連。
- 每頁中存儲了多條記錄，記錄之間使用單鏈表連接組成了一條有序的鏈表，順序是按照索引字段排序的。
- B+ 樹中查詢數據時，每次查詢都是從 根節點開始，一直需要搜索到葉子節點。
MySQL中頁是InnoDB引擎中存儲數據的基本單位（塊是文件系統操作的最小單位，扇區是磁盤操作的最小單位），數據是按數據頁為單位來讀寫的，和磁盤交互的時候都是以頁來進行的，每個頁的大小默認是16kb。也就是說，當需要讀取一條記錄的時候，並不是將這個記錄本身從磁盤讀取出來，而是以頁為單位，將整個也加載到內存中，一個頁中可能有很多記錄，然後在內存中對頁通過二分法進行查詢。
整體上來說MySQL中的索引用到了B+樹，鏈表，二分法查找，做到了快速定位目標數據，快速範圍查找。
InnoDB引擎中的索引類型
InnoDB引擎中有2種索引類型：主鍵索引（聚集索引）、輔助索引（非聚集索引） 。
- 主鍵索引：每個表只有一個主鍵索引，葉子節點同時保存了主鍵的值以及對應記錄的數據。
- 輔助索引：葉子節點保存了索引字段的值以及主鍵的值，與聚簇索引的區別在於輔助索引的葉子節點中存放的是主鍵索引的鍵值。
如下Person 表，id 作為主鍵索引，name 作為輔助索引。
結合如上圖中 Person表，InnoDB引擎數據查詢過程如下：
如果需要查詢 id=1 的數據，只需要通過主鍵索引（聚集索引）中查詢就可以了。
如果需要查詢 name='Jacy' 的數據，需要使用非聚集索引與聚集索引，需要2步：
- 首先，通過輔助索引中查詢 name='Jacy' 的數據，獲取id的值為 12。
- 然後，根據id再到主鍵索引中查詢 id=12 的數據記錄。
如上，這個查詢過程在MySQL中叫做 回表，下面我們會具體介紹回表。
聚集索引（主鍵索引）
聚集索引中鍵值的邏輯順序決定了表中相應行的物理順序（索引中的數據物理存放地址和索引的順序是一致的），可以這麼理解：只要是索引是連續的，那麼數據在存儲介質上的存儲位置也是連續的。 比方說：想要到字典上查找一個字，我們可以根據字典前面的拼音找到該字，注意拼音的排列時有順序的。
聚集索引就像我們根據拼音的順序查字典類似，可以大大的提高效率。在經常搜索一定範圍的值時，通過索引找到第一條數據，根據物理地址連續存儲的特點，然後查詢相鄰的數據，直到到達條件截止項。
每個表一定會有一個聚集索引，整個表的數據存儲以B+ 樹的方式存在文件中，葉子節點中的key為主鍵值，data為完整的整行記錄的信息，非葉子節點存儲主鍵的值。
通過聚集索引查詢數據只需要按照B+ 樹的搜索過程，即可以查詢到對應的記錄。聚集索引按照如下規則創建：
- 當定義了主鍵後，InnoDB會利用主鍵來生成其聚集索引。
- 如果沒有主鍵，InnoDB會選擇第一個非空的唯一索引來創建聚集索引。
- 如果合適的非空的唯一索引，InnoDB會隱式的創建一個6個字節的自增的列來作為聚集索引，該列的值會隨著數據的插入自增。
非聚集索引
索引的邏輯順序與磁盤上的物理存儲順序不同。非聚集索引的鍵值在邏輯上也是連續的，但是表中的數據在存儲介質上的物理順序是不一致的，即記錄的邏輯順序和實際存儲的物理順序沒有任何聯繫。索引的記錄節點有一個數據指針指向真正的數據存儲位置。
非聚集索引就像根據偏旁部首查字典一樣，字典前面的目錄在邏輯上也是連續的，但是查兩個偏旁在目錄上挨著的字時，字典中的字卻很不可能是挨著的。
每個表可以有多個非聚集索引，B+ 樹結構，葉子節點的key為索引字段的值，data為主鍵的值。非葉子節點只存儲索引字段的值。
通過非聚集索引查詢記錄的時候，可能需要2次操作，先在非聚集索引中查詢出主鍵，然後再到聚集索引中查詢出主鍵對應的行記錄，也就是進行兩次 B+樹查詢。
InnoDB引擎中B+樹的數據查詢過程
我們在查詢過程中，當使用多個索引時，InnoDB引擎使用的哪個索引，為什麼有時候雖然使用了索引，但看執行計劃卻顯示沒有使用索引，這弄清楚這些之前。我們先看一下B+ 樹查詢數據的過程。
主鍵或唯一索引查詢
如上圖，所有的數據都是唯一的，我們查詢 26 的記錄，過程如下：
- 將Data page1頁加載到內存，在內存中採用二分法查找。
- 確定26位於[20,40) 中間，然後再去加載20關聯的 Data page3頁。
- 將Data page3頁加載到內存中，通過採用二分法找到26 的記錄後退出。
非唯一索引查詢
如上圖，數據為並不是唯一的，我們查詢26 的所有記錄，過程如下：
- 將Data page1頁加載到內存，在內存中通過二分法查找。
- 確定26位於 [20,40)中間，然後再去加載20關聯的 Data page3 頁。
- 將Data page3 頁加載到內存中，通過二分法找到最後一個小於 26的記錄，即 23，然後通過鏈表從23 開始向後訪問，找到所有的26記錄，直到遇到第一個大於26的值時停止。
範圍查詢
如上圖，查詢 [25,45] 所有記錄，由於數據頁之間是雙向鏈表升序結構，頁內部的數據是單項升序鏈表結構，所以只用找到範圍的起始值所在的位置，然後通過依靠鏈表訪問兩個位置之間所有的數據即可，過程如下：
- 將Data page1 頁加載到內存，在內存中通過二分法查找。
- 確定25位於 20關聯的Data page3 頁中，45位於40關聯的Data page4 頁中。
- 將Data page3 頁加載到內存中，通過二分法找到第一個25的記錄，然後通過鏈表結構繼續向後訪問Data page3頁中的 26，當Data page3頁訪問完畢之後，通過Data page3頁的nextpage指針訪問下一頁Data page4 頁，同樣加載到內存中，通過二分法查找所有小於45的記錄。
模糊匹配查詢
如上圖，我們查詢以 b 字母開頭的所有記錄，過程如下：
- 將 Data page1 頁數據加載到內存中。
- 通過二分法查找最後一個小於等於b 的值，即b，以及第一個大於b的，即z。b指向葉節點Data page3 頁，z指向葉節點Data page5 頁，確定以f開頭的記錄可能存在於[Data page3,Data page5)這個範圍的頁內，即Data page3、Data page4 這兩頁中。
- 依次加載Data page3 到內存中，通過二分法找到第一條b 開頭的記錄，然後以鏈表方式繼續向後訪問Data page4 頁中的記錄，即可以找到所有以 b 開頭的記錄。
當我們在SQL中使用LIKE %b%全模糊查詢時，執行過程是什麼樣的呢？
如上圖，b在每個頁中都存在，我們通過Data page1 頁中的記錄是無法判斷包含b的記錄在哪些頁的，只能加載所有葉子節點（頁），然後遍歷所有記錄進行過濾，才可以找到包含b 的記錄。所以如果使用了LIKE %b%全模糊查詢，索引對查詢是無效的。
複合索引的最左匹配原則
當B+ 樹的數據項是複合的數據結構，比如(name,age,sex) 的時候，B+ 樹是按照從左到右的順序來建立搜索樹的，比如當使用 (Tony, 20, 男) 查詢時，B+ 樹會優先比較 name 來確定下一步的查詢方向，如果name 相同再依次比較 age 和sex ，最後得到查詢的數據。
但使用(20, 男)這樣的沒有name的數據來查詢的時候，B+ 樹則不知道下一步該查哪個節點，因為建立搜索樹的時候name 就是第一個索引字段，必須要先根據name 來搜索才能知道下一步去哪裡查詢。
比如當使用 (Tony, 男) 查詢時，B+ 樹可以用name 來指定搜索方向，但下一個字段age 的缺失，所以只能把名字等於Tony 的數據都找到，然後再匹配性別是男 的數據了， 即索引的最左匹配特性，如上圖。
同時，在上圖中，將 a, b, c 3個字段建立為複合索引(a,b,c)，索引中數據的順序是以a asc, b asc, c asc這種排序方式存儲在節點中的，索引先以a字段升序，如果a相同的時候，再以b字段升序，b相同的時候，再以c字段升序。
我們分別看下，當使用以下字段進行查詢時，查詢過程是什麼樣子的。
- 查詢 a=1 的記錄
由於頁中的記錄是以a asc,b asc,c asc這種排序方式存儲的，所以a字段是有序的，可以通過二分法快速查詢到，過程如下：
1.將Data page 1加載到內存中，通過二分法查找，可以確定a=1的記錄位於{1,1,1}和{1,6,1}內，關聯Data page2與Data page3 頁。
2.加載Data page 2 頁到內存中，通過二分法快速找到第一條a=1的記錄，然後通過鏈表向下一條及下一頁Data page4 頁進行查詢，直到找到第一個不滿足a=1的記錄為止。
- 查詢 a=1 and b=6 的記錄
首先可以確定a=1 and b=6的記錄位於{1,6,1}內，關聯Data page3 頁並加載到內存中，後續查找過程和a=1 查找步驟類似。
- 查詢 c=1 的記錄
這種情況通過Data page 1頁中的記錄，無法判斷c=1的記錄在哪些頁中的，只能加載索引樹所有葉子節點，對所有記錄進行遍歷，然後進行過濾，此時索引是無效的。
- 查詢 b=1 and c=1 的記錄
同上，這種也無法利用索引，只能進行全表掃描，此時索引無效。
- 查詢 a=1 and c=1 的記錄
這種只能利用到索引中的a字段，通過a 確定索引範圍，然後加載a關聯的所有記錄，再對c的值進行判斷過濾。
- 查詢 a=1 and b>=0 and c=1 的記錄
這種情況只能先確定a=1 and b>=0所在頁範圍，然後對這個範圍的所有頁進行遍歷，c字段在這個查詢的過程中，是無法確定c的數據在哪些頁的，此時c的索引失效，只有a、b能夠有效的確定索引頁的範圍。
總結，對於複合索引失效的可能原因有以下幾點：
複合索引的生效原則是從前往後依次使用生效，如果中間某個索引沒有使用，那麼僅斷點前面的索引部分起作用，斷點後面的索引不起作用，造成斷點的原因一般有：
- 前邊的任意一個索引沒有參與查詢，後面的不生效。
- 前邊的任意一個索引失效，當前索引及後面全部不生效。
- 前邊的任意一個索引字段參與的是範圍查詢（>、
索引區分度
如上圖，上面是兩個有序的數組，都是10條記錄，如果我們需要查詢值為6 的所有記錄，查詢這兩個數組哪個更快一些？
我們使用二分法查找包含6 的所有記錄過程如下：先使用二分法找到最後一個小於6的記錄，然後從這條記錄向後獲取下一個記錄，依次與6 比較，直到遇到第一個大於6 的數字結束，或者到達數組末尾結束，通過這種方法找到6 的記錄，第一個數組的查詢更快的一些。因為第二個數組中含有6的比例更多的，需要訪問以及匹配的次數更多一些。
這裡涉及數據的區分度問題：索引區分度 = count(distint 字段) / count(字段)。
當索引區分度高的時候，查詢數據要更快一些。當索引區分度太低，說明重複的數據比較多，查詢的時候基本上接近於全索引數據的掃描了，此時查詢速度是比較慢的。
如上圖中，第一個數組的索引區分度為 0.9，第二個數組的索引區分度為0.2，所以第一個有序數組的查詢效率更快一些。
結合實例理解索引的正確使用方式
為了更好地理解上述內容，我們以如下測試數據 nickname_information 表為例，其包含編號、姓名、性別、暱稱 4個字段，其中除性別字段存在重複值，其餘各字段均不重複，共300萬條測試數據。
包含多個索引時，查詢如何選擇？
我們在name、sex 兩個字段上分別創建索引 idx_name，idx_sex，如下：
查詢姓名為testops1000001 並且性別為女的所有信息：
我麼可以看到執行之間不到1ms，name與sex都是索引字段，那麼實際執行時使用的是哪個索引？
我們或許會說是根據WHERE 子句後的索引字段順序，name 位於WHERE 第一個，所以走的是name字段所在的索引？執行過程如下：
- 首先，根據name 所在的索引找到testops1000001 對應的主鍵索引。
- 然後，根據主鍵索引查詢所有數據記錄。
- 最後，遍歷所有數據記錄過濾出sex=1 的值。
我們看一下 name='testops1000001' 查詢速度，如下：
那麼索引的選擇真的與WHERE子句的索引字段順序有關麼？我們把name 和sex 的順序對調一下，如下：
查詢速度仍然很快，這次是不是先通過sex 索引查詢出數據，然後再過濾name 呢？我們再來看一下sex=1查詢速度：
看上面，查詢耗時220毫秒，150萬數據，因此，肯定不是使用的sex 的索引。我們使用 explain來看一下SQL執行計劃。
我們通過執行計劃，可以看到該SQL中可能使用的索引（possible_keys）包含idx_name,idx_sex ，但實際使用的索引（key）是idx_name 。
因此，當WHERE子句中包含多個索引字段並且關係是 AND 時，會使用索引區分度高的索引字段，如上例子中，顯然name 字段不存在重複度，並且age 字段的重複度很高，因此會使用name 查詢會更快一些。
模糊查詢
如上兩個SQL查詢語句，第一個使用後模糊查詢（'testops1000%'），第二個使用全模糊查詢（%testops1000%'）。
第一個SQL語句可以利用到name 字段上面的索引，第二個SQL語句因無法確定需要查找的值所在的範圍的，只能全表掃描，無法利用索引，所以速度比較慢。
WHERE 子句中使用 LIKE 進行模糊查詢時，在關鍵詞前加通配符或者前後都加通配號都無法使用索引，從而引發全表掃描。解決LIKE '%abc%' 時索引不被使用的方法就是添加覆蓋索引（只訪問索引的查詢，索引和查詢列一致，只需掃描索引而無須回表）。
回表
當需要查詢的字段在索引樹中不存在時（非索引字段），需要再次到聚集索引中去獲取，這個過程叫做回表，如查詢：
如上SQL查詢語句中查詢是所有字段，由於name 列所在的索引中只有name、id（主鍵索引） 兩個列的值，不包含sex、nickname，所以上面過程如下：
- 首先，通過name 索引查詢 testops1000001 對應的記錄，取出id為1000001。
- 然後，在主鍵索引中查詢 id=1000001 的記錄，獲取整行中所有字段的值。
索引覆蓋
當查詢中採用的索引樹中包含了要查詢的所有字段，不需要再去聚集索引查詢數據，這種叫索引覆蓋。或者說查詢語句的執行只需要從輔助索引中就可以得到查詢記錄，而不需要查詢聚集索引中的記錄。
如上SQL查詢語句中，name 對應idx_name 索引，id為主鍵索引，所以idx_name 索引樹葉子節點中包含了id、name的值，這個查詢只用走 idx_name這一個索引就可以了。若改為查詢全部字段，還需要一次回表獲取sex、nickname的值，則不是索引覆蓋。
所以設計SQL的時候，儘量避免使用'*'，可能會多一次回表操作，同時需要關注是否可以使用索引覆蓋來實現，效率更高一些。
索引下推
Index Condition Pushdown(ICP) 是MySQL 5.6中新特性，是一種在存儲引擎層使用索引過濾數據的一種優化方式，ICP可以減少存儲引擎訪問基表的次數以及MySQL服務器訪問存儲引擎的次數。
舉個例子，我們需要查詢name以 testops1000 開頭的並且性別為男 的記錄數，SQL語句如下：
如上SQL查詢語句，存在2種可能執行的過程：
第一種執行方式，如下：
- 首先，通過name 索引查詢出以testops1000 的第一條記錄，得到記錄的 id。
- 然後，利用id 去主鍵索引中查詢出這條記錄T1。
- 最後，判斷 T1.sex 是否為1，然後重複上面的操作，直到找到所有記錄為止。
上面的過程中需要走name索引以及需要回表操作。
第二種執行方式，通過ICP的方式，我們可以這麼做，創建一個(name, sex) 的組合索引，查詢過程如下：
- 首先，通過(name,sex) 索引查詢出以 testops1000 的第一條記錄，可以得到(name, sex, id)，記做T1。
- 然後，判斷 T1.sex 是否為1，然後重複上面的操作，知道找到所有記錄為止。
這個過程中不需要回表操作了，通過索引的數據就可以完成整個條件的過濾，速度比上面的更快一些。若當我們select全部字段時，索引下推可以減少回表查詢的範圍次數。
需要注意的是：索引下推一般可用於所求查詢字段（SELECT列）不是或者不全是聯合索引的字段，查詢條件為多條件查詢且查詢條件子句（WHERE、ORDER BY）字段全是聯合索引。
假設表table1 有聯合索引（a,b）,下面語句可以使用索引下推提高效率 SELECT * FROM table1 WHERE a > 2 AND b > 10;
類型錯誤使索引失效
如上SQL查詢語句中，第2條查詢很快，第三條用name 和 10086 比較，name為索引字段且字段類型為字符串類型，當字符串和數字比較的時候，會將字符串強制轉換為數字，然後進行比較，所以第3個查詢變成了全表掃描。
那麼如果字段類型是數字類型，查詢時使用字符串類型，會怎樣？如下：
id上面有主鍵索引，id 是int 類型的，可以看到，上面兩個查詢都非常快，都可以正常利用索引快速查詢，所以如果字段是數字類型，查詢的值無論是字符串還是數組都會走索引。
函數使索引失效
name字段上建立索引，如上SQL查詢語句中，第一個查詢SQL使用索引，第二個查詢SQL不使用索引。
第二個SQL對name 字段使用了concat 函數之後，name所在的索引樹無法快速定位需要查找的數據所在的頁，只能將所有頁的記錄加載到內存中，然後對每條數據使用concat 函數進行計算之後再進行條件判斷，此時索引失效，進行全表數據掃描。
應儘量避免在 WHERE 子句中對 “=” 左邊的字段進行函數表達式運算，可以將表達式運算移至“=”右邊，否則將導致引擎放棄使用索引而進行全表掃描。
運算符使索引失效
id字段上建立主鍵索引，如上SQL查詢語句中，第一個查詢SQL使用索引，第二個查詢SQL不使用索引。
第二個SQL中對id 使用運算符，id所在的索引樹是無法快速定位需要查找的數據所在的頁，只能將所有頁的記錄加載到內存中，然後對每條數據的id 進行計算之後再判斷是否等於 2，此時索引失效，進行全表數據掃描。
應儘量避免在 WHERE 子句中對 “=” 左邊的字段進行算術運算，可以將表達式運算移至“=”右邊，否則將導致引擎放棄使用索引而進行全表掃描。
索引的一些建議
- 複合索引要遵守最佳左前綴原則，必須按照從左到右的順序匹配，MySQL會一直向右匹配直到遇到斷點(>、 2 and c = 3 如果建立(a,b,c)順序的索引，c則用不到索引的。
- 查詢記錄的時候，避免使用*，儘量利用索引覆蓋，減少回表操作，提升效率。
- 索引下推一般可用於查詢條件為多條件查詢且查詢條件子句（WHERE、ORDER BY）字段全是複合索引，可以減少回表操作，提升效率。
- 避免對索引字段使用函數、運算符操作，會使索引失效。
- 類型錯誤，如字段num類型為varchar，WHERE條件用NUM = 1,會使索引失效。
- 模糊查詢LIKE 以通配符開頭如，%ab，會使索引無效，變為全表掃描，但是'值%'這種可以有效利用索引。
- 排序中儘量使用到索引字段，這樣可以減少排序，提升查詢效率。
- 在區分度高的字段上面建立索引可以有效的使用索引，區分度太低，無法有效的利用索引，可能需要掃描所有數據頁，此時和不使用索引幾乎無區別。
閱讀更多 軟件測試開發技術棧 的文章
