MySQL中Explain用法详解

Explain简介

我们在写后端程序的时候，通常会写sql来查询数据，如果是单表查询的时候，那直接select就完事了，但是如果是连表查询数据量也不小的话，就造成了查询速度会比较慢，那么我们该怎么知道我的sql的实际执行情况，它有没有走索引，执行效率是啥呢？数据库就给我们提供了这么一种功能，这个就是本文的重点了:expalin。

通过explain，我们可以获取到sql语句的执行计划，比如像表的读取顺序，使用了哪些索引等等。但是各个数据库的explain执行计划所展示出的内容是不太相同的，这里我们就来看MySQl中的执行计划explain。

先来跑两行SQL作为本文的例子：

CREATE TABLE `project` (
  `id` int(12) NOT NULL,
  `project_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`project_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into  project (id,project_name) 
values ('1', 'project1 '), 
('2', 'projectplus'),
('3','projectpro');

CREATE TABLE `student` (
  `id` int(12) NOT NULL,
  `name` varchar(32) DEFAULT NULL COMMENT '姓名',
  `project_id` int(12) DEFAULT NULL COMMENT '课程编码',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into student (id,name,project_id) 
values ('1','name1','1'),
('2','nameplus','2'),
('3','namepro','3');

我们来执行一条最简单的sql，一探expalin结果的究竟。

explain SELECT * from student

控制台：

可以看到，显示出来的参数还是很多的，那这些都是什么意思呢？接下来我们一个一个来进行分析一下。

id

id这一列代表sql语句执行的顺序，id值越大的行，越先执行，id值相同的情况下，执行顺序就从上往下执行。一般来说，有多少个select，就会有多少个id。

select_type

这个从字面意思来看呢。就是查询类型，它主要就是来区别普通查询、联合查询、子查询等。

他有六种类型：

1. Simple：简单查询，查询不包含子查询和union

   explain SELECT * from student：可以看到，这个sql的explain执行计划上的select_type就是simple

2. Primary：对于union、union all、子查询的大查询，最左侧的就是primary，复杂查询中最外层的select

   explain SELECT * from student union select * from student

3. Derived：包含在from子句中的子查询，MySQL会将结果存放在一个临时表中，也称为派生表

   explain SELECT * from (SELECT s1.* from student s1 union SELECT s2.* from student s2) k

4. Union：在union中的第二个和随后的select（）

   其实3、4、5可以使用同一条sql：

5. Union result：从union临时表检索结果的select

6. Subquery：包含在select中的子查询(不在from子句中)，select查的字段中的信息。

   explain SELECT student.*,(SELECT name from student where name=‘name1’) from student where name=‘name1’

table

这个就是当前行所执行的表，当然了，如果表定义了别名的话，那么就会显示别名。

Partitions

分区，也就是查询的表所在的分区，如果是NULL的话，就代表该表没有被设置分区。

type

该行查询所使用的访问类型，他的值有十多种，但是这些我们没有必要全部知道，这里我列出来最常见的八种，这八种类型的效率就代表从好到差：

1. System:这个是效率最高的，比如像where id=xxxx，但是在这里有个点我要说一下，就是啥吧，在MySQL5.7版本之前使用这类sql是可以出现System类型的，但是在5.7版本之后呢，就不会出现这个System类型了，转而是以Const类型来替代，也就是下面这种。

2. Const：mysql能对查询部分进行优化并将其转化为常量，用于primary key或者unique key的所有列与常数比较时，所以表最多只有一个匹配行，读取一次，速度比较快，

   对primary key或者unique key字段进行的查询，就是const

   select * from (select *from film where id=1)tmp;

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

   使用join进行连表查询时，对unique key或者primary key字段进行关联条件

   explain SELECT * from student left join project on student.project_id=project.id

4. Ref：代表在非唯一性索引或者非主键上进行的查询。

   EXPLAIN SELECT * from student where name=‘name1’

5. Ref_or_null，与上面类似，但是可以搜索值为null的行

   EXPLAIN SELECT * from student where name=‘namepro’ or name is null

6. Range：范围扫描，一般在in、between、>、<、>=等情况下使用，使用一个索引来检索给定范围的行。

   EXPLAIN SELECT * from student where id>1

7. Index：即使全表扫描，我们在表中也设置有主键索引，此时会走索引，

   select count(*) from student

8. All：全表扫描

   EXPLAIN SELECT * from student

   一般保证查询至少达到range级别，最好能达到ref。

possible_keys

表示该行查询可能使用到的查询索引，它是理论上的，某些情况下，是与实际用的索引不同的。

key

上面是可能使用到的索引，而这个呢，就是该行实际使用到的索引。

key len

表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

ref

显示关联的字段。如果使用常数等值查询，则显示const，如果是连接查询，则会显示关联的字段。

rows

rows列代表该行查询所涉及到多少行数据，而非最终的结果。

Filered

这个是个百分比，rowsfiltered的值与前表产生交互，比如rows是1000，filtered是10,那么就说明会有10000*0.1=100的值与前表交互，

返回结果的行数占查询的行数的比例

explain SELECT * from student where id=‘1’

Extra

这一列展示的是额外信息。

Using index：表示使用索引，但是select的字段需要设置索引，如果有order by的话，那么order by里面的字段也要设置了索引。

explain select name from student order by name

Using where：使用where语句。

explain select * from student where id > 1;

Using temporary：使用临时表

explain select distinct project_id from student；这种就是Using temporary，因为表中没有distinct project_id这种字段，因此是先建立个临时表，然后对这个临时表来进行去重，这种一般是需要优化的，首先可以使用索引来进行优化。

Using filesort：采用文件扫描对结果进行计算排序，效率很差，对于排序，只有select字段与order by字段都被覆盖的话，才允许使用Using index

Explain select * from student order by name（Using filesort）

总结

以上呢就是关于MySQL的explain执行计划的内容了，虽然他的显示出来的字段是比较多的，但是其实它是有的是重要的，有的则是不那么重要，因此我们其实是没有必要每一列都要彻底搞清楚的。在这里我列几个个人觉得相对重要的属性：id、type、key、ref、extra。如果需要对sql进行优化的话，着重关注这几个就可以。