Explain详解

1.什么是执行计划

如果线上有了慢查询语句后，就要对语句进行分析。一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执 行查询等等.

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查

2. 语法

在sql前面加上一个explain

explain select * from tableA 1

3.详细解释：

-- auto-generated definition
create table emp
(
    id         int auto_increment  primary key,
    uid        varchar(32)                         not null,
    ename      varchar(50)                         null,
    job        varchar(15)                         null comment '雇员的职位',
    mgr        int                                 null comment '雇员对应的领导编号，领导也是雇员',
    hiredate   date                                null comment '雇佣日期',
    sal        float(7, 2)                         null comment '基本工资，其中有两位小数，五倍整数，一共是七位',
    comm       float(7, 2)                         null comment '奖金，佣金',
    deptno     int                                 null comment '部门id',
    status     int       default 0                 null,
    created_at timestamp default CURRENT_TIMESTAMP not null,
    updated_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
    constraint emp_uid_uindex   unique (uid)
)charset = utf8;

create index emp_created_at_index on emp (created_at);
create index emp_deptno_index  on emp (deptno);
create index emp_ename_hiredate_deptno_index  on emp (ename, hiredate, deptno);
create index emp_hiredate_index  on emp (hiredate);
create index emp_sal_index on emp (sal);


create table dept
(
    deptno int         not null
        primary key,
    dname  varchar(14) null,
    loc    varchar(13) null
)
    charset = utf8;

模拟造数据：
https://learnku.com/articles/49513

  <!--模拟造数据-->
        <dependency>
            <groupId>com.apifan.common</groupId>
            <artifactId>common-random</artifactId>
            <version>1.0.4</version>
        </dependency>
        <dependency>
            <groupId>com.github.javafaker</groupId>
            <artifactId>javafaker</artifactId>
            <version>1.0.2</version>
        </dependency>

id 列

• id列的编号是 select 的序列号
• 有几个 select 就有几个id
• 并且id的顺序是按 select 出现的顺序增长的。
• id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

2. select_type列

simple:简单查询。查询不包含子查询和union
primary:复杂查询中最外层的 select
subquery:包含在 select 中的子查询(不在 from 子句中，是个单独对的查询)
derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表(derived的英文含义)

explain select (select 1 from dept where id = 1) from (select * from emp where id = 1) emp;

3. table列

• 简单的sql模式下，表示的就是当前表名或者别名
• 当 from 子句中有子查询时，table列是<derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查 询。
• 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

explain select (select 1 from dept where id = 1) from (select * from emp where id = 1) emp;

explain select * from emp where hiredate > '2005-01-11' union  select * from emp where ename like '王';

4. type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行

依次从最优到最差分别为 system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref

NULL:
mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如:在索引列中选取最小值，可 以单独查找索引来完成，不需要在执行时访问表 //

explain select min(uid) from emp;

system

• 当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的,比如 MyISAM、 Memory，那么对该表的访问方法就是 system。

test_myisam只有一条数据,为sysytem

test_innodb也只有一条数据，但是确实all

const

• 当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法 就是 const。因为只匹配一行数据，所以很快

-- 主键
explain select * from emp where id =5;
-- 唯一索引
explain select * from emp where uid = 'e83b90ce651b4de6a1b98e101eb4cf28';

eq_ref

• primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

-- emp.id = dept.deptno都是唯一索引或者主键索引
explain select * from emp inner join dept on emp.id = dept.deptno

ref:

• 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会 找到多个符合条件的行。

explain select * from emp where hiredate = '2010-02-14';

range:

• 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

explain select * from emp where id > 30;

index:

• 扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这 种通常比ALL快一些。

-- ename,hiredate,deptno为索引列 覆盖索引
explain select ename,hiredate,deptno from emp where emp.ename  like '%谢致远';

ALL:
即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

explain select * from emp where emp.ename  like '%谢致远';

其实还有一些其他的状态：
fulltext , ref_or_null , index_merge , unique_subquery , index_subquery
大致的性能顺序是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
如果没有联合索引，mysql就可以合并索引比如:index_merge

explain  select * from emp where deptno = 30 or hiredate = '2010-02-14'

5.possible_keys列

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引 对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提 高查询性能，然后用 explain 查看效果。

6. key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

7. key_len列

key_len计算规则如下:

1. 字符串类型:
char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节

• char(n):如果存汉字长度就是 3n 字节
• varchar(n):如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为 varchar是变长字符串
  2. 数值类型
• tinyint:1字节
• smallint:2字节
• int:4字节
• bigint:8字节

3. 时间类型

• date:3字节
• timestamp:4字节
• datetime:8字节

4. 如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索 引。

8. ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有:const(常量)，字段名

9. rows列

预估可能需要扫描的行数

explain select * from emp where id > 400000;

explain select * from emp where id > 500005;

10.filtered

某个表经过搜索条件过滤后剩余记录条数的百分比

一般如果where后至少有2个条件，才会出现次场景，比如下sql中，MySQL 先获取到
id > 300的有9164条，执行计划的 filtered 列就代表查询优化器预测 在这 9164 条记录中，有多少条记录满足其余的搜索条件，也就是 ename like '刘%'这个条件 的百分比。此处 filtered 列的值是 50.0，说明查询优化器预测在 5286 条记录中有 50.00%的 记录满足 ename like '刘%'这个条件

explain select * from emp where id > 300 and ename  like '刘%';

11.Extra列

Using index: 使用覆盖索引

--  虽然是 like 左模糊，但是任然使用了索引，相当于索引全部扫描了一般，不用回表了
explain select ename,hiredate,deptno from emp where emp.ename  like '%谢致远';

Using where: 使用 where 语句来处理结果，并且查询的列未被索引覆盖

explain select * from emp where id > 300 

Using index condition: 查询的列不完全被索引覆盖，where条件中是一个前导列的范围 或者索引下推

-- 索引下推 
explain select * from emp where emp.ename  like '谢致远%'  and hiredate = '2010-02-14';

索下推的理解

explain select * from emp where ename  like '刘%';

Using temporary: mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索 引来优化。

-- created_at 无索引
explain select distinct (created_at) from emp;

Using filesort: 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的。

-- created_at无索引
explain select * from emp order by created_at;