哈希联接优化
从MySQL 8.0.18开始,MySQL对任何查询都具有相等连接条件且不使用索引的查询使用哈希连接,例如:
SELECT *FROM t1JOIN t2ON t1.c1=t2.c1;
散列连接通常比在这种情况下要快,并且打算在这种情况下代替在MySQL早期版本中使用的块嵌套循环算法(请参阅块嵌套循环联接算法)。
在刚刚所示的例子,在本节剩下的例子,我们假设三个表t1
,t2
以及t3
使用下面的语句已创建:
CREATE TABLE t1 (c1 INT, c2 INT);CREATE TABLE t2 (c1 INT, c2 INT);CREATE TABLE t3 (c1 INT, c2 INT);
您可以看到通过使用了哈希联接EXPLAIN FORMAT=TREE
,如下所示:
mysql>EXPLAIN FORMAT =TREE ->SELECT * ->FROM t1 ->JOIN t2 ->ON t1.c1=t2.c1\G *************************** 1. row *************************** EXPLAIN : -> Inner hash join (t2.c1 = t1.c1) (cost=0.70 rows=1) ->Table scanon t2 ( cost=0.35rows =1) ->Hash ->Table scanon t1 ( cost=0.35rows =1)
要参见是否将散列连接用于给定的连接,必须EXPLAIN
与FORMAT=TREE
选项一起使用。EXPLAIN ANALYZE
还显示有关使用的哈希联接的信息。
哈希联接也用于涉及多个联接的查询,只要每对表的至少一个联接条件为等联接,如此处所示的查询:
SELECT *FROM t1JOIN t2ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)JOIN t3ON (t2.c1 = t3.c1);
在刚刚显示的情况下,执行联接后,将所有非等联接的额外条件用作过滤器。可以在以下输出中看到EXPLAIN FORMAT=TREE
:
mysql>EXPLAIN FORMAT =TREE ->SELECT * ->FROM t1 ->JOIN t2 ->ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) ->JOIN t3 ->ON (t2.c1 = t3.c1)\G *************************** 1. row *************************** EXPLAIN : -> Inner hash join (t3.c1 = t1.c1) (cost=1.05 rows=1) ->Table scanon t3 ( cost=0.35rows =1) ->Hash ->Filter: (t1 . c2 < t2 .c2 ) (cost=0.70rows =1) ->Inner hash join (t2 . c1 = t1 .c1 ) (cost=0.70rows =1) ->Table scanon t2 ( cost=0.35rows =1) ->Hash ->Table scanon t1 ( cost=0.35rows =1)
从刚刚显示的输出中还可以看出,多个哈希联接可以(并且被)用于具有多个等联接条件的联接。
如果任何一对联接表都没有至少一个等联接条件,则不能使用哈希联接,如下所示:
mysql>EXPLAIN FORMAT =TREE ->SELECT * ->FROM t1 ->JOIN t2 ->ON (t1.c1 = t2.c1) ->JOIN t3 ->ON (t2.c1 < t3.c1)\G *************************** 1. row *************************** EXPLAIN : <not executable by iterator executor>
在这种情况下,采用较慢的块嵌套循环算法,如MySQL 8.0.18之前的版本中没有可用的索引:
mysql>EXPLAIN ->SELECT * ->FROM t1 ->JOIN t2 ->ON (t1.c1 = t2.c1) ->JOIN t3 ->ON (t2.c1 < t3.c1)\G *************************** 1. row *************************** id : 1 select_type : SIMPLE table : t1 partitions : NULL type : ALL possible_keys : NULL key : NULL key_len : NULL ref : NULL rows : 1 filtered : 100.00 Extr : NULL *************************** 2. row *************************** id : 1 select_type : SIMPLE table : t2 partitions : NULL type : ALL possible_keys : NULL key : NULL key_len : NULL ref : NULL rows : 1 filtered : 100.00 Extr : Using where; Using join buffer (Block Nested Loop) *************************** 3. row *************************** id : 1 select_type : SIMPLE table : t3 partitions : NULL type : ALL possible_keys : NULL key : NULL key_len : NULL ref : NULL rows : 1 filtered : 100.00 Extr : Using where; Using join buffer (Block Nested Loop)
哈希联接也适用于笛卡尔乘积,即未指定联接条件时,如下所示:
mysql>EXPLAIN FORMAT =TREE ->SELECT * ->FROM t1 ->JOIN t2 ->WHERE t1.c2 > 50\G *************************** 1. row *************************** EXPLAIN : -> Inner hash join (cost=0.70 rows=1) ->Table scanon t2 ( cost=0.35rows =1) ->Hash ->Filter: (t1 . c2 > 50) (cost=0.35rows =1) ->Table scanon t1 ( cost=0.35rows =1)
默认情况下,MySQL尽可能使用哈希联接。仅在MySQL 8.0.18中,可以控制是否通过以下两种方式之一使用哈希联接:
- 在全局或会话级别上,通过使用
hash_join=on
或hash_join=off
作为optimizer_switch
服务器系统变量设置的一部分。默认值为hash_join=on
。 - 通过使用优化程序提示之一
HASH_JOIN
或NO_HASH_JOIN
作为给定联接的一部分,在逐个情况下进行。
(从MySQL 8.0.19开始,hash_join
优化器开关以及HASH_JOIN
和NO_HASH_JOIN
优化器提示不再起作用。)
哈希联接的内存使用情况可以使用join_buffer_size
系统变量进行控制;哈希联接不能使用超过此数量的内存。当散列连接所需的内存超过可用容量时,MySQL通过使用磁盘上的文件来处理。如果发生这种情况,您应该注意,如果哈希联接无法容纳到内存中并且创建的文件数超过设置的数量,联接可能不会成功open_files_limit
。为避免此类问题,请进行以下更改之一:
- 增加
join_buffer_size
以使哈希联接不会溢出到磁盘。 - 增加
open_files_limit
。