到底什么是MySQL執行計劃嗎？
面試官說：工作這么久了，應該知道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;
二、執行計劃中包含的信息
貼心的小農已經把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;
- -- 右關聯e
- 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
主要用來分辨查詢的類型，是普通查詢還是聯合查詢還是子查詢
- --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、如果是具體的表名，則表明從實際的物理表中獲取數據，也可以是表的別名
- explainselect*fromempwheresal>(selectavg(sal)fromemp);
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 > eqref > ref > fulltext > refornull > indexmerge > uniquesubquery > indexsubquery > 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
表示索引中使用的字節數，可以通過keylen計算查詢中使用的索引長度，在不損失精度的情況下長度越短越好。
1、一般地，keylen 等于索引列類型字節長度，例如int類型為4 bytes，bigint為8 bytes;
2、如果是字符串類型，還需要同時考慮字符集因素，例如utf8字符集1個字符占3個字節，gbk字符集1個字符占2個字節
3、若該列類型定義時允許NULL，其keylen還需要再加 1 bytes
4、若該列類型為變長類型，例如 VARCHAR(TEXT\BLOB不允許整列創建索引，如果創建部分索引也被視為動態列類型)，其keylen還需要再加 2 bytes 字符集會影響索引長度、數據的存儲空間，為列選擇合適的字符集;變長字段需要額外的2個字節，固定長度字段不需要額外的字節。而null都需要1個字節的額外空間，所以以前有個說法：索引字段最好不要為NULL，因為NULL讓統計更加復雜，并且需要額外一個字節的存儲空間。
- 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官方手冊，大家加油~
