調(diào)用EXPLAIN

要使用EXPLAIN吼肥，只需要在查詢的select關(guān)鍵字前面機上explain這個詞舆逃。下面是一個簡單的explain結(jié)果：

mysql> explain select 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used
1 row in set (0.00 sec)

EXPLAIN有兩個主要變種重绷。EXPLAIN EXTENDED,EXPLAIN PARTITIONS。EXPLAIN 時 肯骇，SELECT語句也會實際執(zhí)行圃泡。EXPLAIN只是個近似結(jié)果碟案，以下是一些相關(guān)限制。

• EXPLAIN不會顯示觸發(fā)器颇蜡、存儲過程价说、UDF的性能。
• 不會顯示一個MySQL的內(nèi)置優(yōu)化
• 不區(qū)分具有相同名字的事物风秤。例如內(nèi)存排序和臨時文件排序都使用"filesort"鳖目，對于磁盤和內(nèi)存中的臨時表都表示"Using temporary"

EXPLAIN中的列

• id列

編號標(biāo)識SELECT所屬的行。若語句中沒有子查詢或聯(lián)合那么id應(yīng)該是相同的1缤弦。否則內(nèi)層的select語句一般會順序編號领迈，對應(yīng)其在原始語句的位置。MySQL將SELECT分為簡單和復(fù)雜兩種，復(fù)雜查詢主要三大類：簡單子查詢狸捅、from子句中的子查詢衷蜓、UNION查詢。下面是一個簡單的子查詢尘喝，

#簡單子查詢
mysql> explain select (select 1 from actor limit 1) from film;
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key                 | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
|  1 | PRIMARY     | film  | index | NULL          | idx_fk_language_id  | 1       | NULL | 1000 | Using index |
|  2 | SUBQUERY    | actor | index | NULL          | idx_actor_last_name | 137     | NULL |  200 | Using index |
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+

#from子句子查詢 這是有一個der臨時表
mysql> explain select film_id from (select film_id from film) AS der;
+----+-------------+------------+-------+---------------+--------------------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key                | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+--------------------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL               | NULL    | NULL | 1000 | NULL        |
|  2 | DERIVED     | film       | index | NULL          | idx_fk_language_id | 1       | NULL | 1000 | Using index |
+----+-------------+------------+-------+---------------+--------------------+---------+------+------+-------------+

#union查詢的結(jié)果總是放到一個臨時表里磁浇，因為不在原生SQL中，所以id列為null
mysql> explain select 1 union all select 1;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
|  2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+

• select_type

這一列顯示了對應(yīng)航是簡單還是復(fù)雜SELECT瞧省。SIMPLE 指簡單查詢扯夭，不熬擴子查詢和UNION。如果包含復(fù)雜查詢鞍匾，則最外層顯示PRIMARY,內(nèi)層部分可能顯示如下:
SUBQUERY 包含在select 查詢列中的子查詢。
DERIVED 包含在from子句的子查詢骑科，MySQL會遞歸執(zhí)行并將結(jié)果放到一個臨時表中橡淑。
UNION 在UNION中的第二個和隨后的select被標(biāo)記為union。如果union被from 子句中的子查詢包含咆爽，那第一個select會被標(biāo)記為DERIVED梁棠。
UNION RESULT 用來標(biāo)記UNION的匿名臨時表檢索結(jié)果的select。

• table 列

這一列顯示了對應(yīng)航正在訪問表的表名或別名斗埂。如下看一看到這條SQL的查詢順序符糊，先關(guān)聯(lián)actor&film_actor，再關(guān)聯(lián)actor

mysql> explain select film.film_id from film inner join film_actor using(film_id) inner join actor using(actor_id);
+----+-------------+------------+--------+------------------------+---------------------+---------+---------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys          | key                 | key_len | ref                       | rows | Extra       |
+----+-------------+------------+--------+------------------------+---------------------+---------+---------------------------+------+-------------+
|  1 | SIMPLE      | actor      | index  | PRIMARY                | idx_actor_last_name | 137     | NULL                      |  200 | Using index |
|  1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id | PRIMARY             | 2       | sakila.actor.actor_id     |   13 | Using index |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY                | PRIMARY             | 2       | sakila.film_actor.film_id |    1 | Using index |
+----+-------------+------------+--------+------------------------+---------------------+---------+---------------------------+------+-------------+

