第236回　MySQLで即時に完了するNative ALTER TABLE操作のまとめ

MySQLでは、いくつかのALTER TABLEの操作は即時に完了できます。MySQL 8.0から導入されたInstant DDL機能や、メタデータの変更のみで完結する機能により、そのような操作が可能になりました。今回は、MySQLで即時に完了するALTER TABLEの操作についてまとめてみようと思います。

この記事は、17.12.1 Online DDL Operationsのドキュメントをもとにまとめています。ドキュメント内に記載されている表で、InstantまたはOnly Modifies MetadataがYesになっているものが即時に完了する操作です。InstantはMySQL 8.0で追加されたアルゴリズムです。データディクショナリのメタデータのみを変更するため、操作は瞬時に完了されます。Only Modifies Metadataはテーブルのメタデータのみ変更する操作となり、こちらも操作は瞬時に完了されます。

カラムの操作

ここでは即時に完了するカラムに関連する操作を紹介します。

カラムの追加と削除

mysql> ALTER TABLE t0 ADD col1 int;
mysql> ALTER TABLE t0 ADD col2 int AFTER col1;

MySQL 8.0とそれ以降から、テーブルの最後にカラムを追加する操作がInstantで実行可能です。これは、AFTER句を指定しないALTER TABLEステートメントの場合です。MySQL 8.0.30からは、AFTER句を用いたテーブルの他のカラム間の位置にカラムを追加する操作が、Instantで実行可能になりました。

mysql> ALTER TABLE t0 DROP COLUMN col1;

MySQL 8.0.30からカラムの削除操作がInstantで実行可能です。

Instantによるカラムの追加と削除は、操作ごとにインクリメントされる行バージョンを管理します。この行バージョンの最大値はMySQL 9.1およびそれ以降は255、MySQL 9.0以前では64となっています。この最大値に達した場合は、Instantによるカラム追加と削除の際にエラーが返されます。行バージョンはOPTIMIZE TABLEやテーブルの再構築を行うことでリセットされるため、テーブルを再構築する必要があります。

Instantで実行できないカラム追加と削除には、以下のようなものがあります。

ROW_FORMAT=COMPRESSEDを使用するテーブル（テーブル圧縮）
FULLTEXTインデックスを持つテーブル
関数インデックスを持つカラムの追加

カラムの名前の変更

mysql> ALTER TABLE t0 RENAME COLUMN old_col1 TO new_col1;

カラム名の変更操作はInstantで実行可能です。

カラムのデフォルト値変更と削除

mysql> ALTER TABLE t0 ALTER COLUMN col1 SET DEFAULT 0;
mysql> ALTER TABLE t0 ALTER COLUMN col1 DROP DEFAULT;

カラムのデフォルト値変更と削除操作は、テーブルのメタデータのみの変更で即時に実行できます。

varchar型のサイズ拡張

mysql> ALTER TABLE t0 CHANGE col1 col1 VARCHAR(255);

VARCHAR型のサイズ拡張操作がすべて即時に完了するわけではありません。VARCHAR型のサイズが0～255バイト間での拡張、または256バイト以上での拡張であれば、テーブルのメタデータのみの変更で対応可能です。しかし、元のカラムが0～255バイトで、それを256バイト以上に拡張すると、テーブルリビルド（ALGORITHM=COPY）が必要となり、コストが高い操作になります。

注意点として、VARCHAR型の()内の数値はバイト数ではなく文字数です。MySQL 8.0からのデフォルトのキャラクターセットはutf8mb4であり、1文字を最大4バイトで表現します。そのため、255バイトはVARCHAR(63)に相当します。したがって、元のカラムがVARCHAR(63)である場合、VARCHAR(64)に拡張する際にはテーブルリビルドが発生します。

mysql> ALTER TABLE t1 CHANGE v v VARCHAR(64);
Query OK, 131072 rows affected (0.36 sec)
Records: 131072  Duplicates: 0  Warnings: 0

ENUMまたはSET型の定義の変更

mysql> CREATE TABLE t0 (col1 ENUM('a', 'b', 'c'));
mysql> ALTER TABLE t1 MODIFY COLUMN col1 ENUM('a', 'b', 'c', 'd'),

ENUMまたはSET列の定義を変更して、新しい列挙またはセットメンバーを有効なメンバー値のリストの末尾に追加する場合、データ型のストレージサイズが変わらない限り即時に実行できます。

SET型では、基本的に8つのメンバーごとに必要なストレージが増えるため、メンバーが8から9、16から17といった追加のタイミングでテーブルリビルド（ALGORITHM=COPY）が必要となり、コストが高い操作になります。

VIRTUALカラムの追加と削除

mysql> ALTER TABLE t0 ADD COLUMN (col2 INT GENERATED ALWAYS AS (col1 + 1) VIRTUAL);
mysql> ALTER TABLE t0 DROP COLUMN col2;

VIRTUALカラムの追加と削除は、パーティション化されていない、かつSTOREDではない場合はInstantで実行可能です。

テーブルや制約関連の操作

ここではで即時完了する制約関連の操作を紹介します。

インデックスの名前の変更

mysql> ALTER TABLE t0 RENAME INDEX old_index_name TO new_index_name;

インデックスの名前の変更操作は、テーブルのメタデータのみの変更で即時に実行できます。

外部キーの追加と削除

mysql> ALTER TABLE t0 ADD CONSTRAINT fk1 FOREIGN KEY index (col1) REFERENCES t1(col2);
mysql> ALTER TABLE t0 DROP FOREIGN KEY fk1;

外部キーの追加と削除操作はforeign_key_checksが無効になっている場合に即時に実行できます。それ以外の場合は、テーブルリビルド（ALGORITHM=COPY）のみがサポートされます。

foreign_key_checksとは外部キー制約の検査を有効、または無効にするためのシステム変数です。この変数をOFFにすることで、外部キーの制約を一時的に無視することができます。ただし、データの整合性が保証されなくなる可能性があるため、慎重に使用する必要があります。

mysql> SET foreign_key_checks=OFF;

テーブル名の変更

mysql> ALTER TABLE t0_old RENAME TO t0_new;

テーブルの名前変更操作はInstantで実行可能です。テーブルのコピーを作成せずに、テーブルに対応するファイルの名前を変更します。また、ALTER TABLE RENAME TOステートメントとRENAME TABLEステートメントは同等でありますが、RENAME TABLEステートメントは同時に複数のテーブルの名前変更が可能です。

まとめ

今回は即時に完了するNative ALTER TABLE操作をまとめてみました。今後、あなたのサービスでALTER TABLEを実行する際に、その操作によるサービスへの影響を最小限に抑えたいと考えている場合は、この記事と17.12.1 Online DDL Operationsドキュメントを参考にしてください。