前言

調(diào)用EXPLAIN可以獲取關(guān)于查詢執(zhí)行計劃的信息，以及如何解釋輸出。EXPLAIN命令是查看查詢優(yōu)化器如何決定執(zhí)行查詢的主要方法，但該動能也有局限性，它的選擇并不總是最優(yōu)的，展示的也并不一定是真相。

1 調(diào)用EXPLAIN

要使用EXPLAIN，只需要在SELECT 關(guān)鍵字之前增加 EXPLAIN這個詞。MySQL會在查詢上設(shè)置一個標(biāo)記。當(dāng)執(zhí)行查詢時，這個標(biāo)記會使其返回關(guān)于在執(zhí)行計劃中每一步的信息，而不是真正完全的執(zhí)行該語句。

它會返回一行或多行信息，顯示出執(zhí)行計劃中的每一部分和執(zhí)行的次序。在查詢中，每個表的輸出只有一行，若多表關(guān)聯(lián)，則輸出多行。別名表單算為一個表，因此如果把表和自己連接，輸出中也會有兩行。這里的表的定義非常的廣：可以是一個子查詢，一個 UNION 結(jié)果。

EXPLAIN有兩個變種：

• EXPLAIN EXTENDED：看起來和正常的EXPLAIN行為一樣，但他會告訴服務(wù)器“逆向編譯”執(zhí)行計劃為一個 SELECT 語句（SHOW WARNINGS 后能看到），該命令在MySQL5.0之后可用，MySQL5.1開始還額外增加一個 filtered 列。
• EXPLAIN PARTITIONS：如果查詢基于分區(qū)表的話，將顯示查詢將訪問的分區(qū)。MySQL5.1以及更新的版本支持。

增加了EXPLAIN之后，MySQL可能仍然會執(zhí)行部分查詢，如果查詢中FROM字句中包括子查詢，那么MySQL實際會執(zhí)行子查詢的，并將其結(jié)果放在一個臨時表中，然后完成外層查詢優(yōu)化。

EXPLAIN 返回的只是個近似結(jié)果，并且還有相關(guān)是的限制：

• 不會告訴你知道觸發(fā)器、存儲過程或 UDF 如何影響查詢。
• 不支持存儲過程，盡管可以單獨(dú)抽取查詢進(jìn)行 EXPLAIN。
• 不會告訴你查詢執(zhí)行中所做的特定優(yōu)化。
• 不會顯示關(guān)于查詢的執(zhí)行計劃的所有信息。
• 無法區(qū)分具有相同名字的事物，例如，它對內(nèi)存排序和臨時文件排序都使用“filesort”，并且對磁盤上和內(nèi)存中的臨時表都顯示“Using temporary”。
• 可能會誤導(dǎo)，例如：可能會對一個很小的limit查詢顯示全索引掃描。
• 只能解釋select查詢（5.6以后允許解釋非select語句），不會對存儲過程調(diào)用和INSERT、DELETE、UPDATE或其他語句做解釋，但可通過重寫某些非 SELECT 查詢以利用 EXPLAIN。

2 EXPLAIN中的列

2.1 id

一個編號，表示select所屬的行。如果查詢中沒有子查詢或關(guān)聯(lián)查詢，那么只會有唯一的SELECT，每一行的該列中都將顯示一個1，否則，內(nèi)層的SELECT語句一般會順序編號，對應(yīng)于其在原始語句中的位置。id越大執(zhí)行優(yōu)先級越高，id相同則認(rèn)為是一組，從上往下執(zhí)行，id為NULL最后執(zhí)行。

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

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

2.2 select_type

表示對應(yīng)行是簡單還是復(fù)雜的查詢。

• SIMPLE，簡單SELECT，查詢不包括UNION和子查詢。
• PRIMARY，查詢中若查詢包含任何復(fù)雜的子部分，最外層的select被標(biāo)記為PRIMARY。

其他部分標(biāo)記如下：

