TLDR;
- MySQL 8.0.20 から INNER JOIN だけじゃなくて、Semi joinや Left/Right outer join でも HASH JOIN が使えるようになったよ
- MySQL 8.0.20 では (EXPLAIN ANALYZEではなく）EXPLAIN でも、HASH JOINが使われているか、表示されるようになったよ
- MySQL 8.0.19 から、optimizer_switch で HASH JOIN の OFF/ON がコントロールできなくなったよ
MySQL 8.0.20 Release Note
Hash joins are now used any time a nested block loop would be employed. This means that hash joins can be used for the following types of queries:
* Inner non-equi-joins
* Semijoins
* Antijoins
* Left outer joins
* Right outer joins
This builds on work done for MySQL 8.0.18, and removes a limitation in the implementation such that a hash join could be used only with a query having at least one equi-join condition.
MySQL 8.0.20 で HASH JOIN が INNER JOIN 以外にも利用できるようになった。
比較のため、HASH JOINが入る前の8.0.17 で試す。
HASH JOINが効きそうな、インデックスを一切貼ってないテーブルどうしをJOINさせる。
mysql> SELECT * FROM t1 JOIN t2 ON t1.a = t2.a;
Empty set (32.01 sec)
mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
16305 rows in set (34.92 sec)
INNER JOIN, LEFT JOIN ともに、30秒ほど実行にかかった。
mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 32538 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 32926 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 32538 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 32926 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
実行計画でも、HASH JOIN ではなく、Block Nested Loop が利用されていることがわかる。
HASH JOINがINNER JOINに対してのみサポートされた、8.0.18。
mysql> SELECT * FROM t1 JOIN t2 ON t1.a = t2.a;
Empty set (0.16 sec)
mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
16305 rows in set (34.65 sec)
INNER JOINは8.0.17で30秒だったものが、0.16秒とHASH JOINにより高速化されている。
LEFT JOINは変わらず。
mysql> EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a = t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 16207 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 16207 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 16207 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 16207 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
素のEXPLAINではHASH JOINかどうかが、区別できない。。。
EXPLAIN ANALYZE を利用する必要がある。
mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.a = t2.a;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t2.a = t1.a) (cost=26268477.04 rows=26266685) (actual time=174.783..174.783 rows=0 loops=1)
-> Table scan on t2 (cost=0.02 rows=16207) (actual time=0.026..38.354 rows=16305 loops=1)
-> Hash
-> Table scan on t1 (cost=1644.95 rows=16207) (actual time=0.053..37.792 rows=16305 loops=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.18 sec)
Inner hash join と表示され、HASH JOIN が使われたことが、わかる。
OUTER JOIN にも HASH JOINが適用されるようになった、8.0.20で試す。
mysql> SELECT * FROM t1 JOIN t2 ON t1.a = t2.a;
Empty set (0.14 sec)
mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
16305 rows in set (0.14 sec)
LEFT JOINも早くなった！
mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 16207 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 16207 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
8.0.20 では、EXPLAIN ANALYZEでなくても、HASH JOINかどうか区別できるようになってる（Using join buffer (hash join)）。
HASH JOINをOFFにできなくなってた
optimizer_switch の hash_join を OFF にしても、、、
HASH JOINが使われてしまう！
Beginning with MySQL 8.0.19, the hash_join optimizer switch and the HASH_JOIN and NO_HASH_JOIN optimizer hints no longer have any effect.
8.0.19 から、optimizer_switch や ヒント句でコントロールできなくなってしまったようです。
