0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@tonbi_attack

親子テーブルを1対1から1対多に組み替えたDBリファクタリングの手順

0
Posted at

はじめに

この記事では、親テーブルと子テーブルの関係を、実質的な1対1から1対多に移行したときのDBリファクタリングを整理します。

題材は実際の移行作業を元にしていますが、テーブル名やカラム名、業務背景は抽象化しています。
主題は、親テーブルに置いていた属性を子テーブルへ移し、既存データを壊さずに関係を組み替える進め方です。
特に、データ構造で表現すべきものをアプリケーション側で無理に吸収した結果、集計や検索や更新のすべてにしわ寄せが広がり、DBを直さない方が危険になったケースを扱います。

もともとは親テーブル側に属性IDを持たせていましたが、この構造では1件の親に複数の子を自然にぶら下げにくくなっていました。
そこで、属性を子テーブル側へ移し、親子関係を整理し直しました。
アプリケーションコードも修正しましたが、この記事では特にDBスキーマ変更とデータ移行の進め方に絞って扱います。

今回の変更は、きれいな設計にしたいから行ったわけではありません。
もともとアプリケーション側で、実質的には1対多で扱いたいデータを、1対1前提の構造に無理やり載せていました。

その結果、次のような問題が出ていました。

  • 特定のカラムを使って擬似的に1対多を表現する必要がある
  • 集計や検索で本来不要な補正ロジックが増える
  • 一部の処理だけ特別扱いが必要になる
  • アプリケーション側で整合性を無理に合わせ続ける必要がある

この状態は、アプリケーションで設計の歪みを吸収しているだけです。
そのまま運用を続ける方が非効率で危険だと判断し、DBを含めたリファクタリングに踏み切りました。

何がつらかったのか

変更前は、概念的には次のような状態でした。

この構造だと、ATTRIBUTE_ID は親テーブル側の属性として扱われます。
そのため、次の問題が出ます。

  • 子ごとに属性を持てない
  • 1つのグループに対して複数の子を扱う表現がしにくい
  • 子に寄せたい情報が親テーブルに残り続ける
  • アプリケーション側でも親から直接属性を読む前提が広がる

実務では、この「最初は単純で扱いやすかった構造」が、後から拡張の足かせになります。

しかも厄介なのは、アプリケーション側で補正している間は、一見すると動いてしまうことです。
しかし、その状態では集計、検索、更新のたびに例外的な考慮が増えます。
結果として、DBを直さないコストの方が大きくなります。

今回のリファクタリングは、まさにその段階に入ったため実施したものです。

言い換えると、今回の判断は「DBリファクタリングは危険だから避けたい」と「アプリケーション側で無理な補正を続けるのはもっと危険」の比較でした。
最終的には、後者のコストと事故リスクの方が大きいと判断しました。

今回のリファクタリング方針

方針はシンプルです。

  • child_table に ATTRIBUTE_ID を追加する
  • 既存の parent_table.ATTRIBUTE_ID を子へ移行する
  • GROUP_KEY を使って PARENT_ID を寄せ直す
  • 不要になった parent_table.ATTRIBUTE_ID を削除する
  • 最後に child_table.ATTRIBUTE_ID を NOT NULL にする

変更後のイメージは次のとおりです。

ポイントは、最初から一気に削らないことです。
Expand-Migrate-Contract の順で、小さく段階的に進めています。

実際に行ったスクリプトの流れ

移行は複数のSQLに分けていました。
この分割がかなり重要です。

1. 不正データを先に落とす

最初に、親テーブルに必要な属性が入っていないデータを削除します。

DELETE FROM
    parent_table
WHERE
    attribute_id IS NULL;

ここで大事なのは、移行前に「新しい制約に乗らないデータ」を先に洗い出すことです。
後から NOT NULL を付けるなら、先に不正データを処理しないと途中で止まります。

ただし、本番でいきなり削除するのは強すぎることがあります。
件数確認なしで実行すると、想定外のデータを落とす可能性があります。

実運用では、次の順で進める方が安全です。

  • まず SELECT COUNT(*) で対象件数を確認する
  • 削除前にバックアップや退避テーブルを作る
  • 即削除が危険なら、隔離テーブルへ移す

例えば退避するなら、次のような形です。

CREATE TABLE parent_table_invalid AS
SELECT *
FROM parent_table
WHERE attribute_id IS NULL;

DELETE FROM parent_table
WHERE attribute_id IS NULL;

2. 子テーブル側に ATTRIBUTE_ID を追加する

次に、子テーブルに新カラムを追加し、外部キーも張ります。

ALTER TABLE child_table
ADD COLUMN ATTRIBUTE_ID VARCHAR(45) NULL AFTER PARENT_ID,
ADD INDEX idx_child_attribute_id (ATTRIBUTE_ID);

ALTER TABLE child_table
ADD CONSTRAINT fk_child_attribute
  FOREIGN KEY (ATTRIBUTE_ID)
  REFERENCES attribute_master (ID)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