• SUBQUERY，包含在SELECT子句（不在from子句中）中的子查詢的SELECT，結(jié)果不依賴于外部查詢。
• DERIVED，包含在from子句中的子查詢中的SELECT。MySQL會遞歸執(zhí)行并將結(jié)果存放在一個臨時表中，也稱為派生表，因為該臨時表是從子查詢中派生來的。
• UNION，UNION中的第二個或后面的SELECT。第一個SELECT被標(biāo)記就好像它以部分外查詢來執(zhí)行，因此第一個SELECT可能顯示為PRIMARY。如果UNION被FROM字句中的子查詢包含，那么它的第一個SELECT被標(biāo)記為DERIVED。
• UNION RESULT，用來從UNION的匿名臨時表中檢索結(jié)果的SELECT。

除了上面這些，SUBQUERY和UNION還可以被標(biāo)記為DEPENDENT和UNCACHEABLE，DEPENDENT意味著SELECT 依賴與外層查詢中發(fā)現(xiàn)的數(shù)據(jù)；UNCACHEABLE意味著SELECT 中的某些特性阻止結(jié)果被緩存于一個 Item_cache 中。

EXPLAIN select * from contacts where contact_id =99000

2.3 table

顯示了EXPLAIN對應(yīng)行正在訪問哪個表。通常情況下，它相當(dāng)表明了：那就是那個表，或者該表的別名。

可以通過該列從上到下觀察MySQL的關(guān)聯(lián)優(yōu)化器為查詢選擇的關(guān)聯(lián)順序。

當(dāng)from字句中有子查詢的時候，table列是<derivedN>的形式，N指向子查詢id，這里N總是指向EXPLAIN輸出結(jié)果中的后面的一行。

當(dāng)有UNION時，UNION RESULT的table列包含一個參與UNION的id列表，UNION RESULT總是出現(xiàn)在UNION中所有參與行之后，例如<union 1,2>。

2.4 type

關(guān)聯(lián)類型，或者說訪問類型，該字段表明MySQL決定如何查找表中的行。

常用的訪問類型如下（性能依次從最差到最優(yōu)）：

• ALL：全表掃描，從頭到尾的查找所需要的行。但仍然存在例外，例如使用了 LIMIT ，或者Extra 列中顯示 “Using distinct/not exists”。
• index：跟全表掃描一樣，只是MySQL掃描表時按照索引次序進(jìn)行而不是行，主要優(yōu)點是避免了排序；缺點是要承擔(dān)按索引次序讀取整個表的開銷。這通常意味著如實按照隨機(jī)次序訪問行，開銷較大。如果Extra 列中顯示 “using index”，說明MySQL正在使用覆蓋索引，這樣就不需要按索引次序訪問每一行數(shù)據(jù)，開小會少很多。
• range：范圍掃描，就是一個有限制的索引掃描，使用一個索引來檢索給定范圍的行，不需要遍歷全部索引。范圍掃描通常出現(xiàn)在between,>,<,>=等操作中。in()和OR也會顯示范圍掃描，但這兩者其實是不同的訪問類型，性能上也有差異。此類查找的開銷根ref索引訪問的開銷相當(dāng)。
• ref：索引訪問，也叫索引查找。返回所有匹配某個單個值的行，然而它可能會找到符合條件的多個行。此類索引訪問只有當(dāng)使用非唯一性索引或者唯一索引的非唯一性前綴時才會發(fā)生。把他叫ref是因為他要和某個參考值相比較。這個參考值或者是一個常數(shù)，或者來自多表查詢前一個表里的結(jié)果值。
• eq_ref：使用這種索引查找，MySQL清楚的知道最多只返回一條符合條件的記錄，使用主鍵或者唯一值索引查找時能看到這種方法。MySQL對于這種訪問類型的優(yōu)化做得非常好，因為它知道到無需估計匹配行的范文或者在找到匹配行后再繼續(xù)查找（因為值不會重復(fù)）。
• const，system：當(dāng)MySQL能對查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)換成一個常量時，它就會使用這些訪問類型。比如通過將某一行的主鍵訪問WHERE字句的方式來查詢主鍵：SELECT id from t where id = 1。此時MySQL就能把這個查詢轉(zhuǎn)換為一個常量。
• NULL：這種訪問方式意味著MySQ能在優(yōu)化階段分解查詢語句，在執(zhí)行階段甚至不需要再訪問表或者索引。例如，從一個索引列里選取最小值可以通過單獨(dú)查詢索引來完成，不需要在執(zhí)行時訪問表。
• index_merge：索引合并（index merge）。MySQL5.0之前，一個表一次只能使用一個索引，無法同時使用多個索引分別進(jìn)行條件掃描。但是從5.1開始，引入了索引合并優(yōu)化技術(shù)，對同一個表可以使用多個索引分別進(jìn)行條件掃描，然后將它們各自的結(jié)果進(jìn)行合并(intersect/union)。index merge使得我們可以使用到多個索引同時進(jìn)行掃描，然后將結(jié)果進(jìn)行合并。聽起來好像是很好的功能，但是如果出現(xiàn)了 index intersect merge，那么一般同時也意味著我們的索引建立得不太合理，因為 index intersect merge 是可以通過建立復(fù)合索引進(jìn)行更一步優(yōu)化的。

