[大数据]mysql explain实践

前言

使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理SQL语句的。帮助分析查询语句或是结构的性能瓶颈。
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放 入临时表中）。

数据准备

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
    , `remark` varchar(255) DEFAULT 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);


 

实践(版本：5.7.30)

语法

在select 语句前加 explain 关键字。

explain select (select 1 from actor limit 1) from film;

输出：

输出结果属性说明

id 列

id列的编号是 select 的序列号，有几个 select 就有几个id (不是绝对的，可能会优化)，并且id的顺序是按 select 出现的顺序 增长的。MySQL将 select 查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。 复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。 id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

示例 1：

explain select id from (select id from film) as der;
explain select id + 1 as c  from (select id from film) as der;
explain select id ,name as c  from (select id,name  from film) as der;

输出：

说明：3个语句的输出，都只有1条记录。由于第1个select查询的 id 列在派生表中，并且是同一个表，所以优化后，仅显示1条记录。

select_type 列

select_type 表示对应行是简单还是复杂的查询，如果是复杂的查询，又是上述三种复杂查询中的哪一种。

1)、SIMPLE。前面已有示例。

2)、PRIMARY：复杂查询中最外层的 select

3)、SUBQUERY：包含在 select 部分的子查询（不在 from 子句中）

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

5)、UNION：在 union 语句中的第二个union 和随后的 select语句

6)、DEPENDENT SUBQUERY：表示这个subquery的查询要受到外部表查询的影响。where 语句中的子句也是SUBQUERY。

7)、DEPENDENT UNION：UNION 被依赖。

示例 2：

explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

输出：

说明：from 子查询 被优化了，所以未显示。

示例 3：

explain 
select *
from (
select * from actor where id = 1
union all select * from actor where id = 2
) a 

输出：

说明：derived2 表示 是第2条语句产生的派生表。

示例 4：

explain 
select (select name from actor where id = a.actor_id ) as name 
from 
(select * from film_actor where film_id =1 ) a 

输出：

示例 5：

explain 
select *
from film_actor
where actor_id in 
(
select id from actor where id = 1
union all select id from actor where id = 2
) 

结果：

table 列

这一列表示 explain 的一行正在访问哪个表。 当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于

是先执行 id=N 的查询。

partitions 列

如果是分区表，则表示是哪个分区。

type 列

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

• ALL：全表扫描

• index：也是全表扫描。扫描表时按索引次序进行，而不是行（即扫描索引的全部记录，然后再查找记录行）。

  它的主要优点是：避免了排序。最大缺点，按索引次序读表有很大开销。extra显示“USING INDEX”表明使用覆盖索引，不会再扫描数据表。

• range：范围扫描，一个有限制（部分数据）的索引扫描。范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围 的行。

• ref：也叫索引查找，返回所有匹配 某个单值的 行。相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

• eq_ref：一种索引查找，最多只返回一条记录。primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件

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

• const，system：部分优化转成常量。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一

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

• NULL：在优化阶段就能返回值，例如 min，max。即通过数据库记录的统计数据进行查询，不真正查询表。

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

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

示例 6：

explain select min(id ) from film ;

输出：

示例 7：

explain select count(name ) from actor ;  -- name没有在索引中
explain select count(id ) from actor ;    -- id 在索引中，聚集索引。
explain select count(name ) from film ;   --  name 在索引中，非聚集索引。

输出：

示例 8：

explain select * from actor where id > 1 ;

结果：

示例 9：

 explain  select * from actor where id =1;

结果：

示例 9：

explain select * from film_actor left join film on film_actor.film_id = film.id;
explain select * from film where name = "film1";

输出：

possible_keys 列

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

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据 不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

key 列

这一列显示mysql实际采用哪个索引来优化对该表的访问。 如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引， 在查询中使用 force index、ignore index。

key_len 列

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

ref 列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量）， 字段名（例：film.id）

rows 列

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

Extra 列

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

• Using index：查询的列被索引覆盖，并且where筛选条件是索引的前导列，是性能高的表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。对于innodb来说，如果是覆盖索引性能会有不少提高。
• Using where：查询的列未被索引覆盖，where筛选条件非索引的前导列
• Using where Using index：查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据
• NULL：查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引， 但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引。
• Using index condition：与Using where类似，查询的列不完全被索引覆盖，where条件中 是一个前导列的范围；
• Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的， 首先是想到用索引来优化。