ここでは NULL 許容で追加しています。
最初から NOT NULL にしないのは、既存データの移行途中で壊さないためです。

3. 既存の属性を親から子へ移す

そのあと、親テーブルの属性を子テーブルへ移します。

UPDATE child_table AS c
JOIN parent_table AS p ON c.PARENT_ID = p.ID
JOIN attribute_master AS a ON p.ATTRIBUTE_ID = a.ID
SET c.ATTRIBUTE_ID = p.ATTRIBUTE_ID
WHERE p.ATTRIBUTE_ID IS NOT NULL;

マスタとJOINしているのは、参照先に存在しないIDをそのまま流し込まないためです。
移行時のSQLは、単なるコピーではなく、参照整合性のチェックも兼ねる方が安全です。

4. GROUP_KEY ベースで親を寄せ直す

今回の移行で一番重要なのはこの部分です。

子テーブルが参照している PARENT_ID を、GROUP_KEY ごとの最小 ID に寄せます。
つまり、同じ GROUP_KEY を持つ親を1つに寄せ、子をその下に集約しています。

CREATE TABLE tmp_min_parent_id AS
SELECT GROUP_KEY, MIN(ID) AS MIN_PARENT_ID
FROM parent_table
GROUP BY GROUP_KEY;

UPDATE child_table AS c
JOIN parent_table AS p ON c.PARENT_ID = p.ID
JOIN tmp_min_parent_id AS t ON p.GROUP_KEY = t.GROUP_KEY
SET c.PARENT_ID = t.MIN_PARENT_ID;

この処理で重要なのは次の点です。

  • 既存データはすでに重複した親を持っている
  • どの親に寄せるかの基準が必要になる
  • 今回は GROUP_KEY と最小 ID を基準にした

こういう移行では、SQLの書き方より「どのレコードを正とみなすか」の判定ルールの方が重要です。

5. 不要レコードと旧カラムを削る

親寄せが終わったら、不要なデータを削除できます。

例えば、子から参照されなくなった親は削除対象です。

DELETE p
FROM parent_table AS p
LEFT JOIN child_table AS c ON p.ID = c.PARENT_ID
WHERE c.PARENT_ID IS NULL;

NOT IN でも書けますが、NULL を含むと意図しない挙動になりやすいため、削除系では LEFT JOIN ... IS NULL の方が安全です。

その後、旧カラムも削除します。

ALTER TABLE parent_table
DROP COLUMN ATTRIBUTE_ID,
DROP INDEX idx_parent_attribute_id;

この順番を逆にすると、移行の確認がしづらくなります。
旧構造を消すのは、必ず新構造で読めることを確認した後です。

6. 最後に NOT NULL を付ける

移行後に ATTRIBUTE_ID IS NULL の子を削除し、最後に NOT NULL を付けます。

ALTER TABLE child_table
DROP FOREIGN KEY fk_child_attribute;

ALTER TABLE child_table
MODIFY ATTRIBUTE_ID VARCHAR(45) NOT NULL;

ALTER TABLE child_table
ADD CONSTRAINT fk_child_attribute
  FOREIGN KEY (ATTRIBUTE_ID)
  REFERENCES attribute_master (ID)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

制約強化は最後にやるのが基本です。
途中で制約を強くしすぎると、マイグレーション自体が進まなくなります。

大量データ移行で気を付けること

ここまでのSQLは考え方を説明するために単純化しています。
実際に本番で流すときは、SQLの正しさだけでは足りません。

特に気を付けたいのは次の点です。

  • 大量 UPDATE や DELETE によるロック時間
  • 一括更新によるトランザクション肥大化
  • 失敗時にどこまで戻せるか

件数が多い場合は、1回で更新しない方が安全です。
例えば ID 範囲や件数でバッチ分割して、少しずつ進めることがあります。

UPDATE child_table AS c
JOIN parent_table AS p ON c.PARENT_ID = p.ID
JOIN attribute_master AS a ON p.ATTRIBUTE_ID = a.ID
SET c.ATTRIBUTE_ID = p.ATTRIBUTE_ID
WHERE c.ID BETWEEN 1 AND 10000
  AND p.ATTRIBUTE_ID IS NOT NULL;

このときは、次もセットで考える必要があります。

  • 1バッチあたりの件数
  • 実行順序
  • 再実行しても壊れない条件
  • 途中失敗時の再開方法

DBリファクタリングはDDLだけの話ではなく、運用手順まで含めて設計する必要があります。

アプリケーションの切り替えタイミング

スキーマ変更以上に怖いのが、アプリケーションの切り替えタイミングです。
新旧構造が混在する期間をどう通すかを決めないと、DBだけ正しくてもアプリが壊れます。

ただし、ここで言う混在は、あくまで短期間の移行フェーズです。
新旧構造が長く共存する状態は、原則として避けるべきです。

