引言

項目上生產(chǎn)環(huán)境最近有個借口查詢突然需要45秒左右。

了不起看到這個問題很疑惑，什么情況，這個接口之前好像沒有出現(xiàn)過任何問題吧。

經(jīng)和運維配合查看，發(fā)現(xiàn)是SQL語句問題，有個sql查詢腳本執(zhí)行竟然消耗了40秒，我拿出來自己執(zhí)行發(fā)現(xiàn)亦是如此。

sql大致情況就是有個left join了一張表，有10幾萬數(shù)據(jù)，使用explain查看，就是這個表執(zhí)行消耗了近40秒。

解決

由于是生產(chǎn)環(huán)境，涉及數(shù)據(jù)隱私，具體sql就不貼了，解決思路就是使用explain+SQL語句查看哪個執(zhí)行是全表掃描。

進而定位到問題，將那張表重新寫了下，加了一個業(yè)務(wù)過濾條件，效率直接從40秒到0.1秒了。

說到這兒，可能有些小伙伴不知道explain執(zhí)行計劃，那我們就一起簡單了解下吧，在實際解決問題和面試都會有用的。

EXPLAIN執(zhí)行計劃

官網(wǎng)地址：?https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

執(zhí)行計劃中包含的信息

ColumnMeaningidThe?SELECTidentifierselect_typeThe?SELECTtypetableThe table for the output rowpartitionsThe matching partitionstypeThe join typepossible_keysThe possible indexes to choosekeyThe index actually chosenkey_lenThe length of the chosen keyrefThe columns compared to the indexrowsEstimate of rows to be examinedfilteredPercentage of rows filtered by table conditionextraAdditional information

表結(jié)構(gòu)準備

表結(jié)構(gòu)準備是為了在MySQL數(shù)據(jù)庫中創(chuàng)建表并插入數(shù)據(jù)，以便后續(xù)的SQL查詢練習。

在這個過程中，我們創(chuàng)建了四個表：dept、emp、emp2和salgrade，以及一個用于測試的t_job表。

這些表中包含了各種類型的數(shù)據(jù)，例如員工信息、部門信息、薪資等級信息等等。

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](https://www.zhihu.com/search?q=salgrade&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra=%7B%22sourceType%22%3A%22answer%22%2C%22sourceId%22%3A2492346578%7D)` 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;

id

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

在MySQL中執(zhí)行SELECT查詢時，會生成一個查詢計劃，其中包含一組數(shù)字，稱為ID。

ID號的作用是表示查詢中執(zhí)行SELECT子句或操作表的順序。

id號分為三種情況：
1、id是相同，那么執(zhí)行順序從上到下

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是不同的，是子查詢的，id的序號將遞增，id值越大的優(yōu)先級越高，會更先被執(zhí)行

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

3、id相同和不同的同時存在：相同的可以歸為一組，從上往下順序執(zhí)行。

然后所有組中，id值越大，優(yōu)先級越高，越先執(zhí)行

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 in (select d.deptno from dept d where d.dname = 'SALES');

select_type

主要用來分辨查詢的類型，是普通查詢還是聯(lián)合查詢還是子查詢

select_type是一個非常重要的字段，它表示MySQL執(zhí)行查詢時的查詢類型，不同的查詢類型會影響到MySQL的執(zhí)行計劃和優(yōu)化方式。

select_typeValueMeaningSIMPLESimple SELECT (not using UNION or subqueries)PRIMARYOutermost SELECTUNIONSecond or later SELECT statement in a UNIONDEPENDENT UNIONSecond or later SELECT statement in a UNION, dependent on outer queryUNION RESULTResult of a UNION.SUBQUERYFirst SELECT in subqueryDEPENDENT SUBQUERYFirst SELECT in subquery, dependent on outer queryDERIVEDDerived tableUNCACHEABLE SUBQUERYA subquery for which the result cannot be cached and must be re-evaluated for each row of the outer queryUNCACHEABLE UNIONThe second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

常見的select_type類型有以下幾種：

1. SIMPLE：簡單SELECT查詢，不包含子查詢或UNION查詢等，查詢中也沒有使用UNION ALL、DISTINCT、GROUP BY、HAVING、LIMIT等關(guān)鍵字。

2. PRIMARY：表示查詢中包含一個或多個子查詢。MySQL會先執(zhí)行主查詢，再執(zhí)行子查詢。

3. SUBQUERY：表示查詢中的第一個子查詢，子查詢包含在SELECT列表中的子查詢或WHERE子句中的子查詢。

4. DERIVED：表示查詢中的子查詢，派生表。MySQL會先執(zhí)行子查詢，然后將結(jié)果存儲在一個臨時表中，再執(zhí)行主查詢。

5. UNION：表示查詢中的UNION操作，UNION操作會將多個查詢的結(jié)果集合并成一個結(jié)果集。

6. UNION RESULT：表示查詢中的UNION操作的結(jié)果集。

7. DEPENDENT SUBQUERY：表示查詢中的子查詢依賴于外部查詢的結(jié)果集。MySQL會根據(jù)外部查詢的結(jié)果集來執(zhí)行子查詢。

8. DEPENDENT UNION：表示查詢中的UNION操作依賴于外部查詢的結(jié)果集。MySQL會根據(jù)外部查詢的結(jié)果集來執(zhí)行UNION操作。

9. DEPENDENT UNION RESULT：表示查詢中的UNION操作的結(jié)果集依賴于外部查詢的結(jié)果集。MySQL會根據(jù)外部查詢的結(jié)果集來執(zhí)行UNION操作。

我們看幾個簡單的例子

--sample:簡單的查詢，不包含子查詢和unionexplain select * from emp;--primary:查詢中若包含任何復雜的子查詢，最外層查詢則被標記為Primaryexplain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;--union:若第二個select出現(xiàn)在union之后，則被標記為unionexplain select * from emp where deptno = 10 union select * from emp where sal >2000;--dependent union:跟union類似，此處的depentent表示union或union all聯(lián)合而成的結(jié)果會受外部表影響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表獲取結(jié)果的selectexplain 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);--DERIVED: from子句中出現(xiàn)的子查詢，也叫做派生類，explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;--UNCACHEABLE SUBQUERY：表示使用子查詢的結(jié)果不能被緩存 explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);--uncacheable union:表示union的查詢結(jié)果不能被緩存：sql語句未驗證

