インデックスチューニング

貼ったのに、速くならない

遅い一覧画面を見つけ、WHERE に出てくる列に索引を追加した。デプロイして測り直したら、ほとんど変わっていない。よくある話です。

まず確かめるのは、貼った索引が使われているかどうかです。EXPLAIN の key 欄に自分が作った索引名が出ているか、PostgreSQL なら Index Scan になっているか。名前が出ていないなら速くならないのは当然で、直すべきは索引ではなく条件の書き方の側です。

索引が効かなくなる書き方

索引に並んでいるのは、列の値そのものです。ですから、条件がその値から離れるほど使えなくなります。

最初の 2 つは、列を関数で包んでいます。索引にあるのは包む前の値なので突き合わせられません。3 つ目は型が違うため、DB が内部で変換を挟み、結果として関数で包んだのと同じことになります。日付は範囲条件へ書き換え、文字と数値は呼び出し側で型を揃えれば、どちらも解消します。

前方一致でない LIKE も同じ理由で効きません。'山田%' は先頭が決まっているので並び順から探せますが、'%山田%' は先頭が分からないので全部見るしかありません。中間一致が業務要件なら、索引の張り方を工夫するのではなく、全文検索の仕組みを別に用意します。

もう 1 つ、書き方ではなくデータの偏りが原因の場合があります。退会済みフラグのように値が 2 種類しかない列に索引を貼っても、半分の行が該当するなら、索引をたどってから本体を引くより全部読んだ方が速い。プランナが索引を無視するのはこの場合で、判断としては正しい動きです。絞り込みに効くのは、該当する行が全体の数パーセント以下に収まる条件だと考えてください。

複数列に貼るときは、順番で結果が変わる

(user_id, created_at) の索引は、user_id で並べ、同じ user_id の中を created_at で並べたものです。順番を入れ替えれば別物になります。

先頭の列が条件に出てこないと、その索引には出番がありません。並べる順は、等号で絞る列を先に、範囲で絞る列や並べ替えに使う列を後に置きます。範囲条件から後ろの列は、並び順の恩恵を受けられないためです。

「ある利用者の注文を新しい順に 20 件」という画面なら、絞り込みに使う user_id が先、並べ替えに使う created_at が後です。この順なら、該当する利用者のかたまりの中がすでに日付順に並んでいるので、端から 20 件取れば終わります。逆順に作ると、日付順に並んだ全体の中から user_id の一致する行を拾い直すことになり、20 件そろえるまでに関係のない行を大量に読みます。

なお索引は書き込みのたびに更新されます。使われていない索引は、遅くする効果しかありません。