MySQL的EXPLAIN命令顯示了mysql如何使用索引來處理select語句以及連接表墨坚⊙硪可以幫助選擇更好的索引和寫出更優(yōu)化的查詢語句。
一泽篮、通過expalin可以得到
1盗尸、表的讀取順序
2、表的讀取操作的操作類型
3帽撑、哪些索引可以使用
4泼各、哪些索引被實際使用
5、表之間的引用
6油狂、每張表有多少行被優(yōu)化器查詢

二历恐、如何調(diào)用expalin命令

1、只需要在SELECT前面加上EXPLAIN即可专筷，例如

explain select surname,first_name form a,b where a.id=b.id 

2弱贼、在語句結(jié)尾(;之前)加上\G能夠更清晰的查看.
3、需要說的是EXPLAIN只對SELECT查詢作解釋,INSERT,UPDATE,DELETE不會

三磷蛹、explain顯示的列
1吮旅、id
語句的執(zhí)行順序標識，如果在語句中沒有子查詢或聯(lián)合,說明只有一個SELECT,于是這個列顯示為1,否則內(nèi)層的SELECT會順序編號.

2味咳、select_type
顯示了對應的查詢是簡單還是復雜SELECT庇勃，主要有以下幾種查詢類型

1)、simple 簡單類型
語句中沒有子查詢或union

2)槽驶、primary
最外層的select 责嚷，不是主鍵
這是為更復雜的查詢而創(chuàng)建的首要表(也就是最外層的表)。這個類型通车囝恚可以在DERIVED 和UNION 類型混合使用時見到罕拂。

3)、union
union是在select 語句中第二個select語句后面所有的select全陨，第一個select 為primary
這是UNION 語句其中的一個SQL 元素爆班。

4)、dependent subquery
子查詢中內(nèi)層中第一個select語句
這個select-type 值是為使用子查詢而定義的辱姨。下面的SQL語句提供了這個值： mysql> EXPLAIN SELECT p.* -> FROM parent p -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);

5)柿菩、dependent union
子查詢中union且為union中第二個select開始的后面所有select，依賴于外部的結(jié)果集雨涛。

6)枢舶、SUBQUERY
子查詢內(nèi)層查詢的第一個SELECT懦胞，結(jié)果不依賴于外部查詢結(jié)果集。

7)祟辟、devived
派生表的查詢語句
當一個表不是一個物理表時医瘫，那么就被叫做DERIVED。下面的SQL 語句給出了一個QEP 中DERIVED select-type 類型的
示例：

 mysql> EXPLAIN SELECT MAX(id)
 -> FROM (SELECT id FROM users WHERE first = 'west') c;

8)旧困、uncacheable subquery
結(jié)果集無法緩存的子查詢

9)醇份、union result
union中合并的結(jié)果
這是一系列定義在UNION 語句中的表的返回結(jié)果。當select_type 為這個值時吼具，經(jīng)沉欧祝可以看到table 的值是<unionN,M>， 這說明匹配的id 行是這個集合的一部分拗盒。下面的SQL產(chǎn)生了一個UNION和UNION RESULT select-type：

mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val LIKE 'a%' 
-> UNION 
-> SELECT p.* FROM parent p WHERE p.id > 5;

**3怖竭、table **

1)、顯示對應行正在訪問哪個表
2)陡蝇、當FROM子句中有子查詢或UNION時,table列是<derivedN>,其中N是id列對應的值

**4痊臭、type **
聯(lián)合查詢所使用的類型，type顯示的是訪問類型登夫，是較為重要的一個指標广匙，結(jié)果值從好到壞依次是：
system > const > eq_ref > ref >fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > range > index > ALL

一般來說，得保證查詢至少達到range級別恼策，最好能達到ref鸦致。

這列很重要,顯示了連接使用了哪種類別,有無使用索引。主要包括以下幾種類型

