前面说了子查询里有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适用内连接,总结下来就是:
该组合必须和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)
而如果吧上面的转成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';
那么我们派生表如何优化呢?
派生表物化:
这种大家肯定是最容易想到的,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;
比如上面这个,他实现判断条件是否满足,满足才吧子表物化。
将派生表和外层表合并
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等。
所以对于派生表,先进行外层和子表的合并,不行的话就物化子表。