MySQL执行计划-Explain工具介绍（看了就会）

explain SELECT * FROM a_user WHERE id = 1;
执行结果有12个字段，12个字段分别是以下字段和解释：

        id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

        select_type：查询类型

        table：正在访问哪个表

        partitions：匹配的分区

        type：访问的类型

        possible_keys：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到

        key：实际使用到的索引，如果为NULL，则没有使用索引

        key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

        ref：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

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

        filtered：查询的表行占表的百分比

        Extra：包含不适合在其它列中显示但十分重要的额外信息

3、Explain 一图详解

4、Explain 详解（带案例-案例SQL在最后）

4.1 id 列

4.1.1 id相同

--执行顺序从上至下
explain SELECT * FROM a_user,a_user_role WHERE a_user.id = a_user_role.user_id;
-- 读取顺序：a_user > a_user_role

4.1.2 id不相同

-- 如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行
explain SELECT a_user.* FROM a_user WHERE a_user.id = (SELECT a_user_role.user_id FROM a_user_role WHERE a_user_role.role_id = 3);
-- 读取顺序：a_user_role > a_user

4.1.3 id有相同，有不相同的

-- id如果相同，可以认为是一组，在一组中的顺序是从上往下顺序执行

-- 以不同组为单位，组id值越大，优先级越高，越先执行
explain 
SELECT * FROM a_user,a_user_role WHERE a_user.id = a_user_role.user_id
union
SELECT * FROM a_user,a_user_role WHERE a_user.id = a_user_role.user_id;
-- 读取顺序：2.a_user > 2.user_role > 1.a_user > 1.user_role

4.2 select_type列(查询类型)

4.2.1 SIMPLE (简单查询)

简单查询：不包含子查询或者Union查询
EXPLAIN SELECT * FROM a_user,a_user_role WHERE a_user.id = a_user_role.user_id;

4.2.2 PRIMARY (子查询中最外层的查询)

查询中若包含任何复杂的子部分，最外层查询则被标记为主查询
explain SELECT a_user.* FROM a_user WHERE a_user.id = (SELECT a_user_role.user_id FROM a_user_role WHERE a_user_role.role_id = 3);

4.2.3 SUBQUERY(在select 或 where中的子查询)

在select或where中包含子查询

explain SELECT a_user.* FROM a_user WHERE a_user.id = (SELECT a_user_role.user_id FROM a_user_role WHERE a_user_role.role_id = 3);

4.2.4 derived (衍生表)

在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

备注：MySQL5.7+ 进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率
-- 先关闭优化
set session optimizer_switch = 'derived_merge=off';
explain select (select 1 from a_user where id = 1) from (select * from a_user_role where id = 1) der;

-- 执行完之后再将优化开启
set session optimizer_switch = 'derived_merge=on';

4.2.5 union

在union中的第二个和随后的select

explain 
SELECT * FROM a_user as a_user_1
union
SELECT * FROM a_user as a_user_2
union
SELECT * FROM a_user as a_user_3;

4.2.6 union result

从 union 临时表检索结果的 select

explain 
SELECT * FROM a_user as a_user_1
union
SELECT * FROM a_user as a_user_2
union
SELECT * FROM a_user as a_user_3;

4.3 table 列

数据来自哪张表

当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。

当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。
explain 
SELECT * FROM a_user as a_user_1
union
SELECT * FROM a_user as a_user_2
union
SELECT * FROM a_user as a_user_3;

4.4 type (访问类型)

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

依次从最优到最差分别为：

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

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

4.4.1 NULL

mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
explain select min(id) from a_user;

4.4.2 system

表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略

4.4.3 const

表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量。
explain select * from a_user WHERE id = 1;

4.4.4 eq_ref

primary key(主键索引)或unique key(联合索引)索引的所有部分被连接使用,最多只会返回一条符合条件的记录。这可能是在const之外最好的联接类型了，简单的 select 查询不会出现这种type。
-- 被关联的表使用主键关联
EXPLAIN SELECT a_user_role.* FROM a_user_role left join a_user on a_user.id = a_user_role.user_id;

4.4.5 ref

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

情况1：简单的select查询，username是非唯一索引
-- 创建一个普通的索引
CREATE INDEX inx_username ON a_user (username);
explain select * from a_user where username = '王五';
情况2：联合索引
-- 创建一个联合索引
CREATE INDEX idx_user_role_id ON a_user_role (user_id,role_id);

-- 这两个sql都是ref级别,只不过使用索引的长度不同(后面详解),但是要遵守最左原则,就是 user_id一定要有
EXPLAIN select * from a_user_role WHERE user_id = 2;
EXPLAIN select * from a_user_role WHERE user_id = 2 and role_id = 2;

4.4.6 ref_or_null

类似ref，不仅想找到某个索引的某个值，还想把该列的null值也找出来;

SQL后面跟着 OR 列 is null
-- username 已有索引
-- CREATE INDEX inx_username ON a_user (username);
EXPLAIN select * from a_user where username = '王五' or username is null;

4.4.7 index_merge(索引合并)

