Explain简介
Explain关键字是Mysql中sql优化的常用「关键字」，通常都会使用Explain来「查看sql的执行计划，而不用执行sql」，从而快速的找出sql的问题所在。
在讲解Explain之前首先创建需要的「用户表user、角色表role、以及用户角色关系表role_user」作为测试用的表：
- // 用户表
- DROP TABLE IF EXISTS `user`;
- CREATE TABLE `user` (
- `id` int(11) NOT NULL,
- `name` varchar(25) DEFAULT NULL,
- `age` int(11) NOT NULL DEFAULT 0,
- `update_time` datetime DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- INSERT INTO `user` (`id`, `name`, `age`,`update_time`) VALUES (1,'张三',23,'2020-12-22 15:27:18'), (2,'李四',24,'2020-06-21 15:27:18'), (3,'王五',25,'2020-07-20 15:27:18');
- DROP TABLE IF EXISTS `role`;
- CREATE TABLE `role` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `name` varchar(10) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `index_name` (`name`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- INSERT INTO `role` (`id`, `name`) VALUES (1,'产品经理'),(2,'技术经理'),(3,'项目总监');
- DROP TABLE IF EXISTS `role_user`;
- CREATE TABLE `role_user` (
- `id` int(11) NOT NULL,
- `role_id` int(11) NOT NULL,
- `user_id` int(11) NOT NULL,
- PRIMARY KEY (`id`),
- KEY `index_role_user_id` (`role_id`,`user_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- INSERT INTO `role_user` (`id`, `role_id`, `user_id`) VALUES (1,2,1),(2,1,2),(3,3,3);
我们首先执行一条sql：explain select * from user where id =2;，执行后可以看到执行的结果如下：
可以看到这里有12个字段那个且都有对应的值，这就是explain的执行计划，能看懂这个执行计划，你离精通sql优化就不远了，下面就来详细的介绍这12个字段分别表示什么意思。
id字段
id表示执行select查询语句的序号，它是sql执行的顺序的标识，sql按照id从大到小执行，id相同的为一组，从上到下执行。
什么意思呢？例如执行这条sql：explain select * from user where id in (select user_id from role_user);
- +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+
- | 1 | SIMPLE | user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | NULL |
- | 1 | SIMPLE | role_user | NULL | index | NULL | index_role_user_id | 8 | NULL | 3 | 33.33 | Using where; Using index; FirstMatch(user); Using join buffer (Block Nested Loop) |
- +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+
显示出的两者的id都相同，便表示sql的执行从上往下执行，第一条记录对应的是user表，然后第二条记录对应的是role_user表，这种是id相同的情况。
若是id不同，例如执行下面的sql：explain select (select 1 from user limit 1) from role;：
- +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
- | 1 | PRIMARY | role | NULL | index | NULL | index_name | 33 | NULL | 3 | 100.00 | Using index |
- | 2 | SUBQUERY | user | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | Using index |
- +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
就会看到有两条记录，并且两条记录的id会不一样，id越大的就越先执行，可以看到id=2的执行的是user表，也就是子查询部分，最后执行最外层的部分。
「结论：」 这个就是id标识sql的执行顺序，一般在复杂查询中会有多条记录，简单查询只有一条记录，复杂查询中id相同的为一组，执行的顺序是从上往下，而id越大的越先执行；Mysql 8中会存在对子查询进行优化，所以有时候即使是复杂查询，也只有一条记录。
select_type字段
select_type表示查询的类型，也就是对应的是简单查询还是复杂查询，若是复杂查询又包含：「简单的子查询、from子句的子查询、union查询」。下面就分别来看看select_type中的所有查询类型。
simple
simple表示简单查询，不含有任何的复杂查询。
PRIMARY
复杂查询中「最外层的select语句的查询类型就是PRIMARY」，例如执行下面的sql：explain select * from role where id = (select id from role_user where role_id = (select id from user where id = 2));
最外层的select，也就是select * from role where id =？会被标记为PRIMARY类型。
SUBQUERY
在「select或者where中包含的子查询」会被表示为SUBQUERY类型，例如上一句执行的sql中就有两次的子查询为SUBQUERY。
DERIVED
「DERIVED表示的是派生表或者衍生表的意思，在from包含的子查询中会被表示为DERIVED类型」，Mysql会递归执行这些子查询，并且把结果放在临时表中。执行sql：explain select * from (select name from user union select name from role) a where a.name = '张三';
在Mysql 5.7以上的版本中对其做了优化，新增了derived_merge(派生合并)，可以加快查询效率。
UNION
在出现「UNION查询语句中，第二个select的查询语句就会被表示为UNION」：
UNION RESULT
「UNION查询语句的结果被标记为UNION RESULT」，如上面执行的sql：explain select * from (select name from user union select name from role) a where a.name = '张三';
第四行记录中从table字段中可以看出，第四行的记录来源于第二行和第三行<union2,3>，因此一个UNION查询语句的结果就会被标记为UNION RESULT
其它
上面的七个select_type都是比较常见的，还有一些不常见的，作为了解就好：
- DEPENDENT UNION：也表示UNION查询语句中第二个或者后面的语句，但是取决于外面的查询。
- DEPENDENT SUBQUERY：子查询中的第一个select语句，也是依赖于外部的查询。
- UNCACHEABLE SUBQUERY：子查询的结果不能被缓存，必须重新评估外连接的第一行。
table字段
这个很容易看出「table字段表示的是查询的是哪个表」，一个是已经存在的表，比如上面的user、role都是我们自己创建的表，也可以表示衍生表。
比如：UNION RESULT的table字段表示为<union2,3>，也就是查询的是第二行和第三行的结果记录。
type字段
「type字段表示的sql关联的类型或者说是访问的类型」。从这个字段中我们可以确定这条sql查找数据库表的时候，查找记录的大概范围是怎么样的，直接就能体现sql的效率问题。
type字段的类型也是有比较多，主要常见掌握的有以下几个：system、const 、eq_ref 、ref 、range 、index 、ALL。它的性能体现是从高到低，即system > const > eq_ref > ref > range > index > ALL，下面就来详细的说一说这属性。
system
system是const的特例，「表示表中只有一行记录」，这个几乎不会出现，也作为了解。
const
const表示通过索引一次就查找到了数据，一般const出现在「唯一索引或者主键索引中使用等值查询」，因为表中只有一条数据匹配，所以查找的速度很快。例子：explain select * from user where id =2;
eq_ref
eq_ref表示使用唯一索引或者主键索引扫描作为表链接匹配条件，对于每一个索引键，表中只有一条记录与之匹配。例如：explain select * from user left join role_user on user.id = role_user.user_id left join role on role_user.role_id=role.id;
ref
ref性能比eq_ref差，也表示表的链接匹配条件，也就是使用哪些表字段作为查询索引列上的值，ref与eq_ref的区别就是eq_ref使用的是唯一索引或者主键索引。
ref扫描后的结果可能会找到多条符合条件的行数据，本质上是一种索引访问，返回匹配的行。例如：explain select * from user where name = '张三';
range
「range使用索引来检索给定范围的行数据，一般是在where后面使用between、<>、in等查询语句就会出现range」：explain select * from user where id > 2;
index
index表示会遍历索引树，index回避ALL速度快一些，但是出现index说明需要检查自己的索引是否使用正确：explain select id from user;
ALL
「ALL与index的区别就是ALL是从硬盘中读取，而index是从索引文件中读取」，ALL全表扫描意味着Mysql会从表的头到尾进行扫描，这时候表示通常需要增加索引来进行优化了，或者说是查询中并没有使用索引作为条件进行查询：explain select * from user;
possible_keys字段
possible_keys表示这一列查询语句可能使用到的索引，仅仅只是可能，列出来的索引并不一定真正的使用到。
当没有使用索引为NULL时，说明需要增加索引来优化查询了，若是表的数据比较少的话，数据库觉得全表扫描更快，也可能为NULL。
key字段
key字段与possible_keys的区别就是，表示的真正使用到的索引，即possible_keys中包含key的值。
若是想Mysql使用或者忽视possible_keys中的索引，可以使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len字段
表示sql查询语句中索引使用到的字节数，这个字节数并不是实际的长度，而是通过计算查询中使用到的索引中的长度得出来的，显示的是索引字段最大的可能长度。
一般来说在不损失精度的前提下，key_len是越小越好，比如上面的测试表的id为int类型，int类型由4个字节组成：explain select * from user where id =2;
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字节 |
若是索引为字符串类型的时候，实际存储的字符串非常长，已经超出了字符串类型的存储最大长度（768字节），mysql，就会使用类似左前缀索引来处理。
ref字段
ref表示列与索引的比较，表连接的匹配条件，表示哪些列或者常量被用于查询索引列上的值。
rows字段
rows表示估算的要扫描的行数，一般Mysql会根据统计表信息和索引的选用情况，估算出查找记录所要扫描的行数，注意这个并不是实际结果集的行数。
partitions、filtered字段
partitions表示所匹配的分区；filtered表示的是查询表行所占表的百分比。
Extra字段
该字段显示的是sql查询的额外信息，主要有以下几种情况：
Using index
表示查询的列被索引覆盖，这个是查询性能比较高的体现，即所要查询的信息搜在索引里面可以得到，不用回表，索引被正确的使用：explain select id from user where id =2;
假如同时出现了using where，表示索引用于执行索引键值的查找；若是没有出现using where，则表示索引用于读取数据，而非执行查询的动作。
Using where
该属性与Using index相反，查询的列并没有被索引覆盖，where条件后面使用的是非索引的前导列，它仅仅是使用了where条件而已：explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id;
Using temporary
「Using temporary表示使用了临时表存储中间的结果，一般在对结果排序的时候会使用临时表」，例如：排序order by 和分组查询group by。例子：explain select * from (select name from user union select name from role) a where a.name = '张三';
Using filesort
Using filesort表示文件排序，说明Mysql对数据使用了外部的索引进行排序，并没有使用表中的索引进行排序：explain select * from user order by name;
Using join buffer
Using join buffer表示使用连接缓存：explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id;
它强调在获取连接条件时，并没有使用索引，而是使用连接缓冲区来存储中间结果，若是出现该值，一般说明需要添加索引来进行优化了。
Impossible where
Impossible where会出现在where后的条件一直为false的情况下，这种可以忽视，比较少出现：explain select * from user where name = 'hah' and name = 'sfsd';
Select tables optimized away
表示select语句没有遍历表或者索引就返回数据了，比如：explain select min(id) from user;
在Extra字段中还有其它的属性，但是几乎都没见过的，不出现，所以哪些就讲解，有兴趣的可以自己去了解，这里只列出这些常见的。
说了那么多理论总是要实践一下的，下面以user测试表为例进行测试实践。
实践
（1）通过查询 explain select * from user where name ='张三';name字段并没有创建索引。
我们可以通过创建一个联合索引index_name_age_time，来解决：
- alter table user add index index_name_age_time (name,age,update_time) ;
当再次查询的时候，就会使用上了索引：
（2）使用联合索引要遵循「最左前缀法则」，关于最左前缀法则原则的使用，之前我写过一篇详细介绍的文章，可以参考[]。
（3）在使用索引进行查询的时候，不要做任何的函数操作，不然会导致索引失效：例子：EXPLAIN SELECT * FROM user WHERE name = '张三';
但是你在使用的时候，使用了left()函数，如：EXPLAIN SELECT * FROM employees WHERE left(name,2) = '张三';，会导致索引失效。
（4）在数据库的查询中不要使用（！=或者<>）等判条件和is null,is not null、like关键词中以%开头来判断，不然也会使索引失效：
