前言

創新互聯專注于鲅魚圈企業網站建設,響應式網站開發,商城網站建設。鲅魚圈網站建設公司,為鲅魚圈等地區提供建站服務。全流程按需制作，專業設計，全程項目跟蹤，創新互聯專業和態度為您提供的服務

explain命令是查看查詢優化器如何決定執行查詢的主要方法。

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

調用EXPLAIN

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

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

這是一個簡單的explain效果：

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

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

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

同時 explain有兩個變種

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

可以通過緊接其后運行show warnings看到這個生成的語句，這個語句直接來自執行計劃，而不是原SQL語句，到這點上已經變成一個數據結構。

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

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

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

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

EXPLAIN限制：

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

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

· 它并不會告訴你mysql在執行計劃中所做的特定優化

· 它并不會顯示關于查詢的執行計劃的所有信息

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

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

重寫非SELECT查詢

mysql explain只能解釋select查詢，并不會對存儲程序調用和insert、update、delete或其他語句做解釋。然而，你可以重寫某些非select查詢以利用explain。為了達到這個目的，只需要將該語句轉化成一個等價的訪問所有相同列的select，任何體積的列都必須在select列表，關聯子句，或者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列

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

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

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

EXPLAIN中的列

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

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

【id列】

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

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

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

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

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

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

最后下面是一個union查詢：

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

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

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

【select_type列】

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

SUBQUERY

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

DERIVED

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

UNION

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

UNION RESULT

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

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

DEPENDENT意味著select依賴于外層查詢中發現的數據。

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

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

【table列】

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

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

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

派生表和聯合

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

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

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

一個復雜select類型的例子

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

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

以下是explain的部分結果：

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

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

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

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

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

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

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

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

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

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

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

【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必須在初次查找的結果里進行第二次查找以找出null條目。

eq_ref：

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

const，system：

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

null：

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

【possible_keys列】

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

【key列】

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

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

【key_len列】

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

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

查看執行計劃：

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

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

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

【ref列】

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

【rows列】

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

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

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

要記住這個數字是mysql認為它要檢查的行數，而不是結果集里的行數，同時也要認識到有很多優化手段，例如關聯緩沖區和緩存，無法影響到行數的顯示，mysql可能不必真的讀所有它估計到的行，它也不知道任何關于操作系統或硬件緩存的信息。

【Extra列】

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

常見的最重要的值如下。

“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列中索引的位圖，并且是冗余的。

樹形格式的輸出

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

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

MySQL 5.6中的改進

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

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

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

總結

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

分享名稱：MySQL中執行計劃explain命令示例詳解
當前路徑：http://vcdvsql.cn/article14/pcopge.html

成都網站建設公司_創新互聯，為您提供品牌網站建設、ChatGPT、、標簽優化、網站收錄、網頁設計公司

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