FOREIGN KEY(外部キー)の罠

データベースのFOREIGN KEY(外部キー)を活用していますでしょうか。テーブル間のデータ整合性を保証するために便利な機能ですが、「遅くなるのでFOREIGN KEYは使わずにソフトウェア側で整合性を保証する。」という選択をしていた時代もありましたね。ハードウェアの性能向上、データベース製品の成熟、ソフトウェアの複雑化により、現在ではテーブル間のデータ整合性はFOREIGN KEYで保証するケースも多いのではないでしょうか。

PRIMARY KEY（主キー）

今回の記事タイトルは「FOREIGN KEY(外部キー)の罠」にしてみたわけですが、この罠について語る上でPRIMARY KEY（主キー）の存在は外せません。
PRIMARY KEYでは、以下の2つの制約が適用されます。

1. 各レコードの値に重複がないことの保証（UNIQUE KEY制約）

2. NULL値が含まれないことの保証（NOT NULL制約）

上記の制約を実現するために、自動的にINDEXが作成されます。
PRIMARY KEYにはINDEXが必ずあるんです。

FOREIGN KEY

一方のFOREIGN KEYですが、制約は1つだけです。

1. FOREIGN KEYで指定した参照先に存在しない値がないことの保証（FOREIGN KEY制約）

PRIMARY KEYの場合はINDEXが作成されていたわけですが、FOREIGN KEYの場合はINDEXが必ず作成されるわけではありません。使用しているデータベースによって挙動が変わってきます。ここが罠のポイントになります。

PRIMARY KEYとFOREIGN KEYは両方○○KEYとなっており、似た感じなのですが、FOREIGN KEYにINDEXが必ずあるとは限らないのです。MySQLではIDNEXが作成されるのですが、PostgreSQLでは作成されません。

以下のER図の場合は、PKにはINDEXが作成され、FK1はデータベースによって変わってきます。

ER図

FK1にINDEXがない場合、Masterテーブルからのレコード削除時に、Transaction1、Transaction2のテーブルに対してフルスキャンが実行されます。Masterテーブルの件数が少なくても、Transactionテーブルにレコードが多いと、Masterテーブルからのレコード削除が遅くなってしまいます。

あとがき

FOREIGN KEYで自動的にINDEXが作成されない件について、ググるといくつもの記事がヒットします。ではなぜわざわざ記事にしているのか？という点なのですが、最初から知っているかどうかで明暗が大きく分かれる知識だと感じたからです。FOREIGN KEYにはINDEXが作成されている。という思い込みがあると、性能劣化の要因として浮上せずに放置されてしまうこともあるのではないでしょうか。

思い込みによって要因への到達が遅れるケースがありますので、初心に返って一つ一つ丁寧に確認することも忘れずにいたいです。