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

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

1. 索引條目通常遠小于數(shù)據(jù)行大小，所以如果只需要讀取索引，那MySQL就會極大地減少數(shù)據(jù)訪問量。這對緩存的負載非常重要，因為這種情況下響應時間大部分花費在數(shù)據(jù)拷貝上。覆蓋索引對于I/O密集型的應用也有幫助，因為索引比數(shù)據(jù)更小，更容易全部放入內存中。

2. 因為索引是按照列值順序存儲的，所以對于I/O密集型的范圍查詢會比隨機從磁盤讀取每一行數(shù)據(jù)的I/O要少的多。對于某些存儲引擎，例如MyISAM甚至可以通過OPTIMIZE命令使得索引完全順序排列，這讓簡單的范圍查詢能使用完全順序的索引訪問。

3. 一些存儲引擎，如MyISAM在內存中只緩存索引，數(shù)據(jù)則依賴操作系統(tǒng)來緩存，因此要訪問數(shù)據(jù)需要一次系統(tǒng)調用。這可能會導致嚴重的性能問題，尤其是那些系統(tǒng)調用找了數(shù)據(jù)訪問中的最大的開銷的場景。

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

? ? ? ?在所有這些場景中，在索引中滿足查詢的成本一般比查詢行要小得多。
? ? ? ?不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引列的值，而哈希索引、空間索引和全文索引都不存儲索引列的值，所以MySQL只能使用B+Tree索引所覆蓋索引。另外，不同的存儲引擎實現(xiàn)覆蓋索引的方式也不同，而且不是所有的引擎都支持覆蓋索引。

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

如：explain select col1 from layout_test where col2=99

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

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

image.png

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

1. 沒有任何索引能夠覆蓋這個查詢。因為查詢從表中選擇了所有的列，而沒有任何索引覆蓋了所有的列。不過理論上mysql有一個捷徑可以利用：where條件中的列是由索引可以覆蓋的，因此Mysql可以使用該索引找到對應的last_name并檢查是否first_name是否匹配，過濾之后再讀取所需要的數(shù)據(jù)行。

2. MySQL不能在索引中執(zhí)行l(wèi)ike操作。這是底層存儲引擎API的限制。MySQL5.5和更早的版本只允許在索引中做簡單的比較操作（等于、不等于及大于）。MySQL能在索引中做最左前綴匹配的LIKE比較，因為該操作可以轉換為簡單的比較操作，但是如果是通配符開頭的LIKE查詢，存儲引擎無法做比較匹配。這種情況下，MySQL服務器只能提取數(shù)據(jù)行的值而不是索引值來做比較。

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

這條語句只檢索1行，而之前的 like '%Kim%'要檢索3行。
也有辦法解決上面所說的兩個問題，需要重寫查詢并巧妙設計索引。

EXPLAIN select * from people JOIN 
    ( select id as childid from people where  last_name='Allen' and first_name like '%Kim%' ) as t1 on (t1.childid=people.id)

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

-- 子查詢利用到了覆蓋索引

EXPLAIN select id  from people where  last_name='Allen' and first_name like '%Kim%'

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

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

1. 第一個數(shù)據(jù)集。last_name為 ‘ming’ 的記錄有30000條，其中對應的first_name包含 ‘xiao’ 的記錄有20000條。
2. 第二個數(shù)據(jù)集。last_name為 ‘ming’ 的記錄有30000條，其中對應的first_name包含 ‘xiao’ 的記錄有40條。
3. 第三個數(shù)據(jù)集。last_name為 ‘ming’ 的記錄有50條，其中對應的first_name包含 ‘xiao’ 的記錄有10條。

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

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

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

? ? ? ?在大多數(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'