索引是什麼？
索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分），它們包含著對數據表裡所有記錄的引用指針；是幫助MySQL高效獲取數據的數據結構。
有什麼作用？
以字典為例，我們要查詢一個字詞的時候，就需要通過字典目錄定位到相關的頁碼進行查找，從而加快查找速度；索引也就可以理解為數據的『目錄』，能夠通過它加快資料庫的查詢速度；
索引分類：主鍵索引、唯一索引、普通索引、全文索引、組合索引
1、主鍵索引：即主索引，根據主鍵pk_clolum（length）建立索引，不允許重複，不允許空值
ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col');
2、唯一索引：用來建立索引的列的值必須是唯一的，允許空值
ALTER TABLE 'table_name' ADD UNIQUE INDEX index_name('col');
3、普通索引：用表中的普通列構建的索引，沒有任何限制
ALTER TABLE 'table_name' ADD INDEX index_name('col');
4、全文索引：用大文本對象的列構建的索引（下一部分會講解）
ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col');
5、組合索引：用多個列組合構建的索引，這多個列中的值不允許有空值
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');
遵循「最左前綴」原則，把最常用作為檢索或排序的列放在最左，依次遞減，組合索引相當於建立了col1,col1col2,col1col2col3三個索引，而col2或者col3是不能使用索引的。
在使用組合索引的時候可能因為列名長度過長而導致索引的key太大，導致效率降低，在允許的情況下，可以只取col1和col2的前幾個字符作為索引
ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3));
表示使用col1的前4個字符和col2的前3個字符作為索引
索引的實現原理MySQL支持諸多存儲引擎，而各種存儲引擎對索引的支持也各不相同，因此MySQL資料庫支持多種索引類型，如BTree索引，B+Tree索引，哈希索引，全文索引等等
1、哈希索引：
只有memory（內存）存儲引擎支持哈希索引，哈希索引用索引列的值計算該值的hashCode，然後在hashCode相應的位置存執該值所在行數據的物理位置，因為使用散列算法，因此訪問速度非常快，但是一個值只能對應一個hashCode，而且是散列的分布方式，因此哈希索引不支持範圍查找和排序的功能。
2、全文索引：
FULLTEXT（全文）索引，僅可用於MyISAM和InnoDB，針對較大的數據，生成全文索引非常的消耗時間和空間。對於文本的大對象，或者較大的CHAR類型的數據，如果使用普通索引，那麼匹配文本前幾個字符還是可行的，但是想要匹配文本中間的幾個單詞，那麼就要使用LIKE %word%來匹配，這樣需要很長的時間來處理，響應時間會大大增加，這種情況，就可使用時FULLTEXT索引了，在生成FULLTEXT索引時，會為文本生成一份單詞的清單，在索引時及根據這個單詞的清單來索引。FULLTEXT可以在創建表的時候創建，也可以在需要的時候用ALTER或者CREATE INDEX來添加：
//創建表的時候添加FULLTEXT索引
CTREATE TABLE my_table(
id INT(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
my_text TEXT,
FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
//創建表以後，在需要的時候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);
全文索引的查詢也有自己特殊的語法，而不能使用LIKE %查詢字符串%的模糊查詢語法
SELECT * FROM table_name MATCH(ft_index) AGAINST('查詢字符串');
注意：
對於較大的數據集，把數據添加到一個沒有FULLTEXT索引的表，然後添加FULLTEXT索引的速度比把數據添加到一個已經有FULLTEXT索引的表快。
5.6版本前的MySQL自帶的全文索引只能用於MyISAM存儲引擎，如果是其它數據引擎，那麼全文索引不會生效。5.6版本之後InnoDB存儲引擎開始支持全文索引
在MySQL中，全文索引只對英文有用，目前對中文還不支持。5.7版本之後通過使用ngram插件開始支持中文。
在MySQL中，如果檢索的字符串太短則無法檢索得到預期的結果，檢索的字符串長度至少為4位元組，此外，如果檢索的字符包括停止詞，那麼停止詞會被忽略。
更深入的理解參考文章：全文索引的深入理解
3、BTree索引和B+Tree索引
BTree索引
BTree是平衡搜索多叉樹，設樹的高度為2d（d>1），高度為h，那麼BTree要滿足以下條件：
每個葉子結點的高度一樣，等於h；
每個非葉子結點由n-1個key和n個指針point組成，其中d<=n<=2d,key和point相互間隔，結點兩端一定是key；
葉子結點指針都為null；
非葉子結點的key都是[key,data]二元組，其中key表示作為索引的鍵，data為鍵值所在行的數據；
BTree的結構如下：
在BTree的機構下，就可以使用二分查找的查找方式，查找複雜度為h*log(n)，一般來說樹的高度是很小的，一般為3左右，因此BTree是一個非常高效的查找結構。
BTree的查詢、插入、刪除過程可以參考：https://blog.csdn.net/endlu/article/details/51720299
B+Tree索引
B+Tree是BTree的一個變種，設d為樹的度數，h為樹的高度，B+Tree和BTree的不同主要在於：
B+Tree中的非葉子結點不存儲數據，只存儲鍵值；
B+Tree的葉子結點沒有指針，所有鍵值都會出現在葉子結點上，且key存儲的鍵值對應data數據的物理地址；
B+Tree的每個非葉子節點由n個鍵值key和n個指針point組成；
B+Tree的結構如下：
B+Tree對比BTree的優點：
1、磁碟讀寫代價更低
一般來說B+Tree比BTree更適合實現外存的索引結構，因為存儲引擎的設計專家巧妙的利用了外存（磁碟）的存儲結構，即磁碟的最小存儲單位是扇區（sector），而作業系統的塊（block）通常是整數倍的sector，作業系統以頁（page）為單位管理內存，一頁（page）通常默認為4K，資料庫的頁通常設置為作業系統頁的整數倍，因此索引結構的節點被設計為一個頁的大小，然後利用外存的「預讀取」原則，每次讀取的時候，把整個節點的數據讀取到內存中，然後在內存中查找，已知內存的讀取速度是外存讀取I/O速度的幾百倍，那麼提升查找速度的關鍵就在於儘可能少的磁碟I/O，那麼可以知道，每個節點中的key個數越多，那麼樹的高度越小，需要I/O的次數越少，因此一般來說B+Tree比BTree更快，因為B+Tree的非葉節點中不存儲data，就可以存儲更多的key。
2、查詢速度更穩定
由於B+Tree非葉子節點不存儲數據（data），因此所有的數據都要查詢至葉子節點，而葉子節點的高度都是相同的，因此所有數據的查詢速度都是一樣的。
更多作業系統內容參考：
硬碟結構
扇區、塊、簇、頁的區別
作業系統層優化（進階，初學不用看）
很多存儲引擎在B+Tree的基礎上進行了優化，添加了指向相鄰葉節點的指針，形成了帶有順序訪問指針的B+Tree，這樣做是為了提高區間查找的效率，只要找到第一個值那麼就可以順序的查找後面的值。
B+Tree的結構如下：
聚簇索引和非聚簇索引
分析了MySQL的索引結構的實現原理，然後我們來看看具體的存儲引擎怎麼實現索引結構的，MySQL中最常見的兩種存儲引擎分別是MyISAM和InnoDB，分別實現了非聚簇索引和聚簇索引。
聚簇索引的解釋是:聚簇索引的順序就是數據的物理存儲順序
非聚簇索引的解釋是:索引順序與數據物理排列順序無關
（這樣說起來並不好理解，讓人摸不著頭腦，請繼續看下文，並在插圖下方對上述兩句話有解釋）
首先要介紹幾個概念，在索引的分類中，我們可以按照索引的鍵是否為主鍵來分為「主索引」和「輔助索引」，使用主鍵鍵值建立的索引稱為「主索引」，其它的稱為「輔助索引」。因此主索引只能有一個，輔助索引可以有很多個。
MyISAM——非聚簇索引
MyISAM存儲引擎採用的是非聚簇索引，非聚簇索引的主索引和輔助索引幾乎是一樣的，只是主索引不允許重複，不允許空值，他們的葉子結點的key都存儲指向鍵值對應的數據的物理地址。
非聚簇索引的數據表和索引表是分開存儲的。
非聚簇索引中的數據是根據數據的插入順序保存。因此非聚簇索引更適合單個數據的查詢。插入順序不受鍵值影響。
只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以後innoDB也支持全文索引)
最開始我一直不懂既然非聚簇索引的主索引和輔助索引指向相同的內容，為什麼還要輔助索引這個東西呢，後來才明白索引不就是用來查詢的嗎，用在那些地方呢，不就是WHERE和ORDER BY 語句後面嗎，那麼如果查詢的條件不是主鍵怎麼辦呢，這個時候就需要輔助索引了。
InnoDB——聚簇索引
聚簇索引的主索引的葉子結點存儲的是鍵值對應的數據本身，輔助索引的葉子結點存儲的是鍵值對應的數據的主鍵鍵值。因此主鍵的值長度越小越好，類型越簡單越好。
聚簇索引的數據和主鍵索引存儲在一起。
聚簇索引的數據是根據主鍵的順序保存。因此適合按主鍵索引的區間查找，可以有更少的磁碟I/O，加快查詢速度。但是也是因為這個原因，聚簇索引的插入順序最好按照主鍵單調的順序插入，否則會頻繁的引起頁分裂，嚴重影響性能。
在InnoDB中，如果只需要查找索引的列，就儘量不要加入其它的列，這樣會提高查詢效率。
使用主索引的時候，更適合使用聚簇索引，因為聚簇索引只需要查找一次，而非聚簇索引在查到數據的地址後，還要進行一次I/O查找數據。
因為聚簇輔助索引存儲的是主鍵的鍵值，因此可以在數據行移動或者頁分裂的時候降低成本，因為這時不用維護輔助索引。但是由於主索引存儲的是數據本身，因此聚簇索引會佔用更多的空間。
聚簇索引在插入新數據的時候比非聚簇索引慢很多，因為插入新數據時需要檢測主鍵是否重複，這需要遍歷主索引的所有葉節點，而非聚簇索引的葉節點保存的是數據地址，佔用空間少，因此分布集中，查詢的時候I/O更少，但聚簇索引的主索引中存儲的是數據本身，數據佔用空間大，分布範圍更大，可能佔用好多的扇區，因此需要更多次I/O才能遍歷完畢。
下圖可以形象的說明聚簇索引和非聚簇索引的區別
從上圖中可以看到聚簇索引的輔助索引的葉子節點的data存儲的是主鍵的值，主索引的葉子節點的data存儲的是數據本身，也就是說數據和索引存儲在一起，並且索引查詢到的地方就是數據（data）本身，那麼索引的順序和數據本身的順序就是相同的；
而非聚簇索引的主索引和輔助索引的葉子節點的data都是存儲的數據的物理地址，也就是說索引和數據並不是存儲在一起的，數據的順序和索引的順序並沒有任何關係，也就是索引順序與數據物理排列順序無關。
此外MyISAM和innoDB的區別總結如下：
總結如下：
InnoDB 支持事務，支持行級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事務，支持表級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
此外，Memory 不支持事務，支持表級別鎖定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
更多MyISAM和innoDB的區別具體內容參考：MyISAMheinnoDB的區別，包括行級鎖死鎖的具體分析
什麼時候要使用索引？
什麼時候不要使用索引？
索引失效的情況：
在組合索引中不能有列的值為NULL，如果有，那麼這一列對組合索引就是無效的。
在一個SELECT語句中，索引只能使用一次，如果在WHERE中使用了，那麼在ORDER BY中就不要用了。
LIKE操作中，'%aaa%'不會使用索引，也就是索引會失效，但是『aaa%』可以使用索引。
在索引的列上使用表達式或者函數會使索引失效，例如：select * from users where YEAR(adddate)<2007，將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成：select * from users where adddate<』2007-01-01′。其它通配符同樣，也就是說，在查詢條件中使用正則表達式時，只有在搜索模板的第一個字符不是通配符的情況下才能使用索引。
在查詢條件中使用不等於，包括<符號、>符號和！=會導致索引失效。特別的是如果對主鍵索引使用！=則不會使索引失效，如果對主鍵索引或者整數類型的索引使用<符號或者>符號不會使索引失效。（經erwkjrfhjwkdb同學提醒，不等於，包括<符號、>符號和！，如果佔總記錄的比例很小的話，也不會失效）
在查詢條件中使用IS NOT NULL會導致索引失效（注意：IS NULL不會導致索引失效，很多文章對此存在誤導）。
字符串不加單引號會導致索引失效。更準確的說是類型不一致會導致失效，比如欄位email是字符串類型的，使用WHERE email=99999 則會導致失敗，應該改為WHERE email='99999'。
在查詢條件中使用OR連接多個條件會導致索引失效，除非OR連結的每個條件都加上索引，這時應該改為兩次查詢，然後用UNION ALL連接起來。
如果排序的欄位使用了索引，那麼select的欄位也要是索引欄位，否則索引失效。特別的是如果排序的是主鍵索引則select * 也不會導致索引失效。
儘量不要包括多列排序，如果一定要，最好為這隊列構建組合索引；
索引的優化
1、最左前綴
索引的最左前綴和和B+Tree中的「最左前綴原理」有關，舉例來說就是如果設置了組合索引<col1,col2,col3>那麼以下3中情況可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。
根據最左前綴原則，我們一般把排序分組頻率最高的列放在最左邊，以此類推。
2、帶索引的模糊查詢優化
在上面已經提到，使用LIKE進行模糊查詢的時候，'%aaa%'不會使用索引，也就是索引會失效。如果是這種情況，只能使用全文索引來進行優化（上文有講到）。
3、為檢索的條件構建全文索引，然後使用
SELECT * FROM tablename MATCH(index_colum) ANGAINST(『word』);
4、使用短索引
對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的 列，如果在前10 個或20 個字符內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。