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

調(diào)用 EXPLAIN

要使用EXPLAIN，只需在查詢中的SELECT關(guān)鍵字之前增加EXPLAIN。MySQL會(huì)在查詢上設(shè)置一個(gè)標(biāo)記。當(dāng)執(zhí)行查詢時(shí)，這個(gè)標(biāo)記會(huì)使其返回關(guān)于在執(zhí)行計(jì)劃中每一步的信息，而不是執(zhí)行它(一般情況下)。它會(huì)返回一行或多行信息，顯示出執(zhí)行計(jì)劃中的每一部分和執(zhí)行的次序。

explain select 1結(jié)果示例：

在查詢中每個(gè)表在輸出中只有一行。如果查詢是兩個(gè)表的連接，那么輸出中將有兩行。別名表單算為一個(gè)表。如果把一個(gè)表與自己來凝結(jié)，輸出中也會(huì)有兩行。"表"意義在這里相當(dāng)廣泛：可以是一個(gè)子查詢，一個(gè)UNION結(jié)果等。

EXPLAIN 有兩個(gè)主要變種：

EXPLAIN EXTENDED。它會(huì)通知服務(wù)器"逆向編譯"執(zhí)行計(jì)劃為一個(gè)SELECT語句?？梢酝ㄟ^緊接其后運(yùn)行SHOW WARNINGS看到這個(gè)生成的語句。這個(gè)語句直接來自執(zhí)行計(jì)劃，而不是原SQL語句，到這點(diǎn)上已經(jīng)變成一個(gè)數(shù)據(jù)結(jié)構(gòu)。在大部分場(chǎng)景下它都與原語句不相同。(結(jié)果輸出添加了filterd列)

EXPLAIN PARTITIONS。它會(huì)顯示查詢將訪問的分區(qū)，如果查詢是基于分區(qū)表的話。 (結(jié)果輸出添加了partitions列)

認(rèn)為增加EXPLAIN時(shí)，MySQL不會(huì)執(zhí)行查詢，這是一個(gè)常見錯(cuò)誤。如果查詢?cè)贔ROM子句中包括子查詢，那么MySQL實(shí)際上會(huì)執(zhí)行子查詢，將其結(jié)果放在一個(gè)臨時(shí)表中，然后完成外層查詢優(yōu)化。它必須在可以完成外層查詢優(yōu)化之前處理所有類似的子查詢。

EXPLAIN只是一個(gè)近似結(jié)果。以下有一些相關(guān)的限制：

EXPLAIN不會(huì)告訴你觸發(fā)器、存儲(chǔ)過程或UDF會(huì)如何影響查詢。

EXPLAIN 不支持存儲(chǔ)過程，盡管可以手動(dòng)抽取查詢并單獨(dú)對(duì)其進(jìn)行EXPLAIN操作。

EXPLAIN不會(huì)告訴你MySQL在查詢執(zhí)行中所做的特定優(yōu)化。

EXPLAIN不會(huì)顯示關(guān)于查詢的執(zhí)行計(jì)劃的所有信息。

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

EXPLAIN 中的列

explain的輸出的列包括：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra等。

id 列

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

MySQL將SELECT查詢分為簡(jiǎn)單和復(fù)雜類型，復(fù)雜類型可分為三大類：簡(jiǎn)單子查詢、所謂的派生表(在FROM子句中的子查詢)，以及UNION查詢。

注意UNION結(jié)果輸出中的額外行。UNION結(jié)果總是放在一個(gè)匿名臨時(shí)表中，之后MySQL將結(jié)果讀取到臨時(shí)表外。臨時(shí)表并不在原SQL中出現(xiàn)，因此它的id列是NULL。

select_type 列

這一列顯示了對(duì)應(yīng)行是簡(jiǎn)單還是復(fù)雜select。值如下：

SIMPLE

該查詢不包括子查詢和UNION。

PRIMARY

復(fù)雜SELECT的最外層行。

SUBQUERY

包含在SELECT 列表中的子查詢中的select(簡(jiǎn)單子查詢)。

SUBQUERY還可被標(biāo)記為DEPENDENT，DEPENDENT SUBQUERY 意味著SELECT依賴于外層查詢中發(fā)現(xiàn)的數(shù)據(jù)。

DERIVED

包含在FROM子句的子查詢中的SELECT 。

MySQL會(huì)遞歸執(zhí)行并將結(jié)果放到一個(gè)臨時(shí)表中。服務(wù)器內(nèi)部稱其"派生表"，因?yàn)樵撆R時(shí)表是從子查詢中派生出來的。

UNION

在UNION中的第二個(gè)和隨后的SELECT 。

第一個(gè)SELECT被標(biāo)記就好像它以部分外查詢來執(zhí)行。如果UNION被FROM子句中的子查詢包含，那么它的第一個(gè)SELECT會(huì)被標(biāo)記為DERIVED 。

UNION還可被標(biāo)記為UNCACHEABLE。UNCACHEABLE UNION 意味著SELECT中的某些特性阻止結(jié)果被緩存與一個(gè)Item_cache中。

UNION RESULT

用來從UNION的匿名臨時(shí)表檢索結(jié)果的SELECT。

table 列

