我々(主語が大きい)は何故MySQLで外部キーを使わないのか
TIME rest time current/total
TopicsPlaceHolder

我々(主語が大きい)は何故MySQLで外部キーを使わないのか

Foreign Key Night

Feb 13th, 2015

Profile

songmu

Mackerel Logo

　

最近のCPAN Module

Q. 外部キー使ってる人いますか？

Q. 外部キー使ってる人いますか？

外部キー便利!!!

MySQLで外部キーを使わない運用をする理由

それなりの規模のMySQL運用だと外部キーを使用するのをためらう理由がある。

本日のスキーマ

CREATE TABLE `member` (
  `id` INTEGER unsigned NOT NULL auto_increment,
  `earned_item_count` INTEGER unsigned NOT NULL DEFAULT 0,
  `name` VARCHAR(191) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

CREATE TABLE `item` (
  `id` SMALLINT unsigned NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

CREATE TABLE `member_item` (
  `member_id` INTEGER unsigned NOT NULL,
  `item_id` SMALLINT unsigned NOT NULL,
  `amount` INTEGER unsigned NOT NULL DEFAULT 0,
  CONSTRAINT `member_item_fk` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`),
  CONSTRAINT `member_item_fk_1` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`),
  PRIMARY KEY (`member_id`, `item_id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

ERD

パーティションを使えない

http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-storage-engines.html

InnoDB foreign keys and MySQL partitioning are not compatible. Partitioned InnoDB tables cannot have foreign key references, nor can they have columns referenced by foreign keys. InnoDB tables which have or which are referenced by foreign keys cannot be partitioned.

暗黙のindex

http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

リレーション先の行にshared lockがかかる

http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.

削除がやりづらくなる

テストfixtureを準備するのがめんどくさい(?)

まとめ

We are Hiring

hatena