当前位置: 首页>数据库>正文

mysql join和逗号连接性能 mysql in和join


这是个老生常谈的问题,平时在简单使用过程中,都能得到想要的结果,但是对于各自的性能,以及适用场景,我们该注意什么?假如现在有表a(主表),b,进行关联查询出a表字段。(mysql版本为5.7)
join:
表连接,这里我只说inner join连接。如果不涉及到order by,首先可以考虑使用join,其次,考虑a,b表对应关系,如果是多对一,或者是一对一,那么,就没有其它顾虑了,在这种场景下,join的性能比exists,in要好。如果a,b是一对多关系,而且查询包括未建立索引的字段,那查出来的结果就会有重复,如果这时你想去重而加了distinct,那该sql的执行计划就会出现using temporary,性能将大打折扣(如果只查询出a.id,或者其它索引字段,那distinct效率还是挺高的)。如果这种一对多的情况确实想用join怎么办?那只能程序中去重了。

那如果需要order by a表字段,使用join会怎样?首先解释一下个概念:驱动表。什么是驱动表?mysql在join表连接时会选择一个结果集较小的表作为驱动表,再以此遍历被驱动表,explain查看执行计划时,第一行的表就是驱动表。如果对驱动表的字段order by,是没有问题的。但是如果对被驱动表order by,则会产生using temporary,以及filesort,性能可想而知!你能保证a表一定是驱动表吗?不一定!就算没有其它任何查询条件,全表数据a表比b表小,但是随着时间推移,a表的数据也可能变得比b表大,另外再加上查询条件,两个表实际返回的结果集不敢确定谁大谁小。所以在有order by的情况下,建议使用exists或者in子查询代替,但是需要注意的是,关于in,如果a表数据量级比b表小,性能可能不是很高,而且,有时候mysql优化器会将in转化为join表连接,执行计划其实是一样的。而对于exists,如果a表数据量级比b表大,性能很可能不高,至于原因,看下面的分析。

exists:
很强大的谓词,关联子查询,子查询不返回任何结果集,只返回true或者false。在mysql5.6以前的版本中,mysql默认会将in独立子查询转化为exists关联子查询(你去看一看老版本下这两者的执行计划,是一样的)。假如a表有m行数据,b表有n行数据,它会用a表的m行数据一行一行的和b表里的数据做比较,复杂度为:m+m*n,所以它的瓶颈在于a表的数据量级,如果a表数据量级比b表大,那逻辑IO的次数就多,性能越糟糕。所以exists适合a表数据量级比b表小的子查询。

in:
最常用的子查询,在mysql5.7版本中,有些in子查询会被优化为join表连接。in适合什么场景呢?在另一篇文章《MySQL中in(常量列表)的执行计划》中提到过,如果in后面的value list很少,而且字段上存在索引,甚至是主键,那性能还是相当高的。不过即使存在索引,当value list达到一个临界值(无法确定这个经验值,根据整个表的量级而决定)时,会导致ALL全表扫描。所以,和exists使用场景相反,in适合a表数据量级比b表大的子查询。



https://www.xamrdz.com/database/68d1923171.html

相关文章: