外部キーにインデックスは必要？ DB ごとの違いと確認方法

外部キー制約を付けると、インデックスも自動で作られると思われがちです。実際には、自動で作るのは主な DB の中では MySQL（InnoDB）だけです。この記事では、外部キーの列にインデックスが必要な理由と、DB ごとの違い、足りないインデックスの見つけ方を説明します。

外部キーの列にインデックスが必要な理由

外部キーの列（子テーブル側）にインデックスがないと、次の 2 つの場面で子テーブル全体を読むことになります。

• 結合と絞り込み：「この顧客の受注一覧」のように、親のキーで子を探す検索は日常的に発生します。
• 親の削除・更新：親の行を消すとき、DB は参照している子の行がないか（ON DELETE CASCADE なら消す対象）を探します。インデックスがないと、親を 1 行消すたびに子テーブルを全件読みます。

子テーブルが小さいうちは問題になりませんが、行数が増えると、親の削除が急に遅くなったり、確認の間のロックが長引いたりします。

DB ごとの違い

複合インデックスで代用できる条件

外部キーの列が、既存の複合インデックスの先頭に含まれていれば、新しく作る必要はありません。たとえば (order_id, line_no) の主キーがあれば、order_id の外部キーはそれで足ります。逆に (line_no, order_id) の順だと、order_id だけの検索には使えません。

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

足りないインデックスを探す（PostgreSQL）

PostgreSQL では、次のクエリで「外部キーの列を先頭に持つインデックスがない」外部キーを一覧できます。

SELECT c.conrelid::regclass AS table_name,
       c.conname AS foreign_key,
       pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (string_to_array(i.indkey::text, ' ')::int2[])[1:array_length(c.conkey, 1)] @> c.conkey
  )
ORDER BY 1, 2;

部分インデックスや式インデックスは条件によって使われないことがあるので、結果は一覧として確認し、実際の検索で使われるかは EXPLAIN で確かめてください。

インデックスを付けなくてよい場合

• 子テーブルが常に小さく、親を消すことも親のキーで子を探すこともない
• 書き込みが非常に多く、インデックスの更新コストのほうが問題になる（測ってから判断します）

迷ったら付けておき、不要なら外すほうが安全です。

設計の段階で気づく

Joinery の Lint は、外部キーの列を先頭に持つインデックスがない場合に警告（JNR003）を出します。MySQL は自動で作られるため対象外です。DDL を流す前の設計の段階で気づけるので、本番で遅くなってから調べる必要がなくなります。