自 MySQL 8.0.18 开始,每当连接中的每对表包含至少一个等值连接条件时就会使用 Hash Join 。Hash Join 不需要索引,通常比块嵌套循环算法更高效。下面这些连接可以通过此规则优化:
SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1; SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) JOIN t3 ON (t2.c1 = t3.c1)
Hash Join 也可以用于笛卡尔积连接——即没有指定关联条件。
使用EXPLAIN FORMAT=TREE
或EXPLAIN ANALYZE
时可以看到用于特定查询的 Hash Join 优化。(MySQL 8.0.20 版本以后,可以省略FORMAT=TREE
而仅使用EXPLAIN
)
Hash Join 可用内存总量可通过 join_buffer_size 值限制。超过该值的 Hash Join 会在磁盘上执行,可以用 open_files_limit 限制用于磁盘上 Hash Join 的磁盘文件数。
MySQL 8.0.19 不再支持hash_join=on
优化器开关,HASH_JOIN
和NO_HASH_JOIN
注释,他们以后将被弃用。MySQL 8.0.18 以后,可以通过使用 NO_BNL 优化器开关禁用 Hash Join:
As of MySQL 8.0.19, the hash_join optimizer switch which was introduced in MySQL 8.0.18 no longer supported (hash_join=on still appears as part of the value of optimizer_switch, but setting it no longer has any effect). The HASH_JOIN and NO_HASH_JOIN optimizer hints are also no longer supported. The switch and the hint are both now deprecated and will be removed in a future MySQL release. In MySQL 8.0.18 and later, hash joins can be disabled using the NO_BNL optimizer switch.
MySQL 8.0.20 版本,MySQL Server 将不再使用块嵌套循环,Hash Join 可以用于任何之前使用块嵌套循环的场景,即使查询包含非等值条件。这适用于非等值内连接、半连接、反连接、左外连接、右外连接。这也意味着系统变量 optimizer_switch 的block_nest_loop
标记不再生效。BNL 和 NO_BNL 优化器注释仍然支持控制hash join的使用。另外,内连接和外连接(包括半连接和反连接)现在可以使用批量键访问——Batched Key Access (BKA),递增分配连接缓冲内存,以使独立查询不会用尽那些他们处理时实际不需要的大量资源。BKA 仅在 MySQL 8.0.18 版本后才支持内连接。
MySQL 8.0.20 还用迭代执行器替换了旧版 MySQL 使用的执行器。这项工作包括替换旧的索引子查询引擎——管理IN查询中没有被优化为半连接的形如WHERE value IN (SELECT column FROM table WHERE ...)
的查询,和之前依赖于旧执行器的同等形式的物化查询。