EXPLAIN 文法（得到SELECT 的相關資訊）

7.2.1 EXPLAIN 文法（得到 SELECT 的相關資訊）

 原文地址 http://imysql.cn/?q=node/35

EXPLAIN tbl_name

或者:

EXPLAIN SELECT select_options

EXPLAIN 語句可以被當作 DESCRIBE 的同義字來用，也可以用來擷取一個MySQL要執行的 SELECT 語句的相關資訊。

• EXPLAIN tbl_name 文法和 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一樣。
• 當在一個 SELECT 語句前使用關鍵字 EXPLAIN 時，MYSQL會解釋了即將如何運行該 SELECT 語句，它顯示了表如何串連、串連的順序等資訊。

 

本章節主要講述了第二種 EXPLAIN 用法。

在 EXPLAIN 的協助下，您就知道什麼時候該給表添加索引，以使用索引來尋找記錄從而讓 SELECT 運行更快。

如果由於不恰當使用索引而引起一些問題的話，可以運行 ANALYZE TABLE 來更新該表的統計資訊，例如鍵的基數，它能幫您在最佳化方面做出更好的選擇。詳情請看"14.5.2.1 ANALYZE TABLE Syntax"。

您還可以查看最佳化程式是否以最佳的順序來串連資料表。為了讓最佳化程式按照 SELECT 語句中的表名的順序做串連，可以在查詢的開始使用 SELECT STRAIGHT_JOIN 而不只是 SELECT。

EXPLAIN 返回了一行記錄，它包括了 SELECT 語句中用到的各個表的資訊。這些表在結果中按照MySQL即將執行的查詢中讀取的順序列出來。MySQL用一次掃描多次串連（single-sweep, multi-join） 的方法來解決串連。這意味著MySQL從第一個表中讀取一條記錄，然後在第二個表中尋找到對應的記錄，然後在第三個表中尋找，依次類推。當所有的表都掃描完了，它輸出選擇的欄位並且回溯所有的表，直到找不到為止，因為有的表中可能有多條匹配的記錄下一條記錄將從該表讀取，再從下一個表開始繼續處理。

在MySQL version 4.1中，EXPLAIN 輸出的結果格式改變了，使得它更適合例如 UNION 語句、子查詢以及派生表的結構。更令人注意的是，它新增了2個欄位： id 和 select_type。當你使用早於MySQL 4.1的版本就看不到這些欄位了。

EXPLAIN 結果的每行記錄顯示了每個表的相關資訊，每行記錄都包含以下幾個欄位：

id  本次 SELECT 的標識符。在查詢中每個 SELECT 都有一個順序的數值。

select_type  SELECT 的類型，可能會有以下幾種：  

SIMPLE - 簡單的 SELECT （沒有使用 UNION 或子查詢

PRIMARY - 最外層的 SELECT

UNION - 第二層，在 SELECT 之後使用了 UNIO

DEPENDENT UNION - UNION 語句中的第二個 SELECT，依賴於外部子查

SUBQUERY - 子查詢中的第一個 SELEC

DEPENDENT SUBQUERY - 子查詢中的第一個 SUBQUERY 依賴於外部的子查

DERIVED - 派生表 SELECT（ FROM 子句中的子查詢）

 

table   記錄查詢引用的表。 

 

type   表連線類型。以下列出了各種不同類型的表串連，依次是從最好的到最差的：
 

system

表只有一行記錄（等於系統資料表）。這是 const 表連線類型的一個特例。

 

 

const

表中最多隻有一行匹配的記錄，它在查詢一開始的時候就會被讀取出來。由於只有一行記錄，在餘下的最佳化程式裡該行記錄的欄位值可以被當作是一個恒定值。 const 表查詢起來非常快，因為只要讀取一次！ const 用於在和 PRIMARY KEY 或 UNIQUE 索引中有固定值比較的情形。下面的幾個查詢中，tbl_name 就是 const 表了：

 

SELECT * FROM tbl_name WHERE primary_key=1;SELECT * FROM tbl_nameWHERE primary_key_part1=1 AND primary_key_part2=2;

 

 

eq_ref

