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 的字段来实现。

Midnight