explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL語(yǔ)句，從而知道MySQL是 如何處理你的SQL語(yǔ)句的。分析你的查詢語(yǔ)句或是結(jié)構(gòu)的性能瓶頸。在select 語(yǔ)句之前增加 explain 關(guān)鍵字钠绍，MySQL 會(huì)在查詢上設(shè)置一個(gè)標(biāo)記，執(zhí)行查詢時(shí)花沉，會(huì)返回執(zhí)行計(jì)劃的信息柳爽，而不是執(zhí)行這條SQL（如果 from 中包含子查詢，仍會(huì)執(zhí)行該子查詢碱屁，將結(jié)果放入臨時(shí)表中）

一磷脯、建表并插入數(shù)據(jù)

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
 `id` int(11) NOT NULL,
 `name` varchar(45) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18');
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (2,'b','2017-12-22 15:27:18');
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (3,'c','2017-12-22 15:27:18');
DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0');
INSERT INTO `film` (`id`, `name`) VALUES (1,'film1');
INSERT INTO `film` (`id`, `name`) VALUES (2,'film2');
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
 `id` int(11) NOT NULL,
 `film_id` int(11) NOT NULL,
 `actor_id` int(11) NOT NULL,
 `remark` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1);
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (2,1,2);
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (3,2,1);

以上代碼建立了三個(gè)表,每張表都插入了三條記錄：

1、actor（演員表）：id（主鍵不自增）娩脾、name（演員名字）赵誓、update_time（修改時(shí)間）

2、film（電影表）：id（主鍵自增）柿赊、name（電影名稱）俩功、idx_name（索引名稱，以name字段為索引）

3碰声、film_actor（電影表和演員的關(guān)聯(lián)表）：id（id主鍵不自增）诡蜓、film_id（電影id）、actor_id（演員id）胰挑、remark（備注）蔓罚、idx_film_actor_id（索引名稱、以film_id和actor_id的聯(lián)合索引）

執(zhí)行

explain select (select id from actor limit 1) from film;

語(yǔ)句瞻颂。執(zhí)行后可以看到下面圖片所展示的信息豺谈，那張圖片中所展示的列分別代表的是什么含義呢？

深入理解MySql的Explain

1贡这、id列

id列的編號(hào)是select的序列號(hào)茬末，有幾個(gè)select就有幾個(gè)id，并且id的順序是按照select出現(xiàn)（執(zhí)行）順序增長(zhǎng)的藕坯。MySql將select查詢分為簡(jiǎn)單查詢（SIMPLE）和復(fù)雜查詢（PRIMARY）团南。

復(fù)雜查詢分為三類：簡(jiǎn)單子查詢、派生表（from語(yǔ)句中的子查詢）炼彪、union查詢吐根。

id列的值越大，執(zhí)行優(yōu)先級(jí)越高辐马，id相同則從上往下執(zhí)行拷橘，id值如果為NULL則最后執(zhí)行。

1）、簡(jiǎn)單子查詢舉例如下：

explain select (select id from actor limit 1) from film;

深入理解MySql的Explain

這個(gè)簡(jiǎn)單的子查詢先查詢actor表后查詢film表

2）冗疮、from子句中的子查詢舉例如下：

explain select id from (select id from film) as der;

深入理解MySql的Explain

這個(gè)查詢執(zhí)行時(shí)有個(gè)臨時(shí)表別名為der萄唇，外部 select 查詢引用了這個(gè)臨時(shí)表

3）、union查詢舉例如下：

<pre style="-webkit-tap-highlight-color: transparent; box-sizing: border-box; font-family: Consolas, Menlo, Courier, monospace; font-size: 16px; white-space: pre-wrap; position: relative; line-height: 1.5; color: rgb(153, 153, 153); margin: 1em 0px; padding: 12px 10px; background: rgb(244, 245, 246); border: 1px solid rgb(232, 232, 232); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">explain select id from actor union all select id from actor;
</pre>

