MySQL 優化之EXPLAIN詳解(執行計劃)

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

如果由於不恰當使用索引而引起一些問題的話，可以運行 analyze table來更新該表的統計信息，例如鍵的基數，它能幫您在優化方面做出更好的選擇。

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

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

explain結果的每行記錄顯示了每個表的相關信息，每行記錄都包含以下幾個字段：

• id ：本次 select 的標識符。在查詢中每個 select都有一個順序的數值。
• select_type ：select 的類型，可能會有以下幾種：

table：記錄查詢引用的表。
type：表連接類型。以下列出了各種不同類型的表連接，依次是從最好的到最差的：
system:表只有一行記錄（等於系統表）。這是 const表連接類型的一個特例。
const:表中最多隻有一行匹配的記錄，它在查詢一開始的時候就會被讀取出來。
\t\t\t\t\t由於只有一行記錄，在餘下的優化程序裡該行記錄的字段值可以被當作是一個 恆定值。const表查詢起來非常快，因為只要讀取一次！const 用於在和 primary key 或unique 索引中有固定值比較的情形。下面的幾個查詢中，tbl_name 就是 c表了：
select * from tbl_name where primary_key=1; select * from tbl_namewhere primary_key_part1=1 and primary_key_part2=2;
eq_ref:\t從該表中會有一行記錄被讀取出來以和從前一個表中讀取出來的記錄做聯合。 

\t\t\t\t\t\t與const類型不同的是，這是最好的連接類型。它用在索引所有部 分都用於做連接並且這個索引是一個primary key 或 unique 類型。eq_ref可以用於在進行"="做比較時檢索字段。比較的值可以是固定值或者是表達式，表達示中可以使用表裡的字段，它們在讀表之前已經準備好 了。以下的幾個例子中，mysql使用了eq_ref 連接來處理 ref_table：select * from ref_table,other_table whereref_table.key_column=other_table.column; select * fromref_table,other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_part2=1;
ref: 該表中所有符合檢索值的記錄都會被取出來和從上一個表中取出來的記錄作聯合。
\t\t\t\t\t\tref用於連接程序使用鍵的最左前綴或者是該鍵不是 primary key 或 unique索引（換句話說，就是連接程序無法根據鍵值只取得一條記錄）的情況。當根據鍵值只查詢到少數幾條匹配的記錄時，這就是一個不錯的連接類型。 ref還可以用於檢索字段使用 =操作符來比較的時候。以下的幾個例子中，mysql將使用 ref 來處理ref_table：
select * from ref_table where key_column=expr; select * fromref_table,other_table whereref_table.key_column=other_table.column; select * fromref_table,other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_part2=1;
ref_or_null: 這種連接類型類似 ref，不同的是mysql會在檢索的時候額外的搜索包含null 值的記錄。
\t\t\t\t\t\t\t\t這種連接類型的優化是從mysql4.1.1開始的，它經常用於子查詢。在以下的例子中，mysql使用ref_or_null 類型來處理 ref_table：select * from ref_table where key_column=expr or key_column is null;
unique_subquery: 這種類型用例如一下形式的 in 子查詢來替換
 ref：value in (select primary_key from single_table where some_expr)
unique_subquery: 只是用來完全替換子查詢的索引查找函數效率更高了。
index_subquery: 這種連接類型類似 unique_subquery。 

\t\t\t\t\t\t\t\t\t\t\t\t\t\t它用子查詢來代替in，不過它用於在子查詢中沒有唯一索引的情況下，例如以下形式：
\t\t\t\t\t\t\t\t\t\t\t\t\t\tvalue in (select key_column from single_table where some_expr)
range: 只有在給定範圍的記錄才會被取出來，利用索引來取得一條記錄。
\t\t\t\t\tkey字段表示使用了哪個索引。key_len字段包括了使用的鍵的最長部分。這種類型時 ref 字段值是 null。range用於將某個字段和一個定植用以下任何操作符比較時 =, <>, >,>=, , between, 或 in：
\t\t\t\t\tselect * from tbl_name where key_column = 10; select * fromtbl_name where key_column between 10 and 20; select * from tbl_namewhere key_column in (10,20,30); select * from tbl_name wherekey_part1= 10 and key_part2 in (10,20,30);
index: 連接類型跟 all 一樣，不同的是它只掃描索引樹。
\t\t\t\t\t它通常會比 all快點，因為索引文件通常比數據文件小。mysql在查詢的字段知識單獨的索引的一部分的情況下使用這種連接類型。
all: 將對該表做全部掃描以和從前一個表中取得的記錄作聯合。
\t\t\t這時候如果第一個表沒有被標識為const的話就不大好了，在其他情況下通常是非常糟糕的。
 正常地，可以通過增加索引使得能從表中更快的取得記錄以避免all。
