MySQL | 05 如何設計高性能的索引？

上回我們主要研究了為什么使用索引，以及索引的數據結構。今天帶你了解如何設計高性能的索引。

其中，有這么一個點，說的是 InnoDB 引擎中使用的是聚簇索引，其主索引的實現樹中的葉子結點存儲的是完整的數據記錄，而輔助索引中存儲的則只是輔助鍵和主鍵的值。

這樣在用輔助索引進行查詢時，會先查出主鍵的值，然后再去主索引中根據主鍵的值查詢目標值。

比如，假想一個表如下圖存儲了 4 行數據。其中 Id 作為主索引，Name 作為輔助索引。

對于聚簇索引，若使用主鍵索引進行查詢，select * from tab where id = 14 這樣的條件查找主鍵，則按照 B+ 樹的檢索算法即可查找到對應的葉節點，之后獲得行數據。

若使用輔助索引進行查詢，對 Name 列進行條件搜索，則需要兩個步驟：

1、第一步在輔助索引 B+ 樹中檢索 Name，到達其葉子節點獲取對應的主鍵值。
2、第二步根據主鍵值在主索引 B+ 樹中再執行一次 B+ 樹檢索操作，最終到達葉子節點即可獲取整行數據。

上面這個過程稱為回表。

回表：在數據中，當查詢數據的時候，在索引中查找索引后，獲得該行的 rowid，根據 rowid 再查詢表中數據，就是回表。

顯然，使用輔助索引出現了回表操作，這勢必會影響查詢性能，那有什么辦法能夠減少回表嗎？

下面就開始我們的主題：如何讓 MySQL 索引更高效！

覆蓋索引

上面，我們查詢的是 select *，如果是根據 Name 查詢 Id 呢？即 select Id from tab where Name='Jobs'。

很明顯，由于輔助索引 Name 上已經存儲了 Id 的值，所以這時，查詢便不會再次回表查詢。

如果索引已經包含了所有滿足查詢需要的數據，這時我們稱之為覆蓋索引（Covering Index），這時就不再需要回表操作。

覆蓋索引是一種非常強大的工具，能大大提高查詢性能，只需要讀取索引而不用讀取數據有以下一些優點：

1、索引條目通常遠小于數據行大小，只需要讀取索引，則 MySQL 會極大地減少數據訪問量。

2、因為索引是按照列值順序存儲的，所以對于 IO 密集的范圍查找會比隨機從磁盤讀取每一行數據的 IO 少很多。

3、覆蓋索引對 InnoDB 表特別有用。因為 InnoDB 的輔助索引在葉子節點中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢；

由于覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優化手段。

聯合索引/最左匹配原則

又名復合索引，由兩個或多個列的索引。

它規定了 MySQL 從左到右地使用索引字段，對字段的順序有一定要求。

另外，一個查詢可以只使用索引中的一部分，更準確地說是最左側部分（最左優先），這就是傳說中的最左匹配原則。

即最左優先，如：

如果有一個 2 列的索引 (col1,col2)，則相當于已經對 (col1)、(col1,col2) 上建立了索引；

如果有一個 3 列索引 (col1,col2,col3)，則相當于已經對 (col1)、(col1,col2)、(col1,col2,col3) 上建立了索引；

但是 (col2,col3) 上并沒有。

假定數據表有一個包含 2 列的聯合索引（a, b），則索引的 B+ 樹結構可能如下：

鍵值都是排序的，通過葉子節點可以邏輯上順序的讀出所有數據。

數據（1,1）（1,2）（2,1）（2,4）（3,1）（3,2）是按照（a，b）先比較 a 再比較 b 的順序排列。

所以從全局看，a 是全局有序的，而 b 則不是。

基于上面的結構，對于以下查詢顯然是可以使用（a，b）這個聯合索引的：

select * from table where a=xxx and b=xxx ;

select * from table where a=xxx;

但是對于下面的 sql 是不能使用這個聯合索引的，因為葉子節點的 b 值，1,2,1,4,1,2 顯然不是排序的。

select * from table where b=xxx

只要滿足最左前綴，就可以利用索引來加速檢索。這個最左前綴可以是聯合索引的最左 N 個字段，也可以是字符串索引的最左 M 個字符。

注意

1、主鍵字段其實跟所有非主鍵索引建立了聯合索引，只是說如果主鍵字段沒有在聯合索引中明確聲明，只會在其他索引中處于最右邊；

2、最左前綴匹配原則，MySQL 會一直向右匹配直到遇到范圍查詢（>、<、between、like）就停止匹配。

比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a,b,c,d) 順序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引，則都可以用到，a,b,d 的順序可以任意調整。

3、= 和 in 的條件可以亂序

MySQL 的查詢優化器會幫你優化成索引可以識別的形式。MySQL 查詢優化器會判斷糾正 SQL 語句該以什么樣的順序執行效率最高，最后才生成真正的執行計劃。

為什么要使用聯合索引？

1、 減少開銷

"一個頂三個"。建一個聯合索 引(col1,col2,col3)，實際相當于建了 (col1),(col1,col2),(col1,col2,col3) 三個索引。

每多一個索引，都會增加寫操作的開銷和磁盤空間的開銷。對于大量數據的表，使用聯合索引會大大的減少開銷！

2、 覆蓋索引

