专栏首页idba性能优化之Block Nested-Loop Join(BNL)

性能优化之Block Nested-Loop Join(BNL)

一 介绍

相信许多开发/DBA在使用MySQL的过程中,对于MySQL处理多表关联的方式或者说性能一直不太满意。对于开发提交的含有join的查询,一般比较抗拒,从而建议将join拆分,避免join可能带来的性能问题,同时也增加了程序和DB的网络交互。

MySQL本身只支持一种表间关联方式,就是嵌套循环(Nested Loop)。如果关联表的数据量很大,则join关联的执行时间会非常长。在5.5以后的版本中,MySQL通过引入BNL算法来优化嵌套执行,本文介绍两种join算法 Nested-Loop Join (NLJ) 和Block Nested-Loop Join(BNL) .

二 原理

2.1 Nested Loop Join算法

NLJ 算法:将驱动表/外部表的结果集作为循环基础数据,然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据,然后合并结果。如果有多表join,则将前面的表的结果集作为循环数据,取到每行再到联接的下一个表中循环匹配,获取结果集返回给客户端。 Nested-Loop 的伪算法如下:

因为普通Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内存循环便要执行多少次.在内部表的连接上有索引的情况下,其扫描成本为O(Rn),若没有索引,则扫描成本为O(Rn*Sn)。如果外部表有很多记录,则Nested-Loops Join会扫描内部表很多次,执行效率非常差。

2.2 Block Nested-Loop Join算法

BNL 算法:将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较,从而减少内层循环的次数. 举例来说,外层循环的结果集是100行,使用NLJ 算法需要扫描内部表100次,如果使用BNL算法,先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中直接匹配这10行数据,内存循环就可以一次与这10行进行比较, 这样只需要比较10次,对内部表的扫描减少了9/10。所以BNL算法就能够显著减少内层循环表扫描的次数. 前面描述的query, 如果使用join buffer, 那么实际join 算法如下:

如果t1, t2参与join的列长度只和为s, c为二者组合数, 那么t3表被扫描的次数为

(S * C)/ join_buffer_size + 1

扫描t3的次数随着join_buffer_size的增大而减少, 直到join buffer能够容纳所有的t1, t2组合, 再增大join buffer size, query 的速度就不会再变快了.

从图中可以看到把t1和t2的结果集放到join_buffer中,而不用每次t1和t2关联后马上有和t3关联,这也是没有必要的,然后只需一次扫描t3即可完成这个查询;需要注意的是join buffer中只保留查询结果中出现的列值,它的大小不依赖于表的大小,我们在伪代码中看到当join buffer被填满后,mysql将会flush buffer。

2.3 MySQL使用Join Buffer有以下要点:

  1. join_buffer_size变量决定buffer大小。
  2. 只有在join类型为all, index, range的时候才可以使用join buffer。
  3. 能够被buffer的每一个join都会分配一个buffer, 也就是说一个query最终可能会使用多个join buffer。
  4. 第一个nonconst table不会分配join buffer, 即便其扫描类型是all或者index。
  5. 在join之前就会分配join buffer, 在query执行完毕即释放。
  6. join buffer中只会保存参与join的列, 并非整个数据行。

三 如何使用

MySQL 5.6版本及以后,优化器管理参数optimizer_switch中的block_nested_loop 参数控制着BNL是否被用于优化器。默认条件下是开启,若果设置为off,优化器在选择 join方式的时候会选择NLJ算法。

四 参考资料

  1. https://dev.mysql.com/doc/refman/5.6/en/nested-loop-joins.html
  2. https://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html
  3. http://hidba.org/?p=300

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-02-24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 从库数据的查找和参数slave_rows_search_algorithms

    我们前面已经知道了对于DML语句来讲其数据的更改将被放到对应的Event中。比如‘Delete’语句会将所有删除数据的before_image放到DELETE_...

    用户1278550
  • Python| 多进程中join()的意义

    multiprocessing 是python提供的跨平台版本的多进程模块。multiprocessing可以充分利用多核,提升程序运行效率。multiproc...

    用户1278550
  • 基于consul的Redis高可用方案

    这几天在研究如何做Redis的高可用容灾方案,查询了资料和咨询DBA同行,了解到Redis可以基于consul和sentinel实现读写分离以及HA高可用方案。...

    用户1278550
  • WPF ContextMenu的使用

    <Grid.ContextMenu > <ContextMenu> <MenuItem Header="增加" Click="M...

    hbbliyong
  • C语言入门系列之8.指针的概念与应用

    指针是C语言中的一个重要的概念,也是C语言的一个重要特色。 正确而灵活地运用它,可以有效地表示复杂的数据结构;能动态分配内存;能方便地使用字符串;有效而方便地...

    cutercorley
  • React使用代理解决跨域问题

    这次又遇到跨域问题,大佬推荐我用跨域代理来解决 本文仅限使用creat-react-app来创建的项目 参考文档:https://facebook.gith...

    治电小白菜
  • ArraySegment<T>泛型结构示例

    以下代码利用ArrarSegment泛型结构,从int数组arr中取出arr[2]到arr[5] using System; using System.Col...

    菩提树下的杨过
  • iOS UITableViewCell使用详解

    - (instancetype)initWithStyle:(UITableViewCellStyle)style reuseIdentifier:(NSStr...

    珲少
  • 令仔学多线程系列(二)----自定义Queue队列

    之前做了一个新的需求,需要从每一次search请求返回中获取相关的数据,然后把这些获取到的数据做异步处理,写入缓存并同步到数据库中。如何做异步在这就想到了用队...

    令仔很忙
  • OC语言Block

    用户1941540

扫码关注云+社区

领取腾讯云代金券