1 标量子查询、行子查询的执行方式
我们经常在下边两个场景中使用到标量子查询或者行子查询：
SELECT子句中，我们前边说过的在查询列表中的子查询必须是标量子查询。
子查询使用=、>、<、>=、<=、<>、!=、<=>等操作符和某个操作数组成一个布尔表达式，这样的子查询必须是标量子查询或者行子查询。
对于上述两种场景中的不相关标量子查询或者行子查询来说，它们的执行方式是简单的，比方说下边这个查询语句：
SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 ='a'LIMIT 1);
它的执行方式和年少的我想的一样：
先单独执行(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)这个子查询。
然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询SELECT * FROM s1 WHERE key1 = ...。
也就是说，对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询，就当作两个单表查询就好了。
对于相关的标量子查询或者行子查询来说，比如下边这个查询：
SELECT * FROM s1 WHERE
key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);
事情也和年少的我想的一样，它的执行方式就是这样的：
先从外层查询中获取一条记录，本例中也就是先从s1表中获取一条记录。
然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从s1表中获取的那条记录中找出s1.key3列的值，然后执行子查询。
最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
再次执行第一步，获取第二条外层查询中的记录，依次类推～
也就是说对于一开始唠叨的两种使用标量子查询以及行子查询的场景中，MySQL优化器的执行方式并没有什么新鲜的。
2 IN子查询优化
物化表的提出
对于不相关的IN子查询，比如这样：
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 ='a');
我们最开始的感觉就是这种不相关的IN子查询和不相关的标量子查询或者行子查询是一样一样的，都是把外层查询和子查询当作两个独立的单表查询来对待，可是很遗憾的是设计MySQL的大叔为了优化IN子查询倾注了太多心血（毕竟IN子查询是我们日常生活中最常用的子查询类型），所以整个执行过程并不像我们想象的那么简单(>_<)。
其实说句老实话，对于不相关的IN子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率还是蛮高的，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：
结果集太多，可能内存中都放不下～
对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，这就导致：
无法有效的使用索引，只能对外层查询进行全表扫描。
在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。
比如说IN子句中的参数只有两个：
SELECT * FROM tbl_name WHERE column IN (a, b);
这样相当于需要对tbl_name表中的每条记录判断一下它的column列是否符合column = a OR column = b。在IN子句中的参数比较少时这并不是什么问题，如果IN子句中的参数比较多时，比如这样：
SELECT * FROM tbl_name WHERE column IN (a, b, c ..., ...);
那么这样每条记录需要判断一下它的column列是否符合column = a OR column = b OR column = c OR ...，这样性能耗费可就多了。
于是乎设计MySQL的大叔想了一个招：不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：
该临时表的列就是子查询结果集中的列。
写入临时表的记录会被去重。
我们说IN语句是判断某个操作数在不在某个集合中，集合中的值重不重复对整个IN语句的结果并没有啥子关系，所以我们在将结果集写入临时表时对记录进行去重可以让临时表变得更小，更省地方～
小贴士： 临时表如何对记录进行去重？这不是小意思嘛，临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引就好了嘛～
一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。
小贴士： IN语句的本质就是判断某个操作数在不在某个集合里，如果集合中的数据建立了哈希索引，那么这个匹配的过程就是超级快的。 有同学不知道哈希索引是什么？我这里就不展开了，自己上网找找吧，不会了再来问我～
如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。
设计MySQL的大叔把这个将子查询结果集中的记录保存到临时表的过程称之为物化（英文名：Materialize）。为了方便起见，我们就把那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。
物化表转连接
事情到这就完了？我们还得重新审视一下最开始的那个查询语句：
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 ='a');
当我们把子查询进行物化之后，假设子查询物化表的名称为materialized_table，该物化表存储的子查询结果集的列为m_val，那么这个查询其实可以从下边两种角度来看待：
从表s1的角度来看待，整个查询的意思其实是：对于s1表中的每条记录来说，如果该记录的key1列的值在子查询对应的物化表中，则该记录会被加入最终的结果集。画个图表示一下就是这样：
从子查询物化表的角度来看待，整个查询的意思其实是：对于子查询物化表的每个值来说，如果能在s1表中找到对应的key1列的值与该值相等的记录，那么就把这些记录加入到最终的结果集。画个图表示一下就是这样：
也就是说其实上边的查询就相当于表s1和子查询物化表materialized_table进行内连接：
SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;
转化成内连接之后就有意思了，查询优化器可以评估不同连接顺序需要的成本是多少，选取成本最低的那种查询方式执行查询。我们分析一下上述查询中使用外层查询的表s1和物化表materialized_table进行内连接的成本都是由哪几部分组成的：
如果使用s1表作为驱动表的话，总查询成本由下边几个部分组成：
物化子查询时需要的成本
扫描s1表时的成本
s1表中的记录数量 × 通过m_val = xxx对materialized_table表进行单表访问的成本（我们前边说过物化表中的记录是不重复的，并且为物化表中的列建立了索引，所以这个步骤显然是非常快的）。
如果使用materialized_table表作为驱动表的话，总查询成本由下边几个部分组成：
物化子查询时需要的成本
扫描物化表时的成本
物化表中的记录数量 × 通过key1 = xxx对s1表进行单表访问的成本（非常庆幸key1列上建立了索引，所以这个步骤是非常快的）。
MySQL查询优化器会通过运算来选择上述成本更低的方案来执行查询。
如果IN子查询符合转换为semi-join的条件，
查询优化器会优先把该子查询转换为semi-join，然后再考虑下边5种执行半连接的策略中哪个成本最低：
Table pullout
DuplicateWeedout
LooseScan
Materialization
FirstMatch
选择成本最低的那种执行策略来执行子查询。
如果IN子查询不符合转换为semi-join的条件，那么查询优化器会从下边两种策略中找出一种成本更低的方式执行子查询：
先将子查询物化之后再执行查询
执行IN to EXISTS转换。
[NOT] EXISTS子查询的执行
如果[NOT] EXISTS子查询是不相关子查询，可以先执行子查询，得出该[NOT] EXISTS子查询的结果是TRUE还是FALSE，并重写原先的查询语句，比如对这个查询来说：
SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE key1 ='a') OR key2 > 100;
因为这个语句里的子查询是不相关子查询，所以优化器会首先执行该子查询，假设该EXISTS子查询的结果为TRUE，那么接着优化器会重写查询为：
SELECT * FROM s1
WHERE TRUE OR key2 > 100;
进一步简化后就变成了：
SELECT * FROM s1
WHERE TRUE;
对于相关的[NOT] EXISTS子查询来说，比如这个查询：
SELECT * FROM s1
WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.common_field);
很不幸，这个查询只能按照我们年少时的那种执行相关子查询的方式来执行。不过如果[NOT] EXISTS子查询中如果可以使用索引的话，那查询速度也会加快不少，比如：
SELECT * FROM s1
WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.key1);
上边这个EXISTS子查询中可以使用idx_key1来加快查询速度。
对于派生表的优化
我们前边说过把子查询放在外层查询的FROM子句后，那么这个子查询的结果相当于一个派生表，比如下边这个查询：
SELECT * FROM ( SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 ='a') AS derived_s1 WHERE d_key3 ='a';
子查询( SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = 'a')的结果就相当于一个派生表，这个表的名称是derived_s1，该表有两个列，分别是d_id和d_key3。
对于含有派生表的查询，MySQL提供了两种执行策略：
最容易想到的就是把派生表物化。
我们可以将派生表的结果集写到一个内部的临时表中，然后就把这个物化表当作普通表一样参与查询。当然，在对派生表进行物化时，设计MySQL的大叔使用了一种称为延迟物化的策略，也就是在查询中真正使用到派生表时才回去尝试物化派生表，而不是还没开始执行查询呢就把派生表物化掉。比方说对于下边这个含有派生表的查询来说：
SELECT * FROM ( SELECT * FROM s1 WHERE key1 ='a') AS derived_s1 INNER JOIN s2 ON derived_s1.key1 = s2.key1 WHERE s2.key2 = 1;
如果采用物化派生表的方式来执行这个查询的话，那么执行时首先会到s2表中找出满足s2.key2 = 1的记录，如果压根儿找不到，说明参与连接的s2表记录就是空的，所以整个查询的结果集就是空的，所以也就没有必要去物化查询中的派生表了。
将派生表和外层的表合并，也就是将查询重写为没有派生表的形式
我们来看这个贼简单的包含派生表的查询：
SELECT * FROM (SELECT * FROM s1 WHERE key1 ='a') AS derived_s1;
这个查询本质上就是想查看s1表中满足key1 = 'a'条件的的全部记录，所以和下边这个语句是等价的：
SELECT * FROM s1 WHERE key1 ='a';
对于一些稍微复杂的包含派生表的语句，比如我们上边提到的那个：
SELECT * FROM ( SELECT * FROM s1 WHERE key1 ='a') AS derived_s1 INNER JOIN s2 ON derived_s1.key1 = s2.key1 WHERE s2.key2 = 1;
我们可以将派生表与外层查询的表合并，然后将派生表中的搜索条件放到外层查询的搜索条件中，就像这样：
SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.key1 ='a'AND s2.key2 = 1;
这样通过将外层查询和派生表合并的方式成功的消除了派生表，也就意味着我们没必要再付出创建和访问临时表的成本了。可是并不是所有带有派生表的查询都能被成功的和外层查询合并，当派生表中有这些语句就不可以和外层查询合并：
聚集函数，比如MAX()、MIN()、SUM()啥的
DISTINCT
GROUP BY
HAVING
LIMIT
UNION 或者 UNION ALL
派生表对应的子查询的SELECT子句中含有另一个子查询
... 还有些不常用的情况就不多说了哈～
所以MySQL在执行带有派生表的时候，优先尝试把派生表和外层查询合并掉，如果不行的话，再把派生表物化掉执行查询。
网友评论