MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構。
資料庫查詢是資料庫的最主要功能之一，我們都希望查詢數據的速度能儘可能的快，因此資料庫系統的設計者會從查詢算法的角度進行優化，這篇文章對索引做一個系統的梳理，希望對大家有幫助。
一、MySQL有哪些索引類型
索引的分類可以從多個角度進行，下面分別從數據結構，物理存儲和業務邏輯三個維度進行劃分。
1、從數據結構角度
（1）B+樹索引(O(log(n)))
關於B+樹索引，後面會深入解析
（2）hash索引
僅僅能滿足"=","IN"和"<=>"查詢，不能使用範圍查詢其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree 索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次的IO訪問，所以 Hash 索引的查詢效率要遠高於 B-Tree 索引只有Memory存儲引擎顯示支持hash索引（3）FULLTEXT索引
現在MyISAM和InnoDB引擎都支持了
（4）R-Tree索引
用於對GIS數據類型創建SPATIAL索引
2、從物理存儲角度
（1）聚集索引（clustered index）
正文內容按照一個特定維度排序存儲，這個特定的維度就是聚集索引；Innodb存儲引擎中行記錄就是按照聚集索引維度順序存儲的，Innodb的表也稱為索引表；因為行記錄只能按照一個維度進行排序，所以一張表只能有一個聚集索引。（2）非聚集索引（non-clustered index）
索引是通過二叉樹的數據結構來描述的，我們可以這麼理解聚簇索引：索引的葉節點就是數據節點。而非聚簇索引的葉節點仍然是索引節點，只不過有一個指針指向對應的數據塊。非聚集索引索引項順序存儲，但索引項對應的內容卻是隨機存儲的；舉個例子說明下：
該表中主鍵id是該表的聚集索引、name為非聚集索引；表中的每行數據都是按照聚集索引id排序存儲的；比如要查找name='Arla'和name='Arle'的兩個同學，他們在name索引表中位置可能是相鄰的，但是實際存儲位置可能差的很遠。name索引表節點按照name排序，檢索的是每一行數據的主鍵。聚集索引表按照主鍵id排序，檢索的是每一行數據的真實內容。
3、從邏輯角度
（1）主鍵索引
主鍵索引是一種特殊的唯一索引，不允許有空值
（2）普通索引或者單列索引
（3）多列索引（複合索引）
複合索引指多個欄位上創建的索引，只有在查詢條件中使用了創建索引時的第一個欄位，索引才會被使用。使用複合索引時遵循最左前綴集合
（4）唯一索引或者非唯一索引
（5）空間索引
空間索引是對空間數據類型的欄位建立的索引，MYSQL中的空間數據類型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL關鍵字進行擴展，使得能夠用於創建正規索引類型的語法創建空間索引。創建空間索引的列，必須將其聲明為NOT NULL，空間索引只能在存儲引擎為MYISAM的表中創建.
二、索引創建方式
unique|fulltext|spatial為可選參數，分別表示唯一索引、全文索引和空間索引；index和key為同義詞，兩者作用相同，用來指定創建索引col_name為需要創建索引的欄位列，該列必須從數據表中該定義的多個列中選擇；index_name指定索引的名稱，為可選參數，如果不指定，MYSQL默認col_name為索引值；length為可選參數，表示索引的長度，只有字符串類型的欄位才能指定索引長度；asc或desc指定升序或降序的索引值存儲1、創建表時建立索引
（1）創建普通索引
查看表結構
show create table table_name;
可以使 EXPLAIN 語句查看索引是否被使用
explainselect * from table_name whereid = 1\G
（2）創建唯一索引
（3）創建全文索引
全文索引只能在char，varchar或者text 類型的欄位上。而且，只有MyISAM 儲存引擎支持全文索引。
（4）創建單列索引
這裡需要注意的，subject 的長度為255，但是index4_st索引只有10。這樣做的目的還是為了提高查詢速度。對於字符型的數據，可以不用查詢全部信息，只查詢其前面的若干字符信息。
（5）創建多列索引
這是我們可以看到，name 和sex欄位上已經創建了index_ns索引。
2、在已經存在的表中創建索引
（1）創建普通索引>
在example0() 表中的id 創建名為index7_id 的索引。
createindex index7_id on example0(id);
（2）創建唯一索引>
createUNIQUEindex index_name on table_name(name);
（3）創建全文索引>
create FULLTEXT index index_name on table_name(info);
（4）創建單列索引>
createINDEX index_name ON table_name(name(10));
（5）創建多列索引>
createINDEX index_name ON table_name(name,sex);
3、用alter table 語句來創建索引
（1）創建普通索引：
在name欄位上創建名為indx_name 的索引
altertable table_name ADDINDEX index_name(name(20));
（2）創建唯一性索引：
altertable table_name ADDUNIQUEINDEX index_name(id);
（3）創建全文索引：
altertable table_name ADD FULLTEXT INDEX index_name(info);
（4）創建單列索引：
altertable table_name ADDINDEX index_name(name(4));
（5）創建多列索引：
alter tabel table_name ADDINDEX index_name(name.sex);
4、刪除索引
DROPINDEX index_name ON table_name;
三、索引樹是如何維護的
目前大部分資料庫系統及文件系統都採用B-Tree或其變種B+Tree作為索引結構，那麼索引樹是如何維護的？
1、查找結構進化史
查找是數據結構和算法中一個非常重要的概念。
線性查找：一個個找；實現簡單；太慢二分查找：有序；簡單；要求是有序的，插入特別慢HASH查找：查詢快；佔用空間；不太適合存儲大規模數據二叉查找樹：插入和查詢很快(log(n))；無法存大規模數據，複雜度退化平衡樹：解決 BST 退化問題，樹是平衡的；節點非常多的時候，依然樹高很高多路查找樹：一個父親多個孩子節點（度）；節點過多樹高不會特別深多路平衡查找樹：B-Tree2、B-Tree
B-Tree是一種多路搜索樹（並不是二叉的）：
定義任意非葉子結點最多只有M個兒子；且M>2；根結點的兒子數為[2, M]；除根結點以外的非葉子結點的兒子數為[M/2, M]；每個結點存放至少M/2-1（取上整）和至多M-1個關鍵字；（至少2個關鍵字）非葉子結點的關鍵字個數=指向兒子的指針個數-1；非葉子結點的關鍵字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；非葉子結點的指針：P[1], P[2], …, P[M]；其中P[1]指向關鍵字小於K[1]的子樹，P[M]指向關鍵字大於K[M-1]的子樹，其它P[i]指向關鍵字屬於(K[i-1], K[i])的子樹；所有葉子結點位於同一層；每個k對應一個data。如：（M=3）相當於一個2–3樹，2–3樹是一個這樣的一棵樹， 它的每個節點要麼有2個孩子和1個數據元素，要麼有3個孩子和2個數據元素，葉子節點沒有孩子，並且有1個或2個數據元素。
B-樹的搜索，從根結點開始，對結點內的關鍵字（有序）序列進行二分查找，如果命中則結束，否則進入查詢關鍵字所屬範圍的兒子結點；重複，直到所對應的兒子指針為空，或已經是葉子結點；B-Tree上查找算法的偽代碼如下：
（1）B-樹的特性
關鍵字集合分布在整顆樹中；任何一個關鍵字出現且只出現在一個結點中；搜索有可能在非葉子結點結束；其搜索性能等價於在關鍵字全集內做一次二分查找；自動層次控制；（2）B-樹的自控制
B樹中每一個內部節點會包含一定數量的鍵值。通常，鍵值的數量被選定在d和2d之間。在實際中，鍵值佔用了節點中大部分的空間。因數2將保證節點可以被拆分或組合。如果一個內部節點有2d個鍵值，那麼添加一個鍵值給此節點的過程，將會拆分2d鍵值為2個d鍵值的節點，並把此鍵值添加給父節點。每一個拆分的節點需要最小數目的鍵值。相似地，如果一個內部節點和他的鄰居兩者都有d個鍵值，那麼將通過它與鄰居的合併來刪除一個鍵值。刪除此鍵值將導致此節點擁有d-1個鍵值;與鄰居的合併則加上d個鍵值，再加上從鄰居節點的父節點移來的一個鍵值。結果為完全填充的2d個鍵值。
（3）B-樹的構造過程
下面是往B樹中依次插入
6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4
3、B+Tree
B-Tree有許多變種，其中最常見的是B+Tree，MySQL就普遍使用B+Tree實現其索引結構。與B-Tree相比，B+Tree有以下不同點：
非葉子結點的子樹指針與關鍵字個數相同；非葉子結點的子樹指針P[i]，指向關鍵字值屬於[K[i], K[i+1])的子樹（B-樹是開區間）；為所有葉子結點增加一個鏈指針；所有關鍵字都在葉子結點出現；內節點不存儲data，只存儲key如：（M=3）
B+的搜索與B-樹也基本相同，區別是B+樹只有達到葉子結點才命中（B-樹可以在非葉子結點命中），其性能也等價於在關鍵字全集做一次二分查找；
（1）B+的特性
所有關鍵字都出現在葉子結點的鍊表中（稠密索引），且鍊表中的關鍵字恰好是有序的；不可能在非葉子結點命中；非葉子結點相當於是葉子結點的索引（稀疏索引），葉子結點相當於是存儲（關鍵字）數據的數據層；更適合文件索引系統；（2）B+樹的構造過程
下面是往B+樹中依次插入
> 6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4
3、索引的物理存儲
一般來說，索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲的磁碟上。
這樣的話，索引查找過程中就要產生磁碟I/O消耗，相對於內存存取，I/O存取的消耗要高几個數量級，所以評價一個數據結構作為索引的優劣最重要的指標就是在查找過程中磁碟I/O操作次數的漸進複雜度。換句話說，索引的結構組織要儘量減少查找過程中磁碟I/O的存取次數。
假如每個盤塊可以正好存放一個B樹的結點（正好存放2個文件名）。那麼一個BTNODE結點就代表一個盤塊，而子樹指針就是存放另外一個盤塊的地址。
（1）模擬B+樹查找過程
下面，咱們來模擬下查找文件29的過程：
根據根結點指針找到文件目錄的根磁碟塊1，將其中的信息導入內存。【磁碟IO操作 1次】此時內存中有兩個文件名17、35和三個存儲其他磁碟頁面地址的數據。根據算法我們發現：17<29<35，因此我們找到指針p2。根據p2指針，我們定位到磁碟塊3，並將其中的信息導入內存。【磁碟IO操作 2次】此時內存中有兩個文件名26，30和三個存儲其他磁碟頁面地址的數據。根據算法我們發現：26<29<30，因此我們找到指針p2。根據p2指針，我們定位到磁碟塊8，並將其中的信息導入內存。【磁碟IO操作 3次】此時內存中有兩個文件名28，29。根據算法我們查找到文件名29，並定位了該文件內存的磁碟地址。分析上面的過程，發現需要3次磁碟IO操作和3次內存查找操作。關於內存中的文件名查找，由於是一個有序表結構，可以利用折半查找提高效率。至於IO操作是影響整個B樹查找效率的決定因素。當然，如果我們使用平衡二叉樹的磁碟存儲結構來進行查找，磁碟4次，最多5次，而且文件越多，B樹比平衡二叉樹所用的磁碟IO操作次數將越少，效率也越高。
4、B+tree的優點
（1）B+-tree的磁碟讀寫代價更低
B+-tree的內部結點並沒有指向關鍵字具體信息的指針。因此其內部結點相對B 樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入內存中的需要查找的關鍵字也就越多。相對來說IO讀寫次數也就降低了。
舉個例子，假設磁碟中的一個盤塊容納16bytes，而一個關鍵字2bytes，一個關鍵字具體信息指針2bytes。一棵9階B-tree(一個結點最多8個關鍵字)的內部結點需要2個盤快。而B+
樹內部結點只需要1個盤快。當需要把內部結點讀入內存中的時候，B 樹就比B+樹多一次盤塊查找時間(在磁碟中就是碟片旋轉的時間)。
（2）B+-tree的查詢效率更加穩定
由於非終結點並不是最終指向文件內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當。
四、索引創建有哪些原則
索引查詢是資料庫中重要的記錄查詢方法，要不要進入索引以及在那些欄位上建立索引都要和實際資料庫系統的查詢要求結合來考慮，下面給出實際中的一些通用的原則：
在經常用作過濾器的欄位上建立索引；在SQL語句中經常進行GROUP BY、ORDER BY的欄位上建立索引；在不同值較少的欄位上不必要建立索引，如性別欄位；對於經常存取的列避免建立索引；用於聯接的列（主健/外健）上建立索引；在經常存取的多個列上建立複合索引，但要注意複合索引的建立順序要按照使用的頻度來確定；預設情況下建立的是非簇集索引，但在以下情況下最好考慮簇集索引，如：含有有限數目（不是很少）唯一的列；進行大範圍的查詢；充分的利用索引可以減少表掃描I/0的次數，有效的避免對整表的搜索。經常用在WHERE子句中的數據列；經常出現在關鍵字order by、group by、distinct後面的欄位，建立索引。如果建立的是複合索引，索引的欄位順序要和這些關鍵字後面的欄位順序一致，否則索引不會被使用；對於那些查詢中很少涉及的列，重複值比較多的列不要建立索引；對於定義為text、image和bit的數據類型的列不要建立索引；限制表上的索引數目。對一個存在大量更新操作的表，所建索引的數目一般不要超過3個，最多不要超過5個。索引雖說提高了訪問速度，但太多索引會影響數據的更新操作。對複合索引，按照欄位在查詢條件中出現的頻度建立索引。在複合索引中，記錄首先按照第一個欄位排序。對於在第一個欄位上取值相同的記錄，系統再按照第二個欄位的取值排序，以此類推。因此只有複合索引的第一個欄位出現在查詢條件中，該索引才可能被使用,因此將應用頻度高的欄位，放置在複合索引的前面，會使系統最大可能地使用此索引，發揮索引的作用。
1、組合多個索引
一個單獨的索引掃描只能用於這樣的條件子句：使用被索引欄位和索引操作符類中的操作符， 並且這些條件以AND連接。
假設在(a, b)上有一個索引， 那麼類似WHERE a = 5 AND b = 6的條件可以使用索引，但是像WHERE a = 5 OR b = 6的條件就不能直接使用索引。
一個類似WHERE x =42 OR x = 47 OR x = 53 OR x = 99 這樣的查詢可以分解成四個在x上的獨立掃描，每個掃描使用一個條件， 最後將這些掃描的結果OR 在一起，生成最終結果。
另外一個例子是，如果我們在x 和y上有獨立的索引，一個類似WHERE x = 5 AND y = 6 這樣的查詢可以分解為幾個使用獨立索引的子句，然後把這幾個結果AND 在一起，生成最終結果。
五、索引失效有哪幾種情況
如果條件中有or，即使其中有條件帶索引也不會使用(這也是為什麼儘量少用or的原因)對於多列索引，不是使用的第一部分(第一個)，則不會使用索引like查詢是以%開頭如果列類型是字符串，那一定要在條件中將數據使用引號引用起來,否則不使用索引如果mysql估計使用全表掃描要比使用索引快,則不使用索引1、聯合索引失效的條件
聯合索引又叫複合索引。兩個或更多個列上的索引被稱作複合索引。
對於複合索引：Mysql從左到右的使用索引中的欄位，一個查詢可以只使用索引中的一部份，但只能是最左側部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3種組合進行查找，但不支持 b,c進行查找 .當最左側欄位是常量引用時，索引就十分有效。
所以說創建複合索引時，應該仔細考慮列的順序。對索引中的所有列執行搜索或僅對前幾列執行搜索時，複合索引非常有用；僅對後面的任意列執行搜索時，複合索引則沒有用處。
六、如何查看索引的使用情況
這裡記錄兩種方式，分別是
1、使用Handler_read查看索引的使用情況
showstatuslike 『Handler_read%';
大家可以注意：
handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數handler_read_rnd_next:這個值越高，說明查詢低效
分析這幾個值，我們可以查看當前索引的使用情況：
Handler_read_first：索引中第一條被讀的次數。如果較高，它表示伺服器正執行大量全索引掃描；例如，SELECT col1 FROM foo，假定col1有索引（這個值越低越好）。Handler_read_key：如果索引正在工作，這個值代表一個行被索引值讀的次數，如果值越低，表示索引得到的性能改善不高，因為索引不經常使用（這個值越高越好）。Handler_read_next ：按照鍵順序讀下一行的請求數。如果你用範圍約束或如果執行索引掃描來查詢索引列，該值增加。Handler_read_prev：按照鍵順序讀前一行的請求數。該讀方法主要用於優化ORDER BY ... DESC。Handler_read_rnd ：根據固定位置讀一行的請求數。如果你正執行大量查詢並需要對結果進行排序該值較高。你可能使用了大量需要MySQL掃描整個表的查詢或你的連接沒有正確使用鍵。這個值較高，意味著運行效率低，應該建立索引來補救。Handler_read_rnd_next：在數據文件中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明你的表索引不正確或寫入的查詢沒有利用索引。2、在sys庫中查看沒用的索引
查詢 schema_unused_indexes庫。
七、EXPLAIN解釋命令查看索引是否生效
explain顯示了mysql如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。
1、一個實際例子
新建一張表，
執行查詢，
響應數據如下，
type 是 ALL,即最壞的情況。Extra 裡還出現了 Using filesort,也是最壞的情況。
2、EXPLAIN列的解釋：
table：顯示這一行的數據是關於哪張表的type：這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALLpossible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句key： 實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MYSQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MYSQL忽略索引key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數rows：MYSQL認為必須檢查的用來返回請求數據的行數Extra：關於MYSQL如何解析查詢的額外信息。將在表4.3中討論，但這裡可以看到的壞的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，結果是檢索會很慢3、type返回結果的解釋
MySQL 在表裡找到所需行的方式。包括（由左至右，由最差到最好）：
| All | index | range | ref | eq_ref | const,system | null |
system 表只有一行：system表。這是const連接類型的特殊情況const:表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行，這個值實際就是常數，因為MYSQL先讀這個值然後把它當做常數來對待eq_ref:在連接中，MYSQL在查詢時，從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用ref:這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊前綴）時發生。對於之前的表的每一個行聯合，全部記錄都將從表中讀出。這個類型嚴重依賴於根據索引匹配的記錄多少—越少越好range:這個連接類型使用索引返回一個範圍中的行，比如使用>或<查找東西時發生的情況index: 這個連接類型對前面的表中的每一個記錄聯合進行完全掃描（比ALL更好，因為索引一般小於表數據）ALL:這個連接類型對於前面的每一個記錄聯合進行完全掃描，這一般比較糟糕，應該儘量避免
4、extra列返回的描述的意義
Distinct:一旦MYSQL找到了與行相聯合匹配的行，就不再搜索了Not exists: MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了Range checked for each Record（index map:#）:沒有找到理想的索引，因此對於從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，並用它來從表中返回行。這是使用索引的最慢的連接之一Using filesort: 看到這個的時候，查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行Using index: 列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候Using temporary 看到這個的時候，查詢需要優化了。這裡，MYSQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上Using where 使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，並且連接類型ALL或index，這就會發生，或者是查詢有問題不同連接類型的解釋（按照效率高低的順序排序）