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