mysql explain用法（轉載）

EXPLAIN文法（擷取SELECT相關資訊）

EXPLAIN 
tbl_name


      

或：

EXPLAIN [EXTENDED] SELECT 
select_options


      

EXPLAIN 語句可以用作DESCRIBE 的一個同義詞，或獲得關于MySQL 如何執行SELECT 語句的資訊：

·         EXPLAIN tbl_name 是DESCRIBE tbl_name 或SHOW COLUMNS FROM tbl_name 的一個同義詞。

·         如果在SELECT 語句前放上關鍵詞EXPLAIN ，MySQL 将解釋它如何處理SELECT ，提供有關表如何聯接和聯接的次序。

該節解釋EXPLAIN 的第2 個用法。

借助于EXPLAIN ，可以知道什麼時候必須為表加入索引以得到一個使用索引來尋找記錄的更快的SELECT 。

如果由于使用不正确的索引出現了問題，應運作ANALYZE TABLE 更新表的統計（例如關鍵字集的勢），這樣會影響優化器進行的選擇。參見13.5.2.1節，“ANALYZE TABLE文法” 。

還可以知道優化器是否以一個最佳次序聯接表。為了強制優化器讓一個SELECT 語句按照表命名順序的聯接次序，語句應以STRAIGHT_JOIN 而不隻是SELECT 開頭。

EXPLAIN 為用于SELECT 語句中的每個表傳回一行資訊。表以它們在處理查詢過程中将被MySQL 讀入的順序被列出。MySQL 用一遍掃描多次聯接（single-sweep multi-join ）的方式解決所有聯接。這意味着MySQL 從第一個表中讀一行，然後找到在第二個表中的一個比對行，然後在第3 個表中等等。當所有的表處理完後，它輸出選中的列并且傳回表清單直到找到一個有更多的比對行的表。從該表讀入下一行并繼續處理下一個表。

當使用EXTENDED 關鍵字時，EXPLAIN 産生附加資訊，可以用SHOW WARNINGS 浏覽。該資訊顯示優化器限定SELECT 語句中的表和列名，重寫并且執行優化規則後SELECT 語句是什麼樣子，并且還可能包括優化過程的其它注解。

EXPLAIN 的每個輸出行提供一個表的相關資訊，并且每個行包括下面的列：

·         id

SELECT 識别符。這是SELECT 的查詢序列号。

·         select_type

SELECT 類型，可以為以下任何一種：

o        SIMPLE

簡單SELECT ( 不使用UNION 或子查詢)

o        PRIMARY

最外面的SELECT

o        UNION

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

o        DEPENDENT UNION

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

o        UNION RESULT

UNION 的結果。

o        SUBQUERY

子查詢中的第一個SELECT

o        DEPENDENT SUBQUERY

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

o        DERIVED

導出表的SELECT ( FROM 子句的子查詢)

·         table

輸出的行所引用的表。

·         type

聯接類型。下面給出各種聯接類型，按照從最佳類型到最壞類型進行排序：

o        system

表僅有一行(= 系統表) 。這是const 聯接類型的一個特例。

o        const

表最多有一個比對行，它将在查詢開始時被讀取。因為僅有一行，在這行的列值可被優化器剩餘部分認為是常數。const 表很快，因為它們隻讀取一次！

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

SELECT * from 
tbl_name


 WHERE 
primary_key


=1
；      

SELECT * from 
tbl_name


      

WHERE 
primary_key_part1


=1
和 primary_key_part2


=2
；      

o        eq_ref

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

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

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

SELECT * FROM 
ref_table


,
other_table


      

  WHERE 
ref_table


.
key_column


=
other_table


.
column


;
      

SELECT * FROM 
ref_table


,
other_table


      

  WHERE 
ref_table


.
key_column_part1


=
other_table


.
column


      

    AND 
ref_table


.
key_column_part2


=1;
      

o        ref

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

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

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

SELECT * FROM 
ref_table


 WHERE 
key_column


=
expr


;
      

SELECT * FROM 
ref_table


