MySQL中explain命令怎么用

這篇文章主要介紹了MySQL中explain命令怎么用，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

explain有何用處呢：為了知道優(yōu)化SQL語句的執(zhí)行，需要查看SQL語句的具體執(zhí)行過程，以加快SQL語句的執(zhí)行效率。

可以使用explain+SQL語句來模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道m(xù)ysql是如何處理sql語句的。通過查看執(zhí)行計劃了解執(zhí)行器是否按照我們想的那樣處理SQL。

explain執(zhí)行計劃中包含的信息如下：

id:  查詢序列號

select_type: 查詢類型

table: 表名或者別名

partitions: 匹配的分區(qū)

type: 訪問類型

possible_keys: 可能用到的索引

key: 實際用到的索引

key_len: 索引長度

ref: 與索引比較的列

rows: 估算的行數(shù)

filtered: 按表條件篩選的行百分比

Extra: 額外信息

下面說下具體每一列的表示的含義和對應sql。

測試使用mysql版本5.7， 使用的3個表結(jié)構(gòu)如下

CREATE TABLE `demo`.`emp`  (  `emp_id` bigint(20) NOT NULL,  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '姓名',  `empno` int(20) NOT NULL COMMENT '工號',  `deptno` int(20) NOT NULL COMMENT '部門編號',  `sal` int(11) NOT NULL DEFAULT 0 COMMENT '銷售量',  PRIMARY KEY (`emp_id`) USING BTREE,  INDEX `u1`(`deptno`) USING BTREE,  UNIQUE INDEX `u2`(`empno`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

CREATE TABLE `demo`.`dept`  (  `id` bigint(20) NOT NULL,  `deptno` int(20) NOT NULL COMMENT '部門編碼',  `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '部門名稱',  PRIMARY KEY (`id`) USING BTREE,  UNIQUE INDEX `dept_u1`(`deptno`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

CREATE TABLE `demo`.`salgrade`  (  `id` bigint(20) NOT NULL,  `losal` int(20) NULL DEFAULT NULL,  `hisal` int(20) NULL DEFAULT NULL,  `emp_id` bigint(20) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

id列

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

id列分為三種情況：

1、如果id相同，那么執(zhí)行順序從上到下

mysql> 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í)行

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

3、id相同和不同的，同時存在：相同的可以認為是一組，從上往下順序執(zhí)行，在所有組中，id值越大，優(yōu)先級越高，越先執(zhí)行

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

select_type列

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

1. sample: 簡單的查詢，不包含子查詢和union

mysql> explain select * from emp;

2. primary: 查詢中若包含任何復雜的子查詢，最外層查詢則被標記為Primary

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

3. union: 在union，union all和子查詢中的第二個和隨后的select被標記為union

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

4. dependent union: 在包含UNION或者UNION ALL的大查詢中，如果各個小查詢都依賴于外層查詢的話，那除了最左邊的那個小查詢之外，其余的小查詢的select_type的值就是DEPENDENT UNION。

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

5. union result: 從union表獲取結(jié)果的select。

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

6. subquery: 在select或者where列表中包含子查詢（不在from子句中）

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

7. dependent subquery: 子查詢中的第一個select（不在from子句中），而且取決于外面的查詢。

mysql> explain select e1.* from emp e1 WHERE e1.deptno = (SELECT deptno FROM emp e2 WHERE e1.empno = e2.empno);

8. derived: 在FROM列表中包含的子查詢被標記為DERIVED，也叫做派生類

mysql> explain select * from ( select emp_id,count(*) from emp group by emp_id ) e;

9. UNCACHEABLE SUBQUERY：一個子查詢的結(jié)果不能被緩存，必須重新評估外鏈接的第一行對于外層的主表，子查詢不可被物化，每次都需要計算（耗時操作）

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

10. uncacheable union: 表示union的查詢結(jié)果不能被緩存：沒找到具體的sql語句驗證.

table列

對應行正在訪問哪一個表，表名或者別名，可能是臨時表或者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

1. all: 全表掃描，需要掃描整張表，從頭到尾找到需要的數(shù)據(jù)行。一般情況下出現(xiàn)這樣的sql語句而且數(shù)據(jù)量比較大的話那么就需要進行優(yōu)化。

mysql> explain select * from emp;

2. index：全索引掃描這個比all的效率要好，主要有兩種情況，一種是當前的查詢時覆蓋索引，即我們需要的數(shù)據(jù)在索引中就可以索取，或者是使用了索引進行排序，這樣就避免數(shù)據(jù)的重排序

mysql> explain  select empno from emp;

3. range：表示利用索引查詢的時候限制了范圍，在指定范圍內(nèi)進行查詢，這樣避免了index的全索引掃描，適用的操作符：=, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()

mysql> explain select * from emp where empno between 100 and 200;

4. index_subquery：利用索引來關(guān)聯(lián)子查詢，不再掃描全表

mysql> explain select * from emp where deptno not in (select deptno from emp)

但是大多數(shù)情況下使用SELECT子查詢時，MySQL查詢優(yōu)化器會自動將子查詢優(yōu)化為聯(lián)表查詢，因此 type 不會顯示為 index_subquery,而是ref

5. unique_subquery: 該連接類型類似于index_subquery,使用的是唯一索引

mysql> explain SELECT * from emp where emp_id not in (select emp.emp_id from emp );

大多數(shù)情況下使用SELECT子查詢時，MySQL查詢優(yōu)化器會自動將子查詢優(yōu)化為聯(lián)表查詢，因此 type 不會顯示為 index_subquery,而是eq_ref

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

mysql> 沒有模擬出來

7. ref_or_null：對于某個字段即需要關(guān)聯(lián)條件，也需要null值的情況下，查詢優(yōu)化器會選擇這種訪問方式.

mysql> 沒模擬出來

8. ref：使用了非唯一性索引進行數(shù)據(jù)的查找

mysql> explain select * from emp where  deptno=10;

9. eq_ref ：當進行等值聯(lián)表查詢使用主鍵索引或者唯一性非空索引進行數(shù)據(jù)查找(實際上唯一索引等值查詢type不是eq_ref而是const)

mysql> explain select * from salgrade s LEFT JOIN emp e on s.emp_id = e.emp_id;

10. const：最多只能匹配到一條數(shù)據(jù)，通常使用主鍵或唯一索引進行等值條件查詢

mysql> explain select * from emp where empno = 10;

11. system：表只有一行記錄（等于系統(tǒng)表），這是const類型的特例，平時不會出現(xiàn),不需要進行磁盤io

mysql> explain SELECT * FROM `mysql`.`proxies_priv`;

possible_keys列

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

key列

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

key_len列

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

索引越大占用存儲空間越大，這樣io的次數(shù)和量就會增加，影響執(zhí)行效率

ref列

顯示之前的表在key列記錄的索引中查找值所用的列或者常量

rows列

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

filtered列

針對表中符合某個條件(where子句或者聯(lián)接條件)的記錄數(shù)的百分比所做的一個悲觀估算。

extra列

包含額外的信息。

1. using filesort: 說明mysql無法利用索引進行排序，只能利用排序算法進行排序，會消耗額外的位置

mysql> explain select * from emp order by sal;

2. using temporary: 建立臨時表來保存中間結(jié)果，查詢完成之后把臨時表刪除

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

3. using index: 這個表示當前的查詢是覆蓋索引的，直接從索引中讀取數(shù)據(jù)，而不用訪問數(shù)據(jù)表。如果同時出現(xiàn)using where 表名索引被用來執(zhí)行索引鍵值的查找，如果沒有，表面索引被用來讀取數(shù)據(jù)，而不是真的查找

mysql> explain select deptno,count(*) from emp group by deptno limit 10;

4. using where: 使用where進行條件過濾

mysql> explain select * from emp where name = 1;

5. using join buffer: 使用連接緩存

mysql> explain select * from emp e left join dept d on e.deptno = d.deptno;

6. impossible where：where語句的結(jié)果總是false

mysql> explain select * from emp where 1=0;

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL中explain命令怎么用”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學習!