Nested-Loop Join Algorithms 一个简单的嵌套循环联接（NLJ）算法，循环从第一个表中依次读取行，取到每行再到联接的下一个表中循环匹配。...Block Nested-Loop Join Algorithm 一个块嵌套循环联接（BNL）算法，将外循环的行缓存起来，读取缓存中的行，减少内循环的表被扫描的次数。
在5.5以后的版本中，MySQL通过引入BNL算法来优化嵌套执行,本文介绍两种join算法 Nested-Loop Join (NLJ) 和Block Nested-Loop Join(BNL) ....Nested-Loop 的伪算法如下: ?...因为普通Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内存循环便要执行多少次.在内部表的连接上有索引的情况下，其扫描成本为O(Rn),若没有索引,则扫描成本为O(...2.2 Block Nested-Loop Join算法 BNL 算法:将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数.
官方资料如下： https://dev.mysql.com/doc/refman/5.7/en/nested-loop-joins.html mysql涉及到的join算法：Nested-Loop Join...和 Block Nested-Loop Join ---- 关于：Nested-Loop Join 在mysql中，Nested-Loop Join是嵌套循环连接，看下官方例子： select t1...---- 关于：Block Nested-Loop Join，是Nested-Loop Join的一种优化，叫缓存块嵌套循环连接，缓存嵌套循环连接是通过一次性缓存多条数据，把参与查询的缓存的列缓存到join...先简单看下join buffer 说明 join buffer size默认是256k，可以配置 join buffer会缓存所有参与查询的列而不是只有join的列 我们再看下Nested-Loop Join
mysql只支持一种join算法：Nested-Loop Join（嵌套循环连接），但Nested-Loop Join有三种变种： 原理： 1.Simple Nested-Loop Join： 如下图...2.Index Nested-Loop Join（索引嵌套）： 这个要求非驱动表（匹配表s）上有索引，可以通过索引来减少比较，加速查询。...3.Block Nested-Loop Join： 如果有索引，会选取第二种方式进行join，但如果join列没有索引，就会采用Block Nested-Loop Join。...使用Block Nested-Loop Join，如果b表数据少，作为驱动表，将b的需要的数据缓存到join buffer中，批量对a表扫描 2.left join： ?...这里用到了索引，所以会采用Index Nested-Loop Join，因为没有筛选条件，会选择一张表作为驱动表去进行join，去关联非驱动表的索引。 如果加了条件 ?
在5.5以后的版本中，MySQL通过引入INLJ和BNL算法来优化嵌套执行, 今天主要介绍三种join算法 Nested-Loop Join (NLJ) 和 Index Nested-Loop Join...(INLJ) 和Block Nested-Loop Join(BNL) ....Mysql常见的几种算法 1.嵌套循环连接算法（Nested-Loop Join(NLJ)） 2.基于索引的嵌套循环连接算法（Index Nested-Loop Join(INLJ)） 3.基于块的嵌套循环连接算法...如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高 Join 算法总结 不论是Index Nested-Loop Join 还是 Block Nested-Loop Join...都是在Simple Nested-Loop Join 的算法的基础上 减少嵌套的循环次数， 不同的是 Index Nested-Loop Join 是通过索引的机制减少内层表的循环次数，Block
MySQL有三种关联逻辑处理策略，分别为：Index Nested-Loop Join、Simple Nested-Loop Join、Block Nested-Loop Join。...Index Nested-Loop Join 索引嵌套循环连接，MySQL选择驱动表与被驱动表关联逻辑之一。...什么情况下MySQL会选择Index Nested-Loop Join？ 当驱动表关联被驱动表的字段上具有索引时，会使用本策略。...当然，MySQL 也没有使用这个Simple Nested-Loop Join算法，而是使用了另一个叫作“Block Nested-Loop Join”的算法，简称 BNL。...如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的； 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。
Join原理 mysql的join算法叫做Nested-Loop Join（嵌套循环连接） 而这个Nested-Loop Join有三种变种，下面分别介绍下 Simple Nested-Loop 这个算法相当简单...Index Nested-Loop 这个是基于索引进行连接的算法 它要求被驱动表上有索引，可以通过索引来加速查询。...下图相比更好地解释了Block Nested-Loop Join算法的运行过程 ?...Nested-Loop Join 当不使用Index Nested-Loop Join的时候，默认使用Block Nested-Loop Join。...3.为匹配的条件增加索引：争取使用Index Nested-Loop Join，减少内层表的循环次数 4.增大join buffer size的大小：当使用Block Nested-Loop Join时
在Mysql的实现中，Nested-Loop Join有3种实现的算法： Simple Nested-Loop Join：SNLJ，简单嵌套循环连接 Index Nested-Loop Join：INLJ...> Block Nested-Loop Join > Simple Nested-Loop Join 本文围绕连个问题展开： DBA不让使用join，使用join有什么问题？...用到的算法是 Index Nested-Loop Join 。...Block Nested-Loop Join 上述红框用到了一个 Using join buffer ，其实就是该优化的算法 Block Nested-Loop Join 这时候，被驱动表上没有可用的索引...如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的； 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。
Join 算法，这时性能还是很好的；但是，用不上被驱动表的索引的时候，使用的 Block Nested-Loop Join 算法性能就差多了，非常消耗资源。...Join 和 Block Nested-Loop Join 两种情况进行优化。...优化 Index Nested-Loop Join 假设我们已经在 age 字段上建立了索引，那么下面这条 sql 语句用到的就是 Index Nested-Loop Join 算法，回顾下具体的执行逻辑...之前我们分析过 Block Nested-Loop Join 算法中用到了 join_buffer，而 Index Nested-Loop Join 并没有用到，这不，在优化这里派上用场了。...Nested-Loop 对于 Index Nested-Loop 来说，具体步骤其实就是一个嵌套查询，首先，遍历驱动表，然后，对这每一行都去被驱动表中根据 on 条件字段进行搜索，由于被驱动表上建立了条件字段的索引
1.嵌套循环连接算法(Nested-Loop Join Algorithm) 一个简单的嵌套循环连接（NLJ）算法从循环中的第一个表中逐行读取一行，将每行传递给处理连接中下一个表的嵌套循环。...于是，mysql5.5对这个算法进行了优化，新增了Index Nested-loop Join，Block Nested-loop Join。...2.索引嵌套循环连接算法(Index Nested-loop Join Algorithm) Index Nested-loop Join是针对有索引的情况，而Block Nested-loop Join...3.块嵌套循环连接算法(Block Nested-Loop Join Algorithm) Block Nested-loop Join 块嵌套循环（BNL）连接算法使用在外部循环中读取的行的缓冲来减少必须读取内部循环中的表的次数
如果只看结果的话，这个算法是正确的，而且这个算法也有一个名字，叫做“Simple Nested-Loop Join”。...当然，MySQL 也没有使用这个 Simple Nested-Loop Join 算法，而是使用了另一个叫作“Block Nested-Loop Join”的算法，简称 BNL。...Block Nested-Loop Join 这时候，被驱动表上没有可用的索引，算法的流程是这样的： 1. ...如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表； 2. 如果是 Block Nested-Loop Join 算法： (1). ...通过对 Index Nested-Loop Join 和 Block Nested-Loop Join 两个算法执行过程的分析，我们也得到了文章开头两个问题的答案： 1.
ref | idx_rule_id | idx_rule_id | 4 | sinanet.a.id | 1 | Using index | MySQL是只支持一种JOIN算法Nested-Loop...Join（嵌套循环链接） 当关联字段有索引时,走的是Index Nested-Loop Join(索引嵌套链接) 没有索引时会走,Block Nested-Loop Join比Simple Nested-Loop
如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的； 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。...如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；如果是 Block Nested-Loop Join 算法： 在 join_buffer_size 足够大的时候，是一样的...使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。
Simple Nested-Loop Join 我们来看一下当进行 join 操作时，mysql是如何工作的。常见的 join 方式有哪些？...如图，当我们进行连接操作时，左边的表是「驱动表」，右边的表是「被驱动表」 Simple Nested-Loop Join 这种连接操作是从驱动表中取出一条记录然后逐条匹配被驱动表的记录，如果条件匹配则将结果返回...Join」 算法 Index Nested-Loop Join 了解了 「Block Nested-Loop Join」 算法之后，可以看到驱动表的每条记录会把被驱动表的所有记录都匹配一遍，非常耗时，...「如果是 Block Nested-Loop Join 算法：」 当 join buffer 足够大时，谁做驱动表没有影响 当 join buffer 不够大时，应该选择小表做驱动表（小表数据量少，放入...join buffer 的次数少，减少表的扫描次数） 「如果是 Index Nested-Loop Join 算法」 假设驱动表的行数是M，因此需要扫描驱动表M行 被驱动表的行数是N，每次在被驱动表查一行数据
Nested Loop 里面又有三种细分的连接方式，分别是Simple Nested-Loop Join、Index Nested-Loop Join、Block Nested-Loop Join，接下来我们就分别去看一下这三种细分的连接方式...02.Simple Nested-Loop Join Simple Nested-Loop Join 是这三种方法里面最简单，最好理解，也是最符合大家认知的一种连接方式，现在有两张表table A 和...03.Index Nested-Loop Join Index Nested-Loop Join 这种方法中，我们看到了 Index，大家应该都知道这个就是索引的意思，这个 Index 是要求非驱动表上要有索引...04.Block Nested-Loop Join 理想情况下，用索引匹配是最高效的一种方式，但是在现实工作中，并不是所有的列都是索引列，这个时候就需要用到 Block Nested-Loop Join
MySQL 的 join 算法：Nested-Loop 和其变种算法 Block Nested-Loop MySQL8.0.18 引入的 hash join 其实可以算是对 Block Nested-Loop...Nested-Loop 算法：外循环和内循环 t1、t2 两表关联时，最简单的 Nested-Loop 的算法如下： for each row in t1 matching range { for...Block Nested-Loop(BNL)的由来 按照 Nested-Loop 算法，如果 inner 表的关联字段有索引，则在内循环中 inner 表可以利用索引查找数据，查找次数等于 outer...如果 t1.a、t2.a 都有索引，且基数高，则效率最高的算法是 Nested-Loop，由于有索引，通常我们会改称其为 Index Nested-Loop，则会选择小表作为 outer 表，这样循环的次数会更少...其他数据库 从上文可以看出，outer 表脱胎于“外循环”，而外循环严格来说是 Nested-Loop 算法中的定义。
Simple Nested-Loop Join（简单的嵌套循环连接） 简单嵌套循环算法的查询过程是嵌套查询，这个关联查询语句首先不能确定那个是驱动表，因为使用join的话，mysql的优化器会自己进行索引的选择...Index Nested-Loop Join（索引嵌套循环连接） 在使用了straight_join的意思就是我们明确指出t1是驱动表，t2被驱动表。...Block Nested-Loop Join（缓存块嵌套循环连接） 刚说的 Simple Nested-Loop Join 算法在MySQl中没有使用，那要是两张表的关联字段都没有使用索引的话，那mysql...那就是使用Block Nested-Loop Join这个算法 查询过程：把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1...如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；如果是 Block Nested-Loop Join 算法：在 join_buffer_size 足够大的时候，是一样的
Simple Nested-Loop Join Simple Nested-Loop Join算法是指读取驱动表t1中的每行数据，将每行数据传递到被驱动表t2上，取出被驱动表t2中满足条件的行,...Index Nested-Loop Join index nested-loop join算法的优化思路是通过驱动表的匹配条件，直接与被驱动表的索引进行匹配，减少了被驱动表的扫描次数。...这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以称之为“Index Nested-Loop Join”，简称 NLJ。...Block Nested-Loop Join Block Nested-Loop join，基于块的嵌套循环，简称BNL算法，其优化思路主要是减少被驱动表的循坏次数，它会将驱动表的数据缓存起来，
像 Oracle 和 MySQL 都使用了嵌套循环（Nested-Loop Join）的实现方式。 我们这边探讨一下 MySQL 的实现。 2....原理 Nested-Loop Join 算法，需要区分驱动表和被驱动表，先访问驱动表，筛选出结果集，然后将这个结果集作为循环的基础，访问被驱动表过滤出需要的数据。...不同 Nested-Loop Join ，讨论其实是对内循环的优化。 为了更专注于 Nested-Loop Join 的讨论，我们这里的 join 操作都不带 where 子句对结果集进行过滤。...SNLJ SNLJ，Simple Nested-Loop Join，简单嵌套循环。这是最简单的方案，性能也一般。对内循环没优化。 假设 A 是驱动表，B 是被驱动表。 ?...INLJ INLJ，Index Nested-Loop Join，索引嵌套循环。 整个算法过程和 SNL 一致，最大的区别在于，用来进行 join 的字段已经在被驱动表中建立了索引。
联表算法 MySQL 的联表算法是基于嵌套循环算法（nested-loop algorithm）而衍生出来的一系列算法，根据不同条件而选用不同的算法 在使用索引关联的情况下，有 Index Nested-Loop...join 和 Batched Key Access join 两种算法; 在未使用索引关联的情况下，有 Simple Nested-Loop join 和 Block Nested-Loop join...两种算法; Simple Nested-Loop 简单嵌套循环，简称 SNL；逐条逐条匹配，就像这样 ?...n（表中记录数） 的 m（表的数量） 次方，所以 MySQL 做了优化，联表查询的时候不会出现这种算法，即使在无 WHERE 条件且 ON 的连接键上无索引时，也不会选用这种算法 Block Nested-Loop...Index Nested-Loop 索引嵌套循环，简称 INL，是基于被驱动表的索引进行连接的算法；驱动表的记录逐条与被驱动表的索引进行匹配，避免和被驱动表的每条记录进行比较，减少了对被驱动表的匹配次数
领取专属 10元无门槛券
手把手带您无忧上云
云服务器
ICP备案
实时音视频
对象存储
即时通信 IM
