explain主要用于sql語句中的select查詢，可以顯示的查看該sql語句索引的命中情況，從而更好的利用索引、優(yōu)化查詢效率。

    Explain語法如下：explain [extended] select ...

其中extended是選用的，如果使用的extended，那么explain之后就可以使用show warnings查看相應(yīng)的優(yōu)化信息，也就是mysql內(nèi)部實際執(zhí)行的query。

select類型：

simple：query中不包含子查詢或聯(lián)合查詢。

primary：包含子查詢或聯(lián)合查詢的query中，最外層的select查詢。

subquery：子查詢在select的目標(biāo)里，不在from中,子查詢的第一個select。

例如：EXPLAIN SELECT (SELECT actor_id FROM actor) FROM film_actor

dependent subquery：子查詢內(nèi)層的第一個select，依賴于外部查詢的結(jié)果集。

例如：EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor)

EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor WHERE actor_id=1)

EXPLAIN SELECT * FROM actor WHERE actor_id = (SELECT actor_id FROM film_actor WHERE actor_id=1)

uncacheable subquery：表示子查詢，但返回結(jié)果不能被cache，必須依據(jù)外層查詢重新計算。（在什么情況下會出現(xiàn)這個？）

derived：子查詢在from子句中，執(zhí)行查詢的時候會把子查詢的結(jié)果集放到臨時表(衍生表)。

例如：EXPLAIN SELECT * FROM (SELECT * FROM actor) AS a。此時table列會顯示<derivedN>，其中N對應(yīng)id列的值。

union：在聯(lián)合查詢中第二個及其以后的select對應(yīng)的類型。

例如：EXPLAIN SELECT * FROM film_actor UNION ALL SELECT * FROM actor

如果union包含在一個from子查詢里面，則from子查詢中的第一個select標(biāo)記為derived。

例如：EXPLAIN SELECT * FROM ( SELECT * FROM film_actor UNION ALL SELECT * FROM actor) a

union result：從union臨時表獲取結(jié)果集合。例如上面兩個查詢結(jié)果集中的最后一行。<union1,2,...>其中1,2,...所標(biāo)識的id列表代表id列，當(dāng)id列表長度超過20個之后就會省略后面的<union1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,...>。例子如上圖。

dependent union：子查詢中的union，且為union中第二個select開始的后面所有select，同樣依賴于外部查詢的結(jié)果集。

例如：EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor UNION ALL SELECT actor_id FROM film_actor)

uncacheable union：表示union第二個或以后的select，但結(jié)果不能被cache，必須依據(jù)外層查詢重新計算。(在什么情況下會出現(xiàn))

按照效率從高到低給出幾種常見的type類型：

NULL：mysql在優(yōu)化過程中分解query，執(zhí)行時甚至不用再訪問表數(shù)據(jù)或者索引，比如id=-1。

例如：EXPLAIN SELECT * FROM actor WHERE actor_id = -1

system：查詢的表僅有一行。這是const聯(lián)接類型的一個特例。(在沒有任何索引的情況下，只有一條數(shù)據(jù)，MyISAM會顯示system，InnoDB會顯示ALL)

const：最多會有一條記錄匹配。因為僅有一行，在這行的列值可被優(yōu)化器剩余部分認(rèn)為是常數(shù)。const表很快，因為它們只讀取一次。發(fā)生在有一個unique key或者主鍵，并且where子句給它設(shè)定了一個比較值。

例如：EXPLAIN SELECT * FROM actor WHERE actor_id = 1(其中actor_id是主鍵)

eq_ref：使用這種索引查找，最多返回一條符合條件的記錄。會在使用主鍵或者唯一性索引訪問數(shù)據(jù)時看到，除了const類型這可能是最好的聯(lián)接類型。

例如：EXPLAIN SELECT * FROM actor, actorsex WHERE actor.actor_id = actorsex.actor_id(其中actor_id是actor、actorsex的主鍵，且actorsex中只有一條記錄，如果多于一條記錄就不是eq_ref)

