本記事は、MySQL 8.0 INSTANT ADD and DROP Column(s)の翻訳版です。
背景
データベースでは、行はディスク上に領域が確保され、行ごとに列の値がディスクに保存されます。テーブルに対し新しい列の追加あるいは古い列の削除をする際は、テーブルを構成する列の正しい値を反映するために、既存の行の内容を変更します。また行数が増えると、列追加・削除によるテーブル定義変更を反映するために既存の行を全て修正するのに時間がかかるようになります。
これについてはMySQLのデフォルトのストレージ・エンジンInnoDBも同様です。そのため、MySQLユーザーの方々から寄せられるご意見の中に、InnoDBテーブルに対する新規・既存カラムの追加・削除をもっと高速化できないかというリクエストがありました。
初期実装
列追加・削除の実行時にテーブル内の全ての既存レコードを変更する必要があることがこの問題を難しくさせていました。そこで、この「必要性」を省略しメタデータの変更のみで列追加・削除を完了させるという案が採用されました。言い換えれば、行には一切触れずメタデータのみ更新するという解決策です。
そこで、ALGORITHM=INSTANTを指定した列追加がMySQL 8.0.12で採用されました。この機能を使用すると、ユーザーはテーブルのサイズに関係なく、テーブルへの新しい列の追加が瞬時に完了します。
以下は、MySQL 8.0.28(本記事でご紹介する機能の改良が行われる前の実装)で実行したALTER TABLE … ADD COLUMNの動作例です。
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.28 Source distribution Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)); Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t1 ADD COLUMN c3 CHAR(10), ALGORITHM=INSTANT; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
この初期実装の段階では、まだ下記のような改善の余地がありました。
- ALGORITHM=INSTANTと指定した場合、新しい列はテーブルの最後列にのみ追加可能
- ALGORITHM=INSTANTはDROP COLUMNではサポートされていなかったため、列削除の際はテーブルの再構築が必要
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.28 Source distribution Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)); Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t1 ADD COLUMN c3 CHAR(10) AFTER c1, ALGORITHM=INSTANT; ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. mysql> ALTER TABLE t1 ADD COLUMN c3 CHAR(10) FIRST, ALGORITHM=INSTANT; ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. mysql>ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
改良
開発チームはこの機能をより良いものにするべく、DROP COLUMNもALGORITHM=INSTANTで即時完了できるよう設計を見直し、INSTANT ADDの制限についても再検討しました。その結果、ALGORITHM=INSTANTでユーザーがテーブルの任意の位置から列削除が可能になり、列追加も同様に任意の位置に列を追加する際にALGORITHM=INSTANTを使用できるようになりました。
8.0.29から実装された機能をまとめると次のようになります。
- ALGORITHM=INSTANT を使用してテーブルの任意の位置に新しい列を追加可能
- ALGORITHM=INSTANT を使用してテーブルの任意の位置から既存の列を削除可能
「どの行にも触れずメタデータのみを更新する」という当初のコンセプトは変わりません。列追加・削除にかかる時間はテーブルの大きさに依存しないので、例えば1行のテーブルに列追加・削除するのと、1億行のテーブルに列追加・削除するのとでは実行完了にかかる時間は変わらないと言えます。
基本の構文
ALTER TABLE <テーブル名> ADD COLUMN <列名> <列の型> [DEFAULT デフォルト値] [FIRST]/[AFTER 列名], ALGORITHM=INSTANT;
ALTER TABLE <テーブル名> DROP COLUMN <列名>, ALGORITHM=INSTANT;
デフォルトでは全ての列追加・削除がALGORITHM=INSTANTで実行されます。上の例ではALGORITHM=INSTANTを明示的に記述しています。
また、単一のALTER TABLE文で複数の列を追加・削除できます。
仕組み
この機能を実現するために、行のバージョン管理に関する考え方がテーブル・メタデータに導入されました。具体的には、行が挿入されるときに、テーブル・メタデータ内に「行バージョン」が記録されます。テーブルがALTER TABLE … ADD/DROP COLUMNを実行していないなければ、テーブル内の全ての行の行バージョンは「0」となります。従来よりレコードのヘッダーには4ビットのメタデータ「info-bits」があり、そのうちの1ビットは未使用だったので、このビットを使用することでレコードが行バージョンを持っていることを示せるようにしました。
デフォルトではこのビットには何もセットされていませんが、ビットが立つと行のバージョン番号がレコードのヘッダーに格納されます。これによりテーブルの再構築なしでALTER TABLE … ADD/DROP COLUMNを実行し、レコード・ヘッダーで行バージョンを管理できます。残りのビットは従来通りに機能するので、INSTANT ADD/DROPなどのDDL操作は行バージョンを記録しているテーブルでも正常に動作します。
レコード・ヘッダーに格納される行バージョンの番号は、ALTER TABLE … ADD/DROP COLUMNを実行する毎に更新されます。ALTER TABLE文の実行後に挿入された新しい行には、同じ番号の行バージョンが記録されます。ALTER TABLE … ADD/DROP COLUMNを複数回実行したテーブルには、異なる行バージョンを持つ行が存在することになります。このテーブルから行がフェッチされると、記録されている行バージョンから、テーブル・メタデータ内の最新の行バージョン番号に更新されます。この行バージョンはテーブル・メタデータなのに対し、レコードに含まれる行バージョンは行のメタデータです。
行バージョンは追加・削除された列の数には関係なく、ALTER TABLE文の実行の回数で更新されます。例えばALTER TABLE … ADD/DROP COLUMNでn列が追加され、m列が削除された場合、行バージョンの番号は+1増加します。
行バージョンの制限事項
このような行バージョンの更新と維持にはバックグラウンドでのメンテナンスが必要なので、制限事項を設けています。行バージョンとして管理できるのは、最大64バージョンです。つまり一つのテーブルに対して最大64回のALTER TABLE … ADD/DROP操作が可能であるということです。上限に達した後にALTER TABLE … ADD/DROPを実行するには、一度テーブルの再構築が必要になります。この機能の開発当時、列追加・削除は頻繁に発生する操作ではないと仮定していたため、テーブルの再構築なしで列追加・削除できる上限を64回と設定しました。この数字はユーザーが変更可能な変数ではなく、固定されています。
上限に達した後にALGORITHM=INSTANTを明示して列追加・削除を実行すると、下記のエラーが発生します。
注: 以降の実行例ではMySQL8.3.0を使用しています。
ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
このエラーはALTER TABLE文でALGORITHM=INSTANTが明示的に使用されている場合にのみ出ます。明示的に指定しない場合、上限の64回まではINSTANTアルゴリズムが暗黙的に使用され、その後は暗黙的にALGORITHM=INPLACEを使用します。
可視性
関連するインフォメーション・スキーマ
メタデータを表示するためのインフォメーション・スキーマINFORMATION_SCHEMA.INNODB_TABLESの、TOTAL_ROW_VERSIONS列を確認することで、各テーブルの現在の行バージョンが分かります。
mysql> CREATE TABLE t1 (c1 CHAR(10)); Query OK, 0 rows affected (0.01 sec) mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%"; +---------+--------------------+ | NAME | TOTAL_ROW_VERSIONS | +---------+--------------------+ | test/t1 | 0 | +---------+--------------------+ 1 row in set (0.05 sec) mysql> ALTER TABLE t1 ADD COLUMN c0 CHAR(10) FIRST, ALGORITHM=INSTANT; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%"; +---------+--------------------+ | NAME | TOTAL_ROW_VERSIONS | +---------+--------------------+ | test/t1 | 1 | +---------+--------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%"; +---------+--------------------+ | NAME | TOTAL_ROW_VERSIONS | +---------+--------------------+ | test/t1 | 2 | +---------+--------------------+ 1 row in set (0.01 sec)
テーブル再構築と全データ削除
テーブル再構築を実行するALTER TABLE操作(例: OPTIMIZE TABLE)もあります。 テーブルが再構築されるとINSTANTのメタデータは初期状態に戻るので、テーブルの行バージョンはINSTANT ADD/DROP COLUMNを一度も実行していない場合と同様になります。
mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%"; +---------+--------------------+ | NAME | TOTAL_ROW_VERSIONS | +---------+--------------------+ | test/t1 | 2 | +---------+--------------------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE t1; +---------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+----------+----------+-------------------------------------------------------------------+ | test.t1 | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.t1 | optimize | status | OK | +---------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.02 sec) mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%"; +---------+--------------------+ | NAME | TOTAL_ROW_VERSIONS | +---------+--------------------+ | test/t1 | 0 | +---------+--------------------+ 1 row in set (0.00 sec)
TRUNCATE TABLEを実行するとテーブルに行が残ってない状態になるため、新しく作成したテーブルと同様に行バージョンは0になります。
SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%"; +---------+--------------------+ | NAME | TOTAL_ROW_VERSIONS | +---------+--------------------+ | test/t1 | 2 | +---------+--------------------+ 1 row in set (0.00 sec) mysql> TRUNCATE TABLE t1; Query OK, 0 rows affected (0.01 sec) mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%"; +---------+--------------------+ | NAME | TOTAL_ROW_VERSIONS | +---------+--------------------+ | test/t1 | 0 | +---------+--------------------+ 1 row in set (0.00 sec)
実行時間の比較
ALGORITHM=COPYを指定した場合とINSTANTを指定した場合で比較すると、列追加の実行時間が格段に高速なことが分かります。
注: 下記の実施例ではMySQL公式Webでご紹介しているサンプルデータベースairportdbを使用しています。
mysql> SELECT COUNT(*) FROM booking; +----------+ | COUNT(*) | +----------+ | 54304619 | +----------+ 1 row in set (0.73 sec) mysql> ALTER TABLE booking ADD COLUMN c1 CHAR(10), ALGORITHM=COPY; Query OK, 54304619 rows affected (7 min 58.01 sec) Records: 54304619 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE booking ADD COLUMN c2 CHAR(10), ALGORITHM=INSTANT; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
制限事項
ALGORITHM=INSTANTを使用したテーブルへの列追加・削除の操作は、下記のテーブルでは未サポートです。
- FTSインデックスを持つテーブル
- 圧縮テーブル(row_format=compressed)
- 一時テーブル
参考情報
ALGORITHM=INSTANTに関するMySQL公式Webマニュアルはこちらです
ALGORITHM=INSTANTを使用した列追加・削除についてはMySQL 8.0.32以降を使用することをお勧めします。