,
other_table


      

  WHERE 
ref_table


.
key_column


=
other_table


.
column


;
      

SELECT * FROM 
ref_table


,
other_table


      

  WHERE 
ref_table


.
key_column_part1


=
other_table


.
column


      

    AND 
ref_table


.
key_column_part2


=1;
      

o        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;
      

參見7.2.7節，“MySQL如何優化IS NULL” 。

o        index_merge

該聯接類型表示使用了索引合并優化方法。在這種情況下，key 列包含了使用的索引的清單，key_len 包含了使用的索引的最長的關鍵元素。詳細資訊參見7.2.6節，“索引合并優化” 。

o        unique_subquery

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

value


 IN (SELECT 
primary_key


 FROM 
single_table


 WHERE 
some_expr


)
      

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

o        index_subquery

該聯接類型類似于unique_subquery 。可以替換IN 子查詢，但隻适合下列形式的子查詢中的非唯一索引：

value


 IN (SELECT 
key_column


 FROM 
single_table


 WHERE 
some_expr


)
      

o        range

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

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

SELECT * FROM 
tbl_name


      

WHERE 
key_column


 = 10;
      

SELECT * FROM 
tbl_name


      

WHERE 
key_column


 BETWEEN 10 and 20;
      

SELECT * FROM 
tbl_name


      

WHERE 
key_column


 IN (10,20,30);
      

SELECT * FROM 
tbl_name


      

WHERE 
key_part1


= 10 AND 
key_part2 IN (10,20,30);


      

o        index

該聯接類型與ALL 相同，除了隻有索引樹被掃描。這通常比ALL 快，因為索引檔案通常比資料檔案小。

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

o        ALL

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

·         possible_keys

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

如果該列是NULL ，則沒有相關的索引。在這種情況下，可以通過檢查WHERE 子句看是否它引用某些列或适合索引的列來提高你的查詢性能。如果是這樣，創造一個适當的索引并且再次用EXPLAIN 檢查查詢。參見13.1.2節，“ALTER TABLE文法” 。

為了看清一張表有什麼索引，使用SHOW INDEX FROM tbl_name 。

·         key

key 列顯示MySQL 實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL 。要想強制MySQL 使用或忽視possible_keys 列中的索引，在查詢中使用FORCE INDEX 、USE INDEX 或者IGNORE INDEX 。參見13.2.7節，“SELECT文法” 。

對于MyISAM 和BDB 表，運作ANALYZE TABLE 可以幫助優化器選擇更好的索引。對于MyISAM 表，可以使用myisamchk --analyze 。參見13.5.2.1節，“ANALYZE TABLE文法” 和5.9.4節，“表維護和崩潰恢複” 。

·         key_len

key_len 列顯示MySQL 決定使用的鍵長度。如果鍵是NULL ，則長度為NULL 。注意通過key_len 值我們可以确定MySQL 将實際使用一個多部關鍵字的幾個部分。

·         ref

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

·         rows

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

·         Extra

該列包含MySQL 解決查詢的詳細資訊。下面解釋了該列可以顯示的不同的文本字元串：

o        Distinct

MySQL 發現第1 個比對行後，停止為目前的行組合搜尋更多的行。

o        Not exists

MySQL 能夠對查詢進行LEFT JOIN 優化，發現1 個比對LEFT JOIN 标準的行後，不再為前面的的行組合在該表内檢查更多的行。

下面是一個可以這樣優化的查詢類型的例子：

SELECT * 
從t1 LEFT JOIN t2 ON t1.id=t2.id
      

  WHERE t2.id IS NULL
；      

假定t2.id 定義為NOT NULL 。在這種情況下，MySQL 使用t1.id 的值掃描t1 并查找t2 中的行。如果MySQL 在t2 中發現一個比對的行，它知道t2.id 絕不會為NULL ，并且不再掃描t2 内有相同的id 值的行。換句話說，對于t1 的每個行，MySQL 隻需要在t2 中查找一次，無論t2 内實際有多少比對的行。

