MySQL大厂面试-各种SQL的执行计划实战分析

每条SQL语句，MySQL都会经过成本和规则的优化，对这个SQL选择对应的一些访问方法和顺序，包括做一些特殊的改写确保执行效率是最优的，然后优化过后，就会得到一个执 行计划。

执行计划，落实到底层，无非就是先访问哪个表，用哪个索引还是全表扫描，拿到数据后如何回表，是否要基于临时磁盘文件做分组聚合或者排序。

简单的单表查询，可能这就一条数据，代表它打算如何访问这一个表。

explain select * from t1

这里直接会扫描表的聚簇索引的叶节点，按顺序扫描过去拿到表里全部数据。

rows=32，说明这个表里就32条数据，全表扫描会全扫出来

filtered=100%，无where过滤条件，所以直接筛选出来的数据就是表里数据的100%占比。

1 多表关联语句

explain select * from t1 join t2

这种关联语句会选择一个表先查询出来数据， 接着遍历每一条数据去另外一个表里查询可以关联在一起的数据，然后关联。

因为是多表关联，其执行计划分为两条，即会访问两个表：

• 第一个表t1，先用ALL方式全表扫描，扫出xxx条数据
• 第二个表访问t2，同样ALL全表扫描，这种多表关联是笛卡尔积，t1表的每条数据都会去t2表全表扫描所有yyy条数据，跟t2表的每条数据都会关联。extra是Nested Loop（MySQL5.7 老版本值，8.0 版本为图中所示的优化），即嵌套循环的访问方式

两条执行计划的id都是1，一般执行计划里，一个SELECT会对应一个id，因为这两条执行计划对应的是一个SELECT语句，所以他们俩的id都是1，是一样。

如果你要是有一个子查询，有另外一个SELECT，那么另外一个SELECT子查询对应的执行计划的id就是2。

2 包含子查询

explain
select *
from citizen
where name in (select name from user)
   or age = 18;

主SELECT语句的WHERE筛选条件是依赖子查询，还有一个自己的WHERE筛选条件。

该SQL里有两个SELECT：

• 主查询SELECT的执行计划的id=1
• 子查询SELECT的执行计划的id=2

第一个执行计划的select_type=PRIMARY，查询类型是主查询。主查询有个where条件age=18，所以其possible_keys包含age索引，但key是NULL且type是ALL，说明没选择age字段索引，而是全表扫描，why？可能MySQL分析成本，使用age字段索引扫描18这个值，几乎就跟全表扫描差不多，可能age字段值几乎都是18，所以最后就选择还不如直接全表扫描。

第二个执行计划select_type=SUBQUERY，即子查询，子查询针对t2表，子查询本身就是个全表查询，但对主查询而言，会使用name in筛选条件，type=index，说明使用了扫描name字段的二级索引，直接扫描name二级索引，来跟子查询的结果集做比对。

3 union SQL

EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2

这是一个典型的union语句，把两个表的查询结果合并起来。

执行计划的：

• 第一、二条，两个SELECT子句对应两个id，分别从t1、t2表里全表扫描

• 第三条，union子句默认把两个结果集合并且去重，所以该执行计划其实就是去重

  所以其table=<union 1,2>，临时表的表名，extra=using temporary，即使用临时表的意思，把结果集放到临时表里进行去重的意思。若用union all，就不会去重。