データモデル設計ガイド

外部キー・リレーション列のインデックス

FK 列に索引がないと親の DELETE / UPDATE が全件スキャン。MySQL は自動・PostgreSQL は手動、N:M の両方向も要注意。

FK 列の子側インデックスは必須

外部キー制約を付けると、親テーブルの行を DELETE / UPDATE するときに子テーブルの該当行が存在しないかを確認する必要があります。子側の FK 列に索引がないと、この確認が子テーブル全件スキャンになります。

例: orders.user_id に FK があり索引がない状況で、ユーザー 1 件を削除しようとすると:

DELETE FROM users WHERE id = 42;
-- 内部的に実行: SELECT 1 FROM orders WHERE user_id = 42;
-- orders 全件スキャン（数千万行でもフルスキャン）

親テーブルの単純な削除が数秒〜数分で返らない、という事故の典型原因です。FK 列には例外なく索引を貼るのが鉄則。

RDBMS	FK 列の索引	備考
MySQL (InnoDB)	自動作成	FK 作成時に同名の索引がなければ自動で作る
PostgreSQL	手動で作る必要あり	FK 宣言だけでは索引は作られない。明示的に CREATE INDEX
SQL Server	手動で作る必要あり	PostgreSQL と同じ
Oracle	手動で作る必要あり	同上

ON DELETE CASCADE と索引

ON DELETE CASCADE は「親を消すと子も連鎖削除」する便利な機能ですが、内部的には親削除のたびに子を WHERE 句で探して DELETE するため、FK 列の索引必須度がさらに上がります。

索引がないままカスケード削除を実行すると:

親 1 行の削除ごとに子テーブル全件スキャン
親を 100 件まとめて削除 → 子テーブル全件スキャン × 100 回
親が数百万件 × 子が数億件で、削除ジョブが何時間も終わらない典型パターン

CASCADE を使う FK は特に子側の索引を最初から用意し、EXPLAIN で DELETE の実行計画を確認する価値があります。ON DELETE RESTRICT / SET NULL でも同様の事情が発生します。

中間テーブル（N:M）の両方向インデックス

N:M リレーションを表す中間テーブル（交差表）は両方向から検索されます。片方向の索引しかないと、逆方向のクエリが遅くなります。

例: user_roles (user_id, role_id) の中間テーブル。

-- ① user から role を引く（ユーザーのロール一覧）
SELECT role_id FROM user_roles WHERE user_id = 42;

-- ② role から user を引く（ロールを持つユーザー一覧）
SELECT user_id FROM user_roles WHERE role_id = 3;

PK を (user_id, role_id) にすると ① は高速ですが、② は role_id が左端欠けで索引が使えません。対策は 2 つ。

逆向きの索引を追加: CREATE INDEX idx_role_user ON user_roles (role_id, user_id)。2 本持つ形だが、カバリングになるので高速
両方向の UNIQUE 制約でも可: UNIQUE (user_id, role_id) + UNIQUE (role_id, user_id)

中間テーブルは両方向の索引を最初から貼るのが定石。後から追加になるケースが非常に多いので、設計レビューでチェック項目にしましょう。

sql

-- N:M 中間テーブルの定番
CREATE TABLE user_roles (
  user_id BIGINT NOT NULL REFERENCES users(id),
  role_id BIGINT NOT NULL REFERENCES roles(id),
  PRIMARY KEY (user_id, role_id)    -- 順方向
);

-- 必ず逆方向の索引も貼る
CREATE INDEX idx_user_roles_role
  ON user_roles (role_id, user_id); -- 逆方向 + カバリング

「親から子一覧」クエリを支える列順

リレーション列に索引を貼るだけでは足りないケースが多くあります。典型は「親 ID で絞った子を新着順に並べる」パターン。

-- ブログ記事のコメント一覧（新着順）
SELECT * FROM comments
WHERE article_id = 123
ORDER BY created_at DESC
LIMIT 50;

ここで comments.article_id 単独の索引だと、article_id で絞ったあとに取得した全件を再ソートすることになります。50 件程度なら問題ありませんが、1 記事に数千コメントが付く人気記事ではソート処理が遅延の原因になります。

正解は (article_id, created_at DESC) の複合索引:

CREATE INDEX idx_comments_article_recency
  ON comments (article_id, created_at DESC);

こうすれば索引自体が「記事ごとに新着順」にソート済みなので、先頭 50 件を読むだけで LIMIT が完結します。FK 索引は単列で終わらせず、その FK と一緒に使われる絞り込み・ソート列を含めた複合索引で設計するのが実務の定石です。

設計レビュー — FK 索引チェックリスト

FK 周りの索引は「忘れる」ことによる事故が多い領域です。設計レビューでは次を機械的に確認しましょう。

[ ] 全 FK 列に索引があるか（PostgreSQL / SQL Server / Oracle は手動）
[ ] ON DELETE CASCADE / RESTRICT / SET NULL を使う FK は特に索引があるか
[ ] N:M 中間テーブルは両方向の索引があるか
[ ] 「親 → 子一覧 + ソート」のクエリを支える複合索引があるか
[ ] FK 列 + 論理削除列（deleted_at IS NULL）の組み合わせは部分インデックスで対応すべきか（部分インデックス）

FK 制約そのものの使いどころは外部キー制約の使いどころを参照してください。