SEEDS Creator's Blog

【MYSQL】サイズの大きなテーブルに対して素早く属性変更を行う方法

テーブルにインデックスやカラムを追加したりする場合、ALTER TABLE、CREATE INDEXを利用すると思いますが、サイズの大きなテーブルに対して実行する場合、時間がかかり、テーブルもロックされてしまうために、状況によっては安易に実行することはできません。(これらのDDLは、データを全コピーしなおすような仕組みになっているので、時間がかかるようです。)

今回は、この作業を短時間で実行する方法をご紹介したいと思います。 ざっくり言うと、属性追加後のテーブルを新規で作成して、データを流し込み、名称変更で完了です。

1.テーブル「A」に(属性追加など)変更を加えたテーブル「A'」を新規作成

create table A';

2.「A」から「A'」にデータコピー

insert into A' select * from A;

※データ量に応じて、where句で分割挿入するなど工夫が必要

3.「A」を「A_bk」にリネーム

alter table A rename to A_bk;

4.「A'」を「A」にリネーム

alter table A' rename to A;

あとは、コピー元のテーブル「A_bk」を削除すればOKです。

テーブル名称の変更は瞬間で終わりますし、データ量の多いテーブルの属性変更が短時間で行えます。それまでの事前準備(1~3)に時間はかかりますが・・・ ただ、今回の手法では、2に時間がかかりますので、(更新頻度が高い)トランザクション系のテーブルに対して適用するのは難しいと思われます。あくまで、(更新頻度が低い)マスタ系の切り替え時に有効な手法だと思います。