外部キーにインデックスは必要か？
はじめに
このトピックで記事を書こうと思ったきっかけは、業務でMySQLを使っていて、個人的にPostgreSQLの勉強をしている時に外部キー制約を持つカラムに対してインデックスを自動で張るかどうかという点で両者に違いがあるのを見つけ、ふと参照されるカラムへのインデックスの必要性ってどのくらいなのか？という疑問が生じたから。で、これについて言及していそうな記事はあまり見つからなかったので、せっかくだし書いてみようと思った。
MySQLは外部キー制約を持つカラムに対し、自動でインデックスを用意する仕様になっており、PostgreSQLではテーブル設計者にその判断を委ねるという形をとっている。果たして外部キー制約のあるカラムにインデックスを用意するかという点で、いいプラクティスはあるか？を自分なりに考えてみたい。
※間違っていたり、こういうケースが考えられるよなどがあればぜひコメントいただきたいです！
そもそもインデックスとは？使い所は？
RDBにおけるインデックスとは、その名の通り対象のレコードがどこに格納されているのか？をデータベースに記憶させておくことで効率的に検索ができるというもの。書籍で言うところの索引と同じ感じ。
インデックスは前述の通り便利なのだが、更新コストに関しても考慮する必要がある。インデックス対象の列が更新、行の追加、削除などの動作毎にインデックスを更新する。単純なテーブルのデータ更新以外に追加の更新コストがかかることになる。なので、検索に使われないインデックスを作成することはディスク容量を無駄にし、処理性能を低下させることにつながる。
インデックスにはいくつか種類があるが、MySQLとPostgreSQLは一般にB-treeインデックスを使う。今回はその前提で他の種類に関しては言及しないでおく。
インデックスのメリデメが分かったところで、使い所に関して話したい。
前述の通りインデックスは検索時に威力を発揮するのでそうするのだが、具体的に有用なタイミングはざっくりと以下である。※Qiitaの記事を参考にさせていただきました。
-
WHERE句でよく使われる列 -
ORDER BY句でよく使われる列 -
JOINの結合条件によく使われる列
また、列に対してもこんな具合だと良いというのもある。
- 列の属性の数が多い時（＝データのばらつきが多い）
- 一意制約がついた列にはインデックスを張りたい（MySQL・PostgreSQLどちらも勝手にやってくれる）
- タイムスタンプで検索する場合などはインデックスが欲しい！
- データの選択率が５~10%以内の時
- 選択率 = 絞り込んだ件数 / 総データ件数
といった具合。
外部キー制約にインデックスは必要か？
それではメイントピックに。
外部キー制約にはON UPDATE・ON DELETE句が用意されている。以降はこの仕様を知っているものとして説明をする。
外部キー制約がついていた場合、参照されるテーブルからの行のDELETEまたは参照される列のUPDATEでは、変更対象の値に一致する行を参照するテーブルをスキャンする必要がある。
そのため、インデックスがあるとその検索がスムーズに行われるであろうことが期待できる。
自身の個人開発では大量のデータを用いてテストすることが難しいため、以下の記事を参考にさせていただく。
記事はPostgreSQLの事例を挙げている。当たり前ではあるが、外部キー制約がついているカラムの更新コストはデータ量が増えると負荷がかかる。そのため、レコードを探すときのヒント（インデックス）がないと、パフォーマンス低下してしまうことが想定される。この事例から外部キー制約をつけたカラムにはインデックスは張っておくと良いことが分かる。
とはいえ、前述のインデックスの使いどころには注意する必要があるし、インデックスの更新コストも別途かかることも考慮に入れる必要があるので、そういったケースが想定される場合は無理にインデックスを作成する必要はない。
結局どうする？
結局どうすんの？という話だが、
基本的には外部キー制約のついたカラムにはインデックスを作成することで性能向上が期待できそう。
が、本当に何も考えずにインデックスを作成することはむしろ更新コストのみをかけてしまう可能性があるので、本当に必要かの考慮をすべきだなと思う。特にPostgreSQLにおいてはDB設計者に委ねられているため、慎重に考えてもいいと思われる。
まとめ
- MySQLでは外部キー制約をつけたカラムにインデックスが貼られる
- PostgreSQLでは明示的に宣言する必要がある
- インデックス作成時の注意点
- 検索が行われない・データが少ない・更新頻度が高いの場合はインデックスの作成を避ける
- 指定したSQL文でインデックスが利用可能か？を検討する（必要があれば実行計画の確認）
- むやみに作らない
- 外部キーの対象カラムにインデックスは必要か？
- 基本的にはインデックスを張ると検索性能は上がりそう
- が、上述のインデックス作成時の注意点を確認したほうが良い
今回の記事はコードも特になく文字ばかりになってしまったので読みにくいかも知れませんが、読んでいただけると幸いです！
参考書籍
参考ページ
参考にすると良さそうな記事
Discussion