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(2)—mysql基于规则优化（四十五)​​

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适用内连接，总结下来就是：

1. 该组合必须和in组成布尔表达式，并在外层的where和on出现。
2. 外层也可以有其他搜索条件，in子查询搜索条件必须和and连接。
3. 不能由若干查询union连接。
4. 前面说的子查询不能由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)

而如果吧上面的转成exists，返回的就不是null，而是true或者false：

mysql> SELECT EXISTS (SELECT 1 FROM s1 WHERE NULL = 1);
+------------------------------------------+
| EXISTS (SELECT 1 FROM s1 WHERE NULL = 1) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT EXISTS (SELECT 1 FROM s1 WHERE 1 = NULL);
+------------------------------------------+
| EXISTS (SELECT 1 FROM s1 WHERE 1 = NULL) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXISTS (SELECT 1 FROM s1 WHERE NULL = NULL);
+---------------------------------------------+
| EXISTS (SELECT 1 FROM s1 WHERE NULL = NULL) |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (0.00 sec)

但我们基本都不会这么写，都会放在sql的where或者on后面，这样就不会区分null或者false，

mysql> SELECT 1 FROM s1 WHERE NULL;
Empty set (0.00 sec)

mysql> SELECT 1 FROM s1 WHERE FALSE;
Empty set (0.00 sec)

所以只要在where或者on后面，直接转成exists是没问题的

SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 where s1.common_field = s2.common_field) OR key2 > 1000;

这个sql转换可以这样

SELECT * FROM s1 WHERE exists (SELECT key3 FROM s2 where s1.common_field = s2.common_field and s1.key1 = s2.key3) OR key2 > 1000;

说到底，为什么要转换呢，这样就可以使用s2.key3的索引查询了，这样不是更快吗？

ANY/ALL子查询优化

比如：

< ANY (SELECT inner_expr ...)可以转换成< (SELECT MAX(inner_expr) ...)

< ALL (SELECT inner_expr ...) 可以转换成< (SELECT MIN(inner_expr) ...)

[NOT]EXISTS子查询执行

SELECT * FROM s1

    WHERE EXISTS (SELECT 1 FROM s2 WHERE key1 = 'a')

        OR key2 > 100;

因为exists返回的是true和false，所以上面的查询返回true可以简化为

SELECT * FROM s1

    WHERE TRUE OR key2 > 100;

最后进一步简化为

SELECT * FROM s1

    WHERE TRUE;

上面说的是不相关子查询，若是相关子查询的话怎么办呢？

SELECT * FROM s1

    WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.common_field);

这种的话就只能按最原始的方法循环查询，前面说过了，当然如果有索引的话也会快不少。

对于派生表优化

前面说的都是子查询放在where和on后面，在in里面，如果吧子查询放在from后面，就是派生表：

SELECT * FROM  (

        SELECT id AS d_id,  key3 AS d_key3 FROM s2 WHERE key1 = 'a'

    ) AS derived_s1 WHERE d_key3 = 'a';

那么我们派生表如何优化呢？

1. 派生表物化：

这种大家肯定是最容易想到的，mysql采用的是延迟物化策略，不是直接查询的时候就物化，免得降低效率。

SELECT * FROM (

        SELECT * FROM s1 WHERE key1 = 'a'

    ) AS derived_s1 INNER JOIN s2

    ON derived_s1.key1 = s2.key1

    WHERE s2.key2 = 1;

比如上面这个，他实现判断条件是否满足，满足才吧子表物化。

1. 将派生表和外层表合并

SELECT * FROM (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1;

其实这个本质就是看s1里满足key1=’a’吗

所以直接优化成

SELECT * FROM s1 WHERE key1 = 'a';

对于稍微复杂点的语句

SELECT * FROM (

        SELECT * FROM s1 WHERE key1 = 'a'

    ) AS derived_s1 INNER JOIN s2

    ON derived_s1.key1 = s2.key1

    WHERE s2.key2 = 1;

我们也可以优化成这样

SELECT * FROM s1 INNER JOIN s2

    ON s1.key1 = s2.key1

    WHERE s1.key1 = 'a' AND s2.key2 = 1;

这样直接消除派生的功能，可以让我们减少创建临时表的开销。但当里面有这些，就不可以合并派生表和外层表了，有聚合函数，比如max()等，比如distinct，group by，having等。

所以对于派生表，先进行外层和子表的合并，不行的话就物化子表。