Mysql 索引覆蓋及回表查詢

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

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

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

從圖中，我們可以看出，掃了兩個索引樹

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

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

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

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

如下語句

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

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

像我們開頭的SQL語句

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

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

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

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

舉個栗子：

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

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

（注：圖來自極客時間專欄）

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

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

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

概念如上，這里我們還是用例子來說明：

/pre

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

（注：圖來自極客時間專欄）

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

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

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

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

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

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

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

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

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

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

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

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

淺聊 MySQL索引覆蓋

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

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

索引情況：

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

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

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

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

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

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

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

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

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

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

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

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

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

引入一個面試問題:

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

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

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

栗子：

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

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

輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因為每張表上可以有多個輔助索引。

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

例如：

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

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

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

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

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

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

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

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

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

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

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

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

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

如： explain select col1 from layout_test where col2=99

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

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

這里索引無法覆蓋該查詢，有兩個原因：

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

也有辦法解決上面所說的兩個問題，需要重寫查詢并巧妙設(shè)計索引。

? ?這種方式叫做延遲關(guān)聯(lián)，因為延遲了對列的訪問。在查詢第一個階段MySQL可以使用覆蓋索引，因為索引包含了主鍵id的值，不需要做二次查找。

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

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

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

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

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

實例3中 ，子查詢效率反而下降。因為索引過濾時符合第一個條件的結(jié)果集已經(jīng)很小了，所以子查詢帶來的成本反而比從表中直接提取完整行更高。

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

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

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

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

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

廣告

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