深入MySQL索引，這篇千萬不能錯過

大家好，我是【碼老思】，索引是一個資料庫繞不開的話題，今天和大家一起聊聊。

1. 索引

索引是對資料庫表中一列或多列的值進行排序的一種結構。 MySQL索引的建立對於MySQL的高效執行是很重要的，索引可以大大提高MySQL的檢索速度。索引只是提高效率的一個因素，如果你的MySQL有巨量資料量的表，就需要花時間研究建立最優秀的索引，或優化查詢語句。

簡單類比一下，資料庫如同書籍，索引如同書籍目錄，假如我們需要從書籍查詢與 xx 相關的內容，我們可以直接從目錄中查詢，定位到 xx 內容所在頁面，如果目錄中沒有 xx 相關字元或者沒有設定目錄（索引），那隻能逐字逐頁閱讀文字查詢，效率可想而知。

1.1 索引優缺點

優點：

以常見的B+樹索引為例，按照順序儲存資料。總結來看有如下三個優點：

索引大大減少了伺服器需要掃描的資料量。
索引可以幫助伺服器避免排序和建立臨時表。
索引可以將Order By等隨機IO轉為順序IO。

缺點：

建立索引和維護索引（發生資料的增、刪、改時）要耗費時間，這種時間隨著資料量的增加而增加
索引需要佔物理空間，除了資料表佔用資料空間之外，每一個索引還要佔用一定的物理空間，如果需要建立聚簇索引，那麼需要佔用的空間會更大
對於非常小的表，大部分情況下簡單的全表掃描更高效；

1.2 索引的原理

MySQL中的資料是儲存在磁碟上的，引入索引的目的就是提高存取磁碟上資料的效率。

那麼這裡先簡單介紹一下磁碟IO和預讀，磁碟讀取資料靠的是機械運動，每次讀取資料花費的時間可以分為尋道時間、旋轉延遲、傳輸時間三個部分。

尋道時間指的是磁臂移動到指定磁軌所需要的時間，主流磁碟一般在5ms以下；
旋轉延遲就是硬碟通過碟片的旋轉，使得要讀取的磁區轉到讀寫頭的下方所需要花費的時間的平均值，比如一個磁碟7200轉，表示每分鐘能轉7200次，也就是說1秒鐘能轉120次，旋轉延遲就是1/120 / 2 = 4.17ms；
傳輸時間指的是從磁碟讀出或將資料寫入磁碟的時間，一般在零點幾毫秒，相對於前兩個時間可以忽略不計。

那麼存取一次磁碟的時間，即一次磁碟IO的時間約等於5+4.17 = 9ms左右，聽起來還挺不錯的，但要知道一臺500 -MIPS的機器每秒可以執行5億條指令，因為指令依靠的是電的性質，換句話說執行一次IO的時間可以執行40萬條指令，資料庫動輒十萬百萬乃至千萬級資料，每次9毫秒的時間，顯然是個災難。考慮到磁碟IO是非常高昂的操作，計算機作業系統做了一些優化，當一次IO時，不光把當前磁碟地址的資料，而是把相鄰的資料也都讀取到記憶體緩衝區內，因為區域性預讀性原理告訴我們，當計算機存取一個地址的資料的時候，與其相鄰的資料也會很快被存取到。每一次IO讀取的資料我們稱之為一頁(page)。具體一頁有多巨量資料跟作業系統有關，一般為4k或8k，也就是我們讀取一頁內的資料時候，實際上才發生了一次IO，這個理論對於索引的資料結構設計非常有幫助。

如果有一種資料結構，每次查詢資料時把磁碟IO次數控制在一個很小的數量級，最好是常數數量級，這樣就能大大提高資料的存取效率，在這種背景下，索引應運而生。

在資料庫中，索引是分很多種類的。而不同的種類很顯然是為了應付不同的場合，這裡介紹幾種常見的索引底層資料結構。

Hash表

雜湊表是做資料快速檢索的有效利器。雜湊演演算法也叫雜湊演演算法，就是把任意值(key)通過雜湊函數變換為固定長度的 key 地址，然後通過這個地址來獲取具體資料。

但是雜湊演演算法有個資料碰撞的問題，也就是雜湊函數可能對不同的 key 會計算出同一個結果，解決碰撞問題的一個常見處理方式就是鏈地址法或者當連結串列過長時轉為紅黑樹（JDK1.8的HashMap的處理方法）。

雜湊演演算法只需要計算一次就可以獲取到對應的資料，檢索速度非常快。但是 Mysql 並沒有採取雜湊作為其底層演演算法，因為雜湊演演算法沒辦法做資料高效範圍查詢，因此不適合作為MySQL的底層索引結構。

自適應Hash索引

自適應Hash索引（Adatptive Hash Index，內部簡稱AHI）是InnoDB的三大特性之一，還有兩個是 Buffer Pool簡稱BP、雙寫緩衝區 Doublewrite Buffer。

