MySQL 8.0.24 で導入された相関スカラーサブクエリの最適化を試してみた

MySQL 8.0.24 では相関スカラーサブクエリ（correlated scalar subquery）の最適化が新たに導入されたと聞いて、試してみました。

効果が確認できずに一旦記事化を断念したのですが、こちらの記事でオプティマイザスイッチの指定が必要だったことを知り（ありがとうございます）、再確認してみました。

• 今日は、MySQL8.0.24の変更点オプティマイザーノートについての日。（今日はなにの日。）

※一旦断念する前は、うっかりヒント句ばかり試していました。

相関スカラーサブクエリとは？

相関サブクエリはサブクエリの評価を（最初に 1 回ではなく）複数回行う必要があるサブクエリ、スカラーサブクエリは最大で 1 行を返すサブクエリですが、これだけだとよくわからないので以下の記事を参考にしてください。

• SQLアタマアカデミー（gihyo.jp）
  • 第9回　SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～　（2）相関サブクエリ
  • 第9回　SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～　（3）便利で危険な相関サブクエリ①
  • 第9回　SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～　（4）便利で危険な相関サブクエリ②

MySQL 8.0 の公式リファレンスマニュアルの説明ページはこちらです（「Beginning with MySQL 8.0.24」以降の部分）。

• 13.2.11.7 Correlated Subqueries（MySQL 8.0 Reference Manual）

※なお、MySQL 8.0 公式リファレンスマニュアルには日本語版が登場しましたが、残念ながらこの項目の記載はありません。

• MySQL 8.0 日本語リファレンスマニュアルが公開されました（note）

試してみる

公式リファレンスマニュアルに示されている SQL（文）に近いものを試してみます。

テーブル定義

テーブル定義

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int NOT NULL,
  `str` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=163841 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

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

テストデータ

t1 が 10 行 1 セット、t2 が 4 行 1 セットでそれぞれ 163,840 行、65,536 行生成しました（同じパターンの繰り返し）。

テストデータ

mysql> SELECT * FROM t1 ORDER BY id LIMIT 10;
+----+----+----------------------------------------------------+
| id | a  | str                                                |
+----+----+----------------------------------------------------+
|  1 |  1 | 1111111111                                         |
|  2 |  2 | 22222222222222222222                               |
|  3 |  3 | 333333333333333333333333333333                     |
|  4 |  4 | 4444444444444444444444444444444444444444           |
|  5 |  5 | 55555555555555555555555555555555555555555555555555 |
|  6 |  6 | 6666666666666666666666666666666666666666           |
|  7 |  7 | 777777777777777777777777777777                     |
|  8 |  8 | 88888888888888888888                               |
|  9 |  9 | 9999999999                                         |
| 10 | 10 | 10                                                 |
+----+----+----------------------------------------------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM t1 ORDER BY id LIMIT 163830, 10;
+--------+--------+----------------------------------------------------+
| id     | a      | str                                                |
+--------+--------+----------------------------------------------------+
| 163831 | 163831 | 1111111111                                         |
| 163832 | 163832 | 22222222222222222222                               |
| 163833 | 163833 | 333333333333333333333333333333                     |
| 163834 | 163834 | 4444444444444444444444444444444444444444           |
| 163835 | 163835 | 55555555555555555555555555555555555555555555555555 |
| 163836 | 163836 | 6666666666666666666666666666666666666666           |
| 163837 | 163837 | 777777777777777777777777777777                     |
| 163838 | 163838 | 88888888888888888888                               |
| 163839 | 163839 | 9999999999                                         |
| 163840 | 163840 | 10                                                 |
+--------+--------+----------------------------------------------------+
10 rows in set (0.11 sec)

mysql> SELECT * FROM t2 ORDER BY id LIMIT 4;
+----+---+
| id | a |
+----+---+
|  1 | 1 |
|  2 | 3 |
|  3 | 7 |
|  4 | 8 |
+----+---+
4 rows in set (0.00 sec)

