前言

explain命令是查看查詢優(yōu)化器如何決定執(zhí)行查詢的主要方法。

這個功能有局限性，并不總會說出真相，但它的輸出是可以獲取的最好信息，值得花時間去了解，因為可以學習到查詢是如何執(zhí)行的。

調(diào)用EXPLAIN

在select之前添加explain，mysql會在查詢上設置一個標記，當執(zhí)行查詢計劃時，這個標記會使其返回關(guān)于執(zhí)行計劃中每一步的信息，而不是執(zhí)行它。

它會返回一行或多行信息，顯示出執(zhí)行計劃中的每一部分和執(zhí)行次序。

這是一個簡單的explain效果：

在查詢中每個表在輸出只有一行，如果查詢是兩個表的聯(lián)接，那么輸出中將有兩行。

別名表單算為一個表，因此，如果把一個表與自己聯(lián)接，輸出中也會有兩行。

“表”的意義在這里相當廣，可以是一個子查詢，一個union結(jié)果等。

同時 explain有兩個變種

EXPLAIN EXTENDED會告訴服務器“逆向編譯” 執(zhí)行計劃為一個select語句。

可以通過緊接其后運行show warnings看到這個生成的語句，這個語句直接來自執(zhí)行計劃，而不是原SQL語句，到這點上已經(jīng)變成一個數(shù)據(jù)結(jié)構(gòu)。

大部分場景下，它都與原語句不相同，你可以檢測查詢偶花旗到底是如何轉(zhuǎn)化語句的。

EXPLAIN EXTENDED在mysql 5.0 以上版本中可用，在5.1中增加了一個filtered列。

EXPLAIN PARTITIONS會顯示查詢將訪問的分區(qū)，如果查詢是基于分區(qū)表的話。

在mysql 5.1以上的版本中會存在。

EXPLAIN限制：

· explain根本不會告訴你觸發(fā)器、存儲過程或UDF會如何影響查詢

· 不支持存儲過程，盡管可以手動抽取查詢并單獨地對其進行explain操作

· 它并不會告訴你mysql在執(zhí)行計劃中所做的特定優(yōu)化

· 它并不會顯示關(guān)于查詢的執(zhí)行計劃的所有信息

· 它并不區(qū)分具有相同名字的事物，例如，它對內(nèi)存排列和臨時文件都使用“filesort”，并且對于磁盤上和內(nèi)存中的臨時表都顯示“Using temporary”

· 可能會產(chǎn)生誤導，比如，它會對一個有著很小limit的查詢顯示全索引掃描（mysql 5.1的explain關(guān)于檢查的行數(shù)會顯示更精準的信息，但早期版本并不考慮limit）

重寫非SELECT查詢

mysql explain只能解釋select查詢，并不會對存儲程序調(diào)用和insert、update、delete或其他語句做解釋。然而，你可以重寫某些非select查詢以利用explain。為了達到這個目的，只需要將該語句轉(zhuǎn)化成一個等價的訪問所有相同列的select，任何體積的列都必須在select列表，關(guān)聯(lián)子句，或者where子句中。

假如，你想重寫下面的update語句使其可以利用explain

UPDATE sakila.actor
INNER JOIN sakila.film_actor USING (actor_id)
SET actor.last_update=film_actor.last_update;

下面的explain語句并不等價于上面的update，因為它并不要求服務器從任何一個表上獲取last_update列

這個差別非常重要。例如，輸出結(jié)果顯示mysql將使用覆蓋索引，但是，當檢索并更新last_updated列時，就無法使用覆蓋索引了，下面這種改寫法就更接近原來的語句：

像這樣重寫查詢并不非?？茖W，但對幫助理解查詢是怎么做的已經(jīng)足夠好了。
（MySQL 5.6將允許解釋非SELECT查詢）

顯示查詢計劃時，對于寫查詢并沒有“等價”的讀查詢，理解這一點非常重要。一個SELECT查詢只需要找到數(shù)據(jù)的一份副本并返回。而任何修改數(shù)據(jù)的查詢必須在所有索引上查找并修改其所有副本，這常常比看起來等價的SELECT查詢的消耗要高得多。

EXPLAIN中的列

將在下一部分展示explain結(jié)果中每一列的意義。

輸出中的行以mysql實際執(zhí)行的查詢部分的順序出現(xiàn)，而這個順序不總是與其在原始SQL中的一致。

