一個(gè)通常的建議是為WHERE條件創(chuàng)建索引，但這其實(shí)是片面的。索引應(yīng)當(dāng)為全部查詢(xún)?cè)O(shè)計(jì)，而不僅僅是WHERE條件。索引確實(shí)能有效地查找數(shù)據(jù)行，但MySQL也能夠使用索引獲取列數(shù)據(jù)，這樣根本不需要去讀取一行數(shù)據(jù)。畢竟，索引的葉子節(jié)點(diǎn)包含了索引對(duì)應(yīng)的值。當(dāng)年能夠讀取索引就能夠拿到想要的數(shù)據(jù)時(shí)為什么還去讀數(shù)據(jù)行呢？當(dāng)索引包含了所有查詢(xún)的數(shù)據(jù)時(shí)，這個(gè)索引就稱(chēng)之為覆蓋索引。

覆蓋索引能夠成為一個(gè)非常有力的工具并且能夠顯著改善性能?？紤]一下不讀數(shù)據(jù)只需要讀取索引的情況：

• 索引值通常會(huì)比整個(gè)行存儲(chǔ)空間小很多，因此MySQL只讀取索引值時(shí)可以只讀取很少的數(shù)據(jù)。這對(duì)于緩存負(fù)荷來(lái)說(shuō)十分重要——響應(yīng)時(shí)間大部分消耗在復(fù)制數(shù)據(jù)。對(duì)于磁盤(pán)I/O而言也是一樣，因?yàn)樗饕龜?shù)據(jù)相比行數(shù)據(jù)存儲(chǔ)空間小很多，因此更節(jié)省I/O負(fù)載和內(nèi)存占用（對(duì)于MyISAM引擎更顯著，因?yàn)镸yISAM可以將索引打包使得存儲(chǔ)空間更?。?/li>
• 索引是按索引值的順序存儲(chǔ)的，因此I/O訪(fǎng)問(wèn)跨度相比隨機(jī)磁盤(pán)位置獲取行數(shù)據(jù)而言消耗的I/O頻次更少。對(duì)于某些存儲(chǔ)引擎，例如MyISAM和Percona XtraDB，你甚至能夠使用OPTIMIZE優(yōu)化表獲得完全有序的索引，這會(huì)使得簡(jiǎn)單范圍的查詢(xún)完全使用順序訪(fǎng)問(wèn)。
• 有些存儲(chǔ)引擎，例如MyISAM，MySQL內(nèi)存中只緩存索引。由于操作系統(tǒng)為MyISAM緩存了數(shù)據(jù)，訪(fǎng)問(wèn)時(shí)通常需要一個(gè)系統(tǒng)調(diào)用。這可能導(dǎo)致巨大的性能影響，尤其是對(duì)于緩存負(fù)荷場(chǎng)景來(lái)說(shuō)，系統(tǒng)調(diào)用對(duì)于數(shù)據(jù)訪(fǎng)問(wèn)來(lái)說(shuō)是最昂貴的代價(jià)。
• 由于InnoDB的聚集索引，覆蓋索引對(duì)于InnoDB來(lái)說(shuō)十分有幫助。InnoDB的輔助索引在其葉子節(jié)點(diǎn)中保存了行的主鍵值。因此，輔助索引覆蓋查詢(xún)后可以避免進(jìn)行主鍵查詢(xún)。
  

在所有的場(chǎng)景中，最典型的就是相比查找數(shù)據(jù)行，只包含索引列的查詢(xún)的代價(jià)相當(dāng)?shù)汀Ｐ枰⒁獾氖?，聚集索引并不是任意?lèi)型的索引。聚集索引必須存儲(chǔ)索引數(shù)據(jù)列對(duì)應(yīng)的值。哈希，空間和全文索引并沒(méi)有存儲(chǔ)這些值，因此MySQL只能使用二叉樹(shù)去覆蓋查詢(xún)。而且，不同的存儲(chǔ)引擎實(shí)現(xiàn)覆蓋索引的方式不同，并且并不是全部的存儲(chǔ)引擎都支持覆蓋索引（例如Memory存儲(chǔ)引擎當(dāng)前就不支持）。

當(dāng)你驗(yàn)證查詢(xún)中索引使用了覆蓋索引時(shí)，使用Explain語(yǔ)句時(shí)，會(huì)在Extra列中看到“Using index”。例如，在store_goods表有一個(gè)(shop_id, goods_category_id1)的多列索引。MySQL可以在查詢(xún)返回?cái)?shù)據(jù)只有這兩列時(shí)使用索引：

EXPLAIN SELECT `goods_category_id1`,`shop_id` FROM `store_goods` WHERE 1

覆蓋索引查詢(xún)?cè)谀承┣闆r會(huì)讓這樣的優(yōu)化失效。MySQL查詢(xún)優(yōu)化器在執(zhí)行查詢(xún)時(shí)會(huì)判斷索引是否覆蓋到。假設(shè)索引覆蓋了WHERE條件，但沒(méi)有覆蓋整個(gè)查詢(xún)。如果評(píng)估結(jié)果決定不走覆蓋索引，那么MySQL 5.5及以前的版本會(huì)直接獲取數(shù)據(jù)行，即便是不需要這些數(shù)據(jù)，然后才會(huì)過(guò)濾掉。