ref：這是一種索引訪問。只有當(dāng)使用一個非唯一性索引或者唯一性索引的非唯一性前綴(換句話說，就是無法根據(jù)該值只取得一條記錄)時才會發(fā)生，將索引和某個值相比較，這個值可能是一個常數(shù)，也可能是來自前一個表里的多表查詢的結(jié)果值。如果使用的鍵僅僅匹配少量行，該聯(lián)接類型是不錯的。

例如：EXPLAIN SELECT * FROM film_actor,actor    WHERE film_actor.actor_id=actor.actor_id AND film_actor.actor_id=1

ref_or_null：類似ref。不同的是Mysql會在檢索的時候額外的搜索包含 NULL 值的記錄，他意味著mysql必須進(jìn)行二次查找，在初次查找的結(jié)果中找出NULL條目。

index_merge：查詢中使用兩個或多個索引，然后對索引結(jié)果進(jìn)行合并。在這種情況下，key列包含所有使用的索引，key_len包含這些索引的最長的關(guān)鍵元素。

select * from test where column1 = 1 or column2 = 2(沒試出來！555555)

unique_subquery：用來優(yōu)化有子查詢的in，并且該子查詢是通過一個unique key選擇的。子查詢返回的字段組合是主鍵或者唯一索引。

例如：EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM actor)

index_subquery：該聯(lián)接類型類似于unique_subquery，子查詢中的返回結(jié)果字段組合是一個索引或索引組合，但不是一個主鍵或者唯一索引。

例如：EXPLAIN SELECT * FROM film_actor WHERE film_id IN (SELECT film_id FROM film_actor)

range：在一定范圍內(nèi)掃描索引。如where中帶有between或者>，此時ref列為NULL。當(dāng)使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關(guān)鍵字列時，可以使用range。

index：按索引次序掃描數(shù)據(jù)。因為按照索引掃描所以會避免排序，但也會掃描整表數(shù)據(jù)，若隨機(jī)讀取開銷會更大。如果extra列顯示using index，說明使用的是覆蓋索引(覆蓋索引：包含所有滿足查詢需要的數(shù)據(jù)列的索引)。對于InnoDB表特別有用，此時只訪問索引數(shù)據(jù)即可，不用再根據(jù)主鍵信息獲取原數(shù)據(jù)行，避免了二次查詢，而MyISAM表優(yōu)化效果相對InnoDB來說沒有那么的明顯。

all：按行掃描全表數(shù)據(jù)，除非查詢中有l(wèi)imit或者extra列顯示使用了distinct或notexists等限定詞。

Extra信息 ：

distinct：當(dāng)mysql找到第一條匹配的結(jié)果值時，就停止該值的查詢，然后繼續(xù)該列其他值的查詢。

not exists：在左連接中，優(yōu)化器可以通過改變原有的查詢組合而使用的優(yōu)化方法。當(dāng)發(fā)現(xiàn)一個匹配的行之后，不再為前面的行繼續(xù)檢索，可以部分減少數(shù)據(jù)訪問的次數(shù)。例如，表t1、t2，其中t2.id為not null，對于SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;由于 t2.id非空，所以只可能是t1中有，而t2中沒有，所以其結(jié)果相當(dāng)于求差。left join原本是要兩邊join，現(xiàn)在Mysql優(yōu)化只需要依照 t1.id在t2中找到一次t2.id即可跳出。

const row not found：涉及到的表為空表，里面沒有數(shù)據(jù)。

Full scan on NULL key：是優(yōu)化器對子查詢的一種優(yōu)化方式，無法通過索引訪問NULL值的時候會做此優(yōu)化。

Impossible Having：Having子句總是false而不能選擇任何列。例如having 1=0

Impossible WHERE：Where子句總是false而不能選擇任何列。例如where 1=0

Impossible WHERE noticed after reading const tables：mysql通過讀取“const/system tables”，發(fā)現(xiàn)Where子句為false。也就是說：在where子句中false條件對應(yīng)的表應(yīng)該是const/system tables。這個并不是mysql通過統(tǒng)計信息做出的，而是真的去實際訪問一遍數(shù)據(jù)后才得出的結(jié)論。當(dāng)對某個表指定了主鍵或者非空唯一索引上的等值條件，一個query最多只可能命中一個結(jié)果，mysql在explain之前會優(yōu)先根據(jù)這一條件查找對應(yīng)記錄，并用記錄的實際值替換query中所有用到來自該表屬性的地方。

例如：select * from a,b where a.id = 1 and b.name = a.name

執(zhí)行過程如下：先根據(jù)a.id = 1找到一條記錄(1, 'name1')，然后將b.name換成'name1'，然后通過a.name = 'name1'查找，發(fā)現(xiàn)沒有命中記錄，最終返回“Impossible WHERE noticed after reading const tables”。

No matching min/max row：沒有行滿足如下的查詢條件。

例如：EXPLAIN SELECT MIN(actor_id) FROM actor WHERE actor_id > 3(只有兩條記錄)

actor_id為唯一性索引時，會顯示“No matching min/max row”，否則會顯示“using where”。

no matching row in const table：對一個有join的查詢，包含一個空表或者沒有數(shù)據(jù)滿足一個唯一索引條件。

No tables used：查詢沒有From子句，或者有一個From Dual(dual：虛擬表，是為了滿足select...from...習(xí)慣)子句。

例如：EXPLAIN SELECT VERSION()

Range checked for each record (index map: N)：Mysql發(fā)現(xiàn)沒有好的index，但發(fā)現(xiàn)如果進(jìn)一步獲取下一張join表的列的值后，某些index可以通過range等使用。Mysql沒找到合適的可用的索引。取代的辦法是，對于前一個表的每一個行連接，它會做一個檢驗以決定該使用哪個索引（如果有的話），并且使用這個索引來從表里取得記錄。這個過程不會很快，但總比沒有任何索引時做表連接來得快。

Select tables optimized away：當(dāng)我們使用某些聚合函數(shù)來訪問存在索引的某個字段時，優(yōu)化器會通過索引直接一次定位到所需要的數(shù)據(jù)行完成整個查詢。在使用某些聚合函數(shù)如min, max的query，直接訪問存儲結(jié)構(gòu)(B樹或者B+樹)的最左側(cè)葉子節(jié)點或者最右側(cè)葉子節(jié)點即可，這些可以通過index解決。Select count(*) from table(不包含where等子句)，MyISAM保存了記錄的總數(shù)，可以直接返回結(jié)果，而Innodb需要全表掃描。Query中不能有g(shù)roup by操作。

unique row not found：對于SELECT … FROM tbl_name，沒有行滿足unique index或者primary key。從表中查詢id不存在的一個值會顯示Impossible WHERE noticed after reading const tables。

Using filesort：指Mysql將用外部排序而不是按照index順序排列結(jié)果。數(shù)據(jù)較少時從內(nèi)存排序，否則從磁盤排序。Explain不會顯示的告訴客戶端用哪種排序。

Using index：表示Mysql使用覆蓋索引避免全表掃描，不需要再到表中進(jìn)行二次查找數(shù)據(jù)。注意不要和type中的index類型混淆。

Using index for group-by：類似Using index，所需數(shù)據(jù)只需要讀取索引，當(dāng)query中有g(shù)roup by或distinct子句時，如果分組字段也在索引中，extra就會顯示該值。

Using temporary：Mysql將創(chuàng)建一個臨時表來容納中間結(jié)果。在group by和order by的時，如果有必要的話。例如group by一個非鍵列，優(yōu)化器會創(chuàng)建一個臨時表，有個按照group by條件構(gòu)建的unique key，然后對于每條查詢結(jié)果（忽略group by），嘗試insert到臨時表中，如果由于unique key導(dǎo)致insert失敗，則已有的記錄就相應(yīng)的updated。例如，name上沒有索引，SELECT name,COUNT(*) FROM product GROUP BY name，為了排序，Mysql就需要創(chuàng)建臨時表。此時一般還會顯示using filesort。

