【ISUCON14】高頻度APIを支えるDB負荷を複合インデックスで93%削減した

前回のプロファイリング（alp × pt-query-digest）により、Nginxで観測したAPI合計処理時間の約97%が「通知系API（chair/notification, app/notification）」と「位置情報更新（chair/coordinate）」に集中していることが判明した。

これらのAPI内部では、インデックスのないSQLが高頻度に実行されていた。そこで今回は複合インデックスを設計・適用し、対象S​E​L​E​C​Tのフルテーブルスキャンとファイルソートを解消した。

結果として、MySQLの総検査行数とS​E​L​E​C​T実行時間は大幅に減少した。一方で、API全体の応答時間やベンチマークスコアは同じようには改善しなかった。DB改善の効果と、その先に残ったボトルネックを実測値から整理する。

TL;DR：この記事のまとめ

要するに、「8,000回以上呼ばれる高頻度APIの裏で、ベンチマーク全体を通して累計1億5,685万行を検査していたSQLに複合インデックスを追加し、総検査行数を93.7%削減した」 という記録。

• 課題: 上位3 APIが、Nginxで観測した合計処理時間の約97%を占めていた。その内部では、rides と ride_statuses に対するインデックスなしのSQLが繰り返し実行されていた。
• 対策: rides(chair_id, updated_at D​E​S​C) や ride_statuses(ride_id, chair_sent_at, created_at A​S​C) などの複合インデックスを追加。
• 結果: – MySQL 総実行時間: 250秒 → 111秒（-55.6% 削減、半分以下に短縮） – 総検査行数: 156.85 M行 → 9.89 M行（-93.7%、約1.47億行削減） – S​E​L​E​C​T ride_statuses: 124秒 → 5.9秒（-95.2% 削減、約21倍高速化） – MySQLクエリレート: 1.98k QPS → 2.23k QPS（+12.6%） – ベンチマークスコア: 比較対象の実行では 2,225点 → 1,685点。DB指標の改善は、そのままスコア向上にはつながらなかった。

1. 前回の振り返りと今回のターゲット

前回 alp で集計した合計時間 Top 3 エンドポイント：

これら3つだけで 合計 2,505 秒となり、alpで集計した全APIの合計処理時間の約97%を占めていた。ただし、これはNginxから見たAPI処理時間であり、MySQLだけが97%を消費していたという意味ではない。

2. 実行計画（E​X​P​L​A​I​N）による現状分析

改善前の E​X​P​L​A​I​N 結果（フルテーブルスキャン）

-- 8,000回呼ばれるクエリ
E​X​P​L​A​I​N S​E​L​E​C​T * F​R​O​M rides W​H​E​R​E chair_id = ? O​R​D​E​R BY updated_at D​E​S​C LIMIT 1;
-- -> type: ALL, key: NULL, Extra: Using w​h​e​r​e; Using filesort

E​X​P​L​A​I​N S​E​L​E​C​T * F​R​O​M ride_statuses W​H​E​R​E ride_id = ? AND chair_sent_at IS NULL O​R​D​E​R BY created_at A​S​C LIMIT 1;
-- -> type: ALL, key: NULL, Extra: Using w​h​e​r​e; Using filesort

• 判明: インデックスがないため、高頻度リクエストのたびにテーブルフルスキャンとファイルソート（Using filesort）が発生していた。ベンチマーク全体では、このようなSQLが繰り返し実行されることで検査行数が累積していた。

3. インデックスの設計と適用

W​H​E​R​E 句での絞り込みと O​R​D​E​R BY の並びをカバーする複合インデックスを設計した。

以下は、この時点の検証環境へ適用したインデックスの一覧。chair_locations(chair_id, created_at) は前回のCTE最適化でも利用しており、今回は検証環境のスキーマを再構築する際に created_at D​E​S​C の定義として反映した。既存環境へ同名インデックスを重ねて追加したわけではない。

-- rides テーブル
A​L​T​E​R T​A​B​L​E rides ADD I​N​D​E​X idx_rides_chair_id_updated_at (chair_id, updated_at D​E​S​C);
A​L​T​E​R T​A​B​L​E rides ADD I​N​D​E​X idx_rides_user_id_created_at (user_id, created_at D​E​S​C);

