Mysql覆蓋索引詳解

更新時(shí)間：2019年03月26日 11:15:02 作者：jh993627471

今天小編就為大家分享一篇關(guān)于Mysql覆蓋索引詳解，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧

概念

如果索引包含所有滿足查詢需要的數(shù)據(jù)的索引成為覆蓋索引(Covering Index)，也就是平時(shí)所說(shuō)的不需要回表操作

判斷標(biāo)準(zhǔn)

使用explain，可以通過(guò)輸出的extra列來(lái)判斷，對(duì)于一個(gè)索引覆蓋查詢，顯示為using index,MySQL查詢優(yōu)化器在執(zhí)行查詢前會(huì)決定是否有索引覆蓋查詢

注意

1、覆蓋索引也并不適用于任意的索引類型，索引必須存儲(chǔ)列的值

2、Hash 和full-text索引不存儲(chǔ)值，因此MySQL只能使用B-TREE

3、并且不同的存儲(chǔ)引擎實(shí)現(xiàn)覆蓋索引都是不同的

4、并不是所有的存儲(chǔ)引擎都支持它們

5、如果要使用覆蓋索引，一定要注意SELECT 列表值取出需要的列，不可以是SELECT *，因?yàn)槿绻麑⑺凶侄我黄鹱鏊饕龝?huì)導(dǎo)致索引文件過(guò)大，查詢性能下降，不能為了利用覆蓋索引而這么做

如果一個(gè)索引包含(或覆蓋)所有需要查詢的字段的值，稱為‘覆蓋索引'。即只需掃描索引而無(wú)須回表。

只掃描索引而無(wú)需回表的優(yōu)點(diǎn)：

1.索引條目通常遠(yuǎn)小于數(shù)據(jù)行大小，只需要讀取索引，則mysql會(huì)極大地減少數(shù)據(jù)訪問(wèn)量。

2.因?yàn)樗饕前凑樟兄淀樞虼鎯?chǔ)的，所以對(duì)于IO密集的范圍查找會(huì)比隨機(jī)從磁盤(pán)讀取每一行數(shù)據(jù)的IO少很多。

3.一些存儲(chǔ)引擎如myisam在內(nèi)存中只緩存索引，數(shù)據(jù)則依賴于操作系統(tǒng)來(lái)緩存，因此要訪問(wèn)數(shù)據(jù)需要一次系統(tǒng)調(diào)用

4.innodb的聚簇索引，覆蓋索引對(duì)innodb表特別有用。(innodb的二級(jí)索引在葉子節(jié)點(diǎn)中保存了行的主鍵值，所以如果二級(jí)主鍵能夠覆蓋查詢，則可以避免對(duì)主鍵索引的二次查詢)

覆蓋索引必須要存儲(chǔ)索引列的值，而哈希索引、空間索引和全文索引不存儲(chǔ)索引列的值，所以mysql只能用B-tree索引做覆蓋索引。

當(dāng)發(fā)起一個(gè)索引覆蓋查詢時(shí)，在explain的extra列可以看到using index的信息

覆蓋索引的坑：mysql查詢優(yōu)化器會(huì)在執(zhí)行查詢前判斷是否有一個(gè)索引能進(jìn)行覆蓋，假設(shè)索引覆蓋了where條件中的字段，但不是整個(gè)查詢涉及的字段，mysql5.5和之前的版本也會(huì)回表獲取數(shù)據(jù)行，盡管并不需要這一行且最終會(huì)被過(guò)濾掉。

如上圖則無(wú)法使用覆蓋查詢，原因：

1.沒(méi)有任何索引能夠覆蓋這個(gè)索引。因?yàn)椴樵儚谋碇羞x擇了所有的列，而沒(méi)有任何索引覆蓋了所有的列。

