Mysql 索引覆蓋及回表查詢

在innoDB中，有兩大索引類，分別是

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛(ài)。我們立志把好的技術(shù)通過(guò)有效、簡(jiǎn)單的方式提供給客戶，將通過(guò)不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：域名申請(qǐng)、雅安服務(wù)器托管、營(yíng)銷軟件、網(wǎng)站建設(shè)、崗巴網(wǎng)站維護(hù)、網(wǎng)站推廣。

執(zhí)行上述語(yǔ)句，執(zhí)行過(guò)程如下圖

從圖中，我們可以看出，掃了兩個(gè)索引樹(shù)

（1）先從普通索引name找到lisi

（2）再根據(jù)主鍵值9，再在聚集索引中找到行記錄。

這就是回表查詢，先在普通索引中找到主鍵值，再在聚集索引中找到行記錄。

很顯然，在一棵索引樹(shù)上就能獲取SQL所需的所有列數(shù)據(jù)的，就是索引覆蓋。

如下語(yǔ)句

很顯然，我們可以直接在name索引上直接找到id,name，不用再去回表。

而且我們通過(guò)explain的extra屬性也能觀察到

像我們開(kāi)頭的SQL語(yǔ)句

我們只需要在name索引中再加個(gè)sex，name(name,sex)，這樣變成了聯(lián)合索引，也是索引覆蓋。

mysql優(yōu)化：覆蓋索引（延遲關(guān)聯(lián)）

我們都知道InnoDB采用的B+ tree來(lái)實(shí)現(xiàn)索引的，索引又分為主鍵索引(聚簇索引)和普通索引(二級(jí)索引)。

那么我們就來(lái)看下 基于主鍵索引和普通索引的查詢有什么區(qū)別？

舉個(gè)栗子：

可以看出我們有一個(gè)普通索引k，那么兩顆B+樹(shù)的示意圖如下：

[圖片上傳失敗...(image-9b05f7-1597911217600)]

（注：圖來(lái)自極客時(shí)間專欄）

當(dāng)我們查詢** select * from T where k=5 其實(shí)會(huì)先到k那個(gè)索引樹(shù)上查詢k = 5，然后找到對(duì)應(yīng)的id為500，最后回表到主鍵索引的索引樹(shù)找返回所需數(shù)據(jù)。

如果我們查詢 select id from T where k=5 **則不需要回表就直接返回。

也就是說(shuō)，基于非主鍵索引的查詢需要多掃描一棵索引樹(shù)。因此，我們?cè)趹?yīng)用中應(yīng)該盡量使用主鍵查詢。

概念如上，這里我們還是用例子來(lái)說(shuō)明：

/pre

[圖片上傳失敗...(image-20977-1597911217600)]

（注：圖來(lái)自極客時(shí)間專欄）

現(xiàn)在，我們一起來(lái)看看這條SQL查詢語(yǔ)句的執(zhí)行流程： select * from T where k between 3 and 5

在這個(gè)過(guò)程中， 回到主鍵索引樹(shù)搜索的過(guò)程，我們稱為回表。 可以看到，這個(gè)查詢過(guò)程讀了k索引樹(shù)的3條記錄（步驟1、3和5），回表了兩次（步驟2和4）。

在這個(gè)例子中，由于查詢結(jié)果所需要的數(shù)據(jù)只在主鍵索引上有，所以不得不回表。那么，有沒(méi)有可能經(jīng)過(guò)索引優(yōu)化，避免回表過(guò)程呢？

如果執(zhí)行的語(yǔ)句是select ID from T where k between 3 and 5，這時(shí)只需要查ID的值，而ID的值已經(jīng)在k索引樹(shù)上了，因此可以直接提供查詢結(jié)果，不需要回表。也就是說(shuō)，在這個(gè)查詢里面，索引k已經(jīng)“覆蓋了”我們的查詢需求，我們稱為覆蓋索引。

由于覆蓋索引可以減少樹(shù)的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段。

需要注意的是，在引擎內(nèi)部使用覆蓋索引在索引k上其實(shí)讀了三個(gè)記錄，R3~R5（對(duì)應(yīng)的索引k上的記錄項(xiàng)），但是對(duì)于MySQL的Server層來(lái)說(shuō)，它就是找引擎拿到了兩條記錄，因此MySQL認(rèn)為掃描行數(shù)是2。

上面介紹了那么多 其實(shí)是在為延遲關(guān)聯(lián)做鋪墊，這里直接續(xù)上我們本次慢查詢的sql：

