MySQL進階學習：深入了解explain指令

這篇文章是MySQL的進階學習，帶大家深入了解explain各字段的意義，希望對大家有幫助！

explain有何用：為了知道最佳化SQL語句的執行，需要查看SQL語句的特定執行過程，以加快SQL語句的執行效率。

可以使用explain SQL語句來模擬最佳化器執行SQL查詢語句，從而知道mysql是如何處理sql語句的。透過查看執行計劃以了解執行器是否按照我們想要的那樣處理SQL。

explain執行計畫中包含的資訊如下：

           id:  查詢序號

  select_type: 查詢類型

# 別名

   partitions: 相符的分區

         type: 存取類型

possible_keys: 可能使用的索引

##   實際到  

#      key_len: 索引長度

          ref: 與索引比較的列

         rows: 估算的行數

#         rows: 估計的行數

##         rows: 估計的行數

## 

#        Extra: 額外資訊

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

測試使用mysql版本5.7， 使用的3個表格結構如下

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查詢的序列號(一組數字)，表示查詢中執行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不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行

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

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

#select_type列#主要用來分辨查詢的類型，是普通查詢還是聯合查詢還是子查詢

    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表中取得結果的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：一个子查询的结果不能被缓存，必须重新评估外链接的第一行对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）

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

10. uncacheable union: 表示union的查询结果不能被缓存：没找到具体的sql语句验证.

table列

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集.

1、如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名.

2、表名是derivedN的形式，表示使用了id为N的查询产生的衍生表.

3、当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id.

type列

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下。

访问的类型有很多，效率从最好到最坏依次是：

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

一般情况下，要保证查询至少达到range级别，最好能达到ref

1. all: 全表扫描，需要扫描整张表，从头到尾找到需要的数据行。一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。

mysql> explain select * from emp;

2. index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序

mysql> explain  select empno from emp;

3. range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符：=, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()

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

4. index_subquery：利用索引来关联子查询，不再扫描全表

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

但是大多数情况下使用SELECT子查询时，MySQL查询优化器会自动将子查询优化为联表查询，因此 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 );

大多数情况下使用SELECT子查询时，MySQL查询优化器会自动将子查询优化为联表查询，因此 type 不会显示为 index_subquery,而是eq_ref

6. index_merge：在查询过程中需要多个索引组合使用.

mysql> 没有模拟出来

7. ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式.

mysql> 没模拟出来

8. ref：使用了非唯一性索引进行数据的查找

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

9. eq_ref ：当进行等值联表查询使用主键索引或者唯一性非空索引进行数据查找(实际上唯一索引等值查询type不是eq_ref而是const)

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

10. const：最多只能匹配到一条数据，通常使用主键或唯一索引进行等值条件查询

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

11. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现,不需要进行磁盘io

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

possible_keys列

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key列

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

key_len列

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

索引越大占用存储空间越大，这样io的次数和量就会增加，影响执行效率

ref列

显示之前的表在key列记录的索引中查找值所用的列或者常量

rows列

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好。

filtered列

针对表中符合某个条件(where子句或者联接条件)的记录数的百分比所做的一个悲观估算。

extra列

包含额外的信息。

1. using filesort: 说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置

mysql> explain select * from emp order by sal;

2. using temporary: 建立临时表来保存中间结果，查询完成之后把临时表删除

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

3. using index: 这个表示当前的查询是覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找

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语句的结果总是false

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

更多编程相关知识，请访问：编程视频！！

以上是MySQL進階學習：深入了解explain指令的詳細內容。更多資訊請關注PHP中文網其他相關文章！