Mysql join算法深入浅出
导语
联表查询在日常的数据库设计中非常的常见，但是联表查询可能会带来性能问题，为了调优、避免设计出有性能问题的SQL，在explain命令中，会显示用的是哪个join算法，学习一下join过程是非常有必要的
当执行下面这个SQL Join，在不同的情况下会产生不一样的复杂度
select * from user tb1 left join tb2 on tb1.field1 = tb2.field2
一 Simple Nested-Loop Join（简单的嵌套循环连接）
简单来说嵌套循环连接算法就是一个双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果,
会产生两张表的集合全匹配，也就是tb1有几条数据 * tb2有几条数据，数量会随着表大匹配的数量会非常大, 就是笛卡尔积
伪代码:
for(field1:tb1) {
for(field2:tb2) {
if(field2.equals(field2)) {
return true;
}
}
}
二 Block Nested-Loop Join（缓存块嵌套循环连接）
这个算法是Simple Nested-Loop Join（简单的嵌套循环连接）的一个优化，如果无法使用Index Nested-Loop Join的时候，数据库是默认使用的是Block Nested-Loop Join算法的 （也就是MySql在field2字段不是索引的情况下才默认是这个类型）
Block Nested-Loop Join 其优化思路是减少内层表的扫表次数。
Simple Nested-Loop Join（简单的嵌套循环连接）第一个for循环一次会加载一行数据，然后去第二个for循环逐行去匹配。
但是Block Nested-Loop Join会在第一个for循环加载几行数据，然后再去然后去第二个for循环逐行去匹配
注意：
- 是否开启Block Nested-Loop Join 算法
需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on 默认为开启，如果关闭则使用Simple Nested-Loop Join算法
-- 查看是否开启Block Nested-Loop Join 算法
Show variables like 'optimizer_switc%';
- join buffer 的大小
这个属性是一个MySql的调优手段
-- 查看join_buffer大小
Show variables like 'join_buffer_size%';
mysql> Show variables like 'join_buffer_size%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.12 sec)
三 Index Nested-Loop Join（索引嵌套循环连接）
这个算法只有在 tb2.field2为唯一索引的时候，才使用，因为我们已经知道了，在tb2表中，只有一行数据，所以我们不需要完整的去对比tb2表，只要一行对比上了就可以返回了，极大的减少了对内层表的匹配次数。过程如下：
三 Hash join
这个算法，在MySql8之前是没有的。主要的思想是把tb2.filed1或者tb1.filed1(谁结果集小谁合适)中的所有的值，构建成散列表，所以它适用于on等值（=）的情况，在日常的数据库表设计中，我们大多部分也是on等值的情况。复杂度变成了 构建成散列表的复杂度 + tb1.filed1 N。
- 对于大数据量的表关联，Hash join算法速度更快，且不用索引
- 在内存中进行，内存超出
join_buffer_size%，会使用硬盘
四 Merge Join
这个算法，同样在MySql8之前是没有的。上面说到了Hash join是在等值（=）才会去使用的，那非等值(>,<,>=,<=)，这种条件在Mysql8中是是否还是之前的循环算法，答案是NO，然而如果两表已经有序，用的是Merge Join。Merge Join在做非等值(>,<,>=,<=)对比的时候，一旦有行不符合条件就会不往下面再去执行的，因为对比之前就是有序的，下面的都是不符合条件的。
五 强制使用某个算法
强制使用嵌套循环连接STRAIGHT_JOIN，
SELECT *
FROM Table1
STRAIGHT_JOIN Table2
ON Table1.Column = Table2.Column;
强制使用哈希连接USE_HASH(e, d)，
SELECT USE_HASH(e, d) *
FROM employees AS e
INNER JOIN departments AS d
ON e.department_id = d.department_id;
强制使用合并连接USE_MERGE(e, d),
SELECT USE_MERGE(e, d) *
FROM employees AS e
INNER JOIN departments AS d
ON e.department_id = d.department_id;
一般来说，我们是不用去选择使用什么join算法的，优化器会帮我们自动选择
