inSmartBank

B/43を運営する株式会社スマートバンクのメンバーによるブログです

MySQL 8.0アップグレード後に性能劣化したクエリ: セミジョイン編

Engineering

データベースアップグレード後の性能劣化、イヤですよね。

去る2023年某日、弊社ではAmazon Aurora MySQL 互換エディション 2 (MySQL 5.7 互換) から Aurora MySQL 互換エディション 3 (MySQL 8.0 互換) にアップグレードしました。当時の背景やアップグレードに関する知見は以下の記事をぜひ読んでみてください。

blog.smartbank.co.jp

ソフトウェアバージョンアップをするとき、旧バージョンが抱えていた問題の解決などの恩恵を我々は期待します。しかし時には予期せぬデグレーションに遭遇することもあります。我々のMySQL 8.0へのアップグレード前後においてもいくつかの問題に遭遇しました。

本記事ではそんな問題の一つ、MySQL 8.0のオプティマイザが選択したセミジョイン最適化が性能劣化を引き起こした事例と解決方法について紹介します。

問題の概要: アップグレード後に117.5倍の性能劣化

MySQL 5.7から8.0へのアップグレード後、とある日次バッチが発行する特定のSELECTクエリの実行時間が大幅に増加しました。クエリ単体で数十倍、バッチ全体に占める該当クエリの実行時間は117.5倍も遅くなっていました。

指標 5.7 8.0
クエリ単体 約20ms 約1,500ms
バッチ全体 67s 2h 11m 33s

劣化前の山がかすむほど、劣化後の山が高いようす

ユーザー影響のほとんどないバッチだったのは幸いでしたが各種マシンリソースを使いまくっている状況なので看過できません。

問題のクエリ

問題となったのは以下のクエリです。なお、本記事に登場するデータベース名・テーブル名・カラム名・インデックス名はすべて仮の名称です。

select `posts`.*
from `posts`
where
  exists (
    select `comments`.*
    from `comments`
    where `comments`.`status` = 1 and `comments`.`post_id` = `posts`.`id`
  )
  and `posts`.`status` = 1
order by `posts`.`id` asc
;

登場するposts, commentsテーブルの構造やインデックスは以下の通りです。

create table posts
  (
    id         bigint unsigned auto_increment primary key,
    status     int default 1 not null
  );
create index index_posts_on_status on posts (status);

create table comments
  (
    id         bigint unsigned auto_increment primary key,
    post_id    bigint unsigned not null,
    status     int default 1   not null,
    constraint fk_rails_0933751166 foreign key (post_id) references posts (id)
  );
create index index_comments_on_status on comments (status);
create index index_comments_on_post_id on comments (post_id);

実際の処理とは異なりますが、イメージが湧くようにクエリにやらせていることを説明すると...

「ドラフトのコメント (from comments where comments.status = 1) を持つ、アクティブな投稿 (from posts where posts.status = 1) を検索する」ようなイメージです。

原因分析

最初に調査結果を述べます。

MySQL 8.0.16からEXISTS句にも効くようになったセミジョイン最適化が問題のクエリに働いたが、サブクエリの処理行数が多すぎてmaterializationに時間がかかるのでかえって性能劣化したというのが今回の顛末でした。

以下に詳しく調査過程を記していきます。

劣化したクエリの調査

まずは問題のクエリについてEXPLAINコマンドを使用して実行計画を確認してみました。

+--+------------+-----------+----------+------+--------------------------------------------------+------------------------+-------+----------------------+------+--------+-----+
|id|select_type |table      |partitions|type  |possible_keys                                     |key                     |key_len|ref                   |rows  |filtered|Extra|
+--+------------+-----------+----------+------+--------------------------------------------------+------------------------+-------+----------------------+------+--------+-----+
|1 |SIMPLE      |posts      |null      |ref   |PRIMARY,index_posts_on_status                     |index_posts_on_status   |4      |const                 |199188|100     |null |
|1 |SIMPLE      |<subquery2>|null      |eq_ref|<auto_distinct_key>                               |<auto_distinct_key>     |8      |my_awesome_db.posts.id|1     |100     |null |
|2 |MATERIALIZED|comments   |null      |ref   |index_comments_on_post_id,index_comments_on_status|index_comments_on_status|4      |const                 |283946|100     |null |
+--+------------+-----------+----------+------+--------------------------------------------------+------------------------+-------+----------------------+------+--------+-----+

rows列を見ると2つのテーブルにおいて数十万行が処理対象となっていて重そうではあります。しかし、対象のテーブルは2つだけなのにEXPLAINの結果にはなぜ3行も現れているのでしょうか？

特に3行目、MATERIALIZEDという見慣れないselect_typeが現れていることが気に掛かり調べてみたところ、これはセミジョイン最適化が有効になっているためだとわかりました。

セミジョイン最適化とは

