mysql面试（六）

前言

本章节详细讲解了一下mysql执行计划相关的属性释义，以及不同sql所出现的不同效果

执行计划

一条查询语句经过mysql查询优化器的各种基于成本和各种规则优化之后，会生成一个所谓的
执行计划，这个执行计划展示了这条查询语句具体查询方式。我们可以通过在查询语句之前放一个explain命令来获取这些执行计划信息，比如多表连接的顺序是什么，每个表用什么方式查询，用到哪些索引，从多少条数据中筛选等等信息。
如图：
在这里插入图片描述

输出的这些内容就是执行计划，每个字段代表了不同的信息，下面是这各个字段所标识的不同信息
id：在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id
select_type： SELECT 关键字对应的那个查询的类型
table：表名
partitions：匹配的分区信息
type：针对单表的访问方法
possible_keys：可能用到的索引
key：实际上使用的索引
key_len：实际使用到的索引长度
ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows：预估的需要读取的记录条数
filtered：某个表经过搜索条件过滤后剩余记录条数的百分比
Extra：一些额外的信息
下面我们来详解一下每个字段的具体信息

id

一般是和sql语句中的select对应的，有多少个select，一般情况就会有几个id。看下图，虽然是查询两张表，但其实是同一个select，所以id都是一。相同的情况还有join连接查询。
在这里插入图片描述
但是嵌套查询的时候，查询优化器可能会进行重写，自动转换为连接查询，最终也是用同一个查询。
如图：

还有一种情况是union连接查询，这种比较特殊，会出现三条数据。这是因为除了把两个表的数据查询出来，还需要创建一个临时表来合并数据返回用户，但是由于合并后不会执行多余的动作，所以他的id也是空的。
如图：
在这里插入图片描述

select_type

由于一个查询语句中，不止有一个select关键字，而每个关键字又代表这一个查询语句，每个查询语句中都可能会查询多个表，每个表都要输出一条查询记录，但是对于同一个select关键字中的表来说，id是相同的。
那mysql为每个select关键字代表的查语句定义了一种属性，不同的查询方式使用不同的属性定义。如下：

SIMPLE：Simple SELECT (not using UNION or subqueries) 查询语句中不包含 UNION 或者子查询的查询都算作是 SIMPLE 类型，join连接查询也是这种 SIMPLE 类型
PRIMARY：对于包含 UNION 、 UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的 select_type 值就是 PRIMARY。对比上面union查询的图
UNION：对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的 select_type 值就是 UNION
UNION RESULT：MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT ，例子上边有
SUBQUERY：如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 SUBQUERY 如图：

在这里插入图片描述

DEPENDENT SUBQUERY：如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是相关子查询，则该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY。如图：

在这里插入图片描述

DEPENDENT UNION：在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的 select_type 的值就是 DEPENDENT UNION。如图：
DERIVED：对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED，如图：id为2的就是子查询

在这里插入图片描述

MATERIALIZED：当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的 select_type 属性就是 MATERIALIZED。如图：

在这里插入图片描述
执行计划的第三条记录的 id 值为 2 ，说明该条记录对应的是一个单表查询，从它的 select_type 值为MATERIALIZED 可以看出，查询优化器是要把子查询先转换成物化表。然后看执行计划的前两条记录的 id 值都为 1 ，说明这两条记录对应的表进行连接查询，需要注意的是第二条记录的 table 列的值是，说明该表其实就是 id 为 2 对应的子查询执行之后产生的物化表，然后将 s1 和该物化表进行连接查询

UNCACHEABLE SUBQUERY和UNCACHEABLE UNION不常见，就不说了

物化表

当我们使用in嵌套select语句查询的时候，比如：
select * from user_info where id in (select user from test1)
如果in里面的数据只有几条还好的话，只需要拼接外层查询上就行了
select * from user_info where id in( 1,2,3…)，这样只需要判断id=1 or id=2 or。。。
但是如果数据条数比较多呢，这样一条条判断到什么时候。所以mysql做了一个优化，就是不直接将这种不相关的子查询结果集作为外层的参数，而是将子查询的结果集放入一张临时表中。
临时表中的记录就是子查询的数据，将记录去重后写入。并且为该临时表建立一个哈希索引，但是如果结果集的内容特别大，也会将索引形式转变为B+树索引。
那么，将这个子查询结果集，保存到临时表中的过程，就是称为 物化（Materialize）。为了方便起见，就把存储子查询结果集的临时表称为 物化表。

table

在我们的sql语句中，有的查询一张表，有的可能是多张表联合查询的。那这个字段就用来区分每个表各自的执行计划。
比如我们上面的图中查询的是一张表就是一条数据，那如果两张表的话，如下：
在这里插入图片描述

partitions

没用过，一般情况都是空，不用管

type

代表这每条执行记录的访问方法，不同的访问方法也可以提现出使用的索引级别，有这么多
system ，const ，eq_ref ， ref ，fulltext ，ref_or_null ，index_merge ，unique_subquery ， index_subquery，range ， index ， ALL

system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是 system
const：当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const
eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref
ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref
fulltext：全文索引
ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就可能是ref_or_null
index_merge：通过多个索引合并的方式来进行单表查询
unique_subquery：针对in语句，如果子查询的in可以转换为exists查询，并且子查询是用主键进行等值匹配的话，就是unique_subquery 如图：
ndex_subquery：与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引
range：如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法，比如 > <
index：可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index，比如查询和条件都在索引字段中，但是不符合左匹配原则。
ALL：全盘扫描

possible_keys和key

possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些， key 列表示实际用到的索引有哪些；
因为在查询之前，直接关联到的索引，在执行优化器处理后，某些不算太优的方式就可能被放弃。比如同时关联了两个索引，但是一个索引的值全部都相同，用了还不如不用，那这个索引就可能被启用。
我们需要注意，possible_keys中可能被用到的索引越多，对于查询优化器计算成本时候的性能影响就越大，所以我们应该尽量删除那些用不到的索引。

key_len

当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：

对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占用的最大存储空间就是 100 × 3 = 300 个字节。
如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多1个字节。
对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

ref

当使用索引进行等值匹配查询的时候，这个列展示的就是和索引进行等值匹配的东西是什么类型。
const 代表常量、 eq_ref 、 ref 、 ref_or_null 、unique_subquery 、 index_subquery
也有可能是表名列名等信息。

rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数

filtered

要结合前一个字段rows来看，大概可以理解为，上一个字段预计扫描数据记录行数，和真正要查询出来的数据行数百分比。
比如：select * form user_info where address = “浙江” and name = "a%"这个语句，索引是idx_address。会扫描索引中的200条数据，但是这200条中满足name = “a%” 的只有20条，那么这个filtered的值就是10.0