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

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

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

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

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

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

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

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

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

id

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

use oemp;

#測試表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;

#測試表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;

#測試表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 相同和不同同時存在
explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 
where s1.id = t2.id;
復制代碼

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

id 相同

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

運行結(jié)果

id 不同

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

運行結(jié)果

id 相同和 id 不同時存在

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

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

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

set optimizer_switch='derived_merge=off';
復制代碼

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

select_type

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

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

table

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

type

類型：all , index , range, ref, eq_ref, const, system ,null type 顯示的是防衛(wèi)類型，是較為重要的一個指標，結(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 類型的特列，平時不會出現(xiàn)，這個也可以忽略不計

count

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

表示通過索引一次就找到了， const 用于比較 primary key 或者 unique 索引。因為只匹配一行數(shù)據(jù)，所以很快如將主鍵置于where 列表中， MySQL 就能將該查詢轉(zhuǎn)換為一個常量。

eq_ref

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

唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描. 查詢案例：

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`;

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

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

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

range

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

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

只檢索給定范圍內(nèi)的行，使用一個索引來選擇行。key 列顯示使用了哪個索引一般就是你在 where 語句中出現(xiàn)了 between、<、>、in 等的查詢這種范圍掃描索引比全表掃描要好，因為它只需要開始于索引的某個點，而結(jié)束于另一個點，不用全表掃描案例結(jié)果：

index

explain select id from t1;
復制代碼

Full Index Scan ， index 于 ALL的卻別，index 類型只遍歷索引樹，這通常比 ALL 快，因為索引文件通常比數(shù)據(jù)文件小。（也就是說雖然 all 和 index 都是讀全表，但是index 是從索引中讀取的，而 all 是從硬盤中讀取的）查詢結(jié)果：

all

explain select * from t1;
復制代碼

Full Table Scan 將遍歷全表找到匹配的行備注：一般來說，得以保證查詢至少達到 rang 級別，最好能達到 ref。

possible_keys

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

key

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

explain select col1, col2  from t1;

create index idx_col1_col2 on t1(col1, col2);

explain select col1, col2  from t1;
復制代碼

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

key_len

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

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

Rows

根據(jù)表統(tǒng)計信息及索引選用情況，大致估算出找到所需的記錄所需讀取的行數(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;
復制代碼

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

3. Using index

explain select col2 from  t1 where col1=100;

explain select col1, col2 from  t1;
復制代碼

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

覆蓋索引（Covering Index）, 一說為索引覆蓋
理解方式一：就是 select 的數(shù)據(jù)列只用從索引中就能取得，不必讀取數(shù)據(jù)行， MySQL 可以利用你索引返回 select 列表的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件，換句話說查詢列要被所建的索引覆蓋
理解方式二：索引是高效找到的行的一個方法，但是一般數(shù)據(jù)庫也能使用索引找到一個列的數(shù)據(jù)，因此它不必讀取整個行，畢竟索引葉子節(jié)點存儲了他們索引的數(shù)據(jù)；當能通過讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了。一個索引包含了（或覆蓋了）滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引。
注意：1. 如果要使用覆蓋索引，一定要注意 select 列表匯總只取出需要的列，不可 select * ；2. 因為如果將所有字段一起做索引將會導致索引文件過大，查詢性能下降。

4. Using Where

表明使用了 where 過濾

5. using join buffer

使用了鏈接緩存

6. impossible where

explain select * from t1 where 1=2;
復制代碼

where 子句的值總是 false , 不能用來獲取任何元組

7. select tbale optimized away

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

8. distinct

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

舉個例子

例子描述：

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);
復制代碼

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

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

參考資料

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

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

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

執(zhí)行計劃是什么？

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