MySQL中explain命令怎么用

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

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

可以使用explain+SQL語(yǔ)句來(lái)模擬優(yōu)化器執行SQL查詢(xún)語(yǔ)句，從而知道mysql是如何處理sql語(yǔ)句的。通過(guò)查看執行計劃了解執行器是否按照我們想的那樣處理SQL。

explain執行計劃中包含的信息如下：

id:  查詢(xún)序列號

select_type: 查詢(xún)類(lèi)型

table: 表名或者別名

partitions: 匹配的分區

type: 訪(fǎng)問(wèn)類(lèi)型

possible_keys: 可能用到的索引

key: 實(shí)際用到的索引

key_len: 索引長(cháng)度

ref: 與索引比較的列

rows: 估算的行數

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

Extra: 額外信息

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

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

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 '部門(mén)編號',  `sal` int(11) NOT NULL DEFAULT 0 COMMENT '銷(xiāo)售量',  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 '部門(mén)編碼',  `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '部門(mén)名稱(chēng)',  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查詢(xún)的序列號(一組數字)，表示查詢(xún)中執行select子句或者操作表的順序。

id列分為三種情況：

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

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不同，如果是子查詢(xún)，id的序號會(huì )遞增，id值越大優(yōu)先級越高，越先被執行

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

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

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列

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

1. sample: 簡(jiǎn)單的查詢(xún)，不包含子查詢(xún)和union

mysql> explain select * from emp;

2. primary: 查詢(xún)中若包含任何復雜的子查詢(xún)，最外層查詢(xún)則被標記為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和子查詢(xún)中的第二個(gè)和隨后的select被標記為union

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

4. dependent union: 在包含UNION或者UNION ALL的大查詢(xún)中，如果各個(gè)小查詢(xún)都依賴(lài)于外層查詢(xún)的話(huà)，那除了最左邊的那個(gè)小查詢(xún)之外，其余的小查詢(xún)的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表獲取結果的select。

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

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

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

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

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

8. derived: 在FROM列表中包含的子查詢(xún)被標記為DERIVED，也叫做派生類(lèi)

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

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

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

10. uncacheable union: 表示union的查詢(xún)結果不能被緩存：沒(méi)找到具體的sql語(yǔ)句驗證.

table列

對應行正在訪(fǎng)問(wèn)哪一個(gè)表，表名或者別名，可能是臨時(shí)表或者union合并結果集.

1、如果是具體的表名，則表明從實(shí)際的物理表中獲取數據，當然也可以是表的別名.

2、表名是derivedN的形式，表示使用了id為N的查詢(xún)產(chǎn)生的衍生表.

3、當有union result的時(shí)候，表名是union n1,n2等的形式，n1,n2表示參與union的id.

type列

type顯示的是訪(fǎng)問(wèn)類(lèi)型，訪(fǎng)問(wèn)類(lèi)型表示我是以何種方式去訪(fǎng)問(wèn)我們的數據，最容易想的是全表掃描，直接暴力的遍歷一張表去尋找需要的數據，效率非常低下。

訪(fǎng)問(wèn)的類(lèi)型有很多，效率從最好到最壞依次是：

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

一般情況下，要保證查詢(xún)至少達到range級別，最好能達到ref

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

mysql> explain select * from emp;

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

mysql> explain  select empno from emp;

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

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

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

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

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

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

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

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

6. index_merge：在查詢(xún)過(guò)程中需要多個(gè)索引組合使用.

mysql> 沒(méi)有模擬出來(lái)

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

mysql> 沒(méi)模擬出來(lái)

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

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

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

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

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

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

11. system：表只有一行記錄（等于系統表），這是const類(lèi)型的特例，平時(shí)不會(huì )出現,不需要進(jìn)行磁盤(pán)io

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

possible_keys列

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

key列

實(shí)際使用的索引，如果為null，則沒(méi)有使用索引，查詢(xún)中若使用了覆蓋索引，則該索引和查詢(xún)的select字段重疊。

key_len列

表示索引中使用的字節數，可以通過(guò)key_len計算查詢(xún)中使用的索引長(cháng)度，在不損失精度的情況下長(cháng)度越短越好。

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

ref列

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

rows列

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

filtered列

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

extra列

包含額外的信息。

1. using filesort: 說(shuō)明mysql無(wú)法利用索引進(jìn)行排序，只能利用排序算法進(jìn)行排序，會(huì )消耗額外的位置

mysql> explain select * from emp order by sal;

2. using temporary: 建立臨時(shí)表來(lái)保存中間結果，查詢(xún)完成之后把臨時(shí)表刪除

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

3. using index: 這個(gè)表示當前的查詢(xún)是覆蓋索引的，直接從索引中讀取數據，而不用訪(fǎng)問(wèn)數據表。如果同時(shí)出現using where 表名索引被用來(lái)執行索引鍵值的查找，如果沒(méi)有，表面索引被用來(lái)讀取數據，而不是真的查找

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

4. using where: 使用where進(jìn)行條件過(guò)濾

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語(yǔ)句的結果總是false

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