Mysql(21)—EXPLAIN執行計劃的字段詳解

詳細介紹了MySQL EXPLAIN執行計劃的各個字段的含義以及使用方式。

調用EXPLAIN可以擷取關于查詢執行計劃的資訊，以及如何解釋輸出。EXPLAIN指令是檢視查詢優化器如何決定執行查詢的主要方法，但該動能也有局限性，它的選擇并不總是最優的，展示的也并不一定是真相。

文章目錄

• 1 調用EXPLAIN
• 2 EXPLAIN中的列
• • 2.1 id
  • 2.2 select_type
  • 2.3 table
  • 2.4 type
  • 2.5 possible_keys
  • 2.6 key
  • 2.7 key_len
  • 2.8 ref
  • 2.9 rows
  • 2.10 Extra

1 調用EXPLAIN

要使用EXPLAIN，隻需要在SELECT 關鍵字之前增加 EXPLAIN這個詞。MySQL會在查詢上設定一個标記。當執行查詢時，這個标記會使其傳回關于在執行計劃中每一步的資訊，而不是真正完全的執行該語句。

它會傳回一行或多行資訊，顯示出執行計劃中的每一部分和執行的次序。在查詢中，每個表的輸出隻有一行，若多表關聯，則輸出多行。别名表單算為一個表，是以如果把表和自己連接配接，輸出中也會有兩行。這裡的表的定義非常的廣：可以是一個子查詢，一個 UNION 結果。

EXPLAIN有兩個變種：

1. EXPLAIN EXTENDED

   ：看起來和正常的EXPLAIN行為一樣，但他會告訴伺服器“逆向編譯”執行計劃為一個 SELECT 語句（SHOW WARNINGS 後能看到），該指令在MySQL5.0之後可用，MySQL5.1開始還額外增加一個 filtered 列。

2. EXPLAIN PARTITIONS

   ：如果查詢基于分區表的話，将顯示查詢将通路的分區。MySQL5.1以及更新的版本支援。

增加了EXPLAIN之後，MySQL可能仍然會執行部分查詢，如果查詢中FROM字句中包括子查詢，那麼MySQL實際會執行子查詢的，并将其結果放在一個臨時表中，然後完成外層查詢優化。

EXPLAIN 傳回的隻是個近似結果，并且還有相關是的限制：

1. 不會告訴你知道觸發器、存儲過程或 UDF 如何影響查詢。
2. 不支援存儲過程，盡管可以單獨抽取查詢進行 EXPLAIN。
3. 不會告訴你查詢執行中所做的特定優化。
4. 不會顯示關于查詢的執行計劃的所有資訊。
5. 無法區分具有相同名字的事物，例如，它對記憶體排序和臨時檔案排序都使用“filesort”，并且對磁盤上和記憶體中的臨時表都顯示“Using temporary”。
6. 可能會誤導，例如：可能會對一個很小的limit查詢顯示全索引掃描。
7. 隻能解釋select查詢（5.6以後允許解釋非select語句），不會對存儲過程調用和INSERT、DELETE、UPDATE或其他語句做解釋，但可通過重寫某些非 SELECT 查詢以利用 EXPLAIN。

2 EXPLAIN中的列

2.1 id

一個編号，表示select所屬的行。如果查詢中沒有子查詢或關聯查詢，那麼隻會有唯一的SELECT，每一行的該列中都将顯示一個1，否則，内層的SELECT語句一般會順序編号，對應于其在原始語句中的位置。id越大執行優先級越高，id相同則認為是一組，從上往下執行，id為NULL最後執行。

例如UNION查詢中最後對于臨時表的查詢，它的id就為null，因為臨時表并不在原sql中出現。

EXPLAIN select * from contacts where contact_id <1000
UNION 
select * from contacts where contact_id >99000
           

2.2 select_type

表示對應行是簡單還是複雜的查詢。

1. SIMPLE

   ，簡單SELECT，查詢不包括UNION和子查詢。

2. PRIMARY

   ，查詢中若查詢包含任何複雜的子部分，最外層的select被标記為PRIMARY。其他部分标記如下：

   1. SUBQUERY

      ，包含在SELECT子句（不在from子句中）中的子查詢的SELECT，結果不依賴于外部查詢。

   2. DERIVED

      ，包含在from子句中的子查詢中的SELECT。MySQL會遞歸執行并将結果存放在一個臨時表中，也稱為派生表，因為該臨時表是從子查詢中派生來的。

   3. UNION

      ，UNION中的第二個或後面的SELECT。第一個SELECT被标記就好像它以部分外查詢來執行，是以第一個SELECT可能顯示為PRIMARY。如果UNION被FROM字句中的子查詢包含，那麼它的第一個SELECT被标記為DERIVED。

   4. UNION RESULT

      ，用來從UNION的匿名臨時表中檢索結果的SELECT。

除了上面這些，

SUBQUERY和UNION

還可以被标記為

DEPENDENT和UNCACHEABLE

，DEPENDENT意味着SELECT 依賴與外層查詢中發現的資料；UNCACHEABLE意味着SELECT 中的某些特性阻止結果被緩存于一個 Item_cache 中。

2.3 table

顯示了EXPLAIN對應行正在通路哪個表。通常情況下，它相當表明了：那就是那個表，或者該表的别名。

可以通過該列從上到下觀察MySQL的關聯優化器為查詢選擇的關聯順序。

當

from

字句中有子查詢的時候，table列是

<derivedN>

的形式，N指向子查詢id，這裡N總是指向EXPLAIN輸出結果中的後面的一行。

當有

UNION

時，UNION RESULT的table列包含一個參與UNION的id清單，UNION RESULT總是出現在UNION中所有參與行之後，例如