自適應Hash索引 = 自適應 + hash索引： 1、自適應即我們不需要自己處理，當InnoDB引擎根據查詢統計發現某一查詢滿足hash索引的資料結構特點，就會給其建立一個hash索引；2、hash索引底層的資料結構是雜湊表（Hash表），其資料特點就是比較適合在記憶體中使用，自適應Hash索引存在於InnoDB架構中的快取中（不存在於磁碟架構中），見下面的InnoDB架構圖

Innodb儲存引擎會監控對錶上二級索引的查詢，如果發現某二級索引被頻繁存取，二級索引成為熱資料，建立雜湊索引可以帶來速度的提升，則：

經常存取的二級索引資料會自動被生成到hash索引裡面去(最近連續被存取三次的資料)，自適應雜湊索引通過緩衝池的B+樹構造而來，因此建立的速度很快。

InnoDB的自適應Hash索引是預設開啟的，可以通過設定引數設定：innodb_adaptive_hash_index = off進行關閉。

自適應Hash演演算法的也有明顯的缺點：

1、hash自適應索引會佔用innodb buffer pool；
2、自適應hash索引只適合搜尋等值的查詢，如select * from table where index_col='xxx'，而對於其他查詢型別，如範圍查詢，是不能使用的；
3、極端情況下，自適應hash索引才有比較大的意義，可以降低邏輯讀。

二叉查詢樹

二叉查詢樹是一種支援資料快速查詢的資料結構，如圖下所示:

二叉查詢樹的時間複雜度是 O(logN)，也可以實現範圍查詢，但是普通的二叉查詢樹有個致命缺點：極端情況下會退化為線性連結串列，二分查詢也會退化為遍歷查詢，時間複雜退化為 O（N），檢索效能急劇下降。

在資料庫中，資料的自增是一個很常見的形式，比如一個表的主鍵是 id，而主鍵一般預設都是自增的，如果採取二元樹這種資料結構作為索引，那上面介紹到的不平衡狀態導致的線性查詢的問題必然出現。因此，簡單的二叉查詢樹存在不平衡導致的檢索效能降低的問題，是不能直接用於實現 Mysql 底層索引的。

紅黑樹

紅黑樹是一種自平衡二叉查詢樹，通過在插入和刪除節點時進行顏色變換和旋轉操作，使得樹始終保持平衡狀態，它具有以下特點：

每個節點非紅即黑；
根節點總是黑色的；
每個葉子節點都是黑色的空節點（NIL 節點）；
如果節點是紅色的，則它的子節點必須是黑色的（反之不一定）；
從根節點到葉節點或空子節點的每條路徑，必須包含相同數目的黑色節點（即相同的黑色高度）。

紅黑樹並不追求嚴格的平衡，而是大致的平衡，一定程度上解決了二叉查詢樹可能的平衡問題。但是紅黑樹的平衡性相對較弱，可能會導致樹的高度較高，也會導致一些資料需要進行多次磁碟 IO 操作才能查詢到，這也是 MySQL 沒有選擇紅黑樹的主要原因。（在資料庫中主鍵自增，主鍵一般都是數百萬數千萬的，這種情況下可能導致紅黑樹一直存在右傾的趨勢，長此以往嚴重影響查詢效能）

因為紅黑樹在插入和刪除節點時只需進行 O(1) 次數的旋轉和變色操作，即可保持基本平衡狀態，TreeMap、TreeSet 以及 JDK1.8 的 HashMap 底層都用到了紅黑樹。

AVL樹

AVL 樹的特點是保證任何節點的左右子樹高度之差不超過 1，因此也被稱為高度平衡二元樹，它的查詢、插入和刪除在平均和最壞情況下的時間複雜度都是 O(logN)。

AVL樹採用了旋轉操作來保持平衡，雖然解決了二叉查詢樹可能存在的不平衡問題，但是需要頻繁地進行旋轉操作（複雜度O（logN））來保持平衡，因此會有較大的計算開銷進而降低了查詢效能。

而且在使用 AVL 樹時，每個樹節點僅儲存一個資料，而每次進行磁碟 IO 時只能讀取一個節點的資料，如果需要查詢的資料分佈在多個節點上，那麼就需要進行多次磁碟 IO。

磁碟 IO 有個有個特點，就是從磁碟讀取 1B 資料和 1KB 資料所消耗的時間是基本一樣的，我們就可以根據這個思路，我們可以在一個樹節點上儘可能多地儲存資料，一次磁碟 IO 就多載入點資料到記憶體，這就是 B 樹，B+樹的的設計原理了。

B樹

下面這個 B 樹，每個節點限制最多儲存兩個 key，一個節點如果超過兩個 key 就會自動分裂。比如下面這個儲存了 7 個資料 B 樹，只需要查詢兩個節點就可以知道 id=7 這資料的具體位置，也就是兩次磁碟 IO 就可以查詢到指定資料，優於 AVL 樹。

