专栏首页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 条评论
登录 后参与评论

相关文章

  • Mysql几种join连接算法

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

    黎明大大
  • MySQL - Join关联查询优化 --- NLJ及BNL 算法初探

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

    小小工匠
  • 【MySQL】之join算法详解

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

    MySQL数据库技术栈
  • 35 | join语句优化

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

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

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

    三哥
  • 性能优化之--BKA

    一 介绍 MySQL 5.6版本提供了很多性能优化的特性,其中之一是关于提高表join性能的算法:Batched Key Access (BKA) ,本文将结...

    用户1278550
  • MySQL 8.0 新特性之Hash Join

    上周在公司做了针对MySQL 8.0新特性相关的分享,提到MySQL 8.0新特性,不得不提到的就是HashJoin,MySQL一直被人诟病没有实现HashJo...

    SEian.G
  • 细品mysql之Join 语句的执行过程

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

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

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

    青石路
  • MySQL慢查询优化 | 联结原理

    前段时间笔者开发某个项目遇到了MySQL性能问题,每张表的数据量都在五千万以上,个别表数据量甚至在一个亿以上,在开发的过程中遇到了非常多的数据库性能优化难点,笔...

    王知无-import_bigdata
  • MySQL实战第四十四讲- 要不要使用分区表?

    在我看来,能够帮我们扩展一个逻辑的边界的问题,就是好问题。因为通过解决这样的问题,能够加深我们对这个逻辑的理解,或者帮我们关联到另外一个知识点,进而可以帮助我们...

    越陌度阡
  • 要面试了,你还没有掌握MySQL join的原理?

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

    hugo_lei
  • Nested-Loop Join Algorithms

    MySQL使用嵌套循环算法来实现多表之间的联接。 Nested-Loop Join Algorithms 一个简单的嵌套循环联接(NLJ)算法,循环从第一个表中...

    高爽
  • 掌握MySQL连接查询到底什么是驱动表

    left join 左连接,用法如下,这种查询会把左表(student)所有数据查询出来,右表不存在的用空表示,结果图如下

    阿伟
  • mysql join关联查询需注意的问题

    居士
  • 写出好的Join语句,前提你得懂这些

    因为驱动结果集越大,意味着需要循环的次数越多,也就是说在被驱动结果集上面所 需要执行的查询检索次数会越多。

    用户4172423
  • MYSQL What's new in 优化和执行 来自旧金山的问候

    以下内容采集自 2019年9月19日 San Francisco Oracle open 大会内容。主题 What’s New in MySQL Optim...

    AustinDatabases
  • MySQL 的 join 功能弱爆了?

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

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

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

    程序员历小冰

扫码关注云+社区

领取腾讯云代金券