# 1.索引類型介紹 #### * Normal（普通索引） 這是最基本的索引，它沒有任何限制，如果是CHAR，VARCHAR類型，length可以小于字段實際長度，如果是BLOB和TEXT類型，必須指定 length #### * Unique（唯一索引） 它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一 #### * Full Text（全文索引） 即為全文索引，目前只有MyISAM引擎支持。不過目前只有 CHAR、VARCHAR ，TEXT 列上可以創建全文索引 由于FULLTEXT對中文支持不是很好，在沒有插件的情況下，最好不要使用。 #### * BTREE BTREE索引就是一種將索引值按一定的算法，存入一個樹形的數據結構中。如二叉樹一樣，每次查詢都是從樹的入口root開始，依次遍歷node，獲取leaf。 在 Innodb里，有兩種形態：一是primary key形態，其leaf node里存放的是數據，而且不僅存放了索引鍵的數據，還存放了其他字段的數據。二是secondary index，其leaf node和普通的BTREE差不多，只是還存放了指向主鍵的信息. 而在MyISAM里，主鍵和其他的并沒有太大區別。不過和Innodb不太一樣的地方是在MyISAM里，leaf node里存放的不是主鍵的信息，而是指向數據文件里的對應數據行的信息. #### * HASH hash索引可以一次定位，不需要像樹形索引那樣逐層查找,因此具有極高的效率。 （1）Hash 索引僅僅能滿足"=","IN"和"<=>"查詢，不能使用范圍查詢。 由于 Hash 索引比較的是進行 Hash 運算之后的 Hash 值，所以它只能用于等值的過濾，不能用于基于范圍的過濾，因為經過相應的 Hash 算法處理之后的 Hash 值的大小關系，并不能保證和Hash運算前完全一樣。 （2）Hash 索引無法被用來避免數據的排序操作。 由于 Hash 索引中存放的是經過 Hash 計算之后的 Hash 值，而且Hash值的大小關系并不一定和 Hash 運算前的鍵值完全一樣，所以數據庫無法利用索引的數據來避免任何排序運算； （3）Hash 索引不能利用部分索引鍵查詢。 對于組合索引，Hash 索引在計算 Hash 值的時候是組合索引鍵合并后再一起計算 Hash 值，而不是單獨計算 Hash 值，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash 索引也無法被利用。 （4）Hash 索引在任何時候都不能避免表掃描。 前面已經知道，Hash 索引是將索引鍵通過 Hash 運算之后，將 Hash運算結果的 Hash 值和所對應的行指針信息存放于一個 Hash 表中，由于不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 鍵值的數據的記錄條數，也無法從 Hash 索引中直接完成查詢，還是要通過訪問表中的實際數據進行相應的比較，并得到相應的結果。 （5）Hash 索引遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高。 對于選擇性比較低的索引鍵，如果創建 Hash 索引，那么將會存在大量記錄指針信息存于同一個 Hash 值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表數據的訪問，而造成整體性能低下。 # 2.建立索引的時機 那么我們需要在什么情況下建立索引呢？一般來說，在WHERE和JOIN中出現的列需要建立索引，但也不完全如此，因為MySQL只對<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE才會使用索引，因為在以通配符%和_開頭作查詢時，MySQL不會使用索引 # 3.Explain優化查詢檢測 使用方法,在select語句前加上Explain就可以了： ~~~ mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; -- 結果： id: 1 select_type: SIMPLE -- 查詢類型（簡單查詢,聯合查詢,子查詢） table: user -- 顯示這一行的數據是關于哪張表的 type: range -- 區間索引（在小于1990/2/2區間的數據),這是重要的列,顯示連接使用了何種類型。從最好到最差的連接類型為system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表掃描了全表才確定結果。一般來說,得保證查詢至少達到range級別,最好能達到ref。 possible_keys: birthday -- 指出MySQL能使用哪個索引在該表中找到行。如果是空的,沒有相關的索引。這時要提高性能,可通過檢驗WHERE子句,看是否引用某些字段,或者檢查字段不是適合索引。 key: birthday -- 實際使用到的索引。如果為NULL,則沒有使用索引。如果為primary的話,表示使用了主鍵。 key_len: 4 -- 最長的索引寬度。如果鍵是NULL,長度就是NULL。在不損失精確性的情況下,長度越短越好 ref: const -- 顯示哪個字段或常數與key一起被使用。 rows: 1 -- 這個數表示mysql要遍歷多少數據才能找到,在innodb上是不準確的。 Extra: Using where; Using index -- 執行狀態說明,這里可以看到的壞的例子是Using temporary和Using ~~~ ### select_type * simple 簡單select(不使用union或子查詢) * primary 最外面的select * union union中的第二個或后面的select語句 * dependent union union中的第二個或后面的select語句,取決于外面的查詢 * union result union的結果。 * subquery 子查詢中的第一個select * dependent subquery 子查詢中的第一個select,取決于外面的查詢 * derived 導出表的select(from子句的子查詢) ### Extra與type詳細說明 * 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上 * Where used 使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行,并且連接類型ALL或index,這就會發生,或者是查詢有問題不同連接類型的解釋（按照效率高低的順序排序 * system 表只有一行：system表。這是const連接類型的特殊情況 * const:表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行,這個值實際就是常數,因為MYSQL先讀這個值然后把它當做常數來對待 * eq_ref:在連接中,MYSQL在查詢時,從前面的表中,對每一個記錄的聯合都從表中讀取一個記錄,它在查詢使用了索引為主鍵或惟一鍵的全部時使用 * ref:這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如,利用最左邊前綴）時發生。對于之前的表的每一個行聯合,全部記錄都將從表中讀出。這個類型嚴重依賴于根據索引匹配的記錄多少—越少越好+ * range:這個連接類型使用索引返回一個范圍中的行,比如使用>或<查找東西時發生的情況+ * index: 這個連接類型對前面的表中的每一個記錄聯合進行完全掃描（比ALL更好,因為索引一般小于表數據）+ * ALL:這個連接類型對于前面的每一個記錄聯合進行完全掃描,這一般比較糟糕,應該盡量避免 其中type: * 如果是Only index,這意味著信息只用索引樹中的信息檢索出的,這比掃描整個表要快。 * 如果是where used,就是使用上了where限制。 * 如果是impossible where 表示用不著where,一般就是沒查出來啥。 * 如果此信息顯示Using filesort或者Using temporary的話會很吃力,WHERE和ORDER BY的索引經常無法兼顧,如果按照WHERE來確定索引,那么在ORDER BY時,就必然會引起Using filesort,這就要看是先過濾再排序劃算,還是先排序再過濾劃算。 # 4.SQL原句優化工具SQLAdvisor 項目 GitHub 地址：https://github.com/Meituan-Dianping/SQLAdvisor SQLAdvisor是由美團點評公司技術工程部DBA團隊（北京）開發維護的一個分析SQL給出索引優化建議的工具。它基于MySQL原生態詞法解析，結合分析SQL中的where條件、聚合條件、多表Join關系 給出索引優化建議。目前SQLAdvisor在美團點評廣泛應用，包括美團支付、酒店旅游、外賣、團購等產品線，公司內部對SQLAdvisor的開發全面轉到github上，開源和內部使用保持一致。