解析MySQL join查询的原理

mysql用nested-loop join算法实现join查询

区分驱动表和被驱动表,以驱动表的结果集为循环的基础,访问被驱动表过滤数据,然后合并结果,驱动表在外循环、被驱动表在内循环。
如果还有第三张参与join查询的表,则以合并的结果为驱动表,第三张表作为被驱动表,以此类推。

left join中的左表是驱动表、右表是被驱动表,right join刚好相反。

nested-loop join有三种实现

snlj

simple nested-loop join

假设a是驱动表,b是被驱动表。

这里会扫描a表,用a的结果集作为外循环,
每循环一次,会扫描b表一遍(遍历内循环)

a表有n行,b表有m行。

snlj的开销如下(最大情况下):

扫描a表1次;
扫描b表n次。
总共读取记录数:n + n * m。

为了专注于理解nested-loop join,这里不讨论带where子句的情况,以下相同。

bnlj

block nested-loop join

假设a是驱动表,b是被驱动表。

用来join的字段在被驱动表没有建立索引

join buffer
mysql会将驱动表结果集中(多条记录)用来join的字段缓存到join buffer,
join buffer的特点是只需要扫描被驱动表一次,就能得到join buffer中所有记录的匹配结果,
减少扫描的次数。

join buffer默认大小256k,会生成n-1个join buffer缓冲区,n为参与join查询的表数量。

a表有n行,b表有m行。

bnlj的开销如下(最大情况下):

扫描a表1次;
扫描b表x次;
x的大小取决于n、join字段的大小、join buffer的大小,通常x<<n。

inlj

index nested-loop join

假设a是驱动表,b是被驱动表。

用来join的字段在被驱动表建立了索引

聚集索引

非聚集索引

在这里我们假设您已对mysql的索引结构有了一定的了解,
如果没有的话,可以去看下:通过b+tree平衡多叉树理解innodb引擎的聚集和非聚集索引

这里会扫描a表,用a的结果集作为外循环,
然后通过b表的索引来检索,不会遍历b表。

a表有n行,b表有m行。

inlj的开销如下(最大情况下):

扫描a表1次;
通过b表索引检索n次,成本比扫描b表n次会低很多;
回表:先找到非聚集索引,再找到聚集索引,会多一次磁盘io。

nlj优先级

inlj>bnlj>snlj

如何优化join查询效率

尽量将小表作为驱动表,大表作为被驱动表;
为参加join的字段在被驱动表建立聚集索引,其次是非聚集索引;
尽可能减少join的字段数量,或者使用长度比较小的字段来join,这样join buffer一次可以缓存更多条记录。

inner join时,mysql会自动将小表作为驱动表,大表作为被驱动表。

扫描整张表是成本非常高的操作。

到此这篇关于mysql join查询的原理的文章就介绍到这了,更多相关mysql join查询内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