您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

一文搞懂MySQL的Join,聊一聊秒杀架构设计

时间:2019-08-07 16:42:46  来源:  作者:
一文搞懂MySQL的Join,聊一聊秒杀架构设计

 

正文

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两张表,表结构分别如下:

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的执行步骤如下:

  1. 从表t1中取一行数据r1
  2. 从r1中,取出字段username到表t2中查询
  3. 取出表t2中满足条件的行,跟r1组成一行,作为结果集的一部份
  4. 重复执行步骤1,2,3,直到表t1的所以数据循环完毕

基本上先遍历t,1,然后根据t1中的每行数据中的username,去表t2中查找满足条件的记录。基本就是2层循环。

如何优化join查询

从上面可以看出,join本质是循环,这里的开销如下:

  1. 遍历t1数据,读取数据为t1表的行数,假设行数为n,则复杂度也为n
  2. 根据t1的匹配字段username去t2中一行一行的查询数据
  3. 这个过程,因为MySQL的数据存储结构为二叉树,时间复杂度为log2(m) m为t2表的总行数
  4. 那么总复杂度近似为 n+n(2log2(m))

从上面的步骤可以看出,优化方向:

  1. 降低t1查询时的开销,主要是磁盘io开销,避免全表扫描,用索引
  2. 降低t2查询时的开销,也用索引
  3. 将数据量多的表做被驱动表,小表作驱动表,m取了对数,大表数据量大对复杂度的影响没有线性增长
  4. 缓存t1表,不用每次去磁盘load,比如一次缓存100条,那么能显著降低磁盘读数据次数,t2每次与缓存中的t1数据进行比较
  5. 随机磁盘读比较耗费磁盘性能,转为顺序读,因为二叉树的存储结构,每次非主键查找,有一个回表的动作,即根据主键再次查询需要的数据

优化的基本方法:

  1. 减少循环次数,减少磁盘IO次数,变随机IO为顺序IO
  2. 其实MySQL针对上面的优化方法有对应的算法:
  3. Simple Nested Loop Join 最普通的循环,这个要避免
  4. Block Nested Loop Join 主要是针对t2表上没有索引,在步骤2将t2中的每一行数据跟join buffer数据做对比,这样将磁盘操作转为内存操作进行比较,但是如果被驱动表的数据比较大的话,也影响性能,主要是cache pool被占满,导致MySQL性能下降
  5. 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的执行过程


Tags:MySQL Join   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
正文MySQL的Join到底能不能用经常听到2种观点: join性能低,尽量少用 多表join时,变为多个SQL进行多次查询其实对于上面的观点一定程度上是正确的,但不是完全正确。但之所以流传...【详细内容】
2019-08-07  Tags: MySQL Join  点击:(177)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条