【id列】

這一列總是包含一個編號，識別select所屬的行，如果在語句當中沒有子查詢或聯(lián)合，那么只會有唯一的select，于是每一行在這個列中都將顯示一個1，否則，內(nèi)層的select語句一般會順序編號，對應于其在原始語句中的位置。

mysql將select查詢氛圍簡單和復雜類型，復雜類型可分為三大類：簡單子查詢，所謂的派生表（在from子句中的子查詢），以及union查詢。

下面是一個簡單的子查詢：

from子句中的子查詢和聯(lián)合給id列增加了更多的復雜性。

下面是一個from子句中的基本子查詢：

如你所知，這個查詢執(zhí)行時有一個匿名的臨時表，mysql內(nèi)部通過別名（der）在外層查詢中引用這個臨時表，在更復雜的查詢中可以看到ref列。

最后下面是一個union查詢：

注意第三個額外的行，union的結(jié)果總是放在一個匿名臨時表中，之后mysql將結(jié)果讀取到臨時表外，臨時表并不在原SQL中出現(xiàn)，因此它的id列為null。

與之前的例子相比（演示子查詢的那個from子句中），從這個查詢產(chǎn)生的臨時表在結(jié)果中出現(xiàn)在最后一行，而不是第一行。

到目前為止這些都非常直截了當，但這三類語句的混合則會使輸出變得非常復雜，我們稍后就會看到。

【select_type列】

這一列顯示了對應行是簡單還是復雜的select（如果是后者，那么是三種復雜類型中的哪一種）。simple值意味著查詢不包括子查詢和union，如果查詢有任何負責的子部分，則最外層部分標記為primary，其他部分標記如下：

SUBQUERY

    包含在select列表中的子查詢中的select（換句話說，不在from子句中）標記為SUBQUERY

DERIVED

    DERIVED值用來表示包含在FROM子句的子查詢中的select，mysql會遞歸執(zhí)行并將結(jié)果放到一個臨時表中。服務器內(nèi)部稱其“派生表”，因為該臨時表是從子查詢中派生來的。

UNION

    在UNION中的第二個和隨后的select被標記為unoin，第一個select被標記就好像它以部分外查詢來執(zhí)行。這就是之前的例子中在union中的第一個select顯示為primary的原因。如果union被from子句中的子查詢包含，那么它的第一個select會被標記為derived。

UNION RESULT

    用來從union的匿名臨時表檢索結(jié)果的select被標記為UNION RESULT。

除了這些值，SUBQUERY和UNION還可以被標記為DEPENDENT何UNCACHEABLE。

DEPENDENT意味著select依賴于外層查詢中發(fā)現(xiàn)的數(shù)據(jù)。

UNCACHEABLE意味著select中的某些特性阻止結(jié)果被緩存于一個Item_cache中。

（Item_cache未被文檔記載，它與查詢緩存不是一回事，盡管它可以被一些相同類型的構(gòu)件否定，例如RAND()函數(shù)。）

【table列】

這一列顯示了對應行正在訪問哪個表，在通常情況下，它相當明了：它就是哪個表，或是該表的列明（如果SQL中定義了別名）。

可以在這一列中從上往下觀察mysql的關(guān)聯(lián)優(yōu)化器為查詢選擇的關(guān)聯(lián)順序，例如，可以看到在下面的查詢中mysql選擇的關(guān)聯(lián)順序不同于語句中所指定的順序：

mysql的執(zhí)行計劃總是左側(cè)深度優(yōu)先樹，如果把這個計劃放倒，就能按順序讀出葉子節(jié)點，它們直接對應于explain中的行，之前的查詢計劃看起來如下圖所示：

派生表和聯(lián)合

當from子句中有子查詢或有union時，table列會變得復雜很多，這些場景下，確實沒有一個“表”可以參考到，因為mysql創(chuàng)建的匿名臨時表僅在查詢執(zhí)行過程中存在。

當在from子句中有子查詢時，table列是<derivedN>的形式，其中N是子查詢的id。這總是“向前引用”——換言之，N指向explain輸出中后面的一行。

當有union時，union result的table列包含一個參與union的id列表。這總是“向后引用”，因為union result出現(xiàn)在union中所有參與行之后，如果在列表中有超過20個id，table列卡諾被截斷以防止太長，此時不可能看到所有的值。幸運的是，仍然可以推測包括哪些行，因為你可以看到第一行的id，在這一行和union result之間出現(xiàn)的一切都會以某種方式被包含。

