深入理解-MySql-的-Explain

43次阅读

共计 6243 个字符，预计需要花费 16 分钟才能阅读完成。

相信大部分入门数据库的朋友都是从数据库的“增删改查”学起的。其实，对于很多搞业务的非专业技术人员而言，可能基本的增删改查也够用了，因为目的并不是要写的多好，只要能正确查到自己想要的分析的数据就可以了。

但是，对于一个专业搞数据分析的人而言，可就没那么简单了。这个自己平时跑个小数可能也没啥感觉，但现实工作中当公司业务数据量达到百万甚至千万级以上时，一个查询语句写的好坏所造成的影响就尤为明显了。所以也就不难理解为什么面试的时候面试官喜欢问一些关于优化的问题。

为了了解自己写的 SQL 是好是坏，MySql 提供了 Explain 执行计划 功能。它对优化 SQL 语句尤为的重要，通过它可以看清执行过程的细节，分析查询语句或是结构的性能瓶颈，找到问题所在。

如何使用 Explain？

explain的使用很简单，就是在 select 语句之前增加 explain 关键字 就 ok 了。MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条 SQL。比如这样：

# explain + sql
explain select * from table where a = 1;

Explain 执行计划能做什么？

  • 确定表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

可以看出执行计划给我们提供的信息是非常有帮助的。只有读懂了这些内容，才能定位问题点在哪，进而去解决。下面东哥给大家介绍一下 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` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18');
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (2,'b','2017-12-22 15:27:18');
INSERT INTO `actor` (`id`, `name`, `update_time`) 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;
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0');
INSERT INTO `film` (`id`, `name`) VALUES (1,'film1');
INSERT INTO `film` (`id`, `name`) VALUES (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);
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (2,1,2);
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (3,2,1);

注意：上面三张表中，actor 主键为 id；film 主键为 id，以 name 字段为索引；film_actor 表中 id 为主键，以 film_id 和 actor_id 为联合索引。

执行计划的内容介绍

我们在 Navicat 里随便执行一个查询语句，看看都会返回哪些内容。

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


执行后的结果不是查询的数据而是执行计划的解释，一共有 id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra 这些字段，每个都代表不同的含义，下面详细介绍。

id

id 决定了每个表的加载和读取顺序。比如你写了个复杂的嵌套逻辑，有很多子查询，那每个 select 执行的顺序就可通过 id 序列号观察出来。

原则是：id 值越大越先被执行。id 值相同的按从上到下的顺序执行。id 为 NULL 的最后执行。

1、id 相同

explain select * from film, actor, film_actor where film.id=actor.id and film.id=film_actor.id;

2、id 不同

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

select_type

select 查询的类型主要有三大类：

1、简单类型

SIMPLE：最简单的 select 查询，就是查询中不包含子查询或者 union，表里如一。

explain select * from film where id=1;

2、嵌套类型

PRIMARY、SUBQUERY、DERIVED 这三个是用在有嵌套逻辑的语句中的。

PRIMARY：嵌套查询最外层的部分被标记为 PRIMARY。

SUBQUERY：出现在 select 或者 where 后面中的子查询被标记为 SUBQUERY。

DERIVED：这个其实我理解是 SUBQUERY 的一种特例，只不过出现的位置比较特殊，是在 from 后面的子查询，MySQL 会将子查询结果存放在一个临时表中，称为 派生表，因为这是我们派生出来的，而非原始表。

通过一个例子说明。

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

3、组合类型

组合类型包括 UNION 和 UNION RESULT 两个。

UNION：UNION 前后如果有两个 select，那么把出现在 union 之后的第二个 select 标记为 UNION；如果 UNION 包含在 from 子句的子查询中，外层 select 将被标记为 DERIVED。

UNION RESULT：从 UNION 表获取结果的 select。

通过一个例子说明。

explain select id from actor union all select id from actor;

table

表示正在访问哪个表, 以表的名称出现。

但是有两个特殊的情况：

1）当 from 子句中有子查询（派生表）时，那 table 就会以 < derivedN > 格式出现。因为此时查询所依赖的表是一个我们派生出来的表，即依赖一个 id 为 N 的子查询的。比如：

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


2）当使用 union 时，UNION RESULT 的 table 值为 <union1,2>，1 和 2 表示参与 union 的 select 行 id。比如：

explain select id from actor union all select id from actor;

type

访问类型，表示 MySQL 是如何访问数据的，是全表扫描还是通过索引等？这是考量 sql 查询优化中一个很重要的指标，共分有很多种类型，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，好的 sql 查询至少达到 range 级别，最好能达到ref。下面挑几个常见且比较重要的说一下。

1. system

表里只有一行记录，这个属于 const 类型的特例，一行数据平时很少出现，可以忽略不计。

2. const

表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只需匹配一行数据，所有很快。如果将主键置于 where 列表中，mysql 就能将该查询转换为一个 const。

system和 const 有啥区别呢？看解释不太好理解，举一个例子。

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


这里子查询就是const，而最外层查询则为system，为什么呢？

因为子查询将主键 id 置于 where 中选择，我们知道主键是有唯一性的，所以这个子查询就只返回一行记录，即匹配了一行数据。而外层查询没得选，因为子查询派生表就给了它一行数据，也就是说它要查询的表里就一行数据。因此，system 是表里只有一行数据，const 是从表里选出唯一一条数据，表里可能很多数据。

3. eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。

explain select * from film_actor left join film on film_actor.film_id = film.id;

4. ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。举例如下：

普通索引的简单查询

explain select * from film where name = "film1";


关联表查询，idx_film_actor_id是 film_id 和actor_id的联合索引。这里使用到了 film_actor 的左边前缀 film_id 部分。

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

5. range

只检索给定范围的行，使用一个索引来选择行。key 列显示使用了那个索引。一般就是在 where 语句中出现了 bettween、<、>、in 等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引

explain select * from actor where id > 1;

6. index

Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树。这通常比 ALL 快，因为索引文件通常比数据文件小。（Index 与 ALL 虽然都是读全表，但 index 是从索引中读取，而 ALL 是从硬盘读取）

explain select * from film;


这里用了查找所有 *，但也返回了 index，这是因为这个表里的两个字段都是索引，id 是主键，name 也被定位为索引。

7. all

全表扫描，意味 MySQL 需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了。

explain select * from film_actor;

possible_keys

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

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

key

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

key_len

表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len 是根据表定义计算而得的，不是通过表内检索出的

举例说明：film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成，并且每个 int 是 4 字节。通过结果中的 key_len=4 可推断出查询使用了第一个列：film_id 列来执行索引查找。

explain select * from film_actor where film_id = 2;

ref

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

ref 为常量

explain select * from film_actor where film_id = 2;

ref 为字段

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

最后一列展示额外的信息。有以下几种重要的值，Using filesort ，Using temporary，Using index，Using where Using index，“

1、Using filesort

MySQL 对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说 mysql 无法利用索引完成的排序操作成为“文件排序”。这种情况下一般也是要考虑使用索引来优化的。

explain select * from actor order by name;

2、Using temporary

mysql 需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。常见于 order by 和 group by。

举例如下：
actor.name 没有索引，此时创建了张临时表。

explain select distinct name from actor;

3、Using index

表示相应的 select 操作中使用了 覆盖索引 （Covering Index），避免了访问表的数据行，效率高
如果同时出现 Using where，表明索引被用来执行索引键值的查找
如果没用同时出现 Using where，表明索引用来读取数据而非执行查找动作。

explain select film_id from film_actor where film_id = 1;

关于索引会专门写一篇文章介绍。

参考：

https://blog.csdn.net/belalds…
https://blog.csdn.net/UncleMo…

最后，如果喜欢本篇文章，欢迎点赞和收藏。
更多精彩内容请关注我的微信公众号：Python 数据科学

正文完
mysql
发表至： mysql
2020-06-05
 0
关于mysql:Mysql普通索引和唯一索引的选择分析
关于mysql:数据库-主从库的安装-读写分离负载均衡实现
关于mysql:简单了解-MySQL-中相关的锁
关于mysql:MySQL行级锁解决消费队列中出现的唯一主键错误