深入理解MySql的Explain

union結(jié)果總是放在一個(gè)匿名臨時(shí)表中术幔，臨時(shí)表不在SQL中出現(xiàn)另萤，因此它的id是NULL。

2诅挑、select_type列

select_type 表示對(duì)應(yīng)行的查詢類型是簡(jiǎn)單查詢還是復(fù)雜的查詢四敞，如果是復(fù)雜的查詢，又是上述三種復(fù)雜查詢中的哪一種拔妥。

1）忿危、simple：簡(jiǎn)單查詢。查詢不包含子查詢和union没龙；舉例如下：

explain select * from film where id=1;

深入理解MySql的Explain

2）铺厨、primary：復(fù)雜查詢中最外層的 select；

3）硬纤、subquery：包含在 select 中的子查詢（不在 from 子句中）解滓；

4）、derived：包含在 from 子句中的子查詢咬摇。MySQL會(huì)將結(jié)果存放在一個(gè)臨時(shí)表中伐蒂，也稱為派生表；

舉例了解 primary肛鹏、subquery 和 derived 類型：

explain select (select id from actor where id = 1) from (select * from film where id

深入理解MySql的Explain

我們先看到id列逸邦，由id的值可以看出先查詢film表(select * from film where id = 1)，并且查詢film表的查詢語(yǔ)句類型是DERIVED（衍生類型）在扰，因?yàn)檫@個(gè)select語(yǔ)句查詢出來結(jié)果后是提供給其它select語(yǔ)句用的缕减，所以就會(huì)衍生出來一個(gè)新的表，再查詢actor表(select id from actor where id = 1)芒珠，查詢actor表的查詢類型是SUBQUERY桥狡，最后執(zhí)行的就是最外層的select語(yǔ)句了，所以它的查詢類型為PRIMARY皱卓。

5）裹芝、union：在 union 中的第二個(gè)和隨后的 select；

6）娜汁、union result：從 union 臨時(shí)表檢索結(jié)果的 select嫂易；

舉例了解 union和union result類型：

explain select id from actor union all select id from actor;

深入理解MySql的Explain

3、table列

這一列表示 explain 的一行正在訪問哪個(gè)表掐禁。當(dāng) from 子句中有子查詢時(shí)怜械，table列是 < derivedN > 格式颅和，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢缕允。

當(dāng)有 union 時(shí)峡扩，UNION RESULT 的 table 列的值為<union1,2>，1和2表示參與 union 的 select 行id障本。

舉例了解table列中的< derivenN >：
explain select (select id from actor where id = 1) from (select * from film where id

深入理解MySql的Explain

從上面的圖片可以看出最外層的select查詢的表是< deriven3 >教届，但是我在sql中明明寫的是別名der，那table列應(yīng)該顯示的值是der呀彼绷。那為什么這里是< derived3 >呢巍佑？

在table值為< deriven3 >本來table是別名der的茴迁，但是der這張表是從select * from film where id = 1查詢來的寄悯，這條sql查詢的表是film，但是它查詢出來的結(jié)果是給其它select語(yǔ)句用的堕义，所以它是衍生出來了一個(gè)新的表猜旬，所以它的查詢類型是derived并且id是為3的。然后我們看到id為1的記錄它所對(duì)應(yīng)的table為< derived3 >倦卖，實(shí)際上這條記錄所對(duì)應(yīng)的查詢語(yǔ)句所查詢的表就是衍生表洒擦。derived3就對(duì)應(yīng)著id為3的查詢結(jié)果衍生出來的表。

舉例了解table列中的<union1,2>：
explain select id from actor union all select id from actor;

深入理解MySql的Explain

在上面的圖片中可以看出UNION RESULT那一條記錄的table值為< union1,2 >怕膛，這個(gè)1和2表示的是參與union的select行的id熟嫩。

4、type列

這一列表示關(guān)聯(lián)類型或訪問類型褐捻，即MySQL決定如何查找表中的行掸茅，查找數(shù)據(jù)行記錄的大概范圍。依次從查詢效率最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL一般來說柠逞，得保證查詢達(dá)到range級(jí)別昧狮，最好達(dá)到ref。

這個(gè)type也可能出現(xiàn)NULL值板壮，NULL值是mysql能夠在優(yōu)化階段分解查詢語(yǔ)句逗鸣，在執(zhí)行階段用不著再訪問表或索引。例如：在索引列中選取最小值绰精，可以單獨(dú)查找索引來完成撒璧，不需要在執(zhí)行時(shí)訪問表。

1）笨使、system和const：

mysql能對(duì)查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)化成一個(gè)常量（可以看show warnings 的結(jié)果）卿樱。用于 primary key 或 unique key 的所有列與常數(shù)比較時(shí)，所以表最多有一個(gè)匹配行阱表，讀取1次殿如，速度比較快贡珊。system是const的特例，表里只有一條元組匹配時(shí)為system涉馁。執(zhí)行以下語(yǔ)句：