一個復雜select類型的例子

下面是一個無意義的查詢，我們這里把它用作某種復雜select類型的緊湊示例

limit子句只是為了方便起見，以防你打算不以explain方式執(zhí)行來看結(jié)果。

以下是explain的部分結(jié)果：

我們特意讓每個查詢部分訪問不同的表，以便可以弄清楚問題所在，但仍然難以解決，從上面開始看起：

第1行向前引用了der_1，這個查詢被標記為<derived3>，在原SQL中是第2行，想了解輸出中哪些行引用了<derived3>中的select語句，往下看。

第2行，它的id是3，因為它是查詢中第3個select的一部分，歸為derived類型是因為它嵌套在from子句中的子查詢內(nèi)部，在原sql中為第4行。

第3行的id為2，在原sql中為第3行，注意，它在具有更高id的行的后面，暗示后面再執(zhí)行，這是合理的。它被歸為DEPENDENT SUBQUERY，意味著其結(jié)果依賴于外層查詢（亦即某個相關(guān)子查詢）。本例中的外查詢從第2行開始，從der_1中檢索數(shù)據(jù)的select。

第4行被歸為union，意味著它是union中的第2個或之后的select，它的表為<derived6>，意味著是從子句from的子查詢中檢索數(shù)據(jù)并附加到union的臨時表。像之前一樣，要找到顯示這個子查詢的查詢計劃的explain行，往下看。

第5行是在原sql中的第8行的der_2子查詢，explain稱其為<derived6>。

第6行是<derived6>的select列表中的一個普通子查詢，它的id為7，這非常重要……

……因為它比5大，而5是第7行的id。為什么重要？因為它顯示了<derived6>子查詢的邊界。當explain輸出select類型為derived的一行時，表示一個“嵌套范圍”開始。如果后續(xù)行的id更小（本例中，5小于6），意味著嵌套范圍已經(jīng)被關(guān)閉。這就讓我們知道第7行是從<derived6>中檢索數(shù)據(jù)的select列表中的部分——例如，第4行的select列表的第一部分（原sql中的第7行）。這個例子相當容易理解，不需要知道嵌套范圍的意義和規(guī)則，當然有時候并不是這么容易。關(guān)于輸出中的這一行另外一個要注意的是，因為有用戶變量，它被列為UNCACHEABLE SUBQUERY。

最后一行union result，它代表從union的臨時表中讀取行的階段。你可以從這行開始反過來向后，如果你愿意的話，它返回id是1和4的行結(jié)果，它們分別引用了<derived3>和<derived6>

如你所見，這些復雜的select類型的組合會使explain的輸出相當難懂，理解規(guī)則會使其簡單些，但仍然需要多時間。

閱讀explain的輸出經(jīng)常需要在列表中跳來跳去，例如，再查看第1行輸出，僅僅盯著看，是無法知道它是union的一部分的，只有看到最后1行你才會明白過來。

【type列】

mysql用戶手冊上說這一列顯示了“關(guān)聯(lián)類型”，但我們認為更準確的說法是訪問類型——換言之就是mysql決定如何查找表中的行。下面是最重要的訪問方法，依次從最差到最優(yōu)：

ALL：

    這就是所謂的全表掃描，意味著mysql必須掃描整張表，從頭到尾，去找到需要的行。（有個例外，例如在查詢里使用了limit，或者在extra列中顯示“Using distinct/not exists”。

index：

    這個跟全表掃描一樣，只是mysql掃描表時按索引次序而不是行，它的主要優(yōu)點是避免了排序；最大缺點是要承擔按索引次序讀取整個表的開銷。這通常意味著若是按隨機次序訪問行，開銷將非常大。

    如果在extra列中看到“Using index”，說明mysql正在使用索引覆蓋，它只掃描索引的數(shù)據(jù)，而不是按索引次序的每一行，它比按索引次序全表掃描的開銷要少很多。

range：

    范圍掃描就是一個有限制的索引掃描，它開始與索引里的某一點，返回匹配這個值域的行，這比全索引掃描要好一點，因為它用不著遍歷全部索引，顯而易見的掃描是帶有between或在where子句里帶有>的查詢。

    當mysql使用索引去查找一系列值時，例如in()和or列表，也會顯示為范圍掃描，然而，這兩者其實是相當不同的訪問類型，在性能上有重要的差異。

    此類掃描的開銷跟索引類型的相當。

