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

explain的返回列詳解

1、id列

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

（1）id號相同，從上往下執(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號相同，從上往下執(zhí)行，先users再orders

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

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

id號不同，號大的先執(zhí)行，先orders再users

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

set session OPTIMIZER_switch='derived_merge=off';#關(guān)閉5.7對衍生表合并優(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（因為id為2的表產(chǎn)生的衍生表）

（4）有null

explain 
select * from users
UNION
select * from orders;

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

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

（1）simple：簡單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時，id為1的記錄select_type通常時primary 

如union語句1——（4）：

explain 
select * from users
UNION
select * from orders;

先執(zhí)行的orders被標記為union，users就是最外層的被標記為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';

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

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

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

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

• 在from中的子句，可想而知是作為表的，所以大概率時衍生表類型。
• 不在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兩種，若第二個select出現(xiàn)在union之后，則被標記為union，如果union被from子句的子查詢包含，那么第一個select被標記為derived。union會去重，union all不會去重。

explain 
select * from users
UNION
select * from orders;

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

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

system    const     eq_ref     ref      range    index     all

一般來說保證range級別，最好能達到ref

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

實驗發(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;

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

Memory存儲引擎（Hash索引）的統(tǒng)計數(shù)據(jù)是精確的，所以當只有一條數(shù)據(jù)的時候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：基于主鍵或者唯一索引連接的兩個表，對于每個索引鍵值，只有一條匹配記錄，被驅(qū)動表的類型為eq_ref

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

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

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

• 我認為的index是全表掃描聚簇索引的時候只掃描主鍵值，不掃描數(shù)據(jù)value。全表掃描不光掃描主鍵值還掃描value。
• 但是 當刪除輔助索引，驅(qū)動表變?yōu)閍ll，這就證明在掃描聚簇索引的時候發(fā)生了全表掃描，為什么不只是掃描主鍵值這樣也是index?？磥砦业睦斫庥姓`，歡迎評論區(qū)對我進行指導(dǎo)。

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

答：否

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

我們可以這么理解，因為唯一索引可以允許插入多個null，所以這樣就違反了概念的只有一條匹配記錄。 

（4）ref：基于非唯一索引連接兩個表或者通過二級索引與常量進行等值匹配，可能會存在多條記錄。

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

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

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

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

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

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

實驗發(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;

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

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

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

（7）all：掃描整個表進行匹配，即掃描聚簇索引樹

all和index的區(qū)別：

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

但是index還有一種情況，請看。

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

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

我在這里糾結(jié)了很長時間，因為我認為使用二級索引不回表才是覆蓋索引。

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

問題：

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

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

總結(jié)

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

最新評論