Semi-join使用条件，派生表优化 (3)—mysql基于规则优化（四十六)

前面说了子查询里有no/any/all不能用limit，group by，order by等，他会被查询优化器优化掉，子查询可能会物化转成内连接semi-join查询，物化就是会吧子查询看做一个表，如果数据太大，超过系统变量tmp_table_size，则会在磁盘里创建b+树的临时表，如果比较小，则会创建内存里hash树的临时表，之后会物化表转连接，但如果直接转where 和on，则可能会出现子查询多条的情况，我们的真实需求并不需要多条，所以有了semi-join。

Semi-join适用

不是所有的都适用内连接

SELECT ... FROM outer_tables

WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...

SELECT ... FROM outer_tables

WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...

上面两个sql适用内连接，总结下来就是：

该组合必须和in组成布尔表达式，并在外层的where和on出现。

外层也可以有其他搜索条件，in子查询搜索条件必须和and连接。

不能由若干查询union连接。

前面说的子查询不能由having和group by等。

不适用semi-join

外层的where条件有其他其他搜索条件与子查询用or连接

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')

在select子句中的in查询情况

SELECT key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a') FROM s1 ;

子查询有group by

SELECT * FROM s1

WHERE key2 IN (SELECT COUNT(*) FROM s2 GROUP BY key1);

子查询有union

SELECT * FROM s1 WHERE key1 IN (

SELECT common_field FROM s2 WHERE key3 = 'a'

UNION

SELECT common_field FROM s2 WHERE key3 = 'b'

);

当这些不适合转内连接的，就是直接物化子查询来查询数据，效率也会非常快。(注意，这里物化之后是不能转成内连接，只能先扫描s1表，看key1的值是不是在物化表内)

不管是相关查询还是不想管查询，都可以吧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)

但当inner_expr和outer_expr值为null的情况下比较特殊，因为null值为操作符返回的是null，比如：

mysql> SELECT NULL IN (1, 2, 3);
+-------------------+
| NULL IN (1, 2, 3) |
+-------------------+
|              NULL |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT 1 IN (1, 2, 3);
+----------------+
| 1 IN (1, 2, 3) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT NULL IN (NULL);
+----------------+
| NULL IN (NULL) |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)