セミジョイン（準結合）はMySQL 5.6で追加された機能です。特定の条件（WHERE句またはON句内にINを使用するなど）を満たすサブクエリ内のテーブルの重複レコードを取り除き、結合と同じような動きをします。

アップグレード前のMySQL 5.7では、EXISTS句を使用しているクエリではこのセミジョイン最適化は動作していませんでした。しかしMySQL 8.0（正確にはMySQL 8.0.16以上）からEXISTS句に対してもセミジョイン最適化が有効になり、問題のクエリに対しても働くようになったとわかりました。*1

しかし、この最適化がなぜ裏目に出たのでしょうか。

materialization動作

MySQLがセミジョインを行う戦略は複数あり、今回はmaterialization動作がオプティマイザによって選択されていました。サブクエリ内のテーブルに対して内部的に実体化した一時テーブルを作成し、さらに重複レコードを削除するためにインデックスを作成してから結合を行う動作です。*2

今回のケースではサブクエリ内の行数が数十万行と多すぎるあまり、一時テーブル・インデックス作成や重複排除に時間がかかって性能が劣化してしまったのではないかと推測しました。

セミジョインを無効化してみる

上記の推測が正しいか検証するため、以下のようにNO_SEMIJOINヒント句を使用してセミジョイン最適化を無効化して結果を見てみます。

select /*+ NO_SEMIJOIN(@test) */ `posts`.*
from `posts`
where
  exists (
    select /*+ QB_NAME(test) */ `comments`.*
    from `comments`
    where `comments`.`status` = 1 and `comments`.`post_id` = `posts`.`id`
  )
  and `posts`.`status` = 1
order by `posts`.`id` asc
;

すると実行計画からMATERIALIZEDが消え、かなりのrowsが削減できました。実際の実行時間もMySQL 5.7時代と同等に近いパフォーマンスを得ることができました。

+--+------------------+--------+----------+----+--------------------------------------------------+-------------------------+-------+----------------------+------+--------+-----------+
|id|select_type       |table   |partitions|type|possible_keys                                     |key                      |key_len|ref                   |rows  |filtered|Extra      |
+--+------------------+--------+----------+----+--------------------------------------------------+-------------------------+-------+----------------------+------+--------+-----------+
|1 |PRIMARY           |posts   |null      |ref |index_posts_on_status                             |index_posts_on_status    |4      |const                 |199188|100     |Using where|
|2 |DEPENDENT SUBQUERY|comments|null      |ref |index_comments_on_post_id,index_comments_on_status|index_comments_on_post_id|8      |my_awesome_db.posts.id|1     |50      |Using where|
+--+------------------+--------+----------+----+--------------------------------------------------+-------------------------+-------+----------------------+------+--------+-----------+

解決策

さて、上記のようにオプティマイザヒントを使用することで問題が解決できることがわかりました。しかしながらヒント句の利用には後述する懸念があり、クエリの書き換えで解決できないかを検討してみました。

オプティマイザヒントの使用

前提を補足すると、対象のアプリケーションではRailsを使用しており、SQLはORMのActiveRecordを介して発行されます。

Rails 6.0以降からはoptimizer_hintsメソッドが使用できます。詳しくは公式ドキュメントや追加されたpull requestをご参照ください。以下は公式ドキュメントに記載されている使用例です。

Topic.optimizer_hints("MAX_EXECUTION_TIME(50000)", "NO_INDEX_MERGE(topics)")
# SELECT /*+ MAX_EXECUTION_TIME(50000) NO_INDEX_MERGE(topics) */ `topics`.* FROM `topics`

このようにORMがサポートする方法で記述できるのは嬉しいものの、ヒント句の利用そのものに対して以下の懸念がありました。

  1. 可読性/チームの知識の観点
    • チームで運用するアプリケーションでは一切ヒント句を用いていない。コード・SQLとして見慣れない形になる
  2. 保守性の観点
    • 現時点では効果的なヒントだが、クエリやデータベース設定の変更によりパフォーマンスに悪影響を与える可能性がある（(1)の観点から、見落とされる可能性がある）
  3. データベースの進化追従の観点
    • データベースの新しいバージョンでオプティマイザがさらに改善されたとき、今回追加したヒント句により恩恵を受けられなくなる可能性がある

なるべくオプティマイザ任せで良い感じになるようにしたいという方向性です。

クエリの書き換え

オプティマイザ任せで良い感じにするため、ヒント句を使わずとも同一の結果を得られるようなクエリへの書き換えを試みます。

EXISTSサブクエリをINNER JOINに変更

先述の通りサブクエリの存在がオプティマイザをかどわかす原因なのでサブクエリをなくしてみます。結合により重複が生まれるのでDISTINCTを使用します。

select distinct `posts`.*
from `posts`
       inner join `comments`
         on `comments`.`status` = 1 and `comments`.`post_id` = `posts`.`id`