explain select * from (select * from film where id = 1) tmp;

深入理解MySql的Explain

從上圖可知id為2隨對(duì)應(yīng)的是select * from film where id = 1這條sql先執(zhí)行门岔，這條sql是根據(jù)主鍵id去查詢的，所以這條sql只能查詢出來一條記錄烤送。對(duì)于查詢語(yǔ)句寒随，如果它的where條件是根據(jù)主鍵索引或者唯一索引去查詢的，能夠明確的知道查詢出的結(jié)果是只有一條記錄帮坚，那么它的type值就是const類型妻往。

在id值為1的記錄，type值為system试和，它的table值為衍生表< derived2 >讯泣，但是這個(gè)衍生表很明確的只有一條記錄，那這里相當(dāng)于就是一個(gè)常量了阅悍。在mysql執(zhí)行sql語(yǔ)句之前好渠，它會(huì)有一個(gè)分析與優(yōu)化，mysql知道你的這個(gè)衍生表中只有一行記錄节视，針對(duì)于這樣的select情況拳锚，它的type就是system。system可以說是const的一個(gè)特例寻行。system和const有什么區(qū)別呢霍掺？const所對(duì)應(yīng)的sql所查詢的表可以有很多記錄，但是const所對(duì)應(yīng)sql查詢出來的結(jié)果集只有一條拌蜘。system所對(duì)應(yīng)sql通過mysql的分析知道這張衍生表只有一條記錄杆烁。

可以使用如下代碼查看MySql是給出的優(yōu)化建議：

explain extended select * from (select * from film where id = 1) tmp;
show warnings;

深入理解MySql的Explain

深入理解MySql的Explain

explain extended會(huì)在 explain 的基礎(chǔ)上額外提供一些查詢優(yōu)化的信息。show warnings 命令可以得到優(yōu)化后的查詢語(yǔ)句拦坠，從而看出優(yōu)化器優(yōu)化了什么连躏。優(yōu)化后的查詢語(yǔ)句在上圖的Message列中

額外還有 filtered 列，是一個(gè)半分比的值贞滨，rows * filtered/100 可以估算出將要和 explain 中前一個(gè)表進(jìn)行連接的行數(shù)（前一個(gè)表指 explain 中的id值比當(dāng)前表id值小的表）入热。

2）、eq_ref： primary key （主鍵索引）或 unique key（唯一索引） 的所有部分被連接使用 晓铆，最多只會(huì)返回一條符合條件的記錄勺良。這可能是在 const 之外最好的聯(lián)接類型了，舉例如下：

explain select * from film_actor left join film on film_actor.film_id = film.id;

image.png

3）骄噪、ref： 相比 eq_ref尚困，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴链蕊，索引要和某個(gè)值相比較事甜，可能會(huì)找到多個(gè)符合條件的行谬泌。舉例如下：

