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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 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官网

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3天前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
6天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
2天前
|
关系型数据库 MySQL 数据库
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
49 23
|
1天前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
|
18天前
|
监控 关系型数据库 MySQL
Aurora MySQL负载突增应对策略与优化方案
通过以上策略,企业可以有效应对 Aurora MySQL 的负载突增,确保数据库在高负载情况下依然保持高性能和稳定性。这些优化方案涵盖了从架构设计到具体配置和监控的各个方面,能够全面提升数据库的响应速度和处理能力。在实际应用中,应根据具体的业务需求和负载特征,灵活调整和应用这些优化策略。
49 22
|
1月前
|
SQL 关系型数据库 MySQL
MySQL派生表合并优化的原理和实现
通过本文的详细介绍,希望能帮助您理解和实现MySQL中派生表合并优化,提高数据库查询性能。
76 16
|
23天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
169 0
|
19天前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
5天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
72 42
|
2月前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
69 3

热门文章

最新文章