275_DBA_子查询优化

子查询概述

1 子查询分类

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

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

3 注意事项

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 适用条件如下

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子句中含有另一个子查询