1)涣楷、all
全表掃描分唾，效果是最不理想的。
2)狮斗、const
const是在where條件以常量作為查詢條件绽乔，最多只會有一條記錄匹配，由于是常量碳褒，實際上只須要讀一次迄汛。
3)、eq_ref
最多只會有一條匹配結(jié)果骤视，一般是通過主鍵或唯一鍵索引來訪問。一般會出現(xiàn)在連接查詢的語句中鹃觉。
4)专酗、fulltext
進行全文索引檢索。
5)盗扇、index
全索引掃描祷肯。MySQL在掃描表時按索引次序進行而不是行沉填。
6)、index_merge
查詢中同時使用兩個（或更多）索引佑笋，然后對索引結(jié)果進行合并（merge）翼闹，再讀取表數(shù)據(jù)。
7)蒋纬、index_subquery
子查詢中的返回結(jié)果字段組合是一個索引（或索引組合）猎荠，但不是一個主鍵或唯一索引。
8)蜀备、rang
索引范圍掃描关摇。一個有限制的索引掃描,它開始于索引里的某一點,返回匹配這個值域的行(顯而易見的范圍掃描.即帶有BETWEEN或在WHERE子句中帶有>的查詢,當MySQL使用索引去查找一系列值的時候,如IN()和OR列表，也為顯示的范圍掃描)
9)碾阁、ref
也叫索引查找,他返回所有匹配某單個值的行,它可能會找到多個符合條件行输虱。
10)、ref_or_null
與ref的唯一區(qū)別就是在使用索引引用的查詢之外再增加一個空值的查詢脂凶。
11)宪睹、system
系統(tǒng)表，表中只有一行數(shù)據(jù)蚕钦；
12)亭病、unique_subquery
子查詢中的返回結(jié)果字段組合是主鍵或唯一約束。

5冠桃、possible_keys
這一列顯示了查詢可以使用哪些索引,是基于查詢訪問的列和使用的比較操作符來判斷的.
如果沒有任何索引可以使用命贴，就會顯示成null

6、key
顯示了MySQL決定采用哪個索引來優(yōu)化對該表的訪問

7食听、key_len
1)胸蛛、key_len列顯示mysql決定使用的鍵長度，如果鍵是null樱报，則長度為null葬项。
2)、顯示MySQL在索引里使用的字節(jié)數(shù).舉個例子就是在查詢中使用到了主鍵,而主鍵的數(shù)據(jù)類型為INT,則為4,SMALLINT則為2
3)迹蛤、使用的索引長度民珍，一般越短越好。

key_len 列定義了用于SQL 語句的連接條件的鍵的長度盗飒。此列值對于確認索引的有效性以及多列索引中用到的列的數(shù)目很重要嚷量。
此列的一些示例值如下所示： 此列的一些示例值如下所示：
key_len: 4 // INT NOT NULL
key_len: 5 // INT NULL
key_len: 30 // CHAR(30) NOT NULL
key_len: 32 // VARCHAR(30) NOT NULL
key_len: 92 // VARCHAR(30) NULL CHARSET=utf8

從這些示例中可以看出，是否可以為空逆趣、可變長度的列以及key_len 列的值只和用在連接和WHERE 條件中的索引的列 有關(guān)蝶溶。索引中的其他列會在ORDER BY 或者GROUP BY 語句中被用到。下面這個來自于著名的開源博客軟件WordPress 的表展示了 如何以最佳方式使用帶有定義好的表索引的SQL 語句：

CREATE TABLE `wp_posts` (  
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
 `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',  
 `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,  
 `post_type` varchar(20) NOT NULL DEFAULT 'post',  
 PRIMARY KEY (`ID`),  
 KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)  
) DEFAULT CHARSET=utf8  
  
 CREATE TABLE `wp_posts` (  
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
 `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',  
 `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,  
 `post_type` varchar(20) NOT NULL DEFAULT 'post',  
 PRIMARY KEY (`ID`),  
 KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)  
) DEFAULT CHARSET=utf8  

這個表的索引包括post_type、post_status抖所、post_date 以及ID列梨州。下面是一個演示索引列用法的SQL 查詢： EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01'; 這個查詢的QEP 返回的key_len 是62。這說明只有post_type列上的索引用到了(因為(20×3)+2=62)田轧。盡管查詢在WHERE 語句 中使用了post_type 和post_date 列暴匠，但只有post_type 部分被用到了。其他索引沒有被使用的原因是MySQL 只能使用定義索引的 最左邊部分傻粘。為了更好地利用這個索引每窖，可以修改這個查詢來調(diào)整索引的列。請看下面的示例：

mysql> EXPLAIN SELECT ID, post_title  
-> FROM wp_posts  
-> WHERE post_type='post'  
-> AND post_status='publish'  
-> AND post_date > '2010-06-01';  