我們都知道在做分頁(yè)時(shí)會(huì)用到Limit關(guān)鍵字去篩選所需數(shù)據(jù)，limit接受1個(gè)或者2個(gè)參數(shù)，接受兩個(gè)參數(shù)時(shí)第一個(gè)參數(shù)表示偏移量，即從哪一行開(kāi)始取數(shù)據(jù)，第二個(gè)參數(shù)表示要取的行數(shù)。 如果只有一個(gè)參數(shù)，相當(dāng)于偏移量為0。

當(dāng)偏移量很大時(shí)，如limit 100000,10 取第100001-100010條記錄，mysql會(huì)取出100010條記錄然后將前100000條記錄丟棄，這無(wú)疑是一種巨大的性能浪費(fèi)。

當(dāng)有這種寫(xiě)法時(shí)，我們可以采用延遲關(guān)聯(lián)來(lái)進(jìn)行優(yōu)化，重點(diǎn)關(guān)注： SELECT id FROM qa_question WHERE expert_id = 69 AND STATUS = 30 ORDER BY over_time DESC LIMIT 0, 10 ， 這里其實(shí)利用了索引覆蓋，where條件后的expert_id 是有添加索引的，這里查詢id 可以避免回表，大大提升效率。

工作中會(huì)遇到各種各樣的問(wèn)題，對(duì)于一個(gè)研發(fā)來(lái)說(shuō)最重要的是能夠從這些問(wèn)題中學(xué)到什么。好久沒(méi)有寫(xiě)博客了，究其原因還是自己變得懶惰了。 (￣ェ￣;)

最后以《高性能Mysql》中的一段話結(jié)束：

淺聊 MySQL索引覆蓋

盡量使用覆蓋索引，減少select *。 那么什么是覆蓋索引呢？ 覆蓋索引是指 查詢使用了索引，并 且需要返回的列，在該索引中已經(jīng)全部能夠找到 。

現(xiàn)在有一張用戶表tb_user;

索引情況：

接下來(lái)，我們來(lái)看一組SQL的執(zhí)行計(jì)劃，看看執(zhí)行計(jì)劃的差別，然后再來(lái)具體做一個(gè)解析。

Using where; Using Index：查找使用了索引，但是需要的數(shù)據(jù)都在索引列中能找到，所以不需 要回表查詢數(shù)據(jù)

Using index condition：查找使用了索引，但是需要回表查詢數(shù)據(jù)

因?yàn)?，在tb_user表中有一個(gè)聯(lián)合索引 idx_user_pro_age_sta，該索引關(guān)聯(lián)了三個(gè)字段 profession、age、status，而這個(gè)索引也是一個(gè)二級(jí)索引，所以葉子節(jié)點(diǎn)下面掛的是這一行的主 鍵id。 所以當(dāng)我們查詢返回的數(shù)據(jù)在 id、profession、age、status 之中，則直接走二級(jí)索引 直接返回?cái)?shù)據(jù)了。 如果超出這個(gè)范圍，就需要拿到主鍵id，再去掃描聚集索引，再獲取額外的數(shù)據(jù)了，這個(gè)過(guò)程就是回表。 而我們?nèi)绻恢笔褂胹elect * 查詢返回所有字段值，很容易就會(huì)造成回表 查詢（除非是根據(jù)主鍵查詢，此時(shí)只會(huì)掃描聚集索引）。

為了大家更清楚的理解，什么是覆蓋索引，什么是回表查詢，我們一起再來(lái)看下面的這組SQL的執(zhí)行過(guò) 程。

id是主鍵，是一個(gè)聚集索引。 name字段建立了普通索引，是一個(gè)二級(jí)索引（輔助索引）。

B. 執(zhí)行SQL : select * from tb_user where id = 2;

根據(jù)id查詢，直接走聚集索引查詢，一次索引掃描，直接返回?cái)?shù)據(jù)，性能高。

C. 執(zhí)行SQL：selet id,name from tb_user where name = 'Arm';

雖然是根據(jù)name字段查詢，查詢二級(jí)索引，但是由于查詢返回在字段為 id，name，在name的二級(jí)索 引中，這兩個(gè)值都是可以直接獲取到的，因?yàn)楦采w索引，所以不需要回表查詢，性能高。

D. 執(zhí)行SQL：selet id,name,gender from tb_user where name = 'Arm';

