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

explain的返回列詳解

1、id列

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

（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再users

（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)行查詢。

2、select_type:表示查詢語句執(zhí)行的查詢操作類型

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

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

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

如union語句1——（4）：

explain 
select * from users
UNION
select * from orders;

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

再如子查詢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。外層只是用了連接查詢，所以外層的兩個(gè)連接表都是primary。

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

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

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

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

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

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

不在from語句中的相關(guān)子查詢，類型就是dependent subquery。

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

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子句的子查詢包含，那么第一個(gè)select被標(biāo)記為derived。union會(huì)去重，union all不會(huì)去重。

explain 
select * from users
UNION
select * from orders;

3、type列：查詢所使用的訪問類型

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

system    const     eq_ref     ref      range    index     all

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

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

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

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

原因： 就如上述概念所說的，表使用的存儲(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類型是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)表的類型為eq_ref

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

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

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

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

問題發(fā)現(xiàn)2：概念所說的主鍵或者唯一索引連接的兩個(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è)表或者通過二級(jí)索引與常量進(jìn)行等值匹配，可能會(huì)存在多條記錄。

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

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

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

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

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

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

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

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

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

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

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

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

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

all和index的區(qū)別：

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

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

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

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

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

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

問題：

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

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

總結(jié)

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

最新評(píng)論