文章來源：https://mp.weixin.qq.com/s/hsvdbGXBcHPA0JOO7gkpjw
什麼是索引？索引為什麼查詢快，索引的數據結構是什麼？聚簇索引/非聚簇索引區別？什麼是覆蓋索引？唯一索引/普通索引？單列索引/聯合索引區別？Full-index全文索引？什麼是下推索引？什麼是最左匹配，查詢回表？哪些字段適合建索引？為什麼一般主鍵索引最好是自增長的, 儘量短的數值類型？為什麼有些SQL不走索引？索引的最佳實踐？
索引為什麼快
索引的本質是空間換時間。
- +bonus: 加快檢索速度，加快多表連接
- -price: 額外空間開銷，維護索引的額外時間開銷
所以我們通過索引這個緩存來提高數據查詢的效率。
假如我們自己設計數據庫索引的話，我們會選取什麼樣的數據結構呢？下面我們來分析下各種查詢常見的數據結構的性格，看看選誰是最合適的人選。
數據結構比較
- 有序數組：等值查詢和範圍查詢場景中的性能就都非常優秀。特定值查詢用二分法就可以快速得到，這個時間複雜度是 O(log(N))。類似between[x, y]的 範圍查詢 也比較快，先用值查詢二分法找到x, 然後向後遍歷，知道找到y。但是他最大的問題是插入或者刪除一個新數據，這個新數據後面的整個數組都需要挪動，複雜度是O(N)。
- HashMap：雖然可以快速定位，值查詢的時間複雜度是O(1), 但是Hashmap沒有順序，進行範圍查詢的話複雜度高是O(N)。
- 二叉樹查找樹BST：二叉樹的高度不均勻，不能自平衡，查找效率跟數據量有關（樹的高度），在極端情況下（插入數據本身就是有序的）這棵樹就退化成鏈表了，查詢實際複雜度是O(N)
- 紅黑樹：是平衡的BST，性能穩定在O(logN), 但因為是二叉樹，樹的高度隨著數據量增加而增加，並且需要再平衡。適合數據都在內存的情況，比如Java裡的HashMap。但是在硬盤尋址的場景下IO成本會比較高。
- B-Tree：相比二叉樹來說是一種多路平衡查詢樹，但是B樹不管葉子節點還是非葉子節點，都會保存數據，這樣導致在非葉子節點中能保存的指針數量變少（有些資料也稱為扇出），指針少的情況下要保存大量數據，只能增加樹的高度，導致IO操作變多，查詢性能變低；
- B+Tree: 從物理存儲結構上說是N叉樹，B-Tree和B+Tree都以頁(4K)來劃分節點的大小，但是由於B+Tree的中間節點（非葉子節點）不存儲數據，存的是索引信息，索引包含Key和Point指針。因此B+Tree能夠在同樣大小的節點中，存儲更多的key，提高查找效率。
每一個索引在 InnoDB 裡面對應一棵 B+ 樹。以 InnoDB 的一個整數字段索引為例，這個 N 差不多是 1200。這棵樹高是 4 的時候，就可以存 1200 ^(4-1) 個值，這已經 17 億了。考慮到樹根的數據塊總是在內存中的，一個 10 億行的表上一個整數字段的索引，查找一個值最多隻需要訪問 3 次磁盤。
聚簇索引/非聚簇索引
區別主要看葉子節點存了什麼數據：
在 InnoDB 裡，索引B+ Tree的葉子節點存儲了整行數據的是主鍵索引，也被稱之為聚簇索引。
而索引B+ Tree的葉子節點存儲了主鍵的值的是非主鍵索引，也被稱之為非聚簇索引。
聚簇索引查詢相對會更快一些，因為主鍵索引樹的葉子節點直接就是我們要查詢的整行數據了。而非主鍵索引的葉子節點是主鍵的值，查到主鍵的值以後，還需要再通過主鍵的值再進行一次查詢（這個過程叫做回表, 也就是查了2個索引樹）。
覆蓋索引
覆蓋索引（covering index）指一個查詢語句的執行只用從索引中就能夠取得，不必從數據表中讀取。覆蓋索引不是索引樹，是一個結果。當一條查詢語句符合覆蓋索引條件時，MySQL只需要通過索引就可以返回查詢所需要的數據，這樣避免了查到索引後再返回表操作，減少I/O提高效率。
例如表T中有一個普通索引 idx_key(key)，那麼：
<code>-- 索引覆蓋了
select id from T where key = 'test';
-- 索引沒覆蓋，需要回表
select * from T where key = 'test';/<code>
問題，為什麼第一個SQL索引覆蓋了? 非聚簇索引的葉子節點存的是id。
唯一索引/普通索引
唯一索引和普通索引在查詢和更新的時候區別：
- 唯一索引找到滿足的第一條記錄會立馬返回，通知檢索（因為唯一性的保證）。但是這個區別並沒有很大的性能區別，因為Innodb是按照頁（默認16KB）讀寫的，讀數據的時候是從B+樹的根節點開始搜索，搜索的時候將整個頁從硬盤加載到內存。
- 唯一索引在插入的時候會多做些判斷，想要做這個判斷就必須先把數據頁讀入內存。但是普通索引不需要做這個判斷，就可以把需要更新的數據做判斷：如果數據在內存則直接更新；如果不在也不加載內存，而是先寫入change buffer，等下次查詢的時候再執行change buffer。這樣普通索引會相對性能好一些。但是注意：如果業務場景是寫入後立馬有查詢，其實還是會立馬需要把數據頁加載到內存，這樣的情況下其實並不能帶來優化IO的操作。
Full-index全文索引
Mysql 5.6 引入了全文索引Full text index，但是隻能適用於分詞的情況，如果是匹配字符串的一部分就不適用了。
MySQL支持三種模式的全文檢索模式：自然語言模式（IN NATURAL LANGUAGE MODE），即通過MATCH AGAINST 傳遞某個特定的字符串來進行檢索。布爾模式（IN BOOLEAN MODE），可以為檢索的字符串增加操作符，例如“+”表示必須包含，“-”表示不包含，“*”表示通配符（這種情況， 即使傳遞的字符串較小或出現在停詞中，也不會被過濾掉），其他還有很多特殊的布爾操作符，可以通過如下參數控制：查詢擴展模式（WITH QUERY EXPANSION）, 這種模式是自然語言模式下的一個變種，會執行兩次檢索，第一次使用給定的短語進行檢索，第二次是結合第一次相關性比較高的行進行檢索。
單列索引/聯合索引
對於一個表裡的多個列，比如是有些列高頻查詢，有些列低頻查詢。如果為每一個低頻的列單獨建立索引感覺有些浪費，如果不建立索引又只能走全表掃描。所以我們經常用聯合索引來解決這個問題，聯合索引如idx_key1_key2_key3(key1,key2,key3)，相當於創建了（key1）、(key1,key2)和(key1,key2,key3)三個索引，那麼在建立聯合索引的時候，如何安排索引內的字段順序？
我們考慮key1 是最常用的列放最前面，key2和key3不常用。
上面這種建立一個聯合索引就實際上包含了3個索引的特性就是最左匹配原則。這個最左匹配可以是聯合索引的最左 N 個字段，也可以是字符串索引的最左 M 個字符。
總結起來
- 索引的匹配規則是 左匹配的
- 只有複合索引的第一個字段出現在查詢條件中，該索引才可能被使用
- 有了(A,B,C)，就等於同時擁有了(A)，（A,B）和 (A,B,C) 三個索引
- 只要索引內，開始用範圍查詢，後面的索引就失效了。**這裡注意：**IN 在 where 中，也屬於準確查詢，不會使後面索引失效。
什麼是下推索引？
在MySQL 5.6中，引入了Index Condition Pushdown Optimization 優化。本質是針對那些需要回表查找的部分如果索引裡已經包含了該列，那麼先在索引裡做過濾判斷。
以用戶表的聯合索引（name, age）為例。如果現在有一個需求：檢索出表中“名字第一個字是張，而且年齡是 10 歲的所有男孩”。那麼，SQL 語句是這麼寫的：
<code>mysql> select * from tuser where name like '張 %' and age=10 and ismale=1;/<code>
我們已經知道了前綴索引規則，所以這個語句在搜索索引樹的時候，只能用 “張”，找到第一個滿足條件的記錄 ID3。當然，這還不錯，總比全表掃描要好。然後呢？當然是判斷其他條件是否滿足。在 MySQL 5.6 之前，只能從 ID3 開始一個個回表。到主鍵索引上找出數據行，再對比字段值。而 MySQL 5.6 引入的索引下推優化（index condition pushdown)， 可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。
哪些字段適合建索引?
為什麼有些SQL不走索引？
為什麼一般主鍵索引最好是自增的, 儘量短的數值類型？
結合B+Tree的特點，自增主鍵是連續的，在插入過程中儘量減少頁分裂，即使要進行頁分裂，也只會分裂很少一部分。並且能減少數據的移動，每次插入都是插入到最後。總之就是減少分裂和移動的頻率。
由於InnoDB索引的特性，因此如果主索引不是自增的(id作主鍵)，那麼每次插入新的數據，都很可能對B+Tree的主索引進行重整，影響性能。因此，儘量以自增id作為InnoDB的主索引。
這就是為什麼主鍵的Id需求一般是整體趨勢遞增的原因。
- 短數
每個非主鍵索引的葉子節點上都是主鍵的值。如果用UUID，比如 b8a52179-7d54-46de-b1de-d88911a42790 做主鍵，那麼每個二級索引的葉子節點佔用約 36字節，而如果用整型做主鍵，則只要 4字節，如果是長整型（bigint）則是 8字節。所以，主鍵長度越小，普通索引的葉子節點就越小，普通索引佔用的空間也就越小。
利用了twitter的雪花算法來儘量做到生成短數字且趨勢自增的的ID。
索引的最佳實踐？
要建索引
- 定義主鍵的數據列一定要建立索引。
- 定義有外鍵的數據列一定要建立索引。
- 對於經常查詢的數據列最好建立索引。
- 對於需要在指定範圍內的快速或頻繁查詢的數據列;
- 經常用在WHERE子句中的數據列。
- 經常出現在關鍵字order by、group by、distinct後面的字段，建立索引。如果建立的是複合索引，索引的字段順序要和這些關鍵字後面的字段順序一致，否則索引不會被使用。
不要建索引
- 對於那些查詢中很少涉及的列，重複值比較多的列不要建立索引。
- 對於定義為text、image和bit的數據類型的列不要建立索引。
- 對於經常存取的列避免建立索引
索引的坑
- 限制表上的索引數目。對一個存在大量更新操作的表，所建索引的數目一般不要超過3個，最多不要超過5個。索引雖說提高了訪問速度，但太多索引會影響數據的更新操作。
- 對複合索引，按照字段在查詢條件中出現的頻度建立索引。在複合索引中，記錄首先按照第一個字段排序。對於在第一個字段上取值相同的記錄，系統再按照第二個字段的取值排序，以此類推。因此只有複合索引的第一個字段出現在查詢條件中，該索引才可能被使用,因此將應用頻度高的字段，放置在複合索引的前面，會使系統最大可能地使用此索引，發揮索引的作用。
索引不會包含有NULL值的列
- 只要列中包含有NULL值都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在數據庫設計時不要讓字段的默認值為NULL
使用短索引（列內容越短越好）
- 對列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字符內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。
索引列排序
- MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下不要使用排序操作；儘量不要包含多個列的排序，如果需要最好給這些列創建複合索引。
like語句操作
- 一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引，而like “aaa%”可以使用索引。即：左匹配規則。可以使用reverse函數來支持逆序匹配，從而增強like走索引的可能。
<code>ALTER TABLE `T` ADD `reverse_identifier` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci;
select * from T where reverse_identifier like reverse('%SDTE');/<code>
不要在列上進行運算
- select * from users where YEAR(adddate)<2007; 將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成 select * from users where adddate
不使用NOT IN和<>操作
- 因為MySQL只對，>=，BETWEEN，IN，以及某些時候的LIKE才會使用索引。因為在以通配符 % 和 _ 開頭作查詢時，MySQL不會使用索引。
閱讀更多 Java架構人生 的文章
