MySQL的Explain命令

標籤：

Explain命令是查看查詢最佳化工具如何決定執行查詢的主要辦法。  調用 EXPLAIN 要使用EXPLAIN，只需在查詢中的SELECT關鍵字之前增加EXPLAIN。MySQL會在查詢上設定一個標記。當執行查詢時，這個標記會使其返回關於在執行計畫中每一步的資訊，而不是執行它（一般情況下）。它會返回一行或多行資訊，顯示出執行計畫中的每一部分和執行的次序。 explain select 1結果樣本：  在查詢中每個表在輸出中只有一行。如果查詢是兩個表的串連，那麼輸出中將有兩行。別名表單算為一個表。如果把一個表與自己來凝結，輸出中也會有兩行。"表"意義在這裡相當廣泛：可以是一個子查詢，一個UNION結果等。 EXPLAIN 有兩個主要變種： 

• EXPLAIN EXTENDED。它會通知伺服器"逆向編譯"執行計畫為一個SELECT語句。可以通過緊接其後運行SHOW WARNINGS看到這個產生的語句。這個語句直接來自執行計畫，而不是原SQL語句，到這點上已經變成一個資料結構。在大部分情境下它都與原語句不相同。（結果輸出添加了filterd列）

 

• EXPLAIN PARTITIONS。它會顯示查詢將訪問的分區，如果查詢是基於分區表的話。 （結果輸出添加了partitions列）

 認為增加EXPLAIN時，MySQL不會執行查詢，這是一個常見錯誤。如果查詢在FROM子句中包括子查詢，那麼MySQL實際上會執行子查詢，將其結果放在一個暫存資料表中，然後完成外層查詢最佳化。它必須在可以完成外層查詢最佳化之前處理所有類似的子查詢。 EXPLAIN只是一個近似結果。以下有一些相關的限制： 

• EXPLAIN不會告訴你觸發器、預存程序或UDF會如何影響查詢。
• EXPLAIN 不支援預存程序，儘管可以手動抽取查詢並單獨對其進行EXPLAIN操作。
• EXPLAIN不會告訴你MySQL在查詢執行中所做的特定最佳化。
• EXPLAIN不會顯示關於查詢的執行計畫的所有資訊。
• EXPLAIN不區分具有相同名字的事物。例如，它對記憶體排序和臨時檔案都使用"filesort"，並且對於磁碟上和記憶體中的暫存資料表都顯示"Using temporary"。

   EXPLAIN 中的列 explain的輸出的列包括：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra等。  id 列 這一列總是包含一個編號，標識select所屬的行。如果在語句當中沒有子查詢或聯合，那麼只會有唯一的select，於是每一行在這個列種都將顯示一個1。否則，內層的select語句一般會順序編號，對應於其在原始語句中的位置。 MySQL將SELECT查詢分為簡單和複雜類型，複雜類型可分為三大類：簡單子查詢、所謂的派生表（在FROM子句中的子查詢），以及UNION查詢。 注意UNION結果輸出中的額外行。UNION結果總是放在一個匿名暫存資料表中，之後MySQL將結果讀取到暫存資料表外。暫存資料表並不在原SQL中出現，因此它的id列是NULL。  select_type 列 這一列顯示了對應行是簡單還是複雜select。值如下：  

SIMPLE	該查詢不包括子查詢和UNION。
PRIMARY	複雜SELECT的最外層行。
SUBQUERY	包含在SELECT 列表中的子查詢中的select（簡單子查詢）。 SUBQUERY還可被標記為DEPENDENT，DEPENDENT SUBQUERY 意味著SELECT依賴於外層查詢中發現的資料。
DERIVED	包含在FROM子句的子查詢中的SELECT 。 MySQL會遞迴執行並將結果放到一個暫存資料表中。伺服器內部稱其"派生表"，因為該暫存資料表是從子查詢中派生出來的。
UNION	在UNION中的第二個和隨後的SELECT 。 第一個SELECT被標記就好像它以部分外查詢來執行。如果UNION被FROM子句中的子查詢包含，那麼它的第一個SELECT會被標記為DERIVED 。 UNION還可被標記為UNCACHEABLE。UNCACHEABLE UNION 意味著SELECT中的某些特性阻止結果被緩衝與一個Item_cache中。
UNION RESULT	用來從UNION的匿名暫存資料表檢索結果的SELECT。

   table 列 這一列顯示對應行正在訪問哪個表。可以在這一列中從上往下觀察MySQL的關聯最佳化器為查詢選擇的關聯順序。 查詢執行計畫於EXPLAIN中的行相對應的方式：    派生表和聯合 當FROM子句中有子查詢或有UNION時，table列會變得複雜得多。在這些情境下，確實沒有一個"表"可以參考到，因為MySQL建立的匿名暫存資料表僅在查詢執行過程中存在。 當在FROM子句中有子查詢時，table列是<derivedN>的形式，其中N是子查詢的id。這總是"向前引用"。換言之，N指向EXPLAIN輸出中後面的一行。 當有UNION時，UNION RESULT的table列包含一個參與UNION的id列表。這總是"反向參考"，因為UNION RESULT出現在UNION中所有參與行之後。  type 列  type列顯示MySQL的訪問類型，即MySQL決定如何尋找表中的行。下面是重要的存取方法，依次從最差到最優：  