下面是一個儲存了 16 個資料的 B 樹，同樣每個節點最多儲存 2 個 key，查詢 id=16 這個資料需要查詢比較 4 個節點，也就是經過 4 次磁碟 IO。看起來查詢效能與 AVL 樹一樣。

但是考慮到磁碟 IO 讀一個資料和讀 100 個資料消耗的時間基本一致，那我們的優化思路就可以改為：儘可能在一次磁碟 IO 中多讀一點資料到記憶體。這個直接反映到樹的結構就是，每個節點能儲存的 key 可以適當增加。

當我們把單個節點限制的 key 個數設定為 6 之後，一個儲存了 7 個資料的 B 樹，查詢 id=7 這個資料所要進行的磁碟 IO 為 2 次。

一個儲存了 16 個資料的 B 樹，查詢 id=7 這個資料所要進行的磁碟 IO 為 2 次。相對於 AVL 樹而言磁碟 IO 次數降低為一半。

所以資料庫索引資料結構的選型而言，B 樹是一個很不錯的選擇。總結來說，B 樹用作資料庫索引有以下優點：

優秀檢索速度，時間複雜度：B 樹的查詢效能等於 O（h * logN），其中 h 為樹高，n 為每個節點關鍵詞的個數；
儘可能少的磁碟 IO，加快了檢索速度；
可以支援範圍查詢。

B+樹

為了進一步的樹的高度，在每一個節點記憶體儲更多的資料，引入B+樹來解決這個問題。

相比B樹，B+樹有幾個特點：

B 樹一個節點裡存的是資料，而 B+樹儲存的是索引（地址），所以 B+樹一個節點能存很多索引。
B+樹葉子節點會存放所有的資料，而所有葉子節點之間，用了一個連結串列串聯起來，便於基於範圍查詢。

通過 B 樹和 B+樹的對比我們看出，B+樹節點儲存的是索引，在單個節點儲存容量有限的情況下，單節點也能儲存大量索引，使得整個 B+樹高度降低，減少了磁碟 IO。其次，B+樹的葉子節點是真正資料儲存的地方，葉子節點用了連結串列連線起來，這個連結串列本身就是有序的，在資料範圍查詢時，更具備效率。因此 Mysql 的索參照的就是 B+樹，B+樹在查詢效率、範圍查詢中都有著非常不錯的效能。

為什麼選擇B+樹來實現索引？

總結來講，主要有以下4點原因：

B+ 樹的層級更少：相較於 B 樹 B+ 每個非葉子節點儲存的關鍵字數更多，樹的層級更少所以查詢資料更快
B+ 樹查詢速度更穩定：B+ 所有關鍵字資料地址都存在葉子節點上，所以每次查詢的次數都相同所以查詢速度要比B樹更穩定;
B+ 樹天然具備排序功能：B+ 樹所有的葉子節點資料構成了一個有序連結串列，在查詢大小區間的資料時候更方便，資料緊密性很高，快取的命中率也會比B樹高。
B+ 樹全節點遍歷更快：B+ 樹遍歷整棵樹只需要遍歷所有的葉子節點即可，，而不需要像 B 樹一樣需要對每一層進行遍歷，這有利於資料庫做全表掃描。

B+樹一個節點有多大？3層B+樹能存放多少資料？

InnoDB儲存引擎也有自己的最小儲存單元——頁（Page），一個頁的大小預設是16K。B+樹一個節點的大小設為一頁或頁的倍數最為合適。因為如果一個節點的大小 < 1頁，那麼讀取這個節點的時候其實讀取的還是一頁，這樣就造成了資源的浪費。 在 MySQL 中 B+ 樹的一個節點大小為「1頁」，也就是16K。之所以設定為一頁，是因為對於大部分業務，一頁就足夠了。

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

接下來可以大概估計下3層B+樹能存放多少資料，首先InnoDB的B+樹中，非葉子節點存的是key + 指標；葉子節點存的是資料行。
對於葉子節點，如果一行資料大小為1k，那麼一頁就能存16條資料；對於非葉子節點，如果key使用的是bigint，則為8位元組，指標在mysql中為6位元組，一共是14位元組，則16k能存放 16 * 1024 / 14 = 1170 個索引指標。於是可以算出，對於一顆高度為2的B+樹，根節點儲存索引指標節點，那麼它有1170個葉子節點儲存資料，每個葉子節點可以儲存16條資料，一共 1170 x 16 = 18720 條資料，具體可以參考下圖。

而對於高度為3的B+樹，就可以存放 1170 x 1170 x 16 = 21902400 條資料（兩千多萬條資料），也就是對於兩千多萬條的資料，我們只需要高度為3的B+樹就可以完成，通過主鍵查詢只需要3次IO操作就能查到對應資料。所以在 InnoDB 中B+樹高度一般為3層時，就能滿足千萬級的資料儲存，所以一個節點為1頁，也就是16k是比較合理的。

