什么是索引？索引為什么查詢快，索引的數據結構是什么？聚簇索引/非聚簇索引區別？什么是覆蓋索引？唯一索引/普通索引？單列索引/聯合索引區別？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)，那么：

-- 索引覆蓋了 select id from T where key = 'test';-- 索引沒覆蓋，需要回表 select * from T where key = 'test';

問題，為什么第一個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 語句是這么寫的：

mysql> select * from tuser where name like '張 %' and age=10 and ismale=1;

我們已經知道了前綴索引規則，所以這個語句在搜索索引樹的時候，只能用 “張”，找到第一個滿足條件的記錄 ID3。當然，這還不錯，總比全表掃描要好。然后呢？當然是判斷其他條件是否滿足。在 MySQL 5.6 之前，只能從 ID3 開始一個個回表。到主鍵索引上找出數據行，再對比字段值。而 MySQL 5.6 引入的索引下推優化（index condition pushdown)， 可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

哪些字段適合建索引?

出現在 SELECT、UPDATE、DELETE 語句的 WHERE 從句中的列

包含在 ORDER BY、GROUP BY、DISTINCT 中的字段

并不要將符合 1 和 2 中的字段的列都建立一個索引， 通常將 1、2 中的字段建立聯合索引效果更好

多表 join 的關聯列

為什么有些SQL不走索引？

使用了通配符開頭，NOT IN 語句或者

聯合索引的第一個字段查詢條件中

數據引擎的優化器選錯了索引（可以適當使用 force index 語句來優化）

為什么一般主鍵索引最好是自增的, 盡量短的數值類型？

• 自增

結合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走索引的可能。

ALTER TABLE `T` ADD `reverse_identifier` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci;select * from T where reverse_identifier like reverse('%SDTE');

不要在列上進行運算

select * from users where YEAR(adddate)<2007; 將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成 select * from users where adddate<‘2007-01-01’;

不使用NOT IN和<>操作

因為MySQL只對<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE才會使用索引。因為在以通配符 % 和 _ 開頭作查詢時，MySQL不會使用索引。

有道無術，術可成；有術無道，止于術

歡迎大家關注Java之道公眾號

好文章，我在看??

總結

以上是生活随笔為你收集整理的Mysql索引扫盲总结的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。