Table pullout （子查询中的表上拉）

当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的 FROM 子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中，比如假设 s2 中存在这个一个 key2 列，列上有唯一性索引：
SELECT FROM s1
WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = ‘a’);
由于 key2 列是 s2 表的唯一二级索引列，所以我们可以直接把 s2 表上拉到外层查询的 FROM 子句中，并且把子查询中的搜索条件合并到外层查询的搜索条件中，上拉之后的查询就是这样的：
SELECT s1. FROM s1 INNER JOIN s2

ON s1.key2 = s2.key2 WHERE s2.key3 = ‘a’;
为啥当子查询的查询列表处只有主键或者唯一索引列时，就可以直接将子查询转换为连接查询呢？因为主键或者唯一索引列中的数据本身就是不重复的嘛！ 所以对于同一条 s1 表中的记录，你不可能找到两条以上的符合 s1.key2 = s2.key2 的记录。

DuplicateWeedout execution strategy （重复值消除）

对于这个查询来说：
SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no= ‘a’);
转换为半连接查询后，s1 表中的某条记录可能在 s2 表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中，为了消除重复，我们可以建立一个临时表，比方说这个临时表长这样：
CREATE TABLE tmp ( id PRIMARY KEY
);
这样在执行连接查询的过程中，每当某条 s1 表中的记录要加入结果集时， 就首先把这条记录的 id 值加入到这个临时表里，如果添加成功，说明之前这条s1 表中的记录并没有加入最终的结果集，现在把该记录添加到最终的结果集； 如果添加失败，说明之前这条 s1 表中的记录已经加入过最终的结果集，这里直接把它丢弃就好了，这种使用临时表消除 semi-join 结果集中的重复值的方式称之为 DuplicateWeedout。

LooseScan execution strategy（松散扫描）

大家看这个查询：
SELECT * FROM s1 WHERE order_note IN (SELECT order_no FROM s2 WHERE order_no> ‘a’ AND order_no< ‘b’);
在子查询中，对于 s2 表的访问可以使用到 order_no 列的索引，而恰好子查询的查询列表处就是 order_no 列，这样在将该查询转换为半连接查询后，如果将 s2 作为驱动表执行查询的话，那么执行过程就是这样：

在 s2 表的 idx_order_no 索引中，值为’aa’的二级索引记录一共有 3 条，那么只需要取第一条的值到 s1 表中查找 s1.order_note= ‘aa’的记录，如果能在 s1 表中找到对应的记录，那么就把对应的记录加入到结果集。依此类推，其他值相同的 二级索引记录，也只需要取第一条记录的值到 s1 表中找匹配的记录，这种虽然是扫描索引，但只取值相同的记录的第一条去做匹配操作的方式称之为松散扫描。
当然除了我们上面所说的，MySQL 中的半连接方式还有好几种，比如
Semi-join Materializationa 半连接物化、FirstMatch execution strategy （首次匹配） 等等，我们就不更深入的讨论了。

semi-join 的适用条件

当然，并不是所有包含 IN 子查询的查询语句都可以转换为 semi-join，只有形如这样的查询才可以被转换为 semi-join：

SELECT … FROM outertables
WHERE expr IN (SELECT … FROM innertables …) AND …
或者这样的形式也可以：
SELECT … FROM outertables
WHERE (oe1, oe2, …) IN (SELECT ie1, ie2, … FROM innertables …) AND …
用文字总结一下，只有符合下边这些条件的子查询才可以被转换为 semi-join： 该子查询必须是和 IN 语句组成的布尔表达式，并且在外层查询的 WHERE 或者 ON 子句中出现。
外层查询也可以有其他的搜索条件，只不过和 IN 子查询的搜索条件必须使用 AND 连接起来。
该子查询必须是一个单一的查询，不能是由若干查询由 UNION 连接起来的形式。
该子查询不能包含 GROUP BY 或者 HAVING 语句或者聚集函数。
MySQL 对不能转为__semi-join 查询的子查询优化
1、对于不相关子查询来说，可以尝试把它们物化之后再参与查询比如我们上边提到的这个查询：
SELECT * FROM s1 WHERE order_note NOT IN (SELECT order_note FROM s2 WHERE order_no= ‘a’)
先将子查询物化，然后再判断 order_note 是否在物化表的结果集中可以加快查询执行的速度。
2、不管子查询是相关的还是不相关的，都可以把 IN 子查询尝试转为 EXISTS
子查询
其实对于任意一个 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)
为啥要转换呢？这是因为不转换的话可能用不到索引，比方说下边这个查询：
SELECT FROM s1 WHERE order_no IN (SELECT order_no FROM s2 where s1.order_note = s2.order_note) OR insert_time > ‘2021-03-22 18:28:28’;
这个查询中的子查询是一个相关子查询，而且子查询执行的时候不能使用到索引，但是将它转为 EXISTS 子查询后却可以使用到索引：
SELECT FROM s1 WHERE EXISTS (SELECT 1 FROM s2 where s1.order_note = s2.order_note AND s2.order_no= s1.order_no) OR insert_time > ‘2021-03-22 18:28:28’00;
转为 EXISTS 子查询时便可能使用到 s2 表的 idx_order_no 索引了。

需要注意的是，如果 **IN 子查询不满足转换为 semi-join 的条件，又不能转换为物化表或者转换为物化表的成本太大，那么它就会被转换为 EXISTS **查询。
在MySQL5.5 以及之前的版本没有引进semi-join 和物化的方式优化子查询时， 优化器都会把 IN 子查询转换为 EXISTS 子查询，所以当时好多声音都是建议大家把子查询转为连接，不过随着 MySQL 的发展，最近的版本中引入了非常多的子查询优化策略，内部的转换工作优化器会为大家自动实现。

小结

如果 IN 子查询符合转换为 semi-join 的条件，查询优化器会优先把该子查询转换为 semi-join，然后再考虑下边 5 种执行半连接的策略中哪个成本最低：
Table pullout DuplicateWeedout LooseScan Materialization FirstMatch
选择成本最低的那种执行策略来执行子查询。
如果 IN 子查询不符合转换为 semi-join 的条件，那么查询优化器会从下边两种策略中找出一种成本更低的方式执行子查询：
先将子查询物化之后再执行查询执行 IN to EXISTS 转换。