mysql hash join_MySQL Hash Join

在MySQL 8.0.18之前，表的join方式只有嵌套循环(nested loop)这一种方式，8.0.18推出了hash join的方式以替代嵌套循环。

使hash join方式生效的前提是用于join的字段上没有索引

且在8.0.18中，还需要一个对等的条件(table1.a=table2.a)，才能满足hash join。

在8.0.20中，取消了对等条件的约束，可以全面支持non-equi-join，Semijoin，Antijoin，Left outer join/Right outer join。

hash join的原理概括为：

选择占用空间较小的表t1(不一定是行数)作为驱动表，计算其join字段的hash值，在内存中build一个hash table，将t1的join字段的hash值存放至hash table。然后对被驱动表t2的join字段计算hash值，并与内存中的hash table进行查找匹配。

参考手册:https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html

准备环境：

MySQL 5.7.19MySQL 8.0.20

数据准备:

通过sysbench，创建两张表sbtest1,sbtest2，分别灌入20w,25w数据，并且删除了k字段的索引。

执行计划查看:

分别执行：

explain select * from sbtest1 x join sbtest2 y on x.k = y.k;

MySQL 8.0.20:

(8.0.18中需使用explain format=tree来查看)

MySQL 5.7.19:

通过执行计划，可以看出，在8.0.20中，非索引的join操作已经优化为hash join。

性能测试:

分别执行：

select count(*) from sbtest1 x join sbtest2 y on x.k = y.k;

MySQL 8.0.20:

MySQL 5.7.19:

通过执行耗时，可以看出，通过hash join的方式比nested loop有了非常明显的优化提升，26min48s —> 0.61s。

PS:

在MySQL 8.0.18中，可以在optimizer_switch 中指定hash_join=on/off用以开关hash join，该方式在MySQL8.0.19版本后失效

hash join通过参数join_buffer_size来控制内存中的使用大小，超出join_buffer_size的设置会在磁盘上创建临时文件来处理