MySQL 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。

准备环境：

MySQL 5.7.19
MySQL 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的设置会在磁盘上创建临时文件来处理