MySQL之InnoDB存儲引擎：執(zhí)行計劃之Explain命令
在MySQL中，查詢優(yōu)化器會對用戶提交的SQL語句進行調(diào)整優(yōu)化并最終生成執(zhí)行計劃。具體地，可通過Explain命令來進行查看
基本使用
關(guān)于Explain命令的使用方法，在前面已經(jīng)多次出現(xiàn)了。直接在我們的SQL語句前添加explain，即可查看該SQL語句的執(zhí)行計劃。下面即是一個查看select語句執(zhí)行計劃的示例。事實上，Explain命令亦可用于delete、insert、update等其它類型的SQL語句上，只不過日常我們更關(guān)心查詢語句的執(zhí)行計劃
字段釋義
上面，我們知道了如何查看執(zhí)行計劃。現(xiàn)在我們來了解下查詢語句的執(zhí)行計劃中各字段的含義
id字段
對于查詢語句而言，在執(zhí)行計劃中其會對每一個select關(guān)鍵字分配一個唯一的id值
單表查詢
例如下面是一個單表查詢的select語句
explain select * from access_method4;
由于是對單表的查詢，所以執(zhí)行計劃中只有一條記錄，符合預(yù)期
連接查詢
對于下面的連接查詢而言，其涉及到對兩張表的查詢
explain
select * from access_method3 left join testbyaaron.access_method4
on access_method3.id = access_method4.id;
從執(zhí)行計劃的結(jié)果中可以看出，雖然有兩條記錄。但是由于只含一個select關(guān)鍵字，故兩條記錄均使用同一個id值
Note
對于連接查詢的執(zhí)行計劃而言，前面的記錄用于表示驅(qū)動表，后面的記錄用于表示被驅(qū)動表
子查詢
對于一個SQL查詢語句而言，其可能會有不止一個select關(guān)鍵字。典型地，有所謂的子查詢
explain
select * from access_method3
where id = (
select access_method4.id from access_method4
where access_method3.name = access_method4.name );
從下圖的執(zhí)行計劃中可以看出，由于存在兩個select關(guān)鍵字，故兩條記錄的id值不一樣
值得一提的是對于子查詢而言，查詢優(yōu)化器有時候會將其優(yōu)化為連接查詢(準確地說，是semi-join半連接)。這樣我們在查看它的執(zhí)行計劃時即會發(fā)現(xiàn)各記錄的id值是一樣的
Union聯(lián)合查詢
在Union聯(lián)合查詢中，其也會存在多個select關(guān)鍵字。例如下面的SQL語句
explain
select * from access_method3 where id = 2
union all
select * from access_method3 where id = 3;
結(jié)果符合預(yù)期，兩條記錄的id值不一樣
現(xiàn)在我們再來看另外一個聯(lián)合查詢。與上面SQL不同的是，由于其沒有使用all關(guān)鍵字，故該聯(lián)合查詢需要對重復(fù)的記錄進行去重
explain
select * from access_method3 where id = 2
union
select * from access_method3 where id = 3;
現(xiàn)在，我們來看看該聯(lián)合查詢的執(zhí)行計劃。可以看出，其多了一條記錄且id值為NULL。原因在于MySQL為了對兩條select命令的結(jié)果進行去重合并，需要建立一個名為 <union1, 2>的臨時表。所以這第三條記錄的id值為NULL
select_type字段
從上面的測試結(jié)果可以看到，執(zhí)行計劃中會生成各數(shù)據(jù)表相應(yīng)的記錄。而select_type字段即表示對數(shù)據(jù)表進行select操作的類型。下面介紹幾種常見的類型
SIMPLE
簡單select，即未使用UNION或子查詢。比如對于單表查詢、連接查詢，其就是SIMPLE類型
PRIMARY
對于含聯(lián)合查詢或子查詢的SQL語句而言，其會有多個select關(guān)鍵字。則SQL語句中第一個(即最左邊)的select類型就是PRIMARY
UNION
對于聯(lián)合查詢而言，除了SQL語句中第一個select類型為PRIMARY，SQL語句中的其余select類型即為UNION
UNION RESULT
對于聯(lián)合查詢而言，如果需要對聯(lián)合查詢的結(jié)果集進行去重，則其對臨時表的select類型即為UNION RESULT
SUBQUERY
對于子查詢而言，如果查詢優(yōu)化器無法將其優(yōu)化為semi-join半連接查詢，且該子查詢?yōu)?strong style="color: rgb(119, 48, 152);">不相關(guān)子查詢。則該子查詢中第一個select的類型即為SUBQUERY
DEPENDENT SUBQUERY
對于子查詢而言，如果查詢優(yōu)化器無法將其優(yōu)化為semi-join半連接查詢，且該子查詢?yōu)?strong style="color: rgb(119, 48, 152);">相關(guān)子查詢。則該子查詢中第一個select類型即為DEPENDENT SUBQUERY
DEPENDENT UNION
對于一個通過union連接、內(nèi)部含有多個select的子查詢而言，且子查詢內(nèi)部各select語句均與外層查詢相關(guān)時，則該子查詢除了第一個select類型為DEPENDENT SUBQUERY，其余select類型均為DEPENDENT UNION。SQL語句示例如下所示
explain
select * from access_method3
where id = (
-- 該子查詢內(nèi)部通過union連接2個select關(guān)鍵字
select access_method4.id from access_method4
where access_method3.name = access_method4.name and access_method4.id>3
union all
select access_method4.id from access_method4
where access_method3.name = access_method4.name and access_method4.id=1 );
執(zhí)行計劃如下所示，符合預(yù)期
DERIVED
對于采用物化的方式執(zhí)行的包含派生表的查詢，該派生表對應(yīng)的子查詢的select類型即為DERIVED。示例SQL如下所示
explain
select *
from (select count(*) as count from access_method3) as t1
where count>996;
執(zhí)行計劃如下所示，符合預(yù)期
table字段
該字段即是執(zhí)行查詢時所作用的數(shù)據(jù)表的表名
type字段
關(guān)于type字段，其表示的就是進行查詢時所使用的訪問方法。之前我們已經(jīng)介紹MySQL進行單表查詢時一些常用的Access Method訪問方法了。這里我們再對其他常見的訪問方法進行補充說明
system
前面我們提到MySQL會對表中的記錄數(shù)進行統(tǒng)計。在表中只有一條記錄的情況下，此時查詢的訪問方法即為system。需要注意其有一個前提條件，即要求存儲引擎對表中記錄數(shù)的統(tǒng)計必須是準確值，而不能是估計值。換言之，該訪問方法對InnoDB存儲引擎不適用，適用于MyISAM、Memory等存儲引擎。下面的SQL語句，即建立了一個基于MyISAM的數(shù)據(jù)表并向其中插入一條記錄
-- 創(chuàng)建基于MyISAM的數(shù)據(jù)表
create table t1 (
name varchar(255),
age int,
primary key (name)
) Engine=MyISAM;
-- 插入一條記錄
insert into t1(name, age) values ('Aaron', 25);
-- 查看查詢SQL的執(zhí)行計劃
explain select * from t1;
結(jié)果如下，符合預(yù)期
fulltext
使用全文索引
unique_subquery
有時候查詢優(yōu)化器會對in子查詢進行優(yōu)化進而轉(zhuǎn)換為exists子查詢，例如下面的SQL使用了in子查詢，其中access_method3的主鍵為id字段
explain
select * from access_method4
where access_method4.name in (
select access_method3.id from access_method3
where access_method3.iphone_number=110
)
or access_method4.type='staff';
現(xiàn)在我們將其轉(zhuǎn)換為exists子查詢，可以看到現(xiàn)在對于exists子查詢而言，可以利用access_method3表的主鍵(聚簇索引)進行等值查詢。這也是為啥查詢優(yōu)化器要進行如此優(yōu)化的原因所在
explain
select * from access_method4
where exists(
select 1 from access_method3
where access_method3.iphone_number=110
and access_method3.id = access_method4.name )
or access_method4.type='staff';
故當將查詢優(yōu)化器將in子查詢優(yōu)化為exists子查詢時，如果子查詢可以利用主鍵索引進行等值查詢，則該子查詢的訪問方法即為unique_subquery。現(xiàn)在我們查看上文in子查詢的執(zhí)行計劃
index_subquery
其與unique_subquery類似，只不過其是在查詢優(yōu)化器將in子查詢優(yōu)化為exists子查詢的條件下，子查詢可以利用二級索引進行等值查詢時的訪問方法。例如下面的SQL語句，其中對access_method3表的name字段建立了一個二級索引
explain
select * from access_method4
where access_method4.name in (
select access_method3.name from access_method3
where access_method4.type = access_method3.country
)
or access_method4.type='staff';
執(zhí)行結(jié)果如下，符合預(yù)期
possible_keys字段
故名思義，該字段只是列出該查詢可以利用的索引。需要注意的是該字段所列出的索引越多，則查詢優(yōu)化器在計算、比較各查詢方案成本時所需的時間也就越多
key字段
前面我們提了possible_keys字段只是給出在查詢過程可以利用的索引，而實際真正地使用哪些索引進行查詢則體現(xiàn)在key字段中
key_len字段
該字段意為當使用索引進行查詢時，該索引的最大長度字節(jié)數(shù)。具體地：
對于固定長度類型的索引字段而言，則其占用的最大長度就是該固定值 對于指定字符集的變長類型的索引字段而言，例如對utf8字符集下的varchar(10)而言，則其所占用的最大長度為3*10=30個字節(jié) 對于變長類型的索引字段而言，其還需占用2個字節(jié)用于存儲變長類型的實際長度 若該索引字段允許NULL值，則其key_len值比不允許NULL值時多1個字節(jié)
說了這么多，那這個字段到底有什么用呢？為此，我們先來建立一張表并插入一些數(shù)據(jù)。其中該數(shù)據(jù)表使用utf8字符集
create table user_info(
id int auto_increment,
name varchar(10) null,
sex varchar(10) null,
age int null,
primary key (id),
index index1(name, sex, age)
);
好了，現(xiàn)在我們查看下面的SQL查詢語句的執(zhí)行計劃
explain
select name from user_info
where name='aaron';
執(zhí)行計劃中的key_len值為33,為什么呢？其實也很好理解。首先對于上面的SQL語句而言，如果使用index1索引，最多也只會用到name索引字段，則此時該索引記錄的最大長度即為10*3 + 2(變長類型) + 1(允許為NULL) = 33
如果我們同時使用index1索引中的name、sex索引字段是什么效果呢
explain
select name from user_info
where name='aaron' and sex='man';
可以看到此時該key_len值變?yōu)?6了。由于name字段與sex字段的類型在數(shù)據(jù)表的定義中完全一致，故此處就不再具體解釋這個66是怎么計算出來的，可以參照上文對name字段的計算過程
至此，相信大家應(yīng)該知道key_len字段的用處了。通過該字段即可讓用戶知道，在通過索引查詢時到底使用了聯(lián)合索引的幾個索引字段
ref字段
該字段表示索引字段進行等值查詢過程中"值"的類型。例如對于下面的SQL查詢語句而言
explain
select name from user_info
where name='aaron';
這里對索引index1的name索引字段進行了等值查詢，其中值為'aaron'。即是一個常數(shù)。從執(zhí)行計劃中我們可以看到其ref字段為const，即常數(shù)
此外該字段還可以是某個列的列名。例如對于下面的SQL語句而言，在對被驅(qū)動表user_info進行單表查詢時，是以tagList表中記錄的tag字段作為等值查詢時的值
explain
select * from tagList left join user_info
on user_info.name=tagList.tag;
則從執(zhí)行計劃中我們可以看到，結(jié)果符合預(yù)期
此外該字段的值還可以是func，即等值匹配的類型是一個函數(shù)。SQL實例如下所示
explain
select * from tagList left join user_info
on user_info.name = lower( tagList.tag );
執(zhí)行計劃結(jié)果如下，符合預(yù)期
rows字段
該字段表示預(yù)計查詢需要掃描的記錄數(shù)(行數(shù))。具體地，當采用全表掃描的方式進行查詢時，其指的就是數(shù)據(jù)記錄的行數(shù)；當通過索引進行查詢時，其指的是索引記錄的行數(shù)
filtered字段
該字段是一個以百分比%為單位的估計值，其表示的是 通過查詢條件過濾后符合要求的記錄數(shù) 占 預(yù)計掃描的記錄數(shù)(即row字段值) 的百分比。換言之，預(yù)計結(jié)果記錄數(shù)量為 filtered值 / 100 * rows值
這里向user_info數(shù)據(jù)表插入一些數(shù)據(jù)，具體結(jié)果如下。其中name字段上建立了一個二級索引index1
示例SQL語句如下
explain
select name from user_info
where name='aaron' and id>5;
從執(zhí)行計劃中我們可以看出，符合上述查詢要求的記錄數(shù)的估計值為 6 * 28.57% = 1.7142，通過觀察我們可以看到真正滿足查詢要求的記錄數(shù)是2條。這也進一步執(zhí)行計劃中的結(jié)果只是一個估計值，而非精確值
Extra字段
該字段是對執(zhí)行計劃信息的補充和完善，這里就一些常見的補充信息進行說明
No tables used
沒有數(shù)據(jù)表，即未使用from子句。示例SQL如下所示
explain select 1;
結(jié)果如下，符合預(yù)期
Impossible WHERE
where子句中條件恒為false。示例SQL如下所示
explain
select * from user_info
where 1=3;
結(jié)果如下，符合預(yù)期
Zero limit
limit子句的參數(shù)為0，即查詢語句返回的記錄數(shù)為0條。示例SQL如下所示
explain select * from user_info limit 0;
結(jié)果如下，符合預(yù)期
Using index
出現(xiàn)索引覆蓋，即不需要進行回表操作。為了方便演示，這里先建立一張表，并在name、age字段上建立了一個聯(lián)合索引index1
create table staff(
id int auto_increment,
name varchar(10) null,
age int null,
salary double null,
primary key (id),
index index1(name, age)
);
現(xiàn)在我們就來寫一個發(fā)生索引覆蓋情況的SQL，示例如下
explain
select age from staff
where name = 'aaron';
執(zhí)行計劃如下所示，符合預(yù)期
相應(yīng)的，如果我們的索引index1不是一個覆蓋索引時，既不會存在該補充信息，示例SQL如下
explain
select salary from staff
where name = 'aaron';
從下面的執(zhí)行計劃可以看到Extra為NULL，符合預(yù)期
Using index condition
對于利用二級索引進行查詢的過程我們已經(jīng)很熟悉了，基本是先利用 索引字段的查詢條件 獲取 二級索引記錄，然后再通過回表操作獲取完整記錄。那問題來了，對于存在一些無法使用索引的索引字段的查詢條件。該何時使用呢？具體地，即是在獲得二級索引的記錄后使用該查詢條件進行過濾，還是在回表操作后獲取到完整的記錄時使用該查詢條件進行過濾呢？在MySQL 5.6之前，對于該查詢條件的使用時機是在回表后獲取到完整記錄的時候。而在MySQL 5.6中引入了 Index Condition Pushdown(ICP, 索引條件下推) 特性，該優(yōu)化將 索引字段的查詢條件 的使用時機放在獲取到二級索引的記錄時，此舉大大減少了不必要的回表操作。故如果某個查詢語句使用了ICP特性，這即會出現(xiàn)補充信息Using index condition
這么說可能有點抽象，下面通過具體的實例作進一步的解釋。其中，name字段上存在一個二級索引index1。可以看到對于查詢條件 name>'aa' 而言，是可以利用到索引index1。但對于查詢條件 name like '%ron'，由于是前綴匹配，故該索引字段的查詢條件無法使用索引。但MySQL可以先利用查詢條件name > 'aa'在索引index1的B+樹中獲取符合條件的記錄，然后再對這些二級索引的記錄進一步使用 name like '%ron' 條件進行過濾，最后再利用二級索引記錄中主鍵進行回表獲取完整的用戶記錄
explain
select * from staff
where name > 'aa' and name like '%ron';
執(zhí)行計劃結(jié)果如下所示，符合預(yù)期
Using where
在全表掃描時或二級索引回表后，使用where子句中的查詢條件進行過濾。SQL示例如下所示，其中name字段上建有二級索引index1
explain
select * from staff
where name='bob' and salary>123;
執(zhí)行計劃的結(jié)果如下，符合預(yù)期
Not exists
對于外連接查詢而言，如果在where子句要求被驅(qū)動表中某個不允許為NULL字段的值為NULL。則會在被驅(qū)動表執(zhí)行計劃的Extra字段中出現(xiàn)Not exists信息。因為在此種場景下，驅(qū)動表中的記錄只有在被驅(qū)動表中無法匹配到記錄時，才會被加入到最終的結(jié)果集。換言之，對于驅(qū)動表中的某條記錄A1來說，如果其通過on子句在被驅(qū)動表中找到一條匹配的記錄后，即可提前停止對A1記錄的匹配。因為A1記錄肯定不會被加入到最終的結(jié)果集中。顯然通過提前停止匹配的操作，大大減少不必要的性能浪費
對于下面的左外連接查詢SQL語句而言，除staff表的id字段不允許為NULL外，其余字段均可為NULL
explain
select * from user_info left join staff
on user_info.age = staff.age
where staff.id is null;
執(zhí)行計劃的結(jié)果如下，符合預(yù)期
Using intersect、Using union、Using sort_union
我們在介紹單表查詢的Access Method訪問方法時，介紹過一種同時使用多個索引的訪問方法——index merge。在該訪問方法下，具體有Intersection、Union、Sort-Union等算法。故當執(zhí)行計劃的type字段值為index merge時，Extra字段即會給出具體算法的補充信息。對應(yīng)于上面提到三種不同算法，分別為Using intersect、Using union、Using sort_union。并在后面的括號中會給出涉及到的索引名稱
這里以Using intersect為例進行介紹，首先建立如下的數(shù)據(jù)表并插入一些數(shù)據(jù)
create table access_method4 (
id int auto_increment,
recv_time datetime null,
create_time varchar(255) null,
name varchar(255) null,
PRIMARY KEY (id),
index index1(create_time),
index index2(recv_time)
);
則SQL查詢的示例如下所示
explain
select * from access_method4
where create_time='2019-03-26 23:59:35.492' -- 對index1索引中所有字段進行等值查詢
and recv_time='2019-03-26 23:59:32'; -- 對index2索引中所有字段等進行值查詢
則執(zhí)行計劃的結(jié)果如下所示
Using filesort
眾所周知，可以利用索引直接實現(xiàn)某些排序需求。例如對于下面的SQL而言，其在name、age字段上建立了一個聯(lián)合索引
explain
select * from staff
where name = 'a'
order by age;
則其執(zhí)行計劃如下所示，Extra字段值空空如也
但大多數(shù)時候我們無法利用索引直接實現(xiàn)有序，而只能在內(nèi)存或磁盤中進行排序。前者內(nèi)存中適用于記錄較少時，后者磁盤中適用于記錄較多時。不論是在內(nèi)存中還是在硬盤中實現(xiàn)排序，在MySQL下均被稱作file sort文件排序。例如對于下面的SQL語句而言，即無法通過索引直接實現(xiàn)排序，而只能通過file sort排序?qū)崿F(xiàn)
explain
select * from staff
where name = 'a'
order by salary;
執(zhí)行計劃結(jié)果如下，符合預(yù)期
Using temporary
當MySQL進行去重、排序、分組等操作時，可能需要在內(nèi)部建立臨時表來完成。值得一提的是，由于建立、維護臨時表的成本并不低，必要時可以通過索引來避免MySQL使用臨時表。示例SQL如下所示
explain select distinct salary from staff;
執(zhí)行計劃結(jié)果如下所示
參考文獻
MySQL是怎樣運行的