MySQL之SQL分析三部曲实际案例(四)--left join on的思考

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: -------------------------------------------------------------------------------------------------正文--------------------------------...
-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------

问题出现于 生产环境,为了方便表述,在测试环境构造了类似的场景

所有操作都是基于 MySQL-5.7.7-rc 下进行的(PS:5.7.8也出来了,看完改动信息,感觉release依然遥遥无期....._(:з」∠)_)

背景:有用户在抱怨生产系统上,某一个Web的页面太慢,忍无可忍
问题分析过程:略
问题聚焦:最终确定是某一个SQL语句太慢,查询时间用了5s(慢查询日志给出的信息)

出问题的SQL语句

点击(此处)折叠或打开

  1. SELECT count(*) FROM tom
  2. INNER JOIN toa ON tom.order_id = toa.order_id
  3. LEFT OUTER JOIN tov ON tom.order_id = tov.order_id
  4. LEFT OUTER JOIN fo ON tom.sale_type = 2 AND fo.order_id = tom.order_id
  5. WHERE tom.pay_time >= '2015-07-20 11:15:47.068' AND tom.pay_time <= '2015-08-20 11:15:47.068' AND tom.shop_id IN ( 1 , 2 , 3 , 4 , 5 , 6 )
这段语句的执行效率一看就知道不高(...(*/ω\*)...废话,关联了一大堆表结果最终结果集的选择条件里面只有tom表的)

单纯的为了优化的话,把那些无用的表连接去掉就好,优化效果非常明显,这种类似的查询的时间都会降低到0.5-0.8秒左右

不过这里有一点是以前见得比较少,那就是在left join的连接条件后面, 有一个对驱动表tom的选择条件在里面(红色加粗部分 ),且 结果集里面,sale_type各种值都存在~

于是打算仔细研究一下选择条件挂在on和where的区别,以及在left join和inner join情况下的异同。

先上一些前期的 分析:
inner join和left join :一个是内连接,一个是左外连接, 一图说明问题 (打字好累,还不一定能区分的很清楚_(:з」∠)_,PS:看看就好,有些语法不一定MySQL能用....)



on和where的选择条件的区别:

on后面的是连接条件, 代表两个表建立关系所遵循的规则

where后面的可以看作是 筛选条件,是对最终结果集进行过滤所遵循的规则

-------------------------------------------------------------------------------------------------思考---------------------------------------------------------------------------------------------------------------

到这里为止,根据 之前的理解 :on的条件里面标注了驱动表的sale_type的条件,这意味着 满足了sale_type=2的条件的列才会去进行连接
所以中间集应该只有sale_type=2的结果,因此结果集中的sale_type都应该是2
PS:这里的理解出现了偏差

那么回到残酷的现实,为什么
结果集里面, sale_type各种值都存在~?

-------------------------------------------------------------------------------------------------求证---------------------------------------------------------------------------------------------------------------
测试环境构造:MySQL-5.7.7.-rc

建表语句

点击(此处)折叠或打开

  1. create table tb1 (id int(2) primary key, col1 int(2), col2 varchar(10));
  2. create table tb2 (id int(2) primary key, col1 int(2), col2 varchar(10));

  3. insert into tb1 values(1,11,'tb1_1');
  4. insert into tb1 values(2,12,null);
  5. insert into tb1 values(3,null,'tb1_3');
  6. insert into tb1 values(4,14,'tb1_4');
  7. insert into tb1 values(5,15,null);
  8. insert into tb1 values(7,null,null);

  9. insert into tb2 values(1,11,'tb2_1');
  10. insert into tb2 values(2,null,'tb2_1');
  11. insert into tb2 values(3,13,null);
  12. insert into tb2 values(4,14,'tb2_4');
  13. insert into tb2 values(6,16,'tb2_6');
  14. insert into tb2 values(7,null,null);
结果如图


构造如下示例语句

点击(此处)折叠或打开

  1. select tb1.id, tb1.col1 from tb1 left join tb2 on tb1.id=tb2.id and tb1.col1=11;
按照最初的想法,最终结果集应该只有tb1.col1=11的情况,那么看看结果


反而是inner join才是想象中的结果


那么问题应该就是出在left join和inner join的区别上了,
仔细回想一下两者的区别: left join会以驱动表做为标准来进行连接,如果从表不满足条件的匹配项,则用null填充,
换句话说, 结果集中, 驱动表 全部行 都会在结果集中体现,但是从表 不满足条件的匹配项 ,则全部以null填充;

以这种理解来看,示例语句中, 使用了 left join 的方式,则无论后面的 on中对驱动表 做出任何选择操作, 如果where中不对最终结果集进行选择,那么tb1的所有列都会原封不动的在结果集中出现~

要验证这个结论,看看完整的结果集就知道了~上图~


如结论所描述,tb1中的所有列都出现在了结果集,且tb2中,不满足tb1.col1=11的其他列,都用null进行了填充

