Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
3
Help us understand the problem. What is going on with this article?
@hmatsu47

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

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

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

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

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

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

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

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

試してみる

公式リファレンスマニュアルに示されている 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 だとこんな感じです。

my8024_ve.png

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

最適化 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 だとこんな感じです。

my8024_ve2.png

複雑化していますが、コスト試算値は最適化 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 対応版で更新作業を終了します。

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

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


3
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
hmatsu47
名古屋で士業向けWebサービスのインフラ構築管理、たまにアプリケーション開発をやっています。 業務利用しているもの、個人研究など、気長にのんびり投稿していきます。ニッチ狙いが多めです。 IPA RISS(001158)・NW・DB/日商・大商2級コレクター?(簿記・ビジネス法務・ビジネス会計)。
infra-workshop
インフラ技術を勉強したい人たちのためのオンライン勉強会です

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
3
Help us understand the problem. What is going on with this article?