派生表和聯(lián)合呛凶。FROM子句有子查詢時男娄，table列時<derivedN>的形式，其中N是子查詢的id,指向下一個查詢的行漾稀。
當(dāng)有UNION時模闲，UNION RESULT的table包含所有參與UNION的查詢行id。下面我們來看一個復(fù)雜的查詢例子,:

mysql> explain select actor_id, 
(select 1 from film_actor where film_actor.actor_id = der_1.actor_id limit 1) 
from  
(select actor_id from actor limit 5) as der_1 
union all select film_id, 
(select @var1 from rental limit 1) 
from 
(select film_id,
(select 1 from store limit 1) from film limit 5) as der_2;
+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+
| id | select_type          | table      | type  | possible_keys | key                 | key_len | ref            | rows  | Extra           |
+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+
|  1 | PRIMARY              | <derived3> | ALL   | NULL          | NULL                | NULL    | NULL           |     5 | NULL            |
|  3 | DERIVED              | actor      | index | NULL          | idx_actor_last_name | 137     | NULL           |   200 | Using index     |
|  2 | DEPENDENT SUBQUERY   | film_actor | ref   | PRIMARY       | PRIMARY             | 2       | der_1.actor_id |    13 | Using index     |
|  4 | UNION                | <derived6> | ALL   | NULL          | NULL                | NULL    | NULL           |     5 | NULL            |
|  6 | DERIVED              | film       | index | NULL          | idx_fk_language_id  | 1       | NULL           |  1000 | Using index     |
|  7 | SUBQUERY             | store      | index | NULL          | idx_unique_manager  | 1       | NULL           |     2 | Using index     |
|  5 | UNCACHEABLE SUBQUERY | rental     | index | NULL          | idx_fk_staff_id     | 1       | NULL           | 16008 | Using index     |
| NULL | UNION RESULT         | <union1,4> | ALL   | NULL          | NULL                | NULL    | NULL           |  NULL | Using temporary |
+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+

#從上往下看崭捍，第一行id=1 table=<derived3> 指向id=3 的行建立的臨時表尸折，即原SQL 第一行從臨時表der_1查詢部分；
#第二行id=3 ,嵌套在from 子句中的子查詢派生表殷蛇，所以select_tyoe=DERIVED, 即原SQL 第四行 建立der_1臨時表這部分 实夹；
#第三行id=2，DEPENDENT SUBQUERY依賴子查詢粒梦，這里即原SQL中第二行依賴der_1即id=3的子查詢派生表,所以她在id=3更高的行后面亮航，暗示了在第二行后面執(zhí)行；
#第四行id=4為UNION谍倦，意味著是原生SQL中UNION 后的第二個或之后的select塞赂，即原SQL第六行檢索@var1這部分，table=<derived6>指向id=6的DRIVERD行;
#第五行id=6在原生SQL中第九行昼蛀，定義der_2臨時表的派生表子查詢宴猾；
#第六行id=7 是一個簡單子查詢SUBQUERY圆存，原生SQL定義der_2的select store 部分,另外因為下面一行id=5,所以id=6的DERIVED嵌套子查詢到這一行結(jié)束；
#第七行id=5即原生SQL有用戶變量@var1部分仇哆，這里已經(jīng)不是派生表嵌套子查詢部分沦辙，而逝檢索派生表部分；
#最后一行UNION RESULT表明了從臨時表中讀取行的讹剔，這里1 ,4說明是讀取id=1,4的行的臨時表即<derived3>油讯、<derived6> 

• type列