由于在name的二級(jí)索引中，不包含gender，所以，需要兩次索引掃描，也就是需要回表查詢，性能相 對(duì)較差一點(diǎn)。

MySql 索引（聚集索引，輔助索引，聯(lián)合索引，覆蓋索引..）

引入一個(gè)面試問(wèn)題:

看完以下以后再回顧，會(huì)發(fā)現(xiàn)迎刃而解

Mysql 可以為每一張表設(shè)置 存儲(chǔ)引擎 這里我們只說(shuō) InnoDB 存儲(chǔ)引擎.

由于實(shí)際情況，數(shù)據(jù)頁(yè)只能按照一棵 B+樹(shù) 進(jìn)行排序， 因此每張表只能擁有一個(gè) 聚集索引（即 主鍵）。

栗子：

每個(gè)葉子節(jié)點(diǎn)的索引行中包含了一個(gè)書(shū)簽（bookmark）. 該書(shū)簽是用來(lái)告訴 InnoDB存儲(chǔ)引擎哪里可以找到該索引對(duì)應(yīng)的數(shù)據(jù)行或者說(shuō) 行數(shù)據(jù)！ 由于InnoDB存儲(chǔ)引擎表， 是按照主鍵來(lái)構(gòu)建的， 所以 ，該書(shū)簽內(nèi)其實(shí)包含或者說(shuō)指向了 數(shù)據(jù)行所對(duì)應(yīng)的聚集索引鍵

也就是說(shuō) 輔助索引的 葉結(jié)點(diǎn)保存了 指向?qū)?yīng)數(shù)據(jù)的 聚集索引， 可以通過(guò)該聚集索引 找到對(duì)應(yīng)的數(shù)據(jù)行

輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因?yàn)槊繌埍砩峡梢杂卸鄠€(gè)輔助索引。

當(dāng)通過(guò)輔助索引來(lái)尋找數(shù)據(jù)時(shí)，InnoDB 存儲(chǔ)引擎會(huì)遍歷輔助索引并通過(guò)葉級(jí)別的指針獲得指向主鍵索引(聚集索引)的主鍵，然后再通過(guò)聚集索引找到一個(gè)完整的數(shù)據(jù)行。

例如：

聚集索引輔助索引關(guān)系:

： 又叫做組合索引 ， 輔助索引的一種 ， 和普通創(chuàng)建索引的方式一樣，不同的是 可以同時(shí)添加多列來(lái)作為索引項(xiàng);

從本質(zhì)上來(lái)說(shuō)，聯(lián)合索引也是一課B+樹(shù)

個(gè)人理解： 所謂最左原則， 是因?yàn)?存儲(chǔ)引擎構(gòu)建組合索引時(shí) 是根據(jù)最左邊的那一列索引項(xiàng)進(jìn)行排序的 ，所以使用組合索引，必須滿足 條件中必須存在 最左邊那一列的索引項(xiàng)，這樣 才可以找到對(duì)應(yīng)的索引，繼而 去尋找對(duì)應(yīng)的數(shù)據(jù)

： 又叫做 索引覆蓋，InnoDB中支持覆蓋索引，即 從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。

比如 這里沒(méi)有根據(jù)最左原則使用組合索引，但是 優(yōu)化器依然進(jìn)行選擇

共勉，歡迎指導(dǎo)謝謝~

「Mysql索引原理（七）」覆蓋索引

? ?通常大家都會(huì)根據(jù)查詢的WHERE條件來(lái)創(chuàng)建合適的索引，不過(guò)這只是索引優(yōu)化的一個(gè)方面。設(shè)計(jì)優(yōu)秀的索引應(yīng)該考慮到整個(gè)查詢，而不單單是WHERE條件部分。索引確實(shí)是一種查找數(shù)據(jù)的高效方式，但是MySQL也可以使用索引來(lái)直接獲取列的數(shù)據(jù)，這樣就不再需要讀取數(shù)據(jù)行。如果索引的葉子節(jié)點(diǎn)中已經(jīng)包含要查詢的數(shù)據(jù)，那么還有什么必要再回到表中查詢呢？ 如果一個(gè)索引覆蓋所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。

覆蓋索引是非常有用的工具，能夠極大地提高性能：

? ?在所有這些場(chǎng)景中，在索引中滿足查詢的成本一般比查詢行要小得多。

