Explain工具介绍

Explain工具介绍

目录

1.Explain工具介绍

1.1.Explain分析示例

1.2.1.type

---

1.Explain工具介绍

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。

注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中。

1.1.Explain分析示例

如下需要创建三个表：

actor表：

SET FOREIGN_KEY_CHECKS=0;

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

INSERT INTO `actor` VALUES ('1', 'a', '2017-12-22 15:27:18');
INSERT INTO `actor` VALUES ('2', 'b', '2017-12-22 15:27:18');
INSERT INTO `actor` VALUES ('3', 'c', '2017-12-22 15:27:18');

film表：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `film`
-- ----------------------------
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 AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `film` VALUES ('3', 'film0');
INSERT INTO `film` VALUES ('1', 'film1');
INSERT INTO `film` VALUES ('2', 'film2');

film_actor表：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `film_actor`
-- ----------------------------
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` VALUES ('1', '1', '1', null);
INSERT INTO `film_actor` VALUES ('2', '1', '2', null);
INSERT INTO `film_actor` VALUES ('3', '2', '1', null);

 1.2.Explain属性详解

-- 走的是全表扫描
EXPLAIN SELECT * from actor;

注：在使用EXPLAIN我们暂时关注type（后续会慢慢补充）

1.1.1.type

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

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL。（在sql优化之后要保证查询打倒range,最好是ref）

注：在介绍这个之前，有一个特殊的情况需要着重关注一下。

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。

例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行的时候访问表。

EXPLAIN SELECT min(id) from film; -- type 为null 表示单独查找索引就能完成，不需要对表有操作

解析：对于film表来说id是主键，这个查询逻辑我直接通过查询主键就可以进行完成，根本就没有查询到数据（所以展示的是Null）。

但是需要注意的是，为Null并不是代表最快，也是需要根据实际情况进行判定（比如数据的多少 ps：假设一个表千万数据量，你认为使用id=1查询快还是全索引扫描取最小值快？）

const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。

用于primary key 或 unique key的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。

system是const的特例，表里只有一条元组匹配时为system。

Explain EXTENDED SELECT * from (select * from film where id = 1) tmp; 

解析：直接按照主键等值的方式进行查询，对于sql解析器来说，你一个表中只有一条数据符合要求，对我来说就是一个const（常量），第二条数据的type是const（常量）

第一条数据的system，我从查询结果中查询数据，但是查询结果只有一条数据，所以对我说类型就是system（也就是为什么说system是const的特例）

eq_ref：primary key或 unique key索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种type。

EXPLAIN SELECT * from film_actor LEFT JOIN film on film_actor.film_id = film.id;

 

 理解：首先看第一条数据，type显示的是all，就代表着会把film_actor表的数据全部查询出来，第二条数据就是根据film_actor中的id去关联film表的id（film的id是主键）。

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

• 简单 select 查询，name是普通索引（非唯一索引）

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;

 

理解：film中的name属性只是个普通索引，并不是主键索引，索引type为ref

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

EXPLAIN SELECT * from actor where id > 1;

 

理解：这个是根据主键进行范围查找，我也用普通的索引进行范围查找了下，最后type是all

index：扫描全表索引，这通常比ALL快一些。

 

注：index是因为film表中所有的属性都有对应的索引。

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

EXPLAIN SELECT * from actor;

 

 （还有其他的属性，后续在进行补充）