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

一 介绍
  相信许多开发/DBA在使用MySQL的过程中,对于MySQL处理多表关联的方式或者说性能一直不太满意。对于开发提交的含有join的查询,一般比较抗拒,从而建议将join拆分,避免join可能带来的性能问题,同时也增加了程序和DB的网络交互。
5.5 版本之前,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 的伪算法如下:

for each row in t1 matching range {

  for each row in t2 matching reference key {

     for each row in t3 {

      if row satisfies join conditions,

      send to client

    }

  }

 }

  因为普通Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内存循环便要执行多少次.在内部表的连接上有索引的情况下,其扫描成本为O(Rn),若没有索引,则扫描成本为O(Rn*Sn)。如果内部表S有很多记录,则SimpleNested-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示意如下:

for each row in t1 matching range {

   for each row in t2 matching reference key {

    store used columns from t1, t2 in join buffer

    if buffer is full {

      for each row in t3 {

         for each t1, t2 combination in join buffer {

          if row satisfies join conditions,

          send to client

        }

       }

      empty buffer

    }

  }

}

if buffer is not empty {

   for each row in t3 {

    for each t1, t2 combination in join buffer {

      if row satisfies join conditions,

      send to client

     }

  }

}

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

(S * C)/join_buffer_size + 1 

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

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的列, 并非整个数据行。

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

时间: 2024-10-30 05:40:29

【MySQL】MySQL性能优化之Block Nested-Loop Join(BNL)的相关文章

【MySQL】性能优化之 straight_join

   研究过或者熟悉oracle性能调优的朋友都知道oracle 提供很多hint 指定from 后的表的连接顺序,如use_hash  ordered ,leading 等,而MySQL 对表的连接只支持 nested loop Join, 提供的表连接驱动的hint 只有--straight_join(相当于Oracle里面的use_nl).其语法如下:   select ..from  tab1  straiht_join  tab2 where ... straight_join 实际上

MySQL数据库性能优化之表结构优化

很多人都将<数据库设计范式>作为数据库表结构设计"圣经",认为只要按照这个范式需求设计,就能让设计出来的表结构足够优化,既能保证性能优异同时还能满足扩展性要求.殊不知,在N年前被奉为"圣经"的数据库设计3范式早就已经不完全适用了.这里我整理了一些比较常见的数据库表结构设计方面的优化技巧,希望对大家有用. 这是 MySQL数据库性能优化专题 系列的第二篇文章:MySQL 数据库性能优化之表结构优化 系列的第一篇文章:MySQL 数据库性能优化之缓存参数优化

MySQL数据库性能优化之缓存参数优化

在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题,所以最近打算写一个MySQL数据库性能优化方面的系列文章,希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助. 数据库属于 IO 密集型的应用程序,其主要职责就是数据的管理及存储工作.而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级.所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO.本文先从

Mysql数据库性能优化一_Mysql

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情.当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库. mysql的性能优化无法一蹴而就,必须一步一步慢慢来,从各个方面进行优化,最终性能就会有大的提升. Mysql数据库的优化技术 对mysql优化是

Mysql数据库性能优化三(分表、增量备份、还原)_MsSql

接上篇Mysql数据库性能优化二 对表进行水平划分           如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了.如果我拆成100个表,那么每个表只有10万条记录.当然这需要数据在逻辑上可以划分.一个好的划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势.比如系统界面上只提供按月查询的功能,那么把表按月拆分成12个,每个查询只查询一个表就够了.如果非要按照地域来分,即使把表拆的再小,查询还是要联合所有表来查,还不如不拆了.所以一个好的拆分依据

解析MySQL数据库性能优化的六大技巧_Mysql

数据库表表面上存在索引和防错机制,然而一个简单的查询就会耗费很长时间.Web应用程序或许在开发环境中运行良好,但在产品环境中表现同样糟糕.如果你是个数据库管理员,你很有可能已经在某个阶段遇到上述情况.因此,本文将介绍对MySQL进行性能优化的技巧和窍门. 1.存储引擎的选择如果数据表需要事务处理,应该考虑使用InnoDB,因为它完全符合ACID特性.如果不需要事务处理,使用默认存储引擎MyISAM是比较明智的.并且不要尝试同时使用这两个存储引擎.思考一下:在一个事务处理中,一些数据表使用Inno

Mysql数据库性能优化三(分表、增量备份、还原)

接上篇Mysql数据库性能优化二 对表进行水平划分 如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了.如果我拆成100个表,那么每个表只有10万条记录.当然这需要数据在逻辑上可以划分.一个好的划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势.比如系统界面上只提供按月查询的功能,那么把表按月拆分成12个,每个查询只查询一个表就够了.如果非要按照地域来分,即使把表拆的再小,查询还是要联合所有表来查,还不如不拆了.所以一个好的拆分依据是 最重要的.关键字

淘宝内部分享:MySQL &amp; MariaDB性能优化

编者按:MySQL是目前使用最多的开源数据库,但是MySQL数据库的默认设置性能非常的差,必须进行不断的优化,而优化是一个复杂的任务,本文描述淘宝数据库团队针对MySQL数据库Metadata Lock子系统的优化,hash_scan 算法的实现解析的性能优化,TokuDB·版本优化,以及MariaDB·的性能优化.本文来自淘宝团队内部经验分享. 往期文章:淘宝内部分享:怎么跳出MySQL的10个大坑 MySQL· 5.7优化·Metadata Lock子系统的优化 背景 引入MDL锁的目的,最

Mysql数据库性能优化一些经验分享

一,性能检测与瓶颈分析 1性能检测常用命令 show status 显示状态信息,参考:Mysql show status命令详解 show processlist 查看当前SQL执行,包括执行状态.是否锁表等,参考:Mysql show processlist命令详解 show variables 显示系统变量,参考:Mysql show variables命令详解 2瓶颈分析常用命令 获取mysql用户下的进程总数 ps -ef | awk '{print $1}' | grep "mysq