ALL	全表掃描，通常意味著MySQL必須掃描整張表，從頭到尾，去找到需要的行。 這裡也有個例外，例如在查詢裡使用了LIMIT，或者在Extra列中顯示"Using distinct/not exists"。
index	這個跟全表掃描一樣，只是MySQL掃描表是按索引次序進行而不是行。它的主要優點是避免了排序；最大的缺點是要承擔按索引次序讀取整個表的開銷。這通常意味著若是按隨機次序訪問行，開銷將會非常大。 如果在Extra列種看到"Using index"，說明MySQL正在使用覆蓋索引，它只掃描索引的資料，而不是按索引次序的每一行。它比按索引次序全表掃描的開銷要少很多。
range	範圍掃描時一個有限制的索引掃描，它開始於索引裡的某一點，返回匹配這個範圍的行。 這比全索引掃描好一些，因為它用不著遍曆全部索引。顯而易見的範圍掃描是帶有BETWEEN或在WHERE子句裡帶有>的查詢。 當MySQL使用索引去尋找一系列值時，例如IN()和OR列表，也會顯示為範圍掃描。然而，這兩者其實是相當不同的訪問類型，在效能上有主要的差異。 此類掃描的開銷跟索引類型相當。
ref	這是一種索引訪問（有時也叫做索引尋找），它返回所有匹配某個單個值得行。然而，它可能會找到多個合格行，因此，它是尋找和掃描的混合體。此類索引訪問只有當使用非唯一性索引或者唯一性索引的非唯一性首碼才會發生。把它叫做ref是因為索引要跟某個參考值相比較。這個參考值或者是一個常數，或者是來自多表查詢前一個表裡的結果值。 ref_or_null是ref之上的一個變體，它意味著MySQL必須在初次尋找的結果。
eq_ref	使用這種索引尋找，MySQL知道最多隻返回一條合格記錄。 這種存取方法可以在MySQL使用主鍵或者唯一性索引尋找時看到，它會將他們與某個參考值作比較。MySQL對於這類訪問類型的最佳化坐的非常好，因為它知道無需估計匹配行的範圍或在找到匹配行後再繼續尋找。
const, system	當MySQL能對查詢的某部分進行最佳化並將其轉換成一個常量時，它就會使用這些訪問類型。 例如，如果你通過將某一行的主鍵放入where子句裡的方式來選取此行的主鍵，MySQL就能把這個查詢轉換為一個常量。然後就可以高效地將表從聯結執行中移除。
NULL	這種訪問方式意味著MySQL能在最佳化階段分解查詢語句，在執行階段甚至用不著再訪問表或者索引。 例如，從一個索引列裡選取最小值可以通過單獨尋找索引來完成，不需要執行時訪問表。

    possible_key 列  這一列顯示了查詢可以使用哪些索引，這是基於查詢訪問的列和使用的比較操作符來判斷的。這個列表是在最佳化過程的早期建立的，因此有些羅列出來的索引可能對於後續最佳化過程是沒用的。  key 列 這一列顯示了MySQL決定採用哪個索引來最佳化對該表的訪問。如果該索引沒有出現在possible_key列中，那麼MySQL選用它是處於另外的原因。例如，它可能選擇了一個覆蓋索引，哪怕沒有WHERE子句。 possible_key揭示了哪一個索引能有助於高效地行尋找，而key顯示的是最佳化採用哪一個索引可以最小化尋找成本。  key_len 列 這列顯示了MySQL在索引裡使用的位元組數。如果MySQL正在使用的只是索引裡的某些列，那麼就可以這這個值來算出具體是哪些列。 key_len 列顯示了在索引欄位中可能的最大長度，而不是表中資料使用的實際位元組數。  ref 列 這一列顯示了之前的表在key列記錄的索引中尋找值所用的列或常量。  rows 列 這一列是MySQL估計為了找到所需的行而要讀取的行數。這個資料是內嵌迴圈關聯計劃裡的迴圈數目。也就是說它不是MySQL認為它最終要從表裡讀取出來的行數，而是MySQL為了找到符合查詢的每一點上標準的那些行而必須讀取的行的平均數。（這個標準包括SQL裡給定的條件，以及來自我聯結次序上前一個表的當前列）。 這個數字是MySQL認為它要檢查的行數，而不是結果集裡的行數。  Extra 列 這一列包含的是不適合在其他列顯示的額外資訊。常見的最重要的值如下：  

Using index	此值表示MySQL將使用覆蓋索引，以避免訪問表。不要把覆蓋索引和index訪問類型混淆。
Using where	這意味著MySQL伺服器將在儲存引擎檢索行後再進行過濾。許多where條件裡涉及索引中的列，當（並且如果）它讀取索引時，就能被儲存引擎檢驗，因此不是所有帶where子句的查詢都會顯示"Using where"。有時"Using where"的出現就是一個暗示：查詢可受益於不同的索引。
Using temporary	這意味著MySQL在對查詢結果排序時會使用一個暫存資料表。
Using filesort	這意味著MySQL會對結果使用一個外部索引排序，而不是按索引次序從表裡讀取行。MySQL有兩種檔案排序演算法。兩種方式都可以在記憶體或磁碟上完成。但EXPLAIN無法知曉MySQL將使用哪一種檔案排序，也不會告訴你排序會在記憶體裡還是磁碟上完成。
Range checked for each record(index map: N)	這個值意味著沒有好用的索引，新的索引將在聯結的每一行上重新估算。N是顯示在possible_keys列種索引的位元影像，並且是冗餘的。

   filtered 列 這一列在使用EXPLAIN EXTENDED時出現。它顯示的是針對錶裡符合某個條件（where子句或聯結條件）的記錄數的百分比所做的一個悲觀估算。如果將rows列和這個百分比相乘，就能看到MySQL估算它將和查詢計劃裡前一個表關聯的行數。        

MySQL的Explain命令