where `posts`.`status` = 1
order by `posts`.`id` asc
;

簡単な変更ですがこれによりNO_SEMIJOINヒント句を使用したときに近いパフォーマンスが出るようになりました。

カバリングインデックスによるさらなる改善

さらに、このクエリを発行しているアプリケーションの処理を見直したところ、posts.id以外のカラムは使用していないことがわかりました。無批判なSELECT *はよくないですね。

クエリが利用するpostsテーブルのカラムはidとstatusのみなので、現存するindex_posts_on_statusを用いたカバリングインデックスを効かせることができそうです。

最終的に、以下のようにクエリを書き換えることでMySQL 8.0におけるパフォーマンスを大幅に改善できました。

Before

select `posts`.*
from `posts`
where
  exists (
    select `comments`.*
    from `comments`
    where `comments`.`status` = 1 and `comments`.`post_id` = `posts`.`id`
  )
  and `posts`.`status` = 1
order by `posts`.`id` asc
;

After

select distinct `posts`.`id`
from `posts`
       inner join `comments`
         on `comments`.`status` = 1 and `comments`.`post_id` = `posts`.`id`
where `posts`.`status` = 1
order by `posts`.`id` asc
;

+--+-----------+--------+----------+----+--------------------------------------------------+-------------------------+-------+----------------------+------+--------+-----------+
|id|select_type|table   |partitions|type|possible_keys                                     |key                      |key_len|ref                   |rows  |filtered|Extra      |
+--+-----------+--------+----------+----+--------------------------------------------------+-------------------------+-------+----------------------+------+--------+-----------+
|1 |SIMPLE     |posts   |null      |ref |PRIMARY,index_posts_on_status                     |index_posts_on_status    |4      |const                 |199188|100     |Using index|
|1 |SIMPLE     |comments|null      |ref |index_comments_on_post_id,index_comments_on_status|index_comments_on_post_id|8      |my_awesome_db.posts.id|1     |50      |Using where|
+--+-----------+--------+----------+----+--------------------------------------------------+-------------------------+-------+----------------------+------+--------+-----------+

postsテーブルに対してUsing indexが表示され、カバリングインデックスが効いていることが確認できました。

Railsのコード

SQLの改善と直接の関係がない余談ですが、Rails上でのコードのBefore/Afterも参考までに記しておきます。

Beforeのコードでは以下のようにEXISTS句を使用していました。CommentのActiveRecord::Relationをwhereのキーワード引数で渡すとサブクエリとして展開されるのが洒脱ですね。

class Post
  scope :with_draft_comments, -> {
    active.where(
      'exists (:comments)',
      comments: Comment.draft.where('comments.post_id = posts.id')
    ).order(:id)
  }
end

Afterのコードではdraft_comments associationを作り、postsテーブルとJOINさせています。これによりINNER JOIN comments ON comments.status = 1 AND comments.post_id = posts.idが発行されるようになりました。

それ以外のdistinctやselect(:id)はその通りの意味で、改めてORMとしての高い表現力が心に沁み渡ります。

class Post
  has_many :draft_comments, -> { Comment.draft }, class_name: 'Comment', inverse_of: :post

  scope :with_draft_comments, -> {
    active.joins(:draft_comments).distinct.select(:id).order(:id)
  }
end

結果: 127倍の高速化

クエリ書き換えによる改善後、クエリ単体の実行時間はアップグレード以前の水準に戻り、バッチ全体に占める同クエリの合計実行時間もMySQL 5.7時代よりもやや短縮されました。

指標 5.7 8.0 (性能劣化時) 8.0 (改善後)
クエリ単体 約20ms 約1,500ms 約20ms
バッチ全体 67s 2h 11m 33s 62s

アップグレード前と同水準に戻っただけではありますが、同バッチに占める該当クエリの実行時間を比較すれば数行のクエリ変更によって127倍高速になりました。

劣化時の山が高すぎて、改善後の山がかすむようす

まとめ

MySQL 8.0へのアップグレードは多くの改善をもたらしますが、一部のクエリでは予期せぬパフォーマンス低下を引き起こす可能性があります。

本記事では我々が遭遇した"セミジョイン最適化が逆効果となる事例"について解説し、クエリの書き換えによって問題を解決したことをお伝えしました。同じような問題に悩む方の参考になれば幸いです。

本記事はEngineering Managerの@ohbaryeが執筆しました。

スマートバンクではパフォーマンスの監視や改善に興味関心のあるエンジニアを募集しています！

smartbank.co.jp

*1:参考「MySQL 8.0のセミジョインの変更点」 https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0103

*2:各種戦略の詳細については「MySQLの準結合（セミジョイン）について」の記事が詳しいです

We create the new normal of easy budgeting, easy banking, and easy living.
In this blog, engineers, product managers, designers, business development, legal, CS, and other members will share their insights.