MySQL Explain语句分析

什么是EXPLAIN？

Explain 命令是查看查询优化器如何决定执行查询的主要方法。学会解释 Explain 命令输出的信息将帮助你了解 MySQL 优化器是如何工作的。

什么时候使用 EXPLAIN?

在工作中，经常要做一些优化工作，比如优化SQL语句。

通常的操作是我们会开启慢查询，让慢查询日志去记录一些执行时间比较长的SQL语句，找出这些SQL语句后，就需要用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有全表扫描、表如何连接及连接顺序等。

所以我们通过对查询语句的分析，可以了解查询语句的执行情况，找出查询语向执行的瓶颈，从而优化查询语句。

EXPLAIN语法

EXPLAIN语句的基本语法如下:

# 使用EXTENED关键字，EXPLAIN语句将产生附加信息。
# select_options 是SELECT语句的查询选项，包括FROM、WHERE等子句。
EXPLAIN [EXTENDED] SELECT select_options

先建表导入数据：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `age` tinyint DEFAULT NULL,
  `height` tinyint DEFAULT NULL,
  `weight` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user (id, name, sex, age, height, weight)
values (1,'liuyang','男', 1, 2, 7);
insert into user (id, name, sex, age, height, weight) 
values (2,'liuyang','女', 2, 1, 8);
insert into user (id, name, sex, age, height, weight) 
values (3,'liuyue','女', 4, 3, 1);
insert into user (id, name, sex, age, height, weight) 
values (4,'liuxing','男', 1, 5, 2);
insert into user (id, name, sex, age, height, weight) 
values (5,'liuxing','女', 5, 6, 7);

使用EXPLAIN语句来分析如下语句:

explain select * from user where age = 1;

让我们了解一下查询结果：

字段	描述
id	该列表示，这是select的查询序列号，id的值越大优先级别越高，越先被执行。如果id相同，则顺序执行。
select_type	该列表示select语句的类型是简单查询还是复杂查询。MySQL将select查询分为简单和复杂查询，复杂查询又分为三类：简单子查询，派生表子查询（FROM 子句中的子查询），以及UNION查询。
table	该列表示这一行的数据是关于哪张表的。
partitions
type	该列表示的是MySQL决定如何查找表中的行。比如：全表扫描、范围查询等
possible_keys	该列显示了查询可以使用哪些索引。如果这列是null，则没有相关的索引。在这种情况下，可以通过检查where子句中的字段，是否适合增加索引来提高查询性能。
key	该列表示查询实际应用到的索引，如果没有选择索引，该列的值是null。想要强制使MySQL使用或者忽视possible_type列中的索引，在查询中使用force index，use index 或者 ignore index。
key_len	该列表示MySQL选择的索引字段按字节计算的长度，如果键是null，则长度为null，注意通过key_len值可以确定mysql将实际使用一个多列索引中的几个字段。
ref	该列表示使用哪个列或常数与索引一起来查询记录
rows	该列显示mysql表中进行查询时必须检查的行数
filtered	该列在使用 EXPLAIN EXTENDED 时出现，显示的是针对表里符合某个条件（where 子句或联接条件）的记录数的百分比所做的一个悲观估算。
Extra	该列显示mysql在处理查询时的详细信息，是不适合出现在其他列显示的额外信息。

对于以上字段信息不同值的解释：

1. select_type

(1) SIMPLE 
SIMPLE表示简单查询，其中不包括UNION查询和子查询。

(2) 如果查询有任何复杂的子部分，则最外层部分标记为 PRIMARY，PRIMARY表示主查询或者
最外层的查询语句，其它部分标记如下：

SUBQUERY：SUBQUERY 表示不在 FROM 子句中的子查询，比如 select 子查询和 where 子查询

DERIVED：DERIVED 表示包含在FROM 子句中的子查询，MySQL 会递归执行并将结果放到一个
临时表中。服务器内部称其为“派生表”，因为该临时表是从子查询中派生来的。

UNION：UNION 表示该关键字后面的那个SQL的查询信息。

UNION RESULT：表示用来从 UNION 的匿名临时表检索结果的select

