Zenn
💭

[InnoDB]大量データを移行した際に起きたことと反省点

2024/09/18に公開
MySQL
DB
データ移行
InnoDB
tech

背景

私が担当しているシステムの中で最もレコード数が多いテーブルがあるのですが、今回そちらのテーブルから一部切り出す対応にようやく着手することになりました...！(念願)
その対応の中で新規テーブルに既存のテーブルのデータを移行することになり、その際にやったこと、起きたこと、反省点を記録します。

やったこと & 起きたこと

対象テーブルは以下の状態でした。
旧テーブルのレコード数：7500万件ほど
移行対象のレコード数：200万件ほど

該当システムは業務用システムのため、使用率の低い夜の時間帯にリペアを実行💪

INSERT-SELECTでデータ移行

INSERT-SELECT文を使って既存テーブルのデータをそのまま新テーブルに作成するぞ

  • 以下のようなクエリを実行 
    • INSERT INTO
    new_table (column1, column2, column3)
SELECT
    old_table.column1, old_table.column2, old_table.column3
FROM
    old_table
WHERE
    hogehoge = xxx
;
  • 3分ほどで実行完了
    • 想定通りではあるが結構時間かかった！

trancateで移行しなおし

上記リペアの条件に不備があることに気づき、データをいちから入れ直すことに><
移行先が新規テーブルかつまだ使われていなかったため、移行し直しが簡単に対応ができました...( ；∀；)

  • 以下クエリを実行 
    • TRUNCATE TABLE new_table;
    • しかし対象テーブルを外部キーとして参照しているテーブルがあるので以下エラーになった
      • Cannot truncate a table referenced in a foreign key constraint...
    • child_new_tableがnew_tableを外部キーとして参照していたためのエラーでした
    • child_new_tableも使われていないテーブルだったのでレコード数はゼロ
    • レコード数がゼロであっても、外部キー参照しているテーブルが存在するだけでtruncateはできないようでした😢
  • 一度外部キーを削除してtruncateし、その後再度外部キーを作成
    • child_new_tableもまだ使われていないテーブルだったためできた対応でした...

テーブルロックによる既存テーブルへのデータ作成失敗

データ移行は完了したが、テーブルロックによるデータインサート失敗のエラーが起きていたことを検知😱
INSERT-SELECT文は行ロックしかしないと思って進めてたのであせあせ。
とりあえずその場はシステムによる自動リトライのおかげでエラー分は無事インサートされていたことが確認できた👍

反省

確認クエリと実行クエリの差分を検知していなかった

移行し直しが必要になった理由は「確認クエリと実行クエリのWHERE条件が一致していなかった」ため。
確認クエリと実行クエリの結果の行数が一致しなかったのでクエリをよく見ると、条件に差分があった...。

次回以降は以下で同様の事象を回避します。

  • クエリレビューで確認クエリと実行クエリのWEHRE条件が一致しているかの確認を入れる
  • INSERT-SELECT文実行前にEXPLAINを必ず挟む 
    • EXPLAIN INSERT INTO
    new_table (column1, column2, column3)
SELECT
    old_table.column1, old_table.column2, old_table.column3
FROM
    old_table
WHERE
    hogehoge = xxx
;

INSERT-SELECT時にテーブルロックされることを考慮していなかった

旧テーブルに対してのinsertがロックにより失敗していた件><
改めてロックのルールを確認してみる。
ロックのルールはDBエンジンによって異なるみたいなのですが、今回はInnoDBでした。
公式によると以下とのこと。

INSERT INTO T SELECT ... FROM S WHERE ... では、T に挿入された各行に排他的インデックスレコードロック (ギャップロックなし) が設定されます。 トランザクション分離レベルが READ COMMITTED の場合、InnoDB は S で一貫性読取り (ロックなし) として検索を実行します。 それ以外の場合、InnoDB は S から取得した行に共有ネクストキーロックを設定します。

やはり条件に一致した対象の行のみロックされるんではないの？🤔

調べてみると、どうやらwhere句の条件に使用しているカラムにindexが貼られていない場合、フルテーブルスキャンになりテーブルロックになるみたい。(参照)

今回where句に使用したカラムのindexを見てみると、indexを貼られていないのは論理削除を管理するdeleted_atカラムのみでした
deleted_at is not nullの条件を使用していたのですが、これが原因でフルテーブルスキャンになったのかもしれない🤔

index貼るか貼らないかを判断する際検索条件として使われるかを意識することが多いのですが、こういったデータリペアの条件に使われる可能性も考慮する必要があるのだと学びました...！

補足で、Laravelの論理削除機能に使われるdeleted_atは罠があるかもしれない、、、と感じたので別途どういった危険があるかを整理したいです。

感想

🐈

参考

補足

いただいたご意見

単にロックしたくないだけなら、下記で一時的に分離レベルを変えるのもありかと思いました。
（自分の接続だけ影響受けるので、本番が変更されることはないです。）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Discussion