MySQLで巨大なテーブルをALTERする

巨大なテーブルに対するALTERはやっかいです。当然、めちゃくちゃ時間かかりますからね。ということでいろいろ考えてみました。

## テスト用テーブル

[[MySQL]大量のレコードをDELETEする](https://qiita.com/saoyagi2/items/b60d43a97086c52cde10) と同じようにテスト用のテーブルを用意します。使用しないインデックスは削りました。

```sql
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 は一瞬で完了します。

例えばカラムコメントの変更をしてみましょう。

```sql
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 となるようなカラムタイプの変更だと時間が掛かります。この間、読み出しはできますが書き込みはできません。

```sql
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 でやればコマンド一発で対応できますが、長時間システムが停止されてしまいます。サービスによってはそんな停止時間を取れないこともあるでしょう。それでも何とかしなければいけない場合には、少々効率が悪かったり手間がかかったりしても、別の手段を考えなければならないわけですね。

```sql
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](https://www.s-style.co.jp/products/percona/toolkit/pt-online-schema-change) というツールがあるそうです。

## レプリケーションを利用する

(2022.11.30追記)

[【衝撃】AWSのRDSがデータを失わないBlue/Greenデプロイに対応しました #reinvent](https://dev.classmethod.jp/articles/rds-bg-deploy/)という記事を読んでいたところ、以下の部分に衝撃を受けました。

> * あらゆるシチュエーションで利用可能
>   - エンジンのアップグレード
>   - （重要）スキーマの変更
>   - OSやメンテナンスのアップデート対応

なんとスキーマ変更にも使えると。上記の「分解してコピーする」では新旧テーブルを用意してデータをコピーしていましたが、それを新旧データベースを丸ごと用意するという事のようです。なるほど、それであればデータのコピーはデータベースの機能であるレプリケーションを使えますから、自前でトリガー仕込んだりする必要もありませんね。なるほどなるほど。

理屈は分かるけれど、自分でオペレーションするのは大変そうと思いますが、それをマネージメントサービスとして提供してくれるなら便利そうです。

ただ、当たり前ですがありとあらゆるスキーマ変更に対応するわけではありませんね。出来ること出来ないことは以下の通りだそうです。

> * レプリケーションに対応しているスキーマ変更
>   - テーブル末尾への列追加
>   - インデックスの作成と削除
> * レプリケーションに対応していないスキーマ変更
>   - 列名の変更
>   - テーブル名の変更

(2025/2/22追記)

[ALGORITHM=INSTANTを使用した列追加・削除](https://blogs.oracle.com/mysql-jp/post/mysql-80-instant-add-drop-columns-jp) によりますと、ALGORITHM=INSTANT による列追加・列削除が出来るようになったそうです。

実際どうだろうと試してみました。試した環境は MySQL 8.0.41 で、この記事で行ったのと同じ1000万レコードのテストテーブルを用意しました。

```
mysql> SELECT version();
+-------------------------+
| version()               |
+-------------------------+
| 8.0.41-0ubuntu0.22.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> DESC huge;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int          | NO   | PRI | NULL    | auto_increment |
| number | int          | YES  | MUL | NULL    |                |
| string | varchar(255) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> SELECT COUNT(*) FROM huge;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.52 sec)
```

実際にカラム追加してみます。

```
mysql> ALTER TABLE huge ADD COLUMN add_number INTEGER AFTER number, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC huge;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| number     | int          | YES  | MUL | NULL    |                |
| add_number | int          | YES  |     | NULL    |                |
| string     | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
```

見事に ALGORITHM=INSTANT で列が追加出来ましたね。

列削除も試してみます。

```
mysql> ALTER TABLE huge DROP COLUMN add_number, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC huge;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int          | NO   | PRI | NULL    | auto_increment |
| number | int          | YES  | MUL | NULL    |                |
| string | varchar(255) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
```

こちらも出来ました。

8.0.12 の時点では ALGORITHM=INSTANT では最後尾への列追加しか出来なかったそうですが、8.0.29 からは任意の位置への列追加と列削除も可能になったそうです。