MyISM和InnoDB中的索引實現

這兩個引擎底層資料和索引的組織方式並不一樣，MyISAM 引擎把資料和索引分開了，一人一個檔案，這叫做非聚集索引方式；Innodb 引擎把資料和索引放在同一個檔案裡了，這叫做聚集索引方式。從兩者建表之後生成的檔案就可以看出來：

Innodb 建立表後生成的檔案有：

frm:建立表的語句
idb:表裡面的資料+索引檔案

MyISAM 建立表後生成的檔案有：

frm:建立表的語句
MYD:表裡面的資料檔案（myisam data）
MYI:表裡面的索引檔案（myisam index）

1. MyISAM引擎的底層實現（非聚集索引方式）

MyISAM 用的是非聚集索引方式，即資料和索引落在不同的兩個檔案上。MyISAM 在建表時以主鍵作為 KEY 來建立主索引 B+樹，樹的葉子節點存的是對應資料的實體地址。我們拿到這個實體地址後，就可以到 MyISAM 資料檔案中直接定位到具體的資料記錄了。

當我們為某個欄位新增索引時，我們同樣會生成對應欄位的索引樹，該欄位的索引樹的葉子節點同樣是記錄了對應資料的實體地址，然後也是拿著這個實體地址去資料檔案裡定位到具體的資料記錄。

2. InnoDB 引擎的底層實現（聚集索引方式）

InnoDB 是聚集索引方式，因此資料和索引都儲存在同一個檔案裡。首先 InnoDB 會根據主鍵 ID 作為 KEY 建立索引 B+樹，如左下圖所示，而 B+樹的葉子節點儲存的是主鍵 ID 對應的資料，比如在執行 select * from user_info where id=15 這個語句時，InnoDB 就會查詢這顆主鍵 ID 索引 B+樹，找到對應的 user_name='Bob'。

這是建表的時候 InnoDB 就會自動建立好主鍵 ID 索引樹，這也是為什麼 Mysql 在建表時要求必須指定主鍵的原因。當我們為表裡某個欄位加索引時 InnoDB 會怎麼建立索引樹呢？比如我們要給 user_name 這個欄位加索引，那麼 InnoDB 就會建立 user_name 索引 B+樹，節點裡存的是 user_name 這個 KEY，葉子節點儲存的資料的是主鍵 KEY。拿到主鍵 KEY 後，InnoDB 才會去主鍵索引樹里根據剛在 user_name 索引樹找到的主鍵 KEY 查詢到對應的資料。

總結對比

InnoDB 和 MyISAM 相比而言，MyISAM 查詢效能更好。MyISAM 直接找到實體地址後就可以直接定位到資料記錄，但是 InnoDB 查詢到葉子節點後，還需要再查詢一次主鍵索引樹，才可以定位到具體資料（針對非主鍵的查詢）。等於 MyISAM 一步就查到了資料，但是 InnoDB 要兩步，那當然 MyISAM 查詢效能更高。

InnoDB中的主鍵索引為聚集索引，其他索引均為非聚集索引。主要原因在於，一個表裡可能有很多個索引，InnoDB 都會給每個加了索引的欄位生成索引樹，如果每個欄位的索引樹都儲存了具體資料，那麼這個表的索引資料檔案就會非常大，有很多資料冗餘。採用現有的設計方案，能夠在犧牲較少查詢效能的前提下，節省大量的磁碟空間。另外，如果和MyISAM一樣在主鍵索引和輔助索引的葉子節點中都存放資料指標，一旦資料發生遷移，則需要去重新組織維護所有的索引，這也會產生巨大的開銷。

1.3 索引分類

按照資料結構維度劃分：

BTree 索引：MySQL 裡預設和最常用的索引型別。只有葉子節點儲存 value，非葉子節點只有指標和 key。儲存引擎 MyISAM 和 InnoDB 實現 BTree 索引都是使用 B+Tree，但二者實現方式不一樣（前面已經介紹了）。
雜湊索引：類似鍵值對的形式，一次即可定位。
RTree 索引：一般不會使用，僅支援 geometry 資料型別，優勢在於範圍查詢，效率較低，通常使用搜尋引擎如 ElasticSearch 代替。
全文索引：對文字的內容進行分詞，進行搜尋。目前只有 CHAR、VARCHAR ，TEXT 列上可以建立全文索引。一般不會使用，效率較低，通常使用搜尋引擎如 ElasticSearch 代替。

按照物理儲存方式劃分：

聚簇索引（聚集索引）：索引結構和資料一起存放的索引，InnoDB 中的主鍵索引就屬於聚簇索引。
非聚簇索引（非聚集索引）：索引結構和資料分開存放的索引，二級索引(輔助索引)就屬於非聚簇索引。MySQL 的 MyISAM 引擎，不管主鍵還是非主鍵，使用的都是非聚簇索引。

