第103回　MySQL 8.0のセミジョインの変更点

MySQL 8.0からサブクエリに対して最適化する機能のセミジョインが強化されました。

MySQL 5.6とそれ以降からセミジョインは追加され、サブクエリを高速で処理できるようになりました。さらに、MySQL 8.0でその機能が進化しています。今回の記事を読む前に、まず以前の記事の第43回　MySQLの準結合（セミジョイン）についてからセミジョインの概要や戦略についてご確認ください。

今回は、強化されたMySQL8.0のセミジョインの変更点について、紹介したいと思います。

セミジョインの変更点

MySQL 8.0とそれ以前では、セミジョインを動作させるためには以下のような条件がありました。

INまたは=ANYを使用したサブクエリであること

よって、EXISTS句を使用したクエリにセミジョインを動作させるために、IN句を使用したクエリに書き換える必要がありました。しかし、MySQL8.0.16からはEXISTS句を使用したクエリでもセミジョインが動作するようになりました。

mysql> EXPLAIN SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id2=t1.id);
+----+-------------+-------+------------+--------+---------------+------+---------+----------+--------+----------+-----------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref      | rows   | filtered | Extra                                   |
+----+-------------+-------+------------+--------+---------------+------+---------+----------+--------+----------+-----------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | NULL          | NULL | NULL    | NULL     | 785178 |   100.00 | Using where; Start temporary            |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | id            | id   | 8       | t.t2.id2 |      1 |   100.00 | Using where; Using index; End temporary |
+----+-------------+-------+------------+--------+---------------+------+---------+----------+--------+----------+-----------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

Note (Code 1276): Field or reference 't.t1.id' of SELECT #2 was resolved in SELECT #1
Note (Code 1003): /* select#1 */ select count(0) AS `count(*)` from `t`.`t1` semi join (`t`.`t2`) where (`t`.`t1`.`id` = `t`.`t2`.`id2`)

Note部分に注目すると、内部で書き換えられたステートメントが表示されます。そこにsemi joinと記述されていることがわかります。

Note (Code 1003): /* select#1 */ select count(0) AS `count(*)` from `t`.`t1` semi join (`t`.`t2`) where (`t`.`t1`.`id` = `t`.`t2`.`id2`)

アンチジョイン

さらに、MySQL 8.0.17（2019/08/06現在での最新バージョン）からはアンチジョインが追加されました。アンチジョインとは、NOT EXISTSやNOT INを使用したサブクエリを最適化する機能です。また、アンチジョインはduplicateweedout戦略やmaterialization戦略といった戦略や動作するための条件はセミジョインと同様になります。

たとえば、MySQL8.0.16とそれ以前では、NOT EXISTSを使用したクエリの実行計画は以下のように出力されます。

EXPLAIN SELECT count(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id2=t1.id);
+----+--------------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                    |
+----+--------------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
|  1 | PRIMARY            | t1    | NULL       | index | NULL          | id   | 8       | NULL | 785175 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ALL   | NULL          | NULL | NULL    | NULL | 785178 |    10.00 | Using where              |
+----+--------------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)

Note (Code 1276): Field or reference 't.t1.id' of SELECT #2 was resolved in SELECT #1
Note (Code 1003): /* select#1 */ select count(0) AS `count(*)` from `t`.`t1` where exists(/* select#2 */ select 1 from `t`.`t2` where (`t`.`t2`.`id2` = `t`.`t1`.`id`)) is false

select_typeがDEPENDENT SUBQUERYとなっています。

この動作は外側のクエリで抽出された件数分を内側のクエリ（exists句内のクエリ）が実行されます。たとえば、内側のテーブルにインデックスがない場合はフルスキャンで実行されてしまい、悲惨な目にあうこともあります。これはMySQLのサブクエリは遅いと言われる要因の動作です。

MySQL 8.0.17からは以下のような実行計画になります。

mysql> EXPLAIN SELECT count(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id2=t1.id);
+----+--------------+-------------+------------+--------+---------------+------------+---------+---------+--------+----------+-------------------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref     | rows   | filtered | Extra                   |
+----+--------------+-------------+------------+--------+---------------+------------+---------+---------+--------+----------+-------------------------+
|  1 | SIMPLE       | t1          | NULL       | index  | NULL          | id         | 8       | NULL    | 785175 |   100.00 | Using index             |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 5       | t.t1.id |      1 |   100.00 | Using where; Not exists |
|  2 | MATERIALIZED | t2          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL    | 785178 |   100.00 | NULL                    |
+----+--------------+-------------+------------+--------+---------------+------------+---------+---------+--------+----------+-------------------------+
3 rows in set, 2 warnings (0.00 sec)

Note (Code 1276): Field or reference 't.t1.id' of SELECT #2 was resolved in SELECT #1
Note (Code 1003): /* select#1 */ select count(0) AS `count(*)` from `t`.`t1` anti join (`t`.`t2`) on((`t`.`t1`.`id` = `<subquery2>`.`id2`)) where true