從該表中會有一行記錄被讀取出來以和從前一個表中讀取出來的記錄做聯合。與 const 類型不同的是，這是最好的連線類型。它用在索引所有部分都用於做串連並且這個索引是一個 PRIMARY KEY 或 UNIQUE 類型。 eq_ref 可以用於在進行"="做比較時檢索欄位。比較的值可以是固定值或者是運算式，運算式中可以使用表裡的欄位，它們在讀表之前已經準備好了。以下的幾個例子中，MySQL使用了 eq_ref 串連來處理 ref_table：

 

SELECT * FROM ref_table,other_tableWHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_tableWHERE ref_table.key_column_part1=other_table.columnAND ref_table.key_column_part2=1;

 

 

ref

該表中所有符合檢索值的記錄都會被取出來和從上一個表中取出來的記錄作聯合。 ref 用於串連程式使用鍵的最左首碼或者是該鍵不是 PRIMARY KEY 或 UNIQUE 索引（換句話說，就是串連程式無法根據索引值只取得一條記錄）的情況。當根據索引值只查詢到少數幾條匹配的記錄時，這就是一個不錯的連線類型。 ref 還可以用於檢索欄位使用 = 操作符來比較的時候。以下的幾個例子中，MySQL將使用 ref 來處理 ref_table：

 

SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_tableWHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_tableWHERE ref_table.key_column_part1=other_table.columnAND ref_table.key_column_part2=1;

 

ref_or_null

這種連線類型類似 ref，不同的是MySQL會在檢索的時候額外的搜尋包含 NULL 值的記錄。這種連線類型的最佳化是從MySQL 4.1.1開始的，它經常用於子查詢。在以下的例子中，MySQL使用 ref_or_null 類型來處理 ref_table：

 

SELECT * FROM ref_tableWHERE key_column=expr OR key_column IS NULL;

詳情請看"7.2.6 How MySQL Optimizes IS NULL"。

 

index_merge

這種連線類型意味著使用了 Index Merge 最佳化方法。這種情況下， key欄位包括了所有使用的索引， key_len 包括了使用的鍵的最長部分。詳情請看"7.2.5 How MySQL Optimizes OR Clauses"。

 

 

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。 range 用於將某個欄位和一個定植用以下任何操作符比較時 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, 或 IN：

 

SELECT * FROM tbl_nameWHERE key_column = 10;SELECT * FROM tbl_nameWHERE key_column BETWEEN 10 and 20;SELECT * FROM tbl_nameWHERE key_column IN (10,20,30);SELECT * FROM tbl_nameWHERE key_part1= 10 AND key_part2 IN (10,20,30);

 

 

index

連線類型跟 ALL 一樣，不同的是它只掃描索引樹。它通常會比 ALL 快點，因為索引檔案通常比資料檔案小。MySQL在查詢的欄位知識單獨的索引的一部分的情況下使用這種連線類型。

 

 

ALL

將對該表做全部掃描以和從前一個表中取得的記錄作聯合。這時候如果第一個表沒有被標識為 const 的話就不大好了，在其他情況下通常是非常糟糕的。正常地，可以通過增加索引使得能從表中更快的取得記錄以避免 ALL。

 

 

possible_keys

possible_keys 欄位是指MySQL在搜尋表記錄時可能使用哪個索引。注意，這個欄位完全獨立於 EXPLAIN 顯示的表順序。這就意味著 possible_keys 裡面所包含的索引可能在實際的使用中沒用到。如果這個欄位的值是 NULL，就表示沒有索引被用到。這種情況下，就可以檢查 WHERE 子句中哪些欄位那些欄位適合增加索引以提高查詢的效能。就這樣，建立一下索引，然後再用 EXPLAIN 檢查一下。詳細的查看章節"14.2.2 ALTER TABLE Syntax"。想看錶都有什麼索引，可以通過 SHOW INDEX FROM tbl_name 來看。

 

 

 

key

key 欄位顯示了MySQL實際上要用的索引。當沒有任何索引被用到的時候，這個欄位的值就是 NULL。想要讓MySQL強行使用或者忽略在 possible_keys 欄位中的索引列表，可以在查詢語句中使用關鍵字 FORCE INDEX, USE INDEX, 或 IGNORE INDEX。如果是 MyISAM 和 BDB 類型表，可以使用 ANALYZE TABLE 來協助分析使用使用哪個索引更好。如果是 MyISAM 類型表，運行命令 myisamchk --analyze 也是一樣的效果。詳細的可以查看章節"14.5.2.1 ANALYZE TABLE Syntax"和"5.7.2 Table Maintenance and Crash Recovery"。

 

 

