当你的数据里只有几千几万，那么 SQL 优化并不会发挥太大价值，但当你的数据里去到了几百上千万，SQL 优化的价值就体现出来了！因此稍微有些经验的同学都知道，怎么让 MySQL 查询语句又快又好是一件很重要的事情。要让 SQL 又快又好的前提是，我们知道它「病」在哪里，而 explain 关键字就是 MySQL 提供给我们的一把武器！
在我们所执行的 SQL 前面加上 explain 关键字，MySQL 就不会真正去执行这条语句，而是模拟优化器执行 SQL 查询语句，最后会输出一系列的指标告诉我们这条语句的性能如何，如下图所示。
mysql> explain select * from student where id = 1 \G****************************************************** id: 1 select_type: SIMPLE table: subject partitions: NULL type: constpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL******************************************************
复制代码
总的来说，explain 关键字可以告诉我们下面这么多信息：
表的读取顺序如何
数据读取操作有哪些操作类型
哪些索引可以使用
哪些索引被实际使用
表之间是如何引用
每张表有多少行被优化器查询
......
今天，我们就来介绍 explain 关键字的各个指标的含义。系好安全带，准备发车了！
为了方便讲解，这里新建了几张表，并初始化了一些数据（建表语句见附录）。这些表的关系如下：
ID 字段
ID 字段的值及其排列顺序，表明 MySQL 执行时从各表取数据的顺序。一般情况下遵循下面两个原则：
对于下面这个例子：
EXPLAIN SELECT teacher.* FROM teacher, teacher_student WHERE teacher_student.student_name = 's001' AND teacher.NAME = teacher_student.teacher_name
复制代码
该例子的输出为：
上面的输出一共有 2 条记录，其 ID 都为 1，这表示其归为一组。对于 ID 相同的组，MySQL 按照顺序从上到下执行，即：先拿 teacher_student 表的数据，再拿 teacher 表的数据。
再来看下面这个例子：
EXPLAIN SELECT * FROM teacher WHERE NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name = 'S002' )
复制代码
该例子的输出为：
上面的输出一共有 3 条记录，其中第 1、2 条的 ID 相同，第 3 条 ID 不同。那么其执行顺序就是 ID 值越大，其越早执行。ID 相同的，按顺序执行。上面的例子，最早拿 teacher_student 表的数据，之后是一个子查询组成的表，最后拿 teacher 表的数据。结合 SQL 分析，这也符合我们的常识。因为我们必须先把子查询的值算出来，因此需要先把 teacher_student 表里的数据拿出来，之后才可以拿去 teacher 表里查询。
select_type 字段
select_type 字段表示该 SQL 是什么查询类型，一共有以下 6 种：
SIMPLE
简单查询，不包含子查询或 union 查询。
-- 查询T001老师都教了哪些学生EXPLAIN SELECT student.* FROM teacher, teacher_student, student WHERE teacher.NAME = 'T001' AND teacher.NAME = teacher_student.teacher_name AND teacher_student.student_name = student.NAME
复制代码
可以看出其 3 个查询都是简单（SIMPLE）查询。因为 ID 相同，所以其查询顺序是按顺序来的。首先从 teacher 表中取出数据，之后从 student 表取出数据，最后 teacher_student 表取数据。
PRIMARY
一般情况下，如果查询中包含了任何复杂的子查询，那么最外层查询会被标记为主查询。
-- PRIMARY 查询哪些老师教授了选修数学课的学生EXPLAIN SELECT * FROM teacher WHERE NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name = ( SELECT student_name FROM student_course WHERE course_name = 'shuxue' ) )
复制代码
在上面的查询中，首先是执行 ID 为 3 的查询，即去 student_course 表取出选修了数学课的学生名字，之后再去进行最外层的查询。可以看到最外层查询的 select_type 为 PRIMARY。
SUBQUERY
在 select 或 where 中包含子查询，那么 select_type 会被标记为 SUBQUERY。以上面的查询为例：
-- PRIMARY 查询哪些老师教授了选修数学课的学生EXPLAIN SELECT * FROM teacher WHERE NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name = ( SELECT student_name FROM student_course WHERE course_name = 'shuxue' ) )
复制代码
在该查询中，where 中包含了子查询，因此在 explain 中有一个 ID 为 3 的查询被标记为 SUBQUERY。
DERIVED
在 FROM 中包含子查询，那么 select_type 会被标记为 SUBQUERY。
UNION
类似包含 union 关键字的会被标记成 UNION 类型，这种查询方式比较少，这里不做深入讲解。
UNION RESULT
类似包含 union 关键字的会被标记成 UNION RESULT 类型，这种查询方式比较少，这里不做深入讲解。
type 字段
type 字段表示访问情况，通常用来衡量 SQL 的查询效率。其值的查询效率从最好到最差分别为：
NULL
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL
NULL
NULL 表示 MySQL 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
explain select max(id) from teacher
复制代码
system
表只有一行记录（等于系统表），这是 const 类型的特列。
出现的情况较少，这里不深入介绍。
const
const 表示该表最多有一个匹配记录。
通常情况下是 SQL 中出现了主键索引或唯一索引。
explain select * from teacher where name = 'T002'
复制代码
上面例子中，teacher.name 字段为唯一索引字段，所以通过该字段只能唯一找到一条记录，因此其 type 类型为 const。
eq_ref
eq_ref 表示主键索引或唯一索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。
与 const 类型非常相似，唯一的区别是 eq_ef 通常出现在联表的情况下，而 const 通常出现在单表情况下。
EXPLAIN SELECT * FROM teacher, teacher_student WHERE teacher.NAME = teacher_student.teacher_name
复制代码
从上面的执行结果可以看出，其首先全表扫描了 teacher_student 表，之后使用 teacher.name 唯一索引去将联合 teacher 表的每一条记录。
要注意的是，eq_ref 这种情况重点在于：读取本表中和关联表表中的每行组合成的一行。 如果并没有关联表中每行这个概念，那么就不会出现 eq_ref 这种类型。例如我在上面的 SQL 中加上 age 为 24 这个条件，即 SQL 为：
EXPLAIN SELECT * FROM teacher, teacher_student WHERE teacher.NAME = teacher_student.teacher_name and teacher.age = 24
复制代码
执行计划变为：
会看到 type 类型都变为 ref 了，eq_ref 消失了。
ref
ref 表示使用了非唯一索引扫描，会返回匹配某个单独值的所有行。
与 const 非常类似，只不过 ref 会匹配到多个记录，而 const 则只会匹配到单个记录。
explain select * from teacher where age = 24
复制代码
age 为普通索引，表中有 2 条记录。
表中数据为：
ref_or_null
类似 ref，但是可以搜索值为 NULL 的行。
explain select * from teacher where age = 24 or age is null
复制代码
当我们增加 age is null 查询条件后，其 type 字段就变成了 ref_or_null。
index_merge
表示使用了索引合并的优化方法。
索引合并指的是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并。
EXPLAIN SELECT * from teacher where id = 1 or age = 24
复制代码
执行计划为：
可以看到使用了 index_merge 的查询类型。在 teacher 表中 id 和 age 都是索引，其将两个字段的索引结果进行合并了。
range
range 表示检索给定范围的行，使用一个索引来选择行，key 列显示使用了哪个索引。
一般就是在你的 where 语句中出现 between、<>、in 等的范围查询。
执行计划为：
上面语句中，我们使用 between 进行范围查询，因此 type 类型为 range。
index
index 表示只遍历索引树，且只从索引树中获取数据。
EXPLAIN SELECT id, age FROM TEACHER
复制代码
上面 SQL 中的 id、age 都是索引字段，可以直接从索引树中读取。因此其 type 字段为 index，表示此次查询数据可以直接从索引树获取到。但是如果查询的字段不在索引树中，那么就是全表扫描了。例如：
EXPLAIN SELECT id, enter_time FROM TEACHER
复制代码
查询 SQL 的 enter_time 字段不是索引，所以上面的查询就变成了全表查询（ALL）。
ALL
ALL 表示该查询将遍历全表以找到匹配行，这是最糟糕的一种查询方式。
table 字段
表示数据来自哪张表
possible_keys 字段
显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用。
key 字段
实际使用到的索引，如果为 NULL，则没有使用索引。
查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在 key 列表。
mysql> explain select * from film_actor where film_id = 2;+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+| 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 4 | const | 1 | Using index |+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
复制代码
key_len 计算规则如下：
字符串
数值类型
tinyint：1 字节
smallint：2 字节
int：4 字节
bigint：8 字节
时间类型
date：3 字节
timestamp：4 字节
datetime：8 字节
其他
如果字段允许为 NULL，需要 1 字节记录是否为 NULL
ref 字段
这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）。
rows 列
这一列是 mysql 估计要读取并检测的行数，注意这个不是结果集里的行数。
Extra 列
这一列展示的是额外信息。
distinct
一旦 mysql 找到了与行相联合匹配的行，就不再搜索了。
mysql> explain select distinct name from film left join film_actor on film.id = film_actor.film_id;+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+| 1 | SIMPLE | film | index | idx_name | idx_name | 33 | NULL | 3 | Using index; Using temporary || 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 4 | test.film.id | 1 | Using index; Distinct |+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
复制代码
Using index
这表示查找某个表的时候，所需要的信息直接从索引就可以拿到，而不需要再访问行记录。
mysql> explain select id from film order by id;+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | film | index | NULL | PRIMARY | 4 | NULL | 3 | Using index |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
复制代码
上面例子中，我只是选择了 id 列，这个列本身是索引，其信息直接在索引树中就可以拿到，因此不需要再访问行记录。
Using where
mysql 服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。
mysql> explain select * from film where id > 1;+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+| 1 | SIMPLE | film | index | PRIMARY | idx_name | 33 | NULL | 3 | Using where; Using index |+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
复制代码
Using temporary
mysql 需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
1. actor.name没有索引，此时创建了张临时表来distinctmysql> explain select distinct name from actor;+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+| 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary |+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
2. film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表mysql> explain select distinct name from film;+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+| 1 | SIMPLE | film | index | idx_name | idx_name | 33 | NULL | 3 | Using index |+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
复制代码
Using filesort
MySQL 中无法利用索引完成的排序操作称为「文件排序」。
在 MySQL 中的 ORDER BY 有两种排序实现方式：
利用有序索引获取有序数据
文件排序
在 explain 中分析查询的时候，利用有序索引获取有序数据显示 Using index ，文件排序显示 Using filesort。至于什么时候使用索引排序，什么时候使用文件排序，这个问题太过于复杂，这里不做深入介绍。
1. actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录mysql> explain select * from actor order by name;+----+-------------+-------+------+---------------+------+---------+------+------+----------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+| 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 2 | Using filesort |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
2. film.name建立了idx_name索引,此时查询时extra是using indexmysql> explain select * from film order by name;+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+| 1 | SIMPLE | film | index | NULL | idx_name | 33 | NULL | 3 | Using index |+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
复制代码
文章转载自：陈树义
原文链接：https://www.cnblogs.com/chanshuyi/p/quick-start-of-mysql-explain.html
评论