サムネイル

はじめに

こんにちは！早稲田大学創造理工学部総合機械工学科 3 年の野村恒晴です。

12 月の 1 ヶ月間、合同会社DMM.comで就業型インターンシップに参加しました。マーケティングテクノロジー部第一エンジニアチームに配属され、バックエンドエンジニアとして業務に取り組みました。

本記事では私がインターン中に取り組んだ API エンドポイントのレイテンシ改善のタスクと、そこから得た学びについて紹介させていただきます。

背景

今回私がレイテンシ改善をした API エンドポイントは、社内で利用される管理画面の広告配信情報を取得するためのエンドポイントでした。

この API では扱うデータ量の増加に伴ってレイテンシが悪化していました。

特に重いケースでは、本番環境で 1300 ms ほどかかることもありました。

このままではユーザー体験を損なってしまうため、高速化をゴールに掲げ、改善に取り組みました。

タスクの流れ

以下の 5 ステップで進めました。

エンドポイントのボトルネックを特定
特定したボトルネックのクエリを解析
クエリ改善の試行錯誤
実装
リリース・動作確認

1. エンドポイントのボトルネックを特定

まず、Datadog APM を使用してトレーシングを実施し、どの処理に時間がかかっているかを可視化しました。

その結果を見てみると、以下の 2 つのクエリがボトルネックでした。

もっとも重い： 該当配信情報の個数を数えるクエリ（SELECT COUNT(*)）
次に重い： 該当配信情報を取得するクエリ

まずはもっとも影響の大きい「個数を数えるクエリ」の改善を最優先方針としました。

2. 特定したボトルネックのクエリを解析

こちらがボトルネックとなっていた、個数を数えるクエリのおおよその構造になります。

以下の特徴を持ったクエリでした。

SELECT COUNT(*)を利用して、条件に合致するレコードの件数を取得する
前方一致検索を行うWHERE LIKEが存在する
外部キーを利用したWHERE句での絞り込みを行う
OR演算子の内側に、NOT EXISTSを利用した相関サブクエリが存在する

SELECT COUNT(*)
FROM table_name outer_table
WHERE
    outer_table.fk_column = 'FK_ID'
    AND outer_table.like_column LIKE 'xxx_%'
    AND (
        <SOME CONDITION>
        OR
        NOT EXISTS (
            SELECT 1
            FROM table_name inner_table
            WHERE
                inner_table.fk_column = 'FK_ID'
                AND inner_table.like_column LIKE outer_table.like_column || '_%'
        )
    );

ボトルネックとなっていたクエリを EXPLAIN ANALYZE 解析したところ、以下の問題が見つかりました。

Seq Scan の発生： インデックスが張られているはずのテーブルが全表スキャン（Seq Scan）されていた。
NOT EXISTS 句の負荷： NOT EXISTS 句内の実行時間がボトルネックとなっており、サブプランのループが全体のパフォーマンスを低下させていた。

3. クエリ改善の試行錯誤

試行錯誤 1：NOT EXISTS の解消

目的： NOT EXISTS 部分のサブプランによる二重ループを解消し、処理を効率化する。
仮説： NOT EXISTS と同じ処理をLEFT JOINによる自己結合で書き換えれば、計算量を抑えられるのではないか。
方法： クエリを自己結合の形式に書き換えて実行した。
結果： クエリの実行時間が 1000 ms → 900 ms となり、パフォーマンスはほぼ変わりませんでした。
考察： NOT EXISTS もLEFT JOINによる自己結合も計算量は変わらず、同じ動作をするクエリで参照行数を減らすことは原理的に難しいことが分かりました。インデックスを活かせるクエリにすることが重要です。

試行錯誤 2：Seq Scan の改善

目的： テーブルの全表スキャンを回避し、インデックススキャンを機能させる。
仮説： LIKE 演算子ではなく不等号を用いることで、インデックスが使われるのではないか。
方法： inner_table.like_column LIKE outer_table.like_column || '_%' を inner_table.like_column > outer_table.like_column のように書き換えた。
結果： 不等号では元の条件を表現できませんでした。
考察： 今回の仕様を満たすクエリを不等号で書き直すことは出来なさそうでした。しかし、不等号で書き換えると実行時間が10ms程度に高速化され、インデックススキャンが高速化に寄与する確信を得ました。

試行錯誤 3：OR 条件の排除

目的： 実行計画を最適化し、クエリの実行速度を劇的に向上させる。
仮説： OR 部分でクエリが重くなっており、条件が排反であればクエリを分けて合算する方が速いのではないか。
方法： クエリをORの前後で 2 つに分割し、それぞれの個数を計算して和を取るロジックに変更した。

