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';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 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);
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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://6mz.cn/article/pjoesd.html