MySQL中Join的执行过程有哪些
今天就跟大家聊聊有关MySQL中Join的执行过程有哪些,可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。
成都创新互联公司专注于正镶白企业网站建设,成都响应式网站建设公司,商城网站定制开发。正镶白网站建设公司,为正镶白等地区提供建站服务。全流程定制网站制作,专业设计,全程项目跟踪,成都创新互联公司专业和态度为您提供的服务
MySQL的Join是如何执行的
join可以说一种集合的运算,比如left join,right join,inner join,full join,outer join,cross join等,这些集合间的计算关系对应在高中数学集合里面的交集,并集,补集,全集等。但在实际的代码中,join运算基本上是通过多层循环来实现的。
举一个例子,假设有t1,t2两张表,表结构分别如下:
createtablet1(
idintnotnullAUTO_INCREMENT,
usernamevarchar(20)notnulldefault'',
ageintnotnulldefault0,
PRIMARYkey(`id`)
)ENGINE=INNODBDEFAULTCHARSET=UTF8MB4;
createtablet2(
idintnotnullauto_increment,
usernamevarchar(20)notnulldefault'',
scoreintnotnulldefalut0,
primarykey(`id`)
))ENGINE=INNODBDEFAULTCHARSET=UTF8MB4;
假设t1有100条数据,t2表有200条数
查询sql为:
selectt1.*,t2.*fromt1leftjoint2on(t1.username=t2.username)
那么这条SQL的执行步骤如下:
从表t1中取一行数据r1
从r1中,取出字段username到表t2中查询
取出表t2中满足条件的行,跟r1组成一行,作为结果集的一部份
重复执行步骤1,2,3,直到表t1的所以数据循环完毕
基本上先遍历t,1,然后根据t1中的每行数据中的username,去表t2中查找满足条件的记录。基本就是2层循环。
如何优化join查询
从上面可以看出,join本质是循环,这里的开销如下:
遍历t1数据,读取数据为t1表的行数,假设行数为n,则复杂度也为n
根据t1的匹配字段username去t2中一行一行的查询数据
这个过程,因为MySQL的数据存储结构为二叉树,时间复杂度为log2(m) m为t2表的总行数
那么总复杂度近似为 n+n(2log2(m))
从上面的步骤可以看出,优化方向:
降低t1查询时的开销,主要是磁盘io开销,避免全表扫描,用索引
降低t2查询时的开销,也用索引
将数据量多的表做被驱动表,小表作驱动表,m取了对数,大表数据量大对复杂度的影响没有线性增长
缓存t1表,不用每次去磁盘load,比如一次缓存100条,那么能显著降低磁盘读数据次数,t2每次与缓存中的t1数据进行比较
随机磁盘读比较耗费磁盘性能,转为顺序读,因为二叉树的存储结构,每次非主键查找,有一个回表的动作,即根据主键再次查询需要的数据
优化的基本方法:
减少循环次数,减少磁盘IO次数,变随机IO为顺序IO
其实MySQL针对上面的优化方法有对应的算法:
Simple Nested Loop Join 最普通的循环,这个要避免
Block Nested Loop Join 主要是针对t2表上没有索引,在步骤2将t2中的每一行数据跟join buffer数据做对比,这样将磁盘操作转为内存操作进行比较,但是如果被驱动表的数据比较大的话,也影响性能,主要是cache pool被占满,导致MySQL性能下降
Index Nested Join 就是都通过主键进行查找关联,这种性能比较好
Batched Key Access Join 这个是 Index Nested Join上做的优化,因为回表的存在,随机操作io也很耗费性能,这个算法的核心在于通过辅助索引去查找时,将得到的主键进行排序,然后按照主键递增的顺序进行查找,对磁盘的读接近顺序读,从而优化性能
到底要不用Join
从上面的分析我们可以看到,用Join还是可行的,只要性能可控且在接受范围内,还是能减少代码复杂度的。需要避免的是join的表没有索引,不然这样的SQL发线上是灾难性的。
总结
Join还是可以大胆的使用,只要把握好几个原则:
1、尽量让join的列是索引列,而且最好是类型相同,尽可能是主键索引;
2、尽量将小表做驱动表(这一点MySQL在5.6某个版本后能自动完成);
3、养成将写好的SQL进行explain的好习惯,观察SQL的执行过程。
看完上述内容,你们对MySQL中Join的执行过程有哪些有进一步的了解吗?如果还想了解更多知识或者相关内容,请关注创新互联行业资讯频道,感谢大家的支持。
文章标题:MySQL中Join的执行过程有哪些
本文网址:http://pwwzsj.com/article/gpjdep.html