先看看sql的執(zhí)行過(guò)程

explain的返回列詳解

1、id列

每個(gè)select都有一個(gè)對(duì)應(yīng)的id號(hào)，并且是從1開(kāi)始自增的。一共由四種情況。

（1）id號(hào)相同，從上往下執(zhí)行。

CREATE table users(
	id int PRIMARY KEY,
	user_id int
)
CREATE table orders(
	id int PRIMARY KEY,
	order_id int
)
 
-- create index inx_users_user on users(user_id);
create index inx_orders_order on orders(order_id);
 
explain 
select users.* from users inner join orders on users.id = orders.id;

id號(hào)相同，從上往下執(zhí)行，先users再orders

（2）id號(hào)不同，號(hào)大的先執(zhí)行

EXPLAIN
select id from users where id = (select id from orders where id = 2);

id號(hào)不同，號(hào)大的先執(zhí)行，先orders再u(mài)sers

（3）兩種情況都有————限制性序號(hào)大的，再同級(jí)從上往下執(zhí)行。

set session OPTIMIZER_switch='derived_merge=off';#關(guān)閉5.7對(duì)衍生表合并優(yōu)化
EXPLAIN
select * from (select users.id from users where user_id = 1) temp inner join orders on temp.id = orders.id;
set session OPTIMIZER_switch='derived_merge=on';

先 （2）后（1）。先user之后derived2，之后orders

uses產(chǎn)生衍生表，衍生表顯示derived2（因?yàn)閕d為2的表產(chǎn)生的衍生表）

（4）有null

explain 
select * from users
UNION
select * from orders;

顯示null，最后執(zhí)行null。表示結(jié)果集，并且不需要使用它進(jìn)行查詢(xún)。

2、select_type:表示查詢(xún)語(yǔ)句執(zhí)行的查詢(xún)操作類(lèi)型

（1）simple：簡(jiǎn)單select、連接查詢(xún)。不包括union和子查詢(xún)的select。

explain 
select * from users;
 
explain 
select users.* from users inner join orders on users.id = orders.id;

（2）primary：復(fù)雜查詢(xún)中最外層查詢(xún)，比如使用union或者union all時(shí)，id為1的記錄select_type通常時(shí)primary 

如union語(yǔ)句1——（4）：

explain 
select * from users
UNION
select * from orders;

先執(zhí)行的orders被標(biāo)記為union，users就是最外層的被標(biāo)記為primary。

再如子查詢(xún)1——（3）：

set session OPTIMIZER_switch='derived_merge=off';
EXPLAIN
select * from (select users.id from users where user_id = 1) temp inner join orders on temp.id = orders.id;
set session OPTIMIZER_switch='derived_merge=on';

為什么有兩個(gè)primary，先用users衍生表derived2。外層只是用了連接查詢(xún)，所以外層的兩個(gè)連接表都是primary。

（3）subquery：指在select語(yǔ)句中出現(xiàn)的子查詢(xún)（不在from語(yǔ)句中），并且結(jié)果不依賴(lài)外部查詢(xún)（不相關(guān)子查詢(xún)）

EXPLAIN
select id from users where id = (select id from orders where id = 2);

為什么強(qiáng)調(diào)不在from語(yǔ)句中呢？

• 在from中的子句，可想而知是作為表的，所以大概率時(shí)衍生表類(lèi)型。
• 不在from語(yǔ)句中的不相關(guān)子查詢(xún)，類(lèi)型就是subquery。 

（4）dependent subquery：指在select語(yǔ)句中出現(xiàn)的查詢(xún)語(yǔ)句（不在from語(yǔ)句中），相關(guān)子查詢(xún)

EXPLAIN
select id from users where id = (select orders.id from orders where users.id = orders.id and id = 3);

不在from語(yǔ)句中的相關(guān)子查詢(xún)，類(lèi)型就是dependent subquery。

（5）derived：派生表，在from子句的查詢(xún)語(yǔ)句，表示從為外部數(shù)據(jù)源中推導(dǎo)出來(lái)的，而不是從select語(yǔ)句中的其他列中選擇出來(lái)的。 

set session OPTIMIZER_switch='derived_merge=off';
EXPLAIN
select * from (select users.id from users where user_id = 1) temp inner join orders on temp.id = orders.id;
set session OPTIMIZER_switch='derived_merge=on';

（6） union：分為union和union all兩種，若第二個(gè)select出現(xiàn)在union之后，則被標(biāo)記為union，如果union被from子句的子查詢(xún)包含，那么第一個(gè)select被標(biāo)記為derived。union會(huì)去重，union all不會(huì)去重。

explain 
select * from users
UNION
select * from orders;

3、type列：查詢(xún)所使用的訪問(wèn)類(lèi)型

需要知道的效率從高到低：

system    const     eq_ref     ref      range    index     all

一般來(lái)說(shuō)保證range級(jí)別，最好能達(dá)到ref

（1）system：const類(lèi)型的一種特使場(chǎng)景，查詢(xún)的表只有一行記錄的情況。并且該表使用的存儲(chǔ)引擎的統(tǒng)計(jì)數(shù)據(jù)是精確的。

實(shí)驗(yàn)發(fā)現(xiàn)InnoDB創(chuàng)建表，并且直插入一條數(shù)據(jù)，但是type類(lèi)型是all，這是為什么呢？

create table product(
	id int PRIMARY KEY,
	price VARCHAR(10)
)
 
insert into product values(1,'123');
 
explain 
select * from product;

原因： 就如上述概念所說(shuō)的，表使用的存儲(chǔ)引擎的統(tǒng)計(jì)數(shù)據(jù)是精確的才是system。InnoDB的統(tǒng)計(jì)數(shù)據(jù)不是精準(zhǔn)的，雖然只有一條數(shù)據(jù)但是type是all。

