タダです.
DB マイグレーションなどで ALTER TABLE ステートメントを実行する機会は多いと思いますが,自分は ALGORITHM 指定した実行形式を意識して使ったことがこれまでありませんでした.今回実行形式に触れる機会があったので学んだメモをまとめます.
ALTER TABLE ステートメントにおける実行形式
MySQL において ALTER TABLE ステートメントの実行形式は3つあります.
COPYINPLACEINSTANT
COPY
COPY は新しいテーブル定義で一時テーブルを作成し,既存データをコピーして入れ替えます.ALTER TABLE ステートメント実行中テーブルをロックするため,実行中は書き込みがブロックされます.大規模テーブルへのロックがかかると,サービス利用に影響も出てしまうのでメンテナンスも必要になります.利用するケースとして主キーの削除やデータ型の変更など INPLACE や INSTANT では対応できない操作が挙げられます.
INPLACE
INPLACE は既存のテーブルファイル内で変更を行います.テーブルの再構築を伴うものとそうじゃないものがありますが,再構築を行うテーブルサイズに比例して実行時間は長くなります.利用するケースとしてカラム追加など多くの操作で使用可能ですが,実行時間の長さもあるため後述の INSTANT を使えるケースは避けたい印象です.
INSTANT
INSTANT はMySQL 8.0.12以降で使用可能になっており,メタデータの更新のみを行い,テーブルサイズに関係なく即時にに完了するオペレーションです.利用ケースとしてカラムの追加,カラム名の変更,インデックスの削除などがあります.MySQL 8.0.30 以前はカラムの追加は最後のカラムに追加する場合は問題ないですが,AFTER や FIRST 句を使ってカラム指定する場合は INSTANT を使用できませんでした.ただ,MySQL 8.0.30 以降からは中間カラムを追加するやカラムの削除の際もINSTANT を使用可能になりました.なお, INSTANT による ALTER TABLE ステートメントの実行ではカラムの追加や削除のたびにテーブルの行バージョンが作成され,行バージョンには64という制限があります.ドキュメントに記載の通り上限を超えるとエラーが出て COPY または INPLACE を使用する必要がでてきます.
When a table with instantly added or dropped columns is rebuilt by table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64 (255 as of MySQL 9.1.0), as each row version requires additional space for table metadata.
エラーメッセージ
ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
どの方式を利用するかの検討
速度と影響の観点から比較すると, INSTANT → INPLACE → COPY の順に利用すると思います.ALTER TABLE ステートメントで特に実行形式を指定しない場合,MySQL はINSTANT → INPLACE → COPY を自動的に選択します.不安がある場合は明示的に以下のように明示的に ALGORITHM = INSTANT などのように指定するとよいでしょう.
参考情報
まとめ
ALTER TABLE ステートメントの実行形式をまとめました.