? ?不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲(chǔ)索引列的值，而哈希索引、空間索引和全文索引都不存儲(chǔ)索引列的值，所以MySQL只能使用B+Tree索引所覆蓋索引。另外，不同的存儲(chǔ)引擎實(shí)現(xiàn)覆蓋索引的方式也不同，而且不是所有的引擎都支持覆蓋索引。

? ?當(dāng)發(fā)起一個(gè)唄索引覆蓋的查詢是，在EXPLAIN的Extra列可以看到“Using index”的信息。

如： explain select col1 from layout_test where col2=99

? ?索引覆蓋查詢還有很多陷阱可能會(huì)導(dǎo)致無(wú)法實(shí)現(xiàn)優(yōu)化。MySQL查詢優(yōu)化器會(huì)在執(zhí)行查詢前判斷是否有一個(gè)索引能進(jìn)行覆蓋。假設(shè)索引覆蓋了wehre條件中的字段，但不是整個(gè)查詢涉及的字段。mysql5.5和更早的版本也總是會(huì)回表獲取數(shù)據(jù)行，盡管并不需要這一行且最終會(huì)被過(guò)濾掉。

如： EXPLAIN select * from people where last_name='Allen' and first_name like '%Kim%'

這里索引無(wú)法覆蓋該查詢，有兩個(gè)原因：

這條語(yǔ)句只檢索1行，而之前的 like '%Kim%'要檢索3行。

也有辦法解決上面所說(shuō)的兩個(gè)問(wèn)題，需要重寫(xiě)查詢并巧妙設(shè)計(jì)索引。

? ?這種方式叫做延遲關(guān)聯(lián)，因?yàn)檠舆t了對(duì)列的訪問(wèn)。在查詢第一個(gè)階段MySQL可以使用覆蓋索引，因?yàn)樗饕酥麈Iid的值，不需要做二次查找。

? ?在FROM子句的子查詢中找到匹配的id，然后根據(jù)這些id值在外層查詢匹配獲取需要的所有列值。雖然無(wú)法使用索引覆蓋整個(gè)查詢，但總算比完全無(wú)法利用索引覆蓋的好吧。

數(shù)據(jù)量大了怎么辦？

? ?這樣優(yōu)化的效果取決于WHERE條件匹配返回的行數(shù)。假設(shè)這個(gè)people表有100萬(wàn)行，我們看一下上面兩個(gè)查詢?cè)谌齻€(gè)不同的數(shù)據(jù)集上的表現(xiàn)，每個(gè)數(shù)據(jù)集都包含100萬(wàn)行。

實(shí)例1中 ，查詢返回了一個(gè)很大的結(jié)果集，因此看不到優(yōu)化的效果。大部分時(shí)間都花在讀取和發(fā)送數(shù)據(jù)上了。

實(shí)例2中 ，經(jīng)過(guò)索引過(guò)濾，尤其是第二個(gè)條件過(guò)濾后只返回了很少的結(jié)果集，優(yōu)化的效果非常明顯：在這個(gè)數(shù)據(jù)及上性能提高了很多，優(yōu)化后的查詢效率主要得益于只需讀取40行完整數(shù)據(jù)行，而不是原查詢中需要的30000行。

實(shí)例3中 ，子查詢效率反而下降。因?yàn)樗饕^(guò)濾時(shí)符合第一個(gè)條件的結(jié)果集已經(jīng)很小了，所以子查詢帶來(lái)的成本反而比從表中直接提取完整行更高。

? ?在大多數(shù)存儲(chǔ)引擎中，覆蓋索引只能覆蓋那些只訪問(wèn)索引中部分列的查詢。不過(guò)，可以更進(jìn)一步優(yōu)化InnoDB?；叵胍幌?，InnoDB的二級(jí)索引的葉子節(jié)點(diǎn)都包含了主鍵的值，這意味著InnoDB的二級(jí)索引可以有效地利用這些額外的主鍵列來(lái)覆蓋查詢。

? ?例如，people表中l(wèi)ast_name字段有一個(gè)二級(jí)索引，雖然該索引的列不包括主鍵id，但也能夠用于對(duì)id做覆蓋查詢：

select id,last_name from people where last_name='hua'

當(dāng)前題目：mysql覆蓋索引怎么用,mysql什么是索引覆蓋
網(wǎng)站路徑：http://jinyejixie.com/article22/dssddjc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站內(nèi)鏈、虛擬主機(jī)、定制網(wǎng)站、建站公司、網(wǎng)站維護(hù)、網(wǎng)站收錄

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)