求证完结,撒花~(*/ω\*)~

总结:半吊子的理解害人,基本功一定要扎实~



-------------------------------------------------------------------------------------------------扩展---------------------------------------------------------------------------------------------------------------
知识点要不断的扩展,成为面,才是王道~

explain看一下示例的执行计划

PS:题外话,手动添加的unique index貌似只会在等值选择的条件下才会被用起来,如图中的eq_ref,而range类好像是用不上unique index,具体以后再研究(有生之年系列+1....._(:з」∠)_....坑越来越多了)

看一下trace的内容,优化器并没有改写这个语句,只是单纯的格式化了一下


那么如果这个tb1.col1=11换成tb2.col1=11会是什么效果?
根据之前的结论,得到的结果应该是:满足tb2.col1=11的tb2的列,会在结果集中用null填充


那么如果这个tb1.col1=11从on的连接条件换到where里面?
根据之前的结论,得到的结果应该是:连接完成以后的中间集包含tb1中的所有列,以及对应的tb2中的列,数据保持原始的样子,在最终结果集中会根据tb1.col1=11的条件,过滤掉不符合的行最终只剩下tb1.col1=11的数据

可能看描述没感觉,不过从结果集来看,有木有觉得很像是这个语句的结果呢~

点击(此处)折叠或打开

  1. select tb1.*,tb2.* from tb1 inner join tb2 on tb1.id=tb2.id and tb1.col1=11;
当然,下面这个语句也是同样的结果集

点击(此处)折叠或打开

  1. select tb1.*,tb2.* from tb1 inner join tb2 on tb1.id=tb2.id where tb1.col1=11;
当然,最终结果集一样,并不代表之前 这几个语句的语义是一样的~

可以试着按照这种顺序去分析这几个语句:根据on条件得出中间结果集-->根据where条件进行过滤/选择-->得到最终结果集

-------------------------------------------------------------------------------------------------吐槽---------------------------------------------------------------------------------------------------------------

PS:坑坑坑,我的眼前一片坑 ......._(:з」∠)_......
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
SQL 存储 关系型数据库
RDS for MySQL的SQL分类与数据类型
小明需在MySQL中管理商品信息,使用SQL完成业务操作。SQL分为DQL(查询)、DML(增删改)、DDL(定义)、DCL(权限控制)和TCL(事务)五大类。DDL用于创建、修改和删除数据库结构,DML处理数据,DCL控制权限,TCL管理事务,DQL则用于查询数据。MySQL有多种数据类型,如数值型(整数、小数)、日期型和字符串型等,选择合适的数据类型是高效开发的关键。
16 0
|
1天前
|
关系型数据库 分布式数据库 数据库
【阿里云云原生专栏】云原生时代的数据库选型:阿里云RDS与PolarDB对比分析
【5月更文挑战第24天】阿里云提供RDS和PolarDB两种数据库服务。RDS是高性能的在线关系型数据库,支持MySQL等引擎,适合中小规模需求;而PolarDB是分布式数据库,具备高扩展性和性能,适用于大规模数据和高并发场景。RDS与PolarDB在架构、性能、弹性伸缩、成本等方面存在差异,开发者应根据具体需求选择。示例代码展示了如何通过CLI创建RDS和PolarDB实例。
181 0
|
2天前
|
SQL 关系型数据库 MySQL
mysql插入500条数据sql语句
【5月更文挑战第12天】
|
2天前
|
SQL 存储 关系型数据库
MySQL进阶-增删查改(全网最详细sql教学)-3
MySQL进阶-增删查改(全网最详细sql教学)
8 0
|
2天前
|
SQL 关系型数据库 MySQL
MySQL进阶-增删查改(全网最详细sql教学)-2
MySQL进阶-增删查改(全网最详细sql教学)
7 0
|
2天前
|
SQL NoSQL 关系型数据库
MySQL进阶-增删查改(全网最详细sql教学)-1
MySQL进阶-增删查改(全网最详细sql教学)
8 0
|
2天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之sql读取mysql写入clickhouse,该如何操作
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2天前
|
SQL 消息中间件 关系型数据库
实时计算 Flink版产品使用合集之 sql采集mysql能拿到before的数据吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2天前
|
SQL 关系型数据库 MySQL
MySQL中的12个SQL编写规范
SQL良好习惯提升查询清晰度、效率和安全性,包括使用EXPLAIN分析查询计划、DELETE/UPDATE时加LIMIT限制影响范围、为表和字段添加注释、关键字大写缩进、指定INSERT字段名、先测试后执行、表含主键及时间戳字段、Update/Delete需Where条件、用InnoDB引擎、避免SELECT *,选择UTF8字符集和规范索引命名。
MySQL中的12个SQL编写规范
|
3天前
|
SQL 关系型数据库 MySQL
【MySQL进阶之路 | 基础篇】SQL概述
【MySQL进阶之路 | 基础篇】SQL概述