執(zhí)行計(jì)劃是什么？

使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語(yǔ)句，從而知道MySQL是如何處理你的SQL語(yǔ)句的，分析你的查詢語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。

官網(wǎng)介紹：dev.mysql.com/doc/refman/…

前提介紹：文中所有案例 mysql 版本為 5.7.23

執(zhí)行計(jì)劃幫助我們完成什么事情？

表的讀取順序
數(shù)據(jù)讀取操作的操作類(lèi)型
哪些索引可以使用
哪些索引被實(shí)際使用
表之間的引用
每張表有多少行被優(yōu)化器查詢

怎么使用執(zhí)行計(jì)劃？

expain + SQL 語(yǔ)句
執(zhí)行計(jì)劃包含信息

執(zhí)行計(jì)劃包含信息解釋

id

select 查詢的序列號(hào)，包含一組數(shù)字，表示查詢中執(zhí)行 select 子句或操作表的順序

use oemp;

#測(cè)試表1
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#測(cè)試表2
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#測(cè)試表3
CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#id 相同
explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id
and t3.other_column = '';

#id 不同
explain select t2.* from t2 where id = (select id from t1 where id = 
(select t3.id from t3 where t3.other_column = ''));

#id 相同和不同同時(shí)存在
explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 
where s1.id = t2.id;
復(fù)制代碼

包含三種情況：id 相同，id 不同，id 相同和 id 不同同時(shí)存在。

id 相同

id 相同，執(zhí)行結(jié)果從上而下

運(yùn)行結(jié)果

id 不同

id不同如果是自查詢，id 的序號(hào)會(huì)遞增，id 值越大，優(yōu)先級(jí)越高，越先被執(zhí)行

運(yùn)行結(jié)果

id 相同和 id 不同時(shí)存在

id 如果相同，可以認(rèn)為是一組的，從上往下執(zhí)行；在所有組中，id 值越大，優(yōu)先級(jí)越高，越先被執(zhí)行；衍生 = DERIVED

執(zhí)行結(jié)果

derived_merge 是 Mysql5.7 引入的，會(huì)試圖將 Derived Table (派生表，from 后的自查詢) 視圖引用，公用表達(dá)式（Common table expressions）與外層查詢進(jìn)行合并。MySQL 5.7 不在兼容的實(shí)現(xiàn)方式，可以通過(guò)調(diào)整 optimizer_switch 來(lái)加以規(guī)避

set optimizer_switch='derived_merge=off';
復(fù)制代碼

說(shuō)白了，如果設(shè)置為 on 那么就不會(huì)出現(xiàn) derived_merge 行結(jié)果如下：

select_type

包括范圍：simple. primary，subquery, derived, union, union result 查詢類(lèi)型主要是用于區(qū)別普通查詢，聯(lián)合查詢，子查詢等復(fù)雜的查詢

simple，簡(jiǎn)單的select 語(yǔ)句，查詢中不包含自查詢或者 union
primary, 查詢?nèi)舭魏螐?fù)雜的子部分，最外層查詢則被標(biāo)記為primary
subquery，在 select 或 where 列表中包含子查詢
derived，在 from 列表中包含自查詢被標(biāo)記為 derived （衍生）MySQL 會(huì)遞歸執(zhí)行這些自查詢，把結(jié)果放在臨時(shí)表中。
union，若第二個(gè) select 出現(xiàn)在 union 之后，則被標(biāo)記為 union. 若 union 包含在 from 子句子查詢中，外層 select 將別標(biāo)記為 derived
union result, 從 union 表中獲取結(jié)果的 select

table

這行數(shù)據(jù)是關(guān)于那種表的

type

類(lèi)型：all , index , range, ref, eq_ref, const, system ,null type 顯示的是防衛(wèi)類(lèi)型，是較為重要的一個(gè)指標(biāo)，結(jié)果從好到壞依次是：system > count > eq_ref > range > index > all

sytem > const > eq_ref > ref > fulltext > ref_or_null > index_merge >> unique_subquery > index_subquery > range > index > ALL

system

表只有一行記錄（等于系統(tǒng)表），這是 const 類(lèi)型的特列，平時(shí)不會(huì)出現(xiàn)，這個(gè)也可以忽略不計(jì)

count