按照應用維度劃分：

主鍵索引：加速查詢 + 列值唯一（不可以有 NULL）+ 表中只有一個。
普通索引：僅加速查詢。
唯一索引：加速查詢 + 列值唯一（可以有 NULL）。
覆蓋索引：一個索引包含（或者說覆蓋）所有需要查詢的欄位的值。
聯合索引：多列值組成一個索引，專門用於組合搜尋，其效率大於索引合併。
全文索引：對文字的內容進行分詞，進行搜尋。目前只有 CHAR、VARCHAR ，TEXT 列上可以建立全文索引。一般不會使用，效率較低，通常使用搜尋引擎如 ElasticSearch 代替。

聚集索引與非聚集索引

聚集索引和非聚集索引的根本區別是表記錄的排列順序和與索引的排列順序是否一致。聚集索引的葉節點就是資料節點。而非聚簇索引的葉節點仍然是索引節點，只不過有一個指標指向對應的資料塊

1. 聚簇索引（Clustered Index）：聚集索引表記錄的排列順序和索引的排列順序一致（以InnoDB聚集索引的主鍵索引來說，葉子節點中儲存的就是行資料，行資料在物理儲器中的真實地址就是按照主鍵索引樹形成的順序進行排列的），所以查詢效率快，只要找到第一個索引值記錄，其餘就連續性的記錄在物理也一樣連續存放。

聚集索引對應的缺點就是修改慢，因為為了保證表中記錄的物理和索引順序一致，在記錄插入的時候，會對資料頁重新排序（因為在真實物理記憶體的儲存順序只能有一種，而插入新資料必然會導致主鍵索引樹的變化，主鍵索引樹的順序發生了改變，葉子節點中儲存的行資料也要隨之進行改變，就會發生大量的資料移動操作，所以效率會慢）。因為在實體記憶體中的順序只能有一種，所以聚集索引在一個表中只能有一個。

2. 非聚簇索引（Clustered Index）：非聚集索引制定了表中記錄的邏輯順序，但是記錄的物理和索引不一定一致（在邏輯上資料是按順序排存放的，但是物理上在真實的記憶體中是雜湊存放的），兩種索引都採用B+樹結構，非聚集索引的葉子層並不和實際資料頁相重疊，而採用葉子層包含一個指向表中的記錄在資料頁中的指標方式。

非聚集索引層次多，不會造成資料重排。所以如果表的讀操作遠遠多於寫操作，那麼就可以使用非聚集索引。但非聚集索引有一個很大的缺點就是可能產生二次查詢，也就是回表，當查到索引對應的指標或主鍵後，可能還需要根據指標或主鍵再到資料檔案或表中查詢。

主鍵索引

資料表的主鍵列使用的就是主鍵索引。一張資料表有隻能有一個主鍵，並且主鍵不能為 null，不能重複。

在 MySQL 的 InnoDB 的表中，當沒有顯示的指定表的主鍵時，InnoDB 會自動先檢查表中是否有唯一索引且不允許存在 null 值的欄位，如果有，則選擇該欄位為預設的主鍵，否則 InnoDB 將會自動建立一個 6Byte 的自增主鍵。

二級索引（輔助索引）

二級索引（Secondary Index）又稱為輔助索引，是因為二級索引的葉子節點儲存的資料是主鍵。也就是說，通過二級索引，可以定位主鍵的位置。

唯一索引，普通索引，字首索引等索引屬於二級索引。

唯一索引(Unique Key):唯一索引也是一種約束。唯一索引的屬性列不能出現重複的資料，但是允許資料為 NULL，一張表允許建立多個唯一索引。建立唯一索引的目的大部分時候都是為了該屬性列的資料的唯一性，而不是為了查詢效率。
普通索引(Index):普通索引的唯一作用就是為了快速查詢資料，一張表允許建立多個普通索引，並允許資料重複和 NULL。
字首索引(Prefix):字首索引只適用於字串型別的資料。字首索引是對文字的前幾個字元建立索引，相比普通索引建立的資料更小，因為只取前幾個字元。
全文索引(Full Text):全文索引主要是為了檢索大文字資料中的關鍵字的資訊，是目前搜尋引擎資料庫使用的一種技術。Mysql5.6 之前只有 MYISAM 引擎支援全文索引，5.6 之後 InnoDB 也支援了全文索引。

覆蓋索引和聯合索引

覆蓋索引： 如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱之為 覆蓋索引（Covering Index） 。我們知道在 InnoDB 儲存引擎中，如果不是主鍵索引，葉子節點儲存的是主鍵+列值。最終還是要「回表」，也就是要通過主鍵再查詢一次，這樣就會比較慢。而覆蓋索引直接從輔助索引中就能得到查詢的記錄，而不需要查詢聚集索引中的記錄。

這裡舉個例子，我們有一張user表，Id列上有主鍵索引，同時在Name列上建立了輔助索引，