訪問類型，換言之就是MySQL如何查找表中的行延欠。
ALL: 俗稱的全表掃描陌兑，通常意味著MySQL必須掃描整張表，去找到需要的行由捎。如果查詢中使用了LIMIT關(guān)鍵字兔综，或者在Extra列中顯示"Using distinct/not exists"。
index: 全索引掃描狞玛，跟全表掃描一樣软驰，不過是按索引次序進(jìn)行而不是行順序。優(yōu)點是避免了排序心肪，缺點是要按照索引次序回表讀取的開銷锭亏，因為此時回表很可能是隨機訪問，開銷非常大硬鞍。不過如果Extra列顯示的是Using index慧瘤，說明使用了覆蓋索引，只掃描索引的數(shù)據(jù)膳凝，沒有回表操作碑隆，開銷要大大減少。
range: 范圍掃描是一個有限制的索引掃描它開始于索引里的某一點蹬音，返回匹配這個值域的行上煤，這筆全索引掃描好一些，因為用不著全部遍歷著淆，顯然是帶有BETWEEN或WHERE 子句帶>的查詢劫狠。MySQL中使用索引去查一系列值時，IN()或OR列表永部，也會顯示為范圍查詢独泞，但這兩者其實是不同的訪問類型。
ref: 索引訪問苔埋，它返回所有匹配某個單個值的行懦砂，可能會找到多個符合條件的行。此類索引訪問只有使用非唯一索引或者唯一性索引的非唯一性前綴時才會發(fā)生。取名ref因為索引要跟某個參考值作比較荞膘，這個參考值或者是一個常數(shù)或者是來自多表查詢里一個表的結(jié)果值罚随。ref_or_null是ref的一個變體，它表示MySQL必須在初次查找的結(jié)果中查找出NULL條目羽资。
eq_ref: 對多只返回一條記錄的索引查找淘菩。在使用主鍵或者唯一性索引查找時看到，它會將索引與某個參考值作比較屠升。
const, system: 當(dāng)MySQL能對查詢的某部分進(jìn)行優(yōu)化并轉(zhuǎn)換成一個常量時潮改，就會使用這些訪問類型。如將主鍵置于where列表中腹暖，MySQL就能將該查詢轉(zhuǎn)換為一個常量,system是const類型的特例汇在，當(dāng)查詢的表只有一行的情況下，使用system脏答。
NULL: 這種訪問方式意味著MySQL能在優(yōu)化階段分解語句趾疚，在執(zhí)行階段甚至用不著在訪問表或者索引。例如以蕴，從一個索引列里選取最小值可以單獨通過查找索引來完成。

• possible_keys列

這一列顯示了查詢可以使用那些索引辛孵。

• key列

這一列顯示了MySQL決定采用哪個索引來優(yōu)化對該表的訪問丛肮。如果該索引沒有出現(xiàn)在poosible_keys中，那么選用它是出于另外的原因魄缚，可能是選擇了覆蓋索引宝与。
換句話說，possible_keys揭示了哪一個索引能有助于高效的行查找冶匹，key顯示的是優(yōu)化采用什么索引減少查詢成本习劫。

• key_len列

該列顯示了MySQL在索引里使用的的字節(jié)數(shù)。

• ref列

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

• rows列

這一列是MySQL為了找到所需的行而要讀取的行數(shù)诽里，注意并不是結(jié)果集里的行數(shù)。

• filtered列

顯示的是針對表里符合某個調(diào)價你的記錄數(shù)的百分比悲觀估算飞蛹。

• Extra

"Using index" 表示MySQL將使用覆蓋索引谤狡，以避免表訪問。
"Using where" 表示MySQL服務(wù)器將在存儲引擎檢索行后在進(jìn)行過濾卧檐。有些WHERE條件里涉及索引當(dāng)中的列墓懂，存儲引擎讀取索引時就可以進(jìn)行校驗，此時便不會顯示Using Where霉囚。
"Using temporary" 這意味MySQL在對結(jié)果排序時會使用一個臨時表捕仔。
"Using filesort" 這意味著MySQL會對結(jié)果使用一個外部索引排序，而不是按照索引次序。
"Range checked for each record(index map:N)" 這個值意味著沒有好用的索引榜跌，新的索引在連接的每一行上重新估算闪唆。