1.什么是覆蓋索引

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

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

2.覆蓋索引為什么快

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

覆蓋索引就實現(xiàn)了從非聚簇索引中直接獲取數(shù)據(jù)，所以效率會提升。

3.SQL優(yōu)化場景

（1）無where條件

請看下面的sql

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

那么如何優(yōu)化？優(yōu)化措施很簡單，就是對查詢列建立索引。如下，

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

Possible_keys為null，說明沒有where條件時優(yōu)化器無法通過索引檢索數(shù)據(jù)；

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

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

（2）where條件區(qū)分度低

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

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

再來看執(zhí)行計劃

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

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

思考：

當(dāng)where條件字段區(qū)分度低（過濾性差），且where條件與查詢字段總數(shù)較少的情況下，使用索引覆蓋優(yōu)化，是個不錯的選擇。

（3）查詢僅選擇主鍵

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

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

當(dāng)查詢字段只有主鍵時，更容易實現(xiàn)索引覆蓋，因為索引只要覆蓋where條件，就可以實現(xiàn)索引覆蓋。

4.總結(jié)與建議

索引的核心作用： （1）通過索引檢索僅需要數(shù)據(jù) （2）從索引中直接獲取查詢結(jié)果
索引覆蓋的條件： （1）Select查詢的返回列包含在索引列中 （2）有where條件時，where條件中要包含索引列或復(fù)合索引的前導(dǎo)列 （3）查詢結(jié)果的總字段長度可以接受

到此這篇關(guān)于Mysql索引覆蓋的實現(xiàn)的文章就介紹到這了,更多相關(guān)Mysql索引覆蓋內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論