一文搞懂MySQL的Join

  点击上方 "程序员小乐" ，关注公众号

8点20分，第一时间与你相约

每日英文

A single hand that wipes tears during failures is much better than countless hands that come together to clap on success. 

失败的时候，一只拭去你泪痕的手，要比成功的时候，无双只向你祝贺的手重要。

每日掏心话

有的时候你得去在乎的少一些，那么你才是能去看到有没有人会在乎得多一些。

来自：xcrossed | 责编：乐乐

链接：jianshu.com/p/e07dd9782b42

图片来自网络

往日回顾：线上故障处理深入思考，看这篇就够了！   正文   MySQL的Join到底能不能用经常听到2种观点：join性能低，尽量少用多表join时，变为多个SQL进行多次查询其实对于上面的观点一定程度上是正确的，但不是完全正确。但之所以流传这么广，主要还是没有搞清楚实际状态，而根据实际使用中总结出来的一些模糊规律。只有了解的MySQL的Join实际执行方式，就会知道上面2种观点是一种模糊的规律，这种规律并不能指导我们实际开发。下面就说说MySQL的实际join执行方式。MySQL的Join是如何执行的join可以说一种集合的运算，比如left join,right join,inner join,full join,outer join，cross join等，这些集合间的计算关系对应在高中数学集合里面的交集，并集，补集，全集等。但在实际的代码中，join运算基本上是通过多层循环来实现的。举一个例子，假设有t1,t2两张表，表结构分别如下：create table t1 (id int not null AUTO_INCREMENT,username varchar(20) not null default '',age int not null default 0,PRIMARY key (`id`))ENGINE=INNODB DEFAULT CHARSET=UTF8MB4  ;create table t2(id int not null auto_increment,username varchar(20) not null default '',score int not null defalut 0,primary key (`id`)))ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;假设t1有100条数据，t2表有200条数查询sql为：select t1.*,t2.* from t1 left join t2 on(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还是可以大胆的使用，只要把握好几个原则：尽量让join的列是索引列，而且最好是类型相同,尽可能是主键索引尽量将小表做驱动表（这一点MySQL在5.6某个版本后能自动完成）养成将写好的SQL进行explain的好习惯，观察SQL的执行过程你对MySQL的join的理解？欢迎在留言区留下你的观点，一起讨论提高。如果今天的文章让你有新的启发，学习能力的提升上有新的认识，欢迎转发分享给更多人。欢迎各位读者加入程序员小乐技术群，在公众号后台回复“在网站上添加加入QQ群的按钮 加群按钮 网站加群按钮 添加加群按钮 QQ群按钮”或者“学习学习学习学习学习学习学习学习学习学习学习学习学习学习学习学习学习学习学习学习”即可。

猜你还想看

阿里、腾讯、百度、华为、京东、搜狗和滴滴最新面试题汇集

10张 GIF 动图让你弄懂递归等概念

切换到Linux工作，世界更美好

阿里HR的10条求职黄金定律，进大厂不可错过！

史上最烂的项目：苦撑 12 年，600 多万行代码史上最烂项目：苦撑12年，600多万行代码...聊一聊秒杀架构设计

关注「程序员小乐」，收看更多精彩内容