7/13 に MySQL 8.0.21 がリリースされました。

リリースノートを読んでいて、Optimizer Notes の 2 つ目に、

A single-table UPDATE or DELETE statement that uses a subquery having a [NOT] IN or [NOT] EXISTS predicate can now in many cases make use of a semijoin transformation or subquery materialization. This can be done when the statement does not use LIMIT or ORDER BY, and when semijoin or subquery materialization is allowed by any optimizer hints used in the subquery, or by the value of the optimizer_switch server system variable.

というものがあり、「これは何だろう？」と思っていたところ、Oracle モ MySQL モデキル DBA の方の呟き によって謎が解けましたので、実際に試してみました。

Multi-Table Trick とは

先の呟きにもリンクがありましたが、こちらを見ていただくとわかりやすいです。

A Multi-Table Trick to Speed up Single-Table UPDATE/DELETE Statements（Øystein on MySQL Optimizer）

MySQL 5.6（あたり？）から、SELECTのWHERE ... IN のサブクエリで、

セミジョイン（準結合）
マテリアライズ（実体化）

によって（特にサブクエリ側のテーブルにたくさんデータがあるときに）処理が高速化されるようになりましたが、これはUPDATE・DELETEでは素直に SQL（文）を書いても適用されませんでした。

それを、例えば

UPDATE t1 SET t1.a=value WHERE t1.a IN (SELECT t2.a FROM t2);

　　↓

UPDATE t1, (SELECT 1) dummy SET t1.a=value WHERE t1.a IN (SELECT t2.a FROM t2);

のように「加工」することによって、UPDATE・DELETEにも適用する（結果として SQL（文）の実行が速くなる）のが Multi-Table Trick ということらしいです。

試してみた

以下のような構造のテーブルに、各 50 行ずつデータを入れてEXPLAINしてみます。

テーブル構造

mysql> USE multitable_test;
Database changed
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `val` int NOT NULL,
  `key` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `valkey` (`val`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

まずは、MySQL 8.0.20 で実行してみます。

8.0.20の結果

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
| id | select_type  | table       | partitions | type  | possible_keys | key    | key_len | ref             | rows | filtered | Extra                    |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL            | NULL |   100.00 | NULL                     |
|  1 | SIMPLE       | t1          | NULL       | ref   | key           | key    | 4       | <subquery2>.key |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | t2          | NULL       | range | valkey        | valkey | 4       | NULL            |   15 |   100.00 | Using where; Using index |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.01 sec)

mysql> EXPLAIN UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | UPDATE             | t1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |   50 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | range | valkey        | valkey  | 4       | NULL |   15 |    10.00 | Using where; Using index |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

SELECTでは有効だったマテリアライズが、UPDATEでは効きません。

次に、MySQL 8.0.21 で実行してみます。

8.0.21の結果

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
| id | select_type  | table       | partitions | type  | possible_keys | key    | key_len | ref             | rows | filtered | Extra                    |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL            | NULL |   100.00 | NULL                     |
|  1 | SIMPLE       | t1          | NULL       | ref   | key           | key    | 4       | <subquery2>.key |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | t2          | NULL       | range | valkey        | valkey | 4       | NULL            |   15 |   100.00 | Using where; Using index |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------------+------+----------+--------------------------+
| id | select_type  | table       | partitions | type   | possible_keys       | key                 | key_len | ref                    | rows | filtered | Extra                    |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------------+------+----------+--------------------------+
|  1 | UPDATE       | t1          | NULL       | ALL    | key                 | NULL                | NULL    | NULL                   |   44 |   100.00 | Using where              |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 4       | multitable_test.t1.key |    1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | t2          | NULL       | range  | valkey              | valkey              | 4       | NULL                   |   15 |   100.00 | Using where; Using index |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

UPDATEでもマテリアライズが適用されました。

時間を比べてみる

先ほどのテーブル内のデータを約 10 万行まで増やして試します。環境は MySQL 8.0.21 です。

各テーブルデータ行数

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|   102400 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
|   102400 |
+----------+
1 row in set (0.29 sec)

まずはオプティマイザスイッチでマテリアライズを無効化してみます。

マテリアライズ無効

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | UPDATE             | t1    | NULL       | index | NULL          | PRIMARY | 4       | NULL | 102567 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | range | valkey        | valkey  | 4       | NULL |     15 |    10.00 | Using where; Using index |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
Query OK, 24576 rows affected (2.71 sec)
Rows matched: 24576  Changed: 24576  Warnings: 0

mysql> ROLLBACK;
Query OK, 0 rows affected (0.95 sec)

MySQL 8.0.20 と同じ実行計画になりました。3 回計測した平均は 2.78 秒でした。

次に、マテリアライズが有効な状態で試してみます（MySQL 8.0.21 のデフォルト）。

マテリアライズ有効

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
| id | select_type  | table       | partitions | type  | possible_keys | key    | key_len | ref             | rows | filtered | Extra                    |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL            | NULL |   100.00 | NULL                     |
|  1 | UPDATE       | t1          | NULL       | ref   | key           | key    | 4       | <subquery2>.key |    1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | t2          | NULL       | range | valkey        | valkey | 4       | NULL            |   15 |   100.00 | Using where; Using index |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
Query OK, 24576 rows affected (1.08 sec)
Rows matched: 24576  Changed: 24576  Warnings: 0

mysql> ROLLBACK;
Query OK, 0 rows affected (0.99 sec)

3 回計測した平均は 1.07 秒でした。

2 ～ 3 倍ほど高速化されました。

Qiitaに投稿したMySQL 8.0関連記事
MySQL 8.0 の薄い本（無料で配布中！）

MySQL 8.0.21 では Multi-Table Trick が必要なくなったらしい

Multi-Table Trick とは

試してみた

時間を比べてみる