這一列顯示對(duì)應(yīng)行正在訪問哪個(gè)表?？梢栽谶@一列中從上往下觀察MySQL的關(guān)聯(lián)優(yōu)化器為查詢選擇的關(guān)聯(lián)順序。

查詢執(zhí)行計(jì)劃于EXPLAIN中的行相對(duì)應(yīng)的方式：

派生表和聯(lián)合

當(dāng)FROM子句中有子查詢或有UNION時(shí)，table列會(huì)變得復(fù)雜得多。在這些場(chǎng)景下，確實(shí)沒有一個(gè)"表"可以參考到，因?yàn)镸ySQL創(chuàng)建的匿名臨時(shí)表僅在查詢執(zhí)行過程中存在。

當(dāng)在FROM子句中有子查詢時(shí)，table列是的形式，其中N是子查詢的id。這總是"向前引用"。換言之，N指向EXPLAIN輸出中后面的一行。

當(dāng)有UNION時(shí)，UNION RESULT的table列包含一個(gè)參與UNION的id列表。這總是"向后引用"，因?yàn)閁NION RESULT出現(xiàn)在UNION中所有參與行之后。

type 列

type列顯示MySQL的訪問類型，即MySQL決定如何查找表中的行。下面是重要的訪問方法，依次從最差到最優(yōu)：

ALL

全表掃描，通常意味著MySQL必須掃描整張表，從頭到尾，去找到需要的行。

這里也有個(gè)例外，例如在查詢里使用了LIMIT，或者在Extra列中顯示"Using distinct/not exists"。

index

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

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

range

范圍掃描時(shí)一個(gè)有限制的索引掃描，它開始于索引里的某一點(diǎn)，返回匹配這個(gè)值域的行。

這比全索引掃描好一些，因?yàn)樗貌恢闅v全部索引。顯而易見的范圍掃描是帶有BETWEEN或在WHERE子句里帶有>的查詢。

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

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

ref

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

ref_or_null是ref之上的一個(gè)變體，它意味著MySQL必須在初次查找的結(jié)果。

eq_ref

使用這種索引查找，MySQL知道最多只返回一條符合條件的記錄。

這種訪問方法可以在MySQL使用主鍵或者唯一性索引查找時(shí)看到，它會(huì)將他們與某個(gè)參考值作比較。MySQL對(duì)于這類訪問類型的優(yōu)化坐的非常好，因?yàn)樗罒o需估計(jì)匹配行的范圍或在找到匹配行后再繼續(xù)查找。

const, system

當(dāng)MySQL能對(duì)查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)換成一個(gè)常量時(shí)，它就會(huì)使用這些訪問類型。

例如，如果你通過將某一行的主鍵放入where子句里的方式來選取此行的主鍵，MySQL就能把這個(gè)查詢轉(zhuǎn)換為一個(gè)常量。然后就可以高效地將表從聯(lián)接執(zhí)行中移除。

NULL

這種訪問方式意味著MySQL能在優(yōu)化階段分解查詢語句，在執(zhí)行階段甚至用不著再訪問表或者索引。

例如，從一個(gè)索引列里選取最小值可以通過單獨(dú)查找索引來完成，不需要執(zhí)行時(shí)訪問表。

possible_key 列

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

key 列

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

possible_key揭示了哪一個(gè)索引能有助于高效地行查找，而key顯示的是優(yōu)化采用哪一個(gè)索引可以最小化查找成本。

key_len 列

這列顯示了MySQL在索引里使用的字節(jié)數(shù)。如果MySQL正在使用的只是索引里的某些列，那么就可以這這個(gè)值來算出具體是哪些列。

key_len 列顯示了在索引字段中可能的最大長(zhǎng)度，而不是表中數(shù)據(jù)使用的實(shí)際字節(jié)數(shù)。

ref 列

這一列顯示了之前的表在key列記錄的索引中查找值所用的列或常量。

rows 列

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

這個(gè)數(shù)字是MySQL認(rèn)為它要檢查的行數(shù)，而不是結(jié)果集里的行數(shù)。

Extra 列

這一列包含的是不適合在其他列顯示的額外信息。常見的最重要的值如下：

Using index

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

Using where

這意味著MySQL服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過濾。許多where條件里涉及索引中的列，當(dāng)(并且如果)它讀取索引時(shí)，就能被存儲(chǔ)引擎檢驗(yàn)，因此不是所有帶where子句的查詢都會(huì)顯示"Using where"。有時(shí)"Using where"的出現(xiàn)就是一個(gè)暗示：查詢可受益于不同的索引。

Using temporary

這意味著MySQL在對(duì)查詢結(jié)果排序時(shí)會(huì)使用一個(gè)臨時(shí)表。

Using filesort

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

Range checked for each record(index map: N)

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

filtered 列

這一列在使用EXPLAIN EXTENDED時(shí)出現(xiàn)。它顯示的是針對(duì)表里符合某個(gè)條件(where子句或聯(lián)接條件)的記錄數(shù)的百分比所做的一個(gè)悲觀估算。如果將rows列和這個(gè)百分比相乘，就能看到MySQL估算它將和查詢計(jì)劃里前一個(gè)表關(guān)聯(lián)的行數(shù)。

總結(jié)

以上是生活随笔為你收集整理的mysql explain 派生表_MySQL的Explain命令的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。