ref：

    這是一種索引訪問（有時也叫做索引查找），它返回所有匹配某個單個值的行，然而，它可能會找到多個符合條件的行，因此，它是查找和掃描的混合體，此類索引訪問只有當使用非唯一性索引或者唯一性索引的非唯一性前綴時才會發(fā)生。把它叫做ref是因為索引要跟某個參考值相比較。這個參考值或者是一個常數(shù)，或者是來自多表查詢前一個表里的結(jié)果值。

    ref_or_null是ref之上的一個變體，它意味著mysql必須在初次查找的結(jié)果里進行第二次查找以找出null條目。

eq_ref：

    使用這種索引查找，mysql知道最多只返回一條符合條件的記錄，這種訪問方法可以在mysql使用主鍵或者唯一性索引查找時看到，它會將它們與某個參考值做比較。mysql對于這類訪問類型的優(yōu)化做得非常好，因為它知道無需估計匹配行的范圍或在找到匹配行后再繼續(xù)查找。

const，system：

    當mysql能對查詢的某部分進行優(yōu)化并將其轉(zhuǎn)換成一個常量時，他就會使用這些訪問類型，舉例來說，如果你通過將某一行的主鍵放入where子句里的方式來選取此行的主鍵，mysql就能把這個查詢轉(zhuǎn)換為一個常量，然后就可以高效地將表從聯(lián)接執(zhí)行中移除。

null：

    這種訪問方式意味著mysql能在優(yōu)化階段分解查詢語句，在執(zhí)行階段甚至用不著再訪問表或者索引。例如，從一個索引列里選取最小值可以通過單獨查找索引來完成，不需要在執(zhí)行時訪問表。

【possible_keys列】

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

【key列】

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

換句話說，possible_keys揭示了哪一個索引能有助于高效地進行查找，而key顯示的是優(yōu)化采用哪一個索引可以最小化查詢成本。下面是一個例子：

【key_len列】

該列顯示了mysql在索引里使用的字節(jié)數(shù)，如果mysql正在使用的只是索引里的某些列，那么就可以用這個值來算出具體是哪些列，要記住，mysql 5.5及之前的版本只能使用索引的最左前綴，舉例來說，film_actor的主鍵是兩個smallint列，并且每個smallint列是兩字節(jié)，那么索引中的每項是4字節(jié)，以下就是一個查詢的示例：

基于結(jié)果中的key_len列，可以推斷出查詢使用唯一的首列——actor_id列，來執(zhí)行索引查找，當我們計算列的使用情況時，務必把字符列中的字符集頁考慮進去。

查看執(zhí)行計劃：

這個查詢中平均長度為13字節(jié)，即為a列和b列的總長度，a列是3個字符，utf8下每一個最多為3字節(jié)，而b列是一個4字節(jié)整型。

mysql并不總是顯示一個索引真正使用了多少，例如，如果對一個前綴模式匹配執(zhí)行l(wèi)ike查詢，它會顯示列的完全寬度正在被使用。

key_len列顯示了在索引字段中可能的最大長度，而不是表中數(shù)據(jù)使用的實際字節(jié)數(shù)，在前面例子中mysql總是顯示13字節(jié)，即使a列恰巧只包含一個字符長度。換言之，key_len通過查找表的定義而被計算出，而不是表中的數(shù)據(jù)。

【ref列】

這一列顯示了之前的表在key列記錄的索引中查找值所用的列或常量，下面是一個展示關(guān)聯(lián)條件和別名組合的例子，注意，ref列反映了在查詢文本中film表是如何以f為別名的：

【rows列】

這一列是mysql估計為了找到所需的行而要讀取的行數(shù)。這個數(shù)字是內(nèi)嵌循環(huán)關(guān)聯(lián)計劃里的 循環(huán)數(shù)目，也就是說它不是mysql認為它最終要從表里讀取出來的行數(shù)，而是mysql為了找到符合查詢的每一點上標準的那些行而必須讀取的行的平均數(shù)。（這個標準包括sql里給定的條件，以及來自聯(lián)接次序上前一個表的當前列。）

根據(jù)表的統(tǒng)計信息和索引的選用情況，這個估算可能很不精確，在mysql5.0及更早的版本里，它也反映不出limit子句，舉例來說，下面這個查詢不會真的檢查1057行。