讓我們看一下為什么這種情況會(huì)發(fā)生，然后如何重寫(xiě)查詢(xún)以便解決這個(gè)問(wèn)題。首先查詢(xún)是這樣的：

EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY' AND title like '%APOLLO%'

這個(gè)時(shí)候的結(jié)果是不會(huì)走覆蓋索引，而是普通的索引，這是因?yàn)椋?/p>

• 沒(méi)有索引覆蓋了查詢(xún)數(shù)據(jù)列，因?yàn)槲覀儚臄?shù)據(jù)表讀取了全部列并且沒(méi)有索引列覆蓋了全部列。理論上，MySQL還有一個(gè)快捷方式可以使用，那就是WHERE條件中使用了索引覆蓋的列，因此MySQL可以先使用這個(gè)索引找到對(duì)應(yīng)的actor，然后在檢查他們的title是否匹配，然后在讀取滿(mǎn)足條件的全部的數(shù)據(jù)行。
• 對(duì)于早期的低版本的存儲(chǔ)引擎API（MySQL 5.5以前的版本）來(lái)說(shuō)，MySQL無(wú)法在索引中使用LIKE操作，而只支持簡(jiǎn)單的比較操作（=，IN，>=）。MySQL可以在索引中使用前綴匹配的LIKE查詢(xún)，這是因?yàn)樗梢詫⑺鼈冝D(zhuǎn)換為比較操作。但是前導(dǎo)通配符（也就是LIKE中前置的%）導(dǎo)致存儲(chǔ)引擎無(wú)法評(píng)估匹配條件。因此，MySQL會(huì)獲取行數(shù)據(jù)再比較，而不是索引的值。
  

有一種方式可以使用巧妙的組合索引和重寫(xiě)查詢(xún)條件。我們可以將索引擴(kuò)展到(artist, title, prod_id)，然后像下面那樣重寫(xiě)查詢(xún)語(yǔ)句：

EXPLAIN SELECT * 
FROM products
	JOIN (
    SELECT prod_id
    FROM products
    WHERE actor='SEAN CARREY' AND TITLE LIKE '%APOLLO%'
 ) AS t1 ON (t1.prod_id=products.prod_id)

我們稱(chēng)之為“遞延JOIN(deferred join)”，因?yàn)樗舆t了列的訪(fǎng)問(wèn)。在第一階段的查詢(xún)中，當(dāng)它在子查詢(xún)中找到了匹配的行的過(guò)程中，MySQL使用了覆蓋索引。雖然在整個(gè)查詢(xún)中沒(méi)有覆蓋到，但總比沒(méi)有的好。

這種優(yōu)化的效果好壞取決于WHERE條件查找到了多少行數(shù)據(jù)。假設(shè)products表包含了上百萬(wàn)行的數(shù)據(jù)?？梢詫?duì)比一下這兩種查詢(xún)的性能對(duì)比，總的數(shù)據(jù)為100萬(wàn)行。

• 第一種情況：有30000個(gè)products的actor是“SEAN CARREY”，其中20000個(gè)的title包含“APOLLO”；
• 第二種情況：有30000個(gè)products的actor是“SEAN CARREY”，其中40個(gè)的title包含“APOLLO”；
• 第三種情況：有50個(gè)products的actor是“SEAN CARREY”，其中10個(gè)的title包含“APOLLO”。
  

對(duì)比結(jié)果如下表。

結(jié)果的解釋如下：

• 在第一種情況中，查詢(xún)返回了很大的結(jié)果集，因此看不到優(yōu)化效果。大部分時(shí)間花在了讀取和發(fā)送數(shù)據(jù)。
• 在第二種情況中，使用覆蓋索引后子查詢(xún)過(guò)濾得到了一個(gè)小的結(jié)果集，這樣優(yōu)化的效果是性能提升了5倍。產(chǎn)生這種效果的原因是相比查出30000行的數(shù)據(jù)集，這里只需要讀取40行。
• 第三種情況顯示了子查詢(xún)失效了。覆蓋索引過(guò)濾返回的結(jié)果集太少了，導(dǎo)致子查詢(xún)的代價(jià)比直接從數(shù)據(jù)表讀取數(shù)據(jù)還要高。

在大多數(shù)存儲(chǔ)引擎中，一個(gè)索引只能夠覆蓋訪(fǎng)問(wèn)列是索引的一部分。然而，InnoDB實(shí)際上會(huì)做進(jìn)一步的優(yōu)化。想想InnoDB的的輔助索引在葉子節(jié)點(diǎn)中存儲(chǔ)了主鍵的值。這意味著InnoDB的輔助索引實(shí)際上有了額外的列幫助InnoDB使用覆蓋索引。 例如，sakila.actor表使用了InnoDB，然后在last_name有一個(gè)索引，因此這個(gè)索引能夠覆蓋或者主鍵actor_id的查詢(xún)——即便這個(gè)列并不是索引的一部分。

EXPLAIN SELECT actor_id, last_name
FROM sakila.actor WHERE last_name = 'HOPPER'

以上就是MySQL 覆蓋索引的優(yōu)點(diǎn)的詳細(xì)內(nèi)容，更多關(guān)于MySQL 覆蓋索引的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論