<union 1,2>

。

2.4 type

關聯類型，或者說通路類型，該字段表明MySQL決定如果查找表中的行。

常用的通路類型如下（性能依次從最差到最優）：

1. ALL

   ：全表掃描，從頭到尾的查找所需要的行。但仍然存在例外，例如使用了

   LIMIT

   ，或者

   Extra

   列中顯示 “

   Using distinct/not exists

   ”。

2. index

   ：跟全表掃描一樣，隻是MySQL掃描表時按照索引次序進行而不是行，主要優點是避免了排序；缺點是要承擔按索引次序讀取整個表的開銷。這通常意味着如實按照随機次序通路行，開銷較大。如果Extra 列中顯示 “using index”，說明MySQL正在使用覆寫索引，這樣就不需要按索引次序通路每一行資料，開小會少很多。

3. range

   ：範圍掃描，就是一個有限制的索引掃描，使用一個索引來檢索給定範圍的行，不需要周遊全部索引。範圍掃描通常出現在

   between,>,<,>=

   等操作中。

   in()和OR

   也會顯示範圍掃描，但這兩者其實是不同的通路類型，性能上也有差異。此類查找的開銷根

   ref

   索引通路的開銷相當。

4. ref

   ：索引通路，也叫索引查找。傳回所有比對某個單個值的行，然而它可能會找到符合條件的多個行。此類索引通路隻有當使用非唯一性索引或者唯一索引的非唯一性字首時才會發生。把他叫ref是因為他要和某個參考值相比較。這個參考值胡總和是一個常數，或者來自多表查詢前一個表裡的結果值。

5. eq_ref

   ：使用這種索引查找，MySQL清楚的知道最多隻傳回一條符合條件的記錄，使用主鍵或者唯一值索引查找時能看到這種方法。MySQL對于這種通路類型的優化做得非常好，因為它知道到無需估計比對行的範文或者在找到比對行後再繼續查找（因為值不會重複）。

6. const，system

   ：當MySQL能對查詢的某部分進行優化并将其轉換成一個常量時，它就會使用這些通路類型。比如通過将某一行的主鍵通路WHERE字句的方式來查詢主鍵：SELECT id from t where id = 1。此時MySQL就能把這個查詢轉換為一個常量。

7. NULL

   ：這種通路方式意味着MySQ能在優化階段分解查詢語句，在執行階段甚至不需要再通路表或者索引。例如，從一個索引列裡選取最小值可以通過單獨查詢索引來完成，不需要在執行時通路表。

2.5 possible_keys

顯示查詢可以使用哪些索引，這是基于查詢通路的列和使用的比較操作符來判斷的。該清單是在優化過程的早期建立的，是以列出來的索引對于後續實際優化過程可能是沒有用的。

2.6 key

顯示mysql決定采用哪一個索引來優化對該表的通路，如果該索引沒有出現在possible_keys列中，那麼MySQL選用它是出于另外的原因——例如，它可能選擇了一個覆寫索引，哪怕它沒有WHERE字句。

possible_keys表示哪些索引有助于高效查找，而key表示該索引可以最小化查詢成本。

如果沒有選擇索引，鍵是

NULL

。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用

FORCE INDEX、USE INDEX或者IGNORE INDEX

。

2.7 key_len

MySQL在索引中使用的位元組數，通過這個值可以算出具體使用了索引中的哪些列，計算時需要考慮字元集，如果字段允許為 NULL，需要1位元組記錄是否為 NULL。

key_len

顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表内檢索出的。

2.8 ref

這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，即哪些列或常量被用于查找索引列上的值。常見的有：const（常量），func，NULL，字段名（例：film.id）

2.9 rows

這一列是mysql估計要讀取并檢測的行數，注意這個不是結果集裡的行數，而是MySQL為了找到符合查詢的每一個标準的那些行而必須讀取的行的平均數。

有時候該估值可能很不精确，該數字也反映不了LIMIT字句的真正檢查行數。

2.10 Extra

這一列展示的是額外資訊。常見的重要值如下：

1. Using index

   ：表示MySQL将使用覆寫索引，這發生在對表的請求列都是同一索引的部分的時候，傳回的列資料隻使用了索引中的資訊，而沒有再去通路表中的行記錄。是性能高的表現。

2. Using where

   ：意味着MySQL伺服器将在存儲引擎檢索行後再進行過濾。就是先通過索引讀取整行資料，再按 WHRER條件進行檢查，符合就留下，不符合就丢棄。

3. Using temporary

   ：MySQL需要建立一張臨時表來處理查詢，比如union、group by等，出現這種情況一般是要進行優化的，首先是想到用索引來優化。

4. Using filesort

   ：MySQL會對結果使用一個外部索引排序，而不是按索引次序從表裡讀取行，即filesort（檔案排序）。此時mysql會根據聯接類型浏覽所有符合條件的記錄，并儲存排序關鍵字和行指針，然後排序關鍵字并按順序檢索行資訊。這種情況下一般也是要考慮使用索引來優化的。filesort有兩種，一種是記憶體排序，一種是磁盤排序，無法得知。

5. Distinct

   : 一旦MySQL找到了與行相聯合比對的行，就不再搜尋了，常見于關聯查詢。

6. No tables used

   ：Query語句中使用from dual 或不含任何from子句。

參考資料：

1. 《 MySQL 技術内幕: InnoDB 存儲引擎》
2. 《高性能 MySQL》
3. 《MySQL實戰45講 | 極客時間 | 丁奇》

如有需要交流，或者文章有誤，請直接留言。另外希望點贊、收藏、關注，我将不間斷更新各種Java學習部落格！