今日は、MySQL 8.0.29で一部制限が解放されたALGORITHM=INSTANTの日。
目次
とある日
MySQL8.0.29がリリースされました。
おめでとうございます。パチパチ。
MySQL ::MySQL8.0リリースノート::MySQL8.0.29での変更（2022-04-26、一般提供）
その中で、追加された機能で面白そうなALGORITHM=INSTANTについて検証した内容をまとめてます。
ALGORITHM=INSTANTとは？
ALTER TABLEなどでカラムを追加するときにつけるとメタデータの更新だけを行うので高速に再構成ができる様になるものです。
本記事では、8.0.29で追加された要素に付いてだけ触れます。
そのため、ALGORITHM=INSTANTの性能などは下記の記事をご覧ください。
MySQL 8.0 の INSTANT DDL について | スマートスタイル TECH BLOG
MySQL :: MySQL 8.0：InnoDBがInstantADDCOLUMNをサポートするようになりました
しかし、この機能には下記のような制限があります。
- カラムの追加は、
ALGORITHM=INSTANTをサポートしない他のALTER TABLEアクションと同じステートメントで組み合せることはできません。- カラムは、テーブルの最後のカラムとしてのみ追加できます。 他のカラム間の他の位置へのカラムの追加はサポートされていません。
ROW_FORMAT=COMPRESSEDを使用するテーブルにはカラムを追加できません。FULLTEXTインデックスを含むテーブルにはカラムを追加できません。- カラムは一時テーブルに追加できません。 一時テーブルでは、
ALGORITHM=COPYのみがサポートされます。- データディクショナリテーブルスペースに存在するテーブルにはカラムを追加できません。
- 行サイズ制限は、カラムの追加時には評価されません。 ただし、行サイズ制限は、テーブルの行を挿入および更新する DML 操作中にチェックされます。
8.0.29の変更について
では、改めて今回のアップデート内容について。
InnoDB： を使用した操作を InnoDBサポートするよう ました。 ALTER TABLE ... DROP COLUMNALGORITHM=INSTANT
ALGORITHM=INSTANT データディクショナリのメタデータの変更のみ をサポートする操作。操作の準備段階と実行段階では、テーブルに対して排他的なメタデータロックは取得されず、テーブルデータは影響を受けないため、操作は瞬時に行われます。明示的に指定されていない場合、ALGORITHM=INSTANTそれをサポートするDDL操作によってデフォルトで使用されます。
MySQL 8.0.29より前では、即座に追加される列は、テーブルの最後の列としてのみ追加できました。MySQL 8.0.29から、即座に追加された列をテーブルの任意の位置に追加できます。
即座に追加または削除された列は、新しい行バージョンを作成します。最大64行のバージョンが許可されます。 行バージョンの数を追跡する ために、新しい TOTAL_ROW_VERSIONS列がテーブルに追加されました 。INFORMATION_SCHEMA.INNODB_TABLES
サポートするDDL操作の詳細について は、「オンラインDDL操作ALGORITHM=INSTANT」を参照 してください。
要点をまとめると下記のとおりです。
- DROP COLUMNALにもALGORITHM=INSTANTが追加
- ALGORITHM=INSTANTの制限が一部解禁
DROP COLUMNALにもALGORITHM=INSTANTが追加
InnoDB： を使用した操作を InnoDBサポートするよう ました。 ALTER TABLE ... DROP COLUMNALGORITHM=INSTANT
ALGORITHM=INSTANTを指定できる場合と指定できない場合があります。
下記はサポート表の一部です。
| 操作 | インスタント | インプレース | テーブルの再構築 | 同時 DML の許可 | メタデータの変更のみ |
|---|---|---|---|---|---|
| カラムの追加 | はい* | はい | いいえ* | はい* | いいえ |
| カラムの削除 | いいえ | はい | はい | はい | いいえ |
| カラム名の変更 | いいえ | はい | いいえ | はい* | はい |
| カラムの並替え | いいえ | はい | はい | はい | いいえ |
| カラムのデフォルト値の設定 | はい | はい | いいえ | はい | はい |
| カラムのデータ型の変更 | いいえ | いいえ | はい | いいえ | いいえ |
VARCHAR カラムサイズの拡張 |
いいえ | はい | いいえ | はい | はい |
| カラムのデフォルト値の削除 | はい | はい | いいえ | はい | はい |
| 自動インクリメント値の変更 | いいえ | はい | いいえ | はい | いいえ* |
カラムの NULL 化 |
いいえ | はい | はい* | はい | いいえ |
カラムの NOT NULL 化 |
いいえ | はい* | はい* | はい | いいえ |
ENUM または SET カラムの定義の変更 |
はい | はい | いいえ | はい | はい |
その他の項目は下記をご覧ください。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.12.1 オンライン DDL 操作
カラム追加するときは、ALGORITHM=INSTANTが使えますが、カラム削除するときは使えません。
ALGORITHM=INSTANTの制限が一部解禁
MySQL 8.0.29より前では、即座に追加される列は、テーブルの最後の列としてのみ追加できました。MySQL 8.0.29から、即座に追加された列をテーブルの任意の位置に追加できます。
ALGORITHM=INSTANTが指定できる場所には制限がありました。
ALGORITHM=INSTANTの制限のうち今回のリリースでは、下記が解禁されました。
「カラムは、テーブルの最後のカラムとしてのみ追加できます。 他のカラム間の他の位置へのカラムの追加はサポートされていません。」
つまり、いままで末尾にカラムを追加するときしか使えなかったALGORITHM=INSTANTが間に追加する際にも使用できるようになりました。
検証
では、追加された2つについて検証してみます。
検証は下記バージョンで実施します。
- 8.0.28
- 8.0.29
create table test_INSTANT( a int primary key auto_increment, b char(8) not null , c int not null );
(root@localhost) [test] 8.0.29 > desc test_INSTANT; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | a | int | NO | PRI | NULL | auto_increment | | b | char(8) | NO | | NULL | | | c | int | NO | | NULL | | +-------+---------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
DROP COLUMNALにもALGORITHM=INSTANTが追加
dカラムを削除します。
(root@localhost) [test_database] 8.0.28 > alter table test_INSTANT add column d int, algorithm=instant; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test_database] 8.0.28 > desc test_INSTANT; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | a | int | NO | PRI | NULL | auto_increment | | b | char(8) | NO | | NULL | | | c | int | NO | | NULL | | | d | int | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
8.0.28
(root@localhost) [test_database] 8.0.28 > alter table test_INSTANT drop d, algorithm=instant; ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. (root@localhost) [test_database] 8.0.28 >
消そうとするとエラーになります。
サポートされてないよって。
8.0.29
(root@localhost) [test] 8.0.29 > desc test_INSTANT; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | a | int | NO | PRI | NULL | auto_increment | | b | char(8) | NO | | NULL | | | d | int | YES | | NULL | | | c | int | NO | | NULL | | +-------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
実行します。
(root@localhost) [test] 8.0.29 > alter table test_INSTANT drop d, algorithm=instant; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test] 8.0.29 > desc test_INSTANT; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | a | int | NO | PRI | NULL | auto_increment | | b | char(8) | NO | | NULL | | | c | int | NO | | NULL | | +-------+---------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
問題なく削除されたことを確認しました。
ALGORITHM=INSTANTの制限が一部解禁
まずは、カラム位置を指定しないで実行して、その後にカラム位置を指定して実行しています。
8.0.28
カラム位置を指定しない場合。
(root@localhost) [test_database] 8.0.28 > ALTER TABLE test_INSTANT ADD d int, algorithm=instant; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test_database] 8.0.28 > desc test_INSTANT; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | a | int | NO | PRI | NULL | auto_increment | | b | char(8) | NO | | NULL | | | c | int | NO | | NULL | | | d | int | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
カラム位置を指定する場合。
(root@localhost) [test_database] 8.0.28 > desc test_INSTANT; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | a | int | NO | PRI | NULL | auto_increment | | b | char(8) | NO | | NULL | | | c | int | NO | | NULL | | +-------+---------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) (root@localhost) [test_database] 8.0.28 > ALTER TABLE test_INSTANT ADD d int AFTER b, algorithm=instant; ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. (root@localhost) [test_database] 8.0.28 >
こちらもエラーになります。
8.0.29
カラム位置を指定しない場合。
(root@localhost) [test] 8.0.29 > ALTER TABLE test_INSTANT ADD d int, algorithm=instant; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test] 8.0.29 > desc test_INSTANT; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | a | int | NO | PRI | NULL | auto_increment | | b | char(8) | NO | | NULL | | | c | int | NO | | NULL | | | d | int | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
8.0.28と同じように、末尾に追加されています。
カラム位置を指定する場合。
(root@localhost) [test] 8.0.29 > ALTER TABLE test_INSTANT ADD d int AFTER b, algorithm=instant; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test] 8.0.29 > desc test_INSTANT; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | a | int | NO | PRI | NULL | auto_increment | | b | char(8) | NO | | NULL | | | d | int | YES | | NULL | | | c | int | NO | | NULL | | +-------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
dカラムがbカラムのあとに追加されていることが確認できました。
〆
機能のサポートが増えていくことは素晴らしいですね。
今回のALGORITHM=INSTANTは知らなかった機能で調べると便利そうだったので速度検証している下記の記事は必見です。
MySQL 8.0 の INSTANT DDL について | スマートスタイル TECH BLOG
参考記事
第30回 InnoDBオンラインDDLについて：MySQL道普請便り｜gihyo.jp … 技術評論社
MySQL 8.0 の INSTANT DDL について | スマートスタイル TECH BLOG
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.11.1 オンライン DDL の概要
