Mysql实验：快速入门 explain 命令

1. 查看表结构

Mysql 中查看表结构，分析查询语句的命令，有相关工作经验的都能答上来：desc 或 explain，而且这两个命令的效果是相同的。不过，今天的内容中，因为要知道表的存储引擎是啥，所以我们使用的是 show create table 命令。这里我准备了两张表，分别是 myisam_user 和 user。通过命令查看下，我们可以看到 ENGINE 分别是 MyISAM 和 InnoDB。

image-20220625115429080

2. 分析查询语句

查看下表中的数据，当前两张表中都只有一条数据数据，执行 explain select user 后，可看到返回结果。

image-20220625120544554

这里的字段有 10 几个，每个字段都有相应的功能，表格列出了相关的描述。接下来就是围绕着这些字段来展开实验，让我们充分理解分析查询语句过程中如何进行优化。比如说我们要知道 possible_keys 显示可能应用在这张表中的索引，key 是实际使用的索引，如果为 NULL，则没有使用索引。key_len 是使用的索引的长度，在不损失精确性的情况下，长度越短越好。rows 是 MySQL 认为它需要执行的行数。执行行数如果远大于返回行数，就要引起注意，需要优化了。

本次实验主要涉及 select_type、type 和 extra，其它字段不在实验范围，如感兴趣也可留言讨论。

字段	描述
select_type	查询中每个select子句的类型。 可选值：SIMPLE，PRIMARY，UNION，DEPENDENT UNION，UNION RESULT，SUBQUERY，DEPENDENT SUBQUERY，DERIVED
table	查询的数据表。
type	显示连接使用了何种类型。 从最好到最差：system，const，eq_ref，ref，ref_or_null，fulltext，index_merge，unique_subquery，index_subquery，range，index，ALL。
possible_keys	显示可能应用在这张表中的索引，一个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。如果为空，则需要考虑相应的查询语句是否合理。
key	实际使用的索引，如果为 NULL，则没有使用索引。 如果没有选择索引，键是 NULL。要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。
key_len	使用的索引的长度。 在不损失精确性的情况下，长度越短越好。
ref	显示哪个列或常数与 key 一起从表中进行选择。
rows	MySQL 认为它需要执行的行数。 优化查询的关键信息就在这，执行行数如果远大于返回行数，就要引起注意，需要优化了。
extra	包含MySQL 解决查询的详细信息。 这又是一个非常关键的列，信息量有点大，一一举例有点多，可以参考官文。EXPLAIN Extra Information

2.1 Select type

关于 select type 字段，前面的表格中已经列出，具体的意义感兴趣的可以自行搜索下，这里不多解释，因为项目中都会尽量避免复杂写法。这里有几条查询语句展示了每个 type 在什么情况下会出现。

首先简单查询出现的就是 simple 了。

第二条语句展示了多个内容在一起，derived 是衍生表的意思，在 table 这一列出现 derived2 表示的是当前这个 explain 结果中的第二行产生的，所以第二行的 type 就是 derived 了。拉下来第 3 行是 union 了 user 表，它与第 2 行产生 union result 写在了第 4 行中，表名就是 union2,3 了。

第三条语句展示的是子查询 subquery 的情况。

第四条语句展示的是 dependent subquery 和 dependent union 了。

image-20220626095459919

需要注意的是，in 查询与 “=” 号查询是有区别的。

image-20220626095738587

explain select * from user where id = 1;
explain select id from (select * from myisam_user union select * from user) a;
explain select * from user where id = (select id from myisam_user where id = 1);
explain select * from user where id in (select id from user where id = 1 union all (select id from myisam_user where id = 1));
explain select * from user where id in (select id from myisam_user where id = 1);

Type

关于 type 字段，是需要重点讨论的字段。表格列出了所有字段并且是按性能从好到差排好序的。接下来就一个一个的实验并解释。

首先是 system 类型，它是 const 链接特有类型，表示只有一行满足条件(只有一行)，一般来说表示 MyISAM 和 Memory 存储引擎中才会出现，但是在 InnoDB 中也可以写出相关语句，不过是在产生衍生表后出现，我的理解是衍生表已经是 Memory 了。下面是针对 MyISAM 和 InnoDB 中实验结果。system 类型在项目遇到不多。

image-20220626102510818