長期的な混在がよくない理由は次のとおりです。

  • どちらの構造を正とするのか曖昧になる
  • 読み取りと書き込みの分岐が増える
  • 新旧の不整合を検知しにくくなる
  • 旧構造を消すタイミングを失いやすい

そのため、段階移行を採る場合でも、次を先に決めておく必要があります。

  • 一時的に混在させる期間
  • 新旧どちらを正とみなすか
  • 旧構造を廃止する条件
  • 切り戻しの条件

よくある進め方は次のとおりです。

  1. 新カラム追加後は旧構造を正としたまま動かす
  2. 移行期間だけトリガーなどで新旧の書き込みを同期する
  3. バックフィルで既存データを埋める
  4. 読み取りを新構造へ切り替える
  5. 旧カラム削除後に同期処理を止める

アプリケーションでダブルライトする方法もありますが、移行のためだけにアプリケーションコードへ分岐を入れると、責務が散りやすくなります。
そのため、短期の移行であれば、DBトリガーや同期用の補助処理で吸収する方が管理しやすい場面があります。

これを入れないと、移行期間中に新規登録されたデータだけ片側にしか入らず、整合性が崩れやすくなります。
ただし、トリガーによる同期も常態化してはいけません。
必要最小限の期間だけ使い、読み取り切り替えと旧構造削除までを移行計画に含める必要があります。

逆に、件数が少なく停止可能なシステムなら、メンテナンス時間を取って一気に切り替える方が単純な場合もあります。
どちらが良いかは、無停止を優先するか、運用の単純さを優先するかで変わります。

停止可能なら、短時間メンテナンスで一気に移行した方が設計としてはきれいです。
段階移行は便利ですが、混在期間を管理できる場合に限って選ぶべきです。

この変更でアプリケーション側は何が変わるか

DB変更だけでも大仕事ですが、実際にはアプリケーション側の修正範囲も広がります。

特に影響が出やすいのは次の箇所です。

  • Entityで親にあった ATTRIBUTE_ID を子側へ移す
  • 親から直接属性を読む処理を、子の集合を見る処理に変える
  • 1件前提だったDTOやAPIレスポンスを、子リスト前提に変える
  • 登録更新処理で、親保存後に子を更新する流れへ変える
  • 既存の検索SQLやJOIN条件を見直す

ここでつらいのは、DB上の1対1がコード上の単純な getter 設計と結びついていることです。
そのため、今回のような変更は単なるDDL変更ではなく、集約の持ち方そのものを変える修正になります。

移行計画を立てるときの考え方

この手のDBリファクタリングでは、次の順番で考えると整理しやすいです。

  1. 変更後の正しい関係を先に決める
  2. 既存データのうち、新構造に乗らないものを洗い出す
  3. 新カラム追加とデータ移行を分ける
  4. 参照先の付け替えを先に終わらせる
  5. 最後に旧カラムと不要データを削除する

特に重要なのは、「1対1を1対多に変える」という説明だけで終わらせないことです。
実際の移行では、次の論点を明文化しないと危険です。

  • どの親レコードを残すのか
  • 重複データをどうみなすのか
  • 不正データは削除か補正か
  • アプリケーション側の読取経路をいつ切り替えるのか
  • 同期をアプリで持つのか、トリガーで吸収するのか
  • バッチ分割とロールバックをどう設計するのか

このリファクタリングが実務で重い理由

1対1から1対多への変更は、見た目よりかなり重いです。

理由は次のとおりです。

  • スキーマ変更だけでは終わらない
  • 既存データの寄せ直しが必要になる
  • SQLのJOIN前提が変わる
  • アプリケーション側のオブジェクト構造も変わる
  • 途中状態を安全に通すための段階的移行が必要になる

逆に言うと、この種の変更を安全にやるには、最初から完璧なDDLを書くことより、段階を分けた移行計画を書く方が重要です。

まとめ

親テーブルと子テーブルの関係を1対1前提から1対多に変えるときは、属性を親から子へ移すだけでは足りません。
親子関係の寄せ直し、不要データの削除、制約の再設定、アプリケーション側の読み書き変更まで含めて考える必要があります。

今回の話で一番大事なのは、データ構造で表現すべきものをアプリケーション側で無理に吸収し続けないことです。
その状態を放置すると、個別機能ごとの補正が増え、最終的には全体の保守性と正確性を崩します。
深刻なバグを防ぐためにも、データ構造で表現できるものは、できるだけデータ構造で表現した方がよいです。

今回の流れを短くまとめると次のとおりです。

  1. 不正データを先に処理する
  2. 子テーブルへ新カラムを追加する
  3. 既存値を移行する
  4. 親参照を寄せ直す
  5. 旧カラムと不要データを削除する
  6. 最後に制約を強化する

DBリファクタリングは、きれいなER図を書く作業ではありません。
本番データを壊さずに、コードとスキーマの前提を少しずつ移し替える作業です。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?