一文详解Mysql优化中explain各字段含义

前言#

explain 有何用处呢：为了知道优化 SQL 语句的执行，需要查看 SQL 语句的具体执行过程，以加快 SQL 语句的执行效率。
可以使用 explain+SQL 语句来模拟优化器执行 SQL 查询语句，从而知道 mysql 是如何处理 sql 语句的。通过查看执行计划了解执行器是否按照我们想的那样处理 SQL。

explain 执行计划中包含的信息如下：

id:  查询序列号
select_type: 查询类型
table: 表名或者别名
partitions: 匹配的分区
type: 访问类型
possible_keys: 可能用到的索引
key: 实际用到的索引
key_len: 索引长度
ref: 与索引比较的列
rows: 估算的行数
filtered: 按表条件筛选的行百分比
 Extra: 额外信息Copy

解析#

下面说下具体每一列的表示的含义和对应 sql.
测试的表结构如下：

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;Copy

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;Copy

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;Copy

01、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;Copy

2、如果 id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

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

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');Copy

02、select_type 列#

i
主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

1. simple: 简单的查询，不包含子查询和 union

   mysql> explain select * from emp;Copy

   

2. primary: 查询中若包含任何复杂的子查询，最外层查询则被标记为 Primary

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

   

3. union: 在 union，union all 和子查询中的第二个和随后的 select 被标记为 union

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

   

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)Copy

   

5. union result: 从 union 表获取结果的 select。

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

   

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

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

   

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);Copy

   

8. derived: 在 FROM 列表中包含的子查询被标记为 DERIVED，也叫做派生类

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

   

9. UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）

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

   

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

03、table 列#

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

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

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

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

04、type 列#

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

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

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

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

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

   mysql> explain select * from emp;Copy

   

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

   mysql> explain  select empno from emp;Copy

   

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

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

   

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

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

   
   但是大多数情况下使用 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 );Copy

   
   大多数情况下使用 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;Copy

   

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

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

   

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

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

    

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

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

    

05、possible_keys 列#

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

06、key 列#

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

07、key_len 列#

表示索引中使用的字节数，可以通过 key_len 计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。
索引越大占用存储空间越大，这样 io 的次数和量就会增加，影响执行效率

08、ref 列#

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

09、rows 列#

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

10、filtered 列#

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

11、extra 列#

包含额外的信息。

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

   mysql> explain select * from emp order by sal;Copy

   

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

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

   

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

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

   

4. using where: 使用 where 进行条件过滤

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

5. using join buffer: 使用连接缓存

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

   

6. impossible where：where 语句的结果总是 false

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

   

作者：纪先生的笔记
链接：[https://mp.weixin.qq.com/s?__biz=Mzg5MDYzMzM5MA==&mid=2247483863&idx=1&sn=5f5911b6e4f8e993ec8e7ee1edc0c279&chksm=cfd8ec16f8af6500551efc1e49dee22ac4bca052c5b8d36044007afafc6f591b091f220a0c69&scene=21#wechat_redirect]
来源：微信公众号

本作品采用《CC 协议》，转载必须注明作者和本文链接