mysql explain詳解

這個文章寫的不錯: www.joehorn.idv.tw/archives/2009/11/20/705/


善用 EXPLAIN SELECT 分析 SQL statement，以下列舉狀態解讀：

EQ_REF	一對一比對
REF	一對多比對
RANGE	特定範圍的資料會被傳回
INDEX	使用 INDEX 裡的資料
ALL	對整個 TABLE 作掃瞄（最差）

借助於EXPLAIN，你可以知道
1)你什麼時候必須為表加入索引以得到一個使用索引找到記錄的更快的SELECT。
2)你也能知道優化器是否以一個最佳次序聯結表。為了強制優化器對一個SELECT語句使用一個特定聯結次序，增加一個STRAIGHT_JOIN子句。
官方的關於explain的文檔在http://dev.mysql.com/doc/refman/5.1/en/using-explain.html（英文），本文可作為官方文獻的不完整通俗讀物。

使用的方法

EXPLAIN tbl_name
或：
EXPLAIN [EXTENDED] SELECT select_options
前者可以得出一個表的字段結構等等，後者主要是給出相關的一些索引信息，而今天要講述的重點是後者。

舉例

mysql> explain select * from event;
+—-+————-+——-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | |
+—-+————-+——-+——+—————+——+———+——+——+——-+
1 row in set (0.00 sec)

各個屬性的含義

id  select查詢的序列號
select_type
select查詢的類型，主要是區別普通查詢和聯合查詢、子查詢之類的複雜查詢。
table
輸出的行所引用的表。
type
聯合查詢所使用的類型。
type顯示的是訪問類型，是較為重要的一個指標，結果值從好到壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般來說，得保證查詢至少達到range級別，最好能達到ref。
possible_keys
指出MySQL能使用哪個索引在該表中找到行。如果是空的，沒有相關的索引。這時要提高性能，可通過檢驗WHERE子句，看是否引用某些字段，或者檢查字段不是適合索引。
key
顯示MySQL實際決定使用的鍵。如果沒有索引被選擇，鍵是NULL。
key_len
顯示MySQL決定使用的鍵長度。如果鍵是NULL，長度就是NULL。文檔提示特別注意這個值可以得出一個多重主鍵裡mysql實際使用了哪一部分。
ref
顯示哪個字段或常數與key一起被使用。
rows
這個數表示mysql要遍歷多少數據才能找到，在innodb上是不準確的。
Extra
如果是Only index，這意味著信息只用索引樹中的信息檢索出的，這比掃瞄整個表要快。
如果是where used，就是使用上了where限制。
如果是impossible where 表示用不著where，一般就是沒查出來啥。
如果此信息顯示Using filesort或者Using temporary的話會很吃力，WHERE和ORDER BY的索引經常無法兼顧，如果按照WHERE來確定索引，那麼在ORDER BY時，就必然會引起Using filesort，這就要看是先過濾再排序划算，還是先排序再過濾划算。

常見的一些名詞解釋

Using filesort
MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。
Using index
從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息。
Using temporary
為瞭解決查詢，MySQL需要創建一個臨時表來容納結果。
ref
對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取
ALL
完全沒有索引的情況，性能非常地差勁。
index
與ALL相同，除了只有索引樹被掃瞄。這通常比ALL快，因為索引文件通常比數據文件小。
SIMPLE
簡單SELECT(不使用UNION或子查詢)