はじめに
MySQLを利用していて、「親テーブルの行を削除したのに、ON DELETE CASCADEで削除された子テーブルの変更がログに見えない……」と困ったことはありませんか？
ON DELETE/UPDATE CASCADE は外部キー制約のオプションであり、親テーブルの行を変更(削除・更新)すると、子テーブルの対応する行も自動的に変更(削除・更新)され、親子テーブルの整合性を保つための仕組みです。
このCASCADEは長らくInnoDBストレージエンジン内部で処理されてきました。
そのため、
・ 実際にどの行が変更されたのかが外から見えづらい
・ バイナリログに子テーブル側の変更が残らない
・ レプリケーションや監査の観点で追跡が難しい
といった課題がありました。
MySQL 9.6では、このCASCADEがSQLレイヤー側で実行される方式に変更されています。
この変更により、CASCADEによる変更が子テーブルも含め全てバイナリログへ記録されるようになりました。
本記事では、この変更によるユーザ側のメリットや、新旧方式で具体的に何が変わったのかを解説していきます。
これまで何が困っていたのか(BEFORE)
従来のMySQLでは、外部キー制約に伴うCASCADEの変更はInnoDBストレージエンジン内部で処理されていました。
そのため、CASCADE処理はSQLレイヤーでは実行されず、バイナリログには親テーブルの変更しか記録されません。
その結果、複数の行が変更されているにもかかわらず、ログ上は1件の変更しか見えず、「実際にどの行がいつ変更されたのか分からない」という状況が生じていました。
この仕様は実務上の影響も小さくありません。
まず、監査や障害調査では、どの行が変更されたのかを正確に把握することが難しいため、外部キー定義やデータ状態を推測しながら調べる必要がありました。
さらにレプリケーション環境では、CASCADEによる変更がバイナリログに記録されないため、レプリカ側は外部キー制約の解釈に依存して変更を再現する方式になっていました。
このため、CASCADEの適用結果はログそのものから決定されるのではなく、レプリカ側のストレージエンジンや外部キー設定が前提条件となって再現される挙動になります。
通常は問題なく動作しますが、これらの前提条件が満たされない場合には、子テーブル側の変更が期待どおりに再現されない可能性があります。
この点については、MySQLの公式リファレンスでも次のように説明されています。
Cascading actions for
InnoDBtables on the source are executed on the replica only if the tables sharing the foreign key relation useInnoDBon both the source and replica.（外部キー関係を持つテーブルがソースとレプリカの両方で InnoDB を使用している場合にのみ、カスケードアクションはレプリカ側で実行されます。）
また、この挙動はbinlog_formatの設定に依存しません。
たとえbinlog_format = ROWを使用していても、バイナリログに記録されるのは親テーブルの DELETE/UPDATEのみであり、CASCADEによって発生した子テーブル側の行変更はログに出力されません。
このように、CASCADEはアプリケーション側で親子テーブルの変更を個別に記述しなくてもよいという運用面の利点はあるものの、どの行がどのタイミングで変更されたのかを外部から把握しづらいという課題がありました。
変更によって何が嬉しいのか（AFTER）
MySQL 9.6.0以降では、外部キー制約に伴うCASCADEはSQLレイヤーで処理されるようになりました。
これにより、CASCADEによる子テーブル側の変更も含め、実際に実行されたすべての操作がバイナリログに記録されます。
この変更によって、従来の課題は大きく解消されます。
1. 「どの行が削除されたのか分からない」問題
親テーブルだけでなく、子テーブルに対する変更もバイナリログに出力されるため、実際に変更があった行を正確に追跡できるようになります。
監査や障害調査でも、外部キー定義やデータ状態を推測する必要がなく、バイナリログを追うだけで実際の変更内容を把握できます。
2. 「レプリケーションが外部キー解釈に依存する」問題
CASCADEの結果そのものがバイナリログとして送られるため、レプリカはバイナリログを適用するだけでよくなります。
従来のようにレプリカ側が外部キー制約を解釈してCASCADEを再実行する必要がなくなり、ストレージエンジンや外部キー設定の差異に左右されず、より一貫したデータ適用が可能になります。
このように、従来はInnoDB内部で見えなかったCASCADEの挙動がSQLレイヤーに引き上げられたことで、「内部で何が起きているのかが見えない」という課題は解消され、ログの可視性とレプリケーションの再現性が大きく向上しました。
CASCADEはどう変わったのか？新旧で比較してみる
ここでは、新旧バージョンの挙動を比較しながら、外部キー処理がSQLレイヤーに移ったことで何が変わったのかを確認していきます。
本検証では、以下の2つの環境を使用しています。
・ MySQL 8.4.8（旧方式：InnoDB内部でCASCADEを処理）
・ MySQL 9.6.0（新方式：SQLレイヤーでCASCADEを処理）
上記環境から、次の点を確認します。
・ 旧方式では、CASCADEによる子テーブル側の変更(削除)がバイナリログに記録されないこと。その結果、レプリケーション時の反映がレプリカ側の外部キー定義に依存すること。
・ 新方式では、CASCADEによる子テーブル側の変更(削除)もバイナリログに記録されること。その結果、レプリカ側に外部キー定義がなくても、バイナリログに含まれる行変更として適用されること。
それでは実際の挙動の違いを確認していきましょう。
まず、以下のような親テーブル（parent）と子テーブル（child）を作成します。
今回の検証では、旧方式ではレプリカ側の外部キー定義に依存して結果が変わるか、新方式では外部キー定義の差分があってもバイナリログに含まれる変更として適用されるかを確認したいため、レプリカ側のchildテーブルではCASCADE付きの外部キー制約（child_ibfk_1）をあえて外しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SOURCE_DB> SHOW CREATE TABLE parent\G *************************** 1. row *************************** Table: parent Create Table: CREATE TABLE `parent` ( `id` int NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SOURCE_DB> SHOW CREATE TABLE child\G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE `child` ( `id` int NOT NULL, `parent_id` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `parent_id` (`parent_id`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci REPLICA_DB> SHOW CREATE TABLE parent\G *************************** 1. row *************************** Table: parent Create Table: CREATE TABLE `parent` ( `id` int NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci REPLICA_DB> SHOW CREATE TABLE child\G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE `child` ( `id` int NOT NULL, `parent_id` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `parent_id` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
ソース側のchildテーブルには、parentテーブルのidを参照するON DELETE CASCADE付きの外部キーがあります。
一方、レプリカ側のchildテーブルには同じ外部キー制約がありません。
この状態で親テーブルの行を削除すると、旧方式ではレプリカ側でCASCADEが再実行されず、新方式ではバイナリログに含まれる子テーブル側の削除がそのまま適用されます。
また、バイナリログの比較の前提として、実行前のログサイズを確認しておきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
・MySQL 8.4.8 SHOW BINARY LOGS; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 | 158 | No | +---------------+-----------+-----------+ 1 row in set (0.00 sec) ・MySQL 9.6.0 SHOW BINARY LOGS; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 | 158 | No | +---------------+-----------+-----------+ 1 row in set (0.000 sec) |
次にサンプルデータを挿入します。
|
1 2 |
INSERT INTO parent VALUES (1, 'parent row'); INSERT INTO child VALUES (100, 1, 'child row'); |
これにより、childテーブルのid=100の行がparentテーブルのid=1を参照する状態になります。
この状態で親テーブルのid=1のデータを削除します。
|
1 |
DELETE FROM parent WHERE id = 1; |
削除後のテーブルを確認すると、以下のような差分が出ていました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
・MySQL 8.4.8 SOURCE_DB> select * from parent; Empty set (0.00 sec) SOURCE_DB> select * from child; Empty set (0.00 sec) REPLICA_DB> select * from parent; Empty set (0.00 sec) REPLICA_DB> select * from child; +-----+-----------+ | id | parent_id | +-----+-----------+ | 100 | 1 | +-----+-----------+ ・MySQL 9.6.0 SOURCE_DB> SELECT * FROM parent; Empty set (0.000 sec) SOURCE_DB> SELECT * FROM child; Empty set (0.000 sec) REPLICA_DB> SELECT * FROM parent; Empty set (0.000 sec) REPLICA_DB> SELECT * FROM child; Empty set (0.000 sec) |
この結果から、旧方式と新方式でレプリケーション時の挙動が異なることが分かります。
MySQL 8.4.8では、ソース側ではCASCADEによって子テーブルの行も削除されていますが、レプリカ側ではchildテーブルにCASCADE付き外部キーが存在しないため、子テーブルの行は削除されていません。
つまり、旧方式では、CASCADEの結果そのものがバイナリログに含まれるのではなく、レプリカ側が外部キー定義をもとに処理を再現するため、レプリカ側の定義差分が結果に影響することが確認できます。
一方、MySQL 9.6.0では、レプリカ側に同じ外部キー定義がなくても、ソース側と同様に子テーブルの行が削除されています。
これは新方式では、CASCADEによって発生した子テーブル側の削除もバイナリログに記録され、その行削除がレプリカに適用されていることを示しています。
続いてDELETE実行後のバイナリログサイズを比較します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
・MySQL 8.4.8 SHOW BINARY LOGS; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 | 1687 | No | +---------------+-----------+-----------+ ・MySQL 9.6.0 SHOW BINARY LOGS; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 | 1803 | No | +---------------+-----------+-----------+ |
MySQL 9.6.0 の方が 約 120 バイト多くなっています。
この差分だけで断定はできませんが、少なくとも子テーブル側のDELETEが追加で記録されている可能性が高いことが分かります。
そこで、実際のバイナリログの中身も確認してみます。
※差分が分かりやすいように改行やコメントを入れています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
・MySQL 8.4.8 BEGIN /*!*/; # at 1545 #260331 6:31:24 server id 1 end_log_pos 1605 CRC32 0x2144f231 Table_map: `test`.`parent` mapped to number 132 # has_generated_invisible_primary_key=0 # at 1605 #260331 6:31:24 server id 1 end_log_pos 1656 CRC32 0x22335b49 Delete_rows: table id 132 flags: STMT_END_F ### DELETE FROM `test`.`parent` ### WHERE ### @1=1 ### @2='parent row' # at 1656 #260331 6:31:24 server id 1 end_log_pos 1687 CRC32 0x66098c84 Xid = 63 COMMIT/*!*/; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
・MySQL 9.6.0 BEGIN /*!*/; # at 1547 #260331 6:46:43 server id 1 end_log_pos 1607 CRC32 0x46af45c3 Table_map: `test`.`parent` mapped to number 180 # has_generated_invisible_primary_key=0 # at 1607 #260331 6:46:43 server id 1 end_log_pos 1667 CRC32 0x56397b0c Table_map: `test`.`child` mapped to number 182 # has_generated_invisible_primary_key=0 ** MySQL 9.6 で追加された DELETE（CASCADE 部分） ** # at 1667 #260331 6:46:43 server id 1 end_log_pos 1721 CRC32 0x2eed97e2 Delete_rows: table id 182 flags: NO_FOREIGN_KEY_CHECKS_F USE_SQL_FOREIGN_KEY_F # at 1721 #260331 6:46:43 server id 1 end_log_pos 1772 CRC32 0x6fe2e8a5 Delete_rows: table id 180 flags: STMT_END_F USE_SQL_FOREIGN_KEY_F ### DELETE FROM `test`.`child` ### WHERE ### @1=100 ### @2=1 ### @3='child row' ** ここまで ** ### DELETE FROM `test`.`parent` ### WHERE ### @1=1 ### @2='parent row' # at 1772 #260331 6:46:43 server id 1 end_log_pos 1803 CRC32 0xf8a5be52 Xid = 101 COMMIT/*!*/; |
MySQL 8.4.8では親テーブルのDELETEしか記録されていません。
子テーブルの削除はInnoDB内部で行われるため、バイナリログには出ていないことが分かります。
このため、レプリカ側では子テーブルの削除をログから直接適用するのではなく、外部キー定義にもとづいて再現する形になります。
一方で、MySQL 9.6.0では子テーブル→親テーブルの順でDELETEが明示的に記録されています。
このことから、外部キー処理がSQLレイヤーで実行され、CASCADEが実際にバイナリログに記録されていることが分かります。
ここまでの比較から、MySQL 9.6ではCASCADEの削除がSQLレイヤーで実行され、子テーブル側のDELETEも含めてバイナリログに明示的に記録されるようになったこと、そしてその結果としてレプリケーション時の反映がレプリカ側の外部キー定義に左右されなくなったことが確認できました。
注意点
CASCADEが SQLレイヤーに移ったことで多くのメリットがありますが、 運用上いくつか注意すべき点も存在します。
1. バイナリログのサイズが増える
CASCADEによる子テーブル側の変更がすべてバイナリログに記録されるため、 従来よりもログサイズが増加します。
そのため、運用する際には、バイナリログが増えても問題がないようにログ容量やローテーションの見直しが事前に必要です。
2. レプリケーション負荷が変わる可能性
CASCADEの結果がすべてバイナリログに出るため、レプリカ側では適用する行イベントが増えることになります。
そのため、レプリケーション遅延やI/Oの増加などのパフォーマンスに影響が出てくる可能性もあります。
まとめ
MySQL 9.6ではCASCADEがSQLレイヤーで実行されるようになり、 ログの可視性やレプリケーションの再現性が大きく向上しました。
一方で、CASCADEの結果がすべてバイナリログに記録されるため、 ログサイズの増加やレプリケーション負荷など、パフォーマンス面への影響も考慮が必要です。
また、運用上の理由で従来の旧方式(InnoDB内部でCASCADEを処理)に戻したい場合は、サーバ起動時に次の設定を有効にします。
|
1 |
innodb_native_foreign_keys=ON |
innodb_native_foreign_keysはMySQL 9.6で追加された、CASCADEの処理場所を切り替えるための新パラメータです。
デフォルトはOFF(CASCADEがSQLレイヤーで実行され子テーブルの変更もバイナリログに出力される設定)になっています。
もともとCASCADEは、アプリケーション側で親子テーブルの変更を個別に実装する必要がなくなるという利点があり、 運用負担を減らす仕組みとして有効でした。
従来は「内部で何が起きているか見えない」という課題もありましたが、 今回の変更により挙動がすべてログに残るようになり、運用上の不安要素が大きく減ったと考えられます。
そのため、親子関係の整合性管理や削除処理の実装で負担を感じている環境では、「アプリのコードをシンプルに保ちつつ、DB側で安全を担保する」ための有力な選択肢として、この改善をきっかけにCASCADEの導入を検討してみてはいかがでしょうか。