2.mysql不能在索引中執(zhí)行LIke操作。mysql能在索引中做最左前綴匹配的like比較，但是如果是通配符開(kāi)頭的like查詢，存儲(chǔ)引擎就無(wú)法做比較匹配。這種情況下mysql只能提取數(shù)據(jù)行的值而不是索引值來(lái)做比較

優(yōu)化后SQL：添加索引（artist,title,prod_id），使用了延遲關(guān)聯(lián)(延遲了對(duì)列的訪問(wèn))

說(shuō)明：在查詢的第一階段可以使用覆蓋索引，在from子句中的子查詢找到匹配的prod_id，然后根據(jù)prod_id值在外層查詢匹配獲取需要的所有值。

5.5時(shí)API設(shè)計(jì)不允許mysql將過(guò)濾條件傳到存儲(chǔ)引擎層（是把數(shù)據(jù)從存儲(chǔ)引擎拉到服務(wù)器層，在根據(jù)條件過(guò)濾），5.6之后由于ICP這個(gè)特性改善了查詢執(zhí)行方式

當(dāng)MySQL不能使用索引進(jìn)行排序時(shí)，就會(huì)利用自己的排序算法(快速排序算法)在內(nèi)存(sort buffer)中對(duì)數(shù)據(jù)進(jìn)行排序，如果內(nèi)存裝載不下，它會(huì)將磁盤(pán)上的數(shù)據(jù)進(jìn)行分塊，再對(duì)各個(gè)數(shù)據(jù)塊進(jìn)行排序，然后將各個(gè)塊合并成有序的結(jié)果集（實(shí)際上就是外排序）

對(duì)于filesort，MySQL有兩種排序算法

1、兩遍掃描算法(Two passes)

實(shí)現(xiàn)方式是先將須要排序的字段和可以直接定位到相關(guān)行數(shù)據(jù)的指針信息取出，然后在設(shè)定的內(nèi)存（通過(guò)參數(shù)sort_buffer_size設(shè)定）中進(jìn)行排序，完成排序之后再次通過(guò)行指針信息取出所需的Columns

注：該算法是4.1之前采用的算法，它需要兩次訪問(wèn)數(shù)據(jù)，尤其是第二次讀取操作會(huì)導(dǎo)致大量的隨機(jī)I/O操作。另一方面，內(nèi)存開(kāi)銷(xiāo)較小

2、一次掃描算法(single pass)

該算法一次性將所需的Columns全部取出，在內(nèi)存中排序后直接將結(jié)果輸出
注：從 MySQL 4.1 版本開(kāi)始使用該算法。它減少了I/O的次數(shù)，效率較高，但是內(nèi)存開(kāi)銷(xiāo)也較大。如果我們將并不需要的Columns也取出來(lái)，就會(huì)極大地浪費(fèi)排序過(guò)程所需要的內(nèi)存。在 MySQL 4.1 之后的版本中，可以通過(guò)設(shè)置 max_length_for_sort_data 參數(shù)來(lái)控制 MySQL 選擇第一種排序算法還是第二種。當(dāng)取出的所有大字段總大小大于 max_length_for_sort_data 的設(shè)置時(shí)，MySQL 就會(huì)選擇使用第一種排序算法，反之，則會(huì)選擇第二種。為了盡可能地提高排序性能，我們自然更希望使用第二種排序算法，所以在 Query 中僅僅取出需要的 Columns 是非常有必要的。

當(dāng)對(duì)連接操作進(jìn)行排序時(shí)，如果ORDER BY僅僅引用第一個(gè)表的列，MySQL對(duì)該表進(jìn)行filesort操作，然后進(jìn)行連接處理，此時(shí)，EXPLAIN輸出“Using filesort”；否則，MySQL必須將查詢的結(jié)果集生成一個(gè)臨時(shí)表，在連接完成之后進(jìn)行filesort操作，此時(shí)，EXPLAIN輸出 “Using temporary;Using filesort”

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請(qǐng)查看下面相關(guān)鏈接

您可能感興趣的文章: