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

    Explain語(yǔ)法如下：explain [extended] select ...

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

select類型：

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

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

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

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

dependent subquery：子查詢內(nèi)層的第一個(gè)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ù)外層查詢重新計(jì)算。（在什么情況下會(huì)出現(xiàn)這個(gè)？）

derived：子查詢?cè)趂rom子句中，執(zhí)行查詢的時(shí)候會(huì)把子查詢的結(jié)果集放到臨時(shí)表(衍生表)。

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

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

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

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

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

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

dependent union：子查詢中的union，且為union中第二個(gè)select開(kāi)始的后面所有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第二個(gè)或以后的select，但結(jié)果不能被cache，必須依據(jù)外層查詢重新計(jì)算。(在什么情況下會(huì)出現(xiàn))

按照效率從高到低給出幾種常見(jiàn)的type類型：

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

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

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

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

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

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

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

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

例如：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會(huì)在檢索的時(shí)候額外的搜索包含 NULL 值的記錄，他意味著mysql必須進(jìn)行二次查找，在初次查找的結(jié)果中找出NULL條目。

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

select * from test where column1 = 1 or column2 = 2(沒(méi)試出來(lái)！555555)

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

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

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

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

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

index：按索引次序掃描數(shù)據(jù)。因?yàn)榘凑账饕龗呙杷詴?huì)避免排序，但也會(huì)掃描整表數(shù)據(jù)，若隨機(jī)讀取開(kāi)銷會(huì)更大。如果extra列顯示using index，說(shuō)明使用的是覆蓋索引(覆蓋索引：包含所有滿足查詢需要的數(shù)據(jù)列的索引)。對(duì)于InnoDB表特別有用，此時(shí)只訪問(wèn)索引數(shù)據(jù)即可，不用再根據(jù)主鍵信息獲取原數(shù)據(jù)行，避免了二次查詢，而MyISAM表優(yōu)化效果相對(duì)InnoDB來(lái)說(shuō)沒(méi)有那么的明顯。

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

Extra信息 ：

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

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

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

Full scan on NULL key：是優(yōu)化器對(duì)子查詢的一種優(yōu)化方式，無(wú)法通過(guò)索引訪問(wèn)NULL值的時(shí)候會(huì)做此優(yōu)化。

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

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

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

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

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

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

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

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

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

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

例如：EXPLAIN SELECT VERSION()

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

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

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

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

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子句時(shí)，如果分組字段也在索引中，extra就會(huì)顯示該值。

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

Using where：表示Mysql將對(duì)storage engine提取的結(jié)果進(jìn)行過(guò)濾。例如，price沒(méi)有index，SELECT * FROM product WHERE price=1300.00。有許多where的條件由于包含了index中的列，在查找的時(shí)候就可以過(guò)濾，所以不是所有帶where子句的查詢會(huì)顯示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查詢時(shí)，有多少目錄需要掃描。

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

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

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

Using sort_union(…), Using union(…), Using intersect(…)：都出現(xiàn)在index_merge讀取類型中。 Using sort_union：用兩個(gè)或者兩個(gè)以上的key提取數(shù)據(jù)，但優(yōu)化器無(wú)法確保每個(gè)key會(huì)提取到一個(gè)自然排好序的結(jié)果，所以為了排除多余的數(shù)據(jù)，需要額外的處理。例如，customer的state，（lname，fname）是key，但lname不是key，SELECT COUNT(*) FROM customer WHERE (lname = ‘Jones') OR (state = ‘UT')，由于lname上面沒(méi)有key，所以使用（lname，fname），使得結(jié)果可能不按照順序，優(yōu)化器需要額外的一些工作。 Using union：用兩個(gè)或者兩個(gè)以上的key提取數(shù)據(jù)，分別取得結(jié)果是已排序，通過(guò)合并就可以獲得正確結(jié)果。例如，customer中的state和（lname，fname）是key，SELECT COUNT(state) FROM customer WHERE (lname = ‘Jones' AND fname='John') OR (state = ‘UT')。 Using intersect：用兩個(gè)或者兩個(gè)以上的key提取數(shù)據(jù)，分別取得結(jié)果是已排序，通過(guò)求交就可以獲得正確結(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é)點(diǎn)，并在所有數(shù)據(jù)節(jié)點(diǎn)同時(shí)估算，把不合條件的列剔除避免網(wǎng)絡(luò)傳輸。

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

latin1：

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

utf8:

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

gbk:

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

word版打包下載

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

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