问题7:数据库多表关联方式

MySql 专栏收录该内容
11 篇文章 0 订阅

在关系型数据库中,多表关联方式是影响性能最大的技术。为什么会把mysql定义为中小型数据库?主要原因是mysql是不支持hash join的。这对多个大表关联查询造成性能的瓶颈。因此,在大数据领域,比较少使用mysql作为后台数据库。不过,也是有规避的方法,例如我们公司bi项目使用mysql,需要把表设计为宽表,进行反范式设计,减少多表关联。虽然单表的数据量大了,但是查询速度快了非常多,从几十秒降到1秒左右。1秒对大数据应用是可以接受的,在oltp应用是不可接受。因此,在oltp应用数据库会进行分库分表设计,这属于垂直拆分。按照业务类型进行拆分多个实例和分库,这属于水平拆分。目标是实现轻数据库重业务模式,很多运算需要放到应用代码执行。这也是基于数据库是CS结构,而业务代码是分布式结构的属性。
言归正传,下面是对数据库关联算法进行分析,不限于mysql。
多表连接的查询方式又分为以下几种:内连接,外连接和交叉连接。外连接又分为:左外连接,右外连接和全外连接。查询方式需要下面多表关联算法来实现:
Nest loop join
嵌套循环关联 适合下面场景,外表是小表,内表是大表,每次外表获取一条记录,会在内表进行一次查询,虽然内表是大表,由于使用高区分度的索引,效率也是非常快。典型的案例维表驱动事实表。
官方文档:http://dev.mysql.com/doc/refman/5.6/en/nested-loop-joins.html
Block Nested-Loop Join
块圈套循环关联 从 mysql 5.6开始支持新的关联方式,每次批量取出部分符合内表的记录,并发处理与内表关联,减少关联次数,提高关联效率。是圈套循环关联的优化版本。
Multi-Range Read(MRR)
多范围读关联,mysql 5.6开始支持新的关联方式,将根据辅助索引获取的结果集根据主键进行排序,将乱序化为有序,可以用主键顺序访问基表,将随机读转化为顺序读,多页数据记录可一次性读入或根据此次的主键范围分次读入,以减少IO操作,提高查询效率。
官方文档: http://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html
Batched Key Access(BKA)
批量键接口关联 从mysql 5.6开始支持新的关联方式,
BKA使用join buffer保存由join的第一个操作产生的符合条件的数据。 然后BKA算法构建key来访问被连接的表,并批量使用MRR接口提交keys到数据库存储引擎去查找查找。提交keys之后,MRR使用最佳的方式来获取行并反馈给BKA .
Sort Merge join
排序混合关联 mysql目前不支持,oracle,sql server,postgresql支持。需要首先对两个表按照关联的字段进行排序,分别从两个表中取出一行数据进行匹配,如果合适放入结果集;不匹配将较小的那行丢掉继续匹配另一个表的下一行,依次处理直到将两表的数据取完。若是关联字段有高区分度的索引,则效率也是非常高。
Hash join
哈希关联 mysql目前不支持,oracle,postgresql支持。下面详细描述oracle的hash join实现方法:Oracle依据hash_area_size/db_block_size/_hash_multiblock_io_count决定hash partition数量,hash表由若干hash partition组成,而每个partition都包含多个hash bucket。表small和big,前者会被选为驱动表,假定其结果集为s,后者为b;驱动表和被驱动表都是最多只被访问一次。哈希表连接的表无需要排序,但是他在做连接之前做哈希运算的时候,会用到HASH_AREA_SIZE来创建哈希表。哈希连接不适用于的连接条件是:不等于<>,大于>,小于<,小于等于<=,大于等于>=,like。哈希连接索引列在表连接中无特殊要求,与单表情况无异。
目前mysql在该方面相对来说是较弱的,我们应该取长补短,使用mysql性能高的地方,减少使用mysql短板的技术。

  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值