高性能的MySQL（5）索引政策-覆寫索引與索引排序

一、覆寫索引

索引是一種查找資料的高效方式，但是MySQL也可以使用索引來直接擷取列的資料，這樣就不再需要讀取資料行。如果索引的葉子節點中已經包含要查詢的資料，那麼還有什麼必要回表查詢呢？

如果一個索引包含或者說覆寫所有需要查詢的字段的值，我們就稱之為“覆寫索引”。

覆寫索引的好處：

1、索引條目通常遠小于資料行大小，是以如果隻需要讀取索引，就極大的減少資料通路量。這對MyISAM尤其正确，因為MyISAM能壓縮索引以變得更小。

2、因為索引是按照順序存儲的，是以對于I/O密集型的範圍查詢會比随機從磁盤讀取每一行資料的I/O要少很多。

3、由于InnoDB的聚簇索引，覆寫索引對InnoDB特别有用。InnoDB的二級索引在葉子節點中儲存了行的主鍵值，是以如果二級主鍵能夠覆寫查詢，則可以避免對主鍵索引的二次查詢。

覆寫索引必須要存儲索引列的值，而哈希索引、空間索引、和全文索引都不能存儲列的值，是以MySQL隻能使用B-Tree索引做覆寫索引。

當發起一個索引覆寫的查詢時，在EXPLAIN的Extra列可以看到Usingindex的資訊。例如：

如果索引覆寫了WHERE條件中的字段，但不是整個查詢涉及的字段，看看是什麼情況

<a href="http://blog.51cto.com/attachment/201310/120323463.png" target="_blank"></a>

沒有任何索引能覆寫這個查詢，因為查詢從表中選擇了所有的列，而沒有任何索引覆寫所有的列。但是索引還是用到了。

接下來可以對比一下，可以使用聚簇索引的InnoDB和MyISAM對覆寫索引的差別。

首先看MyISAM表，表結構如下

<a href="http://blog.51cto.com/attachment/201310/122724869.png" target="_blank"></a>

看一下如下這個查詢，沒有用到覆寫索引

<a href="http://blog.51cto.com/attachment/201310/122849961.png" target="_blank"></a>

對同樣結構的InnoDB引擎，來看下會有什麼不同的結果。

<a href="http://blog.51cto.com/attachment/201310/123034119.png" target="_blank"></a>

同樣的查詢，隻是表引擎不一樣，看看結果

<a href="http://blog.51cto.com/attachment/201310/123128899.png" target="_blank"></a>

這是因為InnoDB的二級索引的葉子節點包含了主鍵的值，這意味着InnoDB的二級索引可以有效的利用這些額外的主鍵來覆寫查詢。

由于InnoDB的聚簇索引，雖然查詢條件的索引列并不包含主鍵，但是也能夠做到對主鍵做覆寫查詢。

二、使用索引掃描來排序

MySQL有兩種方式可以生成有序結果。

1、通過排序操作

2、按索引順序掃描

如果EXPLAIN出來的type列的值為“index”，則說明使用了索引掃描排序。

MySQL可以使用同一個索引既滿足排序，有用于查找行，設計索引時應該進可能的滿足這兩種任務才是最好的。

隻有當索引的順序和ORDERBY的順序完全一緻，并且所有列的排序方向都一樣時，才能使用索引來對結果進行排序。如果是關聯多個張表，則隻有ORDERBY子句引用的字段全部是第一個表時，才能使用索引排序。同時ORDERBY也需要滿足最左字首的要求。

有一種情況下ORDERBY可以不滿足最左字首要求，那就是前導列為常量的時候，接下來我們用例子來看看。

<a href="http://blog.51cto.com/attachment/201310/135201217.png" target="_blank"></a>

雖然ORDERBY子句不滿足最左字首，但是依然可以索引排序，這是因為索引的第一列被指定為一個常數。

如果不是常數，不會使用索引排序

<a href="http://blog.51cto.com/attachment/201310/135954642.png" target="_blank"></a>

下面這也可以使用索引排序

<a href="http://blog.51cto.com/attachment/201310/135447433.png" target="_blank"></a>

<a href="http://blog.51cto.com/attachment/201310/135839559.png" target="_blank"></a>

不能使用索引排序的查詢

1、查詢使用了不同的排序方向，索引列都是正序定義的

<a href="http://blog.51cto.com/attachment/201310/140701505.png" target="_blank"></a>

2、排序字段不在索引列

<a href="http://blog.51cto.com/attachment/201310/140856286.png" target="_blank"></a>

3、無法組成最左字首

<a href="http://blog.51cto.com/attachment/201310/140952786.png" target="_blank"></a>

4、某個列有範圍查詢

<a href="http://blog.51cto.com/attachment/201310/141246961.png" target="_blank"></a>

DONE!!

本文轉自shayang8851CTO部落格，原文連結：http://blog.51cto.com/janephp/1311417，如需轉載請自行聯系原作者