通過把所有rows列的值相乘，可以粗略地估算出整個查詢會檢查的行數(shù)，例如，以下這個查詢大約會檢查2600行。

要記住這個數(shù)字是mysql認為它要檢查的行數(shù)，而不是結(jié)果集里的行數(shù)，同時也要認識到有很多優(yōu)化手段，例如關(guān)聯(lián)緩沖區(qū)和緩存，無法影響到行數(shù)的顯示，mysql可能不必真的讀所有它估計到的行，它也不知道任何關(guān)于操作系統(tǒng)或硬件緩存的信息。

【Extra列】

這一列包含的是不適合在其他列顯示的額外信息。mysql用戶手冊里記錄了大多數(shù)可以在這里出現(xiàn)的值。

常見的最重要的值如下。

“Using index”

    此值表示mysql將使用覆蓋索引，以避免訪問表。不要把覆蓋索引和index訪問類型弄混了。

“Using where”

    這意味著mysql服務器將在存儲引擎檢索行后再進行過濾，許多where條件里涉及索引中的列，當（并且如果）它讀取索引時，就能被存儲引擎檢驗，因此不是所有帶where子句的查詢都會顯示“Using where”。有時“Using where”的出現(xiàn)就是一個暗示：查詢可受益于不同的索引。

“Using temporary”

    這意味著mysql在對查詢結(jié)果排序時會使用一個臨時表。

“Using filesort”

    這意味著mysql會對結(jié)果使用一個外部索引排序，而不是按索引次序從表里讀取行。mysql有兩種文件排序算法，這兩種排序方式都可以在內(nèi)存或者磁盤上完成，explain不會告訴你mysql將使用哪一種文件排序，也不會告訴你排序會在內(nèi)存里還是磁盤上完成。

“Range checked for each record(index map: N)”

    這個意味著沒有好用的索引，新的索引將在聯(lián)接的每一行上重新估算，N是顯示在possible_keys列中索引的位圖，并且是冗余的。

樹形格式的輸出

    mysql用戶往往更希望把explain的輸出格式化成一棵樹，更加精確地展示執(zhí)行計劃。實際上，explain查看執(zhí)行計劃的方式確實有點笨拙，樹狀結(jié)構(gòu)也不適合表格化的輸出，當extra列里有大量的值時，缺點更明顯，使用union也是這樣，union跟mysql能做的其他類型的聯(lián)接不太一樣，它不太適合explain。

    如果對explain的規(guī)則和特性有充分了解，使用樹形結(jié)構(gòu)的執(zhí)行計劃也是可行的。但是這有點枯燥，最好還是留給自動化的工具處理，Percona Toolkit包含了pt-visual-explain，它就是這樣一個工具。

MySQL 5.6中的改進

mysql5.6中將包含一個對explain的重要改進：能對類似update、insert等的查詢進行解釋，盡管可以將dml語句轉(zhuǎn)化為等價的“select”查詢并explain，但結(jié)果并不會完全反映語句是如何執(zhí)行的，因而這仍然非常有幫助。在開發(fā)使用類似Percona Toolkit中的pt-upgrade時曾嘗試使用過那個技術(shù)，我們不止一次發(fā)現(xiàn)，在將查詢轉(zhuǎn)化為select時，優(yōu)化器并不能按我們預期的代碼路徑執(zhí)行。因而explain一個查詢而不需要轉(zhuǎn)化為select，對我們理解執(zhí)行過程中到底發(fā)生什么，是非常有幫助的。

mysql5.6還將包括對查詢優(yōu)化和執(zhí)行引擎的一系列修改，允許匿名的臨時表盡可能晚地被具體化，而不總是在優(yōu)化和執(zhí)行使用到此臨時表的部分查詢時創(chuàng)建并填充它們，這將允許mysql可以直接解釋帶子查詢的查詢語句，而不需要先實際地執(zhí)行子查詢。

最后，mysql5.6將通過在服務器中增加優(yōu)化跟蹤功能的方式改進優(yōu)化器的相關(guān)部分，浙江允許用戶查看優(yōu)化器坐出的抉擇，以及輸入（例如，索引的基數(shù)）和抉擇的原因。這非常有幫助，不僅僅對理解服務器選擇的執(zhí)行計劃如此，對為什么選擇這個計劃也如此。

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。