📚
MySQL 外部キー制約とインデックスに必要な知識
バクラク請求書 でリードエンジニアをしているSaaS事業部の @yyoshiki41（中川佳希）です！ バクラクシリーズでは、経理向けSaaSに始まりコーポレートDXをサポートする複数プロダクトを提供しています。
サービスローンチから1年経過したこともあり、2021年12月から2022年1月は短い間に事業部として怒涛のリリースの日々でした。
サービス全体で変化はありましたが今後も変わらずプロダクト開発を通して、経理・コーポレートチームの仕事がバクラクになるようサポートしていきます！
今回の記事は、MySQL の外部キー制約とインデックスについてです。
Foreign Keys
多くの人が馴染みあると思いますが改めて整理すると、2つの役割を担っていると言えます。
- 複数のテーブル間でインデックスとしてデータの参照を効率的に行う（外部キー）
- 複数のテーブル間でデータの一貫性を保つ（外部キー制約）
上記をサンプルのテーブルを使って具体的にみていきます。
外部キーの作成とインデックス
親テーブルとその外部キーをもつ子テーブルを以下のSQLで作成してみます。
作成されたテーブル定義の結果は以下です。 比較してみると上の テーブルのCREATE文は簡略化して書いたもので、MySQLが自動でインデックスキー（KEY ）を作成してくれている事がわかります。
上では簡略化した書き方をしましたが、外部キー作成の構文は以下で表現されます。
マルチカラムインデックスの場合は？
では、マルチカラムインデックスを持つテーブルでの挙動もみてみます。
先に Composite Key として使用するインデックスカラムとして、外部キーとなるカラムを使用しておけば、特に自動でのインデックス作成は行われません。
逆に、先に外部キー以外のカラムを指定していると、外部キーの単独のインデックスが自動で作成されます。
今回の外部キーの話とは逸れますが、マルチカラムインデックスは内部でカラム値を定義順に連結して作成した値を持っていることが分かります。（効率的なキーの設計をしておけば、追加でのインデックス作成が不要になる。）
外部キー制約
MySQLでは , の後ろにオプション（）をつけて表現でき、参照元の親テーブルが削除された場合にどのようにデータの一貫性を保つかを指定できます。 デフォルトでは制約違反のデータ操作は拒否されます。
例えば を指定すれば、親テーブルのレコードが削除された場合に、子テーブルで参照元としているレコードも自動で削除されます。（各オプションの挙動については、他の記事に譲ります。）
外部キー制約とインデックスまとめ
最初に書きましたが、外部キーの重要な役割にデータの一貫性を保つことがあります。親テーブルのレコードが削除された場合などに、子テーブルのデータをチェックして制約を満たすかを確認する必要があります。その参照を高速に処理するためにインデックスが用いられます。ユーザーがインデックス作成を明示的に定義しない場合には、MySQL側で自動で作成が行われます。ユーザーにとっても、複数テーブルにまたがるデータを参照する際に効率的なクエリのために、このインデックスを効率的に使うことを意識しておく必要はあります。今回は普段あまり意識されない内部的な動きを整理してみる記事となりました。
LayerX ではエンジニア採用をオープンしています。カジュアルに話をする機会などもありますのでお待ちしております！