在SELECT查詢的添加一個post_status 列的限制條件后抹腿，QEP顯示key_len 的值為132岛请，這意味著post_type、post_status警绩、post_date 三列(62+62+8崇败，(20×3)+2，(20×3)+2肩祥，8)都被用到了后室。此外，這個索引的主碼列ID 的定義是使用MyISAM 存儲索 引的遺留痕跡混狠。當使用InnoDB 存儲引擎時岸霹，在非主碼索引中包含主碼列是多余的，這可以從key_len 的用法看出來将饺。 相關(guān)的QEP 列還包括帶有Using index 值的Extra 列贡避。

8、Ref
顯示了之前的表在key列記錄的索引中查詢值所用到的列或常量
予弧。

9刮吧、rows
顯示的是MySQL為了找到所需的值而要讀取的行數(shù).

10、extra
在此顯示的是在其他列不適合顯示的額外信息
掖蛤，主要可能會是以下內(nèi)容：
1)杀捻、Distinct
查找distinct 值，當mysql找到了第一條匹配的結(jié)果時蚓庭，將停止該值的查詢致讥，轉(zhuǎn)為后面其他值查詢。

2)器赞、Full scan on NULL key
子查詢中的一種優(yōu)化方式垢袱，主要在遇到無法通過索引訪問null值的使用。

3)港柜、Using index
表優(yōu)化器只需要通過訪問索引就可以獲取到需要的數(shù)據(jù)

4)惶桐、Using where
表示優(yōu)化器需要通過索引回表查詢數(shù)據(jù)(需要通過訪問數(shù)據(jù)塊獲取結(jié)果集)

5)、Using temporary
意味著MySQL在對查詢結(jié)果排序時會用到一個臨時表.
這個值表示使用了內(nèi)部臨時(基于內(nèi)存的)表。一個查詢可能 用到多個臨時表姚糊。有很多原因都會導致MySQL 在執(zhí)行查詢期間 創(chuàng)建臨時表。兩個常見的原因是在來自不同表的列上使用了 DISTINCT授舟，或者使用了不同的ORDER BY 和GROUP BY 列救恨。 想了解更多內(nèi)容可以訪問http://forge.mysql.com/wiki/Overview_ of_query_execution_and_use_of_temp_tables。 可以強制指定一個臨時表使用基于磁盤的MyISAM 存儲引 擎释树。這樣做的原因主要有兩個： ? 內(nèi)部臨時表占用的空間超過min(tmp_table_size肠槽，max_ heap_table_size)系統(tǒng)變量的限制 ? 使用了TEXT/BLOB 列

6)、Using filesort
看到這個的時候奢啥，查詢就需要優(yōu)化了秸仙。意味著MySQL會對結(jié)果使用一個外部索引排序,而不是按索引次序從表里讀出來.
這是ORDER BY 語句的結(jié)果。這可能是一個CPU 密集型的過程桩盲。 可以通過選擇合適的索引來改進性能寂纪，用索引來為查詢結(jié)果排序。詳細過程請參考第4 章赌结。

7). Using join buffer
這個值強調(diào)了在獲取連接條件時沒有使用索引捞蛋，并且需要連接緩沖區(qū)來存儲中間結(jié)果。 如果出現(xiàn)了這個值柬姚，那應該注意拟杉，根據(jù)查詢的具體情況可能需要添加索引來改進性能。
這篇博客中有Using join buffer的詳細說明
https://my.oschina.net/xinxingegeya/blog/495897

這篇博客中有join的詳細說明
http://blog.csdn.net/tonyxf121/article/details/7796657

8). Impossible where
這個值強調(diào)了where 語句會導致沒有符合條件的行量承。請看下面的示例： mysql> EXPLAIN SELECT * FROM user WHERE 1=2;

9). Select tables optimized away
這個值意味著僅通過使用索引搬设，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行

10). Index merges
當MySQL 決定要在一個給定的表上使用超過一個索引的時候，就會出現(xiàn)以下格式中的一個撕捍，詳細說明使用的索引以及合并的類型拿穴。
? Using sort_union(...)
? Using union(...)
? Using intersect(...)

11). Using where, Using index .....
說明該查詢有部分數(shù)據(jù)需要通過回表方式獲取有部分數(shù)據(jù)可以直接通過索引獲取