explain select * from (select * from t1 where id =1) d1;
復(fù)制代碼

表示通過(guò)索引一次就找到了， const 用于比較 primary key 或者 unique 索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快如將主鍵置于where 列表中， MySQL 就能將該查詢轉(zhuǎn)換為一個(gè)常量。

eq_ref

explain select * from t1, t2 where t1.id = t2.id;
復(fù)制代碼

唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配。常見(jiàn)于主鍵或唯一索引掃描. 查詢案例：

ref

# tb_emp ddl
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ;

#員工表添加年齡列
alter table tb_emp add column `age` int(11) default null after `name`;

#添加復(fù)合索引
create index idx_emp_name_age on tb_emp(`name`, `age`);

explain select * from tb_emp where `name` = 'z3';
復(fù)制代碼

非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行，本質(zhì)上也是一種索引訪問(wèn)，它返回所有匹配某個(gè)單獨(dú)的行，然而，它可能會(huì)找到多個(gè)符合個(gè)條件的行，所以它應(yīng)該屬于查找和掃描的混合體

range

explain select * from t1 where id between 1 and 3;

explain select * from t1 where id in (1, 2, 3);
復(fù)制代碼

只檢索給定范圍內(nèi)的行，使用一個(gè)索引來(lái)選擇行。key 列顯示使用了哪個(gè)索引一般就是你在 where 語(yǔ)句中出現(xiàn)了 between、<、>、in 等的查詢這種范圍掃描索引比全表掃描要好，因?yàn)樗恍枰_(kāi)始于索引的某個(gè)點(diǎn)，而結(jié)束于另一個(gè)點(diǎn)，不用全表掃描案例結(jié)果：

index

explain select id from t1;
復(fù)制代碼

Full Index Scan ， index 于 ALL的卻別，index 類(lèi)型只遍歷索引樹(shù)，這通常比 ALL 快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。（也就是說(shuō)雖然 all 和 index 都是讀全表，但是index 是從索引中讀取的，而 all 是從硬盤(pán)中讀取的）查詢結(jié)果：

all

explain select * from t1;
復(fù)制代碼

Full Table Scan 將遍歷全表找到匹配的行備注：一般來(lái)說(shuō)，得以保證查詢至少達(dá)到 rang 級(jí)別，最好能達(dá)到 ref。

possible_keys

顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)。查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用.

key

實(shí)際使用的索引，如果為NULL，則沒(méi)有使用索引查詢中若使用了覆蓋索引，則該索引僅出現(xiàn)在KEY列表中

explain select col1, col2  from t1;

create index idx_col1_col2 on t1(col1, col2);

explain select col1, col2  from t1;
復(fù)制代碼

案例一（加索引之前）案例二（加索引之后）

key_len

desc t1; 
explain select * from t1 where col1 = 'ab';
explain select * from t1 where col1 = 'ab' and col2 = 'bc';
復(fù)制代碼

表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢中的使用的索引的長(zhǎng)度，在不損失精確性的情況下，長(zhǎng)度越短越好 key_len 顯示的只為索引字段的最大可能長(zhǎng)度，** 并非實(shí)際使用長(zhǎng)度**。即 key_len e是更具表定義計(jì)算而得，不是通過(guò)表內(nèi)檢索出的。查詢結(jié)果：總結(jié)：條件越多，付出的代價(jià)越大，key_len 的長(zhǎng)度也就越大，建議在一定條件的情況下，key_len 越短，效率越高。

Rows

根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需的記錄所需讀取的行數(shù)

filtered

Extra

包含不適合其他列中顯示但十分重要的額外信息 id, select_type, table, type , possible_keys, key, key_len, ref, rows, Extra

1. Using filesort

文件排序

2. Using temporary

explain select col2 from t1 where col1 in ('ab', 'ac', 'as') group by col2 \G;

explain select col2 from t1 where col1 in ('ab', 'ac', 'as') 
group by col1, col2, col3 \G;
復(fù)制代碼

使用了臨時(shí)表保存中間結(jié)果， MySQL 在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表。常見(jiàn)于排序 order by 和分組查詢 group by 。例子：

3. Using index

explain select col2 from  t1 where col1=100;

explain select col1, col2 from  t1;
復(fù)制代碼

