Mysql Explain詳解

　　一語法

　　explain < table_name >

　　例如: explain select * from t where id=;

　　二explain輸出解釋

　　+++++++++++

　　| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |

　　+++++++++++

　　id

　　我的理解是SQL執行的順利的標識SQL從大到小的執行

　　例如:

　　mysql> explain select * from (select * from ( select * from t where id=) a) b;

　　+++++++++++

　　| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |

　　+++++++++++

　　|  | PRIMARY     | <derived> | system | NULL              | NULL    | NULL    | NULL |    |       |

　　|  | DERIVED     | <derived> | system | NULL              | NULL    | NULL    | NULL |    |       |

　　|  | DERIVED     | t         | const  | PRIMARYidx_t_id | PRIMARY |        |      |    |       |

　　+++++++++++

　　很顯然這條SQL是從裡向外的執行就是從id= 向上執行

　　 select_type

　　就是select類型可以有以下幾種

　　() SIMPLE

　　簡單SELECT(不使用UNION或子查詢等) 例如:

　　mysql> explain select * from t where id=;

　　+++++++++++

　　| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |

　　+++++++++++

　　|  | SIMPLE      | t    | const | PRIMARYidx_t_id | PRIMARY |        | const |    |       |

　　+++++++++++

　　() PRIMARY

　　我的理解是最外層的select例如:

　　mysql> explain select * from (select * from t where id=) a ;

　　+++++++++++

　　| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |

　　+++++++++++

　　|  | PRIMARY     | <derived> | system | NULL              | NULL    | NULL    | NULL |    |       |

　　|  | DERIVED     | t         | const  | PRIMARYidx_t_id | PRIMARY |        |      |    |       |

　　+++++++++++

　　()UNION

　　UNION中的第二個或後面的SELECT語句例如

　　mysql> explain select * from t where id= union all select * from t ;

　　+++++++++++

　　| id | select_type  | table      | type  | possible_keys     | key     | key_len | ref   | rows | Extra |

　　+++++++++++

　　|  | PRIMARY      | t         | const | PRIMARYidx_t_id | PRIMARY |        | const |    |       |

　　|  | UNION        | t         | ALL   | NULL              | NULL    | NULL    | NULL  | |       |

　　|NULL | UNION RESULT | <union> | ALL   | NULL              | NULL    | NULL    | NULL  | NULL |       |

　　+++++++++++

　　()DEPENDENT UNION

　　UNION中的第二個或後面的SELECT語句取決於外面的查詢

　　mysql> explain select * from t where id in (select id from t where id= union all select id from t)  ;

　　+++++++++++

　　| id | select_type        | table      | type   | possible_keys     | key     | key_len | ref   | rows | Extra                    |

　　+++++++++++

　　|  | PRIMARY            | t         | ALL    | NULL              | NULL    | NULL    | NULL  | | Using where              |

　　|  | DEPENDENT SUBQUERY | t         | const  | PRIMARYidx_t_id | PRIMARY |        | const |    | Using index              |

　　|  | DEPENDENT UNION    | t         | eq_ref | PRIMARYidx_t_id | PRIMARY |        | func  |    | Using where; Using index |

　　|NULL | UNION RESULT       | <union> | ALL    | NULL              | NULL    | NULL    | NULL  | NULL |                          |

　　+++++++++++

　　()UNION RESULT

　　UNION的結果

　　mysql> explain select * from t where id= union all select * from t ;

　　+++++++++++

　　| id | select_type  | table      | type  | possible_keys     | key     | key_len | ref   | rows | Extra |

　　+++++++++++

　　|  | PRIMARY      | t         | const | PRIMARYidx_t_id | PRIMARY |        | const |    |       |

　　|  | UNION        | t         | ALL   | NULL              | NULL    | NULL    | NULL  | |       |

　　|NULL | UNION RESULT | <union> | ALL   | NULL              | NULL    | NULL    | NULL  | NULL |       |

　　+++++++++++

　　()SUBQUERY

　　子查詢中的第一個SELECT

　　mysql> explain select * from t where id = (select id from t where id= )  ;

　　+++++++++++

　　| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra       |

　　+++++++++++

　　|  | PRIMARY     | t    | const | PRIMARYidx_t_id | PRIMARY |        | const |    |             |

　　|  | SUBQUERY    | t    | const | PRIMARYidx_t_id | PRIMARY |        |       |    | Using index |

　　+++++++++++

　　()  DEPENDENT SUBQUERY

　　子查詢中的第一個SELECT取決於外面的查詢

　　mysql> explain select id from t where id in (select id from t where id= )  ;

　　+++++++++++

　　| id | select_type        | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra                    |

　　+++++++++++

　　|  | PRIMARY            | t    | index | NULL              | PRIMARY |        | NULL  | | Using where; Using index |

　　|  | DEPENDENT SUBQUERY | t    | const | PRIMARYidx_t_id | PRIMARY |        | const |    | Using index              |

　　+++++++++++

　　()DERIVED

　　派生表的SELECT(FROM子句的子查詢)

　　mysql> explain select * from (select * from t where id=) a ;

