MySQLで外部キー制約を持つカラム追加のマイグレーションをオンラインDDLで実行する

技術本部Sansan Engineering Unit Nayoseグループの金久保です。

Nayoseグループでは、当社のほぼすべてのプロダクトが利用する共通基盤である”データの名寄せ”サービスの開発を日々行っており、その中でさまざまなデータベースやSQLに関する課題の解決に取り組んでいます。

今回は、私が直面したマイグレーションの課題とその解決方法について知見を共有したいと思います。

MySQLのDDLアルゴリズム
外部キー制約のあるカラム追加における課題
- 問題点
マイグレーション中のFOREIGN_KEY_CHECKを無効化する
- 注意点
比較して検証する
FOREIGN_KEY_CHECKを無効化しても良いかの判断基準
まとめ
最後に

※今回の記事は次の環境を使用した内容になります。
・MySQL 8.0
・Amazon Aurora MySQL v3.04.3

MySQLのDDLアルゴリズム

MySQLでは３つのDDLアルゴリズムが存在します。特に指定がなければ以下で紹介している順に、INSTANT → INPLACE → COPYの優先度で評価され、対象のDDL操作が可能なアルゴリズムを採用します。

比較項目/アルゴリズム	INSTANT	INPLACE	COPY
実行速度	超高速	比較的速い	最も遅い
データ操作	メタデータのみ変更	テーブル構造をその場で変更	新しくテーブルを作成し、データを一行ずつコピーする
特徴	データ量に依存しない	オンラインDDLが可能	伝統的なDDLアルゴリズム
操作中のDML	可能	可能	不可
制限	使用用途が限定的	一部非対応なDDL操作がある	オフラインDDLのみ

dev.mysql.com

外部キー制約のあるカラム追加における課題

外部キー制約のあるカラムを追加するDDLでは、参照整合性（子テーブルの特定のカラムの値が親テーブルの特定のカラムに存在することを保証する）によりCOPYアルゴリズムが採用されます。
したがって、本DDLはオフラインDDLとして実行され、マイグレーション実行中のDML操作ができなくなります。

dev.mysql.com

問題点

Nayoseグループが管轄する”データ名寄せ”サービスは、Sansanのほぼ全てのプロダクトから利用されています。
そのため、オフラインDDLが行われる場合、マイグレーション実行中にデータの更新が不可能になることから次の問題を懸念していました。

Sansanのほぼ全てのプロダクトを利用するユーザーに対して名寄せサービスが提供できなくなること

加えて、対象のテーブルが数千万件のレコードを持っていることから、マイグレーション完了までに30~60分程度の時間がかかることもわかりました。

以上よりユーザー影響の大きさを鑑みて、「外部キー制約を持つカラム追加をオンラインDDLで行う」アプローチを取ることにしてみました。

マイグレーション中のFOREIGN_KEY_CHECKを無効化する

外部キー制約のあるカラム追加がオフラインDDLになってしまう理由は、参照整合性を保つためにテーブルロックをし、データコピー毎に外部キー制約チェック(FOREIGN_KEY_CHECK)を行うからです。

したがってマイグレーション中にこのチェックを省略することで、テーブル全体にかかるロックを解除し、DML操作ができるようになります。

実際のマイグレーションのフローは次のようになります。
該当のマイグレーションファイルが実行されると、外部キー制約のチェックを無効化した上で、処理したい内容を実行し、その後外部キー制約のチェックを再度有効化してマイグレーションの操作を完了させます。

こちらを実際にクエリに落とし込むと以下のように表現できます。
ALTER TABLE中の操作前に FOREIGN_KEY_CHECK=0 を指定し無効化します。
その後カラム追加と外部キー制約の付与し、最後に FOREIGN_KEY_CHECK=1 で再度有効化してマイグレーションを完了します。

SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE hoge_tables
ADD COLUMN new_column_id BIGINT AFTER order_column,
ADD CONSTRAINT fk_hoge_tables_new_column_id FOREIGN KEY (new_column_id)
REFERENCES fuga_tables (id),
ALGORITHM=INPLACE, LOCK=NONE;

SET FOREIGN_KEY_CHECKS = 1;

※MySQLのSETステートメントではスコープを明示的にGLOBALにしない限り、SESSIONのみが対象です。

注意点

クエリを実行する前に次の3点に注意する必要があります。
1. セッション完了時にFOREIGN_KEY_CHECKを有効化すること
2. 明示的に ALGORITHM=INPLACE を指定すること

COPYアルゴリズムが選ばれないように明示的にINPLACEを指定します
ALGORITHMステートメントを指定すると、指定した以外のDDLアルゴリズムが採用された場合エラーになります

3. LOCK=NONEオプションを指定すること

明示的にLOCKをNONEに指定することで、DMLがブロックされることを防ぎます

比較して検証する

実際にどれほどマイグレーションによる影響が出るのか、マイグレーションテストをしながら負荷検証をして、影響度合いを監視ツールで観測してみました。
カラム追加&外部キー制約が付与される該当テーブルへ書き込みを行うAPIリクエストを次のような負荷強度で流してみます。

負荷強度を次のように設定:

VUS: 400vu/s
継続時間: ( ⅰ ) 30分 ( ⅱ ) 10分（各DDLでのマイグレーション所要時間に合わせる）

( ⅰ ) 従来通りCOPYアルゴリズムで行った場合

オフラインDDLとして実行されテーブルロックされるので結果の通り、30分以上マイグレーションに時間がかかり、6.29kのリクエストに対し163件のINSERTエラーが発生しています。

( ⅱ ) FOREIGN_KEY_CHECKを無効化しINPLACEアルゴリズムで行った場合

FOREIGN_KEY_CHECKを無効化し、INPLACEアルゴリズムで負荷を流してみます。マイグレーションにかかる時間が300s程度に短縮され、9.38kのリクエストに対しINSERTエラーの発生は0件です。
(※本番のマイグレーションではメタデータロックにより、他の参照箇所のクエリと競合しDeadlock Errorが発生する、というケースが数件ありましたがINSERTエラーはやはり0件でした。)