Mysql索引覆蓋

作者：京東零售 孫濤

1.什麼是覆蓋索引

通常情況下，我們創建索引的時候只關注where條件，不過這只是索引優化的一個方向。優秀的索引設計應該縱觀整個查詢，而不僅僅是where條件部分，還應該關注查詢所包含的列。索引確實是一種高效的查找數據方式，但是mysql也可以從索引中直接獲取數據，這樣就不在需要讀數據行了。 覆蓋索引（covering index）指一個查詢語句的執行只需要從輔助索引中就可以得到查詢記錄，而不需要回表，去查詢聚集索引中的記錄。可以稱之為實現了索引覆蓋。 在mysql數據庫中，如何看出一個sql是否實現了索引覆蓋呢？





 

從執行計劃看，Extra的信息為using index ，即用到了索引覆蓋。

2.覆蓋索引為什麼快

innodb存儲引擎底層實現包括B+樹索引和哈希索引，innodb存儲引擎默認的索引模型/結構是B+樹，所以大部分時候我們使用的都是B+樹索引，因為它良好的性能和特性更適合於構建高併發系統。根據索引的存儲方式來劃分，索引可以分為聚簇索引和非聚簇索引。聚簇索引的特點是葉子節點包含了完整的記錄行，而非聚簇索引的葉子節點只有索引字段和主鍵ID。非聚簇索引中因為不含有完整的數據信息，查找完整的數據記錄需要回表，所以一次查詢操作實際上要做兩次索引查詢。而如果所有的索引查詢都要經過兩次才能查到，那麼肯定會引起效率下降，畢竟能少查一次就少查一次。

覆蓋索引就實現了從非聚簇索引中直接獲取數據，所以效率會提升。





 

3.SQL優化場景

（1）無where條件

請看下面的sql





 





 

執行計劃中，type為ALL，代表進行了全表掃描，掃描行數達到了26274308，所以執行時間為9.25秒,也是正常的。

那麼如何優化？優化措施很簡單，就是對查詢列建立索引。如下，

alter table instance_space_history add index idx_org1(org1); 看添加索引後的執行計劃





 

Possible_keys為null，説明沒有where條件時優化器無法通過索引檢索數據；

但是看extra的信息 Using index，即從索引中獲取數據，減少了讀取的數據塊的數量 。





 

在看實際優化效果，掃描行數沒變，但是使用了覆蓋索引，查詢時間從9.25秒縮短到5.67秒。 思考： 無where條件的查詢，可以通過索引來實現索引覆蓋查詢。但前提條件是，查詢返回的字段數足夠少，更不用説select *之類的了。畢竟，建立key length過長的索引，始終不是一件好事情。

（2）where條件區分度低

使用區分度極低的字段作為where條件的查詢SQL，對於dba或者研發人員優化一直是比較頭疼的問題，這裏介紹一種思路，就是通過索引覆蓋來優化 。 t_material_image是一張8億多數據的大表，where條件的material_type字段區分度很低，下面是沒加任何索引的執行計劃和查詢時間（7.35秒）。





 





 

最容易想到的優化方式，就是給where條件的字段加索引，添加索引語句如下： alter table t_material_image add index idx_material_type (material_type);

再來看執行計劃





 

通過執行計劃和測試結果看，的確是有效果的，但是走索引後的查詢效率依然不能滿足我們期望。 然後試着給material_type,material_id添加聯合索引。 alter table t_material_image add index idx_material_id_type (material_type,material_id);





 





 

從這個sql的執行計劃看，出現Using index，實現了索引覆蓋；再看執行時間，性能得到了巨大的提升，居然已經可以跑到0.85s左右了。

思考：

當where條件字段區分度低（過濾性差），且where條件與查詢字段總數較少的情況下，使用索引覆蓋優化，是個不錯的選擇。

（3）查詢僅選擇主鍵

對於Innodb的輔助索引，它的葉子節點存儲的是索引值和指向主鍵索引的位置，然後需要通過主鍵在查詢表的字段值，所以輔助索引存儲了主鍵的值。如果查詢所選擇的列只有主鍵，應該考慮通過索引覆蓋優化。 看下面的兩個sql，字段 pin 和completion_time有聯合索引，where條件差別只有comment_voucher_status = 0，但是執行時間差距巨大（第一個sql0.58s,第二個sql0.2s），為什麼呢？是不是很困惑





 





 





 





 

我們來看執行計劃，主要差別體現在extra，第一個sql用到Using index condition，而第二個sql用到Using index，因為pin和completion_time有聯合索引，而且查詢結果只選擇了主鍵id，所以第二個sql覆蓋了所有的where條件字段和查詢結果選擇字段，故實現了索引覆蓋。 思考：

當查詢字段只有主鍵時，更容易實現索引覆蓋，因為索引只要覆蓋where條件，就可以實現索引覆蓋。

4.總結與建議

索引的核心作用： （1）通過索引檢索僅需要數據 （2）從索引中直接獲取查詢結果

索引覆蓋的條件： （1）Select查詢的返回列包含在索引列中 （2）有where條件時，where條件中要包含索引列或複合索引的前導列 （3）查詢結果的總字段長度可以接受