こんにちは！株式会社うるる新卒のゆうだいです！
今日は、業務を進める上で遭遇した「インデックスを貼ったはずなのに速度が改善されない！？」という問題について自分の備忘録として残していこうと思います！
注意
実際のテーブル名やデータそのものではなく、分かりやすいように別の例に置き換えています。タスク管理システムをイメージしながら読んでいただければ幸いです。
結論
時間がない方の為に最初に結論からです。
ページの表示速度の為にインデックスを貼ったのに速度が改善されない！
→ 調べたらMySQLのオプティマイザとやらがインデックスを使わずにデータ取得した方が早くね速くね？！と勘違いをして遅かったみたい！
→ オプティマイザに情報を与えて間違えないようにするため、別の場所にもインデックスを貼って解決！ 以上！
です、ではここから詳しいところに入っていきます。
何が起きたか
ここからが本編です。実際に何が起きて、どう調べていったかを順番に書いていきます。
対象は重いので直してほしいと言われた「完了したタスクに紐づく未読コメント一覧」を表示する画面です。
タスクとコメントは1対多の関係で、コメントはtask_idで親のタスクにひもづいています。コメントには既読/未読を表すis_readというフラグもあって、今回の画面はこれがfalseのものだけを表示する作りになっていました。
データ数でいうとコメントは30万件を超えるテーブルとなっていて、クエリを見る限り複雑でないクエリではありましたが、時間がかかるという状況でした。
調査をすると、未読コメントを素早く絞り込むためのインデックスが貼られていないことがわかったので、(is_read, created_at)という複合インデックスを追加し、ローカルでデータ数をある程度再現して検証をする限りうまく動いているというところまでいきました。
ところが、本番相当のデータ量・データ分布を用意したステージング環境にリリースしてみると、画面の重さはまったく変わっていませんでした。
疑ったけど違ったもの
以下疑ったけど違ったもの
- インデックスがうまく作られていないのではないか？
→ ちゃんとある - 未読コメントの件数が多すぎて、絞り込みがうまく効いていなかったりする？
→ そんなこともなく、未読コメント自体は全体からすればごく一部
インデックスを貼ったはずなのに、ローカルでは改善したはずなのに、この段階でタスクがまた振り出しに戻りました。
EXPLAIN ANALYZEで見えたもの
埒が明かないのでClaude君！助けて！とすると「ステージング環境でEXPLAIN ANALYZEを試しなさい」と言われたので、実行をしてみました。
EXPLAIN ANALYZEはSQLクエリを実行した上で、実行計画（どうやってデータを取りに行くか）と、実際に実行した結果の時間・行数の両方を見せてくれるコマンドです。
結果はこんな感じでした（わかりやすいようにテーブル名・列名は置き換えています）。
-> Nested loop inner join (cost=2702 rows=44.6) (actual time=0.31..3410 rows=217 loops=1)
-> Filter: ((tasks.status = 4) and (tasks.deleted_at is null)) (cost=1368 rows=128) (actual time=0.05..18.2 rows=12459 loops=1)
-> Table scan on tasks (cost=1290 rows=12789) (actual time=0.04..14.9 rows=12789 loops=1)
-> Filter: (comments.is_read = false) (cost=7.1 rows=0.348) (actual time=0.27..0.27 rows=0.0174 loops=12459)
-> Index lookup on comments using task_id_foreign (task_id = tasks.id) (cost=3.6 rows=34.8) (actual time=0.06..0.25 rows=24.1 loops=12459)
ここで目を引くのが、Filterの行にあるrows=128（見積もり）とactual rows=12459（実測）の差です。
MySQLは「完了ステータスのタスクは128件くらいだろう」と見積もって、tasksテーブルから読み始める計画を立てていました。でも実際には12,459件もあったんです。
見積もりが100倍近く外れていたことになります。
MySQLが実行計画を選ぶ仕組み
ここで、データ取得をするとき、どういう流れでデータが取得されているのかという話をしていきます。
SQLを実行するとき、MySQLは「こういうデータの取り方をすると、これくらいコストがかかりそうだ」という候補をいくつか作って、その中から一番安そうなものを選んで実行しています。これが実行計画です。この「候補を作ってコストを見積もって、一番安いものを選ぶ」役割を担っているのがオプティマイザです。
今回のクエリはtasksとcommentsをJOINしています。JOINするとき、オプティマイザは「どっちのテーブルを先に読んで、そこから相手のテーブルを都度探しにいくか」を決める必要があります。先に読む方を駆動表と呼びます。
このとき駆動表に選ばれるのは、基本的には「絞り込んだあとに残る行数が少ないと見積もられた方」です（正確には結合順序の候補ごとに総コストを比較して選んでいて、行数の見積もりはその中でも大きな要素、というのが厳密な言い方です）。理由は単純で、駆動表を読んで残った行1件ごとに、相手のテーブルへ探しにいく処理が1回発生するからです。残る行が少ないほど、相手のテーブルを探しにいく回数も減って、安く済むという理屈です。
今回のケースで言うと、tasks.status = 4という条件に該当する行が「128件くらいだろう」と見積もられたので、MySQLはtasksを駆動表にする計画を立てていました。128件なら、comments側への検索も128回で済むので、確かに安上がりに見えます。
でも実際には12,459件該当していました。つまりcommentsへの検索が、本当は12,459回発生していたことになります。
なぜこの見積もりのズレがそんなに効くのか
「128件と12,459件、確かに100倍近い差はあるけど、12,459件くらいなら普通にSQLで扱う件数として珍しくないし、そこまで大きな問題にはならないのでは？」と自分は最初はそう思いました。
実際、tasksテーブル自体は全部で1万2千件を少し超える程度しかない小さめのテーブルです。この規模のテーブルを普通にスキャンするだけなら、そこまで重い処理にはなりません。1万件そこそこを対象にした処理が、それ単体で極端に遅くなることはあまり無いはずです。
問題はtasksの件数そのものではなく、「その件数ぶんだけcommentsへの検索が発生する」という掛け算の構造にありました。
tasksを読む処理は、ページを順番に読んでいくだけです。一方comments側は、is_readという列がJOINに使っているインデックス（task_id_foreign）には含まれていないため、候補行1件ごとに「インデックスで場所を特定してから、行本体を取りにいく」という二度引きが発生します。まとめて読めるtasks側のスキャンに比べて、1回あたりのコストがどうしても高くなります。
これが128回で済むか、12,459回発生するかで、comments側の負荷はまったく変わってきます。EXPLAIN ANALYZEの出力にあったrows=34.8は、commentsへの検索1回あたりで平均何行ヒットするかの見積もりです。実測（actual側）を見ると24.1になっていて、見積もりと完全には一致していません。ただしそのズレは1.4倍程度で、tasks側の100倍近いズレとはかなり対照的です。task_id経由の単純なルックアップは、インデックスの統計から比較的当たりやすい見積もりになる、ということです。
実測値の24.1にループ回数の12,459を掛けると、
12,459 × 24.1 ≒ 300,300
となります。commentsテーブル自体が30万件を超える規模なので、これはほぼ全件を、comments側の二度引きというもっともコストの高い方法で読みにいっていたことになります（同じtask_idを重複して読むことはないので、テーブル件数を超えることはありません）。
しかもこの約30万回分の行取得のうち、is_read = falseのFilterで最終的に残るのはたった217件でした。ほとんどを読んだ直後に捨てている計算です。バッファプールに載りきっていなければ、この行取得にはさらにランダムI/Oも乗ってくるので、実測でも3秒を超える時間がかかっていました。
つまり「128件と12,459件の差」がそのまま効いているのではなく、「そのタスク数ぶんだけ発生する、comments側への高コストな行取得の回数」が効きパフォーマンスが落ちていました。見積もりが128件だったからこそ「tasksを起点にしても安い」という判断が成立していたわけで、その前提が崩れた時点で、この計画は根本から間違っていたことになります。
見積もりの元になる統計情報
次にこの「128件」という数字はどうやってでてきたのかを書いていきます。
MySQLも当てずっぽうで数字を出しているわけではなく、列ごとに持っている情報を材料にしています。
オプティマイザが行数を見積もるときに使う材料は、大きく4つに分けられます。
- インデックス統計（カーディナリティ）：インデックスを貼った列に自動で付く、「値が何種類あるか」の推定値。テーブルの約1割が更新されると自動で再計算される
-
インデックスダイブ：
status = 4のような定数条件に対して、計画を立てる時点でB-treeの該当範囲を覗いて件数を見積もる仕組み。インデックスのある列でだけ働く -
ヒストグラム：
ANALYZE TABLE ... UPDATE HISTOGRAMで明示的に作る分布のメモ。インデックスの無い列にも統計を与えられるが、（MySQL 8.0時点では）自動では更新されない - 決め打ちの既定値：上記のどれも持たない列に使われる最後の手段。等値条件は一律で約10%の行がヒットするとみなし、ANDで条件が重なるとその割合が乗算される
今回のtasks.statusは、このどれも持っていませんでした。インデックスも貼られていなければ、ヒストグラムも作られていない。だから最後の「決め打ちの既定値」が使われていたことになります。
status = 4という等値条件でまず約10%、そこにdeleted_at IS NULLという条件がANDで重なって、さらにその約10%。掛け合わせると約1%です。tasksの全件数（12,789件）の約1%が128件で、まさにEXPLAIN ANALYZEに出ていた見積もり値と一致します。
実態はというと、完了ステータスのタスクがほとんどを占めていたので、本当は約98%が該当していました。「約1%だろう」という決め打ちと、「実際は約98%」という実態の差が、そのまま100倍近い見積もりのズレになっていたわけです。
なぜローカルの検証をすり抜けたのか
最初に複合インデックスを追加したとき、ローカル環境でもそれなりに検証はしていました。commentsの件数（30万件超）はステージング環境に合わせていましたが、tasks.statusの分布は全く違うものとして構築がされてしまっていました。
実際にはローカルのテストデータでは、完了ステータスのタスクは全体の一部程度にとどまり、一方ステージング環境では、ほとんどのタスクが完了ステータスでした。
一見するとこの分布差が再現漏れの原因に見えますが、実はそうではありません。悪い方の計画（tasks駆動）の見積もりコストのうち、status = 4という条件の該当率（約1%）は決め打ちの既定値に由来する数字なので、tasks.statusの実際の分布とは無関係にどの環境でも同じです。tasksの総件数を揃えていれば、この該当率にもとづくコスト全体もほぼ同じ値になります。計画の選択を左右していたのは、むしろ良い方の計画（複合インデックスを使うcomments駆動）の見積もりコストの方でした。こちらはcomments.is_readに対する統計にもとづいて計算されるため、環境ごとに違う値になり得ます。
ステージング環境では、この良い方の計画の見積もりコストが悪い方のほぼ一律のコストを上回っていたため、比較の結果悪い方が選ばれていました。一方ローカル環境では、同じ良い方の計画の見積もりコストがこれを下回っており、比較の結果良い方が選ばれていました。複合インデックスを追加した直後にローカルで体感できるくらい速くなったのは、まさにこの「良い方の計画が選ばれていた」ことが効いた結果です。
ちなみに、選ばれなかった側の計画の見積もりコストは、FORCE INDEXでそのインデックスの使用を強制したEXPLAINで確認できます。EXPLAINだけなら実行はされないので、本番相当の環境でも安全に試せる小技です。
厄介なのは、commentsの件数や大まかな未読の割合を本番相当に合わせたつもりでも、この良い方の計画の見積もりコスト自体は再現できていなかった、という点です。統計情報はサンプリングにもとづく推定値なので、行数や割合といった大まかな指標を揃えたからといって、実際に計算される見積もり値まで一致するとは限りません。
行数や大まかな分布を揃えても、計画の「選択」そのものの再現には限界がある、というのがここでの反省点でした。本来であれば、リリース後に対象環境でEXPLAIN ANALYZEを取り直して、狙った計画が実際に選ばれていることを確認する工程を、最初から検証計画に含めておくべきでした。
対応の選択肢
tasks.statusに統計情報を持たせる方法は、ひとつではありません。検討した選択肢を整理してみます。
| 案 | 内容 | 長所 | 短所 |
|---|---|---|---|
| ヒストグラム | ANALYZE TABLE ... UPDATE HISTOGRAM |
デプロイ不要ですぐ効く | （MySQL 8.0時点では）自動更新されず、環境ごとに手動で反映する必要がある |
statusへのインデックス |
マイグレーション1本 | 統計が自動更新される | 書き込みコストがわずかに増える |
FORCE INDEX |
クエリで使うインデックスを強制指定する | 少なくともそのインデックスは使われる状態になる | あくまでアクセス方法を強制するだけで、駆動表（結合順序）までは固定されない。ベンダー依存のSQLがアプリのコードに入り込む |
STRAIGHT_JOIN |
テーブルの結合順序を書いた順に固定する | 駆動表そのものを直接指定できる | 同上、ベンダー依存のSQLがコードに入り込む |
今回は急ぎの対応ではなかったので、「今すぐ効くけど手動更新が要る」ヒストグラムより、「デプロイは要るけど自動更新される」インデックス追加を選びました。
効果の確認
インデックス追加後、ステージング環境で改めてEXPLAIN ANALYZEを実行しました。今度はこんな計画になりました。
-> Nested loop inner join (cost=4890 rows=291) (actual time=0.09..156 rows=217 loops=1)
-> Index lookup on comments using idx_comments_is_read_created_at (is_read=false) (cost=1050 rows=2995) (actual time=0.05..38.1 rows=2987 loops=1)
-> Filter: ((tasks.status = 4) and (tasks.deleted_at is null)) (cost=0.25 rows=0.0974) (actual time=0.035..0.035 rows=0.0727 loops=2987)
-> Single-row index lookup on tasks using PRIMARY (id = comments.task_id) (cost=0.25 rows=1) (actual time=0.032..0.033 rows=1 loops=2987)
今度はcomments側が駆動表になっています。複合インデックスで未読コメントを2,987件まで一気に絞り込み、その1件ごとにtasksを主キーで引く、という形です。主キーでの検索は常に1件しかヒットしないので、コストの低いアクセス方法です。
行取得の回数で比べると、Beforeが約30万回（comments側の二度引き）だったのに対し、Afterは複合インデックス経由の2,987件＋tasksへの主キー引き2,987回で、合計6,000回に満たない程度まで減っています。実行時間も、Beforeのactual timeが最大3.4秒程度だったのに対し、Afterは最大0.16秒程度まで縮んでいます。
ちなみにtasks側のFilterに出ているrows=0.0974という見積もりは、statusにインデックスを追加したことでstatus = 4という定数条件に対してインデックスダイブが働くようになり、実態に近い約97.4%という見積もりが出るようになった一方、deleted_at側は相変わらず既定値の10%のままなので、0.974 × 0.1 ≒ 0.0974という計算になっています。統計を与えたからといって見積もりが完全に実態と一致したわけではありませんが、tasks駆動の計画のコスト見積もりを押し上げるには十分で、比較の結果を逆転させることができました。
実際にアプリが投げているクエリを流してみても応答は一瞬で返ってくるようになり、重かった一覧ページの表示もはっきり速くなったことを確認できました。
まとめ
さて、では今回は「インデックスを貼ったはずなのに速度が改善されない！？」という問題についてまとめました。
個人的にはこのようなアウトプットを残していくのは好きだなと感じているので、また学びを得るたびに何かしらの形でアウトプットを行っていきたいなと考えています。
この記事を書くにあたって基礎的な部分までしかDBについて知らないなと思い調査をしたり、chatGPTと壁打ちを行ったりしており、もう少しオプティマイザについて深堀りたいなという気持ちもあるので、近いうちにその記事も出したいですね！
では最後に今回得られた教訓だけ残し締めたいと思います！
ここまで読んでいただきありがとうございました！
得られた教訓
- インデックスは、貼れば使われるというものではない。使うかどうかはオプティマイザが統計情報にもとづいて決める
- 性能検証では、行数だけでなくデータの分布も本番相当に揃える必要がある。それでも計画の「選択」そのものの再現には限界があるので、リリース後に対象環境で
EXPLAIN ANALYZEを取り直して確認する工程を、最初から検証計画に含めておきたい - 見積もりと実測の乖離（
EXPLAIN ANALYZEのrows=）が、統計情報の問題を見つける最短の手がかりになる