mysql覆蓋索引怎么用,mysql什么是索引覆蓋

Mysql 索引覆蓋及回表查詢

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

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

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

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

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

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

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

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

如下語句

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

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

像我們開頭的SQL語句

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

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

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

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

舉個(gè)栗子：

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

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

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

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

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

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

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

/pre

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

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

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

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

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

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

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

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

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

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

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

當(dāng)有這種寫法時(shí)，我們可以采用延遲關(guān)聯(lián)來進(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 可以避免回表，大大提升效率。

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

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

淺聊 MySQL索引覆蓋

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

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

索引情況：

接下來，我們來看一組SQL的執(zhí)行計(jì)劃，看看執(zhí)行計(jì)劃的差別，然后再來具體做一個(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è)二級索引，所以葉子節(jié)點(diǎn)下面掛的是這一行的主 鍵id。 所以當(dāng)我們查詢返回的數(shù)據(jù)在 id、profession、age、status 之中，則直接走二級索引 直接返回?cái)?shù)據(jù)了。 如果超出這個(gè)范圍，就需要拿到主鍵id，再去掃描聚集索引，再獲取額外的數(shù)據(jù)了，這個(gè)過程就是回表。 而我們?nèi)绻恢笔褂胹elect * 查詢返回所有字段值，很容易就會造成回表 查詢（除非是根據(jù)主鍵查詢，此時(shí)只會掃描聚集索引）。

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

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

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字段查詢，查詢二級索引，但是由于查詢返回在字段為 id，name，在name的二級索 引中，這兩個(gè)值都是可以直接獲取到的，因?yàn)楦采w索引，所以不需要回表查詢，性能高。

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

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

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

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

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

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

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

栗子：

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

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

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

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

例如：

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

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

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

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

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

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

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

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

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

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

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

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

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

如： explain select col1 from layout_test where col2=99

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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