小白眼中的子查询执行方式
不相关子查询执行方式
先单独执行子查询，获取子查询的结果集
再将得到的子查询结果集作为外层查询的参数，再执行外层查询
相关子查询执行方式
先从外层查询中获取一条记录
然后从获取的这条记录中拎出子查询中涉及的值，然后执行子查询
最后根据子查询的查询结果集来检测外层查询 WHERE 子句的条件是否成立，如果成立，就将外层查询的那条记录加入到结果集中，否则就丢弃
重复执行步骤1，获取第二条外层查询中的记录，以此类推
标量子查询、行子查询的执行方式
不相关标量子查询或者行子查询执行方式
SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)
首先单独执行子查询，获取子查询查询结果集
将子查询结果集作为外层查询的参数
执行外层查询
总结：包含不相关的标量子查询或者行子查询的 SQL 来说，MySQL 会分别独立执行子查询和外层查询，很简单就是两个单表查询
相关标量子查询或者行子查询执行方式
SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1)
先从外层查询中获取一条记录
然后从这条记录中拎出子查询中涉及的值，然后执行子查询
最后根据子查询的结果集来检测外层查询 WHERE 子句的条件是否成立，如果成立就将外层查询的这条记录加入到结果集中，否则就丢弃
再回到步骤1获取外层查询的下一条记录，直到外层查询中获取不到记录为止
在使用标量子查询或者行子查询的场景中，MySQL 的执行方式并没有什么新鲜的，与小白的理解是一致的
IN 子查询优化
IN 子查询是最常用的子查询
物化表的提出
将 IN 子查询的结果集写入到一个临时表中
该临时表的列就是 IN 子查询结果集中的列
写入临时表的记录会被去重，这里去重的目的是让临时表尽量小，节省空间
由于 IN 子查询结果集一般不会很大，所以会建立基于内存的 MEMORY 临时表，而且会为该内存临时表建立哈希索引，判断匹配过程会非常快
如果子查询结果集很大，临时表类型会转为基于磁盘的存储引擎来保存记录，索引类型也相应地转为 B+ 树索引
物化：将子查询结果集中的记录保存到临时表的过程就叫物化
物化表：那个存储子查询结果集的临时表就叫物化表
物化表也是有索引的：基于内存就有哈希索引，基于磁盘就有 B+ 树索引
注意，相关子查询不是一个独立的查询，所以不能进行物化操作
物化表转连接
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a')
SELECT s1.* FROM s1 INNER JOIN 物化表 ON s1.key1 = 物化表.m_val
这两个 SQL 在语义上是一样的，所以 IN 子查询可以重写为内连接形式
查询优化器会分别分析不同连接顺序的执行成本
物化子查询时需要的成本
扫描驱动表的成本：只访问一次
被驱动表访问成本：访问多次
选择成本最低的连接顺序执行
将子查询转换为半连接
半连接概念解释
半连接 semi-join 的定义背景：在语义上理解 IN 子查询表达式的作用：外层查询的一条记录在结果集中要么不出现，要么就是只出现一次，这个与转为正常连接的 SQL 是有差异的地方，如果我们将 IN 子查询直接粗暴地转为连接查询，那么结果集中外层表的记录可能会重复出现，所以这时就引出来半连接的概念
将 s1 表与 s2 表进行半连接的语义为：对于 s1 表中的某条记录来说，我们只关心 s2 表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集只保留 s1 表的记录
半连接是 MySQL 内部采用的一种执行子查询的方式，我们用户是不能显式操作的
半连接实现方案
消除半连接结果集中重复值的方案
Table Pullout 子查询中的表上拉
SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a')
SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = 'a'
当子查询的查询列表中只有主键或者唯一索引列时，可以直接将子查询中的表上拉到外层查询的 FROM 子句中，并将子查询中的搜索条件合并到外层查询的搜索条件中，表上拉的核心原因是 IN 子查询的结果集数据没有重复值
Duplicate Weedout 重复值消除
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a')
s1 表的单条记录在 s2 表中可能存在多条匹配的记录，所以就有可能被多次添加到最后的结果集中
Duplicate Weedout 方案是建一个临时表，这个临时表只存 s1 表的主键，这样每当 s1 表中的某条记录加入结果集之前，先将这条记录的主键加到临时表中，如果临时表能够添加成功，说明这个记录之前没被加过结果集，那么现在就加到最后的结果集中，反之，不加直接丢弃
LooseScan 松散扫描
SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b')
当 IN 子查询走了某个普通二级索引，并且这个子查询的查询列表中只有这个索引的索引列，那么再将 IN 子查询转为半连接查询后，就可以将这个子查询中的表作为驱动表，注意这里虽然也是扫描索引，但是只取键值相同的第一条记录去执行匹配操作
Semi-join Materialization 半连接物化
先将外层查询的 IN 子句中的不相关子查询进行物化，然后再将外层查询的表与物化表进行连接，这种执行方式在本质上也算是一种实现半连接的方案，只不过由于物化表中没有重复的记录，所以可以直接将子查询转为连接查询
FirstMatch 首次匹配
先取一条外层查询的记录
然后到子查询的表中寻找符合匹配条件的记录
如果能找到一条，就将该外层查询记录加到最终的结果集中并且停止查找更多匹配的记录，如果找不到，就将该外层查询的记录丢弃，然后再开始取下一条外层查询的记录，重复这个过程，直到外层查询获取不到记录为止
半连接的适用条件
SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...
SELECT ... FROM outer_table WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...
该子查询必须是与 IN 组成的布尔表达式，并且在外层查询的 WHERE 或者 ON 子句中出现
外层查询也可以有其他的搜索条件，只不过必须使用 AND 与 IN 子查询的搜索条件连接起来
该子查询必须是一个单一的查询，不能是由 UNION 连接起来的若干个查询
该子查询不能包含 GROUP BY、HAVING 语句或者聚集函数
不适用于半连接的情况
在外层查询的 WHERE 子句中，存在其他搜索条件使用 OR 与 IN 子查询组成的布尔表达式连接起来的情况
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a') OR key2 > 100
使用 NOT IN 而不是 IN 的情况
SELECT * FROM s1 WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a')
IN 子查询在 SELECT 子句中的情况
SELECT key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a') FROM s1
IN 子查询中包含 GROUP BY、HAVING 或者聚集函数的情况
SELECT * FROM s1 WHERE key2 IN (SELECT COUNT(*) FROM s2 GROUP BY key1)
IN 子查询中包含 UNION 的情况
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a' UNION SELECT common_field FROM s2 WHERE key3 = 'b')
无法转为半连接的 IN 子查询的执行方式
对于不相关子查询，先将 IN 子查询物化之后再参与查询
SELECT * FROM s1 WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a')
注意，这里将子查询物化之后，不能转为与外层查询的表连接，只能是先扫描 s1 表，然后针对 s1 表的某条记录来判断该记录的 key1 值是否在物化表中
无论 IN 子查询是相关的还是不相关的，都可以将 IN 子查询转为 EXISTS 子查询
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr = inner_expr)
只要 IN 子查询放在 WHERE 或者 ON 子句中，那么 IN 就可以转为 EXISTS，否则 IN 是不能转为 EXISTS 的
IN 转为 EXISTS 的好处就是转换之前可能不走索引，但是转换之后可能就走索引了
如果 IN 子查询不满足转为半连接的条件，又不能转为物化表，或者转为物化表的成本太高，那么它就会被转为 EXISTS 子查询
IN 子查询执行方案优先级总结
如果 IN 子查询符合转换为半连接的条件，查询优化器就会将该子查询转为半连接，从五种半连接执行策略中选择执行成本最低的方案执行
如果 IN 子查询不符合转换为半连接的条件，查询优化器会从下边两种策略中找成本最低的方案执行
先将子查询物化，再执行查询
执行 IN 到 EXISTS 的转换
ANY/ALL 子查询优化
如果 ANY/ALL 子查询是不相关子查询，那么子查询就会转换成我们熟悉的方式来执行
< ANY (SELECT inner_expr ...) 转换为 < (SELECT MAX(inner_expr) ...)
> ANY (SELECT inner_expr ...) 转换为 > (SELECT MIN(inner_expr) ...)
< ALL (SELECT inner_expr ...) 转换为 < (SELECT MIN(inner_expr) ...)
> ALL (SELECT inner_expr ...) 转换为 > (SELECT MAX(inner_expr) ...)
EXISTS 子查询的执行
如果 EXISTS 子查询是不相关子查询，那么查询优化器会首先执行该子查询，然后对查询进行 TRUE 或者 FALSE 的改写
如果 EXISTS 子查询是相关子查询，就只能按照小白认为的相关子查询的执行方式来执行，要是可以走到索引，效率就会相应提高
对于派生表的优化
将派生表和外层查询合并，也就是将查询重写为没有派生表的形式
将派生表与外层查询合并，然后将派生表中的搜索条件放到外层查询的搜索条件中，通过将外层查询与派生表合并的方式可以消除派生表，就意味着我们不用再付出创建和访问临时表的成本了
派生表不能与外层查询合并的场景，当派生表中有下面这些函数或者语句时，就不可以与外层查询合并
聚集函数
DISTINCT
GROUP BY
HAVING
LIMIT
UNION 或者 UNION ALL
派生表对应的子查询的 SELECT 子句中含有另一个子查询
把派生表物化
将派生表的结果集写到一个临时表中，然后把这个物化表当作普通表一样来参与查询
在物化派生表时，会采用延迟物化的策略，也就是在查询中会真正使用到派生表时才会去物化它，而不是在执行查询之前就先将派生表物化，如果另外一个参与连接的表都没有可用于连接的记录，结果集就是空的，那就根本用不上这个派生表，所以就没有必要去物化它
