1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@haroya01(キム ドンヒョン)

DBで外部キーは避けるべきか？

Last updated at Posted at 2026-01-30
  • 実験用のスクリプトやコードでAIの助けを借りたため、多少誤りがあるかもしれません。ご指摘いただければ幸いです
  • 日本語がまだ不慣れなため、翻訳には機械翻訳を使用しました。もし不自然な点や間違いがありましたら、ご指摘いただけると幸いです

実験のきっかけ

  • データベースを勉強する中で、「実務では外部キーを避ける」という記事をよく目にしました
  • なぜそうなのか気になったので、実際に実験してみました

外部キーとは？ foreign key

  • RDBMSにおいて、子テーブルと親テーブルの関係を結びつけ
  • 各テーブル間の関連を明示的に把握できる
  • 制約条件である参照整合性referential integrityによって、データの整合性を保つことができる

実験環境のセットアップ

  test-mysql:
    image: mysql:8.0.33
    container_name: mysql-test
    profiles : ["test"]
    ports:
      - "3307:3306"
    environment:
      MYSQL_ROOT_PASSWORD: wowowo1234
      MYSQL_DATABASE: test
    deploy:
      resources:
        limits:
          cpus: '2'
          memory: 4G
    command:
      - --innodb-buffer-pool-size=2G
      - --innodb-log-file-size=512M
      - --max-connections=200

以下のようにDockerを使用して、テストごとに同一の環境を構築しました。

実験条件

  • MySQL 8.0.33, InnoDB
  • 4 threads, 60秒
  • chat_roomsに50万row事前に格納

実験内容

  • sysbenchで4つのスレッドが60秒間、ランダムなroom_codeでINSERT/UPDATEを繰り返し実行する
  • 外部キーありのテーブルとなしのテーブルに同一条件でテストし、QPSとLatencyを比較する
  • sysbench INSERT 性能測定スクリプト
    image.png
  • sysbench UPDATE 性能測定スクリプト
    image.png

外部キーありの場合の測定結果

  • DBのデータは以下の通りである
CREATE TABLE chat_rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_code VARCHAR(100),
    INDEX ix_room_code(room_code)
);

CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_code VARCHAR(100),
    content VARCHAR(200),
    FOREIGN KEY (room_code) REFERENCES chat_rooms(room_code)
);

INSERT INTO chat_rooms (room_code)
SELECT CONCAT('room_', seq)
FROM (
    SELECT ROW_NUMBER() OVER () as seq
    FROM information_schema.columns a,
         information_schema.columns b
    LIMIT 500000
) t;

  • INSERT の測定結果は以下の通りである
    image.png

  • UPDATE の測定結果は以下の通りである
    image.png

外部キーなしの場合の性能測定結果

  • DBのデータは以下の通りである
CREATE TABLE chat_rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_code VARCHAR(100),
    INDEX ix_room_code(room_code)
);

CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_code VARCHAR(100),
    content VARCHAR(200),
    INDEX ix_room_code(room_code)
);

INSERT INTO chat_rooms (room_code)
SELECT CONCAT('room_', seq)
FROM (
    SELECT ROW_NUMBER() OVER () as seq
    FROM information_schema.columns a,
         information_schema.columns b
    LIMIT 500000
) t;
  • INSERT の測定結果は以下の通りである

image.png

  • UPDATE の測定結果は以下の通りである

image.png

結果および総まとめ

項目 外部キー O 外部キー X 差異
INSERT QPS 1,606 1,732 +7.8%
INSERT Latency 2.49 ms 2.31 ms -7.2%
UPDATE QPS 1,589 1,637 +3.0%
UPDATE Latency 2.51 ms 2.44 ms -2.8%
  • 外部キーなしの方が3〜8%速い

なぜこのような差が生じたのか？

  • INSERT時に実際にどのようなLockが取得されるか確認してみた
-- セッション1
START TRANSACTION;
INSERT INTO messages_with_fk (room_code, content) VALUES ('room_1', 'test');
-- コミットせずに待機した後、セッション2の結果を確認してROLLBACK;
-- セッション2
SELECT * FROM performance_schema.data_locks;

外部キーありの場合の結果

ENGINE LOCK_ID TRX_ID THREAD EVENT SCHEMA TABLE INDEX_NAME OBJECT_INSTANCE TYPE MODE STATUS LOCK_DATA
INNODB 281472730665768:1076:281472627966840 302521 71 19 test chat_rooms 281472627966840 TABLE IS GRANTED
INNODB 281472730665768:1077:281472627966752 302521 71 19 test messages_with_fk 281472627966752 TABLE IX GRANTED
INNODB 281472730665768:10:5:2:281472627963840 302521 71 19 test chat_rooms ix_room_code 281472627963840 RECORD S,REC_NOT_GAP GRANTED 'room_1', 1

外部キーなしの場合の結果

ENGINE LOCK_ID TRX_ID THREAD EVENT SCHEMA TABLE INDEX_NAME OBJECT_INSTANCE TYPE MODE STATUS LOCK_DATA
INNODB 281472730665768:1078:281472627966752 302523 71 47 test messages_no_fk 281472627966752 TABLE IX GRANTED

結論

  • 外部キーがあるとINSERTのたびに親テーブルにlockが追加でかかり、親rowの存在有無を確認する
  • なぜなら確認中に親rowが削除されてはいけないため、Shared Lockがかかる
  • このため3〜8%の性能差が生じるのではないかと思う