Memory存儲(chǔ)引擎（Hash索引）的統(tǒng)計(jì)數(shù)據(jù)是精確的，所以當(dāng)只有一條數(shù)據(jù)的時(shí)候type是system。

在InnoDB中查找只有一行數(shù)據(jù)的系統(tǒng)表，type類(lèi)型是system。

explain select * from mysql.proxies_priv;

有多行數(shù)據(jù)的系統(tǒng)表

（2）const：where條件是基于主鍵或者唯一索引查看一行，并且連接條件是常量（變量就是不確定的）

EXPLAIN
select * from product where id = 1;

（3）eq_ref：基于主鍵或者唯一索引連接的兩個(gè)表，對(duì)于每個(gè)索引鍵值，只有一條匹配記錄，被驅(qū)動(dòng)表的類(lèi)型為eq_ref

explain 
select * from users inner join orders on users.id = orders.id;

 正如概念所說(shuō)，被驅(qū)動(dòng)表orders是eq_ref類(lèi)型。

問(wèn)題發(fā)現(xiàn)1：驅(qū)動(dòng)表users，類(lèi)型是all，看rows為8，因?yàn)樾枰猽sers的id列的所有值所以全表掃描，但是如果建輔助索引，類(lèi)型就變?yōu)閕ndex（這里index的原因是直接掃描了輔助索引不用回表就可以獲取到全部的主鍵值，這很好理解）。但是這里我產(chǎn)生疑問(wèn)，index和all的區(qū)別到底是什么？

• 我認(rèn)為的index是全表掃描聚簇索引的時(shí)候只掃描主鍵值，不掃描數(shù)據(jù)value。全表掃描不光掃描主鍵值還掃描value。
• 但是 當(dāng)刪除輔助索引，驅(qū)動(dòng)表變?yōu)閍ll，這就證明在掃描聚簇索引的時(shí)候發(fā)生了全表掃描，為什么不只是掃描主鍵值這樣也是index。看來(lái)我的理解有誤，歡迎評(píng)論區(qū)對(duì)我進(jìn)行指導(dǎo)。

問(wèn)題發(fā)現(xiàn)2：概念所說(shuō)的主鍵或者唯一索引連接的兩個(gè)表，那連接條件是驅(qū)動(dòng)表.主鍵=被驅(qū)動(dòng)表.唯一索引列，是否還是eq_ref

答：否

explain 
select * from users inner join orders on users.id = orders.order_id;

我們可以這么理解，因?yàn)槲ㄒ凰饕梢栽试S插入多個(gè)null，所以這樣就違反了概念的只有一條匹配記錄。 

（4）ref：基于非唯一索引連接兩個(gè)表或者通過(guò)二級(jí)索引與常量進(jìn)行等值匹配，可能會(huì)存在多條記錄。

①第一種情況：基于非唯一索引連接的兩個(gè)表

explain 
select * from users inner join orders on users.id = orders.order_id;

上述order_id的唯一索引被刪除了

發(fā)現(xiàn)這和上面的問(wèn)題發(fā)現(xiàn)2，都是相同的，被驅(qū)動(dòng)表不管是唯一索引還是非唯一索引類(lèi)型都是ref（確認(rèn)過(guò)了），這是為什么？我學(xué)習(xí)的視頻和文檔都沒(méi)有給出解釋或者說(shuō)沒(méi)有發(fā)現(xiàn)這個(gè)問(wèn)題，歡迎評(píng)論區(qū)解答。 

②第二種情況：通過(guò)二級(jí)索引（非唯一索引）列進(jìn)行等值匹配

explain 
???????select * from orders where order_id = 11;

實(shí)驗(yàn)發(fā)現(xiàn)order_id如果為非唯一索引的等值查詢(xún)type就是ref，如果是唯一索引的等職查詢(xún)就是const。 

 （5）range：掃描部分索引，比如使用索引獲取某些范圍區(qū)間的記錄。

explain 
select * from orders where id > 3;
 
explain 
select * from orders where order_id > 11;

有些概念說(shuō)的是非唯一索引進(jìn)行掃描部分索引，實(shí)驗(yàn)發(fā)現(xiàn)唯一索引掃描部分索引也會(huì)range。

當(dāng)id > 1為條件時(shí)，系統(tǒng)選擇了全表掃描。待解決。。。。。 

（6）index：掃描整個(gè)索引就能拿到結(jié)果，一般時(shí)二級(jí)索引，這種查詢(xún)一般為使用覆蓋索引（需優(yōu)化，縮小數(shù)據(jù)范圍）

（7）all：掃描整個(gè)表進(jìn)行匹配，即掃描聚簇索引樹(shù)

all和index的區(qū)別：

• all全表掃描，掃描整個(gè)索引樹(shù)。
• index一般發(fā)生在覆蓋索引上，全表掃描整個(gè)輔助索引（二級(jí)索引）

但是index還有一種情況，請(qǐng)看。

explain
???????select id from users;

上述用到了主鍵索引，type類(lèi)型是index，extra是using index意思是使用到了覆蓋索引。

我在這里糾結(jié)了很長(zhǎng)時(shí)間，因?yàn)槲艺J(rèn)為使用二級(jí)索引不回表才是覆蓋索引。

這里我們可以認(rèn)為，主鍵索引是一個(gè)特殊的二級(jí)索引，沒(méi)有回表，查找數(shù)據(jù)就是index覆蓋索引。

問(wèn)題：

explain 
???????select * from users inner join orders on users.id = orders.id;

既然上述使用主鍵索引可以index，為什么驅(qū)動(dòng)表類(lèi)型不可以是index?？赡鼙磉B接有些特殊情況，其中的水很深，我學(xué)到之后會(huì)完善這個(gè)問(wèn)題。 

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論