如上圖，如果通過name進行資料檢索：

select * from users where name = ?

需要需要在name索引中找到name對應的Id，然後通過獲取的Id在主鍵索引中查到對應的行。整個過程需要掃描兩次索引，一次name，一次id。如果我們查詢只想查詢id的值，就可以改寫SQL為：

select id from users where name = ?

因為只需要id的值，通過name查詢的時候，掃描完name索引，我們就能夠獲得id的值了，所以就不需要再去掃面id索引，就會直接返回。當然，如果你同時需要獲取age的值：

select id，age from users where name = ?

這樣就無法使用到覆蓋索引了。

知道了覆蓋索引，就知道了為什麼sql中要求儘量不要使用select *，要寫明具體要查詢的欄位。其中一個原因就是在使用到覆蓋索引的情況下，不需要進入到資料區，資料就能直接返回，提升了查詢效率。在用不到覆蓋索引的情況下，也儘可能的不要使用select *，如果行資料量特別多的情況下，可以減少資料的網路傳輸量。

聯合索引： 指對錶上的多個列進行索引，在使用聯合索引進行查詢時，會遵守下面提到的最左匹配原則。

為什麼要使用聯合索引？

1. 減少開銷。建一個聯合索引 (col1,col2,col3)，實際相當於建了 (col1)，(col1,col2)，(col1,col2,col3) 三個索引。每多一個索引，都會增加寫操作的開銷和磁碟空間的開銷。對於大量資料的表，使用聯合索引會大大的減少開銷！
2. 覆蓋索引。對聯合索引 (col1,col2,col3)，如果有如下的 SQL：select col1,col2,col3 from test where col1=1 and col2=2;。那麼 MySQL 可以直接通過遍歷索引取得資料，而無需回表，這減少了很多的隨機 IO 操作。減少 IO 操作，特別的隨機 IO 其實是 DBA 主要的優化策略。所以，在真正的實際應用中，覆蓋索引是主要的提升效能的優化手段之一。
3. 效率高。索引列越多，通過索引篩選出的資料越少。有 1000W 條資料的表，有如下 SQL：select from table where col1=1 and col2=2 and col3=3，假設假設每個條件可以篩選出 10% 的資料，如果只有單值索引，那麼通過該索引能篩選出 1000W10%=100w 條資料，然後再回表從 100w 條資料中找到符合 col2=2 and col3=3 的資料，然後再排序，再分頁；如果是聯合索引，通過索引篩選出 1000w * 10% * 10% * 10% =1w，效率提升可想而知！

最左匹配原則：

最左字首匹配原則指的是，在使用聯合索引時，MySQL 會根據聯合索引中的欄位順序，從左到右依次到查詢條件中去匹配，如果查詢條件中存在與聯合索引中最左側欄位相匹配的欄位，則就會使用該欄位過濾一批資料，直至聯合索引中全部欄位匹配完成，或者在執行過程中遇到範圍查詢（如 >、< ）才會停止匹配。對於 >=、<=、BETWEEN、like 字首匹配 的範圍查詢，並不會停止匹配。所以，我們在使用聯合索引時，可以將區分度高的欄位放在最左邊，這也可以過濾更多資料。

例如：如果建立(a,b)順序的索引，我們的條件只有b=xxx，是匹配不到(a,b)索引的；但是如果查詢條件是a = 1 and b = 2或者b=2 and a=1就可以，因為優化器會自動調整a,b的順序，並不需要嚴格按照索引的順序來；再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，因為c欄位是一個範圍查詢，它之後的欄位會停止匹配。

為什麼會形成最左匹配原則？

首先要知道，最左匹配原則都是針對聯合索引來說的，所以我們有必要了解一下聯合索引的原理。瞭解了聯合索引，那麼為什麼會有最左匹配原則這種說法也就理解了。

我們都知道索引的底層是一顆B+樹，那麼聯合索引當然還是一顆B+樹，只不過聯合索引的健值數量不是一個，而是多個。構建一顆B+樹只能根據一個值來構建，因此資料庫依據聯合索引最左的欄位來構建B+樹。
例子：假如建立一個（a,b)的聯合索引，那麼它的索引樹是這樣的：

可以看到a的值是有順序的，1，1，2，2，3，3，而b的值是沒有順序的1，2，1，4，1，2。所以b = 2這種查詢條件沒有辦法利用索引，因為聯合索引首先是按a排序的，b是無序的。同時我們還可以發現在a值相等的情況下，b值又是按順序排列的，但是這種順序是相對的。所以最左匹配原則遇上範圍查詢就會停止，剩下的欄位都無法使用索引。例如a = 1 and b = 2 a,b欄位都可以使用索引，因為在a值確定的情況下b是相對有序的，而a>1and b=2，a欄位可以匹配上索引，但b值不可以，因為a的值是一個範圍，在這個範圍中b是無序的。

