mysql语句及优化之----join以及join的优化_a 和 b 两个表做等值连接(inner join) 怎么优化

目录

一、内连 inner join

二、左连 left join 和 右连 right join

三、全连 full join

四、join语句的底层算法

4.1 NLJ算法(index Nested-Loop Join)

4.11 驱动表与被驱动表

4.2  BNL算法(Block Nested-Loop Join)

4.3  BKA算法（Batched Key Access）

4.3.1  MRR（即：Multi-Range Read）

4.3.2 BKA算法

4.4  Hash Join算法

总结：

五、join语句的优化

5.1 用小表驱动大表

5.2 直接给被驱动表的join的关联字段加索引。促使BNL转为BKA

---

join大致分为 内连 inner join，左连 left join，右连 right join，全连 full join，各种情况如下图：

一、内连 inner join

下面有两个表，分别为 Websites表 和 access_log 表。

Websites表：

access_log表：

"Websites" 表中的 "id" 列指向 "access_log" 表中的字段 "site_id"。上面这两个表是通过 "site_id" 列联系起来的。

然后，如果我们运行下面的 SQL 语句（包含 INNER JOIN）：

（意思是 输出 Websites中id等于access_log中site_id 的数据）

奇怪的知识：

inner join 和 where 语句实现的效率是一样的。

例如：
select a.*,b.* from a inner join b on a.id = b.id
等同于
select a.*,b.* from a,b where a.id = b.id

二、左连 left join 和 右连 right join

左连 left join：

left join会从左表中返回所有行，即使右表中没有匹配的行。

例如有这两张表：

然后想输出Persons中每个人对应的订单(orderNo)，执行如下sql语句：

（一对多的情况也会打印，例如一个 Adams可能对应多个orderNo）

右连 和左连其实差不多，反过来而已。

 

三、全连 full join

FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配，或者如果 "Orders" 中的行在表 "Persons" 中没有匹配，这些行同样会列出。

例子：

执行外连SQL：

结果：

可以看到，只要左表或右表里有的数据都会打印出来，即使该数据没匹配对应的字段。即左表和右表的数据全部返回，不满足条件的用null填充。

 

四、join语句的底层算法

join的两种算法：NLJ(index Nested-Loop Join) 和 BNJ (Block Nested-Loop Join)

 

4.1 NLJ算法(index Nested-Loop Join)

SQL执行时，内部流程是这样的：

1. 先从outerTable表里面取出一行数据 outerRow
2. 从 outerRow中取出关联字段 id，去innerTable表中查找（逐个比对innerTable中每一条数据），满足条件的行取出。
3. 重复1、2步骤，直到outerTable最后一行循环结束。

每次只读取表中的一行数据，也就是说如果outerTable有10万行数据, innerTable有100行数据，需要读取10000000次(假设这两个表的文件没有被操作系统给缓存到内存, 我们称之为冷数据表)，当然现在没啥数据库引擎使用这种算法（太慢了)

4.11 驱动表与被驱动表

如上代码所示，外层循环 outerTable就是驱动表，里层循环innerTable就是被驱动表。

 

4.2  BNL算法(Block Nested-Loop Join)

BNL的 join 过程对 t1 和 t2 都做了一次全表扫描，并且将表 t2 中的 500 条数据全部放入内存 join_buffer 中，并且对于表 t1( 10000条数据) 中的每一行数据，都要去 join_buffer 中遍历一遍，都要做 500 次对比，所以一共要进行 500 * 10000 次内存对比操作，具体流程如下图所示。BNL只是先把驱动表t2读入内存。

join_buffer 并不是无限大的，由 join_buffer_size 控制，默认值为 256K。当要存入的数据过大时，就只有分段存储了，整个执行过程就变成了：

• 扫描表 t2，将符合条件的数据行存入 join_buffer，因为其大小有限，存到100行时满了，则执行第二步；
• 扫描表 t1，每取出一行数据，就跟 join_buffer 中的数据进行对比，满足 join 条件的，则放入结果集；
• 清空 join_buffer；
• 再次执行第一步，直到全部数据被扫描完，由于 t2 表中有 500行数据，所以一共重复了 5次

