子查询在MySQL中是怎麽执行的

以一个懵懂无知的青蛙来想像子查询的执行方式..

如果是不相关子查询

mysql> select * from single_table where key1 in (select common_field from single_table2);

先执行子查询select common_field from single_table2得到结果後，再将其当作参数，去执行外层查询
select * from single_table where key1 in(...)

如果是相关子查询

mysql> select * from single_table s1 where key1 in (select common_field from single_table2 s2 where s1.key2 = s2.key2);

要先从外层获得第一笔资料，利用这个值去找到子查询中符合的值，再将符合的值带入外层条件判断是否成立，是的话加入结果集，不是就舍弃，重复此步骤去执行获取第二笔纪录，一直往下。

纯量子查询、行子查询的执行方式，确实如我们所想这麽直观，但事情总不会这麽简单。

in子查询最佳化

先了解什麽是物化表？
对於一个不相关的子查询如下：

mysql> select * from single_table s1 where key1 in (select common_field from single_table2 s2 where key3 = 'a');

之所以无法单纯地得到子查询select common_field from single_table2 s2 where key3 = 'a'的结果後，带入去查询的原因是当结果的资料量很大的时候，效能就会变得很慢@@
因此mysql工程师想到的招式是将结果先存入一个临时表，里面的列就是子查询结果中的列，然後写入的纪录都会被去重。而当然这个表一样也可以像其他的表一样建立索引，加快速度。
这个将子查询结果的纪录保存到临时表的过程称为物化。
方便起见我们把临时表叫做物化表。

物化表转连接

mysql> select * from single_table s1 where key1 in (select common_field from single_table2 s2 where key3 = 'a');

我们重新来看这个叙述，把子查询物化後，我们把物化表的名称叫materialized_table，该物化表储存的子查询结果集的列为m_val。
以物化表的角度来看，就是扫描物化表的每一笔纪录找到值与s1的key1相同的纪录，将其加到最终结果。
因此这个叙述也可以看成是

select * from single_table inner join materialized_table on key1 = m_val;

这是一个内连接，所以用那一个当驱动表都是可以的，因此要分别计算以那个当驱动表所耗费的成本最低，来当作最终方案。

将子查询转为半连接

mysql> select * from single_table s1 where key1 in (select common_field from single_table2 s2 where key3 = 'a');

透过前面的方式将子查询物化转为连接的方式提高了不少效能，但由於还是有临时表的成本，因此工程师思考可不可以在没有物化的情况下直接将子查询转为连接呢？
有的！可以透过半连接的方式，具体的策略有以下五种，有兴趣的人可以在深入研究

• table pullout
• duplicate weedout
• LooseScan
• Semi-join Materialization
• FirstMatch

而当in子查询不符合转为半连接的条件，查询最佳化工具会从下面的两种策略找出成本最低的方式来执行

1. 子查询物化，在执行查询
2. 执行in到exists的转换

mysql在处理带有衍生表的叙述时，优先尝试把衍生表和外层查询进行合并
如果不行，再把衍生表物化掉，然後执行查询。