MySQL 子查询优化[IN/EXISTS]--smei join

MySQL 里面有哪些子查询呢？

• 标量子查询
• 内联视图
• 半连接/反连接
• 本篇主要讲解半连接查询 
  半连接？可以这么理解 where 条件后面有In/EXISTS这样的子查询称为semi jion

格式：select ..... from outer_tables where expr in (select .... from inner_tables ...) and ...

• 为什么要用semi join来进行优化子查询？

  • 因为where后面的子查询每扫描一条数据，Where子查询都会被重新执行一遍，这样效率就会很低如果父表数据很多带来什么问题？那么就有了将子查询的结果提升到FROM中，不需要再父表中每个符合条件的数据都要去把子查询执行一轮了。

• MySQL又是需要满足什么条件才会转换成semi jion？

  • 子查询是in or = any , 不可以是not in
  • 子查询只能包含一个Query bolock, 不可以有union等操作
  • 子查询不能包含group by 或者having
  • 不能包含聚合函数
  • 子查询的谓词是where子句的一部分
  • 子查询谓词不可以是外部查询条件或者否定查询条件
  • 不可以包含Straight_join 限定词
  • 只能用于select insert，而update，delete则都不可用

• 有哪些因为可以将半连接和常规连接进行区分？

  • 在semi-join 中内部表不会在结果中造成重复
  • 内部table中没有列添加到操作结果中。
  • 这意味着半连接的结果是外表行中的子集。这也意味着大部分的半连接的特殊处理是关于内部表中有效的消除重复

那么我们了解了为什么有semi jion，满足什么条件转换成semi join以及如何区分，那对于semi jion 又有哪些优化策略呢？ 
--因为半连接是一种常规连接操作，并结合从半连接内部表中删除可能的重复项。
MySQL实现了四种不同的半连接执行策略，它们有不同的删除重复项的方法：

1. FirstMatch
2. DuplicateWeedout
3. Materialization
4. LooseScan

• FirstMatch：

当扫描inner table 来组合数据时，并且有多个符合条件的数据时，只选择第一条满足条件的记录，连接后的结果，存与临时表。

EG：

select * from country 
    where country.code in ( select city.CountryCode from city    
         where    city.Population >1*1000*1000)
    and Country.Continent='Europe';

由于Germany有两个大城市（在该图中），它将被放入查询输出两次。 这是不正确的，SELECT ... FROM Country不应该产生两次相同的国家记录。 FirstMatch策略避免了一旦找到第一次真正的匹配就通过快速执行生成重复项：