對聯合索引 (col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么 MySQL 可以直接通過遍歷索引取得數據，而無需回表，這減少了很多的隨機 IO 操作。

減少 io 操作，特別的隨機 io 其實是 dba 主要的優化策略。所以，在真正的實際應用中，覆蓋索引是主要的提升性能的優化手段之一。

3、 效率高

索引列越多，通過索引篩選出的數據越少。

有 1000W 條數據的表，有如下sql: select col1,col2,col3 from table where col1=1 and col2=2 and col3=3，假設假設每個條件可以篩選出 10% 的數據。

如果只有單值索引，那么通過該索引能篩選出 1000W_10%=100w 條數據，然后再回表從 100w 條數據中找到符合 col2=2 and col3= 3 的數據，然后再排序，再分頁；

如果是聯合索引，通過索引篩選出 1000w_10% * 10% *10%=1w，效率提升可想而知！

索引下推

索引條件下推（ICP：index condition pushdown）是 MySQL 中一個常用的優化，尤其是當 MySQL 需要從一張表里檢索數據時。

ICP（index condition pushdown）是 MySQL 利用索引（二級索引）元組和篩字段在索引中的 WHERE 條件從表中提取數據記錄的一種優化操作。

ICP 的思想是：存儲引擎在訪問索引的時候檢查篩選字段在索引中的 where 條件，如果索引元組中的數據不滿足推送的索引條件，那么就過濾掉該條數據記錄。

ICP（優化器）盡可能的把 index condition 的處理從 server 層下推到存儲引擎層。

存儲引擎使用索引過濾不相關的數據，僅返回符合 index condition 條件的數據給 server 層。也是說數據過濾盡可能存儲引擎層進行，而不是返回所有數據給 server 層，然后后再根據 where 條件進行過濾。

下推過程

優化器沒有使用 ICP 時

數據訪問和提取的過程如下：

①：MySQL Server 發出讀取數據的命令，調用存儲引擎的索引讀或全表表讀。此處進行的是索引讀。

②、③：進入存儲引擎，讀取索引樹，在索引樹上查找，把滿足條件的（紅色的）從表記錄中讀出（步驟 ④，通常有 IO）。

⑤：從存儲引擎返回標識的結果。

以上，不僅要在索引行進行索引讀取（通常是內存中，速度快。步驟 ③），還要進行進行步驟 ④，通常有 IO。

⑥：從存儲引擎返回查找到的多條數據給 MySQL Server，MySQL Server 在 ⑦ 得到較多的元組。

⑦--⑧：依據 WHERE 子句條件進行過濾，得到滿足條件的數據。

注意在 MySQL Server 層得到較多數據，然后才過濾，最終得到的是少量的、符合條件的數據。

在不支持 ICP 的系統下，索引僅僅作為 data access 使用。

優化器使用ICP時

①：MySQL Server 發出讀取數據的命令，過程同圖一。

②、③：進入存儲引擎，讀取索引樹，在索引樹上查找，把滿足已經下推的條件的（紅色的）從表記錄中讀出（步驟 ④，通常有 IO）；

⑤：從存儲引擎返回標識的結果。

此處，不僅要在索引行進行索引讀取（通常是內存中，速度快。步驟 ③），還要在 ③ 這個階段依據下推的條件進行進行判斷，不滿足條件的，不去讀取表中的數據，直接在索引樹上進行下一個索引項的判斷，直到有滿足條件的，才進行步驟 ④ ，這樣，較沒有 ICP 的方式，IO 量減少。

⑥：從存儲引擎返回查找到的少量數據給 MySQL Server，MySQL Server 在 ⑦ 得到少量的數據。

因此比較圖一無 ICP 的方式，返回給 MySQL Server 層的即是少量的、 符合條件的數據。

舉例

比如：

SELECT * FROM employees 
WHERE first_name='Mary' 
AND last_name LIKE '%man';

在沒有 ICP 時，首先通過索引前綴從存儲引擎中讀出所有 first_name 為 Mary 的記錄，然后在 server 端用 where 篩選 last_name 的 like 條件；

而啟用 ICP 后，由于 last_name 的 like 篩選可以通過索引字段進行，那么存儲引擎內部通過索引與 where 條件的對比來篩選掉不符合 where 條件的記錄，這個過程不需要讀出整條記錄，同時只返回給 server 篩選后條記錄，因此提高了查詢性能。

注意事項

有幾個關于ICP的事情要注意：

• ICP 只能用于二級索引，不能用于主索引；

• 也不是全部 where 條件都可以用 ICP 篩選，如果某 where 條件的字段不在索引中，當然還是要讀取整條記錄做篩選，在這種情況下，仍然要到 server 端做 where 篩選；

• ICP 的加速效果取決于在存儲引擎內通過 ICP 篩選掉的數據的比例；

總結建索引的幾大原則

1、最左前綴匹配原則，非常重要的原則，MySQL 會一直向右匹配直到遇到范圍查詢 （>、<、between、like）就停止匹配；

2、= 和 in 的條件可以亂序；

3、盡量選擇區分度高的列作為索引，區分度表示字段不重復的比例，比例越大我們掃描的記錄數越少；

4、索引列不能參與計算，保持列「干凈」。原因很簡單，b+ 樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。

5、盡量的擴展索引，不要新建索引。

索引是最好的解決方案嗎？

索引不是最好的，但已經是相當好的了。

當表非常小時，沒必要使用索引，直接全表查詢好了；

當表是中大型時，比較適合使用索引，來快速定位目標數據；

當表是超大型時，創建和維護索引都是不小的代價，需要專業的 DBA 來分析，這種情況下可以嘗試使用分表技術；

參考：
https://blog.csdn.net/u012006689/article/details/73195837
http://lihx8.lofter.com/post/1cc9bc99_7da03fe