学习
实践
活动
专区
工具
TVP
写文章
专栏首页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
登录 后参与评论
0 条评论

相关文章

  • Mysql几种join连接算法

    相信有开发或DBA小伙伴,对于mysql处理多表关联方式或者说性能方面一直不太满意,对于开发提交的join查询,一般都是比较抗拒的,从而建议将join进行拆分,...

    黎明大大
  • SQL 不知道咋优化?吹一手 join 语句的优化准没错

    面试最怕遇到的问题是什么,如何做优化一定当仁不让,SQL 优化更是首当其冲,这里先跟大家分享一个比较容易理解的 join 语句的优化~

    飞天小牛肉
  • 【MySQL】之join算法详解

    在阿里巴巴的java开发手册有这么一条强制规定:超过三个表禁止join,需要join的字段,数据类型保持绝对一致,多表关联查询时,要保证被关联的字段需要...

    MySQL数据库技术栈
  • 技术分享 | 咬文嚼字之驱动表 & outer表

    爱可生 DBA 团队成员,擅长故障分析、性能优化,个人博客:https://www.jianshu.com/u/a95ec11f67a8,欢迎讨论。

    爱可生开源社区
  • MySQL - Join关联查询优化 --- NLJ及BNL 算法初探

    两个表 t1 和 t2 , 一样的,包括索引信息 a 字段有索引 b字段没有索引。

    小小工匠
  • MySQL实战第三十五讲- join语句怎么优化?

    在上一篇文章中,我和你介绍了 join 语句的两种算法,分别是 Index Nested-Loop Join(NLJ) 和 Block Nested-Loop ...

    越陌度阡
  • 35 | join语句优化

    一般来说,使用join语句,会用到两种算法,分别是Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BN...

    HaC
  • 为何阿里不推荐MySQL使用join?

    今天这篇文章,我就先跟你说说join语句到底是怎么执行的,然后再来回答这两个问题。

    JavaEdge
  • SQL联表细节,MySQL JOIN 的执行过程

      对于 MySQL 的 JOIN,不知道大家有没有去想过他的执行流程,亦或有没有怀疑过自己的理解(自信满满的自我认为!);如果大家不知道怎么检验,可以试着回答...

    三哥
  • 神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(一)

      我:嗨,老板娘,有冰红茶没   老板娘:有   我:多少钱一瓶   老板娘:3块   我:给我来一瓶,给,3块   老板娘:来,你的冰红茶   我:玩呐,我...

    青石路
  • 细品mysql之Join 语句的执行过程

    今天优化了一个,join关联查的语句,需要优化join的语句,那我们肯定得了解他的一个执行过程。正所谓知己知彼,百战百胜!!

    袁新栋-jeff.yuan
  • Join 语句执行过程性能差,原因可能是什么?哪里需要建立索引?

    可能你会一脸懵逼,But 实际上,其实考的就是 join 这个知识点,不难,看完这篇文章你就会啦~

    飞天小牛肉
  • MySQL 5.7 和 8.0 几处细节上的差异

    MySQL 8.0 相对于 MySQL 5.7,有很多新特性,比如:快速加列、原子 DDL、不可见索引、额外端口、角色管理等。这一节内容,就不讲这些新特性了,只...

    数据库交流
  • MySQL 的 join 功能弱爆了?

    关于MySQL 的 join,大家一定了解过很多它的“轶事趣闻”,比如两表 join 要小表驱动大表,阿里开发者规范禁止三张表以上的 join 操作,MySQL...

    程序员历小冰
  • MySQL 的 join 功能弱爆了?

    关于MySQL 的 join,大家一定了解过很多它的“轶事趣闻”,比如两表 join 要小表驱动大表,阿里开发者规范禁止三张表以上的 join 操作,MySQL...

    程序员历小冰
  • left join使用不当性能居然相差58倍

    hello,everyone.爱情只会影响我们打代码的速度,七夕节,我当然打代码了!!!

    柏炎
  • 要面试了,你还没有掌握MySQL join的原理?

    join 是 MySQL 用来进行联表操作的,用来匹配两个表的数据,筛选并合并出符合我们要求的结果集。

    hugo_lei

扫码关注腾讯云开发者

领取腾讯云代金券