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

2021-12-08 19:18:35

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实现了四种不同的半连接执行策略，它们有不同的删除重复项的方法：

FirstMatch
DuplicateWeedout
Materialization
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策略避免了一旦找到第一次真正的匹配就通过快速执行生成重复项：

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 操作：

内部连接产生重复项。 Germany有三次big city,此时将DuplicateWeedout策略进行应用：

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官网

码农公寓

相关文章