還不會看MySQL的EXPLAIN執行計劃？這篇文章能幫到你

我們在進行SQL優化的時候，主要是看where後面的字段有沒有用到索引。如何看這個查詢有沒有用到索引，那就看Explain執行計劃了。

關于索引相關的知識可以看看這篇文章：

“

👉

詳解

下面我将解釋每個字段的含義。

Column	Meaning
id	查詢id，可了解為查詢的順序
select_type	對應查詢的類型
table	查詢的表名
partitions	比對的分區資訊
type	通路類型，這個比較重要
possible_keys	可能用到的索引
key	實際用到的索引
key_len	實際使用到的索引的長度
ref	與索引進行等值比對的資訊
rows	預計要讀取的行數
filtered	條件過濾後的剩餘記錄百分比
extra	額外資訊

id

id的值為數字，表示查詢中執行select子句或者操作表的順序。

關于id有下列3種情況：

1.如果id值相同，執行順序為從上至下

explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;
           

2.如果id值不同，值越大執行的優先級越高

explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');
           

select_type

用于分辨查詢類型，比如普通查詢，連表查詢等。官網的解釋如下：

select_type  Value	Meaning
SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	Result of a UNION.
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DERIVED	Derived table
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

上面的解釋你肯定看的雲裡霧裡的，我們實際來寫SQL看看每種出現的情景。

SIMPLE：簡單的查詢，不包含子查詢和union

explain select * from emp;
           

PRIMARY：查詢中若包含任何複雜的子查詢，最外層查詢則被标記為PRIMARY

EXPLAIN SELECT * FROM member m WHERE m.`code` =
(SELECT m2.`code` FROM member m2 WHERE m2.`code` < (SELECT m1.`code` FROM member m1 ORDER BY m1.`code` DESC LIMIT 1) ORDER BY m2.`code` DESC LIMIT 1);
           

UNION：若第二個SELECT出現在UNION之後，則被标記為UNION

explain select * from emp where deptno = 10 union select * from emp where sal >2000;
           

DEPENDENT UNION：跟UNION類似，此處的DEPENDENT 表示UNION或UNION ALL聯合而成的結果會受外部表影響

explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)
           

UNION RESULT：從UNION表擷取結果的SELECT

explain select * from emp where deptno = 10 union select * from emp where sal >2000;
           

SUBQUERY：在SELECT或者WHERE清單中包含子查詢

explain select * from emp where sal > (select avg(sal) from emp);
           

DEPENDENT SUBQUERY：SUBQUERY的子查詢要受到外部表查詢的影響

explain select * from emp e where e.deptno in (select distinct deptno from dept d WHERE d.deptno = e.deptno) OR e.deptno = '30';
           

DERIVED: from子句中出現的子查詢，也叫做派生類

EXPLAIN SELECT * FROM (SELECT deptno, count(*) as c FROM emp GROUP BY deptno) AS derived_s1 where c > '5';
           

UNCACHEABLE SUBQUERY：表示使用子查詢的結果不能被緩存

explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
           

UNCACHEABLE UNION：表示union的查詢結果不能被緩存

沒有寫出可驗證的SQL😭。

table

對應行正在通路哪一個表，表名或者别名，可能是臨時表或者union合并結果集

1. 如果是具體的表名，則表明從實際的實體表中擷取資料，當然也可以是表的别名
2. 表名是derivedN的形式，表示使用了id為N的查詢産生的衍生表
3. 當有union result的時候，表名是union n1、n2等的形式，n1、n2表示參與union的id

type

表示通路類型，每種類型産生的查詢效率不一樣，效率從高到低依次為：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

效率最低的為ALL，表示全表掃描了。我們在sql優化時主要看type，并且可以按這個順序優化。下面我将列舉出每個場景的sql。

ALL：全表掃描，一般情況下出現這樣的sql語句而且資料量比較大的話那麼就需要進行優化。

explain select * from emp;
           

index：全索引掃描這個比all的效率要好，主要有兩種情況，一種是目前的查詢時覆寫索引，即我們需要的資料在索引中就可以索取，或者是使用了索引進行排序，這樣就避免資料的重排序。

EXPLAIN SELECT * from member ORDER BY id;
EXPLAIN SELECT code from member;
           

range：表示利用索引查詢的時候限制了範圍，在指定範圍内進行查詢，這樣避免了index的全索引掃描，适用的操作符：=、<>、 >、>=、<、<=、IS NULL、BETWEEN、LIKE、or、IN()。

