巨大なテーブルに対するALTERはやっかいです。当然、めちゃくちゃ時間かかりますからね。ということでいろいろ考えてみました。
テスト用テーブル
[MySQL]大量のレコードをDELETEする と同じようにテスト用のテーブルを用意します。使用しないインデックスは削りました。
root@localhost [test]> CREATE TABLE seed (
-> value INTEGER UNIQUE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
root@localhost [test]> INSERT INTO seed (value) VALUES
-> (0), (1), (2), (3), (4),
-> (5), (6), (7), (8), (9);
Query OK, 10 rows affected (0.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
root@localhost [test]> DROP TABLE IF EXISTS huge;
Query OK, 0 rows affected, 1 warning (0.01 sec)
root@localhost [test]> CREATE TABLE huge (
-> id INTEGER PRIMARY KEY AUTO_INCREMENT,
-> number INTEGER,
-> string VARCHAR(255),
-> INDEX num_idx (number)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)
root@localhost [test]> INSERT INTO huge SELECT NULL, s1.value, s1.value
-> FROM seed AS s1, seed AS s2, seed AS s3, seed AS s4,
-> seed AS s5, seed AS s6, seed AS s7;
Query OK, 10000000 rows affected (3 min 27.90 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
ロック不要パターン
まず検討しなければならないのは、その ALTER TABLE によってテーブルロックされるかどうかです。MySQL の ALTER TABLE は基本的に新規テーブルを作成して全レコードをコピーするという挙動です(ALGORITHM=COPY)。この間、テーブルへの書き込みは禁止されますので、システムも非常に制限された状態になってしまいます。それに対して最近の MySQL では一部でオンライン DDL が使えるようになりました。ALGORITHM=INPLACEであれば全レコードをコピーして時間が掛かるのは変わりませんが、ロックしないのでテーブルへの書き込みもできます。ただし重いですが。ALGORITHM=INSTANTであればレコードをコピーすらしませんので、ALTER TABLE は一瞬で完了します。
例えばカラムコメントの変更をしてみましょう。
root@localhost [test]> ALTER TABLE huge MODIFY COLUMN number
-> INTEGER COMMENT '数字';
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
一瞬で完了しましたね。ALTER TABLE は時間が掛かるから極力実行しないようにしようというのは、過去の常識になったと言えるでしょう。ALTER TABLE の種類によっては一瞬で完了するものもありますので、COPY/INPLACE/INSTANT のどのアルゴリズムが適用されるかを確認して、INSTANTであれば気軽にALTER TABLEを実施してよいかと思います。ただし、レコードコピーをしない ALGORITHM=INSTANT でもメタデータはロックします。該当テーブルでトランザクションが実行中だとメタデータロックが取得できないため、ALTER TABLEもロック待ちになります。そしてALTER TABLE以後の該当テーブルに対するクエリは全て ALTER TABLE の終了待ちになってしまうので、事実上システムが停止してしまいます。トランザクションを使用するスロークエリがある場合は注意しましょう。
一方、ALGORITHM=COPY となるようなカラムタイプの変更だと時間が掛かります。この間、読み出しはできますが書き込みはできません。
root@localhost [test]> ALTER TABLE huge MODIFY COLUMN
-> number BIGINT COMMENT '数字';
Query OK, 10000000 rows affected (2 min 29.68 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
分解してコピーする
ALGORITHM=COPY/INPLACEでは全レコードを新テーブルにコピーします。これと同じことを分解して行ってはどうかというアイデアです。なんでMySQLが勝手にやってくれることをわざわざ分解して行うのかというと、システムの停止時間を短くするためです。ALTER TABLE でやればコマンド一発で対応できますが、長時間システムが停止されてしまいます。サービスによってはそんな停止時間を取れないこともあるでしょう。それでも何とかしなければいけない場合には、少々効率が悪かったり手間がかかったりしても、別の手段を考えなければならないわけですね。
root@localhost [test]> CREATE TABLE huge_copy (
-> id INTEGER PRIMARY KEY AUTO_INCREMENT,
-> number BIGINT COMMENT '数字',
-> string VARCHAR(255),
-> INDEX num_idx (number)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)
root@localhost [test]> INSERT INTO huge_copy
-> SELECT * FROM huge WHERE number=0;
Query OK, 1000000 rows affected (1 min 4.99 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
root@localhost [test]> INSERT INTO huge_copy
-> SELECT * FROM huge WHERE number=1;
Query OK, 1000000 rows affected (37.06 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
root@localhost [test]> INSERT INTO huge_copy
-> SELECT * FROM huge WHERE number=2;
Query OK, 1000000 rows affected (41.41 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
root@localhost [test]> INSERT INTO huge_copy
-> SELECT * FROM huge WHERE number=3;
Query OK, 1000000 rows affected (40.85 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
root@localhost [test]> INSERT INTO huge_copy
-> SELECT * FROM huge WHERE number=4;
Query OK, 1000000 rows affected (46.24 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
root@localhost [test]> INSERT INTO huge_copy
-> SELECT * FROM huge WHERE number=5;
Query OK, 1000000 rows affected (1 min 16.79 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
root@localhost [test]> INSERT INTO huge_copy
-> SELECT * FROM huge WHERE number=6;
Query OK, 1000000 rows affected (1 min 7.07 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
root@localhost [test]> INSERT INTO huge_copy
-> SELECT * FROM huge WHERE number=7;
Query OK, 1000000 rows affected (2 min 55.05 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
root@localhost [test]> INSERT INTO huge_copy
-> SELECT * FROM huge WHERE number=8;
Query OK, 1000000 rows affected (2 min 51.38 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
root@localhost [test]> INSERT INTO huge_copy
-> SELECT * FROM huge WHERE number=9;
Query OK, 1000000 rows affected (3 min 2.51 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
root@localhost [test]> RENAME TABLE huge to huge_old, huge_copy TO huge;
Query OK, 0 rows affected (0.26 sec)
root@localhost [test]> DROP TABLE huge_old;
Query OK, 0 rows affected (28.84 sec)
15分32秒52でした。ALGORITHM=COPY よりも随分時間が掛かってしまいましたし、number=9 のコピーでは全レコードコピーよりも時間が掛かってしまいますので、このケースでは分解してコピーする意味はなかったですね。事前に仕込みでコピーできるレコード数と、システム停止してメンテナンス時間中にコピーするレコード数のバランスによって手法を検討しなければなりませんね。
分解してコピーする場合に大切なのは、最後にコピーするレコードをどうやって特定するかです。INSERT しかされない履歴的なテーブルの場合、日時カラムで判定できるでしょう。全てのレコードが常に更新可能性がある場合、更新日時カラムがなければ判定できません。場合によってはTRIGGER を仕込んでオリジナルテーブルに更新がかかったら新テーブルにも同じ更新がかかるようにするなどの方法も検討しなければならないでしょう。
(2021.9.8追記)
分解してコピーする手順をやってくれる pt-online-schema-change というツールがあるそうです。
レプリケーションを利用する
(2022.11.30追記)
【衝撃】AWSのRDSがデータを失わないBlue/Greenデプロイに対応しました #reinventという記事を読んでいたところ、以下の部分に衝撃を受けました。
- あらゆるシチュエーションで利用可能
- エンジンのアップグレード
- （重要）スキーマの変更
- OSやメンテナンスのアップデート対応
なんとスキーマ変更にも使えると。上記の「分解してコピーする」では新旧テーブルを用意してデータをコピーしていましたが、それを新旧データベースを丸ごと用意するという事のようです。なるほど、それであればデータのコピーはデータベースの機能であるレプリケーションを使えますから、自前でトリガー仕込んだりする必要もありませんね。なるほどなるほど。
理屈は分かるけれど、自分でオペレーションするのは大変そうと思いますが、それをマネージメントサービスとして提供してくれるなら便利そうです。
ただ、当たり前ですがありとあらゆるスキーマ変更に対応するわけではありませんね。出来ること出来ないことは以下の通りだそうです。
- レプリケーションに対応しているスキーマ変更
- テーブル末尾への列追加
- インデックスの作成と削除
- レプリケーションに対応していないスキーマ変更
- 列名の変更
- テーブル名の変更