　　+++++++++++

　　| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |

　　+++++++++++

　　|  | PRIMARY     | <derived> | system | NULL              | NULL    | NULL    | NULL |    |       |

　　|  | DERIVED     | t         | const  | PRIMARYidx_t_id | PRIMARY |        |      |    |       |

　　+++++++++++

　　table

　　顯示這一行的數據是關於哪張表的

　　有時不是真實的表名字看到的是derivedx(x是個數字我的理解是第幾步執行的結果)

　　mysql> explain select * from (select * from ( select * from t where id=) a) b;

　　+++++++++++

　　| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |

　　+++++++++++

　　|  | PRIMARY     | <derived> | system | NULL              | NULL    | NULL    | NULL |    |       |

　　|  | DERIVED     | <derived> | system | NULL              | NULL    | NULL    | NULL |    |       |

　　|  | DERIVED     | t         | const  | PRIMARYidx_t_id | PRIMARY |        |      |    |       |

　　+++++++++++

　　type

　　這列很重要顯示了連接使用了哪種類別有無使用索引

　　從最好到最差的連接類型為consteq_regrefrangeindexhe和ALL

　　()system

　　這是const聯接類型的一個特例表僅有一行滿足條件如下(t表上的id是 primary key)

　　mysql> explain select * from (select * from t where id=) a ;

　　+++++++++++

　　| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |

　　+++++++++++

　　|  | PRIMARY     | <derived> | system | NULL              | NULL    | NULL    | NULL |    |       |

　　|  | DERIVED     | t         | const  | PRIMARYidx_t_id | PRIMARY |        |      |    |       |

　　+++++++++++

