子查询注意事项&semi-join(2)—mysql基于规则优化（四十五)

前面说了mysql会吧一些冗余的sql语句查询优化重写，比如多于的括号，比如有的外连接其实跟内连接类似，可以优化查询表的顺序。子查询又分为相关和不相关子查询，如果子查询过滤条件里有外层查询的参数，则是相关子查询，反之则是不相关子查询。Any函数就代表只要有一个就行，最小的，all代表必须所有的都满足这个条件，所以必须最大的也满足。当我们判断子查询里是否存在的时候，则用exists判断，有则返回true。

​​条件简化&子查询（1）--Mysql基于规则优化（四十四)​​

子查询注意事项

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对应的标量，在执行子查询，子查询获取到值后，在用集合查询外层查询，如此循环。

标量子查询和行子查询

1. select子句时，必须使用标量子查询。
2. 子查询使用=,>,<,<>等操作符和某个操作数组成布尔表达式，这样子查询必须是标量子查询或者行子查询。

对于上面两种查询来说，执行的很简单，跟我们前面想的一样，

SELECT * FROM s1 WHERE

    key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

这种相关子查询也和我们前面想的一样：

1. 外层获取一条记录，用于s2.key3的值。
2. 查询子查询集合。
3. 再根据集合的where语句看外层子查询是否符合。
4. 重复第一步查询。

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;

然后可以用我们上面介绍的查询方法查询，但需要注意的是，由于相关子查询不是一个独立的查询，所以不能转为物化表来执行。