开发者社区> 王静静@> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: MySQL 里面有哪些子查询呢? 标量子查询 内联视图 半连接/反连接 本篇主要讲解半连接查询 半连接?可以这么理解 where 条件后面有In/EXISTS这样的子查询称为semi jion 格式:select .
+关注继续查看

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';

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

dba_jingjing@3306>[world]>desc
    -> select *
    ->     from country
    -> where country.code in
    ->     ( select city.CountryCode
    ->          from city
    ->      where    city.Population >1*100)
    -> and Country.Continent='Europe';
+----+-------------+---------+------------+------+----------------------------+---------------+---------+--------------------+------+----------+----------------------------------+
| id | select_type | table   | partitions | type | possible_keys              | key           | key_len | ref                | rows | filtered | Extra                            |
+----+-------------+---------+------------+------+----------------------------+---------------+---------+--------------------+------+----------+----------------------------------+
|  1 | SIMPLE      | country | NULL       | ref  | PRIMARY,idx_Continent      | idx_Continent | 4       | const              |   46 |   100.00 | Using where                      |
|  1 | SIMPLE      | city    | NULL       | ref  | CountryCode,idx_Population | CountryCode   | 3       | world.country.Code |   18 |    97.91 | Using where; FirstMatch(country) |
+----+-------------+---------+------------+------+----------------------------+---------------+---------+--------------------+------+----------+----------------------------------+
2 rows in set, 1 warning (0.03 sec)

如果没有开启simi jion 的方式下运行:

dba_jingjing@3306>[world]>desc
    -> select *
    ->     from country
    -> where country.code in
    ->     ( select city.CountryCode
    ->          from city
    ->      where    city.Population >1*100)
    -> and Country.Continent='Europe';
+----+--------------------+---------+------------+----------------+----------------------------+---------------+---------+-------+------+----------+-------------+
| id | select_type        | table   | partitions | type           | possible_keys              | key           | key_len | ref   | rows | filtered | Extra       |
+----+--------------------+---------+------------+----------------+----------------------------+---------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY            | country | NULL       | ref            | idx_Continent              | idx_Continent | 4       | const |   46 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | city    | NULL       | index_subquery | CountryCode,idx_Population | CountryCode   | 3       | func  |   18 |    97.91 | Using where |
+----+--------------------+---------+------------+----------------+----------------------------+---------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)

  • DuplicateWeedout :

先和子查询做简单的inner join 操作,并使用临时表(建有Primary key)来消除重复记录。

select * 
from country 
where country.code in ( select city.CountryCode 
                       from city  
                       where 
                           Population >0.33 * country.Population and 
                           city.Population >1*1000*1000);

首先做inner join 操作:
image
内部连接产生重复项。 Germany有三次big city,此时将DuplicateWeedout策略进行应用:
image

dba_jingjing@3306>[world]>desc select * from country where country.code in ( select city.CountryCode from city  where Population >0.33 * country.Population and city.Population >1*1000*1000);
+----+-------------+---------+------------+--------+----------------------------+----------------+---------+------------------------+------+----------+----------------------------------------+
| id | select_type | table   | partitions | type   | possible_keys              | key            | key_len | ref                    | rows | filtered | Extra                                  |
+----+-------------+---------+------------+--------+----------------------------+----------------+---------+------------------------+------+----------+----------------------------------------+
|  1 | SIMPLE      | city    | NULL       | range  | CountryCode,idx_Population | idx_Population | 4       | NULL                   |  237 |   100.00 | Using index condition; Start temporary |
|  1 | SIMPLE      | country | NULL       | eq_ref | PRIMARY                    | PRIMARY        | 3       | world.city.CountryCode |    1 |   100.00 | Using where; End temporary             |
+----+-------------+---------+------------+--------+----------------------------+----------------+---------+------------------------+------+----------+----------------------------------------+
2 rows in set, 2 warnings (0.03 sec)


