EXPLAIN 中的列
接下来我们将详细说明下 EXPLAIN 执行结果每一列的信息。
1、id 列
设计表时通常会设计 id,一般会作为主键,执行计划的结果也不例外,也有 id 列,id 列编号是 SELECT 的序列号,并且 id 的顺序是按 SELECT 出现的顺序增长的。id列越大执行优先级越高,id 相同则从上往下执行,id 为 NULL 最后执行。
MySQL将 SELECT 查询分为简单查询 SIMPLE 和复杂查询 PRIMARY。
复杂查询包括:简单子查询、派生表( FROM 语句中的子查询)、UNION 和 UNION ALL 查询。
简单查询:
复杂查询:
1)简单子查询
EXPLAIN SELECT (SELECT 1 from user LIMIT 1) from user;
2)FROM 子句中的子查询
EXPLAIN SELECT * FROM (SELECT id, count(*) as c from group GROUP BY name) as derived
这个查询执行时有个临时表别名为 derived,外部 SELECT 查询引用了这个临时表
3)UNION 和 UNION ALL 查询
EXPLAIN SELECT * FROM user UNION SELECT * FROM user;
UNION 结果总是放在一个匿名临时表中,临时表不在 SQL 中出现,临时表名为 <union1, 2>,因此它的 id 是 NULL,表明这个临时表是为了合并两个查询结果集而创建的。
跟 UNION 对比,UNION ALL 无需为最终结果而去重,仅是单纯的将多个查询结果集中的记录合并成一个并返回给用户,所以不会使用到临时表,故没有 id 为 NULL 记录。如下所示:
EXPLAIN SELECT * FROM user UNION ALL SELECT * FROM user;
注意点:子查询优化为连接查询
查询优化器可能对子查询进行重写,进而转换为连接查询,查询计划中的两个id值是相同的,如下所示:
EXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM user_group);
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;