table

對應(yīng)行正在訪問哪一個表，表名或者別名，可能是臨時表或者union合并結(jié)果集
1、如果是具體的表名，則表明從實際的物理表中獲取數(shù)據(jù)，當然也可以是表的別名
2、表名是derivedN的形式，表示使用了id為N的查詢產(chǎn)生的衍生表
3、當有union result的時候，表名是union n1,n2等的形式，n1,n2表示參與union的id

type

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

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

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

--all:全表掃描，一般情況下出現(xiàn)這樣的sql語句而且數(shù)據(jù)量比較大的話那么就需要進行優(yōu)化。explain select * from emp;--index：全索引掃描這個比all的效率要好，主要有兩種情況，一種是當前的查詢時覆蓋索引，即我們需要的數(shù)據(jù)在索引中就可以索取，或者是使用了索引進行排序，這樣就避免數(shù)據(jù)的重排序explain ?select empno from emp;--range：表示利用索引查詢的時候限制了范圍，在指定范圍內(nèi)進行查詢，這樣避免了index的全索引掃描，適用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() explain select * from emp where empno between 7000 and 7500;--index_subquery：利用索引來關(guān)聯(lián)子查詢，不再掃描全表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);--index_merge：在查詢過程中需要多個索引組合使用，沒有模擬出來--ref_or_null：對于某個字段即需要關(guān)聯(lián)條件，也需要null值的情況下，查詢優(yōu)化器會選擇這種訪問方式explain select * from emp e where ?e.mgr is null or e.mgr=7369;--ref：使用了非唯一性索引進行數(shù)據(jù)的查找 create index idx_3 on emp(deptno); explain select * from emp e,dept d where e.deptno =d.deptno;--eq_ref ：使用唯一性索引進行數(shù)據(jù)查找explain select * from emp,emp2 where emp.empno = emp2.empno;--const：這個表至多有一個匹配行，explain select * from emp where empno = 7369;--system：表只有一行記錄（等于系統(tǒng)表），這是const類型的特例，平時不會出現(xiàn)

possible_keys

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

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

key

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

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

key_len

表示索引中使用的字節(jié)數(shù)，可以通過key_len計算查詢中使用的索引長度，在不損失精度的情況下長度越短越好。

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

ref

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

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

rows