a）、簡(jiǎn)單select查詢逻谦，name是普通索引掌实。

explain select * from film where name = "film1";

深入理解MySql的Explain

b）、關(guān)聯(lián)表查詢邦马，idx_film_actor_id是film_id和actor_id的聯(lián)合索引贱鼻。這里使用到了film_actor的左邊前綴film_id部分。

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

深入理解MySql的Explain

4）滋将、range： 范圍掃描通常出現(xiàn)在 in(), between ,> ,<, >= 等操作中邻悬。使用一個(gè)索引來檢索給定范圍的行。

舉例如下：

explain select * from actor where id > 1;

深入理解MySql的Explain

5）随闽、index： 掃描全表索引父丰，這通常比ALL快一些。（index是從索引中讀取的橱脸，而all是從硬盤中讀却∶住）

舉例如下：

explain select * from film;

深入理解MySql的Explain

6）、all：即全表掃描添诉，意味著mysql需要從頭到尾去查找所需要的行。通常情況下這需要增加索引來進(jìn)行優(yōu)化了医寿。舉例如下：

深入理解MySql的Explain

5栏赴、possible_keys列

這一列顯示查詢可能使用哪些索引來查找。explain 時(shí)可能出現(xiàn) possible_keys 有列靖秩，而 key 顯示 NULL 的情況须眷，這種情況是因?yàn)楸碇袛?shù)據(jù)不多，mysql認(rèn)為索引對(duì)此查詢幫助不大沟突，選擇了全表查詢花颗。

如果該列是NULL，則沒有相關(guān)的索引惠拭。在這種情況下扩劝，可以通過檢查 where 子句看是否可以創(chuàng)造一個(gè)適當(dāng)?shù)乃饕齺硖岣卟樵冃阅埽缓笥?explain 查看效果职辅。

6棒呛、keys列

這一列顯示mysql實(shí)際采用哪個(gè)索引來優(yōu)化對(duì)該表的訪問。 如果沒有使用索引域携，則該列是 NULL簇秒。如果想強(qiáng)制mysql使用或忽視possible_keys列中的索引，在查詢中使用 force index秀鞭、ignore index趋观。

7扛禽、key_len列

這一列顯示了mysql在索引里使用的字節(jié)數(shù)，通過這個(gè)值可以算出具體使用了索引中的哪些列皱坛。

舉例說明：film_actor的聯(lián)合索引 idx_film_actor_id 由 film_id 和 actor_id 兩個(gè)int列組成旋圆，并且每個(gè)int是4字節(jié)。通過結(jié)果中的key_len=4可推斷出查詢使用了第一個(gè)列：film_id列來執(zhí)行索引查找麸恍。

explain select * from film_actor where film_id = 2;

[圖片上傳失敗...(image-eece39-1543817581310)]

key_len計(jì)算規(guī)則如下：

字符串：

char(n)：n字節(jié)長(zhǎng)度

varchar(n)：2字節(jié)存儲(chǔ)字符串長(zhǎng)度灵巧，如果是utf-8，則長(zhǎng)度 3n + 2

數(shù)值類型：

tinyint：1字節(jié)

smallint：2字節(jié)

int：4字節(jié)

bigint：8字節(jié)

時(shí)間類型：

date：3字節(jié)

timestamp：4字節(jié)

datetime：8字節(jié)

如果字段允許為 NULL抹沪，需要1字節(jié)記錄是否為 NULL 索引最大長(zhǎng)度是768字節(jié)刻肄，當(dāng)字符串過長(zhǎng)時(shí)，mysql會(huì)做一個(gè)類似左前綴索引的處理融欧，將前半部分的字符提取出來做索引敏弃。

8、ref列

這一列顯示了在key列記錄的索引中噪馏，表查找值所用到的列或常量麦到，常見的有：const（常量），字段名欠肾。舉例如下：

