來自靈魂的拷問——知道什麼是SQL執行計劃嗎？

牧小農的夏天發表於2020-08-07

在這裡插入圖片描述

面試官說：工作這麼久了，應該知道sql執行計劃吧，講講Sql的執行計劃吧！
看了看面試官手臂上紋的大花臂和一串看不懂的韓文，吞了吞口水，暗示自己鎮定點，整理了一下思緒緩緩的對面試官說：我不會
面試官：。。。。，回去等通知吧
我：%^&%$!@#

一、前言

當我們工作到了一定的年限之後，一些應該掌握的知識點，我們是必須需要去了解的，比如今天面試官問的SQL執行計劃
當我們執行一條SQL的時候，可以直接對應的結果，但是你並不曉得，它會經歷多深遠黑暗的隧道，通過聯結器、查詢快取、分析器、優化器、執行器重重篩選，才有可能展示到我們面前，有時候當你等待N長時間，但是展現的卻是 timeout，這個時候想砸電腦的心都有了，不過當你看了今天的SQL執行計劃後，你再也不用砸電腦了，看懂了這篇文章你就會知道這都不是事，讓我們一起來揭曉這裡面的奧妙

在實際的應用場景中，為了知道優化SQL語句的執行，需要檢視SQL語句的具體執行過程，以加快SQL語句的執行效率。
通常會使用explain+SQL語句來模擬優化器執行SQL查詢語句，從而知道mysql是如何處理sql語句的。

官網地址： https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

首先我們來下面的一條sql語句，其中會有id、select_type 、table 等等這些列，這些就是我們執行計劃中所包含的資訊，我們要弄明白的就是這些列是用來幹嘛的，以及每個列可能存在多少個值。
explain select * from emp;
在這裡插入圖片描述

二、執行計劃中包含的資訊

列(Column)	含義(Meaning)
id	The `SELECT` identifier（每個select子句的標識id）
select_type	The `SELECT` type（select語句的型別）
table	The table for the output row（當前表名）
partitions	The matching partitions （顯示查詢將訪問的分割槽，如果你的查詢是基於分割槽表）
type	The join type（當前表內訪問方式）
possible_keys	The possible indexes to choose（可能使用到的索引）
key	The index actually chosen（經過優化器評估最終使用的索引）
key_len	The length of the chosen key （使用到的索引長度）
ref	The columns compared to the index（引用到的上一個表的列）
rows	Estimate of rows to be examined （要得到最終記錄索要掃描經過的記錄數）
filtered	Percentage of rows filtered by table condition（儲存引擎返回的資料在server層過濾後，剩下滿足查詢的記錄數量的比例）
extra	Additional information （額外的資訊說明）

貼心的小農已經把sql複製出來了，只需要放到資料庫中執行即可，方便簡單快捷 

														 ——牧小農

建表語句：

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `DEPTNO` int NOT NULL,
  `DNAME` varchar(14) DEFAULT NULL,
  `LOC` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` int NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int DEFAULT NULL,
  PRIMARY KEY (`EMPNO`),
  KEY `idx_job` (`JOB`),
  KEY `jdx_mgr` (`MGR`),
  KEY `jdx_3` (`DEPTNO`),
  KEY `idx_3` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-02-02', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-01-05', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-09-06', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');

DROP TABLE IF EXISTS `emp2`;
CREATE TABLE `emp2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `empno` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `emp2` VALUES ('1', '111');
INSERT INTO `emp2` VALUES ('2', '222');

DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `GRADE` int NOT NULL,
  `LOSAL` double DEFAULT NULL,
  `HISAL` double DEFAULT NULL,
  PRIMARY KEY (`GRADE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `salgrade` VALUES ('1', '700', '1200');
INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
INSERT INTO `salgrade` VALUES ('5', '3001', '9999');

DROP TABLE IF EXISTS `t_job`;
CREATE TABLE `t_job` (
  `id` int NOT NULL AUTO_INCREMENT,
  `job` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `j` (`job`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.1 id

select查詢的序列號，包含一組數字，表示查詢中執行select子句或者操作表的順序

id號分為三種情況：

 1、如果id相同，那麼執行順序從上到下

-- 左關聯
explain select * from emp e left  join dept d on e.deptno = d.deptno;
-- 右關聯
explain select * from emp e right join dept d on e.deptno = d.deptno;

通過left join 和 right join 驗證；id一樣（注意執行計劃的table列），left join 先掃描e表，再掃描d表；right join 先掃描d表，再掃描e表
在這裡插入圖片描述

 2、如果id不同，如果是子查詢，id的序號會遞增，id值越大優先順序越高，越先被執行

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

在下面的表中回先查詢 id 為 2的資料也就是我們的 d表（注意：我們可以看到d表中select_type為 SUBQUERY 也就是子查詢的意思），然後根據d表中的deptno去查詢 e表中的資料
在這裡插入圖片描述

3、id相同和不同的，同時存在：相同的可以認為是一組，從上往下順序執行，在所有組中，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 where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');

在這裡先從id為2的執行，如果id是一樣的，就按照順序執行
在這裡插入圖片描述

2.2 select_type

主要用來分辨查詢的型別，是普通查詢還是聯合查詢還是子查詢

select_type 值	含義(Meaning）
SIMPLE	簡單的查詢不包含 UNION 和 subqueries
PRIMARY	查詢中若包含任何複雜的子查詢，最外層查詢則被標記為Primary
UNION	若第二個select出現在union之後，則被標記為union
DEPENDENT UNION	跟union類似，此處的depentent表示union或union all聯合而成的結果會受外部表影響
UNION RESULT	從union表獲取結果的select
SUBQUERY	在select或者where列表中包含子查詢
DEPENDENT SUBQUERY	subquery的子查詢要受到外部表查詢的影響
DERIVED	from子句中出現的子查詢，也叫做派生類
UNCACHEABLE SUBQUERY	表示使用子查詢的結果不能被快取
UNCACHEABLE UNION	表示union的查詢結果不能被快取

--simple:簡單的查詢，不包含子查詢和union
explain select * from emp;

在這裡插入圖片描述

--primary:查詢中若包含任何複雜的子查詢，最外層查詢則被標記為Primary
explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');

在這裡插入圖片描述

--union:若第二個select出現在union之後，則被標記為union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

在這裡插入圖片描述

--dependent union:跟union類似，此處的depentent表示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 = (select distinct deptno from dept where deptno = e.deptno);

在這裡插入圖片描述

--DERIVED: from子句中出現的子查詢，也叫做派生類，
explain select * from (select ename staname,mgr from emp where ename = 'W' union select ename,mgr from emp where ename = 'E') a;

在這裡插入圖片描述

-- UNCACHEABLE SUBQUERY：表示使用子查詢的結果不能被快取
 explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);

在這裡插入圖片描述

--uncacheable union:表示union的查詢結果不能被快取
explain select * from emp where exists (select 1 from dept where  emp.deptno = dept.deptno union select 1 from dept where  deptno = 10);

在這裡插入圖片描述

2.3 table

對應行正在訪問哪一個表，表名或者別名，可能是臨時表或者union合併結果集
1、如果是具體的表名，則表明從實際的物理表中獲取資料，也可以是表的別名
explain select * from emp where sal > (select avg(sal) from emp) ;
在這裡插入圖片描述

2、表名是derivedN的形式，表示使用了id為N的查詢產生的衍生表
explain select * from (select ename staname,mgr from emp where ename = 'WARD' union select ename staname,mgr from emp where ename = 'SMITH') a;

derived2 ： 表明我們需要從衍生表2中取資料
在這裡插入圖片描述

3、當有union result的時候，表名是union n1,n2等的形式，n1,n2表示參與union的id
explain select * from emp where deptno = 10 union select * from emp where sal >2000;
在這裡插入圖片描述

2.4 type

type顯示的是訪問型別，訪問型別表示我是以何種方式去訪問我們的資料，最容易想的是全表掃描，直接暴力的遍歷一張表去尋找需要的資料，效率非常低下，訪問的型別有很多，效率從最好到最壞依次是：

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

一般情況下，得保證查詢至少達到range級別，最好能達到ref

--all:全表掃描，一般情況下出現這樣的sql語句而且資料量比較大的話那麼就需要進行優化。
explain select * from emp;

在這裡插入圖片描述

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

在這裡插入圖片描述

--range：表示利用索引查詢的時候限制了範圍，在指定範圍內進行查詢，這樣避免了index的全索引掃描，適用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
explain select * from emp where empno between 7000 and 7500;

在這裡插入圖片描述

--index_subquery：利用索引來關聯子查詢，不再掃描全表
explain select * from emp where emp.job in (select job from t_job);

--unique_subquery:該連線型別類似與index_subquery,使用的是唯一索引
 explain select * from emp e where e.deptno in (select distinct deptno from dept);

--ref_or_null：對於某個欄位即需要關聯條件，也需要null值的情況下，查詢優化器會選擇這種訪問方式
explain select * from emp e where  e.mgr is null or e.mgr=7369;

在這裡插入圖片描述

--ref：使用了非唯一性索引進行資料的查詢
 create index idx_3 on emp(deptno);
 explain select * from emp e,dept d where e.deptno =d.deptno;

在這裡插入圖片描述

--eq_ref ：使用唯一性索引進行資料查詢
explain select * from emp,emp2 where emp.empno = emp2.empno;

在這裡插入圖片描述

--const：這個表至多有一個匹配行，
explain select * from emp where empno = 7369;

在這裡插入圖片描述

--system：表只有一行記錄（等於系統表），這是const型別的特例，平時不會出現

2.5 possible_keys

顯示可能應用在這張表中的索引，一個或多個，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢實際使用

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

在這裡插入圖片描述

2.6 key

實際使用的索引，如果為null，則沒有使用索引，查詢中若使用了覆蓋索引，則該索引和查詢的select欄位重疊。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

在這裡插入圖片描述

2.7 key_len

表示索引中使用的位元組數，可以通過key_len計算查詢中使用的索引長度，在不損失精度的情況下長度越短越好。
1、一般地，key_len 等於索引列型別位元組長度，例如int型別為4 bytes，bigint為8 bytes；
2、如果是字串型別，還需要同時考慮字符集因素，例如utf8字符集1個字元佔3個位元組，gbk字符集1個字元佔2個位元組
3、若該列型別定義時允許NULL，其key_len還需要再加 1 bytes
4、若該列型別為變長型別，例如 VARCHAR（TEXT\BLOB不允許整列建立索引，如果建立部分索引也被視為動態列型別），其key_len還需要再加 2 bytes
字符集會影響索引長度、資料的儲存空間，為列選擇合適的字符集；變長欄位需要額外的2個位元組，固定長度欄位不需要額外的位元組。而null都需要1個位元組的額外空間，所以以前有個說法：索引欄位最好不要為NULL，因為NULL讓統計更加複雜，並且需要額外一個位元組的儲存空間。

-- key_len的長度計算公式：

-- varchar(len)變長欄位且允許NULL      : len*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(變長欄位)
-- varchar(len)變長欄位且不允許NULL    : len*(Character Set：utf8=3,gbk=2,latin1=1)+2(變長欄位)

-- char(len)固定欄位且允許NULL         : len*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)
-- char(len)固定欄位且不允許NULL       : len*(Character Set：utf8=3,gbk=2,latin1=1)

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

在這裡插入圖片描述

2.8 ref

顯示索引的哪一列被使用了，如果可能的話，是一個常數

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

在這裡插入圖片描述

2.9 rows

根據表的統計資訊及索引使用情況，大致估算出找出所需記錄需要讀取的行數，此引數很重要，直接反應的sql找了多少資料，在完成目的的情況下越少越好

explain select * from emp;

在這裡插入圖片描述

2.10 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 deptno,count(*) from emp group by deptno limit 10;

在這裡插入圖片描述

--using where:使用where進行條件過濾
explain select * from emp2 where empno = 1;

在這裡插入圖片描述

三總結

到這裡執行計劃就講完了，sql的執行計劃並不是很難，主要是記住每個列代表的意思和如何進行優化，這個是需要大量的訓練和實操實現的，有興趣的小夥伴可以自行去試試，還是很有趣的，本文只是簡單介紹一下MySQL執行計劃，想全面深入瞭解MySQL,可優先閱讀MySQL官方手冊，大家加油~

RabbitMQ靈魂拷問
2019-04-08
MQ
靈魂拷問，你真的瞭解DNS嗎？
2022-12-14
DNS
【Java】面試官靈魂拷問：if語句執行完else語句真的不會再執行嗎？
2020-05-23
Java面試
ORACLE:什麼是執行計劃
2015-01-09
Oracle
靈魂拷問：你真的理解System.out.println()列印原理嗎？
2020-10-17
MySQL高頻面試題的靈魂拷問
2019-12-18
MySql面試題
為什麼該SQL的執行計劃不走索引？？？
2013-01-29
SQL索引
靈魂拷問：flutter構建應用的方式是否是一種倒退？
2021-05-26
Flutter
關於程式碼重構的靈魂三問：是什麼？為什麼？怎麼做？
2020-12-21
用sql profile來固定執行計劃
2011-06-03
SQL
SQL的執行計劃
2008-06-25
SQL
面試官靈魂三問：什麼是SOA？什麼是微服務？SOA和微服務有什麼區別？
2022-02-25
面試微服務
sql 執行計劃
2008-08-20
SQL
MySQL靈魂拷問：36題帶你面試通關
2021-10-17
MySql面試
[靈魂拷問]MySQL面試高頻一百問(工程師方向)
2019-07-22
MySql面試工程師
關於HTML表格合併單元格的靈魂拷問
2020-12-16
HTML
大資料開發-Spark-拷問靈魂的5個問題
2021-01-31
大資料Spark
Oracle sql執行計劃
2020-12-14
OracleSQL
SQL Server執行計劃
2016-01-11
SQLServer
SQL執行計劃分析
2009-04-27
SQL
SQL執行計劃異常引起的效能問題
2022-04-15
SQL
SQL執行計劃異常引起的效能問題
2021-02-20
SQL
決定一個SQL執行效率的是執行計劃, 而不是SQL的寫法
2010-07-13
SQL
Spring Boot 到底是怎麼執行的，你知道嗎？
2019-03-20
Spring Boot
Spring Boot到底是怎麼執行的，你知道嗎？
2019-04-17
Spring Boot
【sql調優之執行計劃】獲取執行計劃
2010-08-16
SQL
sql的執行計劃詳解
2015-01-09
SQL
[每日一題]一道面試題是如何引發深層次的靈魂拷問？
2020-12-24
每日一題面試題
你知道YouTube的架構是什麼嗎
2008-11-19
架構
Oracle中檢視已執行sql的執行計劃
2014-06-17
OracleSQL
前端JS:什麼是深拷貝？什麼是淺拷貝？
2019-04-24
前端JS
獲取SQL執行計劃
2016-10-15
SQL
SQL 執行計劃案例1
2010-10-16
SQL
剖析SQL Server執行計劃
2009-10-22
SQLServer
檢視sql執行計劃
2009-11-17
SQL
你知道什麼是路由器嗎？
2022-03-08
路由器
我們真的知道測試行業的內卷是什麼嗎？
2020-10-05
行業
RMAN自動執行計劃
2013-12-26

來自靈魂的拷問——知道什麼是SQL執行計劃嗎？

一、前言

二、執行計劃中包含的資訊

2.1 id

2.2 select_type

2.3 table

2.4 type

2.5 possible_keys

2.6 key

2.7 key_len

2.8 ref

2.9 rows

2.10 extra

三 總結

相關文章

三總結