こんにちは！
先日、業務でLaravelからMySQLに対してカラム追加のマイグレートを行ったところ、いつまで経っても終わらずに解決に苦労したことがあったので、同じ境遇の方に向けて書いてみようと思います。
今回は大容量のテーブルに対してマイグレートを行う場合の注意点と対策などを中心にお話ししていこうと思います。
「MySQLにマイグレート実行したけど、終わらない…」
「MySQLのカラム追加やカラム削除が終わらない…」
などお悩みの方は参考になるかと思います。
それでは、見ていきましょう！
原因
まず、大容量のテーブルに対してALTER TABLEを実行する場合は、細心の注意を払わなければなりません。
ALTER TABLEというのはテーブルの定義を行う際に実行するコマンドのことで、要するにカラム追加やカラム削除などですね。
ALTER TABLEの仕組み上、大容量のテーブルに対して定義変更する場合は非常に時間がかかる可能性があります。
では、ALTER TABLEでどのようなことを行なっているかというと、以下のようなことを行なっています。
- 対象のテーブルをロックする。この際、READのみ受け付け、WRITEを受け付けなくなる
- 空の一時テーブルを作成する
- 元のテーブルから一時テーブルへデータをコピーする
- 一時テーブルの名前を元テーブルと同じにする
- 元テーブルを破棄する
要するに、テーブルのコピーを取っているわけですね。
そして、テーブルが大容量であるほど、このコピーに時間がかかるわけです。
それでは、どうすれば速く定義変更することができるでしょうか？
解決策をみていきましょう。
解決策
INSTNATアルゴリズム
筆者が一番おすすめするのは、「INSTANTアルゴリズム」を使用して定義変更する方法です。
INSTANTアルゴリズムとは、メタデータの更新のみ行うことで負荷をかけずに、しかも高速でテーブル定義を変更するアルゴリズムのことです。
実行方法は、以下のようにALTER TABLE実行時にアルゴリズムを明示的に指定するだけです。
ALTER TABLE test ADD COLUMN test_id INT, ALGORITHM=INSTANT;MySQLのALTER TABLEには、以下の3種類のアルゴリズムがあります。
- COPY・・・テーブルをロックしている間にコピーして入れ替える。従来の手法。
- INPLACE・・・テーブルへの書き込みを許可した上で、コピーして入れ替える。時間がかかったとしてもテーブルはロックされず、稼働したまま定義変更ができる。オンラインDDLと呼ばれる。
- INSTANT・・・メタデータの更新のみ行う。負荷がかからず非常に速い。
中でも、INSTANTアルゴリズムはMySQL8.0.12のバージョンから追加されているもののようです。
ただし、INSTANTアルゴリズムには一定の制限があるようです。
具体的には以下のような操作しか行えません。
- インデックスオプションの変更
- テーブル名の変更
- SET/DROP DEFAULT
- MODIFY COLUMN
- virtual column の追加、削除
- カラム追加（制限あり）
カラム追加には以下の制限があります。
- INSTANTアルゴリズムがサポートされていないものと組む合わせて使えません
- テーブルの最後の列以外に追加する場合は使用できません
- ROW_FORMAT=COMPRESSED の場合は使用できません
- FULLTEXTインデックスを含むテーブルでは使用できません
- 一時テーブルには使用できません
- データディクショナリテーブルには使用できません
- 行サイズ制限はカラム追加時に評価されません
ただし、これらをクリアしていれば使用することができます。
筆者の場合は、65万行のテーブルに対してALTER TABLEを実行し、デフォルトでは4時間半程度待っても完了しなかったのが、わずか1秒未満で完了するようになりました。
素晴らしいですね👏
INSTANTアルゴリズムの条件を満たしていない場合は、INPLACEアルゴリズムの使用を検討してみると良いでしょう。
INPLACEアルゴリズムは時間がかかる可能性がありますが、バックグラウンドで実行しておけば、テーブルをロックしないので、稼働させたまま定義変更することができます。
カラム位置調整を外す
次は、Laravel視点での対策になります。
LaravelのマイグレーションファイルでALTER TABLEを実行する場合は、おそらくアルゴリズムの明示的な指定はできないと思います。（調査不足だったらすみませんm(_ _)m）
筆者の場合は以下のように、追加するカラムの位置に調整を加えていました。
$table->integer('status')->after('user_id')->comment('実行ステータス')->nullable();上記の場合だと、user_idの後ろにカラムを追加する、という意味になります。
ただし、カラム位置を指定するとどうやら遅くなってしまうようです。
筆者の場合は、このカラム位置調整を外したところ、かなりマイグレートが速くなりました。
カラム位置を調整している場合は、外してからマイグレートしてみると良いと思います。
まとめ
まとめると、以下のようになります。
- 大容量テーブルでALTER TABLEが遅いのは、テーブルをコピーしているから
- INSTANTアルゴリズムを使えば、高速でカラム追加など行える
- Laravelのマイグレーションはカラム位置指定なしの方が速い
筆者自身この解決にかなり時間を要したので、同じ壁にぶつかっている方は参考になれば嬉しいです。
これを教訓にDDLを実行する際は気をつけていきたいですね！
それでは、今日はここまで！
参考
コメント