275_DBA_子查询优化

子查询概述

1 子查询分类

按照返回结果集区分子查询

标量子查询

只返回一个单一值的子查询

Select （select m1 from t1 limit 1）

行子查询

只返回一条记录的子查询

Select * from s1 where s1.key1 = (select key1 from s2 limit 1)

列子查询

只返回一列数据的子查询

Select * from s1 where s1.key1 in (select key1 from s2)

表子查询

子查询结果包很多记录（类似表）

Select * from s1 where (s1.key1, s2.key2) in (select key1 ,key2 from s2)

按与外层查询关系区分子查询

不相关子查询

子查询可以自己运行 不依赖外层查询

Select * from s1 where s1.key1 in (select key1 from s2)

相关子查询

子查询需要依赖外层查询

Select * from s1 where s1.key1 in (select key1 from s2 where s2.id =s1.id)

2 子查询在 布尔表达式中的使用

子查询大部分场景在 where 或者 on 条件中充当搜索条件，与操作符(comparison_operator)形成布尔表达式

使用 =, <,> >= , != 作为布尔表达式的操作符

Select * from s1 where s1.key1 > (select min(key1) from t2 )

注: 子查询只能是标量子查询或者行子查询

[NOT] IN /ANY/SOME/ALL子查询

Select * from s1 where s1.key1 in (select key1 from s2)

注：

子查询必须是列子查询或者表子查询 包括多个记录

ANY/SOME意思相同

Select*from s1 where s1.key1 > any(select key1 from s2)  #等价 > (select min(key1) from s2)

对于s1表的某条记录的 key1列 如果子查询中的结果集有任何一个比 key1小 则整个布尔表达式返回true

Select*from s1 where s1.key1 > all(select key1 from s2) #等价 > (select max(key1) from s2)

对于s1表的某条记录的 key1列 如果子查询中的结果集都比 key1小 则整个布尔表达式返回true

Exists 子查询

只关心子查询中是否有记录 而不关心它记录具体是啥 如果exists 后面有记录则 exists表达式即为true

select count(*) from t1 where name in (select name from t2);

select count(*) from t1 where exists (select 1 from t2 where t1.name=t2.name);

3 注意事项

子查询必须用小括号引起来

Select 子句中必须是标量子查询

explain  select (select key1 , key2 from s2);

Operand should contain 1 column(s)

对于in/any/some/all 子查询中 不允许有limit子句

explain select * from s1 where s1.key1 in (select * from s2 limit 2)

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

子查询中 order by  distinct都是画蛇添足 没啥业务意义

select * from s1 where s1.key1 in (select distinct  key2 from s2 )

4 子查询执行流程

例1 相关子查询

select * from s1 where key1 in (select common_field from s2 where s1.key2 = s2.key2) # 其实是一个select 转成了semi join

1 从外层 s1 去一条记录 from s1 where s1.key2 = aaa;

2 将 key2=aaaa 这个常亮 代入内层子查询 s2 去进行进行检索  select common_field from s2 where s2.key2=aaa

3 将 common_field 返回给外层循环，外层s1 判断 select * from s1 where key1 in (common_field) 如果满足就返回 不满足就继续循环

例2 不相关子查询

explain select * from s1 where key1 = (select commen_field from s2 limit 1 )

# 其实是相当于两个 select 不能转成semi join  当初两张表做单表查询

5 子查询的优化

5.1 物化表的提出

Mysql 将子查询的结果集保存到临时表的过程 称为 物化(materialize)

例 explain  select * from s1 where s1.commen_field in  (select s2.commen_field from s2  )   # 对s2结果集进行了物化

类似该SQL in （xxx,xxx,xxxx）很多结果集，相当于 select * from s1 where s1.commen_field = xxx1 or s1.commen_field=xxx2 or … or …

1 Mysql 做了一点点优化，不是直接将子查询的结果集 当结果 直接当外层查询的参数, 而是将结果集写入一个临时表， 将临时表写入涉及的列，将临时表进行去重

2 临时表去重大部分是在内存完成的，只要将列设为主键即可去重,或者使用memory引擎做临时表 同时建立hash索引

3 如果临时表过大 超过了 @@tmp_table_size 或 @@max_heap_table_size 会利用磁盘做临时表

5.2  物化转连接

explain select * from s1 where s1.commen_field in (select s2.commen_field from s2 )

# 对s2结果集进行了物化  且与 s1 进行了 join （id 均为1 意味着只有一个select）

5.3 将子查询转为半连接

由于发现了 物化 join的优势, 直接将子查询转为 join 发挥更大优势 而且不需要进行临时表的创建

例

explain select * from s1 where s1.commen_field in (select s2.commen_field from s2 ) 该SQL 其实可以理解为 select * from s1 join s2 on s1.commen_field = s2.commen_field select * from s1 semi join s2 on s1.commen_field = s2.commen_field # 并没有半连接的语法

但我们无法预估 对于S1 表来说, 在S2表中 有多少条记录满足 s1.commen_field = s2.commen_field 可能一条没有，可能只有一条或者多条

此时MySQL提出了 semi join 概念, s1 和 s2 semi join的意思是，对于S1 表中记录，我们只care 在s2中是否存在匹配的记录，而不关心具体多少条与之匹配，最终只报了S1匹配到的结果集即可

具体实现 semi join 方式如下

1 table pullout

当子查询的查询列只有主键 or 唯一索引列时, 可以吧子查询的表上拉到外层查询from子句中，并把子查询搜索条件合并到外层查询搜索条件中

