金三銀四，每年的這個時候都是求職招聘的黃金期，很不幸，我也加入了這個浩浩蕩蕩的求職大軍中。每每面試，都會問到：你是如何使用索引的？聚集索引和非聚集索引的區別是什麼？為什麼使用索引會查詢快？等等等等一系列的索引問題，恰巧平時使用索引都是哪個欄位常用做查詢就加上索引，不知其所以然。回來深入研究，把學習筆記和大家分享，希望給你帶來幫助。
在學習索引之前，我們拋出以下六個問題，如果你都能解答出來，那這篇文章就不用看了，你的索引已經學成了
為什麼要給表加上主鍵？為什麼加索引後會使查詢變快？為什麼加索引後會使寫入、修改、刪除變慢？什麼情況下要同時在兩個欄位上建索引？ 聚合索引為什麼比非聚合索引查詢快？索引越多越好嗎？
索引的原理
一個表中沒有主鍵，數據是無序的放置在磁碟存儲器上，一行一行的排列的很整齊，加上了主鍵，表在磁碟上的存儲結構就由整齊排列的結構轉變成了 樹狀結構(B+ Tree)，換句話說整個表就變成了了一個索引，也就是聚集索引，這就是為什麼一個表只能有一個主鍵，一個表只能有一個聚集索引，因為主鍵的作用就是把表的數據格式轉換成索引的格式放置。
根據索引定位到值所在的葉結點，然後再通過葉結點取到數據行。所以為什麼加索引查詢效率會高，就是這個原因。
然而， 事物都是有兩面的， 索引能讓資料庫查詢數據的速度上升， 而使寫入數據的速度下降，原因很簡單的， 因為平衡樹這個結構必須一直維持在一個正確的狀態， 增刪改數據都會改變平衡樹各節點中的索引數據內容，破壞樹結構， 因此，在每次數據改變時， DBMS必須去重新梳理樹（索引）的結構以確保它的正確，這會帶來不小的性能開銷，也就是為什麼索引會給查詢以外的操作帶來副作用的原因(索引不是越多越好的第一個原因)。
非聚集索引和聚集索引一樣， 同樣是採用平衡樹作為索引的數據結構。索引樹結構中各節點的值來自於表中的索引欄位， 假如給user表的name欄位加上索引 ， 那麼索引就是由name欄位中的值構成，在數據改變時， DBMS需要一直維護索引結構的正確性。如果給表中多個欄位加上索引 ， 那麼就會出現多個獨立的索引結構，每個索引（非聚集索引）互相之間不存在關聯。 如下圖
每次給欄位建一個新索引， 欄位中的數據就會被複製一份出來， 用於生成索引。 因此， 給表添加索引，會增加表的體積， 佔用磁碟存儲空間，這也就是索引不是越多越好的第二個原因
非聚集索引和聚集索引的區別在於， 通過聚集索引可以查到需要查找的數據， 而通過非聚集索引可以查到記錄對應的主鍵值 ， 再使用主鍵的值通過聚集索引查找到需要的數據，如下圖
不管以任何方式查詢表， 最終都會利用主鍵通過聚集索引來定位到數據， 聚集索引（主鍵）是通往真實數據所在的唯一路徑。這就是聚合索引比非聚合索引效率高的原因。
是否不使用聚集索引就能查詢出所需要的數據呢？答案是肯定的， 這種非主流的方法 稱之為「覆蓋索引」查詢， 也就是平時所說的複合索引或者多欄位索引查詢。 當為欄位建立索引以後， 欄位中的內容會被同步到索引之中， 如果為一個索引指定兩個欄位， 那麼這個兩個欄位的內容都會被同步至索引之中
舉例：一個表，有id，姓名，生日，年齡等欄位，姓名和生日組成複合索引index_birthday_and_user_name，通過非聚集索引index_birthday_and_user_name查找birthday等於1991-11-1的葉節點的內容，然而， 葉節點中除了有user_name表主鍵ID的值以外， user_name欄位的值也在裡面， 因此不需要通過主鍵ID值的查找數據行的真實所在， 直接取得葉節點中user_name的值返回即可。 通過這種覆蓋索引直接查找的方式， 可以省略不使用覆蓋索引查找的後面兩個步驟， 大大的提高了查詢性能，如下圖
使用索引的技巧
1.索引不會包含有NULL的列
只要列中包含有NULL值，都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此符合索引就是無效的。
2.使用短索引
對串列進行索引，如果可以就應該指定一個前綴長度。例如，如果有一個char（255）的列，如果在前10個或20個字符內，多數值是唯一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。
3.索引列排序
mysql查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫默認排序可以符合要求的情況下不要使用排序操作，儘量不要包含多個列的排序，如果需要最好給這些列建複合索引。
4.like語句操作
一般情況下不鼓勵使用like操作，如果非使用不可，注意正確的使用方式。like 『%aaa%』不會使用索引，而like 『aaa%』可以使用索引。
5.不要在列上進行運算
6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的
7.索引要建立在經常進行select操作的欄位上。
這是因為，如果這些列很少用到，那麼有無索引並不能明顯改變查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。
8.索引要建立在值比較唯一的欄位上。
9.對於那些定義為text、image和bit數據類型的列不應該增加索引。因為這些列的數據量要麼相當大，要麼取值很少。
10.在where和join中出現的列需要建立索引。
11.where的查詢條件裡有不等號(where column != …),mysql將無法使用索引。
12.如果where字句的查詢條件裡使用了函數(如：where DAY(column)=…),mysql將無法使用索引。
13.在join操作中(需要從多個數據表提取數據時)，mysql只有在主鍵和外鍵的數據類型相同時才能使用索引，否則及時建立了索引也不會使用
全文索引
舊版的MySQL的全文索引只能用在MyISAM, 不過新版的MySQL5.6.24上InnoDB引擎也加入了全文索引
欄位類型：char、varchar和text
創建全文索引（1）創建表的同時創建全文索引 CREATE TABLE article ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT(title, body) ) 建立的索引名 是「title」（2）通過 alter table 的方式來添加 ALTER TABLE `student` ADD FULLTEXT INDEX ft_stu_name (`name`) #ft_stu_name是索引名，可以隨便起 或者：ALTER TABLE `student` ADD FULLTEXT ft_stu_name (`name`)（3）直接通過create index的方式 CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`) 也可以在創建索引的時候指定索引的長度： CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`(20)刪除全文索引（1）直接使用 drop index DROP INDEX 索引名稱 ON 表名;（2）使用 alter table的方式 ALTER TABLE 表名 DROP INDEX 索引名稱;使用全文索引跟普通索引稍有不同，使用全文索引的格式： MATCH (columnName) AGAINST ('string')SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聰')當查詢多列數據時： 建議在此多列數據上創建一個聯合的全文索引，否則使用不了索引的。 SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聰 廣東')使用全文索引需要注意的是：(基本單位是詞)分詞，全文索引以詞為基礎的，MySQL默認的分詞是所有非字母和數字的特殊符號都是分詞符(外國人嘛)MySQL中與全文索引相關的幾個變量使用命令：mysql> SHOW VARIABLES LIKE 'ft%'; #ft就是FullText的簡寫
ft_boolean_syntax + -><()~*:""&| #改變IN BOOLEAN MODE的查詢字符，不用重新啟動MySQL也不用重建索引 ft_min_word_len 4 #最短的索引字符串，默認值為4，(通常改為1)修改後必須重建索引文件，重新建立索引命令：repair table tablename quick
ft_max_word_len 84 #最長的索引字符串，默認值為84，修改後必須重建索引文件
ft_query_expansion_limit 20 #查詢括展時取最相關的幾個值用作二次查詢
ft_stopword_file (built-in)#全文索引的過濾詞文件，共545個默認詞
（1）ft_boolean_syntax (+ -><()~*:""&|)使用的詳解
IN BOOLEAN MODE自己百度查
SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聰' IN BOOLEAN MODE)
（2）+ : 用在詞的前面，表示一定要包含該詞，並且必須在開始位置。
eg: +Apple 匹配：Apple123, "tommy, Apple"
（3）- : 不包含該詞，所以不能單獨用，這樣是查不到任何row的，必須搭配其他語法使用
eg: MATCH (girl_name) AGAINST ('-林志玲 +張筱雨') 匹配到： 所有不包含林志玲，但包含張筱雨的記錄
（4）空(也就是默認情況)，表示可選的，包含該詞的順序較高。
apple banana找至少包含上面詞中的一個的記錄行
+apple +juice兩個詞均在被包含
+apple mac包含詞 「apple」，但是如果同時包含 「mac」，它的排列將更高一些
+apple -macintosh 包含 「apple」 但不包含 「macintosh」
（5）> :提高該字的相關性，查詢的結果會排在比較靠前的位置。
（6）< :降低相關性，查詢的結果會排在比較靠後的位置。
(7)：可以通過括號來使用字條件。
eg: +aaa +(>bbb <ccc) // 找到有aaa和bbb和ccc，aaa和bbb，或者aaa和ccc(因為bbb，ccc前面沒有+，所以表示可有可無)，然後 aaa&bbb > aaa&bbb&ccc > aaa&ccc
（8）~ :將其相關性由正轉負，表示擁有該字會降低相關性，但不像「-」將之排除，只是排在較後面。
eg: +apple ~macintosh 先匹配apple，但如果同時包含macintosh，就排名會靠後。
（9）* :通配符，這個只能接在字符串後面。
MATCH (girl_name) AGAINST ('+*ABC*') #錯誤，不能放前面MATCH (girl_name) AGAINST ('+張筱雨*') #正確
（10）" " :整體匹配，用雙引號將一段句子包起來表示要完全相符，不可拆字。
eg: "tommy huang" 可以匹配 tommy huangxxxxx 但是不能匹配 tommy is huang。
B+樹索引和哈希索引的區別
B+樹是一個平衡的多叉樹，從根節點到每個葉子節點的高度差值不超過1，而且同層級的節點間有指針相互連結
哈希索引就是採用一定的哈希算法，把鍵值換算成新的哈希值，檢索時不需要類似B+樹那樣從根節點到葉子節點逐級查找，只需一次哈希算法即可立刻定位到相應的位置，速度非常快。
B+樹索引和哈希索引的明顯區別是：
· 如果是等值查詢，那麼哈希索引明顯有絕對優勢，因為只需要經過一次算法即可找到相應的鍵值；當然了，這個前提是，鍵值都是唯一的。如果鍵值不是唯一的，就需要先找到該鍵所在位置，然後再根據鍊表往後掃描，直到找到相應的數據；
· 從示意圖中也能看到，如果是範圍查詢檢索，這時候哈希索引就毫無用武之地了，因為原先是有序的鍵值，經過哈希算法後，有可能變成不連續的了，就沒辦法再利用索引完成範圍查詢檢索；
· 同理，哈希索引也沒辦法利用索引完成排序，以及like 『xxx%』 這樣的部分模糊查詢（這種部分模糊查詢，其實本質上也是範圍查詢）；
· 哈希索引也不支持多列聯合索引的最左匹配規則；
· B+樹索引的關鍵字檢索效率比較平均，不像B樹那樣波動幅度大，在有大量重複鍵值情況下，哈希索引的效率也是極低的，因為存在所謂的哈希碰撞問題。
索引為什麼使用B+Tree ，而不使用二叉樹來實現
其實從算法邏輯上講，二叉查找樹的查找速度和比較次數都是最小的，但是從Mysql的角度講，我們不得不考慮一個現實問題：磁碟IO。
查找都是索引操作，一般來說索引非常大，尤其是關係型資料庫這種，當數據量比較大的時候，索引的大小有可能幾個G甚至更多，數據量大的索引能達到億級別，所以為了減少內存的佔用，資料庫索引是存儲在外部磁碟上的。
當我們利用索引查詢的時候，不可能把整個索引全部加載到內存，只能逐一加載每個磁碟頁，磁碟頁對應索引樹的節點，為了減少磁碟IO的次數，就需要儘量降低樹的高度