possible_keys：possible_keys字段是指 mysql在搜索表記錄時可能使用哪個索引。
注意，這個字段完全獨立於explain 顯示的表順序。這就意味著 possible_keys裡面所包含的索引可能在實際的使用中沒用到。 

如果這個字段的值是null，就表示沒有索引被用到。這種情況下，就可以檢查 where子句中哪些字段那些字段適合增加索引以提高查詢的性能。
就這樣，創建一下索引，然後再用explain 檢查一下。詳細的查看章節"14.2.2 alter tablesyntax"。
想看錶都有什麼索引，可以通過 show index from tbl_name來看。 
key：key字段顯示了mysql實際上要用的索引。
當沒有任何索引被用到的時候，這個字段的值就是null。想要讓mysql強行使用或者忽略在 possible_keys字段中的索引列表，
可以在查詢語句中使用關鍵字force index, use index,或 ignore index。如果是 myisam 和 bdb 類型表，
可以使用 analyzetable 來幫助分析使用使用哪個索引更好。如果是 myisam類型表，
運行命令 myisamchk --analyze也是一樣的效果。
詳細的可以查看章節"14.5.2.1 analyze tablesyntax"和"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.id where t2.id isnull;
假使 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.9how mysql optimizes order by"。kllklksdddddusing index:字段的信息直接從索引樹中的信息取得，而不再去掃描實際的記錄。這種策略用於查詢時的字段是一個獨立索引的一部分。
using temporary: mysql需要創建臨時表存儲結果以完成查詢。這種情況通常發生在查詢時包含了groupby 和 order by 子句，它以不同的方式列出了各個字段。
using where:where子句將用來限制哪些記錄匹配了下一個表或者發送給客戶端。
除非你特別地想要取得或者檢查表種的所有記錄，否則的話當查詢的extra 字段值不是 using where 並且表連接類型是 all 或 index時可能表示有問題。
如果你想要讓查詢儘可能的快，那麼就應該注意 extra 字段的值為usingfilesort 和 using temporary 的情況。
你可以通過 explain 的結果中 rows字段的值的乘積大概地知道本次連接表現如何。
它可以粗略地告訴我們mysql在查詢過程中會查詢多少條記錄。如果是使用系統變量 max_join_size 來取得查詢結果，這個乘積還可以用來確定會執行哪些多表select 語句。 
下面的例子展示瞭如何通過 explain提供的信息來較大程度地優化多表聯合查詢的性能。
假設有下面的 select 語句，正打算用 explain 來檢測：
EXPLAIN
SELECT
tt.ticketnumber,
tt.timein,
tt.projectreference,
tt.estimatedshipdate,
tt.actualshipdate,
tt.clientid,tt.servicecodes,
tt.repetitiveid,
tt.currentprocess,
tt.currentdppers 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 columntype 
tt actualpc char(10) 
tt assignedpc char(10) 
tt clientid char(10) 
et employid char(15) 
do custnmbr char(15) 
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就可以在這些字段上更快的是用索引（對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 extra 
tt all assignedpc, null null null 3872 using clientid, where actualpc 
do all primary null null null 2135 range checked for each record (keymap: 1) 
et_1 all primary null null null 74 range checked for eachrecord (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分句中用不同的次序列出各個表。
 
 

                

            

            
            
            
            
            
            
            
            
            
            
            

            

            

            

                
分享到:
                

            
            


            
            
 閱讀更多  IT源程序 
                的文章
            
            
            
            
關鍵字:
                
                MySQL
                
                執行
                
                詳解