浅谈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组合的时候,总是感觉心里有点不舒服,而且总觉得这种写法是有问题的,一方面有点好奇,直接用inner join会怎样呢?差别在哪里?后续使用inner join发现速度要比left join快一些,所以这边就研究一下这个问题。

left join 为什么会比 inner join 慢?

①、逻辑运算量

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


②、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,所以果断选择使用inner join来连接表。

left join的优化

根据上面的对比,可以总结出来一些简单的优化方案如下:

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

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

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

  • 如果sql里面有排序,请给排序字段加上索引,不然很容易会造成排序使用全表扫描。可参考:参考:https://www.oschina.net/question/930697_2190172

  • 如果where条件中含有右表的非空条件(除开is null),则left join语句等同于join语句,可直接改写成join语句

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

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

注意: 上面的优化方案中,倒数第一、倒数第二和倒数第三 参考博客:https://luxuryzh.iteye.com/blog/197600


我们都知道,sql尽量使用数据量小的表做主表,这样效率更高,不过有时候因为逻辑要求,要使用数据量大的表做主表,此时使用left join 就会比较慢,即使关联条件有索引。在这种情况下就要考虑是不是能使用inner join 了。这是因为inner join 在执行的时候回自动选择最小的表做基础表,效率更高。

尾声

巩固下基础吧:

left join 是做左外关联,主表内容都会显示,符合关联条件的附表内容才会显示出来。left join 是 left outer join 的简写形式。


inner join 是内关联,没有主表和附表的概念,两个表中,同时符合关联条件的数据才会显示出来。join 是 inner join 的简写形式。



声明:禁止任何非法用途使用,凡因违规使用而引起的任何法律纠纷,本站概不负责。

扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

精彩评论

全部回复12人评论7,777人参与