ORDER BYの仕組み
全てのデータを取り出してから、ORDER BYで並び替えて、最後にLIMITで必要なデータを取り出します。
データを取り出してからバラバラの大量のデータを並び替えるため基本的にソートはかなり高コストな処理になります。実際のデータが大きくなればなるほどコストは増大していきます。
対処法
事前にWHERE句を使って対象を絞り込む
事前に絞り込めばORDER BY句のパフォーマンスはかなり上がります。さらにWHERE句でインデックスを使えればさらにパフォーマンスが上がるので尚良いです。（「WHERE句狙いのインデックス」と言います。）
「ORDER BY句狙いのインデックス」を使う。
WHERE句にインデックスを適用することが必ずしも最適ではないです。（カーディナリティが低く、データが偏っている場合などはWHERE句にインデックスを使っても効果は薄い。）
PostgreSQLとMySQLの標準的なインデックスの実装であるBTree Indexはデータをソート済みの状態で保存している。対象のソート結果とインデックスが同じであればインデックスから値を取り出せば良いという考え方です。（ソートの処理をすることなく、インデックスから該当データを順番に取り出すだけで済む）
なので、WHERE句ではなく、ORDER BY句に対してインデックスを使うことで効果が出る場合があります。
ORDER BY句インデックスの強み
- ソート処理が不要になる。
- 評価数がLIMITの件数に達した時点で結果を返せる。
どちらの対策を取れば良いかの判断材料
「WHERE句狙いのインデックス」が良いか「ORDER BY句狙いのインデックス」が良いかは、中に入っているデータによって変わります。そのためには「実行計画」をよく見ることが重要です。定期的に実行計画を叩いて最適な設計手法を選択できるように精進しましょう。
オプティマイザは今の情報しかわからず未来にどんなデータが入っているかはわからないので、DB専門担当ではなくてもバックエンドエンジニアは日々実行計画を監視していくことが重要です。
最近は、MySQL Workbenchや、pgAdmin4など実行計画をグラフィカルに表示してくれるツールも充実してきています。既存のアプリケーションでも新たな発見があってSQLの見直しで大幅にパフォーマンスが向上するということもあるかもしれません。（特に今回ご紹介したソート処理は大きくパフォーマンスを改善できます。）
この記事へのコメントはありません。