前面说了mysql会吧一些冗余的sql语句查询优化重写，比如多于的括号，比如有的外连接其实跟内连接类似，可以优化查询表的顺序。子查询又分为相关和不相关子查询，如果子查询过滤条件里有外层查询的参数，则是相关子查询，反之则是不相关子查询。Any函数就代表只要有一个就行，小的，all代表必须所有的都满足这个条件，所以必须大的也满足。当我们判断子查询里是否存在的时候，则用exists判断，有则返回true。
子查询注意事项
1、子查询必须用括号，否则报错：
mysql> SELECT SELECT m1 FROM t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT m1 FROM t1' at line 1
2、在select语句中，子查询必须是标量，如果有多行或者多列报错：
mysql> SELECT (SELECT m1, n1 FROM t1);
ERROR 1241 (21000): Operand should contain 1 column(s)
3、对于in/not in/ANT/all/some这些子语句不允许用limit：
mysql> SELECT * FROM t1 WHERE m1 IN (SELECT * FROM t2 LIMIT 2);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
所以因为没有聚集函数和having，所以group by就是多余的
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 GROUP BY m2);
Distinct也是多余的，集合里去重没啥意义
SELECT * FROM t1 WHERE m1 IN (SELECT DISTINCT m2 FROM t2);
同理orderby也是多余的。
子查询在mysql怎么执行的呢？
我们继续用前面创建的表single_table来演示，假设有两个一样的表s1和s2，以前我是这么认为的：
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2);
不相关子查询应该是先查询子sql，获取到标量之后，在执行外层sql。
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key2 = s2.key2);
相关子查询则是先执行外层sql，获取到s2.key2对应的标量，在执行子查询，子查询获取到值后，在用集合查询外层查询，如此循环。
标量子查询和行子查询
- select子句时，必须使用标量子查询。
- 子查询使用=,>,<,<>等操作符和某个操作数组成布尔表达式，这样子查询必须是标量子查询或者行子查询。
对于上面两种查询来说，执行的很简单，跟我们前面想的一样，
SELECT * FROM s1 WHERE
key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);
这种相关子查询也和我们前面想的一样：
- 外层获取一条记录，用于s2.key3的值。
- 查询子查询集合。
- 再根据集合的where语句看外层子查询是否符合。
- 重复步查询。
In子查询优化
对于不相关的in子查询：
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
这时候如果in里面的参数太多，会导致无法有效使用索引，只能对外层全表扫描，于是mysql设计者想到了，直接这种in的子查询，吧子查询看做一个临时表，如果低于系统变量，tmp_table_size会创建memory的内存临时表，此时此刻则是hash索引。写入的临时表而且会通过索引去重，如果超过了tmp_table_size则会创建物理磁盘的临时表，索引类型也会变为b+树索引。
Mysql吧这种in的子查询建立临时表过程叫做物化(Materialize)，所以这种临时表又叫物化表，因为物化表里都有索引，基于内存的有hash索引，基于磁盘的有b+树索引，所以查询起来效率非常高。
物化表转连接
当mysql建立了物化表时候，其实查询的过程是什么呢，先扫描s1表，然后把所有s1查询出来的值带入物化表查询，或者显示先扫描物化表，吧所有物化表查询出来的值带入s1的key1列查询。这时候我们可以看到，其实就是两个表的内链接，如果是内连接，就要考虑表优先级顺序来选择低成本的来执行sql了。
如果选s1作为驱动表，则成本是物化临时表需要的成本 + s1驱动表扫描成本 + s1表中数据去物化表单表访问成本。(前面说过物化表是有索引，且不是重复，所以效率很高)
如果选物化表作为驱动表，则成本是物化临时表需要的成本 + 物化表扫描成本 + 物化表数据 去s1单表访问成本。(因为s1表的key1也是索引，所以这里效率也很高)
将子查询转换成semi-join
既然是物化之后转连接，mysql设计者于是想着能不能直接不物化，转连接呢
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
我们可以看到上面的查询过程：先查询s1的值，然后把key1值带入到物化表，看看common_field是否存在，后吧这些值放入结果集。
SELECT s1.* FROM s1 INNER JOIN s2
ON s1.key1 = s2.common_field
WHERE s2.key3 = 'a';
看起来是不是像这样，让他们两个值相同，然后用where过滤s2的值。
这时候，我们可以研究下，在s2执行完key3 = ‘a’之后满足s1.key1=s2.common_fiels 的数据有多少。
情况1：s2表过滤后，没有任何记录满足s1.key1=s2.common_fiels,则没有数据加入结果集。
情况2：s2表过滤后，有一条满足s1.key1=s2.common_fiels,则一条数据加入结果集。
情况3：s2表过滤后，有多条满足s1.key1=s2.common_fiels,则多条数据加入结果集。
其实对于我们需要的结果只需要s2表满足这个条件就好，我们只要查询出s1的值，并不需要管他有多少数据，但这时候并不满足情况三的条件，所以这时候mysql内部出现一个新的连接，叫semi-join。这个连接就能满足我们前面需要的数据，并不需要管子连接查询的数据，满足就直接查询s1就好。
SELECT s1.* FROM s1 SEMI JOIN s2
ON s1.key1 = s2.common_field
WHERE key3 = 'a';
那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';
为什么呢，因为这些数不可能存在查询出一个集合出来，已经是的了。
DuplicateWeedout execution strategy(重复值消费)
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
上面的sql，因为查询列不是值，所以肯定不能用上面上拉的方法，这时候就需要建立临时表，给临时表一个id，当吧数据放入s1集合时，先加入临时表，如果不存在临时表，则添加进集合成功，如果临时表存在，则不添加进集合。
LooseScan execution strategy(松散扫描)
如下sql：
SELECT * FROM s1
WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');
如果以s2为驱动表，如果key1查询来aa,aa,aa,ab,ab,ab,bb,bb,bb,只需要在s1表中s1.key3 = ‘aa’，只需要取相同值个值的记录放入结果集，这种取值方式就是松散扫描。
Semi-join Materialization execution strategy
我们前面吧子查询进行物化，然后通过外层表和物化表连接，本身就是semi-join，只不过物化表没有重复记录，所以可以将子查询转为连接查询。
FirstMatch execution strategy （匹配）
这种是原始的半连接方式，和我们前面想法是一样的，关子查询则是先执行外层sql，获取到s2.key2对应的标量，在执行子查询，子查询获取到值后，在用集合查询外层查询，如此循环。
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3);
比如这种in语句也可以转成
SELECT s1.* FROM s1 SEMI JOIN s2
ON s1.key1 = s2.common_field AND s1.key3 = s2.key3;
然后可以用我们上面介绍的查询方法查询，但需要注意的是，由于相关子查询不是一个独立的查询，所以不能转为物化表来执行。
文章来源：知乎平台 原文地址：https://zhuanlan.zhihu.com/p/412869445
