反向索引和自增索引差別_Mysql索引掃盲總結

什麼是索引？ 索引為什麼查詢快，索引的資料結構是什麼？ 聚簇索引/非聚簇索引差別？ 什麼是覆寫索引？ 唯一索引/普通索引？ 單列索引/聯合索引差別？ 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 個字元。 總結起來

1. 索引的比對規則是左比對的
2. 隻有複合索引的第一個字段出現在查詢條件中，該索引才可能被使用
3. 有了(A,B,C)，就等于同時擁有了(A)，(A,B)和 (A,B,C) 三個索引
4. 隻要索引内，開始用範圍查詢，後面的索引就失效了。**這裡注意：**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)， 可以在索引周遊過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

哪些字段适合建索引?

1. 出現在 SELECT、UPDATE、DELETE 語句的 WHERE 從句中的列
2. 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
3. 并不要将符合 1 和 2 中的字段的列都建立一個索引， 通常将 1、2 中的字段建立聯合索引效果更好
4. 多表 join 的關聯列

為什麼有些SQL不走索引？

1. 使用了通配符開頭，NOT IN 語句或者
2. 聯合索引的第一個字段查詢條件中
3. 資料引擎的優化器選錯了索引(可以适當使用

   force index

   語句來優化)

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

• 自增

結合B+Tree的特點，自增主鍵是連續的，在插入過程中盡量減少頁分裂，即使要進行頁分裂，也隻會分裂很少一部分。并且能減少資料的移動，每次插入都是插入到最後。總之就是減少分裂和移動的頻率。 由于InnoDB索引的特性，是以如果主索引不是自增的(id作主鍵)，那麼每次插入新的資料，都很可能對B+Tree的主索引進行重整，影響性能。是以，盡量以自增id作為InnoDB的主索引。 這就是為什麼 主鍵的Id需求一般是整體趨勢遞增的原因。

• 短數

每個非主鍵索引的葉子節點上都是主鍵的值。如果用UUID，比如

b8a52179-7d54-46de-b1de-d88911a42790

做主鍵，那麼每個二級索引的葉子節點占用約 36位元組，而如果用整型做主鍵，則隻要 4位元組，如果是長整型(bigint)則是 8位元組。是以，主鍵長度越小，普通索引的葉子節點就越小，普通索引占用的空間也就越小。 利用了twitter的雪花算法來盡量做到生成

短數字

且

趨勢自增

的的ID。

索引的最佳實踐？

要建索引

1. 定義主鍵的資料列一定要建立索引。
2. 定義有外鍵的資料列一定要建立索引。
3. 對于經常查詢的資料列最好建立索引。
4. 對于需要在指定範圍内的快速或頻繁查詢的資料列;
5. 經常用在WHERE子句中的資料列。
6. 經常出現在關鍵字order by、group by、distinct後面的字段，建立索引。如果建立的是複合索引，索引的字段順序要和這些關鍵字後面的字段順序一緻，否則索引不會被使用。

不要建索引

1. 對于那些查詢中很少涉及的列，重複值比較多的列不要建立索引。
2. 對于定義為text、image和bit的資料類型的列不要建立索引。
3. 對于經常存取的列避免建立索引

索引的坑

1. 限制表上的索引數目。對一個存在大量更新操作的表，所建索引的數目一般不要超過3個，最多不要超過5個。索引雖說提高了通路速度，但太多索引會影響資料的更新操作。
2. 對複合索引，按照字段在查詢條件中出現的頻度建立索引。在複合索引中，記錄首先按照第一個字段排序。對于在第一個字段上取值相同的記錄，系統再按照第二個字段的取值排序，以此類推。是以隻有複合索引的第一個字段出現在查詢條件中，該索引才可能被使用,是以将應用頻度高的字段，放置在複合索引的前面，會使系統最大可能地使用此索引，發揮索引的作用。

索引不會包含有NULL值的列

1. 隻要列中包含有NULL值都将不會被包含在索引中，複合索引中隻要有一列含有NULL值，那麼這一列對于此複合索引就是無效的。是以我們在資料庫設計時不要讓字段的預設值為NULL

使用短索引(列内容越短越好)

1. 對列進行索引，如果可能應該指定一個字首長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字元内，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。

索引列排序

1. MySQL查詢隻使用一個索引，是以如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。是以資料庫預設排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列建立複合索引。

like語句操作

1. 一般情況下不鼓勵使用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');
           

不要在列上進行運算

1. select * from users where YEAR(adddate)<2007; 将在每個行上進行運算，這将導緻索引失效而進行全表掃描，是以我們可以改成 select * from users where adddate

不使用NOT IN和<>操作

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

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

歡迎大家關注Java之道公衆号

好文章，我在看❤️