1.4 高效能索引策略

(1) 選擇合適的欄位建立索引

應該建立索引的列

在經常需要搜尋的列上，可以加快搜尋的速度，但建立索引時要避免新增不必要的欄位。
在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構
在經常用在連線（JOIN）的列上，這些列主要是一外來鍵，可以加快連線的速度
在經常需要根據範圍（<，<=，=，>，>=，BETWEEN，IN）進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的
在經常需要排序（order by）的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；如果待排序的列有多個，可以在這些列上建立組合索引。
在經常使用在WHERE子句中的列上面建立索引，加快條件的判斷速度。

不該建立索引的列

對於那些在查詢中很少使用或者參考的列不應該建立索引。若列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。
對於那些只有很少資料值或者重複值多的列也不應該增加索引。這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的資料行佔了表中資料行的很大比例，即需要在表中搜尋的資料行的比例很大。增加索引，並不能明顯加快檢索速度。
對於那些定義為text, image和bit資料型別的列不應該增加索引。這些列的資料量要麼相當大，要麼取值很少。

(2) 避免為頻繁更新的欄位建立索引

主要考慮索引的維護成本，雖然索引能帶來查詢上的效率，但是維護索引的成本也是不小的。如果一個欄位不被經常查詢，反而被經常修改，那麼就更不應該在這種欄位上建立索引了。

(3) 索引不是越多越好

索引並不是越多越好，建議單張表索引不超過 5 個，索引可以提高效率同樣可以降低效率。索引可以增加查詢效率，但同樣也會降低插入和更新的效率，甚至有些情況下會降低查詢效率。

因為 MySQL 優化器在選擇如何優化查詢時，會根據統一資訊，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用於查詢，就會增加 MySQL 優化器生成執行計劃的時間，同樣會降低查詢效能。

(4) 考慮建立聯合索引

利用好最左匹配原則。在建立一個N列的聯合索引時，實際是建立了MySQL可利用的N個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。另外，如果是聯合索引，多個欄位在一個索引上，那麼將會節約很大磁碟空間，且修改資料的操作效率也會提升。

在建立聯合索引的時候，注意避免索引的冗餘。比如在同一張表上建立兩個索引（a）和(a, b)，能夠命中索引(a, b)就肯定能命中索引(a) ，那麼索引(a)就是冗餘索引。

(5) 字串使用字首索引

使用短索引。如果對字串列進行索引，應該指定一個字首長度，只要有可能就應該這樣做。例如，有一個CHAR(200)列，如果在前10個或20個字元內，多數值是唯一的，那麼就不要對整個列進行索引。對前10個或20個字元進行索引能夠節省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁碟 IO 較少，較短的值比較起來更快。更為重要的是，對於較短的鍵值，索引快取記憶體中的塊能容納更多的鍵值，因此，MySQL 也可以在記憶體中容納更多的值。這樣就增加了找到行而不用讀取索引中較多塊的可能性。

假設這裡有一個user表，裡面有一個欄位叫做email，如何給email欄位新增索引呢？我們可以對比下兩種方式，

alter table user add index index1(email) 
alter table user add index index2(email(6))

上面兩種方式都是在新增索引，不同點就是第二種新增的僅僅是郵箱的字首索引,因此在建立對應索引的B+樹時，下面這種字首索引只會用到email的前6個欄位。但是我們選擇的長度需要具有區分度，這個例子中，如果不同行資料的email欄位前6個字元基本都一致，那樣的話字首索引就沒有起到很好的效果，雖然節省了空間，但是查詢時間會變長。

那麼如何選擇合適的字首長度呢？建立索引之前，我們要關注字首欄位的區分度，區分度越大，效能越高，意味著重複的值就越少。這樣既可以節省空間，也可以不用增加更多的查詢成本。

(6) 避免索引失效

一般來講，索引失效的情況有如下10種情況：

違反最左匹配法則：如果索引有多列，要遵守最左字首法則，即查詢從索引的最左前列開始並且不跳過索引中的列。
在索引列上進行操作：如計算、函數、（自動or手動）型別轉換等操作，會導致索引失效從而全表掃描。
索引範圍條件右邊的列：索引範圍條件右邊的索引列會失效，例如聯合索引中，某個列使用了>或者<的範圍比較，後續的欄位將不會使用索引。
儘量使用覆蓋索引：SELECT * 不會直接導致索引失效，但它可能會帶來一些其他的效能問題比如造成網路傳輸和資料處理的浪費、無法使用索引覆蓋;
使用不等於：mysql在使用不等於（!=、<>）的時候無法使用索引會導致全表掃描（除覆蓋索引外）;
like以萬用字元開頭：例如'%abc'，相反如果使用'abc%'索引可以生效。
字串不加單引號導致索引失效。
使用or連線：查詢條件中使用 or，且 or 的前後條件中有一個列沒有索引，涉及的索引都不會被使用到;
order by和group by的欄位違法最左匹配法則或者非索引欄位，會導致額外的檔案排序(order by)和生成臨時表(group by)，降低效能。

