简单说说SQL中Join的使用

原创 2017年11月09日 00:00:00

640?wx_fmt=jpeg&wxfrom=5&wx_lazy=1

最近工作中,遇到了一起由于慢SQL引起DB CPU > 90% ,数据库hang住。。最终导致其他业务查询统统失败。

       细看下来是由于几张大表Join关联查询引起的,故障本身很常见,不过让我想到有必要讲讲如何规避Join的问题。

       以下的讨论都是基于数据库能力有限的前提下,否则后续的讨论就可以直接忽略了。


首先,我们来谈谈SQL Join的使用场景


1)如果系统存在


高并发、分布式

业务逻辑简单

数据的一致性要求不高

允许延迟读


那么建议在SQL中少使用Join。减少join的目的是在这类业务场景下,除了直观地降低了高并发状态下的资源消耗外,更大的好处是降低了业务之间的耦合,增加了扩展性。服务就可以拆分成多个微服务和多个数据库,便于在一部分负担过重时进行增配;或者直接改为使用缓存等等。


2)如果系统存在


低并发、频繁复杂数据写入

CPU密集而非IO密集

业务逻辑通过数据库处理甚至包含大量存储过程

对一致性与完整性要求很高的系统

需要大量的报表和统计


那么是需要数据库Join的,可以说是无法避免Join。比如部分金融业务、财务系统、企业应用之类,复杂join也是不可避免的,不仅要写,还要写好,才能发挥数据库最大的功用。



对于情况1,我们有以下几种常见的方案来替代Join:


1. 分多次select取不同表的数据,然后在应用代码里做Join;

2. 各自存数据的同时,做一张宽的冗余表,从宽表里取查询数据;

3. 需要Join的数据保存在缓存中(如redis),缓存可以使用主动式(数据修改时更新缓存)或被动式(缓存删除后,读取时才加载);

4. 从独立的用户API接口进行读取。和方法1类似,在代码里做聚合。



对于情况2, 我们的目标是优化Join,提升对应的性能,常见的方案如下:


1.用小结果集驱动大的结果,目的是为了尽可能减少Join语句中的NestedLoop的循环总次数,比如,当两个表(表A和表B)Join的时候,如果表A通过WHERE条件过滤后有10条记录,而表B有20条记录。如果我们选择表A作为驱动表,也就是被驱动表的结果集为20,那么我们通过Join条件对被驱动表(表B)的比较过滤就会有10次。反之,如果我们选择表B作为驱动表,则需要有20次对表A的比较过滤。


2.保证Join语句中被驱动表上Join条件字段已经被索引,保证被驱动表上Join条件字段已经被索引的目的,正是针对上面第1点的考虑,只有让被驱动表的Join条件字段被索引了,才能保证循环中每次查询都能够消耗较少的资源,这也正是优化内层循环的实际优化方法。


3.增大Join_Buffer_Size的大小,MySQL在完成某些join需求的时候(all row join/all index /scan join)为了减少参与join的“被驱动表”的读取次数以提高性能,需要使用到join buffer来协助完成join操作。当join buffer 太小,MySQL不会将该buffer存入磁盘文件而是先将join buffer中的结果与需求join的表进行操作,然后清空join buffer中的数据,继续将剩余的结果集写入buffer中,如此往复,这势必会造成被驱动表需要被多次读取,成倍增加IO访问,降低效率(执行计划中如果现实using join buffer)。如果join语句不是很少的话,个人建议可以适当增大join_buffer_size到1MB左右,如果内存充足可以设置为2MB。


最后一点总结就是,数据不大的时候怎么做都行,就按数据库规范设计最好。数据量大的时候,为了性能就只能牺牲一些规范了。任何的规范都是在特定情况下的某种妥协,脱离了这个环境,就不一定成立了。



扫描二维码或手动搜索微信公众号【架构栈】: ForestNotes

欢迎转载,带上以下二维码即可

                         640?wx_fmt=jpeg


点击阅读原文”,所有【架构栈】近期的架构文章汇总

↓↓↓

版权声明:本文为博主原创文章,未经博主允许不得转载。

SQL中的join操作总结(非常好)

1.1.1 摘要 Join是关系型数据库系统的重要操作之一,SQL Server中包含的常用Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一个表中的行...
  • u010159842
  • u010159842
  • 2015年08月25日 16:11
  • 533

SQL优化——SQL子句执行顺序和Join的一点总结

原文:点击打开链接 1.笛卡尔积(Cartesian product) 顾名思义, 这个概念得名于笛卡儿. 在数学中,两个集合 X 和 Y 的笛卡儿积(Cartesian prod...
  • yenange
  • yenange
  • 2014年02月17日 11:54
  • 4490

SQL join on 性能分析

今天突然遇到一个问题,到底是sql语句先join后where 效率高,还是先where后join高 经过测试是一样的 SELECT MaterialID,Amount, ABC FROM ( ...
  • xueyong4712816
  • xueyong4712816
  • 2011年08月01日 16:44
  • 1965

SQL性能优化

  • 2014年12月20日 10:21
  • 20KB
  • 下载

T-SQL 中的CROSS JOIN用法(半翻译)

今天来翻译一篇关于T-SQL的文章,本文可供微软认证70-461:QueryingMicrosoft SQL Server 2012的学习和练习之用。本文以翻译为主,引出个人工作中的一些思考,详见最后...
  • DBA_Huangzj
  • DBA_Huangzj
  • 2015年09月15日 13:09
  • 4540

解决spark sql关联(join)查询使用“or“缓慢的问题

1.需求描述将a表的数据与b表的两个字段进行关联,输出结果a表数据约24亿条b表数据约30万条2.优化效果优化后执行时间从数天减少到数分钟3.资源配置spark 1.4.1200core,600G R...
  • lsshlsw
  • lsshlsw
  • 2015年10月20日 19:55
  • 4938

SQL数据库使用JOIN的优化方法

 很早以前,也是一提到SQL Server,就觉得它的性能没法跟Oracle相比,一提到大数据处理就想到Oracle。自己一路走来,在本地blog上记录了很多优化方面的 post,对的错的都有,没有时...
  • utpcb
  • utpcb
  • 2007年11月05日 10:59
  • 423

亲测SQL left join on 和 where 效率

最近看到很多前辈都用了left join on,于是在网上查了好久,感觉说的不大对劲,于是,亲测了一下。虽然不严谨,但是,已经反映一般规律了。 亲测SQL  left join on 和 whe...
  • yang_best
  • yang_best
  • 2015年02月06日 13:38
  • 3726

[SqlServer] 性能优化实战-join与where条件执行顺序

昨天经历了一场非常痛苦的性能调优过程,但是收获也是刻骨铭心的,感觉对sql引擎的原理有了进一步认识。 问题起源于测试人员测一个多条件检索的性能时,发现按某个条件查询会特别慢。对应的sql语句简化为: ...
  • szx1999
  • szx1999
  • 2015年11月25日 14:47
  • 4581

SQL Server中的三种Join方式

1.SQL Server中的三种Join方式 在Sql Server中,每一个join命令,在内部执行时,都会采用三种更具体的join方式来运行。这三种join的方法是:nested loops...
  • isoleo
  • isoleo
  • 2014年10月31日 22:02
  • 2718
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:简单说说SQL中Join的使用
举报原因:
原因补充:

(最多只允许输入30个字)