根據(jù)表的統(tǒng)計信息及索引使用情況，大致估算出找出所需記錄需要讀取的行數(shù)，此參數(shù)很重要，直接反應(yīng)的sql找了多少數(shù)據(jù)，在完成目的的情況下越少越好

explain select * from emp;

extra

包含額外的信息。

--using filesort:說明mysql無法利用索引進行排序，只能利用排序算法進行排序，會消耗額外的位置explain select * from emp order by sal;--using temporary:建立臨時表來保存中間結(jié)果，查詢完成之后把臨時表刪除explain select ename,count(*) from emp where deptno = 10 group by ename;--using index:這個表示當前的查詢時覆蓋索引的，直接從索引中讀取數(shù)據(jù)，而不用訪問數(shù)據(jù)表。如果同時出現(xiàn)using where 表名索引被用來執(zhí)行索引鍵值的查找，如果沒有，表面索引被用來讀取數(shù)據(jù)，而不是真的查找explain select deptno,count(*) from emp group by deptno limit 10;--using where:使用where進行條件過濾explain select * from t_user where id = 1;--using join buffer:使用連接緩存，情況沒有模擬出來--impossible where：where語句的結(jié)果總是falseexplain select * from emp where empno = 7469;

EXPLAIN使用場景

1. 查看查詢的執(zhí)行計劃。

使用EXPLAIN命令可以查看查詢的執(zhí)行計劃，包括查詢中使用的索引、表的連接方式、數(shù)據(jù)讀取方式等等。

通過查看執(zhí)行計劃，可以了解MySQL是如何處理查詢操作的，從而發(fā)現(xiàn)查詢中可能存在的性能問題，進而進行優(yōu)化。

2. 比較不同查詢方案的性能。

使用EXPLAIN命令可以比較不同查詢方案的性能，例如在多個索引中選擇最優(yōu)索引、使用不同的連接方式等等。

通過比較不同查詢方案的執(zhí)行計劃，可以找到最優(yōu)的查詢方案，從而提高查詢性能。

3. 優(yōu)化查詢語句。

使用EXPLAIN命令可以發(fā)現(xiàn)查詢語句中可能存在的性能問題，例如沒有使用索引、使用了不必要的子查詢等等。

通過優(yōu)化查詢語句，可以讓MySQL選擇更優(yōu)的查詢方案，從而提高查詢性能。

4. 了解表結(jié)構(gòu)對查詢性能的影響。

使用EXPLAIN命令可以了解表結(jié)構(gòu)對查詢性能的影響，例如表中是否存在大字段、字段類型是否匹配等等。

通過了解表結(jié)構(gòu)對查詢性能的影響，可以進行相應(yīng)的優(yōu)化，提高查詢性能。

其實在大多數(shù)時候，使用這個命令一般都是排查慢查詢原因，這個是用的最多的。

EXPLAIN使用注意事項

1. EXPLAIN命令只能用于SELECT語句。

EXPLAIN命令不能用于INSERT、UPDATE、DELETE等語句。

2. EXPLAIN命令不能直接修改數(shù)據(jù)。

EXPLAIN命令只是用來查看查詢的執(zhí)行計劃，不能直接修改查詢結(jié)果或數(shù)據(jù)庫中的數(shù)據(jù)。

3. EXPLAIN命令只能查看當前用戶有權(quán)限查看的表和字段。

如果當前用戶沒有權(quán)限查看某些表或字段，那么在使用EXPLAIN命令時，這些表或字段的信息將不會顯示。

4. EXPLAIN命令的輸出結(jié)果可能會受到多種因素的影響

例如查詢條件、表結(jié)構(gòu)、索引狀態(tài)等等。因此，在使用EXPLAIN命令時，需要綜合考慮多種因素，才能得出正確的結(jié)論。

5. EXPLAIN命令可以使用不同的選項

例如EXTENDED、PARTITIONS等等。通過使用不同的選項，可以得到更詳細的執(zhí)行計劃信息，從而更好地了解MySQL是如何處理查詢操作的。

總結(jié)

今天了不起就一個生產(chǎn)環(huán)境優(yōu)化，帶大家了解下EXPLAIN相關(guān)知識。

希望大家在接下來的開發(fā)生涯中，持續(xù)使用這個命令，解決和優(yōu)化各種數(shù)據(jù)庫慢查詢問題。