LayerX エンジニアブログ

LayerX の エンジニアブログです。

MySQL 外部キー制約とインデックスに必要な知識

MySQL

バクラク請求書 でリードエンジニアをしているSaaS事業部の @yyoshiki41（中川佳希）です！ バクラクシリーズでは、経理向けSaaSに始まりコーポレートDXをサポートする複数プロダクトを提供しています。

サービスローンチから1年経過したこともあり、2021年12月から2022年1月は短い間に事業部として怒涛のリリースの日々でした。

サービス全体で変化はありましたが今後も変わらずプロダクト開発を通して、経理・コーポレートチームの仕事がバクラクになるようサポートしていきます！

今回の記事は、MySQL の外部キー制約とインデックスについてです。

Foreign Keys

多くの人が馴染みあると思いますが改めて整理すると、2つの役割を担っていると言えます。

  1. 複数のテーブル間でインデックスとしてデータの参照を効率的に行う（外部キー）
  2. 複数のテーブル間でデータの一貫性を保つ（外部キー制約）

上記をサンプルのテーブルを使って具体的にみていきます。

外部キーの作成とインデックス

親テーブルとその外部キーをもつ子テーブルを以下のSQLで作成してみます。

CREATE TABLE `parents` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `children` (
  `id` int NOT NULL,
  `parent_id` int NOT NULL,
  FOREIGN KEY (`parent_id`) REFERENCES `parents` (`id`)
) ENGINE=InnoDB;

作成されたテーブル定義の結果は以下です。 比較してみると上の children テーブルのCREATE文は簡略化して書いたもので、MySQLが自動でインデックスキー（KEY parent_id）を作成してくれている事がわかります。

mysql> SHOW CREATE TABLE children\G;
*************************** 1. row ***************************
       Table: children
Create Table: CREATE TABLE `children` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `children_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parents` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

上では簡略化した書き方をしましたが、外部キー作成の構文は以下で表現されます。

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

マルチカラムインデックスの場合は？

では、マルチカラムインデックスを持つテーブルでの挙動もみてみます。

CREATE TABLE `children` (
  `id` int NOT NULL,
  `parent_id` int NOT NULL,
  `revision` int NOT NULL,  
  KEY (`parent_id`, `revision`),
  FOREIGN KEY (`parent_id`) REFERENCES `parents` (`id`)
) ENGINE=InnoDB;

先に Composite Key として使用するインデックスカラムとして、外部キーとなるカラムを使用しておけば、特に自動でのインデックス作成は行われません。

mysql> SHOW CREATE TABLE children\G;
*************************** 1. row ***************************
       Table: children
Create Table: CREATE TABLE `children` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `revision` int(11) NOT NULL,
  KEY `parent_id` (`parent_id`,`revision`),
  CONSTRAINT `children_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parents` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

逆に、先に外部キー以外のカラムを指定していると、外部キーの単独のインデックスが自動で作成されます。

CREATE TABLE `children` (
  `id` int NOT NULL,
  `parent_id` int NOT NULL,
  `revision` int NOT NULL,  
  KEY (`revision`, `parent_id`),
  FOREIGN KEY (`parent_id`) REFERENCES `parents` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `children` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `revision` int(11) NOT NULL,
  KEY `revision` (`revision`,`parent_id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `children_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parents` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

今回の外部キーの話とは逸れますが、マルチカラムインデックスは内部でカラム値を定義順に連結して作成した値を持っていることが分かります。（効率的なキーの設計をしておけば、追加でのインデックス作成が不要になる。）

外部キー制約

MySQLでは ON DELETE, ON UPDATE の後ろにオプション（RESTRICT | CASCADE | SET NULL | NO ACTION）をつけて表現でき、参照元の親テーブルが削除された場合にどのようにデータの一貫性を保つかを指定できます。 デフォルトでは制約違反のデータ操作は拒否されます。

Cannot add or update a child row: a foreign key constraint fails

例えば ON DELETE CASCADE を指定すれば、親テーブルのレコードが削除された場合に、子テーブルで参照元としているレコードも自動で削除されます。（各オプションの挙動については、他の記事に譲ります。）

外部キー制約とインデックスまとめ

最初に書きましたが、外部キーの重要な役割にデータの一貫性を保つことがあります。
親テーブルのレコードが削除された場合などに、子テーブルのデータをチェックして制約を満たすかを確認する必要があります。その参照を高速に処理するためにインデックスが用いられます。ユーザーがインデックス作成を明示的に定義しない場合には、MySQL側で自動で作成が行われます。
ユーザーにとっても、複数テーブルにまたがるデータを参照する際に効率的なクエリのために、このインデックスを効率的に使うことを意識しておく必要はあります。
今回は普段あまり意識されない内部的な動きを整理してみる記事となりました。

LayerX ではエンジニア採用をオープンしています。カジュアルに話をする機会などもありますのでお待ちしております！

open.talentio.com

open.talentio.com

open.talentio.com