key_len

key_len 欄位顯示了MySQL使用索引的長度。當 key 欄位的值為 NULL 時，索引的長度就是 NULL。注意， key_len 的值可以告訴你在聯合索引中MySQL會真正使用了哪些索引。

 

 

ref

ref 欄位顯示了哪些欄位或者常量被用來和 key 配合從表中查詢記錄出來。

 

 

rows

rows 欄位顯示了MySQL認為在查詢中應該檢索的記錄數。

 

 

Extra

本欄位顯示了查詢中MySQL的附加資訊。以下是這個欄位的幾個不同值的解釋：

 

 

Distinct

MySQL當找到目前記錄的匹配聯合結果的第一條記錄之後，就不再搜尋其他記錄了。

 

 

Not exists

MySQL在查詢時做一個 LEFT JOIN 最佳化時，當它在當前表中找到了和前一條記錄符合 LEFT JOIN 條件後，就不再搜尋更多的記錄了。下面是一個這種類型的查詢例子：

 

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.idWHERE t2.id IS NULL;

假使 t2.id 定義為 NOT NULL。這種情況下，MySQL將會掃描表 t1 並且用 t1.id 的值在 t2 中尋找記錄。當在 t2 中找到一條匹配的記錄時，這就意味著 t2.id 肯定不會都是 NULL，就不會再在 t2 中尋找相同 id 值的其他記錄了。也可以這麼說，對於 t1 中的每個記錄，MySQL只需要在 t2 中做一次尋找，而不管在 t2 中實際有多少匹配的記錄。

 