explain select * from myisam_user;
explain select * from user;
explain select * from user where id = 1;
explain select * from (select * from user where id = 1 limit 1) a;

const 类型，使用用唯一索引或者主键查询，使返回记录最多只有一行记录时类型通常是 const，如前面查询加上 id = 1，需要注意的是数据必须为 not null。

eq_ref 类型出现在要连接多个表的查询计划中，常常是用来比较两个表之间带索引的列。eq_ref 通常使用的是primary key 或 unique index。这是最好的连接类型。为了防止 const 或者 system 类型干扰，在两张表中分别扩充到 2 条以上数据。

image-20220626104537233

select * from user,myisam_user;
explain select * from user,myisam_user where user.id = myisam_user.id;

ref 类型跟 eq_ref 类型的区别在于采用的索引不是主键或者唯一索引。一个好的查询最次也得到ref级别，再低就准备优化吧。

为了验证此类型需要在 name 字段上增加索引。

image-20220626142730063

explain select * from user where name = 'aa';

ref_or_null 经常用于子查询，它和 ref 类似，只是在查询的时候会搜索 null 值的记录。这里我修改了表结构，并将数据增加到了 7 条，防止索引失效。

show create table user;
select * from user;
explain select * from user where name = 'aa' or name is null;

fulltext 是全文索引，要注意的是全文索引的优先级很高，若全文索引和普通索引同时存在时，MySQL 不管代价，优先选择使用全文索引。为了验证此功能，将 name 字段的索引类型修改为 fulltext。

image-20220626114944475

show create table user;
select * from user where id > 3;
explain select * from user where match(name) against('boss') and id > 3;

index_merge 类型表示查询使用了两个以上的索引，最后取交集或者并集，常见 and/or 的条件使用了不同的索引。注意，index_merge看上去是使用了索引，但当表的数量非常大时，其实查询速度还是非常的慢。这里把索引改回普通索引。

image-20220626143608646

show create table user;
explain select * from user where name = 'aa' or id = 2;

unique_subquery 用于带 in 的子查询，子查询返回不重复值唯一值，unique_subquey 通常使用的是primary key 或 unique index。官方文档中表示 unique_subquery 只是 eq_ref 的一个特例，对于下图中这种 in 的语句查询会出现以提高查询效率。由于 MySQL 会对select 进行优化，基本无法出现这个场景，只能用 update 这种语句了。

image-20220626122216356

explain update user set age = 19 where id in (select * from (select id from user where name like 'jo%') a);

index_subquery 和 unique_subquery 类似，只是针对的是非唯一索引。

range 是范围查询，其实就是带有限制条件的索引扫描。

常见的范围查询比如 between and，>，<，like，in 都有可能出现 range。

image-20220626123201656

explain select * from user where id > 10;

index 跟全表扫描类似，只是扫表是按照索引顺序进行。

image-20220626143848406

explain select name from user;

ALL 就是全表扫描，没啥好说的。

Extra

Extra 是查询分析的附加额外信息，这个太多了，有兴趣可以自己看官方文档，只列举一些常见的。

Using index 是覆盖索引。简单来说就是查询的数据在索引只已经存在，直接拿出结果就可以了

image-20220626124444925

explain select name from user where name like 'a%' and name = 'aa';

Using index condition 是索引下推，简单来说就是加上了条件筛选，减少了回表的操作。这里可以看到 age >= 17 的数据有 3 条，但是名字以 a 开头的只有一条，所以我们在查询时，存储引擎会主动帮我们过滤掉不必要的数据。

image-20220626144402626

select * from user where age >= 17;
select * from user where name like 'a%';
explain select * from user where age >= 17 and name like 'a%';

distinct、Using index for group-by distinct 操作是一旦MYSQL找到了与行相联合匹配的行，就不再搜索了。一直没找到相关的 case，但是 distinct 和 group by 都会触发展示 Using index for group-by。

image-20220626133848569

explain select count(distinct name) from user;

Using filesort 使用外部索引文件排序，但是不能从这里看出是内存还是磁盘排序，我们只能知道更消耗性能。

Using temporary 使用了临时表排序。文件排序、临时表排序理论上是我们一定要优化的语句了，当然有特殊需求除外。

image-20220626134239047

explain select distinct name,age from user group by name,age;

参考

• 8.8.2 EXPLAIN Output Format
• EXPLAIN Extra Information
• Understanding the unique_subquery optimization