例如：
mysql> explain select * 
from article 
where id = (select id from article where content  = 'LrQ2Ievq9P');

+----+-------------+---------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
|  1 | PRIMARY     | article | NULL       | const | PRIMARY       | PRIMARY | 4       | const |        1 |   100.00 | NULL        |
|  2 | SUBQUERY    | article | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 10856301 |    10.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
2 rows in set, 1 warning (16.28 sec)

mysql> explain select * from article where id = 6565544 union all select * from article where id = 8484848 ;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | article | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  2 | UNION       | article | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

2. type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL、index、range、ref、eq_ref、const、system、NULL
（从左到右，性能从差到好）

ALL：这就是经常说的全表扫描，通常意味着MySQL将遍历全表以找到匹配的行

index: 这个跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行顺序。
它的主要优点是避免了排序；最大的缺点是要承担按索引次序读取整个表的开销。
如果在 Extra 列中看到 “Using index”, 说明 MySQL 正在使用覆盖索引，
它只扫描索引的数据，而不是按索引次序的每一行。它比按索引次序全表扫描的开销要少很多。

range: 范围扫描就是一个有限制的索引扫描，它开始于索引里的某一点，返回
匹配这个值的行。这比全索引扫描好一些，因为它用不着遍历全部索引。显而易见的范围扫描是
带有 BETWEEN 或在 WHERE 子句里带有 > 的查询。

ref: 这是一种索引访问，它返回所有匹配某个值的行。然而，它可能会找到多个符合条件
的行，因此，它是查找和扫描的混合体。此类索引访问只有当使用非唯一索引或者唯一索引的
非唯一前缀时才会发生。

eq_ref: 类似ref，区别就在使用这种索引查找，MySQL 知道最多只返回一条符合
条件的记录。这种访问方法可以在 MySQL 使用主键或者唯一性索引查找时看到，它会将它们与
某个参考值做比较。MySQL 对这类访问类型的优化做的非常好，因为
它知道无须估计匹配行的范围
或在找到匹配行后再继续查找。

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用
这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，
system是const类型的特例，当查询的表只有一行的情况下，使 system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如，从一个
索引列里选取最小值可以通过单独索引查找完成，不需要再执行时访问表。

3. Extar

Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL
到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，
常见的一些额外信息说明如下：

Using index: 表示相应的select操作中使用了覆盖索引（Covering Index）,
避免回表操作，效率不错！
注意：不要把覆盖索引和index访问类型搞混了。如果同时出现using where，
表明索引被用来执行索引键值的查找；
如果没有同时出现using where，表名索引用来读取数据而非执行查找动作。

Using where: 这意味着 MySQL 服务器将在存储引擎检索行后再进行过滤。
许多 where 条件里涉及索引中的列，当它读取索引时，
就能被存储引擎检验，因此并不是所有带 where 子句的查询都会显示 “Using where”。
有时，“Using where” 的出现就是一个暗示：查询可受益与不同的索引

Using filesort: 如果根据索引列进行排序（order by 索引列）是可以
用到索引的，SQL查询引擎会先根据索引列进行排序，然后获取对应记录的主键 id 执行回表操作，
如果排序字段用不到索引则只能在内存中或磁盘中进行排序操作，MySQL把这种在内存或者
磁盘上进行排序的方式统称为文件排序（英文名：filesort），如果某个查询需要使用
文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort

Using temporary: 许多查询的执行过程中，MySQL会借助临时表来完成一些功能，
比如去重、排序之类的，比如我们在执行许多包含distinct、group by、union
等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部
的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示
Using temporary提示.

Using join buffer: 在连接查询执行过程中，当被驱动表不能有效的利用索引加快
访问速度，MySQL一般会为其分配一块名叫 join buffer的内存块来加快查询速度

impossible where: where子句的值总是false，不能用来获取任何元组

select tables optimized away: 在没有GROUPBY子句的情况下，
基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，
不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct: 优化distinct，在找到第一匹配的元组后即停止找同样值的工作

Using index condition：查找使用了索引，但是需要回表查询数据(也表示索引下推)