SELECT COUNT(*)
FROM table_name
WHERE
    fk_column = 'FK_ID'
    AND like_column LIKE 'xxx_%'
    AND <SOME CONDITION>;


SELECT COUNT(*)
FROM table_name outer_table
WHERE
    outer_table.fk_column = 'FK_ID'
    AND outer_table.like_column LIKE 'xxx_%'
    AND NOT EXISTS (
        SELECT 1
        FROM table_name inner_table
        WHERE
            inner_table.fk_column = 'FK_ID'
            AND inner_table.like_column LIKE outer_table.like_column || '_%'
    );

結果： クエリの実行時間が 1000 ms → 50 ms となり、大幅にパフォーマンスが改善しました。
考察： 今回のように個数だけを考える場合、OR を使ってクエリを一つにする必要はなく、クエリを分けることで実行計画に悪影響を与えずに済むことが分かりました。

試行錯誤 4：インデックスの最適化

目的： like_column カラムと fk_column カラムを確実にインデックススキャンさせる。
仮説： varchar_pattern_ops 演算子クラスを設定すれば前方一致検索でインデックスが使われ、未設定のカラムにインデックスを追加すればさらに効果が出るのではないか。
方法： like_columnに対してはvarchar_pattern_ops を設定したインデックスを作成し、fk_column にも新たにインデックスを張った。
結果： どちらもインデックススキャンされるようになり、クエリの実行時間がさらに改善しました。
考察： ロケール設定の影響を受けないバイナリ順のインデックスを強制することで、前方一致検索が高速化されました。これでクエリの改善はやり尽くした状態となりました。

4. 実装

チューニングしたクエリが発行されるよう、GORM を用いて実装しました。2 つに分けたクエリで求めた個数の和を返すように処理を変更しています。

5. リリース・動作確認

本番環境へリリースし、実際の広告配信情報取得のレイテンシを確認しました。

結果、当初 1300 ms ほどかかっていたエンドポイント自体のレイテンシが 300 ms まで短縮されました。

成果

今回のタスクを通して、大幅な高速化を実現しました。

項目	改修前	改修後
主要クエリ	1000 ms	50 ms
API 全体	1300 ms	300 ms

1番重たかったクエリは約 50 ms まで改善しました。しかし、2番目に重たいクエリやクエリ以外の箇所にもまだ改善の余地があります。

インターンでの学び

今回のインターンを通じて、単なる開発力の向上以上に、エンジニアとして働くうえでの「思考のフレームワーク」を多く学びました。

1. 「推測するな、計測せよ」の実践

パフォーマンス改善において、勘に頼ってコードを修正することの危うさを学びました。

可視化の重要性:

Datadog APM でエンドポイント全体のボトルネックを可視化し、さらに EXPLAIN ANALYZE で DB 内部の実行計画を読み解くことで、初めて「どこを直すべきか」の確信が持てました。

理論と現実の乖離:

「自己結合なら速くなるはず」という仮説が外れた際も、実行計画を比較することで「なぜ変わらなかったのか（計算量が同じだったから）」を論理的に納得できました。

2. DB 内部構造とインデックスへの深い洞察

SQL を書くだけでなく、RDBMS がどのようにデータを探索するかを意識するようになりました。

演算子クラスの影響:

LIKE 前方一致でインデックスを効かせるための varchar_pattern_ops の存在など、データ型やロケール設定がパフォーマンスに与える影響を学びました。

オプティマイザとの対話:

複雑な OR 条件が実行計画をいかに狂わせるか、そしてクエリを分割するという「急がば回れ」の手法が、結果としてオプティマイザの最適化を助け、劇的な高速化に繋がることを体感しました。

3. 「自走」するためのコミュニケーション術

メンターの方とのやり取りの中で、詰まった時の相談の質が変わりました。

「答え」ではなく「手段」を問う:

最初は解決策を聞いてしまいがちでしたが、次第に「どのようなツールを使えば解析できるか」「どのログを見れば原因に近づけるか」といった、解決へのプロセスを質問するように意識しました。

エンジニアとしての責任感:

タスクの見積もりが甘く、遅れが出そうになった際、早めに状況を共有し、どの範囲までを優先して完遂させるべきか調整する「リスク管理」の重要性を学びました。

まとめとおわりに

今回のインターンでは、クエリチューニングという難易度の高いタスクに挑戦し、バックエンド開発の奥深さを知ることができました。

メンターの屋比久さんをはじめ、温かく迎え入れてくださったマーケティングテクノロジー部の皆さんに心から感謝いたします。この経験を糧に、これからも精進し続けたいと思います！

1ヶ月間ありがとうございました！

DMM Developers Blog

APIレイテンシを1300msから300msへ。重いクエリを1/20に高速化できた話