这个流程体现了该算法名称中 Block 的由来，分块去执行 join 操作。因为表 t2 的数据被分成了 5 次存入 join_buffer，导致表 t1 要被全表扫描 5次。

如上所示，和表数据可以全部存入 join_buffer 相比，内存判断的次数没有变化，都是两张表行数的乘积，也就是 10000 * 500，但是被驱动表会被多次扫描，每多存入一次，被驱动表就要扫描一遍，影响了最终的执行效率。

（block 块的好处是 每次都会取一块数据(join_buffer_size)到内存以减少I/O的开销）

4.3  BKA算法（Batched Key Access）

4.3.1  MRR（即：Multi-Range Read）

BKA是基于MRR思想的，所以先要简单说一下MRR：

MRR的优化目的是尽量使用顺序读盘。

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

这就是MRR的优化思路。

那么执行的语句的步骤就可以变成：

1. 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
2. 将 read_rnd_buffer 中的 id 进行递增排序；
3. 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。
4. 如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。

4.3.2 BKA算法

BKA其实是对NLJ算法的优化。

假设驱动表为t1，被驱动表是t2。

NLJ 算法执行的逻辑是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了，因为每次都是匹配一直值，那怎么存在MRR里的排序呢？所以要想办法一次性多传些值给t2。方法就是 从表 t1 里一次性地多拿些行出来，一起传给表 t2。既然如此，我们就把表 t1 的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是 join_buffer。通过上一篇文章，我们知道 join_buffer 在 BNL 算法里的作用，是暂存驱动表的数据。但是在 NLJ 算法里并没有用。那么，我们刚好就可以复用 join_buffer 到 BKA 算法中。

BKA如下图：

图中，我在 join_buffer 中放入的数据是 P1~P100，表示的是只会取查询需要的字段。当然，如果 join buffer 放不下 P1~P100 的所有数据，就会把这 100 行数据分成多段执行下图的流程。

如果要使用BKA的话，要先设置：

 
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

4.4  Hash Join算法

上述算法只是 join算法之一，还有一种更加高效的join算法：hash join。但是只有 Mysql 8.0版本支持hash join，但是8.0目前还不是主流版本。

hash join 的执行流程如下：

1. 将驱动表 t2 中符合条件的数据取出，对其每行的 join 字段值进行 hash 操作，然后存入内存中的hashmap中；
2. 遍历被驱动表 t1，每取出一行符合条件的数据，也对其 join 字段值进行 hash 操作，拿结果到内存的hashmap中查找匹配，如果找到，则成为结果集的一部分。

可以看出，该算法和 Block Nested-Loop Join 有类似之处，只不过是将无序的 Join Buffer 改为了散列表 hash table，从而让数据匹配不再需要将 join buffer 中的数据全部遍历一遍，而是直接通过 hash，以接近 O(1) 的时间复杂度获得匹配的行，这极大地提高了两张表的 join 速度。不过由于 hash 的特性，该算法只能适用于等值连接的场景。

(hash join 是可以完全替代 BNL的)

总结：

1.  BNL和BKA都是批量的提交一部分行给被join的表，从而减少访问的次数，那么它们有什么区别呢？

• BNL比BKA出现的早，BKA直到5.6才出现，而NBL至少在5.1里面就存在。

• BNL主要用于当 被驱动表 上无索引  

• BKA主要是指在 被驱动表 上有索引可以利用（不包括索引失效的情况），那么就在行提交给 被驱动表 之前，对这些行按照索引字段进行排序，因此减少了随机IO，排序这才是两者最大的区别

五、join语句的优化

5.1 用小表驱动大表

即小的数据表驱动大的数据表。

假设 t1表的数据有M个，t2表的数据有N个。（N>M）

• 如果走全表的话，无论是大表做驱动表还是小表做驱动表，都是M*N。
• 但若是大表t2有索引，且作为被驱动表，那在查找t2中符合条件的数据时，自然就不需要全表遍历，因为有索引，走B+树来查找就好了。（驱动表肯定要全表遍历的）

5.2 直接给被驱动表的join的关联字段加索引。促使BNL转为BKA