索引是存儲引擎用于快速查找記錄的一種資料結構，我們可以通過合理的使用資料庫索引以提高資料庫的通路效率。接下來主要介紹在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，會使索引無效，變為全表掃描，但是'值%'這種可以有效利用索引。
- 排序中盡量使用到索引字段，這樣可以減少排序，提升查詢效率。
- 在區分度高的字段上面建立索引可以有效的使用索引，區分度太低，無法有效的利用索引，可能需要掃描所有資料頁，此時和不使用索引幾乎無差別。