大量データが入ったMySQLのテーブルになんとかカラムを追加する

mysql rdbms

オンラインのMySQLで、案件数100万件のレコードが入っていてかつWRITEが頻繁に発生しているテーブルに 新たにカラムを追加する必要があり、なんとか解決した話。

Alterでのカラム変更の場合、テーブルにWriteロックがかかってしまう。また、Alterでカラム変更を行うと、 レコードの数に比例した時間が必要となってしまう。そのため、数100万規模のレコードがすでに存在する既存の テーブルの場合、結構な時間Writeロックがかかってしまい、その間オンラインから発生する更新操作ができなくなってしまう。

ということで、Alterクエリ以外でオンラインへの影響が少ないカラム追加を次のような方法で行いました。

・既存のテーブル〜<table>

・カラムを追加した新しいテーブル〜<newtable>

  1. 新たなテーブル定義の<newtable> ( <table> に新たにカラムを追加したテーブル)を作成する。
  2. から特定の登録日時までのレコードを<newtable>に移行する。

    具体的にはinsert 〜 select 〜 クエリを利用した。

    insert into <newtable> (c1, c2, ...) select c1, c2, ... from <table> where created_at < '2016-01-10 00:00:00';
    1. 2つのテーブル1トランザクションで一度にリネームを行うことで、一貫性を保つ。<table>→<oldtable>、<newtable>→<table> このタイミングから、カラムが追加されている新しいテーブルにレコードが登録されるようになる。

    参照 MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.32 RENAME TABLE 構文

    rename table <table> to <oldtable>, <newtable> to <table>;

    あとはのんびり古いテーブルに残っているレコードを移行するだけ。

    1. <oldtable>から 2で移行していない残りのレコードをRename後の<table>に移行する。

    というわけで、無事にオンラインに影響なくカラム追加ができました。

    めでたし、それではm(__)m