MySQL执行计划EXPLAIN详解

本文以MySQL 5.7 Reference Manual为主轴(翻译&取其精华)并结合网文百家之长整理而成，因为笔者水平有限，文中如有不准确之处请包涵，如转载请注明原文出处guocun09-Oraman的日记

创新互联建站专业为企业提供锡山网站建设、锡山做网站、锡山网站设计、锡山网站制作等企业网站建设、网页设计与制作、锡山企业网站模板建站服务，10年锡山做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

基本概念：

EXPLAIN提供SQL语句是怎么样执行的信息，为select,delete,insert,replace,update语句工作。

EXPLAIN为查询语句中使用到的每个table返回一行信息。

MySQL中所有的join方式都是使用nested-loop join

一．详细说明

EXPLAIN Output Columns

列名	解释	说明
id	select标识符	Query Optimizer选定执行计划中查询的序列号。表示查询中执行select子句或操作表的顺序，id值越大优先级越高，越先被执行。id相同，执行顺序由上至下
select_type	select类型	没有子查询或union时都是simple，否则会有primary和union之类的，这里要注意带有uncacheable的类型，表示无法缓存，外层行切换会导致重新计算该select
table	输出行所属的表	表名或，，
partitions	匹配的分区	涉及到表的分区，没有使用分区则是NULL
type	join类型	下面有详细说明
possible_keys	可能被选择的索引	MySQL能在该表中使用哪些index助于查询，如果为空，说明没有可用index
key	实际被选择的索引	实际决定选择的index,如果没有选择index,值为NULL
key_len	被选择的键的长度	MySQL在多部分索引中使用的部分的长度，可能有多个值
ref	需要与索引比较(连接)的列	列名或者const（常数，where id = 1的时候就是const了）
rows	估计要被检验的行数	InnoDB中不一定精确，只是一个估计值
filtered	被表的条件所过滤的行的百分比	估计值
extra	额外信息	附加信息

1.select_type

select_type类型	说明
SIMPLE	简单的select查询，不使用 union及子查询
PRIMARY	最外层的select查询
UNION	UNION中的第二个或随后的select查询，不依赖于外部查询的结果集
DEPENDENT UNION	UNION中的第二个或随后的select查询，依赖于外部查询的结果集
SUBQUERY	子查询中的第一个select查询，不依赖于外部查询的结果集
DEPENDENT SUBQUERY	子查询中的第一个select查询，依赖于外部查询的结果集
DERIVED	用于from子句里有子查询的情况。 MySQL会递归执行这些子查询，把结果放在临时表里
UNCACHEABLE SUBQUERY	结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估
UNCACHEABLE UNION	UNION中的第二个或随后的select查询，属于不可缓存的子查询

2.Join类型(type栏位)

Join类型 (按最优到最差排序)	说明
system	表只有一行(=system表)
const	表最多只有一行匹配，通常用到：PK或Unique index
eq_ref	每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref	使用=或<=>，可以是最左前缀索引或非主键或非唯一键，如果每次只匹配少数行，那会是比较好的
fulltext	全文索引搜索
ref_or_null	与ref类似，但包括NULL 例：SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
index_merge	索引合并，比如一个table中有多个index column在where条件中例：SELECT * FROM ref_table WHERE key_column1=expr1and key_column2=expr2;
unique_subquery	仅仅只是索引查找，取代子查询完全获得更好的效率例：value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery	同上，但替换子查询中的”select non_unique_key_column“
range	index范围检索，key栏位显示使用了哪个索引通常用到：=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN()
index	index全扫描，两种情形： 1.仅仅扫描整个index tree，这时Extra栏位为Using index 2.按照index顺序全表扫描，这时Extra栏位不会出现Using index
all	全表扫描

3.Extra信息(常用附加信息)

Extra信息	说明
const row not found	Table was empty
distinct	查询唯一值，发现到一个匹配的就停止当前搜索
FirstMatch(tbl_name)	The semi-join FirstMatch join shortcutting strategy is used for tbl_name.
No tables used	查询没有from子句，或有from dual子句
No exists	优化了left join，一旦找到了配置left join的行就不再检索，例如： SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
Range checked for each record (index map: N)	没找到理想的index,从前面一个表中找一个行的组合，mysql检查那个index能否range或者index merge方式从表中返回数据。它不是很快，但比没有index要好
Using fliesort	使用排序检索，出现时性能可能不高
Using index	Index scan,不需要回表
Using index condition
Using join buffer	Block Nested Loop, Tables from earlier joins are read in portions into the join buffer, and then their rows are used from the buffer to perform the join with the current table.
Using temporary	Query过程中构造一张临时表，常见order by,group by中。出现时性能可能不高
Using where	有where子句

二.实验

环境准备

CREATE DATABASE `gc` /*!40100 DEFAULT CHARACTER SET utf8 */;

use gc;

CREATE TABLE `emp` (

`emp_no` varchar(20) NOT NULL,

`emp_name` varchar(30) NOT NULL,

`age` int(11) DEFAULT NULL,

`dept` varchar(45) DEFAULT NULL,

PRIMARY KEY (`emp_no`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into emp values ('MW00001','Oraman',30,'1');

insert into emp values ('MW00002','GC',25,'2');

insert into emp values ('MW00003','Tom Kyte',50,'1');

insert into emp values ('MW00004','Jack Ma',40,'3');

insert into emp values ('MW00005','James',33,'4');

CREATE TABLE `dept` (

`dept_no` varchar(45) NOT NULL,

`dept_name` varchar(30) NOT NULL,

`dept_header` varchar(20) DEFAULT NULL,

PRIMARY KEY (`dept_no`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into dept values ('1','DBA','MW00003');

insert into dept values ('2','DEV','MW00002');

insert into dept values ('3','BOD','MW00004');

insert into dept values ('4','Business','MW00005');

例1.

mysql> explain select * from emp where dept='1';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

解释：Simple简单的单表查询，type:all全表扫描，Extra:Using where使用where子句

例2.

mysql> explain select * from emp where emp_no='MW00001';

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

| 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 62 | const | 1 | 100.00 | NULL |

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

解释：Simple简单的单表查询，type:const使用到PK，possible_keys:可能使用到index为PRIMARY，key:实际使用到index为PRIMARY

例3.

mysql> explain select * from emp a,dept b where a.emp_name='Oraman' and a.dept=b.dept_no;

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+

| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |

| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 137 | gc.a.dept | 1 | 100.00 | NULL |

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+

解释：两行id相同都是1，以第一行做为驱动表先执行。

Simple简单的单表查询，第一行type:all全表扫描，第二行type:eq_ref a表与b表连接使用到=且只有一行，ref:gc.a.dept通过a表dept栏位连接b表

例4.

mysql> explain select * from dept b where exists (select * from emp a where age>30 and a.dept=b.dept_no);

+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | PRIMARY | b | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |

| 2 | DEPENDENT SUBQUERY | a | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |

+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

解释：id为2的做为驱动表第2行先执行，select_type：DEPENDENT SUBQUERY子查询并依赖外部查询结果集。第1行select_type：PRIMARY最外层的select

以上几个基本的EXPLAIN例子看懂了吗？是不是很简单，和Oracle的区别请自己领悟了。。

网页题目：MySQL执行计划EXPLAIN详解
文章路径：http://cqcxhl.cn/article/pjoesd.html

重庆分公司，新征程启航

MySQL执行计划EXPLAIN详解

其他资讯