你真的理解Explain关键字吗？

大家好，我是栗子为

最近有朋友和我说，他二面被问到

如何做Mysql查询优化，他一下没答上来，我感到很遗憾，都这个时候了，面试官对于MySQL的考查早已不是索引失效、事务、连接等，更想考查在项目中你是如何去优化查询语句，提高查询效率。我一边安慰朋友，一边默默打开以前做的笔记，开始构思这一篇《你真的理解Explain关键字吗》…….

好了，废话不多说，直接上干货，相信看完这篇，你再也不怕面试被问到查询优化了…..

我们通常使用explain关键字来查看sql的执行计划，而不用执行sql语句

举个🌰 先来三张表

user表

id	name	age	update_time
1	张飞	23	2022-05-16 10:52:38
2	关羽	28	2022-05-16 10:52:38
3	刘备	28	2022-05-16 10:52:38

（三兄弟结拜的年龄哦）

role表

id	name
1	大哥
2	二弟
3	三弟

role_user表

id	role_id	user_id
1	3	1
2	2	2
3	1	3

下面执行一条SQL语句看看是什么结果

explain select * from user where id = 2;

结果如下

可以看到共有12个字段，这些字段叫做explain执行计划，下面分别做解释

id

表示SQL语句执行的顺序，按照从大到小的顺序执行，若id相同，则从从上到下

explain select (select 1 from user limit 1) from role;

select_type为subquery的先执行，也就是先执行了子查询操作

拓展：

一般sql语句中出现select 1 from table，其作用是查看表中是否有记录，对比 select * from table和select xxx from table，因为不用查字典表，这三种语句的效率即为select 1 >select xxx >select *

Mysql 8中会存在对子查询进行优化，所以有时候即使是复杂查询，也只有一条记录

select_type

表示查询的类型，也就是对应的是简单查询还是复杂查询

其类型如下

举个🌰

explain select * from (select name from user union select name from role) a where a.name = '张飞';

解释：在union语句中，select name from role其select_type为union，select name from user其select_type为derived，因为属于from中的子查询，最外层查询其select_type即为primary

table

表示这一行的数据是关于哪张表的，可以是存在的表也可以是union result衍生的表

partitions

表示所匹配的分区

type

显示查询使用了哪种类型

possible_keys

表示这一列查询语句可能使用到的索引，仅仅只是可能，列出来的索引并不一定真正的使用到
这一列为NULL就需要我们增加索引来提高查询效率
若表中数据比较少，采用全表扫描方式更快，此字段也可能为NULL

key

实际使用的索引，如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中

举个🌰

explain select id,name from role;

key字段即为覆盖索引

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

又到了举🌰环节！

列	类型	KEY_LEN	备注
id	int	4+1	int为4bytes,允许为NULL,加1byte
id	bigint not null	8	bigint为8bytes
user	char(30) utf8	30*3+1	utf8每个字符为3bytes,允许为NULL,加1byte
user	varchar(30) not null utf8	30*3+2	utf8每个字符为3bytes,变长数据类型,加2bytes
user	varchar(30) utf8	30*3+2+1	utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes
detail	text(10) utf8	30*3+2+1	TEXT截取部分,被视为动态列类型。

ref

表示列与索引的比较，表连接的匹配条件，表示哪些列或者常量被用于查询索引列上的值

rows

表示估算的要扫描的行数，一般Mysql会根据统计表信息和索引的选用情况，估算出查找记录所要扫描的行数，注意这个并不是实际结果集的行数

filtered

表示的是查询表行所占表的百分比

extra

显示sql查询的额外信息

举个🌰

explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id;

解释：三个简单查询，从上到下的顺序执行，先查user表的所有数据，由于第一个where连接条件需要role_user的信息，所以第二个查询role_user表，用到了连接缓存，来存储中间结果，需要添加索引来进行优化，第三个查询role表，根据role_user中的role_id，使用到了主键索引