使用了组合索引(也就是多个索引的结果集合并)

-- 下面这个sql 会被优化成走两个索引
-- select * from a_user where id = 1 和 select * from a_user where username = '王五'
EXPLAIN select * from a_user where id = 1 or username = '王五';

4.4.8 range(范围索引)

范围扫描通常出现在 in(), between ,> ,= 等操作中。使用一个索引来检索给定范围的行。
EXPLAIN select * from a_user where id > 1;

4.4.9 index

扫描全表索引(某个索引树)，这通常比ALL快一些
EXPLAIN select id from a_user;

4.4.10 all

即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常大部分情况下这需要增加索引来进行优化了。
EXPLAIN select * from a_user;

4.5 possible_keys 列

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

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

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

4.6 key 列

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引,则该列是 NULL。

4.7 key_len 列

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

key_len计算规则如下:

字符串
char(n)：n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n+2
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节　
时间类型
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为NULL。索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

举例说明：

a_user_role 的联合索引idx_user_role_id 由 user_id 和 role_id两个bigint组成的,一个bigint占8个字节。
EXPLAIN select * from a_user_role WHERE user_id = 2;
EXPLAIN select * from a_user_role WHERE user_id = 2 and role_id = 2;

4.8 ref列

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

4.9 rows 列

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

4.10 extra 列

展示额外信息，但是十分重要

4.10.1 Using index

使用覆盖索引，查询结果就是索引所在列，避免访问了表的数据行。
EXPLAIN select id from a_user WHERE id = 2;

4.10.2 Using where

使用where语句来处理结果，本人理解为，查询条件没有走索引或者是没有索引。
DROP INDEX inx_username ON a_user;
EXPLAIN select * from a_user WHERE username = '123';

CREATE INDEX inx_username ON a_user (username);

4.10.3 Using temporary

mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
-- 先删除表中的索引
-- DROP INDEX <索引名> ON <表名>

DROP INDEX inx_username ON a_user;
explain SELECT distinct username from a_user;
-- 再将索引添加回来
CREATE INDEX inx_username ON a_user (username);
explain SELECT distinct username from a_user;

4.10.4 Using filesort

将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

a_user.username未创建索引，会浏览a_user整个表，保存排序关键字username和对应的id，然后排序username并检索行记录
-- 删除索引
DROP INDEX inx_username ON a_user;
-- 根据 username排序
explain SELECT id,username from a_user order by username;

-- 将索引添加回来
CREATE INDEX inx_username ON a_user (username);

4.10.5 Select tables optimized away

使用某些聚合函数（比如 max、min）来访问存在索引的某个字段
explain select min(id) from a_user;

4.10.6 impossible where

where子句的值总是false，不能用来获取任何数据。
explain select * from a_user WHERE username = '张三' and username = '李四';

5、演示数据SQL

CREATE TABLE `a_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `username` varchar(50) DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';


CREATE TABLE `a_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(20) NOT NULL COMMENT '角色名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='角色';

CREATE TABLE `a_user_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `role_id` bigint(20) NOT NULL COMMENT '角色id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户权限表';

INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (1, '张三', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (2, '李四', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (3, '王五', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (4, '王五', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (5, '15010480559', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (6, 'liulongying', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (7, 'dutianyu', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (8, 'zhuyanlin', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (9, NULL, '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (10, 'admin', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (11, 'guodianwei', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (12, 'zhaozhengxing1111', '$2a$10$EZCI.Ysb7hWhUQ.aYZqpK.Mg5VWg/99eU1ldheTR/seGRolXoqudO');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (13, 'jianghongyu', '$2a$10$XGfOgLOUN9urcbVmK4XuXuv.hipxfmaEhEGibZkZXhmbh4r/eFAdO');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (14, 'cuiqingqing', '$2a$10$DEZPRouyJqZAVG2qHnPMMeH6ztfPAF7nhIQTaTg63hZ5.5//F/cXO');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (15, 'hepengcong', '$2a$10$a/DWtXdHFoMdSm1V2LCPvuTH3qBe0tfTOdFa26EVl1whghnCpgzFy');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (16, 'jianghongyu1', '$2a$10$XG');

INSERT INTO `a_role`(`id`, `name`) VALUES (1, '管理员');
INSERT INTO `a_role`(`id`, `name`) VALUES (2, '子管理员');
INSERT INTO `a_role`(`id`, `name`) VALUES (3, '部长');
INSERT INTO `a_role`(`id`, `name`) VALUES (4, '组长');

INSERT INTO `a_user_role`(`id`, `user_id`, `role_id`) VALUES (1, 1, 1);
INSERT INTO `a_user_role`(`id`, `user_id`, `role_id`) VALUES (2, 1, 2);
INSERT INTO `a_user_role`(`id`, `user_id`, `role_id`) VALUES (3, 2, 3);
INSERT INTO `a_user_role`(`id`, `user_id`, `role_id`) VALUES (5, 2, 4);
INSERT INTO `a_user_role`(`id`, `user_id`, `role_id`) VALUES (4, 3, 4);