2.5 possible_keys

顯示查詢可以使用哪些索引，這是基于查詢訪問的列和使用的比較操作符來判斷的。該列表是在優(yōu)化過程的早期創(chuàng)建的，因此列出來的索引對于后續(xù)實際優(yōu)化過程可能是沒有用的。

2.6 key

顯示mysql決定采用哪一個索引來優(yōu)化對該表的訪問，如果該索引沒有出現(xiàn)在possible_keys列中，那么MySQL選用它是出于另外的原因——例如，它可能選擇了一個覆蓋索引，哪怕它沒有WHERE字句。

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

如果沒有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

2.7 key_len

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

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

2.8 ref

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

2.9 rows

這一列是mysql估計要讀取并檢測的行數(shù)，注意這個不是結(jié)果集里的行數(shù)，而是MySQL為了找到符合查詢的每一個標(biāo)準(zhǔn)的那些行而必須讀取的行的平均數(shù)。

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

2.10 Extra

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

• Using index：表示MySQL將使用覆蓋索引，這發(fā)生在對表的請求列都是同一索引的部分的時候，返回的列數(shù)據(jù)只使用了索引中的信息，而沒有再去訪問表中的行記錄。是性能高的表現(xiàn)。
• Using index condition：在5.6版本后加入的新特性索引下推（Index Condition Pushdown，ICP），在索引遍歷過程中，對索引中包含的字段先做判斷（即使該字段沒有使用到索引），直接過濾掉不滿足條件的記錄，減少回表次數(shù)。
• Using where：意味著MySQL服務(wù)器將在存儲引擎檢索行后再進(jìn)行過濾。就是先通過索引讀取整行數(shù)據(jù)，再按 WHRER條件進(jìn)行檢查，符合就留下，不符合就丟棄。查詢的列未被索引覆蓋。
• Using temporary：MySQL需要創(chuàng)建一張臨時表來中間結(jié)果并進(jìn)一步處理，比如union、group by、distinct等，出現(xiàn)這種情況一般是要進(jìn)行優(yōu)化的，首先是想到用索引來優(yōu)化。
• Using filesort：MySQL會對結(jié)果使用一個外部索引排序，而不是按索引次序從表里讀取行，即filesort（文件排序）。此時mysql會根據(jù)聯(lián)接類型瀏覽所有符合條件的記錄，并保存排序關(guān)鍵字和行指針，然后排序關(guān)鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來優(yōu)化的。filesort有兩種，一種是內(nèi)存排序，一種是磁盤排序，無法得知。
• Distinct: 一旦MySQL找到了與行相聯(lián)合匹配的行，就不再搜索了，常見于關(guān)聯(lián)查詢。
• No tables used：Query語句中使用from dual 或不含任何from子句。
• Using join buffer：使用了連接緩存，join語句用到了緩沖區(qū)。

到此這篇關(guān)于MySQL EXPLAIN執(zhí)行計劃解析的文章就介紹到這了,更多相關(guān)MySQL EXPLAIN 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論