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）查詢結果的總欄位長度可以接受