MySQL 8.0.20 以前でサブクエリの結果をINで受けるDELETE文を書いたらテーブルスキャンになった話：MySQL 8.0.21 で有効になった最適化の検証

この記事はなに？

MySQL 8.0.20 以前でサブクエリの結果をINで受けるDELETE文（後述）を書いたところ、意図に反して削除対象のテーブルが全行スキャンされてしまいました。この挙動が、MySQL 8.0.21 で有効になった最適化によって解消するという情報を得た*1ので、検証してみた結果を書きます。

題材は以下のようになっているテーブルです。親 parents テーブルの主キーを参照している子 children テーブルがあり、親テーブルには主キーとは別にインデックスが効くテーブルがある場面です。

上記のようなテーブルに対して、以下のようなDELETE文を発行したいとします。親テーブルの主キーでないカラム（インデックスを持つ）が特定の値（下の例では3）であるようなレコード、に紐づく子テーブルのレコードを削除する、というDELETEですね。今回は、このときの実行計画とロックの取り方を検証します。

ちなみに、以下のDELETE文ではサブクエリの結果を使って IN で比較していますが、ここが = 比較の場合は挙動がだいぶ変わってきます（バージョンの新旧、最適化の有無に関わらず type: range の比較的平和な実行計画になります）。そのケースについては本記事のスコープ外です。

DELETE FROM children WHERE parent_id IN
(SELECT parent_id FROM  parents WHERE parent_idx_col = 3);

使用する MySQL のバージョンは、以下の通りです。

• MySQL 8.0.20
• MySQL 8.0.21

まとめ

そこそこ長いので、まとめを先に書いておきます。

• DELETE（UPDATE）にINで受けるサブクエリを使ったときの動きが MySQL 8.0.21 で変わったらしいので見てみたよ
  • 参考
    • MySQL 8.0.21 では Multi-Table Trick が必要なくなったらしい #MySQL - Qiita
• MySQL 8.0.20 だとDELETE対象のテーブルが先に評価されて全行スキャン、サブクエリは相関サブクエリ扱いになったよ
  • パフォーマンスが悪いよ & ロックもめっちゃかかるよ
• MySQL 8.0.21 だとサブクエリが先に評価されて、その結果を使ってDELETE対象のテーブルをインデックスで検索しにいけるよ
  • パフォーマンスがいいよ & ロックもだいぶ減っているよ
  • semijoin の最適化が効いている結果みたいだよ
• MySQL 8.0.20 以前を使っているなら更新系 SQLにINで受けるサブクエリを書くときは注意！
  • JOIN を使った書き方にするなどしましょう

目次

実験用のテーブルのDDL

親テーブル