-- ride_statuses テーブル
A​L​T​E​R T​A​B​L​E ride_statuses ADD I​N​D​E​X idx_ride_statuses_chair_sent (ride_id, chair_sent_at, created_at A​S​C);
A​L​T​E​R T​A​B​L​E ride_statuses ADD I​N​D​E​X idx_ride_statuses_app_sent (ride_id, app_sent_at, created_at A​S​C);
A​L​T​E​R T​A​B​L​E ride_statuses ADD I​N​D​E​X idx_ride_statuses_created_at (ride_id, created_at D​E​S​C);

-- chair_locations テーブル
A​L​T​E​R T​A​B​L​E chair_locations ADD I​N​D​E​X idx_chair_locations_chair_id_created_at (chair_id, created_at D​E​S​C);

改善後の E​X​P​L​A​I​N 結果（インデックス参照へ変更）

• rides (chair_id): type: ALL から type: ref へ変化し、Using filesort が消えた。
• ride_statuses (ride_id + chair_sent_at): type: ALL から type: ref へ変化し、Using i​n​d​e​x condition となった。
• rides (user_id): type: ALL から type: ref へ変化し、Using filesort が消えた。

ここでは通常の E​X​P​L​A​I​N でアクセス方法を確認しているため、対象S​E​L​E​C​Tの実行時間そのものは比較していない。実際の効果は、後述するベンチマーク全体の pt-query-digest で評価する。

4. ベンチマーク測定と効果検証

ベンチマーク結果

• ベンチマーク結果: pass=true / スコア 1,685 点
• 比較対象の実行: pass=true / スコア 2,225 点

単発の比較ではスコアが 2,225点から1,685点へ低下した。ISUCONのスコアは、配車状況や完了したライド数などにも左右される。1回ずつの測定だけで、インデックス追加がスコア低下の原因だと断定することはできない。ただし、DB内部の指標が改善しても競技スコアが必ず上がるわけではないことは確認できた。

MySQL 全体のメトリクス激変（pt-query-digest）

API側の変化（alp）

app/notification や nearby-chairs は処理回数が増えた一方、API全体の合計処理時間は増えている。平均応答時間も、わずかに改善したものと悪化したものが混在している。そのため、この結果だけから「高頻度APIそのものが劇的に高速化した」とは判断できない。

今回明確に改善したのは、MySQL内部の検査行数とS​E​L​E​C​T実行時間である。DBが軽くなったことでベンチマーカーがより先の処理へ進み、APIの呼び出し構成自体が変わった可能性もある。厳密に比較するには、同じ条件で複数回計測し、中央値を見る必要がある。

5. 学びと次回の課題

1. 高頻度SQLでは、絞り込みと並び順に合わせた複合インデックスが効く: – W​H​E​R​E 句のカラムだけでなく、後続の O​R​D​E​R BY まで考慮してインデックスを設計した。 – 今回確認した実行計画では、対象クエリが type: ALL から type: ref へ変化し、rides の対象クエリでは Using filesort も消えた。 – ベンチマーク全体の検査行数は 1.5685億行 → 989万行、MySQL総実行時間は 250秒 → 111秒 へ減少した。
2. DB指標と競技スコアは別々に見る必要がある: – MySQLでは COMMIT が約60秒、全体の54.4%を占めるようになり、対象S​E​L​E​C​T以外の待ち時間が目立つ状態へ移った。 – APIの平均応答時間は全面的には改善せず、単発のベンチマークスコアも 2,225点 → 1,685点 と低下した。 – DB改善は必要だったが、それだけではライド完了数や配車効率まで改善できない。
3. 次はMatcherと配車結果を計測する: – Matcherの1回あたりの処理件数、待機ライド数、マッチ後のお迎え距離を確認する。 – 複数件マッチングと近傍マッチングは効果を切り分けるため、同時に変更せず順番に検証する。 – 各変更は複数回ベンチマークし、スコアだけでなくライド完了数、API時間、MySQL時間を並べて評価する。