1）瓶颠、ref為const

explain select * from film_actor where film_id = 2;

深入理解MySql的Explain

2）媳维、ref為字段

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

深入理解MySql的Explain

9锰瘸、rows列

這一列是mysql估計(jì)要讀取并檢測(cè)的行數(shù)，注意這個(gè)不是結(jié)果集里的行數(shù)匙头。

10瑟慈、Extra列

這一列展示的是額外信息桃移。常見的重要值如下：

1）、Using index： 查詢的列被索引覆蓋葛碧，并且where篩選條件是索引的前導(dǎo)列借杰，是性能高的表現(xiàn)。一般是使用了覆蓋索引(索引包含了所有查詢的字段)进泼。對(duì)于innodb來說蔗衡，如果是輔助索引性能會(huì)有不少提高；

舉例如下：

explain select film_id from film_actor where film_id = 1;

深入理解MySql的Explain

2）缘琅、Using where： 查詢的列未被索引覆蓋粘都，where篩選條件非索引的前導(dǎo)列；

舉例如下：

explain select * from actor where name = 'a';

深入理解MySql的Explain

3）刷袍、Using where Using index： 查詢的列被索引覆蓋翩隧，并且where篩選條件是索引列之一但是不是索引的前導(dǎo)列，意味著無(wú)法直接通過索引查找來查詢到符合條件的數(shù)據(jù)；

舉例如下：

explain select film_id from film_actor where actor_id = 1;

深入理解MySql的Explain

4）堆生、NULL： 查詢的列未被索引覆蓋专缠，并且where篩選條件是索引的前導(dǎo)列，意味著用到了索引淑仆，但是部分字段未被索引覆蓋涝婉，必須通過“回表”來實(shí)現(xiàn)，不是純粹地用到了索引蔗怠，也不是完全沒用到索引墩弯；

舉例如下：

explain select * from film_actor where film_id = 1;

深入理解MySql的Explain

5）、Using index condition： 與Using where類似寞射，查詢的列不完全被索引覆蓋渔工，where條件中是一個(gè)前導(dǎo)列的范圍；

舉例如下：

explain select * from film_actor where film_id > 1;

深入理解MySql的Explain

6）桥温、Using temporary： mysql需要?jiǎng)?chuàng)建一張臨時(shí)表來處理查詢引矩。出現(xiàn)這種情況一般是要進(jìn)行優(yōu)化的，首先是想到用索引來優(yōu)化侵浸。

舉例如下：

1旺韭、actor.name沒有索引，此時(shí)創(chuàng)建了張臨時(shí)表掏觉。

explain select distinct name from actor;

深入理解MySql的Explain

2区端、film.name建立了idx_name索引，此時(shí)查詢時(shí)extra是using index,沒有用臨時(shí)表履腋。

explain select distinct name from film;

[圖片上傳失敗...(image-3ff8b5-1543817581308)]

6）珊燎、Using filesort：mysql 會(huì)對(duì)結(jié)果使用一個(gè)外部索引排序，而不是按索引次序從表里讀取行遵湖。此時(shí)mysql會(huì)根據(jù)聯(lián)接類型瀏覽所有符合條件的記錄，并保存排序關(guān)鍵字和行指針晚吞，然后排序關(guān)鍵字并按順序檢索行信息延旧。這種情況下一般也是要考慮使用索引來優(yōu)化的。

舉例如下：

1槽地、actor.name未創(chuàng)建索引迁沫，會(huì)瀏覽actor整個(gè)表，保存排序關(guān)鍵字name和對(duì)應(yīng)的id捌蚊，然后排序name并檢索行記錄集畅。

explain select * from actor order by name;

深入理解MySql的Explain

2、film.name建立了idx_name索引,此時(shí)查詢時(shí)extra是using index缅糟。

explain select * from film order by name;

深入理解MySql的Explain