MySQL之Explain工具的使用及部分优化

1.Explain工具可以帮助我们观看mysql语句的执行情况，只需要在执行语句前面加explain就行

现使用如下三张表，film表建立了name索引，film_actor建立联合索引`idx_film_actor_id` (`film_id`,`actor_id`)：

CREATE TABLE `actor` (
  `id` int NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `film` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `film_actor` (
  `id` int NOT NULL,
  `film_id` int NOT NULL,
  `actor_id` int NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我们先使用Explain语句试一下，结果如下：

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

可以看到有很多的列，以下是关于这几列的部分讲解：

1.id:select 的序列号，有几个 select 就有几个id,数字越高越优先执行，如果一样则按照先后顺序执行。从图可以知道mysql先查询film表，然后在查询actor表，最后在执行最外面的select语句。

2.select_type列 ：对应行是简单还是复杂的查询。

primary：图中可知primary对应的是复杂查询中最外层的 select 。

derived：包含在 from 子句中的子查询。MySQL会创建一个临时表，然后将结果存放在一个临时表中。

subquery：包含在 select 中的子查询（不在 from 子句中） 。

3. table列 ：表示 explain 的一行正在访问哪个表。

4. type列 ：该列可以知道语句执行的优劣，按照最优到最坏分别是system > const > eq_ref > ref > range > index > ALL ，一般对语句的优化最好保证查询达到range级别，最好达到ref即可。

一般使用主键或者唯一索引与常数比较时候会是system和const级别。primary key 或 unique key 索引的所有部分被连接使用 ，一般是eq_ref，如

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

不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，一般是ref,如

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

idx_film_actor_id是film_id和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;

查询语句中出现in(), between ,> ,<, >= 等操作，一般会是range，如

 explain select * from actor where id > 1;

扫描全索引，一般是扫描某个二级索引会是index,如

 explain select * from film;

全表扫描，扫描聚簇索引的所有叶子节点，会是ALL，这种情况通常需要加索引进行优化，如：

 explain select * from actor;

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

6. key列：这一列显示mysql实际采用哪个索引来优化对该表的访问。 如果没有使用索引，则该列是 NULL。

7. key_len列 ：这一列显示了mysql在索引里使用的字节数。

8. ref列 ：这一列显示了在key列记录的索引中，表查找值所用到的列或常量。

9. rows列：这一列是mysql估计要读取并检测的行数，该行数是mysql的一个估算值。

10. Extra列：这一列展示的是额外信息。常见的有：

Using index:使用覆盖索引

Using where :使用 where 语句来处理结果，并且查询的列未被索引覆盖

Using index condition ：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要建立索引进行优化，如：下面的查询语句name没有索引,用到的是全表扫描

explain select distinct name from actor;--actor.name没有索引，此时创建了张临时表来distinct 

而下面的语句name有索引，级别就从all变成了index,且没有建立临时表

 explain select distinct name from film;--film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表

Using filesort：文件排序，该情况也需要考虑用索引来优化。如下面语句就是文件排序，actor.name未创建索引，进行的是全表扫描，效率较低。该数据比较小使用的是文件排序中的双路排序。（关于文件排序可以看我的另外一篇文章）

explain select * from actor order by name;

而如下语句使用到了索引，type就变成了index，使用了覆盖索引。

 explain select * from film order by name;

Select tables optimized away ：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段，如：

 explain select min(id) from film;

 

 

explain select * from actor;