表示相應(yīng)的 select 操作使用了覆蓋索引 (Covering Index), 避免了訪問(wèn)表的數(shù)據(jù)行，效率不錯(cuò)～如果同時(shí)出現(xiàn) using where , 表示索引被用來(lái)執(zhí)行索引鍵值的查找；如果沒(méi)有同時(shí)出現(xiàn) using where , 表明索引引用來(lái)讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作。例子：覆蓋索引（Covering Index）

覆蓋索引（Covering Index）, 一說(shuō)為索引覆蓋
理解方式一：就是 select 的數(shù)據(jù)列只用從索引中就能取得，不必讀取數(shù)據(jù)行， MySQL 可以利用你索引返回 select 列表的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件，換句話說(shuō)查詢列要被所建的索引覆蓋
理解方式二：索引是高效找到的行的一個(gè)方法，但是一般數(shù)據(jù)庫(kù)也能使用索引找到一個(gè)列的數(shù)據(jù)，因此它不必讀取整個(gè)行，畢竟索引葉子節(jié)點(diǎn)存儲(chǔ)了他們索引的數(shù)據(jù)；當(dāng)能通過(guò)讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了。一個(gè)索引包含了（或覆蓋了）滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引。
注意：1. 如果要使用覆蓋索引，一定要注意 select 列表匯總只取出需要的列，不可 select * ；2. 因?yàn)槿绻麑⑺凶侄我黄鹱鏊饕龑?huì)導(dǎo)致索引文件過(guò)大，查詢性能下降。

4. Using Where

表明使用了 where 過(guò)濾

5. using join buffer

使用了鏈接緩存

6. impossible where

explain select * from t1 where 1=2;
復(fù)制代碼

where 子句的值總是 false , 不能用來(lái)獲取任何元組

7. select tbale optimized away

在沒(méi)有 GROUPBY 子句的情況下，基于索引優(yōu)化 MIN/MAX 操作或者對(duì)于 MyISAM 存儲(chǔ)引擎優(yōu)化 COUT（*）操作不必等到執(zhí)行階段再進(jìn)行計(jì)算，查詢執(zhí)行計(jì)劃生成的階段即完成優(yōu)化。

8. distinct

優(yōu)化 distinct 操作在找到第一匹配的元祖后立即停止找相同值的動(dòng)作。

舉個(gè)例子

例子描述：

explain select d1.name, (select id from t3) d2 from 
(select id, name from t1 where other_column = '') d1 
union 
(select name, id from t2);
復(fù)制代碼

查詢結(jié)果：案例解析：

第一行（執(zhí)行順序4）：id 列為1 ，表示 union 的第一個(gè) select , select_type 的 primary 表表示該查詢?yōu)橥鈱硬樵儯?table
列被標(biāo)記為 , 表示查詢結(jié)果來(lái)自一個(gè)衍生表，其中 derived3 中的 3 代表查詢衍生自第三個(gè) select 查詢，即 id 為 3 的 select [select d1.name ... ]
第二行（執(zhí)行順序?yàn)?）：id 為 3 ，是整個(gè)查詢中第三個(gè) select 的一部分，因查詢包含在from 中，所以為derived ?！緎elect id, name from where other_column = ''】
第三行（執(zhí)行順序?yàn)?）：select 列表中的子查詢 select_type 為 subquery , 為整個(gè)查詢中的第二個(gè) select . [select id from t3]
第四行（執(zhí)行順序?yàn)?）：select_type 為 union ，說(shuō)明第四個(gè) select 是 unin 里的第二個(gè) select , 最先執(zhí)行【select name ,id from t2】
第五行（執(zhí)行順序?yàn)?）：代表 union 的臨時(shí)表中讀取行的階段， table 列的表示用第一個(gè) 和第四個(gè) select 結(jié)果進(jìn)行union 操作 ?！緝蓚€(gè)結(jié)果 union 操作】

參考資料

mysql.com
dev.mysql.com/doc/refman/…

作者：老鄭_
鏈接：https://juejin.cn/post/7025079353608257550
來(lái)源：稀土掘金
著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處。

MySQL 執(zhí)行計(jì)劃使用詳解

執(zhí)行計(jì)劃是什么？

執(zhí)行計(jì)劃幫助我們完成什么事情？

怎么使用執(zhí)行計(jì)劃？