快捷導(dǎo)航

Mysql索引覆蓋的實(shí)現(xiàn)

更新時(shí)間：2023年03月03日 10:14:43 作者：京東零售孫濤

本文主要介紹了Mysql索引覆蓋的實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

1.什么是覆蓋索引

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

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

2.覆蓋索引為什么快

innodb存儲(chǔ)引擎底層實(shí)現(xiàn)包括B+樹索引和哈希索引，innodb存儲(chǔ)引擎默認(rèn)的索引模型/結(jié)構(gòu)是B+樹，所以大部分時(shí)候我們使用的都是B+樹索引，因?yàn)樗己玫男阅芎吞匦愿m合于構(gòu)建高并發(fā)系統(tǒng)。根據(jù)索引的存儲(chǔ)方式來劃分，索引可以分為聚簇索引和非聚簇索引。聚簇索引的特點(diǎn)是葉子節(jié)點(diǎn)包含了完整的記錄行，而非聚簇索引的葉子節(jié)點(diǎn)只有索引字段和主鍵ID。非聚簇索引中因?yàn)椴缓型暾臄?shù)據(jù)信息，查找完整的數(shù)據(jù)記錄需要回表，所以一次查詢操作實(shí)際上要做兩次索引查詢。而如果所有的索引查詢都要經(jīng)過兩次才能查到，那么肯定會(huì)引起效率下降，畢竟能少查一次就少查一次。

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

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

（1）無where條件

請(qǐng)看下面的sql

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

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

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

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

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

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

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

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

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

再來看執(zhí)行計(jì)劃

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

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

思考：

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

（3）查詢僅選擇主鍵

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

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

當(dāng)查詢字段只有主鍵時(shí)，更容易實(shí)現(xiàn)索引覆蓋，因?yàn)樗饕灰采wwhere條件，就可以實(shí)現(xiàn)索引覆蓋。

4.總結(jié)與建議

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

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

您可能感興趣的文章: