一本彻底搞懂MySQL索引优化EXPLAIN百科全书 (2)

EXPLAIN执行计划

EXPLAIN 中的列
接下来我们将详细说明下 EXPLAIN 执行结果每一列的信息。

1、id 列

设计表时通常会设计 id,一般会作为主键,执行计划的结果也不例外,也有 id 列,id 列编号是 SELECT 的序列号,并且 id 的顺序是按 SELECT 出现的顺序增长的。id列越大执行优先级越高,id 相同则从上往下执行,id 为 NULL 最后执行。

MySQL将 SELECT 查询分为简单查询 SIMPLE 和复杂查询 PRIMARY。

复杂查询包括:简单子查询、派生表( FROM 语句中的子查询)、UNION 和 UNION ALL 查询。

简单查询:

file

复杂查询:

1)简单子查询

EXPLAIN SELECT (SELECT 1 from user LIMIT 1) from user;

file

2)FROM 子句中的子查询

EXPLAIN SELECT * FROM (SELECT id, count(*) as c from group GROUP BY name) as derived

file

这个查询执行时有个临时表别名为 derived,外部 SELECT 查询引用了这个临时表

3)UNION 和 UNION ALL 查询

EXPLAIN SELECT * FROM user UNION SELECT * FROM user;

file

UNION 结果总是放在一个匿名临时表中,临时表不在 SQL 中出现,临时表名为 <union1, 2>,因此它的 id 是 NULL,表明这个临时表是为了合并两个查询结果集而创建的。

跟 UNION 对比,UNION ALL 无需为最终结果而去重,仅是单纯的将多个查询结果集中的记录合并成一个并返回给用户,所以不会使用到临时表,故没有 id 为 NULL 记录。如下所示:

EXPLAIN SELECT * FROM user UNION ALL SELECT * FROM user;

file

注意点:子查询优化为连接查询

查询优化器可能对子查询进行重写,进而转换为连接查询,查询计划中的两个id值是相同的,如下所示:

EXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM user_group);

file

2、select_type 列

MySQL中优化器中的概念:

物化:

子查询语句中的子查询结果集中的记录保存到临时表的过程称之为 物化(英文名:Materialize),简单理解为存储子查询结果集的临时表称之为 物化表。

也正因为物化表的记录都建立了索引(基于内存的物化表有哈希索引,基于磁盘的有B+树索引),因此通过 IN 语句判断某个操作数在不在子查询的结果集中变得很快,从而提升语句的性能。

半连接 semi-join:

也是跟 IN 语句子查询有关。

通用语句:

SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...

outer_tables 表对 inner_tables 半连接的意思:

对于outer_tables的某条记录来说,我们仅关心在inner_tables表中是否存在匹配的记录,而不用关心具体有多少条记录与之匹配,最终结果只保留 outer_tables 表的记录。

每一个 SELECT 关键字的查询都定义了一个 select_type 属性,知道这个查询属性就能知道在整个查询语句中所扮演的角色。

1)SIMPLE:简单查询。查询不包含子查询 和 UNION。

2)PRIMARY:复杂查询中最外层的SELECT,可参照上面的 UNION 查询语句。

3)SUBQUERY:包含的子查询语句无法转换为 semi-join,并且为不相关子查询,查询优化器采用物化方案执行该子查询,该子查询的第一个 SELECT 就会 SUBQUERY。该查询由于被物化,只需要执行一次。

4)DERIVED:对于采用物化形式执行的包含派生表的查询,该派生表的对应的子查询为 DERIVED。

查询语句如下所示:

EXPLAIN SELECT * FROM (SELECT id, count(*) as c FROM user GROUP BY id) AS derived_u where c>1;

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zyxwsz.html