Using where：表示Mysql將對storage engine提取的結(jié)果進(jìn)行過濾。例如，price沒有index，SELECT * FROM product WHERE price=1300.00。有許多where的條件由于包含了index中的列，在查找的時候就可以過濾，所以不是所有帶where子句的查詢會顯示Using where。

Using join buffer：5.1.18版本以后才有的值。join的返回列可以從buffer中獲取，與當(dāng)前表join。

例如：explain select * from t1,t2 where t1.col < 10 and t2.col < 10

Scanned N databases：指在處理information_schema查詢時，有多少目錄需要掃描。

例如：EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES

網(wǎng)上說這個查詢會顯示Scanned all databases，我試了下extra列是空。

Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table：指示從information_schema查詢信息時有關(guān)文件開啟的優(yōu)化。 Skip_open_table：表信息已經(jīng)獲得，不需要打開。 Open_frm_only：只打開.frm文件。 Open_trigger_only：只打開.trg文件。 Open_full_table：沒有優(yōu)化。.frm，.myd和.myi文件都打開。

Using sort_union(…), Using union(…), Using intersect(…)：都出現(xiàn)在index_merge讀取類型中。 Using sort_union：用兩個或者兩個以上的key提取數(shù)據(jù)，但優(yōu)化器無法確保每個key會提取到一個自然排好序的結(jié)果，所以為了排除多余的數(shù)據(jù)，需要額外的處理。例如，customer的state，（lname，fname）是key，但lname不是key，SELECT COUNT(*) FROM customer WHERE (lname = ‘Jones') OR (state = ‘UT')，由于lname上面沒有key，所以使用（lname，fname），使得結(jié)果可能不按照順序，優(yōu)化器需要額外的一些工作。 Using union：用兩個或者兩個以上的key提取數(shù)據(jù)，分別取得結(jié)果是已排序，通過合并就可以獲得正確結(jié)果。例如，customer中的state和（lname，fname）是key，SELECT COUNT(state) FROM customer WHERE (lname = ‘Jones' AND fname='John') OR (state = ‘UT')。 Using intersect：用兩個或者兩個以上的key提取數(shù)據(jù)，分別取得結(jié)果是已排序，通過求交就可以獲得正確結(jié)果。例如，customer中的state和（lname，fname）是key，SELECT COUNT(state) FROM customer WHERE (lname = ‘Jones' AND fname='John') AND (state = ‘UT')。

Using where with pushed condition：僅用在ndb上。Mysql Cluster用Condition Pushdown優(yōu)化改善非索引字段和常量之間的直接比較。condition被pushed down到cluster的數(shù)據(jù)節(jié)點，并在所有數(shù)據(jù)節(jié)點同時估算，把不合條件的列剔除避免網(wǎng)絡(luò)傳輸。

mysql5.1.5下latin1、utf8、gbk字符數(shù)、字節(jié)數(shù)、漢字的對應(yīng)關(guān)系：

latin1：

1character=1byte, 1漢字=2character 一個字段定義成varchar(200)，可以存儲100個漢字或者200個字符，占用200個字節(jié)。尤其是當(dāng)字段內(nèi)容是字母和漢字組成時，盡量假設(shè)字段內(nèi)容都是由漢字組成，據(jù)此來設(shè)置字段長度。

utf8:

1character=3bytes, 1漢字=1character一個字段定義成 varchar(200)，則它可以存儲200個漢字或者200個字母，占用600個字節(jié)。

gbk:

1character=2bytes,1漢字=1character一個字段定義成 varchar(200)，則它可以存儲200個漢字或者200個字母，占用400個字節(jié)。

word版打包下載

php技術(shù)：mysql總結(jié)之explain，轉(zhuǎn)載需保留來源！

鄭重聲明：本文版權(quán)歸原作者所有，轉(zhuǎn)載文章僅為傳播更多信息之目的，如作者信息標(biāo)記有誤，請第一時間聯(lián)系我們修改或刪除，多謝。