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

2023-02-02 00:00:00 查询 临时 标量 物化 外层

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

标量子查询和行子查询

  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;

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

相关文章