MySQL 执行计划explain各参数含义及示例

目录

explain是什么

explain怎么用

explain参数

id

type

extra

ref

rows

possible_keys

key

key_len

filtered

select_type

table

partitions

---

从前面几篇知道了mysql的体系结构，sql的执行流程，还有索引是怎么加速查询的以及为什么InnoDB要使用B+Tree，接下来从sql执行流程中sql的优化处理到存储引擎执行查询数据阶段来优化sql，其实这个过程优化的就是索引，在优化索引前需要先了解一下explain。各位看到此博客的小伙伴，如有不对的地方请及时通过私信我或者评论此博客的方式指出，以免误人子弟。多谢！

explain是什么能干啥

使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的。

借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。

explain怎么用长啥样

使用explain很简单，只要在要执行的sql前加上explain关键字即可，如：

EXPLAIN SELECT * FROM t_emp;

先看下执行执行计划包含的信息，具体每一列的含义下面详说：

explain参数

id

表示查询中执行select子句或操作表的顺序，它是一个数字，id值越大，越优先执行，id相同，从上到下顺序执行。

type

访问类型，常见type的值及每种值代表的含义如下图：

结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL。

const：表示通过索引一次就找到了，如通过主键和唯一索引的等值查询。

explain select * from t_student where id = '0109bc0f';

ref：非唯一索引，等值匹配，可能有多行命中 ，返回匹配某个单独值的所有行。

alter table t_student add index idx_qq(qq_account) ;

explain select * from t_student where qq_account = '1873561761';

explain select * from  t_student t left join t_student_archive t1 on t.id = t1.student_id; 

eq_ref：唯一索引扫描，对于每一个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引上的 join 查询，对于前表的每一行，后表只有一行命中。

explain select * from t_student_archive t1 left join t_student t on t.id = t1.student_id;

range：索引上的范围扫描，例如：between、in、>、<。

explain select * from t_student where qq_account in('1873561761','1317568010');

index：索引上的全集扫描，与all类型的区别是index类型只扫描索引树，而all扫描的是数据文件，因为索引文件通常比数据文件要小，自然会比all快。

explain select qq_account from t_student;

 

ALL：全表扫描 

drop index idx_qq on t_student;
explain select qq_account from t_student

 

possible_keys：查询过程中可能用到的索引。

key：实际使用的索引。

ken_len：使用的索引的长度。长度越短越好。

extra

一些其他列之外的额外信息，包含MySQL解决查询的详细信息 。常见的返回信息就下面几个，具体信息如下：

Using where：表示 SQL 操作使用了 where 过滤条件。

explain select * from t_student where wx_account = '1873561761';

Using index：只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 或者说 SQL 操作中使用了覆盖索引，避免了访问表的数据行，效率高。如果同时出现Using where，表明索引被用来执行索引键值的查找，如果没有出现Using where，表明索引用来读取数据而非执行查找动作。

alter table t_student add index idx_qq(qq_account) ;

explain select qq_account from t_student where qq_account = '1873561761';

Using union：说明如何为index_merge联接类型合并索引扫描。

explain select * from t_student where qq_account = '1873561761' or  id = '00f0b9c1';

Using filesort：MySQL 对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取。MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

explain select * from t_student order by wx_account ;

Using temporary：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于order by 或 group by。

explain select qq_account from t_student where wx_account = 'sky157488592'
union
select qq_account from t_student where qq_account = '1873561761';

 

extra的参数有很多，但通常用到的也就上面这几个，要尽量避免出现Using filesort和Using temporary，出现Using filesort时说明查询时没有用到索引排序，而是对对数据使用一个外部的文件内容进行了排序，影响查询效率，如使用一个没有索引的列进行排序查询；而出现Using temporary就更严重了，出现了临时表，可能中间查询数据会先拷贝到临时表，返回数据时再将临时表删掉，这是必须要避免的，如使用一个没建索引对列进行分组查询等。

Using index condition: 表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录，即需要回表查询。

如在name,phone,qq_account上建立了联合索引的前提下：

explain select * from t_student where name = '大大卷' and qq_account = '1888888888'

ref

显示索引的哪一列被使用了，或者说哪些列或者常数被用于查找索引列上的值。

explain select * from t_student where id = '0109bc0f';
explain select * from  t_student t left join t_student_archive t1 on t.id = t1.student_id;

 

如上：第一个查询使用了定值 0109bc0f 作为查询条件，因此ref值为const；第二个查询使用了t表的id字段。

rows

根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数。

possible_keys

查询过程中可能用到的索引。

key

实际使用的索引，如果为 NULL ，则没有使用索引。

key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好。

filtered

表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

比如一个表示性别的列，非男即女，一共有10条数据，5男5女，你要查所有性别为男的数据，检索这10行只得到了5行结果，那百分比就为50%，像这种区分度不高的就没必要建立索引，可以通过filtered参数去判断是不是适合建立索引。

select_type

select查询的类型，主要用于区分普通查询、联合查询、子查询等，主要有下面几种类型：

simple

简单的select查询，查询中不包含子查询或union。

primary

查询中包含任何复杂的子部分，最外层查询就会被标记为primary。

subquery

在select或where中包含了子查询，示例同上。

derived

在from列表中包含的子查询被标记为derived(衍生)，mysql会递归执行这些子查询，把结果放在临时表里。

 union与union reslut

若第二个select出现在union之后，则被标记为union，从union表获取结果的select被标记为union reslut。

table

查询涉及的表。

partitions

查询使用到表分区的分区名。

 

补充：

对于上面type参数中的system补充如下：

system：这种类型很少出现，并且自测发现在mysql5.6中还可能出现的在5.7之后就没了。比如我新建了一张表(mysql版本为5.6.16-log)

create table t_test1(
id int primary key,
name varchar(36)
);

进行如下查询：

explain select * from (select * from t_test1 where id = 1)a;

结果中出现了system，结果如下：

同样的查询在mysql版本为5.7.25-log中就没有了system，应该是5.7之后对查询做了优化，原先需要两次查询的，现在只需一次查询。