explain执行计划

EXPLAIN命令的作用是获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序

explain关键词分析

语法 explain + select语句

EXPLAIN SELECT * FROM `tb_item`

执行结果可以看到执行结束后列的名称（不考虑*==partitiions==*）

环境准备

建立三张表

CREATE TABLE `t_role` (
  `id` VARCHAR(32) NOT NULL,
  `role_name` VARCHAR(255) DEFAULT NULL,
  `role_code` VARCHAR(255) DEFAULT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;


CREATE TABLE `t_user` (
  `id` VARCHAR(32) NOT NULL,
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(96) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `user_role` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `user_id` VARCHAR(32) DEFAULT NULL,
  `role_id` VARCHAR(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ur_user_id` (`user_id`),
  KEY `fk_ur_role_id` (`role_id`),
  CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB DEFAULT CHARSET=utf8;


INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
INSERT INTO `t_user` (`id`, `username`, `password`, `name`) VALUES('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');

INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

explain之id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有2种：

id 相同表示加载表的顺序是从上到下。

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

2. id 不相同表示加载表的顺序是从大到小的。

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

此时可以看到进行子查询或者嵌套子查询的时候id的值越大，表示最里面的查询优先级越高。

explain之select_type

表示 SELECT 的类型，常见的取值，如下表所示：

Simple 简单的select查询，查询中不包含子查询或者UNION。
PRIMARY 查询中若包含任何复杂的子查询，最外层查询标记为该标识，而内部的为SUBQUERY。可以看到子查询的表为user_id,外面的表为t_user，外面的标记为primary，里面的为subquery
Union 若第二个SELECT出现在UNION之后，则标记为UNION。
UNION Result从UNION表获取结果的SELECT。可以看到union联合查询的结果就是union result；

explain之table

展示这一行的数据是关于哪一张表的。

explain之type （重要）

type 显示的是访问类型，是查询计划种较为重要的一个指标，可取值为：

NULL

MySQL不访问任何表，索引，直接返回结果当对查询会话变量或者函数是type是null，此时查询效率是最高的。

SYSTEM

不讨论这种情况，因为如果一张表只有一条数据，那么这一条sql也没有优化的空间，无论怎么查询都是最快的。这是const类型的特例，一般不会出现。

const 通过主键或者唯一键进行匹配并且只返回一行数据

但是这种情况也有例外，当表中的数据较少时，由于查询效率也很快，所以会转化为==all== 两条sql是一样的唯一不同的就是两张表的数据量不一样。如果一张表的数据量比较小，type走const还是all？mysql 服务器会进行评估，如果数据量比较小，直接回表查询所有数据即可。

ref 使用普通索引字段查询

非唯一性索(不能用主键索引和唯一索引查询)引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）。首先我们在t_user表的name字段上面创建一个普通索引：

create index idx_user_name on t_user(name);

使用explain分析sql语句：

explain select * from t_user where name='学生1';

执行结果

eq_ref 关联查询通过主键进行关联并且查询的结果只有1条

使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描。

range 描述区间查询

只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。

这里和const一样分两种情况讨论：

当数据量大的时候 type的类型是range
当数据量小的时候此时和const一样，直接全表查

index 查询索引树就可以将数据查询出来

index与 ALL的区别为 index 类型只是遍历了索引字段，通常比ALL快，ALL是遍历数据文件。

建立索引字段

CREATE INDEX t_user_id_username_name ON t_user(id,username,name);

ALL

全表扫描，性能最低

explain 之key

possible_keys: 显示可能应用在这张表的索引，一个或多个。
key：实际使用的索引，如果为NULL，则没有使用索引。
key_len: 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。注：可能会用到的索引possible_keys存在但是key为nuL这种情况就是索引失效的情况

explain之extra

其他的额外的执行计划信息，在该列展示。

Using where

Sql使用where条件过滤时触发

Using filesort

Sql使用order by时触发 Using filesort说明，得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化。慢的原因就是：排序时会生成一个临时文件，将所有查询的结果通过IO放入文件中，然后在文件中排序，排序结束后在通过IO进行返回，得到最终的结果。

Using temporary

Extr为Using temporary说明，需要建立临时表(temporary table)来暂存中间结果。这类SQL语句性能较低，往往也需要进行优化。典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

比Using filesort好一点的原因就是它不是通过文件的方式，而是通过临时表的形式处理数据。

注：order by或者group by这些都容易触发文件排序临时表操作

Using index

Extra为Using index说明，SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。这类SQL语句往往性能较好。

explain分析执行计划