explain select * from s1 where key2 in (select key2 from s2 where key3 = "alex")

从执行计划中 发现 id 均为1 说明 使用了join , 所以SQL 理论上等于 select * from s1 join s2 on s1.key2=s2.key2 and s2.key3=”alex”

因为是 key2 是唯一索引 所以肯定唯一且不重复，所以直接将子查询转为了连接查询

2 duplicate weedout

优化器会尝试将in 转成半连接, 当策略为 duplicate_Weedout时（通过创建临时表方式为外层查询中的记录进行去重操作） 驱动表执行计划对应extra为start temporary, 被驱动表为 end temporary

explain select * from s1 where key2 in (SELECT key2 from s2 where s2.commen_field = "alex")

# 其实优化器将SQL 优化成 select * from s1 semi join s2 on s1.key2 = s2.key2 where s2.commen_field = "alex"   将s2 当成了驱动表

3 loose scan

将in 子查询 转成 semi join ，如果采用looseScan （虽然是扫描索引，但只取键值相同的第一条记录去匹配） 则驱动表执行计划 extra 显示looseScan

# 转成semi join  select * from s1 semi join s2 on  s1.key3= s2.key1 where s2.key1 like “a%”

# 转成semi join 后 s2 作为驱动表; 基于索引 key1检索中结果集符合 s2.key1 like “a%” 可能有 3865条，例 aalex aalex abob abob, 再做代入内层循环时候，只会取第一个 s2.key1 =aalex 作为条件代入 内层循环去判断 是否能在 s1 找到对应的记录，如果能找到就返回结果集，不能找到则进行下一次判断

备注： 只会取键值相同的第一条记录去进行判断

explain select * from s1 where s1.key3 in (SELECT key1 from s2 where s2.key1 like "a%" )

4 semi-join Materialization

先把外层不相关子查询物化的方式 ，再讲外层查询与物化表进行 join 方式 也算一种半连接

explain select * from s1 where s1.commen_field in (select s2.commen_field from s2 ) # 对s2结果集进行了物化 且与 s1 进行了 join （id 均为1 意味着只有一个select）

5 first match

首次匹配时最原始的semi join 执行方式, 先去外层查询一条记录, 然后到子查询中寻找服务匹配条件的记录

# s2 是子查询 意味着会被执行多次 且要判断是否符合外层传入的列值

explain select * from s1 where s1.commen_field in (SELECT key1 from s2 where s2.key1 = s1.key1)

6  semi join 适用条件

不是所有的in 都能转成 semi join 适用条件如下

子查询必须是与IN 操作符组成的布尔表达式，且在外查询的where 或者  on子句中

外层查询可以有其它条件，必须适用 and 操作符与 IN子查询的搜索条件连接起来

子查询必须是单一查询，不能有union 连起来的若干查询

子查询不能包括 group by， having语句或者聚合函数

其它情况

# or 条件 ; # not in; 位于select子句中

1 # or 条件

select * from s1 where key1 in (select * from xxx) or key2 > “alex”

2  # not in

select * from s1 where key1 not in (select * from xxx)

3 select key1 in  select 子句

explain select key1 in (select key2 from s2 where key1 > "bob") from s1;

4 包括union  情况

explain select * from s1 where key1 in (select s2.commen_field from s2 where key3 ="a" union select s2.commen_field from s2 where key3 ="b")

注意 MySQL扔有相关办法来优化 in 不能转为 semi join 的方式 就是 exists , 对于任意一个 in 都可以写成 exists方式

语法

select count(*) from t1 where name in (select name from t2); select count(*) from t1 where exists (select 1 from t2 where t1.name=t2.name);

注意 子查询是个大表时候 使用exists的目的其实是让大表走到索引上，如果外查询是个大表 in 效率会好一些

总结

如何 IN 子查询符合转为semi  join 条件，优化器会把子查询转为 semi join 查询，然后从下面集中方式中选取最优成本进行执行

Table Pullout

Duplicate Weedout

LooseScan

Semi-join Materialization

FirstMatch execution

如果 IN 子查询不符合转为半连接条件，优化器从下面策略中选取成本最低执行

先将子查询物化，再执行查询  primary subquery

执行IN 转为 exists 的转换

补充

对于派生表的优化

如果把子查询放在from子句后面，这个子查询相当于一个派生表，对于派生表MySQL提出两种执行策略

1 把派生表进行物化

先将结果集写到临时表进行物化，然后把物化表当成普通表一样进行访问，但是MySQL选择了类似懒加载/延迟物化的思想，用到了在物化，而不是在查询时候直接进行物化

例 下面SQL 会先执行 s2.key3 = “bob” 如果有结果在 物化子查询

explain SELECT * from ( select * from s1 where key1 ="alex") as ss join s2 on ss.id =s2.id where s2.key3 = "bob"

2 将派生表和外层查询合并 （将派生表重写成没有派生表的形式） mysql优先会使用这种方式

可以将派生表与外层查询合并，将派生表的搜索条件放在外层循环，例下面这个SQL

explain SELECT * from ( select * from s1 where key1 ="alex") as ss join s2 on ss.id =s2.id where s2.key3 = "bob"

根据执行计划 发现ID均为1 说明进行了合并，这样就消除了派生表，但派生表中有如下函数/语句时候就不能合并

聚合函数 max(), min(), sum()

Distinct

Group by

Having

Limit

Union / union all

派生表中的子查询select子句中含有另一个子查询

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。