Mysql深度讲解 – 派生表

前言

Mysql在查询语句中一般会使用自建表，这种自建表有临时表，派生表以及在之前的子查询优化【子查询优化】的博客里面介绍的物化表。本篇就对遗漏的派生表做一个介绍，作为Mysql知识的补充。更多Mysql调优内容请点击【Mysql优化-深度讲解系列目录】。

派生表

派生表是一般是一个查询中的子查询结果集，它和临时表的区别在于：用户无法看到临时表，仅仅能够通过explain关键字查找到一个sql的某些步骤使用了临时表。而派生表则是有名字的临时表，这个名字就是用户在sql中赋予的，比如：

select * from (select a, b from t1) as t;

上面这个sql，子查询是放在from后面的，这个子查询的结果相当于一个派生表，表的名称是t，有a，b两个字段。

派生表物化

我们首先回忆一下什么叫物化：在mysql中，不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里，这个将子查询结果集中的记录保存到临时表的过程称之为物化Materialize。那么将派生表的结果集写到一个内部的临时表中，然后就把这个物化表当作普通表一样参与查询的过程，就叫做派生表物化。

派生表的物化策略

在对派生表进行物化时，使用了一种称为延迟物化的策略。也就是在查询中真正使用到派生表时才会去尝试物化派生表，而不是还没开始执行查询就把派生表物化掉。比如：

select * from (select * from t1 where a = 10) as derived1 inner join t2 on derived1.a = t2.a where t2.a =10;

如果采用物化派生表的方式来执行这个查询的话，那么执行时首先会到t1表中找出满足t1.a = 10的记录。如果找不到，说明参与连接的t1表记录就是空的，所以整个查询的结果集就是空的，所以也就没有必要去物化查询中的派生表了。

派生表的优化(消除)

出现派生表并不是一个好的查询方案，因为派生表本质上也是临时表，如果过大也许不得不在磁盘上创建，这样的创建和访问都是需要消耗IO资源的。因此如果能够通过优化把派生表消除也就变成了一种优化手段。有些时候Mysql的查询优化器会自动优化sql，并且把派生表合并到外层查询中，比如：

select * from (select * from t1 where a = 1) as t;

将会被优化为：

select * from t1 where a = 1;

在使用explain关键字查询执行计划的时候，执行方案是一致的。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	NULL

再比如更复杂一些的情况：

select * from (select * from t1 where a = 1) as t inner join t2 on t.a = t2.a where t2.b = 1;

我们可以将派生表与外层查询的表合并，然后将派生表中的搜索条件放到外层查询的搜索条件中，优化为如下sql：

select * from t1 inner join t2 on t1.a = t2.a where t1.a = 1 and t2.b = 1;

这样通过将外层查询和派生表合并的方式成功的消除了派生表。

派生表无法被消除的情况

并不是所有带有派生表的查询都能被成功的和外层查询合并，当派生表中有这些语句就不能够和外层查询合并：

• 含有聚集函数，比如MAX()、MIN()、SUM()之类的
• 使用DISTINCT去重关键字
• 使用GROUP BY分组关键字
• 使用HAVING关键字
• 使用LIMIT关键字
• 使用UNION 或者 UNION ALL关键字
• 派生表对应的子查询的SELECT子句中含有另一个子查询

临时表，派生表，物化表的区别

临时表，派生表，物化表这三个其实是一样的东西，只不过所处环境不同名字不一样。临时表是Mysql自己生成的表，比如UNION RESULT就是临时表，是名字都不配有的表。当用户赋予临时表名字的时候，就变成了派生表。但无论是临时表还是派生表，空间都比较小，查询起来比较方便，可以放在内存中。一旦两种表过大，必须加上索引，就被称为物化表。物化表有没有名字都无所谓，只要被生成了索引就是物化表，其中物化表又可以分为内存物化表(哈希索引)和磁盘物化表(B+树索引)。

总结

本篇博客基本上详细解析了派生表是什么，该如何优化，以及优化的限制条件等等。通过上述的分析，MySQL在执行带有派生表的时候，优先尝试把派生表和外层查询合并掉，如果不行的话，再把派生表物化掉执行查询。这个逻辑同样也可以作为我们自己写sql语句的一个参考。

附：本例中构建的表：

create table t1(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=InnoDB;
create index idx_t1_bcd on t1(b,c,d);

insert into t1 values(12,1,2,4,'a');
insert into t1 values(6,4,5,4,'b');
insert into t1 values(9,1,1,1,'c');
insert into t1 values(1,6,7,4,'d');
insert into t1 values(15,2,2,5,'e');
insert into t1 values(7,9,3,6,'f');
insert into t1 values(4,2,1,7,'g');
insert into t1 values(3,3,3,3,'h');
insert into t1 values(10,5,5,5,'ss');

create table t2(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=InnoDB;

insert into t2 values(1,6,7,4,'d');
insert into t2 values(4,2,1,7,'g');
insert into t2 values(3,3,3,3,'h');