「3月以前の集計はすぐに終わるのに、4月の集計がいつまで経っても終わらない。」
feature部会計決済グループのこじこじです。
あれは今年の4月上旬の出来事でした。
hacomonoのとある月次集計機能を担当している私のもとに上記の問い合わせが飛び込んで来ました。
結論、mysqlのクエリオプティマイザの誤判断で、使われるべきindexが使われず、意図せずテーブルのフルスキャンが発生していたという事象でした。
最終的にはUSE INDEXを指定するようにクエリを修正し、意図したインデックスが強制的に使われるように修正をリリースしました。
発生した現象
この問い合わせが来たのは4月の上旬なので、4月の集計対象になるデータはまだ少ないはずで
実際にデータを見てみると、確かに4月に集計対象になるデータは少なめでした。
| 年月 | 取得する件数 | 実行速度 | |
|---|---|---|---|
| 2025年3月 | 3,000件程度 | ← 過去年月のデータ量は出揃っていて大体毎月3,000件程度 | 0.2秒くらい |
| 2025年4月 | 800件程度 | ← まだ4月上旬なので、4月分のデータは出揃っておらず件数は少ない | *30分くらいかかっても終わらない |
*実際にはタイムアウトが設定されているので、ユーザーは集計結果を取得できていません。
直感的にはデータ量が少なければ、取得にかかる時間も短くなりそうなものですが、
月をまたぐとデータ量が4分の1以下なのに、実行時間は150倍以上遅くなるという不可解な現象に遭遇しました。
原因調査
というわけで、調査に入っていきます。
実際のデータやクエリは張れないのでアレですが、インデックスが使われていないような挙動に思われたので、
とりあえず実際に実行されたクエリをformat=jsonでexplainしてみました。
クエリ自体は多くのテーブルとジョインしたり、サブクエリがあったり300行を超える複雑なもので、
案の定、あるテーブルをJOINする際に3月以前の集計ではindexが使われているが、4月の集計ではindexが使われず、テーブルのフルスキャンが行われていました。
一方でクエリコストは圧倒的に3月以前の集計のほうが高いと言う結果でした。
| 年月 | インデックス | クエリコスト |
|---|---|---|
| 2025年3月 (速い方) | ✅ 日付インデックス使用 | 200,000 くらい |
| 2025年4月 (遅い方) | ❌ フルテーブルスキャン | 15,000 くらい |
一体なぜなのか
AI時代なので、とりあえずcursorにexpainの結果を読ませて原因を調べてもらいました。
問題の原因
データ量が少なすぎる:
- 202504では対象データが9行しかないため、MySQLのクエリオプティマイザはインデックスを使用するよりも全件スキャンの方が効率的だと判断しています
- これは一般的な最適化戦略で、データ量が少ない場合はインデックスのオーバーヘッドを避けるためです
日付範囲の影響:
- 202504のデータはまだ新しいため、データ量が少なく、インデックスの使用が効率的でないと判断されています
- 一方、202503は過去のデータで量が多いため、インデックスを使用する方が効率的だと判断されています
複雑なクエリの中で、あるテーブルをジョインするするときに、
「JOINするデータは9件しかない。それならスキャンしたほうが早いっしょ。」と
クエリオプティマイザが判断してしまった、ということのようです。
しかし、JOINするのは9件だけだが、そのテーブルにはその月の集計には関係のない大量のデータが存在しており、フルスキャンは結果として悪手であったということみたいです。
オプティマイザの誤判断
正直、オプティマイザは非常に賢いので適切にインデックスが定義されていて、使えるインデックスがあれば使ってくれるだろうくらいに思っていました。
そんなことあるのか…と。
今までオプティマイザがどうやって実行計画を立てているのか、なんて気にしたこともなかったのですが、ここに来て調べてみました。
MySQL オプティマイザの仕組み
DeepResearchやNotebookLMを駆使して調べてみました。
わかったような、わからないようなという感じですが、要は
- 複数の実行方法を検討する
- それぞれの「コスト」を見積もる：
- 複数の実行方法を検討する
と言う流れのようです。
コストベース最適化
MySQL 8.0 ではコストベース最適化をなるものが働いているようです。
要はいろんなパターンの操作(インデックスを使ったり使わなかったり)を検討して、一番コストが低いものを採用する仕組みと理解しました。賢すぎる。
MySQL の公式ドキュメント1によると、オプティマイザは以下のコスト要素を考慮します：
- I/O コスト: ディスクからのデータ読み取り
- CPU コスト: レコードの比較・評価処理
- メモリコスト: 一時テーブルの作成・処理
範囲最適化とインデックス選択
範囲最適化（Range Optimization）2では、オプティマイザが以下の手順でインデックスを選択します：
- 統計情報の収集: テーブルの行数、インデックスの分散度を調査
- コスト推定: 各アクセス方法のコストを計算
- 最適プランの選択: 最低コストの実行計画を採用
問題は、この推定が条件に一致する行数のみに基づいていることです。テーブル全体のサイズは考慮されますが、実際のスキャンコストの見積もりが不正確になる場合があります。
なぜこのような現象が起きるのか？
コスト推定の仕組み
オプティマイザのコスト推定には以下の要素が影響します：
- テーブル統計情報:
ANALYZE TABLEで更新される情報 - インデックス統計: 各インデックスの選択性やカーディナリティ
- ヒストグラム情報: MySQL 8.0 で追加された分散度情報
判断ミスが起こる理由
統計情報は information_schema テーブルや SHOW INDEX コマンドで確認でき、これらの情報がオプティマイザの判断材料となります。
オプティマイザが以下のような計算をしていると推測されます：
- インデックススキャンコスト = (インデックス読み取り) + (データページ読み取り)
- フルスキャンコスト = (全データページ読み取り) × (条件一致率)
少量データでは「条件一致率が低い」と判断し、フルスキャンを選択してしまうことが起きるようです。
教訓
1. オプティマイザは完璧ではない
- 統計情報やデータ分布によって判断ミスが発生
- 「賢い」判断が必ずしも最適とは限らない
2. データ量の変化がパフォーマンスに与える影響
- 少量データ ≠ 高速処理
- コスト推定の仕組みを理解することが重要
といったところが学びとして得られました。
オプティマイザが何をしているのかについてあまりに無知だった、
というか興味の外側だったので、そういうこともあるので気をつけようと思いました。
今回は、
- インデックス追加によるメタデータロックを嫌った
- 当該のINDEXの使用を強制することによるデメリットがなかった
のでUSE INDEXで対応しましたが、そもそもインデックス設計を見直すとか状況に応じたベターな対応はあると思うので適宜検討できればと思います。
最後に
現象が再現できるサンプルでも用意できればよかったのですが、もとのクエリが複雑すぎることもあり、再現できませんでした。面目無いです。
同じような事象にぶつかったときにこういうものもあるのかという参考にしていただければ 🙏
参考文献
関連リソース
- MySQL 8.0 Reference Manual - EXPLAIN Statement
- MySQL 8.0 Reference Manual - Index Hints
- MySQL 8.0 Reference Manual - ANALYZE TABLE Statement
SNSアカウントなど
- github: https://github.com/kojikono
- X: https://x.com/koji_kono
- instagram(趣味関係): https://www.instagram.com/koji_kono
💁 関連記事