dba_jingjing@3306>[world]>show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'world.country.Population' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `world`.`country`.`Code` AS `Code`,`world`.`country`.`Name` AS `Name`,`world`.`country`.`Continent` AS `Continent`,`world`.`country`.`Region` AS `Region`,`world`.`country`.`SurfaceArea` AS `SurfaceArea`,`world`.`country`.`IndepYear` AS `IndepYear`,`world`.`country`.`Population` AS `Population`,`world`.`country`.`LifeExpectancy` AS `LifeExpectancy`,`world`.`country`.`GNP` AS `GNP`,`world`.`country`.`GNPOld` AS `GNPOld`,`world`.`country`.`LocalName` AS `LocalName`,`world`.`country`.`GovernmentForm` AS `GovernmentForm`,`world`.`country`.`HeadOfState` AS `HeadOfState`,`world`.`country`.`Capital` AS `Capital`,`world`.`country`.`Code2` AS `Code2` from `world`.`country` semi join (`world`.`city`) where ((`world`.`country`.`Code` = `world`.`city`.`CountryCode`) and (`world`.`city`.`Population` > (0.33 * `world`.`country`.`Population`)) and (`world`.`city`.`Population` > <cache>(((1 * 1000) * 1000))))
2 rows in set (0.03 sec)

该查询将读取City表中的237行,并且它们中的每个都将在Country表中进行主键查找,从而得到另外237行。 这总共提供了474行,并且您需要在临时表中添加237个查找。
那么关闭semi join:

dba_jingjing@3306>[world]>desc select * from country where country.code in ( select city.CountryCode from city  where Population >0.33 * country.Population and city.Population >1*1000*1000);
+----+--------------------+---------+------------+----------------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type        | table   | partitions | type           | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+---------+------------+----------------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | country | NULL       | ALL            | NULL          | NULL        | NULL    | NULL |  239 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | city    | NULL       | index_subquery | CountryCode   | CountryCode | 3       | func |   18 |    11.11 | Using where |
+----+--------------------+---------+------------+----------------+---------------+-------------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.03 sec)

这个执行计划将会读取到(239+239 * 18)=4541行数据,这个会比较慢。

--另外两种策略下次分享

图片来源MariaDB官网

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL 8.0中对EXISTS、NOT EXISTS的持续优化
MySQL 8.0中对EXISTS、NOT EXISTS的持续优化
0 0
软件测试mysql面试题:mysql中 in 和 exists 区别?
软件测试mysql面试题:mysql中 in 和 exists 区别?
0 0
我用一篇文章带你搞懂mysql中的union(all)、limit、exists关键字(二)
我用一篇文章带你搞懂mysql中的union(all)、limit、exists关键字(二)
0 0
我用一篇文章带你搞懂mysql中的union(all)、limit、exists关键字(一)
我用一篇文章带你搞懂mysql中的union(all)、limit、exists关键字(一)
0 0
【MySQL优化】in 和 exists 使用区别
【MySQL优化】in 和 exists 使用区别
0 0
Mysql常用sql语句(19)- in / exists 子查询
Mysql常用sql语句(19)- in / exists 子查询
0 0
MySQL防止重复插入相同记录 insert if not exists
MySQL防止重复插入相同记录 insert if not exists
0 0
MySQL not exists 真的不走索引么?
在一些业务场景中,会使用NOT EXISTS语句确保返回数据不存在于特定集合,部分同事会发现NOT EXISTS有些场景性能较差,甚至有些网上谣言说”NOT EXISTS不走索引”,哪对于NOT EXISTS语句,我们如何优化呢?
0 0
MySQL 8.0中对EXISTS、NOT EXISTS的持续优化
MySQL 8.0中对EXISTS、NOT EXISTS的持续优化
0 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
MySQL表和索引优化实战
立即下载
PostgresChina2018_张启程_为什么我们抛弃MongoDB和MySQL,选择PgSQL
立即下载
从理论到实践,深度解析MySQL Group Replication
立即下载