Mysql高性能索引的政策

Mysql的explain指令：

explain指令顯示了mysql如何使用索引來執行select查詢語句，可以幫助寫出更好的索引和更優化的查詢語句。看示例如下：

結果如下：

explain列的解釋：

table：顯示這一行的資料是關于哪張表的

type：這是重要的列，顯示連接配接使用了何種類型。從最好到最差的連接配接類型為const、eq_reg、ref、range、index和all。顯示index說明使用索引掃描來做排序。

possible_keys：顯示可能應用在這張表中的索引。

key： 實際使用的索引。如果為null，則沒有使用索引。很少的情況下，mysql會選擇優化不足的索引。這種情況下，可以在select語句中使用use index（indexname）來強制使用一個索引或者用ignore index（indexname）來強制mysql忽略索引。

key_len：使用的索引的長度。在不損失精确性的情況下，長度越短越好。

ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數。

rows：mysql認為必須檢查的用來傳回請求資料的行數

extra：出現using index說明使用覆寫索引。

關于explain更加詳細的資訊，可參考部落格 http://www.cnblogs.com/linjiqin/p/4125898.html

覆寫索引:

對于聚集索引（使用B-Tree），它的主鍵索引的data域中存儲的是完整的行資訊，它的二級索引的data域中存儲的是主鍵。是以，當使用二級索引時，可能先在二級索引中找到目标行的主鍵，然後通過主鍵到主鍵索引中去查詢完整的行資訊，這就需要一次回表操作。

設想一下，如果二級索引是一個多列索引，這個多列索引中包含了一次查詢所需要的全部字段，那麼就不再需要回表操作了。我們把這種索引叫“覆寫索引”。

如下結構的一張表：

它的索引情況如下：其中有一個主鍵索引（id）和一個多列索引（username, password）;

下面是建立表的語句：

對于這張表，來看下面的查詢：

1.

結果：

2.

結果：

查詢1和查詢2兩者完全不同，查詢1使用覆寫查詢，覆寫查詢使用到的索引是index_username_password（username, password）。一定有讀者問，查詢1需要的列id和username也沒有被索引index_username_password覆寫啊，為何卻顯示覆寫索引？

其中的原因在于聚集索引的二級索引的機制，大家别忘了聚集索引的二級索引的data域中存儲的是主鍵值。是以對于像InnoDB這種聚集索引來說， 它的二級索引index_username_password（username,password）實際上等同于四個索引（username,password），（username,password,id），(username)，(username,id)。這一點大家要清楚。對于多列索引，大家也要注意，多列索引的順序很重要，因為列的順序決定了它的字首索引可以是啥！而字首索引經常要用到。繼續》》》

3.

結果：

4.

結果：

查詢3和查詢4想必大家可以了解了，是以index_username_password（username,password）可以覆寫username,password,id三列，但不會覆寫enable這一列。是以查詢4不是覆寫索引。

5.

結果：

6.

結果：

查詢5是非覆寫索引，因為select *中包含了enable這一個字段。可以使用内連接配接進行優化，優化後子查詢将使用覆寫索引，雖然沒有全部使用覆寫索引，但比完全無法利用索引覆寫要好。

使用索引掃描來做排序：

Mysql有兩種方式來生成排序的結果：一種是通過檔案排序操作（file sort），另一種是按照索引順序掃描。如果explain出來的type列的值為“index”或者extra列沒有出現“using filesort”，那麼說明了mysql使用索引掃描來做排序。通過索引掃描的方式進行排序是非常快的，這就需要設計一個索引，它既能滿足排序又能滿足查找。仍然以上面的資料庫test_table為例。

1.

結果：

2.

結果：

3.

結果：

4.

結果：

上面的四種查詢均使用了索引掃描進行排序，這裡where子句中的列用A表示，order by子句中的列用B表示， 隻要A和B中列的總和是“索引的全部列或者索引的最左字首”并且“位于左側的索引列不能有範圍查詢”，那麼就是索引掃描。實在無法确定的話，使用explain如果出現using filesort的話就是檔案排序而非索引掃描排序。

下面是一些非索引掃描排序：

上圖中的查詢中，第一個查詢不存在可以将其覆寫的索引；第二個查詢排序方向不同，無法使用索引；第三個查詢位于左側的列出現範圍查詢；第四個查詢位于左側的列有範圍查詢。

再一次強調一下，使用explain，如果extra列出現using index說明使用覆寫索引；如果extra列出現using filesort說明使用索引排序。

字首索引：

介紹索引之前，先介紹下索引選擇性。索引選擇性=（不重複的索引值/總記錄數）。索引選擇性的值越接近1，說明該索引作為查詢條件的過濾效果越好。仍然以test_table為例，來說明如何使用字首索引。

在test_table表中，username的字段值太長，建立索引的時候占用的空間太大，而且長度越長索引需要的對比時間就越長，是以我們希望使用username字段的一部分字首作為索引。但是盡管使用字段字首，我們仍然希望它的索引選擇性不要太低。為此，先有如下查詢語句：

結果如下：

可見，當使用username字段的前8位時其索引選擇性就和完整列的索引選擇性基本一緻了，繼續增加長度幫助不大。

是以建立如下字首索引：

在建立該字首索引之前，先删除原來的複合索引index_username_pass（username,password），因為該索引實際上等同于（username,password）和（username）兩個索引。

在删除原來的索引後執行如下語句：

這條語句的時間時1.903s(在我的資料庫中，大約有200萬條記錄)。然後建立如上的字首索引，繼續執行上面的語句時間縮短到0.01毫秒以下。說明該字首索引效果顯著。雖然字首索引更小更高效，但是它卻無法用來做order by和group by，并且也無法用來做覆寫掃描。

• 

• 大小: 5.4 KB

• 

• 大小: 7.1 KB

• 

• 大小: 9.9 KB

• 

• 大小: 7.9 KB

• 

• 大小: 3.1 KB

• 

• 大小: 4.8 KB

• 

• 大小: 3.4 KB

• 

• 大小: 4.6 KB

• 

• 大小: 3.5 KB

• 

• 大小: 4.8 KB

• 

• 大小: 3.9 KB

• 

• 大小: 5.1 KB

• 

• 大小: 3.9 KB

• 

• 大小: 4.6 KB

• 

• 大小: 6.9 KB

• 

• 大小: 5.2 KB

• 

• 大小: 4.3 KB

• 

• 大小: 4.7 KB

• 

• 大小: 4.7 KB

• 

• 大小: 4.7 KB

• 

• 大小: 4.8 KB

• 

• 大小: 4.9 KB

• 

• 大小: 4.7 KB

• 

• 大小: 4.8 KB

• 

• 大小: 19.7 KB

• 

• 大小: 9.2 KB

• 

• 大小: 4.8 KB

• 

• 大小: 2.2 KB

• 

• 大小: 1.9 KB

• 

• 大小: 1.9 KB

• 

• 大小: 4.8 KB

• 

• 大小: 4.3 KB

• 

• 大小: 4.7 KB

• 

• 大小: 4.7 KB

• 

• 大小: 4.8 KB

• 

• 大小: 4.9 KB

• 

• 大小: 4.7 KB

• 

• 大小: 4.8 KB

• 

• 大小: 4.7 KB

• 

• 大小: 3.4 KB

• 

• 大小: 9.4 KB

• 檢視圖檔附件