カバリングインデックス

索引に当たっているのに、まだ遅い

一覧画面が遅いので実行計画を見たら、索引はちゃんと使われていました。全件走査でもありません。それでも 3 秒かかります。

原因は、索引を引いたあとにあります。検索用の索引の葉に入っているのは主キーの値までで、画面に出したい氏名や金額は入っていません。だから DB は、索引が返した主キーを 1 件ずつ持って、本体の側をもう一度引きに行きます。この往復は 1 件につき 1 回です。該当が 1 万件なら 1 万回です。

1 件だけ返すクエリなら誤差ですが、まとめて返すクエリでは、この往復が支配的になります。索引が効いているのに遅いときは、たいていここです。

実行計画に索引の名前が出ていることは、速いことの証明にはなりません。索引でどこまで絞れたか、そのあと本体を何回引いたかまで見て、初めて遅さの原因が分かります。

欲しい列を、索引の中に入れてしまう

往復が起きるのは、欲しいものが索引の中に無いからです。ならば入れてしまえばよい、というのがカバリングインデックスです。

そのクエリが必要とする列を全部含んだ索引にしておくと、索引を読んだ時点で答えが揃います。本体を引きに行く必要がなくなり、往復はゼロになります。

CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at, status, total);

この並び順には理由があります。前から順に、等号で絞る列、並べ替えに使う列、最後に取り出したいだけの列、という順番です。先頭が絞り込みに効き、途中が並べ替えの手間を省き、後ろが往復を消します。取り出すだけの列を前に置くと、絞り込みも並べ替えも効かなくなります。

MySQL の InnoDB では、どの索引の葉にも主キーの値が必ず入っています。だから主キーだけを取り出すクエリは、何もしなくても最初からこの状態です。PostgreSQL には、取り出す専用の列を並び順に加えずに索引へ含める書き方があり、索引を太らせずに同じ効果を得られます。

全部入れれば速い、ではない

では欲しい列を片っ端から入れればよいかというと、そうはいきません。

含めた列は、そのまま索引の大きさになります。長い説明文の列を 1 つ入れるだけで、索引が本体と同じくらい重くなることがあります。重い索引はメモリを占め、他の索引を追い出します。

書き込みにも跳ね返ります。含めた列のどれか 1 つが更新されるたびに、この索引も直します。取り出したいだけで入れた列が、更新の多い列だった場合、往復を消して得た分を書き込みで払い直すことになります。

新しく作る前に、すでにある索引を見ます。同じ列から始まる索引がすでにあるなら、そこへ列を足すほうが安く済みます。似た索引を 2 本並べると、読むときはどちらか一方しか使われないのに、書き込みでは両方が更新されます。

そこで、含めるのは短くて更新の少ない列に絞ります。判断の目安は、そのクエリが 1 回に何件返すかです。数件しか返さないなら往復も数回なので、索引を太らせる価値はありません。数千件を返す一覧や、集計のために大量に読む処理でだけ効きます。件数が増えるほど効き方も大きくなるので、いま遅いところだけでなく、これから件数が伸びる場所を先に見ておくと無駄が出ません。狙いどおり効いているかは、実行計画に「索引だけで完結した」旨が出るかどうかで確かめられます。