Note部分に注目すると、内部で書き換えられたSQLステートメントが表示されます。そこにanti joinと記述されていることがわかります。

Note (Code 1003): /* select#1 */ select count(0) AS `count(*)` from `t`.`t1` anti join (`t`.`t2`) on((`t`.`t1`.`id` = `t`.`t2`.`id2`)) where true

上記の実行計画では、materializationによるセミジョイン戦略が使用されています。この動作はt2テーブルから内部的に実体化した一時テーブル作成し、重複レコードを削除するためにインデックスを作成してから結合しているため、前述のもの比べて大幅に速度が改善されます。

セミジョインとアンチジョインの戦略を調整する方法

セミジョインまたは、アンチジョインはすべての戦略がデフォルトでは有効になっています。オプティマイザーがコストを計算し、戦略を選択します。しかし、たまにこちらが期待した戦略を選択しないこともあります。そのようなときに使用する戦略を調整する方法を紹介します。

optimizer_switch

MySQ L5.6とそれ以降であれば、optimizer_switchオプションを調整することで、グローバルまたはセッション単位で設定可能です。それぞれのフラグに関しては、第43回　MySQLの準結合（セミジョイン）についてに記載しています。

ヒント句

MySQL5.7とそれ以降からは、ヒント句を使ってSQLステートメント単位で調整することが可能です。

強制的にセミジョインまたはアンチジョインを有効にする場合は、SEMIJOINヒント句 /*+ SEMIJOIN(@QB_NAME) */を使用します。強制的にセミジョインまたはアンチジョインを無効にする場合は、NO_SEMIJOINヒント句 /*+ NO_SEMIJOIN(@QB_NAME) */を使用します。

@QB_NAMEはサブクエリ部分に記述するもので、クエリブロックの名前となります。

例：強制的にセミジョインを無効化したSQLステートメント

mysql> SELECT /*+ NO_SEMIJOIN(@test) */ count(*) FROM t1 WHERE id IN (SELECT /*+ QB_NAME(test) */ id2 FROM t2 );

上記のように、まずサブクエリに対して/*+ QB_NAME(test) */のヒント句を記述して、testという名前をつけました。外側のクエリで/*+ NO_SEMIJOIN(@test) */を記述することで、オプティマイザーはセミジョインを使用しないように処理します。

また、このヒント句を使って特定の戦略で処理されるように指定することも可能です。たとえば、以下のクエリはオプティマイザーがmaterialization戦略を選択しています。

mysql> explain SELECT /*+ SEMIJOIN(@test) */ count(*) FROM t1 WHERE id in  (SELECT /*+ QB_NAME(test) */ id2 FROM t2 );
+----+--------------+-------------+------------+--------+---------------+------------+---------+---------+--------+----------+--------------------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref     | rows   | filtered | Extra                    |
+----+--------------+-------------+------------+--------+---------------+------------+---------+---------+--------+----------+--------------------------+
|  1 | SIMPLE       | t1          | NULL       | index  | id            | id         | 8       | NULL    | 785175 |   100.00 | Using where; Using index |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 5       | t.t1.id |      1 |   100.00 | Using where              |
|  2 | MATERIALIZED | t2          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL    | 785179 |   100.00 | NULL                     |
+----+--------------+-------------+------------+--------+---------------+------------+---------+---------+--------+----------+--------------------------+

これをduplicateweedout戦略を選択するように変更したい場合は、SEMIJOINヒント句に戦略名を追加します。以下のように記述します。

mysql> explain SELECT /*+ SEMIJOIN(@test DUPSWEEDOUT) */ count(*) FROM t1 WHERE id in  (SELECT /*+ QB_NAME(test) */ id2 FROM t2 );
+----+-------------+-------+------------+--------+---------------+------+---------+----------+--------+----------+-----------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref      | rows   | filtered | Extra                                   |
+----+-------------+-------+------------+--------+---------------+------+---------+----------+--------+----------+-----------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | NULL          | NULL | NULL    | NULL     | 785179 |   100.00 | Using where; Start temporary            |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | id            | id   | 8       | t.t2.id2 |      1 |   100.00 | Using where; Using index; End temporary |
+----+-------------+-------+------------+--------+---------------+------+---------+----------+--------+----------+-----------------------------------------+

NO_SEMIJOINヒント句は選択してほしくない戦略を記述することで、オプティマイザーは記述された戦略を無視するようになります。また、カンマ区切りで複数の戦略を記述することも可能です。

有効な戦略名は以下の通りになっています。

DUPSWEEDOUT … duplicateweedout戦略
FIRSTMATCH … Fisrtmarch戦略
LOOSESCAN … LooseScan戦略
MATERIALIZATION … materialization戦略