Mysql Explain 分析

DROP TABLE IF EXISTS `actor`; 
 CREATE TABLE `actor` (
 `id` int(11) NOT NULL, 
 `name` varchar(45) DEFAULT NULL, 
 `update_time` datetime DEFAULT NULL, 
 PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` VALUES ('1', 'a', '2017-12-22 15:27:18');
INSERT INTO `actor` VALUES ('2', 'b', '2017-12-22 15:27:18');
INSERT INTO `actor` VALUES ('3', 'c', '2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`; 
 CREATE TABLE `film` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`name` varchar(10) DEFAULT NULL, 
PRIMARY KEY (`id`), 
KEY `idx_name` (`name`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

正文：

mysql>  explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
在查询中的每个表会输出一行，如果有两个表通过 join 连接查询，那么会输出两行

explain 两个变种

1）explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通

过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有

filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表

进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

 

1）

mysql>  explain extended select * from film where id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | film  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set

mysql>  show warnings;
+-------+------+---------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                     |
+-------+------+---------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`film`.`id` AS `id`,`test`.`film`.`name` AS `name` from `test`.`film` where 1 |
+-------+------+---------------------------------------------------------------------------------------------+
1 row in set

2）

相比 explain 多了个 partitions 字段，如果查询是基于分区表的 话，会显示查询将访问的分区。

2）explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的

话，会显示查询将访问的分区。

explain中的列

接下来我们将展示 explain 中每个列的信息。

接下来我们将展示 explain 中每个列的信息。

1. id列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的

顺序增长的。

id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

2. select_type列

select_type 表示对应行是简单还是复杂的查询。

1）simple：简单查询。查询不包含子查询和union

mysql>  explain select * from film where id = 2;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | film  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set

2）primary：复杂查询中最外层的 select 3）subquery：包含在 select 中的子查询（不在 from 子句中）

4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为

派生表（derived的英文含义）

3. table列

这一列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查

询，于是先执行 id=N 的查询。

当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的

select 行id。

4. type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概

范围。

依次从最优到最差分别为： system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询达到range级别，最好达到ref

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在

索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

mysql>  explain select min(id) from film;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set

const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show

warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多

有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为

systemmysql> explain extended select * from (select * from film where id = 1) tmp;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | film       | const  | PRIMARY       | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+

 

eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合

条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种

type:

mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
+----+-------------+------------+--------+---------------+---------+---------+-------------------------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref                     | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------------------------+------+-------+
|  1 | SIMPLE      | film_actor | ALL    | NULL          | NULL    | NULL    | NULL                    |    1 |       |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY       | PRIMARY | 4       | test.film_actor.film_id |    1 |       |
+----+-------------+------------+--------+---------------+---------+---------+-------------------------+------+-------+
2 rows in set

 

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要

和某个值相比较，可能会找到多个符合条件的行。

1. 简单 select 查询，name是普通索引（非唯一索引）

mysql> explain select * from film where name = 'film1';
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | film  | ref  | idx_name      | idx_name | 33      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set

 

 

关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor

的左边前缀film_id部分。

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定

范围的行

 

mysql>  explain select * from actor where id > 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

 

index：扫描全表索引，这通常比ALL快一些。

mysql> explain select * from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 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 |

ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索

引来进行优化了

 

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

 

 

mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

 

 

 

5. possible_keys列

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中

数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可

以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

 

 

6. key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索

引，在查询中使用 force index、ignore index。

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些

列。

举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，

并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执

行索引查找。

8. ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常

量），字段名（例：film.id）

9. rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

 

10. Extra列

这一列展示的是额外信息。常见的重要值如下：

 

 

2）Using where：使用 where 语句来处理结果，查询的列未被索引覆盖

mysql> explain select * from actor where name = 'a';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set

 

3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范

围；

 

4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行

优化的，首先是想到用索引来优化。

mysql> 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 |    3 | 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 | NULL          | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘

完成排序。这种情况下一般也是要考虑使用索引来优化的。

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 |    3 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引

的某个字段是

 

mysql> explain select min(id) from film;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+