追加の疑問

  • それでは性能上の差はあるものの、わずかな差でデータの整合性を捨てる理由があるのだろうか
  • そこで調べてみると、DDL（データベース拡張時）で性能を大きく消費するとのことだったので測定してみた

DDL性能にも差があるのか？

  • 500万件のデータがあるテーブルに外部キー/インデックスを追加する時間を測定してみた
CREATE TABLE chat_rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_code VARCHAR(100),
    INDEX ix_room_code(room_code)
);

CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_code VARCHAR(100),
    content VARCHAR(200)
);

INSERT INTO chat_rooms (room_code)
SELECT CONCAT('room_', seq)
FROM (
    SELECT ROW_NUMBER() OVER () as seq
    FROM information_schema.columns a,
         information_schema.columns b
    LIMIT 500000
) t;

INSERT INTO messages (room_code, content)
SELECT CONCAT('room_', FLOOR(1 + RAND() * 500000)), 'test'
FROM information_schema.columns a,
     information_schema.columns b,
     information_schema.columns c
LIMIT 5000000;

外部キーありの場合（親rowの存在有無を検証する）

SET @start = NOW(6);
ALTER TABLE messages
ADD CONSTRAINT fk_room FOREIGN KEY (room_code) REFERENCES chat_rooms(room_code);
SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6)) / 1000000 AS fk_seconds;

image.png

インデックスのみの場合

SET @start = NOW(6);
ALTER TABLE messages ADD INDEX ix_room_code(room_code);
SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6)) / 1000000 AS idx_seconds;

image.png

外部キーありだが親の存在有無を検証しない場合

SET FOREIGN_KEY_CHECKS = 0;
SET @start = NOW(6);
ALTER TABLE messages
ADD CONSTRAINT fk_room FOREIGN KEY (room_code) REFERENCES chat_rooms(room_code);
SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6)) / 1000000 AS fk_nocheck_seconds;
SET FOREIGN_KEY_CHECKS = 1;

image.png

結論

作業内容 所要時間
外部キー追加（検証あり） 32.1868s
外部キーなしでインデックスのみ追加 16.7893s
外部キー追加（検証なし） 16.2571s
  • 実際にカラムを追加する際、外部キーカラムを追加する場合は時間がかなり多くかかった
  • ただし、すでに整合性が保証された状態であればFOREIGN_KEY_CHECKS=0で回避可能である
  • つまり外部キーを避けるべき理由はDDL性能だけではなさそうである

DBで外部キーを避けるべきか？に対する私の考え

  • まずインターネットでよく見かけた回答については、以下のように結論を出した

「MySQLは外部キーを指定するとインデックスを使ってくれるのでJoin性能が良い」

  • 外部キーを設定するとインデックスが自動生成されるのは正しいが、インデックスだけ別途設定してもJoin性能は同じである

「JPA外部キーがないと使えないのでは？」

  • @ManyToOneは外部キーなしでも使用可能である
  • JPAはオブジェクト指向プログラミングのためのツールであるため、データを取得してオブジェクトにマッピングしてくれさえすれば外部キーの有無とは関係ない
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "room_code", foreignKey = @ForeignKey(ConstraintMode.NO_CONSTRAINT))
private ChatRoom chatRoom;

「外部キーを使用すると性能に問題がある」

  • 3〜8%の差が発生したが、それがデータ整合性を諦めるほどの差なのか、性能か正確性か、常にトレードオフできるという心構えで考えるべきだと思う
  • 私はこの程度の差で、現在進めているプロジェクトで外部キーを外す必要性を感じなかった

「DDLでドメイン拡張時に外部キーを使用すると性能上の問題がある」

  • 実際に有意に性能上の問題があることを確認した
  • しかし親rowをチェックする機能をオフにすれば（すでにデータの整合性が保たれているという前提で）性能上の差は発生しない

「開発時にテストデータ生成時、制約条件を守るのが面倒である」

  • これも上記と同様に、制約条件の確認機能をテスト時にだけ一時的にオフにすればよいと思う
  • また、実際と同じ環境でテストするのが良いと思うので、制約条件を（多いのは分かるが...）守るのはどうだろうか？

「アプリケーション側でデータベースを制御するのが正しい」

  • ドメインは常にデータベース側と分離しようとする方向に進化している
  • アプリケーション側で開発者がデータベースの全権を明確に制御すべきである（予測可能な）（ドメインはデータベースに依存性を持たせてはいけない）という点では、DB外部キーを外すこともできそうである
  • しかしDBが最後の検証（防御的開発？）を担うべきだという観点では、外部キーを設定してもよさそうである

「運用の柔軟性のために？」

  • 致命的なエラーが発生した際、外部キーのせいで削除できない場合、サービスに大きな問題となり得ると思う

それでも外部キーを使わない理由

  • 大規模サービスで外部キーを使わないのは、性能よりもシャーディングのためだと思う

  • シャーディングするとテーブルが別々のサーバーに分散される

  • 異なるサーバー間では外部キー制約が不可能である

  • そのためアプリケーション側で制約条件を検査する

  • またMSAの観点でも、同様の理由が当てはまる

それでも...

  • Instagramは1400万ユーザーまで単一DBで耐えた
  • 私が行うほとんどのプロジェクトでは外部キーを使っても良さそうだと思った

最終結論

  • 若干の性能差がある
  • 常にトレードオフ、自分のドメインに応じて決定しよう
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?