共计 8543 个字符，预计需要花费 22 分钟才能阅读完成。
这篇文章主要讲解了“explain 都不会用，你还好意思说精通 MySQL 查询优化？”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“explain 都不会用，你还好意思说精通 MySQL 查询优化？”吧！
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 都是比较常见的，还有一些不常见的，作为了解就好：
鸿蒙官方战略合作共建——HarmonyOS 技术社区
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
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 关键词中以 % 开头来判断，不然也会使索引失效：
感谢各位的阅读，以上就是“explain 都不会用，你还好意思说精通 MySQL 查询优化？”的内容了，经过本文的学习后，相信大家对 explain 都不会用，你还好意思说精通 MySQL 查询优化？这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