EXPLAIN SELECT id from  member WHERE CODE = 99 OR CODE = 100;
           

index_subquery：利用索引來關聯子查詢，不再掃描全表。

沒有寫出可驗證的SQL😭。

unique_subquery：該連接配接類型類似與index_subquery，使用的是唯一索引:該連接配接類型類似與index_subquery,使用的是唯一索引。

沒有寫出可驗證的SQL😭。

index_merge：在查詢過程中需要多個索引組合使用。

沒有寫出可驗證的SQL😭。

“

以上3種都隻模拟出index類型的。

”

ref_or_null：對于某個字段即需要關聯條件，也需要null值的情況下，查詢優化器會選擇這種通路方式。

EXPLAIN SELECT * from member WHERE code is NULL or code = '13';
           

ref：使用了非唯一性索引進行資料的查找。

EXPLAIN SELECT * from member WHERE code = '99';
           

eq_ref ：使用唯一性索引進行資料查找。

explain select * from emp,bonus where emp.empno = bonus.empno;
           

const：這個表至多有一個比對行。常見的通過主鍵索引擷取一條資料type為const。

EXPLAIN SELECT * from member WHERE id = '1';
           

system：表隻有一行記錄（等于系統表），這是const類型的特例，平時不會出現。

possible_keys

表示目前查詢可能會用到的索引，實際會根據優化器有所改變。

EXPLAIN SELECT code from member;
           

key

實際使用到的索引。

key_len

表示索引的長度，一般越短越好。

ref

顯示索引的哪一列被使用了，如果可能的話，是一個常數。複雜的查詢可能不是常數。

explain select * from emp where emp.job in (select job from bonus);
           

rows

一個很重要的參數，預計找出所需記錄需要讀取的行數，一般越少越好。

filtered

在

rows

一樣的情況下，

filtered

越大，扇出值越小，效率可能也越高。

Extra

額外資訊。常見的幾種類型如下：

using filesort：說明mysql無法利用索引進行排序，隻能利用排序算法進行排序，會消耗額外的位置。

explain select * from emp order by sal;
           

using temporary：建立臨時表來儲存中間結果，查詢完成之後把臨時表删除。

explain select ename,count(*) from emp where deptno = 10 group by ename;
           

using index：這個表示目前的查詢是覆寫索引的，直接從索引中讀取資料，而不用通路資料表。如果同時出現using where 表名索引被用來執行索引鍵值的查找，如果沒有，表面索引被用來讀取資料，而不是真的查找。

EXPLAIN SELECT id from  member WHERE CODE in (99, 100);
           

using where：使用where進行條件過濾。

見上面的例子。

好了關于Explain的字段介紹以及出現場景就介紹到這裡啦，有沒有收獲呢。

福利

文中用的member表

CREATE TABLE `member` (
  `id` varchar(255) CHARACTER SET utf8mb4 NOT NULL COMMENT '主鍵id',
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '姓名',
  `code` int(11) DEFAULT NULL COMMENT '編号',
  `create_date` date DEFAULT NULL COMMENT '建立時間',
  UNIQUE KEY `idx_id` (`id`) USING BTREE,
  KEY `idx_code_name` (`code`,`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成員表';

           

其餘的表背景回複【sql】即可擷取建表檔案sql和sql資料。

往期推薦

• 我寫出這樣幹淨的代碼，老闆直誇我
• 雲南麗江旅遊攻略
• 使用ThreadLocal怕記憶體洩漏？
• Java進階之路思維導圖
• 程式員必看書籍推薦
• 3萬字的Java後端面試總結（附PDF）

掃碼二維碼，擷取更多精彩。或微信搜Lvshen_9，可背景回複擷取資料

1.回複"java" 擷取java電子書；

2.回複"python"擷取python電子書；

3.回複"算法"擷取算法電子書；

4.回複"大資料"擷取大資料電子書；

5.回複"spring"擷取SpringBoot的學習視訊。

6.回複"面試"擷取一線大廠面試資料

7.回複"進階之路"擷取Java進階之路的思維導圖

8.回複"手冊"擷取阿裡巴巴Java開發手冊(嵩山終極版)

9.回複"總結"擷取Java後端面試經驗總結PDF版

10.回複"Redis"擷取Redis指令手冊，和Redis專項面試習題（PDF）

11.回複"并發導圖"擷取Java并發程式設計思維導圖（xmind終極版）
           

另：點選【我的福利】有更多驚喜哦。