CREATE TABLE parents (
  `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_idx_col` int(10) unsigned NOT NULL,
  PRIMARY KEY (`parent_id`),
  UNIQUE KEY `uk_parent_idx_col` (`parent_idx_col`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

子テーブル

CREATE TABLE children (
  `child_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`child_id`),
  CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parents` (`parent_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

データ

parents

parent_id	parent_idx_col
1	1
2	2
3	3
4	4
5	5

children

child_id	parent_id
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
9	5
10	5

実験の流れ

以下の設定にしておきます。

SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

トランザクションを貼ってから以下のDELETE文の（実行計画も取得）実行します。

EXPLAIN DELETE FROM children WHERE parent_id IN
(SELECT parent_id FROM  parents WHERE parent_idx_col = 3);

DELETE FROM children WHERE parent_id IN
(SELECT parent_id FROM  parents WHERE parent_idx_col = 3);

トランザクションを commit せずに、以下でロックの状態を確認します。

SHOW ENGINE INNODB STATUS;

この流れを、MySQLのバージョンを変えて実施します。

MySQL 8.0.20 の場合

まず、変更前の MySQL 8.0.20 で試します。

実行計画

いかにも遅そうな実行計画になりました。

• 子テーブルが先に評価され、 type: ALL（テーブルスキャン）
• 親テーブルは DEPENDENT SUBQUERY （相関サブクエリ）扱い

mysql> EXPLAIN DELETE FROM children WHERE parent_id IN
    -> (SELECT parent_id FROM  parents WHERE parent_idx_col = 3);
+----+--------------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type        | table    | partitions | type  | possible_keys             | key               | key_len | ref   | rows | filtered | Extra       |
+----+--------------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | DELETE             | children | NULL       | ALL   | NULL                      | NULL              | NULL    | NULL  |   10 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | parents  | NULL       | const | PRIMARY,uk_parent_idx_col | uk_parent_idx_col | 4       | const |    1 |   100.00 | Using index |
+----+--------------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+

SHOW ENGINE INNODB STATUS;

children テーブルは全行+ 上限行への排他のネクストキーロックがかかりました。

parent テーブルは検索対象の行だけが共有ロック対象になっています。

• 12行のロック
• children テーブルに IX ロック
  • 主キーのインデックスにネクストキーロックがかかる
  • 11行
    • supremum （上限を意味する仮想的な行）
    • 10行（=レコード行数）
      • 00000001：1
      • 0000000a ： 10（=末尾のレコードのID）
• parents テーブルに IS ロック
  • UNIQUEキーのインデックスにレコードロック locks rec but not gap がかかる
  • 1行
    • ID = 3 ：検索対象のレコード

抜粋

トランザクションのロック全体

---TRANSACTION 2107, ACTIVE 79 sec
4 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 2
MySQL thread id 9, OS thread handle 140137874208512, query id 50 localhost root starting

子テーブルへのロック

TABLE LOCK table `tmp`.`children` trx id 2107 lock mode IX
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `tmp`.`children` trx id 2107 lock_mode X

親テーブルへのロック

TABLE LOCK table `tmp`.`parents` trx id 2107 lock mode IS
RECORD LOCKS space id 2 page no 5 n bits 72 index uk_parent_idx_col of table `tmp`.`parents` trx id 2107 lock mode S locks rec but not gap

全体

=====================================
2022-01-10 01:02:18 0x7f7464363700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 20 srv_active, 0 srv_shutdown, 2655 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 31
OS WAIT ARRAY INFO: signal count 24
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2112
Purge done for trx's n:o < 2112 undo n:o < 0 state: running but idle
History list length 15
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421612870740520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421612870739672, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2107, ACTIVE 79 sec
4 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 2
MySQL thread id 9, OS thread handle 140137874208512, query id 50 localhost root starting
show engine innodb status
TABLE LOCK table `tmp`.`parents` trx id 2107 lock mode IS
RECORD LOCKS space id 2 page no 5 n bits 72 index uk_parent_idx_col of table `tmp`.`parents` trx id 2107 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000003; asc     ;;

TABLE LOCK table `tmp`.`children` trx id 2107 lock mode IX
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `tmp`.`children` trx id 2107 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 000000000829; asc      );;
 2: len 7; hex 810000010f0110; asc        ;;
 3: len 4; hex 00000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 6; hex 00000000082a; asc      *;;
 2: len 7; hex 820000010f0110; asc        ;;
 3: len 4; hex 00000001; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 81000000850110; asc        ;;
 3: len 4; hex 00000002; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000004; asc     ;;
 1: len 6; hex 000000000830; asc      0;;
 2: len 7; hex 82000000860110; asc        ;;
 3: len 4; hex 00000002; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000005; asc     ;;
 1: len 6; hex 00000000083b; asc      ;;;
 2: len 7; hex 010000011c0151; asc       Q;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 00000000083b; asc      ;;;
 2: len 7; hex 010000011c017b; asc       {;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000007; asc     ;;
 1: len 6; hex 000000000833; asc      3;;
 2: len 7; hex 81000000870110; asc        ;;
 3: len 4; hex 00000004; asc     ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000008; asc     ;;
 1: len 6; hex 000000000834; asc      4;;
 2: len 7; hex 82000000880110; asc        ;;
 3: len 4; hex 00000004; asc     ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000009; asc     ;;
 1: len 6; hex 000000000835; asc      5;;
 2: len 7; hex 81000000880110; asc        ;;
 3: len 4; hex 00000005; asc     ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 000000000836; asc      6;;
 2: len 7; hex 82000001100110; asc        ;;
 3: len 4; hex 00000005; asc     ;;

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
852 OS file reads, 641 OS file writes, 357 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.27 writes/s, 1.64 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 2.45 non-hash searches/s
---
LOG
---
Log sequence number          28068318
Log buffer assigned up to    28068318
Log buffer completed up to   28068318
Log written up to            28068318
Log flushed up to            28068318
Added dirty pages up to      28068318
Pages flushed up to          28068318
Last checkpoint at           28068318
157 log i/o's done, 0.42 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 427323
Buffer pool size   8192
Free buffers       7194
Database pages     994
Old database pages 381
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 829, created 165, written 362
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 994, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140137389229824 , state=sleeping
Number of rows inserted 15, updated 0, deleted 2, read 28
0.00 inserts/s, 0.00 updates/s, 0.18 deletes/s, 1.00 reads/s
Number of system rows inserted 37, updated 356, deleted 0, read 4702
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

MySQL 8.0.21 の場合

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

実行計画

実行計画が変わっていることがわかります。サブクエリ部分が相関サブクエリでなくなり、 children テーブルもインデックスを使った参照に変わっています。

• 親テーブルが先に評価され、 UNIQUEキー uk_parent_idx_col を使っている （Using index）
• 子テーブルは外部キー fk_parent_id を使って type: ref なので、サブクエリの結果のレコード1件ずつに対してインデックスを使って比較している
  • https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html#explain-join-types

mysql> explain DELETE FROM children WHERE parent_id IN
    -> (SELECT parent_id FROM  parents WHERE parent_idx_col = 3);
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys             | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | parents  | NULL       | const | PRIMARY,uk_parent_idx_col | uk_parent_idx_col | 4       | const |    1 |   100.00 | Using index |
|  1 | DELETE      | children | NULL       | ref   | fk_parent_id              | fk_parent_id      | 4       | const |    2 |   100.00 | NULL        |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+

SHOW ENGINE INNODB STATUS;

ロックの取り方も変わっています。children テーブルでロック対象になっているレコードも減っています。

• 6行のロック
• children テーブルに IX ロック
  • 5行
    • 2行：外部キーのインデックス fk_parent_id にネクストキーロック
      • 00000003 ：parent_id = 3
        • 00000005：child_id = 5
        • 00000006：child_id = 6
      • 検索該当（削除対象）レコード
    • 2行：主キーのインデックスにレコードロック locks rec but not gap
      • 00000005： child_id = 5
      • 00000006 ： child_id = 6
    • 1行：外部キーのインデックス fk_parent_id にギャップロック locks gap before rec
      • 00000004 ： parent_id = 4
        • 00000007：parent_id = 7
• parents テーブルに IS ロック
  • UNIQUEキーのインデックス uk_parent_idx_col にレコードロック locks rec but not gap
  • 1行
    • 00000003：parent_idx_col = 3
      • 00000003 ： parent_id = 3

抜粋

トランザクションのロック全体

---TRANSACTION 2108, ACTIVE 20 sec
6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 18, OS thread handle 140669508515584, query id 116 localhost root starting

子テーブルへのロック

TABLE LOCK table `tmp`.`children` trx id 2108 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 80 index fk_parent_id of table `tmp`.`children` trx id 2108 lock_mode X
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000005; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000006; asc     ;;

RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `tmp`.`children` trx id 2108 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000005; asc     ;;
 1: len 6; hex 00000000083c; asc      <;;
 2: len 7; hex 020000011e0151; asc       Q;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 00000000083c; asc      <;;
 2: len 7; hex 020000011e017b; asc       {;;
 3: len 4; hex 00000003; asc     ;;

RECORD LOCKS space id 3 page no 5 n bits 80 index fk_parent_id of table `tmp`.`children` trx id 2108 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000004; asc     ;;
 1: len 4; hex 00000007; asc     ;;

親テーブルへのロック

TABLE LOCK table `tmp`.`parents` trx id 2108 lock mode IS
RECORD LOCKS space id 2 page no 5 n bits 72 index uk_parent_idx_col of table `tmp`.`parents` trx id 2108 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000003; asc     ;;

全体

=====================================
2022-01-10 01:05:07 0x7ff02c15f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 23 srv_active, 0 srv_shutdown, 4092 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 18
OS WAIT ARRAY INFO: signal count 17
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2113
Purge done for trx's n:o < 2113 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422144539163200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422144539162344, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2108, ACTIVE 20 sec
6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 18, OS thread handle 140669508515584, query id 116 localhost root starting
SHOW ENGINE INNODB STATUS
TABLE LOCK table `tmp`.`parents` trx id 2108 lock mode IS
RECORD LOCKS space id 2 page no 5 n bits 72 index uk_parent_idx_col of table `tmp`.`parents` trx id 2108 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000003; asc     ;;

TABLE LOCK table `tmp`.`children` trx id 2108 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 80 index fk_parent_id of table `tmp`.`children` trx id 2108 lock_mode X
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000005; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000006; asc     ;;

RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `tmp`.`children` trx id 2108 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000005; asc     ;;
 1: len 6; hex 00000000083c; asc      <;;
 2: len 7; hex 020000011e0151; asc       Q;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 00000000083c; asc      <;;
 2: len 7; hex 020000011e017b; asc       {;;
 3: len 4; hex 00000003; asc     ;;

RECORD LOCKS space id 3 page no 5 n bits 80 index fk_parent_id of table `tmp`.`children` trx id 2108 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000004; asc     ;;
 1: len 4; hex 00000007; asc     ;;

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
984 OS file reads, 734 OS file writes, 409 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.39 writes/s, 1.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 1.89 non-hash searches/s
---
LOG
---
Log sequence number          28221617
Log buffer assigned up to    28221617
Log buffer completed up to   28221617
Log written up to            28221617
Log flushed up to            28221617
Added dirty pages up to      28221617
Pages flushed up to          28221617
Last checkpoint at           28221617
179 log i/o's done, 0.26 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 453831
Buffer pool size   8192
Free buffers       7059
Database pages     1128
Old database pages 429
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 961, created 167, written 413
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1128, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140669059286784 , state=sleeping
Number of rows inserted 15, updated 0, deleted 2, read 20
0.00 inserts/s, 0.00 updates/s, 0.11 deletes/s, 0.17 reads/s
Number of system rows inserted 38, updated 356, deleted 0, read 5052
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

MySQL 8.0.21 でオプティマイザスイッチを変えてみる

ここまでで、 MySQL 8.0.20 と 8.0.21 とで動きが変わることは確認できました。最後に、8.0.21 でどのような最適化が効いてこの結果になっているのかを調べてみます。

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.

MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.21 (2020-07-13, General Availability)

という記載があったので、オプティマイザスイッチを変えて実験してみました。

対象の設定は以下の2つです。

• subquery materialization
• semijoin transformation

subquery materialization

まず、subquery materialization をオフにしてみます。

dev.mysql.com

SET SESSION optimizer_switch='materialization=off';

実行計画を取ります。

mysql> EXPLAIN DELETE FROM children WHERE parent_id IN
    -> (SELECT parent_id FROM  parents WHERE parent_idx_col = 3);
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys             | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | parents  | NULL       | const | PRIMARY,uk_parent_idx_col | uk_parent_idx_col | 4       | const |    1 |   100.00 | Using index |
|  1 | DELETE      | children | NULL       | ref   | fk_parent_id              | fk_parent_id      | 4       | const |    2 |   100.00 | NULL        |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+

オプティマイザスイッチを変える前と変わりませんでした。以下の記事では、こちらのオプティマイザスイッチを変更することで動きが変わっているので、サブクエリの形などの違いによってどちらの最適化が効くのかが違うようです。

qiita.com

semijoin transformation

materialization を on に戻して、今度はsemijoin transformationをオフにしてみます。

dev.mysql.com

SET SESSION optimizer_switch='semijoin=off';

mysql> EXPLAIN DELETE FROM children WHERE parent_id IN
    -> (SELECT parent_id FROM  parents WHERE parent_idx_col = 3);
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys             | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | DELETE      | children | NULL       | ALL   | NULL                      | NULL              | NULL    | NULL  |    8 |   100.00 | Using where |
|  2 | SUBQUERY    | parents  | NULL       | const | PRIMARY,uk_parent_idx_col | uk_parent_idx_col | 4       | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+

8.0.20 の場合に似た実行計画になることが確認できました。MySQL 8.0.20 のものと比較すると、

• select_type が DEPENDENT SUBQUERYからSUBQUERY なっている
• rowsが 10 → 8 に変わっている

という違いはありますが、実行して SHOW ENGINE INNODB STATUS をとってみると、ロックのかかり方は（MySQL 8.0.20 と）変わらないように見えます。

ロックの詳細については以下。

=====================================
2022-01-10 07:49:33 0x7ff02c15f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 25 srv_active, 0 srv_shutdown, 20947 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 24
OS WAIT ARRAY INFO: signal count 22
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2119
Purge done for trx's n:o < 2119 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422144539163200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422144539162344, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2114, ACTIVE 18 sec
4 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 2
MySQL thread id 18, OS thread handle 140669508515584, query id 134 localhost root starting
show engine innodb status
TABLE LOCK table `tmp`.`parents` trx id 2114 lock mode IS
RECORD LOCKS space id 2 page no 5 n bits 72 index uk_parent_idx_col of table `tmp`.`parents` trx id 2114 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000003; asc     ;;

TABLE LOCK table `tmp`.`children` trx id 2114 lock mode IX
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `tmp`.`children` trx id 2114 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000000082a; asc      *;;
 2: len 7; hex 82000001110110; asc        ;;
 3: len 4; hex 00000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 6; hex 00000000082b; asc      +;;
 2: len 7; hex 810000010e0110; asc        ;;
 3: len 4; hex 00000001; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 6; hex 000000000830; asc      0;;
 2: len 7; hex 82000000860110; asc        ;;
 3: len 4; hex 00000002; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000004; asc     ;;
 1: len 6; hex 000000000831; asc      1;;
 2: len 7; hex 81000000860110; asc        ;;
 3: len 4; hex 00000002; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000005; asc     ;;
 1: len 6; hex 000000000842; asc      B;;
 2: len 7; hex 010000011d0151; asc       Q;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 000000000842; asc      B;;
 2: len 7; hex 010000011d017b; asc       {;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000007; asc     ;;
 1: len 6; hex 000000000834; asc      4;;
 2: len 7; hex 82000000880110; asc        ;;
 3: len 4; hex 00000004; asc     ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000008; asc     ;;
 1: len 6; hex 000000000835; asc      5;;
 2: len 7; hex 81000000880110; asc        ;;
 3: len 4; hex 00000004; asc     ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000009; asc     ;;
 1: len 6; hex 000000000836; asc      6;;
 2: len 7; hex 82000001120110; asc        ;;
 3: len 4; hex 00000005; asc     ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 000000000837; asc      7;;
 2: len 7; hex 810000010f0110; asc        ;;
 3: len 4; hex 00000005; asc     ;;

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
984 OS file reads, 784 OS file writes, 445 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          28226956
Log buffer assigned up to    28226956
Log buffer completed up to   28226956
Log written up to            28226956
Log flushed up to            28226956
Added dirty pages up to      28226956
Pages flushed up to          28226956
Last checkpoint at           28226956
191 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 453831
Buffer pool size   8192
Free buffers       7056
Database pages     1131
Old database pages 429
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 961, created 170, written 439
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1131, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140669059286784 , state=sleeping
Number of rows inserted 15, updated 0, deleted 4, read 49
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 38, updated 356, deleted 0, read 5052
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

微妙な違いはありますが、設定変更前の MySQL 8.0.21 では semijoin の最適化が効いていたと言えそうです。semijoin についてのドキュメントの一番下をみると、以下のように記載されています。

In MySQL 8.0.21 and later, a semijoin transformation can also be applied to a single-table UPDATE or DELETE statement that uses a [NOT] IN or [NOT] EXISTS subquery predicate, provided that the statement does not use ORDER BY or LIMIT, and that semijoin transformations are allowed by an optimizer hint or by the optimizer_switch setting.

MySQL :: MySQL 8.0 Reference Manual :: 8.2.2.1 Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations

終わりに

MySQL 8.0.21 で新しい最適化が効くようになったよ！というお話でした。

なお、本記事の執筆にあたっては mysql-casual Slack の皆様（特に @yoku0825 さん @hmatsu47 さん）に教えていただいた内容を大いに参考にしました。ありがとうございました！

mysql-casual.connpass.com

参考

qiita.com

abicky.net

実践ハイパフォーマンスMySQL 第3版

dev.mysql.com

dev.mysql.com

詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド

nippondanji.blogspot.com

yoku0825.blogspot.com

dev.classmethod.jp

*1:@hmatsu47 さんから情報を提供していただきました。