SQL を1行変えたら DB の負荷が 1/3 になった話

eye_cache

こんにちは、エンジニアの toki (@tokai235) です。法人向け eGift サービス giftee for Business の開発をしています。普段はバックエンドやインフラのお仕事が多いですが、フロントエンドが好きです(？)。

今回は開発しているプロダクトの中で、パフォーマンスのチューニングをしたのでその話をしようと思います。

何に困ってた？

giftee for Business は、企業様がキャンペーンなどで配布するためのギフトをまとめて購入できるサービスです。まとめて購入した企業様は、エンドユーザーの方にメールなどでギフトを個別に配布することが出来ます。

こうした性質から、ギフトを配布したタイミングでエンドユーザーの方が一斉にもらったギフトを開くことになり、アクセスがスパイクすることが多くなります。このときにアプリケーションはちゃんと通常通りのレスポンスを返していて、ユーザーの方は不自由なく利用できていたのですが、スパイクにより DB の CPU 使用率が高騰するということが起きていました。具体的には平常時は 30% 程度のところ、スパイク時は 70% 程度まで上がっていました。これが以前は1ヶ月に一度などの低頻度だったのですが、giftee for Business のサービス拡大に伴って、ほぼ毎日起きるようになってきたため、対応をすることにしました。

何が原因だった？

ギフトを開いたときに呼ばれる、とある SQL クエリが負荷をかけていました。

このクエリ、クエリタイムは 80ms 程度で特別遅いわけではなく、また必要なインデックスはすでに作成済みされていました。

しかしクエリ内で INDEX FULL SCAN がなされていたため、スパイク時に大量のクエリがリクエストされると負荷が上がる原因になっていました。リクエストされるクエリのスループットは 3k count/sec からスパイク時には 6k count/sec 程度になり、これがそのまま DB の CPU 高騰につながっていました。

どういうクエリ？

ここからが本題です。実際のクエリを簡略化したものが以下になります。

SELECT model_B.id
FROM model_B
WHERE model_B.model_A_id IN (1, 3, 5, ...)
AND model_B.id IN (
  SELECT MAX(model_B.id)
  FROM model_B
  WHERE model_B.available_begin_at <= '2025-04-08 00:00:00'
  AND model_B.available_end_at > '2025-04-08 00:00:00'
  GROUP BY model_B.model_A_id
);

やっていることとしては、親である model_A に紐づき、かつ有効期限内の model_B を1つ取得する、という感じです。ただ注意しないといけないのは、データ構造上 model_A に紐づく有効期限内の model_B が複数存在しうるということです。なのでこのクエリでは、

まずサブクエリで「有効期限内の model_B に対し model_A ごとに id が最大の model_B id」を取得
1 で取得した model_B id と model_A id でフィルターした model_B を取得

のようにしています。

model_A と model_B の関係を図示したのが以下です。

model_relations

このクエリの何が問題？

問題となるのはサブクエリのスキャン範囲が広すぎることです。

より客観的に事実を確認するため、EXPLAIN で実行計画を見てみましょう。

EXPLAIN の出力については MySQL 公式 Doc で詳しく説明してくれているので、知っておくと便利です。また、MySQL 8.0.18 以上であればより詳細な EXPLAIN ANALYZE も使えるので、そちらも試してみるといいでしょう。

id	select_type	table	...	type	...	rows	filtered	Extra
1	PRIMARY	model_B	...	range	...	221	100.00	Using where; Using index
2	SUBQUERY	model_B	...	index	...	104,041	25.00	Using where; Using index

実行計画を見ると、PRIMARY は type: range で index が使われており、rows(スキャンした行数) も抑えられています。一方で SUBQUERY は type: index となっており、これは INDEX FULL SCAN を指します。

なぜこのような結果になるのでしょう？

なぜ available_begin_at, available_end_at でフィルターをかけているのに INDEX FULL SCAN ？

これは where 句で指定した available_begin_at, available_end_at の範囲が広く、絞り込み前後の rows があまり変わらなかった可能性が高いです。in や = といった等価演算子ではなく <, > といった不等価演算子での検索なので、こういったことが起こりやすいです。

なぜサブクエリでは model_A_id の where 句が効かない？

サブクエリとメインクエリがそれぞれ独立して実行されるためです。

MySQL 公式 Doc では以下のような記述があります。

MySQL ははじめてサブクエリー結果を必要としたときに、その結果を一時テーブルに実体化します。あとで結果が必要になったときに、MySQL は再度一時テーブルを参照します。

なお余談ですが、サブクエリとメインクエリが相関しあう、その名の通り相関サブクエリというものもあるのですが、今回のケースは非相関サブクエリと呼ばれるものです。

どうやって解決した？

さきほど説明した通り、問題はサブクエリのスキャンした行数が大きいことでした。なのでなんらかのフィルターをかけてそれを減らしてやれば、問題は解決するはずです。

ここでさきほど見た EXPLAIN の結果を思い出してみてください。同じ model_B へのクエリだったにもかかわらず、PRIMARY クエリの rows はわずか 221 でした。サブクエリの rows の実に 1/470 です。この差は model_A_id の where 句によって発生しているので、つまりこうすればいいことになります。

SELECT model_B.id
FROM model_B
WHERE model_B.model_A_id IN (1, 3, 5, ...)
AND model_B.id IN (
  SELECT MAX(model_B.id)
  FROM model_B
  WHERE model_B.available_begin_at <= '2025-04-08 00:00:00'
  AND model_B.available_end_at > '2025-04-08 00:00:00'
  AND model_B.model_A_id IN (1, 3, 5, ...) -- サブクエリにも model_A_id の where を追加
  GROUP BY model_B.model_A_id
);