o        range checked for each record (index map: #)

MySQL 沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知，可能部分索引可以使用。對前面的表的每個行組合，MySQL 檢查是否可以使用range 或index_merge 通路方法來索取行。關于适用性标準的描述參見7.2.5節，“範圍優化” 和7.2.6節，“索引合并優化” ，不同的是前面表的所有列值已知并且認為是常量。

這并不很快，但比執行沒有索引的聯接要快得多。

o        Using filesort

MySQL 需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據聯接類型浏覽所有行并為所有比對WHERE 子句的行儲存排序關鍵字和行的指針來完成排序。然後關鍵字被排序，并按排序順序檢索行。參見7.2.12節，“MySQL如何優化ORDER BY” 。

o        Using index

從隻使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。當查詢隻使用作為單一索引一部分的列時，可以使用該政策。

o        Using temporary

為了解決查詢，MySQL 需要建立一個臨時表來容納結果。典型情況如查詢包含可以按不同情況列出列的GROUP BY 和ORDER BY 子句時。

o        Using where

WHERE 子句用于限制哪一個行比對下一個表或發送到客戶。除非你專門從表中索取或檢查所有行，如果Extra 值不為Using where 并且表聯接類型為ALL 或index ，查詢可能會有一些錯誤。

如果想要使查詢盡可能快，應找出Using filesort 和Using temporary 的Extra 值。

o        Using sort_union(...) , Using union(...) , Using intersect(...)

這些函數說明如何為index_merge 聯接類型合并索引掃描。詳細資訊參見7.2.6節，“索引合并優化” 。

o        Using index for group-by

類似于通路表的Using index 方式，Using index for group-by 表示MySQL 發現了一個索引，可以用來查詢GROUP BY 或DISTINCT 查詢的所有列，而不要額外搜尋硬碟通路實際的表。并且，按最有效的方式使用索引，以便對于每個組，隻讀取少量索引條目。詳情參見7.2.13節，“MySQL如何優化GROUP BY” 。

通過相乘EXPLAIN 輸出的rows 列的所有值，你能得到一個關于一個聯接如何的提示。這應該粗略地告訴你MySQL 必須檢查多少行以執行查詢。當你使用max_join_size 變量限制查詢時，也用這個乘積來确定執行哪個多表SELECT 語句。參見7.5.2節，“調節伺服器參數” 。

下列例子顯示出一個多表JOIN 如何能使用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;
      

對于這個例子，假定：

·         被比較的列聲明如下：

表	列	列類型
tt	ActualPC	CHAR(10)
tt	AssignedPC	CHAR(10)
tt	ClientID	CHAR(10)
et	EMPLOYID	CHAR(15)
do	CUSTNMBR	CHAR(15)

·         表有下面的索引：

表	索引
tt	ActualPC
tt	AssignedPC
tt	ClientID
et	EMPLOYID ( 主鍵)
do	CUSTNMBR ( 主鍵)

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

開始，在進行優化前，EXPLAIN 語句産生下列資訊：

table type possible_keys key  key_len ref  rows  Extra
      

et    ALL  PRIMARY       NULL NULL    NULL 74
      

do    ALL  PRIMARY       NULL NULL    NULL 2135
      

et_1  ALL  PRIMARY       NULL NULL    NULL 74
      

tt    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 能更高效地在聲明具有相同類型和尺寸的列上使用索引。在本文中，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    Extra
      

tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
      

             ClientID,                                         where
      

             ActualPC
      

do    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 Extra
      

et    ALL    PRIMARY       NULL     NULL    NULL          74
      

tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
      

             ClientID,                                         where
      

             ActualPC
      

et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
      

do    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 Extra
      

tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
      

             ClientID,                                        where
      

             ActualPC
      

et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
      

et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
      

do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1
      

注意在從EXPLAIN 輸出的rows 列是一個來自MySQL 聯接優化器的“教育猜測”。你應該檢查數字是否接近事實。如果不是，可以通過在SELECT 語句裡面使用STRAIGHT_JOIN 并且試着在FROM 子句以不同的次序列出表，可能得到更好的性能。