mysql> SELECT * FROM t2 ORDER BY id LIMIT 65532, 10;
+-------+--------+
| id    | a      |
+-------+--------+
| 65533 | 163831 |
| 65534 | 163833 |
| 65535 | 163837 |
| 65536 | 163838 |
+-------+--------+
4 rows in set (0.02 sec)

EXPLAINの確認

最適化 OFF の場合

EXPLAIN(最適化OFF)

mysql> EXPLAIN FORMAT=TREE
    ->   SELECT * FROM t1
    ->     WHERE ( SELECT a FROM t2
    ->               WHERE t2.a=t1.a ) > 0\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((select #2) > 0)  (cost=16682.58 rows=163618)
    -> Table scan on t1  (cost=16682.58 rows=163618)
    -> Select #2 (subquery in condition; dependent)
        -> Index lookup on t2 using idx_a (a=t1.a)  (cost=1.09 rows=1)

1 row in set, 1 warning (0.00 sec)

Visual Explain だとこんな感じです。

※コスト試算値など数値が違う部分がありますが、サンプリング統計情報をベースとした試算値なので実行タイミングで変わることがあります。以降も同様。

最適化 ON の場合

EXPLAIN(最適化ON)

mysql> EXPLAIN FORMAT=TREE
    ->   SELECT /*+ SET_VAR(optimizer_switch = 'subquery_to_derived=on') */ * FROM t1
    ->     WHERE ( SELECT a FROM t2
    ->               WHERE t2.a=t1.a ) > 0\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=10761.64 rows=10981)
    -> Filter: (reject_if((derived_1_2.Name_exp_2 > 1)) and (derived_1_2.a is not null))  (cost=0.34..3709.04 rows=10981)
        -> Table scan on derived_1_2  (cost=2.50..2.50 rows=0)
            -> Materialize  (cost=2.50..2.50 rows=0)
                -> Group aggregate: count(0)
                    -> Filter: (t2.a > 0)  (cost=6622.10 rows=32947)
                        -> Index range scan on t2 using idx_a  (cost=6622.10 rows=32947)
    -> Index lookup on t1 using idx_a (a=derived_1_2.a)  (cost=0.54 rows=1)

1 row in set, 1 warning (0.01 sec)

Visual Explain だとこんな感じです。

複雑化していますが、コスト試算値は最適化 ON で小さくなっているようです。

実行時間を比較してみる

• 元のままの SQL（文）
• SELECT *をSELECT COUNT(*)に変えたもの

の 2 種類で試した結果です（単位 : 秒）。

相関スカラーサブクエリ最適化	OFF	ON
SELECT *	0.39	0.17
SELECT COUNT(*)	0.62	0.89

元のままのSELECT *では遅くなり、SELECT COUNT(*)では逆に速くなるという微妙な結果になりました。

EXPLAINで表示される結果に差はないのですが、件数だけ返すのとデータページの列も返すのとでは実質的な処理の量が変わるようです。

余談

この記事は MySQL 8.0 の薄い本 8.0.24 対応版改訂に向けて書いたものですが、すでに予告したとおり、MySQL 8.0 の薄い本は 5 月発行予定の 8.0.24 対応版で更新作業を終了します。

• 「MySQL 8.0 の薄い本」更新終了のお知らせ（note）

更新期間は 2 年余りでしたが、お付き合いいただいたみなさま、ありがとうございました。

今後は MySQL 関連のコミュニティ活動から少し距離を置きます（ちょっと別のことを始める準備期間が必要なので。昔風にいえば**「ROM ります」**的なスタンスです）。
登壇を含むアウトプット活動は MySQL 8.0 の薄い本 8.0.24 対応版で一旦終了です（ヒラメじゃなくなったイルカさん、欲しかったけどタイミングが合わなくて残念でした）。

---

• Qiitaに投稿したMySQL 8.0関連記事
• MySQL 8.0 の薄い本（無料で配布中！） ←書くのはこれで最後