はじめに
先日、改修タスクでカラム追加が必要になった時のこと。
ローカル環境で実装とテストが終わり、次は検証環境でテストするぞという時に問題が発生した。テーブル定義変更のALTER文が10分以上待っても終わらない。何気なく対象テーブルのレコード数を調べると1億レコードを超えていた。
変更は10テーブルあるし数時間かかるのでは…と焦ったが、調べてみるとそんなことはなかった。
テーブル定義変更を高速に行う仕組みとして、オンラインDDLやALGORITHMについて学んだ内容を残しておく。
オンラインDDLとは
テーブル定義を変更する際に、可能な限りロックをかけず読み書きを止めないようにする仕組みのこと。
つまり、できるだけシステム停止しなくても済むありがたい機能。
ALGORITHMの種類
MySQLではDDLの実行方式として、COPY、INPLACE、INSTANT の3種類のアルゴリズムが用意されている。INPLACE、INSTANTがオンラインDDLと呼ばれるもの。
ALGORITHMを明示的に指定しない場合は、利用可能なものからINSTANT ＞ INPLACE ＞ COPYの優先順で自動選択される。
COPY
古い方式。MySQL5.5以前はこの方式しか存在しなかった。
変更後のテーブルを新しく作成し、既存データを全件コピーするということが内部で行われている。
当然、大量データを持つテーブルでは実行時間が長くなる。
INPLACE
MySQL5.6以降で導入された方式。
テーブルコピーを行わず、既存テーブルに対して変更を実施する。
処理内容によってはデータの再構築が内部的に行われるため、実行時間は変更内容に依存する。
INSTANT
MySQL8.0で追加された方式。
データには触れず、メタデータのみを変更する。
テーブル再構築が行われることはなく、データ量が関係しないため、実行時間は常に一瞬。
ALGORITHM=INSTANTについて
さて、INSTANTアルゴリズムはMySQL8.0だったら使えるのかと思いきや、よくよく調べると正確には8.0.12で導入されていた。
MySQL8.0のGA版は8.0.11。最初のバージョンだけはこの機能が存在しない。
導入されたバージョン詳細
MySQL 8.0.12
アルゴリズムにINSTANTが導入された。
列追加のみ可能で列削除では使用不可。
AFTER句などと組み合わせた使用は不可能で、列の最後に追加するしかない。
MySQL 8.0.29
INSTANTが列削除に対応した。
AFTER句などと組み合わせて任意の位置に挿入することも可能になった。
MySQL 8.0.32
INSTANTアルゴリズムに関連する不具合が修正されて安定して使用できるようになったバージョンらしい。
制限事項
ALGORITHM=INSTANTが使用できるのは、一つのテーブルにつき最大64回。
上限に達するとテーブル再構築が必要。
仕組みとして、内部的に列情報がバージョン管理されており、information_schema.innodb_tablesのtotal_row_versionsで確認可能。
試してみる
8.0系の最新(最終)バージョンであるMySQL 8.0.46を用意して、テスト用のテーブルを作成して適当に試してみた。
CREATE TABLE tbl01(
id BIGINT NOT NULL AUTO_INCREMENT
, name VARCHAR(255)
, PRIMARY KEY (`id`)
) ENGINE = InnoDB;
まずはtotal_row_versionsを確認。
mysql> SELECT total_row_versions FROM information_schema.innodb_tables WHERE name LIKE '%tbl01';
+--------------------+
| total_row_versions |
+--------------------+
| 0 |
+--------------------+
カラムを1つ追加。total_row_versionsがインクリメントされたことを確認。
mysql> ALTER TABLE tbl01 ADD COLUMN test01 INT, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT total_row_versions FROM information_schema.innodb_tables WHERE name LIKE '%tbl01';
+--------------------+
| total_row_versions |
+--------------------+
| 1 |
+--------------------+
さらにカラムを63個追加。
ALTER TABLE tbl01 ADD COLUMN test02 INT, ALGORITHM=INSTANT;
ALTER TABLE tbl01 ADD COLUMN test03 INT, ALGORITHM=INSTANT;
・
・
・
ALTER TABLE tbl01 ADD COLUMN test64 INT, ALGORITHM=INSTANT;
total_row_versionsが上限の64になっていることを確認。
mysql> SELECT total_row_versions FROM information_schema.innodb_tables WHERE name LIKE '%tbl01';
+--------------------+
| total_row_versions |
+--------------------+
| 64 |
+--------------------+
65個目はエラーになった。
mysql> ALTER TABLE tbl01 ADD COLUMN test65 INT, ALGORITHM=INSTANT;
ERROR 4092 (HY000): Maximum row versions reached for table test_db/tbl01. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
ALGORITHMを指定しなかった場合、INPLACEが自動選択されるようでエラーとならずに実行できた。
mysql> ALTER TABLE tbl01 ADD COLUMN test65 INT;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
そして、total_row_versionsがリセットされていた。
mysql> SELECT total_row_versions FROM information_schema.innodb_tables WHERE name LIKE '%tbl01';
+--------------------+
| total_row_versions |
+--------------------+
| 0 |
+--------------------+
複数カラムをまとめて追加した場合は、total_row_versionsは1しか増えない。
ALTER TABLE tbl01
ADD COLUMN test100 INT AFTER name,
ADD COLUMN test101 INT AFTER test100,
ADD COLUMN test102 INT AFTER test101,
ALGORITHM=INSTANT;
mysql> SELECT total_row_versions FROM information_schema.innodb_tables WHERE name LIKE '%tbl01';
+--------------------+
| total_row_versions |
+--------------------+
| 1 |
+--------------------+
ALGORITHM=INSTANTはインデックス追加には使用できない。
mysql> ALTER TABLE tbl01 ADD INDEX index_test(test01),ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
他に検証途中で気づいたこととして、レコードが0件の状態のテーブルに対してALGORITHM指定なしのALTER文で列追加すると、total_row_versionsは増えず0にリセットされた。ALGORITHM=INSTANTを明示的に指定すると、0件でもtotal_row_versionsはインクリメントされた。
0件の場合は、INSTANTが使用されなかったということだと思う。
1件でもレコードがあれば、ALGORITHM指定なしでもtotal_row_versionsがインクリメントされたので、基本的にはINSTANTが優先されることは間違いなさそう。
ちなみに
テスト用DBは8.0.11だったので8.0系内でバージョンアップしました。
バージョンアップするとALTER文の実行が一瞬で終わりました。
まとめ
- MySQLで大量データを扱う場合、MySQL8.0.32以降（最低でも8.0.12以降）を使用すると楽になる
- ALGORITHMは明示的に指定した方が安全だと感じた
- 可能ならバージョンアップはやった方がいい（それはそう）