range checked for each record (index map: #)

MySQL沒找到合適的可用的索引。取代的辦法是，對於前一個表的每一個行串連，它會做一個檢驗以決定該使用哪個索引（如果有的話），並且使用這個索引來從表裡取得記錄。這個過程不會很快，但總比沒有任何索引時做表串連來得快。

 

 

Using filesort

MySQL需要額外的做一遍從而以排好的順序取得記錄。排序程式根據串連的類型遍曆所有的記錄，並且將所有符合 WHERE 條件的記錄的要排序的鍵和指向記錄的指標儲存起來。這些鍵已經排完序了，對應的記錄也會按照排好的順序取出來。詳情請看"7.2.9 How MySQL Optimizes ORDER BY"。

 

 

Using index

欄位的資訊直接從索引樹中的資訊取得，而不再去掃描實際的記錄。這種策略用於查詢時的欄位是一個獨立索引的一部分。

 

 

Using temporary

MySQL需要建立暫存資料表儲存結果以完成查詢。這種情況通常發生在查詢時包含了 GROUP BY 和 ORDER BY 子句，它以不同的方式列出了各個欄位。

 

 

Using where

WHERE 子句將用來限制哪些記錄匹配了下一個表或者發送給用戶端。除非你特別地想要取得或者檢查表種的所有記錄，否則的話當查詢的 Extra 欄位值不是 Using where 並且表連線類型是 ALL 或 index 時可能表示有問題。

如果你想要讓查詢儘可能的快，那麼就應該注意 Extra 欄位的值為Using filesort 和 Using temporary 的情況。

 

你可以通過 EXPLAIN 的結果中 rows 欄位的值的乘積大概地知道本次串連表現如何。它可以粗略地告訴我們MySQL在查詢過程中會查詢多少條記錄。如果是使用系統變數 max_join_size 來取得查詢結果，這個乘積還可以用來確定會執行哪些多表 SELECT 語句。詳情請看"7.5.2 Tuning Server Parameters"。

下面的例子展示了如何通過 EXPLAIN 提供的資訊來較大程度地最佳化多表聯集查詢的效能。

假設有下面的 SELECT 語句，正打算用 EXPLAIN 來檢測：

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,            tt.ProjectReference, tt.EstimatedShipDate,            tt.ActualShipDate, tt.ClientID,            tt.ServiceCodes, tt.RepetitiveID,            tt.CurrentProcess, tt.CurrentDPPerson,            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,            et_1.COUNTRY, do.CUSTNAME        FROM tt, et, et AS et_1, do        WHERE tt.SubmitTime IS NULL            AND tt.ActualPC = et.EMPLOYID            AND tt.AssignedPC = et_1.EMPLOYID            AND tt.ClientID = do.CUSTNMBR;

在這個例子中，先做以下假設：

• 要比較的欄位定義如下：
  

  Table	Column	Column Type
  tt	ActualPC	CHAR(10)
  tt	AssignedPC	CHAR(10)
  tt	ClientID	CHAR(10)
  et	EMPLOYID	CHAR(15)
  do	CUSTNMBR	CHAR(15)

• 資料表的索引如下：
  

  Table	Index
  tt	ActualPC
  tt	AssignedPC
  tt	ClientID
  et	EMPLOYID (primary key)
  do	CUSTNMBR (primary key)

• tt.ActualPC 的值是不均勻分布的。

在任何最佳化措施未採取之前，經過 EXPLAIN 分析的結果顯示如下：

 

table type possible_keys key  key_len ref  rows  Extraet    ALL  PRIMARY       NULL NULL    NULL 74do    ALL  PRIMARY       NULL NULL    NULL 2135et_1  ALL  PRIMARY       NULL NULL    NULL 74tt    ALL  AssignedPC,   NULL NULL    NULL 3872           ClientID,           ActualPC      range checked for each record (key map: 35)

由於欄位 type 的對於每個表值都是 ALL，這個結果意味著MySQL對所有的表做一個迪卡爾積；這就是說，每條記錄的組合。這將需要花很長的時間，因為需要掃描每個表總記錄數乘積的總和。在這情況下，它的積是 74 * 2135 * 74 * 3872 = 45,268,558,720 條記錄。如果資料表更大的話，你可以想象一下需要多長的時間。

在這裡有個問題是當欄位定義一樣的時候，MySQL就可以在這些欄位上更快的是用索引（對 ISAM 類型的表來說，除非欄位定義完全一樣，否則不會使用索引）。在這個前提下，VARCHAR 和 CHAR是一樣的除非它們定義的長度不一致。由於 tt.ActualPC 定義為 CHAR(10)，et.EMPLOYID 定義為 CHAR(15)，二者長度不一致。
為瞭解決這個問題，需要用 ALTER TABLE 來加大 ActualPC 的長度從10到15個字元：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

現在 tt.ActualPC 和 et.EMPLOYID 都是 VARCHAR(15)
了。再來執行一次 EXPLAIN 語句看看結果：

table type   possible_keys key     key_len ref         rows    Extratt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using             ClientID,                                         where             ActualPCdo    ALL    PRIMARY       NULL    NULL    NULL        2135      range checked for each record (key map: 1)et_1  ALL    PRIMARY       NULL    NULL    NULL        74      range checked for each record (key map: 1)et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

這還不夠，它還可以做的更好：現在 rows 值乘積已經少了74倍。這次查詢需要用2秒鐘。
第二個改變是消除在比較 tt.AssignedPC = et_1.EMPLOYID 和 tt.ClientID = do.CUSTNMBR 中欄位的長度不一致問題：

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),    ->                MODIFY ClientID   VARCHAR(15);

現在 EXPLAIN 的結果如下：

table type   possible_keys key      key_len ref           rows Extraet    ALL    PRIMARY       NULL     NULL    NULL          74tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using             ClientID,                                         where             ActualPCet_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

這看起來已經是能做的最好的結果了。
遺留下來的問題是，MySQL預設地認為欄位tt.ActualPC 的值是均勻分布的，然而表 tt 並非如此。幸好，我們可以很方便的讓MySQL分析索引的分布：

mysql> ANALYZE TABLE tt;

到此為止，表串連已經最佳化的很完美了，EXPLAIN 的結果如下：

table type   possible_keys key     key_len ref           rows Extratt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using             ClientID,                                        where             ActualPCet    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

請注意，EXPLAIN 結果中的 rows 欄位的值也是MySQL的串連最佳化程式大致猜測的，請檢查這個值跟真實值是否基本一致。如果不是，可以通過在 SELECT 語句中使用 STRAIGHT_JOIN 來取得更好的效能，同時可以試著在 FROM
分句中用不同的次序列出各個表。