MySQL 8.0.12 はマイナーバージョンアップですが、ALTER TABLE でカラムを追加する際のアルゴリズムに「INSTANT」が追加されました。
MySQL 8.0: InnoDB now supports Instant ADD COLUMN | MySQL Server Blog
ALGORITHM=INSTANT とは？
従来の「COPY」や「INPLACE」と異なり、メタデータの更新だけ行うことで高速かつ負荷をかけずにカラムの追加などが行えるようになりました。
ただし、使える範囲は限定的で下記のような操作のみになります。
- インデックスオプションの変更
- テーブル名の変更
- SET/DROP DEFAULT
- MODIFY COLUMN
- virtual column の追加、削除
- カラム追加（制限あり）
カラム追加であっても、以下のような制限があります。
- INSTANTアルゴリズムがサポートされていないものと組む合わせて使えません
- テーブルの最後の列以外に追加する場合は使用できません
- ROW_FORMAT=COMPRESSED の場合は使用できません
- FULLTEXTインデックスを含むテーブルでは使用できません
- 一時テーブルには使用できません
- データディクショナリテーブルには使用できません
- 行サイズ制限はカラム追加時に評価されません
ALTER TABLE で ALGORITHM を指定しない場合、INSTANT -> INPLACE -> COPY の順にトライしていきます。
MySQL :: MySQL 8.0 Reference Manual :: 15.12.1 Online DDL Operations
アルゴリズムによる速度差を検証する
ここではテストデータを使ってアルゴリズムの違いによる速度差を計測していきたいと思います。
テストデータの作成
ベンチマークツールの sysbench を使って100万件のデータを投入しました。
|
1 2 3 4 5 6 7 |
mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.40 sec) |
カラムを追加する
COPY アルゴリズム
|
1 2 3 |
mysql> alter table sbtest1 add column m1 int, algorithm=copy; Query OK, 1000000 rows affected (21.70 sec) Records: 1000000 Duplicates: 0 Warnings: 0 |
実行完了まで 21.70 秒かかりました。
INPLACE アルゴリズム
|
1 2 3 |
mysql> alter table sbtest1 add column m2 int, algorithm=inplace; Query OK, 0 rows affected (6.54 sec) Records: 0 Duplicates: 0 Warnings: 0 |
実行完了まで 6.54 秒と COPY アルゴリズムに比べるとかなり高速です。
INSTANT アルゴリズム
|
1 2 3 |
mysql> alter table sbtest1 add column m3 int, algorithm=instant; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 |
実行完了まで 0.05秒 になりました。
default を含む場合のカラム追加で INSTANT アルゴリズム
|
1 2 3 |
mysql> alter table sbtest1 add column m5 varchar(255) not null default 'abcd', algorithm=instant; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 |
0.11秒と相変わらず高速でした。
データを見るとちゃんとデフォルト値が入っています。
|
1 2 3 4 5 6 7 8 9 |
mysql> select m5 from sbtest1 limit 3; +------+ | m5 | +------+ | abcd | | abcd | | abcd | +------+ 3 rows in set (0.00 sec) |
カラム追加にかかった時間を比較すると以下の通りです。
| アルゴリズム | 処理時間 |
|---|---|
| COPY | 21.70 秒 |
| INPLACE | 6.54 秒 |
| INSTANT | 0.05 秒 |
対応していない操作で INSTANT を指定した場合
INSTANT アルゴリズムに対応していない DROP COLUMN でこのアルゴリズムを指定した場合
|
1 2 |
mysql> alter table sbtest1 drop column m3, algorithm=instant; ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. |
フォールバックして COPY や INPLACE が使われるわけではなくエラーを出力して停止してくれました。
まとめ
MySQL 5.6 からオンラインDDLに既に対応していましたが、レコード数が非常に多いテーブルなどではDDL実行中の負荷や実行時間を考慮して、負荷の少ない夜間やメンテナンスを入れて実行するといった運用も多いと思います。
MySQL 8.0.12 から追加された INSTANT アルゴリズムを使うことで、そういった運用面での負担から解放されると考えられます。
