MySQL 8.0.20 でHASH JOINが効くケースが拡大した

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 以外にも利用できるようになった。

MySQL 8.0.17

比較のため、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 が利用されていることがわかる。

MySQL 8.0.18

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 が使われたことが、わかる。

MySQL 8.0.20

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 やヒント句でコントロールできなくなってしまったようです。

mita2 database life

主にMySQLに関するメモです