　　(nst

　　表最多有一個匹配行它將在查詢開始時被讀取因為僅有一行在這行的列值可被優化器剩余部分認為是常數const表很快因為它們只讀取一次！

　　const用於用常數值比較PRIMARY KEY或UNIQUE索引的所有部分時在下面的查詢中tbl_name可以用於const表

　　SELECT * from tbl_name WHERE primary_key=

　　SELECT * from tbl_name WHERE primary_key_part=和 primary_key_part=

　　例如:

　　mysql> explain select * from t where id=;

　　+++++++++++

　　| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |

　　+++++++++++

　　|  | SIMPLE      | t    | const | PRIMARYidx_t_id | PRIMARY |        | const |    |       |

　　+++++++++++

　　() eq_ref

　　對於每個來自於前面的表的行組合從該表中讀取一行這可能是最好的聯接類型除了const類型它用在一個索引的所有部分被聯接使用並且索引是UNIQUE或PRIMARY KEY

　　eq_ref可以用於使用= 操作符比較的帶索引的列比較值可以為常量或一個使用在該表前面所讀取的表的列的表達式

　　在下面的例子中MySQL可以使用eq_ref聯接來處理ref_tables

　　SELECT * FROM ref_tableother_table

　　WHERE ref_tablekey_column=lumn;

　　SELECT * FROM ref_tableother_table

　　WHERE ref_tablekey_column_part=lumn

　　AND ref_tablekey_column_part=;

　　例如

　　mysql> create unique index  idx_t_id on t(id) ;

　　Query OK rows affected ( sec)

　　Records:   Duplicates:   Warnings:

　　mysql> explain select * from tt where tid=taccountid;

　　+++++++++++

　　| id | select_type | table | type   | possible_keys     | key       | key_len | ref                  | rows | Extra |

　　+++++++++++

　　|  | SIMPLE      | t    | ALL    | NULL              | NULL      | NULL    | NULL                 | |       |

　　|  | SIMPLE      | t    | eq_ref | PRIMARYidx_t_id | idx_t_id |        | dbatesttaccountid |    |       |

　　+++++++++++

　　()ref

　　對於每個來自於前面的表的行組合所有有匹配索引值的行將從這張表中讀取如果聯接只使用鍵的最左邊的前綴或如果鍵不是UNIQUE或PRIMARY KEY（換句話說如果聯接不能基於關鍵字選擇單個行的話）則使用ref如果使用的鍵僅僅匹配少量行該聯接類型是不錯的

　　ref可以用於使用=或<=>操作符的帶索引的列

　　在下面的例子中MySQL可以使用ref聯接來處理ref_tables

　　SELECT * FROM ref_table WHERE key_column=expr;

　　SELECT * FROM ref_tableother_table

　　WHERE ref_tablekey_column=lumn;

　　SELECT * FROM ref_tableother_table

　　WHERE ref_tablekey_column_part=lumn

　　AND ref_tablekey_column_part=;

　　例如:

　　mysql> drop index idx_t_id on t;

　　Query OK rows affected ( sec)

　　Records:   Duplicates:   Warnings:

　　mysql> create index idx_t_id on t(id) ;

　　Query OK rows affected ( sec)

　　Records:   Duplicates:   Warnings:

　　mysql> explain select * from tt where tid=taccountid;

　　+++++++++++

　　| id | select_type | table | type | possible_keys     | key       | key_len | ref                  | rows | Extra |

　　+++++++++++

　　|  | SIMPLE      | t    | ALL  | NULL              | NULL      | NULL    | NULL                 | |       |

　　|  | SIMPLE      | t    | ref  | PRIMARYidx_t_id | idx_t_id |        | dbatesttaccountid |    |       |

　　+++++++++++

　　 rows in set ( sec)

　　()  ref_or_null

　　該聯接類型如同ref但是添加了MySQL可以專門搜索包含NULL值的行在解決子查詢中經常使用該聯接類型的優化

　　在下面的例子中MySQL可以使用ref_or_null聯接來處理ref_tables

　　SELECT * FROM ref_table

　　WHERE key_column=expr OR key_column IS NULL;

　　() index_merge

　　該聯接類型表示使用了索引合並優化方法在這種情況下key列包含了使用的索引的清單key_len包含了使用的索引的最長的關鍵元素

　　例如:

　　mysql> explain select * from t where id= or accountid= ;

　　+++++++++++

　　| id | select_type | table | type        | possible_keys              | key                        | key_len | ref  | rows | Extra                                                |

　　+++++++++++

　　|  | SIMPLE      | t    | index_merge | idx_t_ididx_t_accountid | idx_t_ididx_t_accountid |      | NULL |    | Using union(idx_t_ididx_t_accountid); Using where |

　　+++++++++++

　　 row in set ( sec)

　　() unique_subquery

　　該類型替換了下面形式的IN子查詢的ref

　　value IN (SELECT primary_key FROM single_table WHERE some_expr)

　　unique_subquery是一個索引查找函數可以完全替換子查詢效率更高

　　()index_subquery

　　該聯接類型類似於unique_subquery可以替換IN子查詢但只適合下列形式的子查詢中的非唯一索引

　　value IN (SELECT key_column FROM single_table WHERE some_expr)

　　()range

　　只檢索給定范圍的行使用一個索引來選擇行key列顯示使用了哪個索引key_len包含所使用索引的最長關鍵元素在該類型中ref列為NULL

　　當使用=<>>>=<<=IS NULL<=>BETWEEN或者IN操作符用常量比較關鍵字列時可以使用range

　　mysql> explain select * from t where id= or id= ;

　　+++++++++++

　　| id | select_type | table | type  | possible_keys     | key       | key_len | ref  | rows | Extra       |

　　+++++++++++

　　|  | SIMPLE      | t    | range | PRIMARYidx_t_id | idx_t_id |        | NULL |    | Using where |

　　+++++++++++

　　 row in set ( sec)

　　()index

　　該聯接類型與ALL相同除了只有索引樹被掃描這通常比ALL快因為索引文件通常比數據文件小

　　當查詢只使用作為單索引一部分的列時MySQL可以使用該聯接類型

　　() ALL

　　對於每個來自於先前的表的行組合進行完整的表掃描如果表是第一個沒標記const的表這通常不好並且通常在它情況下很差通常可以增加更多的索引而不要使用ALL使得行能基於前面的表中的常數值或列值被檢索出

　　possible_keys

　　possible_keys列指出MySQL能使用哪個索引在該表中找到行注意該列完全獨立於EXPLAIN輸出所示的表的次序這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用

　　如果該列是NULL則沒有相關的索引在這種情況下可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能如果是這樣創造一個適當的索引並且再次用EXPLAIN檢查查詢

　　 key

　　key列顯示MySQL實際決定使用的鍵（索引）如果沒有選擇索引鍵是NULL要想強制MySQL使用或忽視possible_keys列中的索引在查詢中使用FORCE INDEXUSE INDEX或者IGNORE INDEX

　　key_len

　　key_len列顯示MySQL決定使用的鍵長度如果鍵是NULL則長度為NULL

　　使用的索引的長度在不損失精確性的情況下長度越短越好

　　 ref

　　ref列顯示使用哪個列或常數與key一起從表中選擇行

　　 rows

　　rows列顯示MySQL認為它執行查詢時必須檢查的行數

　　 Extra

　　該列包含MySQL解決查詢的詳細信息下面詳細

　　()Distinct

　　一旦MYSQL找到了與行相聯合匹配的行就不再搜索了

　　()Not exists

　　MYSQL優化了LEFT JOIN一旦它找到了匹配LEFT JOIN標准的行

　　就不再搜索了

　　()Range checked for each

　　Record（index map:#）

　　沒有找到理想的索引因此對於從前面表中來的每一個行組合MYSQL檢查使用哪個索引並用它來從表中返回行這是使用索引的最慢的連接之一

　　()Using filesort

　　看到這個的時候查詢就需要優化了MYSQL需要進行額外的步驟來發現如何對返回的行排序它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行

　　()Using index

　　列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的這發生在對表的全部的請求列都是同一個索引的部分的時候

　　()Using temporary

　　看到這個的時候查詢需要優化了這裡MYSQL需要創建一個臨時表來存儲結果這通常發生在對不同的列集進行ORDER BY上而不是GROUP BY上

　　()Using where

　　使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶如果不想返回表中的全部行並且連接類型ALL或index這就會發生或者是查詢有問題

From:http://tw.wingwit.com/Article/program/MySQL/201311/29305.html