MySQL执行计划
11 Aug 2021 | MySQL |MySQL中的 explain命令可以获取一个查询操作的执行计划，描述MySQL如何执行查询操作、执行顺序，使用到的索引，以及成功返回结果集需要执行的行数。可以帮助我们分析SQL语句，知道查询效率低下的原因，改进查询性能。
explain的限制：
- 不支持存储过程
- 不会显示
MySQL的优化过程 - 不会显示所有执行信息
开始之前，所需要的建表语句如下：
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` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (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;
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` INT(11) NOT NULL,
`film_id` INT(11) NOT NULL,
`actor_id` INT(11) NOT NULL,
PRIMARY KEY (`id`), /*主键*/
KEY `idx_film_actor_id` (`film_id`,`actor_id`) /*普通联合键*/
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);
查看索引：
SHOW INDEX FROM actor;
SHOW INDEX FROM film;
SHOW INDEX FROM film_actor;
explain中的列：
mysql> explain select * from actor\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 100.00
Extra: NULL
1. id 列
表示select标识符，同时表明执行顺序，查询序号即为sql语句执行的顺序。
（1）当id值相同时，按从上到下的顺序执行；
（2）当id不同时，按id从大到小执行；
2. select_type 列
查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。
常用取值有：
SIMPLE：简单查询。
mysql> explain select * from film where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | film | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2）PRIMARY：复杂查询中最外层的 select。
3）SUBQUERY：包含在 select 中的子查询（不在 from 子句中）。
mysql> explain select (select id from actor where id = 1) from actor;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | actor | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | Using index |
| 2 | SUBQUERY | actor | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表。
5）UNION：在 union 中的第二个和随后的 select。
6）UNION RESULT：从 union 临时表检索结果的 select。
mysql> explain select 1 union select 2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3. table 列
输出行所引用的表的名称，有时候并不是真实的表名。
4. partitions 列
其记录与查询匹配的分区，无分区表的值为 NULL。
5. type 列
MySQL决定如何查找表中的行，很重要的一列。
依次从最优到最差分别为：NULL> system > const > eq_ref > ref > ref_or_null > range > index > ALL
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
mysql> explain select max(id) from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
system: 表仅有一行，是 const 类型的特例。不常见。
const : 在整个查询过程中这个表最多只会有一条匹配的行，用到了 primary key 或者unique 索引。表数据在分解执行计划时读取。
mysql> explain select * from film where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | film | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
eq_ref：多表联合查询时，使用primary key 或 unique key作为连接条件。最多只会从该表中返回一条符合条件的记录。
mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
+----+-------------+------------+------------+--------+---------------+-------------------+---------+---------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+-------------------+---------+---------------------------+------+----------+-------------+
| 1 | SIMPLE | film_actor | NULL | index | NULL | idx_film_actor_id | 8 | NULL | 3 | 100.00 | Using index |
| 1 | SIMPLE | film | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test02.film_actor.film_id | 1 | 100.00 | NULL |
+----+-------------+------------+------------+--------+---------------+-------------------+---------+---------------------------+------+----------+-------------+
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者键的最左边的前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
/* name是普通索引 */
mysql> explain select * from film where name = 'film';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | ref | idx_name | idx_name | 33 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
/* film_id 是联合索引的最左前缀 */
mysql> explain select * from film left join film_actor on film.id = film_actor.film_id;
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
| 1 | SIMPLE | film_actor | NULL | ref | idx_film_actor_id | idx_film_actor_id | 4 | test02.film.id | 1 | 100.00 | Using index |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
ref_or_null：类似ref，但是可以搜索值为NULL的行。
mysql> explain select * from film where name = "film1" or name is null;
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | film | NULL | ref_or_null | idx_name | idx_name | 33 | const | 2 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
mysql> explain select * from actor where id > 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
index: 全索引扫描，先读索引，再读实际的行，结果也是全表扫描，主要优点是避免了排序。（索引是排好序的，并且 all 是从硬盘中读的，index 可能不在硬盘上）
mysql> explain select count(*) from film;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化。
mysql> explain select * from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
6. possible_keys 列
这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。
7. key 列
这一列显示mysql实际采用哪个索引来优化对该表的访问。
如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视索引，在查询中使用 force index、ignore index。
8. key_len 列
表示查询优化器使用了索引的字节数， 这个字段可以评估组合索引是否完全被使用，或只有最左部分字段被使用到。 key_len 的计算规则如下:
- 字符串
- char(n): n 字节长度
- varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
- 数值类型:
- TINYINT: 1字节
- SMALLINT: 2字节
- MEDIUMINT: 3字节
- INT: 4字节
- BIGINT: 8字节
- 时间类型
- DATE: 3字节
- TIMESTAMP: 4字节
- DATETIME: 8字节
- 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.
/* film_id 是联合索引的一部分 */
mysql> explain select * from film_actor where film_id = 2;
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | film_actor | NULL | ref | idx_film_actor_id | idx_film_actor_id | 4 | const | 1 | 100.00 | Using index |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
9. ref 列
ref：常见的有：const，func，null，字段名。显示哪个字段或常数与索引 key 一起被使用。
- 当使用常量等值查询，显示
const。 - 当关联查询时，会显示相应关联表的关联字段。
- 如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为
func。 - 其他情况
null。
10. rows 列
rows：以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数。注意，这不是真实的结果集行数，这只是一个估计值。一般来说，值越小越好。
mysql> explain select * from actor\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 100.00
Extra: NULL
11. filtered 列
filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。从优化的角度来说，我们希望过滤的越多越好。
12. Extra 列
Extra ：不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示。
-
Using index我们在相应的
select操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQl优化中理想的状态。
覆盖索引：一条
SQL只需要通过索引就可以返回我们所需要查询的数据。而不必通过二级索引（回表），查到主键之后再通过主键查询整行数据。
/* 下面的查询也会输出Using index的原因是：film表只有两个字段 id 和 name，而id是主键，name是索引，所以通过遍历name的索引树就能得到全部字段 */
mysql> explain select * from film\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
partitions: NULL
type: index
possible_keys: NULL
key: idx_name
key_len: 33
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index
-
Using where表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层再进行后过滤（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为后过滤。查询中含有 WHERE 子句时较常见。
mysql> explain select * from actor where name='b'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where
-
Using temporary表示查询后结果需要使用临时表（或内存，或磁盘）来存储，一般在排序或者分组查询时用到。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
mysql> explain select distinct name from actor\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 100.00
Extra: Using temporary
-
Using filesort:如果查询所需的排序与使用的索引排序不一致，MySQL在取得结果后，还需要按查询所需的顺序对结果进行排序，这时就会出现 Using filesort。这个操作可能在磁盘，也有可能在内存，但是
explain并不会告诉你，所以这种情况下一般也是要考虑使用索引来优化的。
mysql> explain select * from actor order by name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 100.00
Extra: Using filesort
总结
- explain 的四个重要字段：type、key、rows、extra；
- 如果 type 的值为 index 或者 ALL，那么说明该 SQL 性能一般，需要优化；
- 如果 key 的值为 NULL，说明该 SQL 没有使用索引，可以考虑在关键字段上增加索引；
- row 的值代表了进行本次查询时，搜索记录的条数，当这个值特别大的时候，说明该 SQL 语句性能差；
- 如果 Extra 字段的值为 Using filesort 或 Using temporary，也是需要优化的，可以通过调整 order by 或者 group by 的字段来实现。
参考资料
- MySQL之explain详解
- mysql explain详解
- Explain执行计划
- 高性能MySQL
