Mysql索引覆蓋

語言: CN / TW / HK

作者:京東零售 孫濤

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