(7) 刪除未使用的索引

刪除長期未使用的索引，不用的索引的存在會造成不必要的效能損耗。MySQL 5.7 可以通過查詢 sys 庫的 schema_unused_indexes 檢視來查詢哪些索引從未被使用。

1.5 索引優化

1. 索引下推

索引下推(Index Condition Pushdown，簡稱ICP)，是MySQL5.6版本的新特性，它能減少回表查詢次數，提高查詢效率。 從下面的架構圖可以看到，MySQL服務層負責SQL語法解析、生成執行計劃等，並呼叫儲存引擎完成資料的儲存和檢索。

索引下推就是將部分上層（服務層）負責的事情，交給了下層（引擎層）去處理。

沒有ICP時，MySQL的查詢可以分成如下幾步：

儲存引擎讀取索引記錄；
根據索引中的主鍵值，定位並讀取完整的行記錄；
儲存引擎把記錄交給Server層去檢測該記錄是否滿足WHERE條件。

使用ICP的情況下，查詢過程：

儲存引擎讀取索引記錄（不是完整的行記錄）；
判斷WHERE條件部分能否用索引中的列來做檢查，條件不滿足，則處理下一行索引記錄；
條件滿足，使用索引中的主鍵去定位並讀取完整的行記錄（就是所謂的回表）；
儲存引擎把記錄交給Server層，Server層檢測該記錄是否滿足WHERE條件的其餘部分。

舉例子說明一下，假設我們需要從user表中根據name和age來查詢使用者，你可能使用這樣的query，

select * from tuser where name like '張%' and age=10;

在沒有使用ICP的情況下，在MySQL 5.6之前，儲存引擎根據通過聯合索引找到name like '張%' 的主鍵id（1、4），逐一進行回表掃描，去聚簇索引找到完整的行記錄，server層再對資料根據age=10進行篩選。

可以看到需要回表兩次，把我們聯合索引的另一個欄位age浪費了。

MySQL 5.6 以後，儲存引擎根據（name，age）聯合索引，找到name like '張%'，由於聯合索引中包含age列，所以儲存引擎直接再聯合索引裡按照age=10過濾。按照過濾後的資料再一一進行回表掃描。

可以看到只回表了一次。

2. MRR優化

MRR，全稱「Multi-Range Read Optimization」，簡單來講，MRR 通過把「隨機磁碟讀」，轉化為「順序磁碟讀」，從而提高了索引查詢的效能。

1. 沒有MRR優化的讀取效果

假設沒有MRR優化，當我們在執行下面的操作時，MySQL 會按照下圖的方式，去磁碟讀取資料（假設資料不在資料緩衝池裡）：

圖中紅色線就是整個的查詢過程，藍色線則是磁碟的運動路線。這張圖是按照 Myisam 的索引結構畫的，不過對於 Innodb 也同樣適用。

對於 Myisam，左邊就是欄位 age 的二級索引，右邊是儲存完整行資料的地方。先到左邊的二級索引找，找到第一條符合條件的記錄（實際上每個節點是一個頁，一個頁可以有很多條記錄，這裡我們假設每個頁只有一條），接著到右邊去讀取這條資料的完整記錄。這樣會導致大量的離散讀操作，由於MySQL是以頁為單位進行資料讀取的，如果這幾條資料不在相同的頁上，還會造成緩衝池中的頁不斷的被替換出去，嚴重影響讀取效率。MRR就是為了減少這種離散讀的操作，在存取磁碟獲取完整的資料之前，在緩衝區將需要存取的行按照主鍵id進行排序，然後再去順序的讀取磁碟，將這種重複離散讀取的行為降到最低。

2. 有MRR優化的讀取效果

有MRR之後，可以看到在讀取行資料之前，多了一個排序的過程。

對於 MyISAM，在去磁碟獲取完整資料之前，會先按照 rowid 排好序，再去順序的讀取磁碟。
對於 Innodb，則會按照聚簇索引鍵值排好序，再順序的讀取聚簇索引。

總結來講，MRR的幾個優點：

1、磁碟和磁頭不再需要來回做機械運動，將隨機存取轉化為順序存取。對於IO密集型的SQL查詢語句，能帶來巨大的效能提升。

2、可以充分利用磁碟預讀。

比如在使用者端請求一頁的資料時，可以把後面幾頁的資料也一起返回，放到資料緩衝池中，這樣如果下次剛好需要下一頁的資料，就不再需要到磁碟讀取。這樣做的理論依據是電腦科學中著名的區域性性原理：當一個資料被用到時，其附近的資料也通常會馬上被使用。

3、在一次查詢中，每一頁的資料只會從磁碟讀取一次。

參考：

歡迎關注公眾號【碼老思】，獲取最通俗易懂的原創技術乾貨。