APIのパフォーマンス改善について

これはHubble Advent Calendar 2024の6日目¹の記事です。

はじめに

こんにちは、バックエンドエンジニアの@yamaopankuです！
株式会社Hubbleさんにて2024年10月より業務委託として参画させていただいております。

Hubbleではバックエンド・DBはそれぞれRuby on Rails 7系、MySQL （AuroraDB）8系で構成されており、Angularで構成されたフロントエンドからAPIのリクエストを受けます。

今回はJOINしてから行ったAPIのパフォーマンスチューニングに関して記事を書いていこうと思います！

取り組んだ課題

Hubbleは契約書管理のSaaSでWordやPDF、EXCELなどの契約書を一元管理できます。
それぞれの契約書に関し、契約書名や契約相手方、契約開始日などの項目を設定可能であり、登録した契約書とその項目値を一覧表示し、項目値で検索できる機能があります。(ドキュメントリストといいます)

とても便利でユーザからも好評な機能ですが、データ量の増加に伴い検索のAPIパフォーマンスが課題として挙がってきており、レイテンシの向上に向けて取り組むことになりました。(p90で18s、AVGで7sほど)

解決に向けてのアプローチ

ボトルネックの把握

HubbleではDatadogを導入しているため、まずはAPMで該当APIのリクエストのflamegraphからボトルネックになっていそうな箇所を把握するところから開始しました。

すると、一つのSQLが80%近くを占めていることがわかりました。
以下のようなイメージのSQLです。
table_aが契約書に設定できる項目であり、契約書 : table_aは1 : Nとなっています。

SELECT
    table_a.x_id,
    table_a.y_id,
    table_a.z_id,
    table_a.value
    table_a.s_id
FROM
    table_a
    ...
    INNER JOIN
        (
            SELECT
                table_a.*
            FROM
                table_a
            WHERE
                table_a.s_id = ?
        ) AS table_a_001
    ON  (
            table_a.row_id = table_a_001.row_id
        )
    ... 指定した項目数だけtable_aの自己結合(INNER JOIN or LEFT OUTER JOIN)
WHERE
    table_a.x_id IN(サブクエリ)
AND table_a.s_id IN(サブクエリ)
AND (
        table_a_001.column_id = ?
		AND table_a_001.value LIKE ?
    )
... 指定した項目数だけtable_aの条件をANDで繋ぐ

「推測するな、計測せよ」という金言に則り、EXPLAINで実行計画を見ると、テーブルアクセスがサブクエリ + 指定した条件数分だけ行われていることがわかります。

また、MySQL 8.0.18以降はEXPLAIN ANALYZEを使用し、より詳細な実行計画を見ることができ、ステップごとの実行時間を見ることができるため、合わせて確認しました。
EXPLAIN ANALYZEはこちらの記事がわかりやすくておすすめです。

EXPLAIN ANALYZEで実行計画を見ると、Nested loop inner joinの箇所で時間を要しており、自己結合の箇所が改善ポイントと特定しました。

解決策

自己結合している理由としては、同じ契約書について複数の項目で条件を絞り込んでいることが背景のため、項目テーブル(先のtable_a)で契約書単位にGROUP BYすれば一度のテーブルアクセスで済むのではと考えました。
次のようなクエリのイメージです。

SELECT
    ...
FROM
    table_a
WHERE
    s_id IN(指定した項目ID)
    AND x_id IN(絞り込み対象の契約書ID)
GROUP BY
    x_id
HAVING COUNT((s_id = ? AND 条件) OR NULL) > 0
... 指定した項目数だけ同様のCOUNTの条件を作り、ANDで繋ぐ

絞り込みとGROUP化対象のカラムでインデックスが使えるよう、s_idとx_idの複合インデックスを作成の上、実行すると変更前と比較し、18s → 3.5sと向上に成功しました。
(理想は1s未満ではありますが...)
EXPLAINによる実行計画もtable_aへのテーブルアクセス1回のみになりました。

今後の取り組み

今回以外のAPIでもまだまだパフォーマンスを上げていけそうなものがあるため、リクエスト数やレイテンシを日々観察しながら調査・対応していきたいと考えています。
また、Hubbleでは一部の機能でOpenSearchも利用しているため、SQLチューニングのみでの解決が難しくなってきたら更なる活用も検討していきたいと思います。

まとめ

この記事では、実行計画をもとにしたSQLチューニングに関して改善に向けて行動したことを書いてきました。
MySQL 8.0はまだ使い始めた段階のサービスが多いと感じており、運用面で課題になることもあると思いますが、EXPLAIN ANALYZEをはじめとした有益な変更もあるため、今後の展望に期待したいですね。

この記事がパフォーマンス改善に取り組む方にとって少しでも参考になれば幸いです。

明日は@power3812の投稿です！

1. 平日のみの投稿なので、投稿日は9日ですが6日目の記事としています。 ↩