mysql的left join和inner join的效率对比,以及如何优化

一、前言

最近在写代码的时候,遇到了需要多表连接的一个问题,初始sql类似于:

select * from a left join b on a.x = b.x left join c on c.y = b.y left join d on d.z=c.z

      这样的多个left join组合,一方面是心里有点不舒服,总觉得这种写法是有问题的,一方面有有点好奇,直接用inner join会怎样呢?差别在哪里?后续使用inner join发现速度要比left join快一些,所以这边就研究一下这个问题。

二、left join为什么会比 inner join 慢

1、关于逻辑运算量

      关于left join的概念,大家是都知道的(返回左边全部记录,右表不满足匹配条件的记录对应行返回null),那么单纯的对比逻辑运算量的话,inner join 是只需要返回两个表的交集部分,left join多返回了一部分左表没有返回的数据。

2、关于mysql连接的算法 Nest Loop Join(嵌套联接循环)

这个算法是mysql默认的连接算法,类似于我们php程序的三个嵌套循环:

(foreach a as v){
	(foreach b as v1){
		(foreach c as v2){
		}
	}
}

      从算法上来看,根据mysql文档,inner join在连接的时候,mysql会自动选择较小的表来作为驱动表,从而达到减少循环次数的目的。我们在使用left join表的时候,默认是使用左表作为驱动表,那么此时左表的大小是我们来控制的,如果控制不当,左表比较大,那么自然循环次数也会变多,效率会下降。

MySQL Nested-Loop Join算法学习

      根据这两方面的对比,left join明显被秒成渣,但是我们的实际业务却经常需要使用left join,一切还是要以实际业务为主,所以大家还是仁者见仁智者见智的选择吧。博主这里因为业务并不是很需要left join,所以果断选择使用inner join来连接表。

三、关于 left join的优化

根据上面咱们的对比,基本可以总结出来一些简单的优化方案。

1、left join选择小表作为驱动表(这部分基本是大家的共识)

2、如果左表比较大,并且业务要求驱动表必须是左表,那么我们可以通过where条件语句,使得左表被过滤的小一些,主要原理和第一条类似

3、关联字段给索引,因为在mysql的嵌套循环算法中,是通过关联字段进行关联,并查询的,所以给关联字段索引很必要

4、如果sql里面有排序,请给排序字段加上索引,不然会造成排序使用全表扫描
		参考:https://www.oschina.net/question/930697_2190172
		
5、如果where条件中含有右表的非空条件(除开is null),则left join语句等同于join语句,可直接改写成join语句。 

6、根据文档,MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。所以把表与表之间的关联字段给上encoding和collation(决定字符比较的规则)全部改成统一的类型

7、右表的条件列一定要加上索引(主键、唯一索引、前缀索引等),最好能够使type达到range及以上(ref,eq_ref,const,system) 

注意: 此处的5、6、7参考博客:https://luxuryzh.iteye.com/blog/1976004 ,这个博客总结的很好,强烈推荐

推荐博客阅读:

MySQL索引原理以及慢查询优化

      博主在查过各种资料之后,最终把自己的连接类型改为了inner join,并且给关联字段以及排序字段都加上了索引,速度比起刚开始的时候确实是快了不少。只是说mysql还有很多需要研究的地方,加油吧,碰到并解决,不亏~

end

  • 27
    点赞
  • 95
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 31
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 31
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

铁柱同学

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值