前言

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

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

調(diào)用EXPLAIN

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

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

這是一個簡單的explain效果：

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

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

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

同時 explain有兩個變種

EXPLAIN EXTENDED會告訴服務(wù)器“逆向編譯” 執(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)生誤導(dǎo)，比如，它會對一個有著很小limit的查詢顯示全索引掃描（mysql 5.1的explain關(guān)于檢查的行數(shù)會顯示更精準(zhǔn)的信息，但早期版本并不考慮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，因為它并不要求服務(wù)器從任何一個表上獲取last_update列

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

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

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

EXPLAIN中的列

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

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

【id列】

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

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

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

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

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

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

最后下面是一個union查詢：

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

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

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

【select_type列】

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

SUBQUERY

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

DERIVED

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

UNION

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

UNION RESULT

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

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

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

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

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

【table列】

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

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

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

派生表和聯(lián)合

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

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

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

一個復(fù)雜select類型的例子

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

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

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

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

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

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

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

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

【type列】

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

ALL：

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

index：

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

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

range：

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

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

    此類掃描的開銷跟索引類型的相當(dāng)。

ref：

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

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

eq_ref：

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

const，system：

    當(dāng)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í)行索引查找，當(dāng)我們計算列的使用情況時，務(wù)必把字符列中的字符集頁考慮進去。

查看執(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為了找到符合查詢的每一點上標(biāo)準(zhǔn)的那些行而必須讀取的行的平均數(shù)。（這個標(biāo)準(zhǔn)包括sql里給定的條件，以及來自聯(lián)接次序上前一個表的當(dāng)前列。）

根據(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服務(wù)器將在存儲引擎檢索行后再進行過濾，許多where條件里涉及索引中的列，當(dāng)（并且如果）它讀取索引時，就能被存儲引擎檢驗，因此不是所有帶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)也不適合表格化的輸出，當(dāng)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)化器并不能按我們預(yù)期的代碼路徑執(zhí)行。因而explain一個查詢而不需要轉(zhuǎn)化為select，對我們理解執(zhí)行過程中到底發(fā)生什么，是非常有幫助的。

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

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

總結(jié)

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