文章目录
- 前言
- 一、JOIN连接算法
- Simple Nested-Loop Join：
- Index Nested-Loop Join(NLJ)：
- Block Nested-Loop Join(BNL)：
- Batched Key Access(BKA)：
- 二、OLTP 业务能不能写 JOIN？
- 1、如果两张join表数据量很小
- 2、如果被驱动表能走索引
- 3、被驱动表没有索引
- 总结
- 能不能使用 join 语句？
- 如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？
- 驱动表？
前言
很多人下意识地认为 JOIN 会降低 SQL 的性能效率，所以就将一条多表 SQL 拆成单表的一条条查询，但这样反而会影响 SQL 执行的效率。究其原因，在于不了解 JOIN 的实现过程
一、JOIN连接算法
Simple Nested-Loop Join：
驱动表和被驱动表都走全表扫描
Index Nested-Loop Join(NLJ)：
驱动表走全表扫描，被驱动表通过索引扫描
Block Nested-Loop Join(BNL)：
被驱动字段不存在索引，因此被驱动表也只能走全表扫描，此时通过join buffer，将驱动表的数据一块块读到join buffer, 然后从被驱动表一条条读取数据做join操作(与join buffer中的数据对比)，重复这两步操作，直到join操作完成；由此可见合理设置join buffer大小至关重要，join buffer越大分块放入次数越少, I/O次数也就越少
Batched Key Access(BKA)：
是NLJ算法的优化版本。借助于MRR优化操作，将多条主键ID暂存join buffer，排序后从聚簇索引读取数据，因为磁盘顺序读取的效率也很高
二、OLTP 业务能不能写 JOIN？
先说结论：可以使用，但需要考虑场景
1、如果两张join表数据量很小
直接使用即可，不会有性能问题
2、如果被驱动表能走索引
这种情况，innodb 引擎会选择使用NLJ算法，如果被驱动表走索引并且索引区分度较高，也可以放心使用。这种情况语句执行流程如下：
驱动表t1, 被驱动表t2:
- 从表 t1 中读入一行数据 R；
- 从数据行 R 中，取出 a 字段到表 t2 里去查找；
- 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
- 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。
可以看出，只要被驱动表的索引区分度足够高，是可以快速检索数据，因此可以放心使用
3、被驱动表没有索引
假设：驱动表t1，被驱动表t2
BNL算法执行流程：
- 把表 t1 的数据读入线程内存 join_buffer 中，比如 select * … 语句，是把整个表 t1 放入了内存；
- 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。
以上流程会进行t1, t2全表扫描，看起来和Simple Nested-Loop Join类似；不同的是，BNL算法的t1表是在内存操作，因此速度和性能上都要快很多。
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。执行流程变成了：
- 扫描表 t1，顺序读取数据行放入 join_buffer 中，当 join_buffer 满了，继续第 2 步；
- 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
- 清空 join_buffer；
- 继续扫描表 t1，持续第1、2步骤，直到查询所有数据
这个流程体现出了这个算法名字中“Block”的由来，表示“分块去 join”。
因此，当Extra 字段里面出现“Block Nested Loop”字样时，是可以使用join操作的。
总结
能不能使用 join 语句？
- 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
- 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。
所以在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。
如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？
- 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
- 如果是 Block Nested-Loop Join 算法：在 join_buffer_size 足够大的时候，是一样的；在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。
所以，这个问题的结论就是，总是应该使用小表做驱动表。
驱动表？
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
