MySQLのカラム追加や削除はなぜ速くなったのか？

はじめに

みなさま、本年もよろしくお願いいたします。現在のMySQLは本番環境でもカラム追加も懸念すべきことも少なくなり、ALTER TABLE ... ADD COLUMN を実行すれば、テーブルのサイズやレコード数に関係なく一瞬で完了するようになりました。

以前は本番環境でカラムを1つ追加するだけなのに、数時間掛かることもあったためメンテナンス画面（サービス停止）にして対応することもありました。

この記事では、MySQLのDDL（スキーマ変更）の技術の進歩を、MySQL5.5系から現在までまとめてみようと思います

※ この記事ではInnoDBを前提としています。

※ Amazon AuroraやMariaDBなどMySQL互換データベースでは、挙動が異なる場合があります。

DDLとは？

DDLとは、データベースの「構造」を定義・変更するSQL文のことです。

CREATE TABLE - テーブルを作成
ALTER TABLE - テーブル構造を変更（カラム追加・削除など）
DROP TABLE - テーブルを削除

この記事で取り上げるのは、ALTER TABLE によるカラム追加・削除についてです。

MySQL 5.5時代：フルコピー

MySQL 5.5以前では、カラム追加時に以下のような処理が行われていました。

問題は大きく2つあります。

1. フルテーブルコピー（COPY アルゴリズム※アルゴリズムについては後述）

全データを物理的にコピーするため、データ量に比例して時間がかかります。「コピー」と言っても、内部的には 1行ずつINSERTを実行 しています。1億レコードあれば、1億回のINSERTが走ります。

2. テーブルロック

コピー中はテーブルが完全にロックされ、SELECTすら実行できないため、サービスが完全に停止します。

※ MySQL5.6以降のCOPYアルゴリズムでは読み取りは可能になりました（後述）

【MySQL 5.5のカラム追加の流れ】

1. テーブル全体をロック（読み書き不可）
2. 新しい構造の空テーブルを裏で作成
3. 全データを1行ずつ新テーブルにコピー
    （5000万行なら5000万回のINSERT）
4. 古いテーブルを削除、新テーブルをリネーム
5. テーブルロック解除

所要時間の目安

レコード数	所要時間（目安）	サービス影響
100万件	数分〜10分程度	短時間停止
1000万件	30分〜1時間程度	計画メンテナンスが必要
5000万件	2〜4時間程度	深夜メンテナンス
1億件以上	半日〜1日以上	週末対応・別戦略の検討

※ これらの時間は、大まかな目安です。
※ DDLの所要時間は、ハードウェア構成・テーブル設計・カラム数・インデックス数・I/Oの性能などによって変動するため、正確な時間ではありません。

MySQL 5.6：Online DDL

2013年2月、MySQL 5.6（5.6.10）で正式リリースされたOnline DDL で、状況は大きく変わりました。

Online DDLとは

Online DDLは、カラム追加や削除中もテーブルへの読み書きを可能にする機能です。完全なロックなしで（または最小限のロックで）実行できるようになりました。

処理時間は操作内容によっては短縮されるケースもありますが、ほぼ同じです。

Online DDL は「処理が高速になる機能」ではなく、「テーブルへのアクセスを止めずにDDLを実行できる機能」です。

新しいオプション：ALGORITHM と LOCK

MySQL 5.6では、ALTER TABLE文にオプションが追加されました。

ALGORITHM オプション

値	説明
`COPY`	従来方式。テーブル全体をコピー（内部的には1行ずつINSERT）。最も遅い
`INPLACE`	テーブルを丸ごとコピーせず、その場で変更（高速）
`DEFAULT`	指定なしの場合のデフォルト。INPLACE → COPY の順で試行

LOCK オプション

値	説明
`NONE`	読み書き両方許可
`SHARED`	読み取りのみ許可、書き込みはブロック
`EXCLUSIVE`	読み書き両方ブロック（従来と同じ）
`DEFAULT`	指定なしの場合のデフォルト。NONE → SHARED → EXCLUSIVE の順で試行

COPYアルゴリズムでも、5.6以降はロックの挙動が改善されています。

バージョン	COPY時のロック	読み取り	書き込み
5.5	EXCLUSIVE固定	❌ 不可	❌ 不可
5.6以降	SHARED → 最終フェーズでEXCLUSIVE	✅ 可能	❌ 不可

5.6以降では処理中のSELECTは可能ですが、INSERT/UPDATE/DELETEはブロックされます。また、処理の最終フェーズでは短時間のEXCLUSIVEロックが発生します。

実際のSQL例

-- Online DDLでカラム追加（読み書き可能）
ALTER TABLE users
ADD COLUMN nickname VARCHAR(100),
ALGORITHM=INPLACE,
LOCK=NONE;

MySQL 5.7：安定性の向上

2015年10月、MySQL 5.7（5.7.9）が正式リリースされました。 Online DDL全体の処理速度が向上されたものの大きな変化はありませんでした MySQL5.7のEOLは2023年10月で、長期間使用されていた主力バージョンとなっていました。

MySQL 8.0（8.0.12）：INSTANT

2018年7月、MySQL 8.0.12の正式リリースされたALGORITHMのINSTANTで状況が変わりました。

INSTANTとは何か

INSTANTアルゴリズムは、データを一切触らずにメタデータだけを変更 します。つまり、テーブルに何億レコードあろうと、カラム追加が一瞬で完了します。

メタデータとは？

ここでいうメタデータ（metadata） とは、データに関するデータ（テーブルの構造情報）のことになります。

【実データとメタデータの違い】

┌─────────────────────────────────────────────────────────┐
│ メタデータ（テーブル定義情報）                            │
│ ・テーブル名: users                                      │
│ ・カラム: id (INT), name (VARCHAR(100)), age (INT)      │
│ ・インデックス: PRIMARY KEY (id)                         │
│ → 数KB程度の小さな情報                                   │
└─────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────┐
│ 実データ（実際に格納されているレコード）                   │
│ ・id=1, name='井出', age=28                             │
│ ・id=2, name='松田', age=30                              │
│ ・... （数千万〜数億レコード）                                  │
│ → 数GB〜数TBになることも                                 │
└─────────────────────────────────────────────────────────┘

従来の方式では「実データ」を全て書き換えていたため時間がかかりました。INSTANTは「メタデータ」だけを書き換えるため、データ量に関係なく一瞬で完了します。

ALGORITHMオプションの進化

MySQL 8.0.12で、ALGORITHMオプションに新たな選択肢が加わりました。

値	導入バージョン	説明
`COPY`	5.5以前	テーブル全体をコピー（内部的には1行ずつINSERT）。最も遅く、完全ロックが必要
`INPLACE`	5.6	その場で変更。コピーより高速だが、内部的にテーブル再構築が発生することも
`INSTANT`	8.0.12	メタデータのみ変更。データに触れないため一瞬で完了
`DEFAULT`	5.6	指定なしの場合のデフォルト。8.0.12以降は INSTANT → INPLACE → COPY の順で試行

-- 明示的にINSTANTを指定
ALTER TABLE users
ADD COLUMN nickname VARCHAR(100),
ALGORITHM=INSTANT;

-- INSTANTが使えない場合はエラーになる
-- ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation.

INSTANTで可能な操作（ver:8.0.12）

カラムの追加（テーブルの最後尾のみ）
カラムのデフォルト値の変更・削除

実際のSQL例

-- INSTANTでカラム追加
ALTER TABLE users
ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
ALGORITHM=INSTANT;

INSTANTの内部動作

【カラム追加】
メタデータ（テーブル定義）だけを更新
　↓
既存の行はそのまま（新カラムの値はデフォルト値として扱う）
　↓
新しく追加される行から実際に値を格納

DEFAULT値はどう扱われる？

DEFAULT CURRENT_TIMESTAMP などを指定しても、既存の行には実際にデータは書き込まれません。DEFAULT値はメタデータとして保存され、既存の行をSELECTした際にMySQLがその値を「返す」仕組みです。

-- 1億行のテーブルにDEFAULT付きカラムを追加しても一瞬
ALTER TABLE users
ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
ALGORITHM=INSTANT;
-- → メタデータに「DEFAULT値はCURRENT_TIMESTAMP」と記録するだけ
-- → 既存の1億行には何も書き込まれない

新しくINSERTされる行からは、実際にデータが格納されます。

性能への影響について

「メタデータから取得する行」と「実際のカラムから取得する行」が混在しても、性能への影響はほとんどありません。 MySQL は行ごとに軽量なチェックを行うだけで、一般的な業務利用では誤差レベルです

INSTANT アルゴリズムがテーブルデータを変更せず、メタデータのみを更新する仕組みであることが公式ドキュメントでも説明されています。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.12.2 オンライン DDL のパフォーマンスと同時実行性

気になる場合は OPTIMIZE TABLE（テーブル全体の再構築）を実行すると、すべての行が新しいカラム構造で再構築されるので検討してください ※ テーブルサイズによってはロックや高負荷が発生するので、実行タイミングは注意してください

INSTANTの実行回数は64回です、それを超過する場合はOPTIMIZE TABLEで再構築しましょう

INSTANT DDL操作を用いたMySQL InnoDBのスキーマ変更 | mysql-jp

MySQL 8.0.29：INSTANT制約の大幅緩和

2022年4月リリースのMySQL 8.0.29で、INSTANTの制約が大幅に緩和されました。

任意の位置 へのカラム追加が可能
カラムの削除 も可能に

実際のSQL例

-- 8.0.29以降: 任意の位置へINSTANT追加が可能
ALTER TABLE users
ADD COLUMN middle_name VARCHAR(50) AFTER first_name,
ALGORITHM=INSTANT;

-- カラム削除もINSTANTで一瞬
ALTER TABLE users
DROP COLUMN ossun_flg,
ALGORITHM=INSTANT;

8.0.29で追加されたINSTANT対応操作

操作	8.0.12	8.0.29以降
カラム追加（最後尾）	✅	✅
カラム追加（任意位置）	❌	✅
カラム削除	❌	✅

なお、既存カラムの順序変更はテーブル全体の再構築が発生するため、データ量に比例して負荷と時間が掛かります。

バージョン別比較まとめ

バージョン	リリース年月	アルゴリズム	ロック	1億件の所要時間	主な変化
5.5	2010年12月	COPY	完全ロック	🔴 数時間〜半日	フルコピー＋完全ロック
5.6	2013年2月	INPLACE	読み書き可能 ※1	🟡 数時間（但し稼働可）	Online DDL登場
5.7	2015年10月	INPLACE	読み書き可能 ※1	🟡 数時間（5.6と同様）	安定性向上（カラム追加は5.6と同様）
8.0.12	2018年7月	INSTANT	メタデータのみ	🟢 1秒未満	INSTANT登場
8.0.29	2022年4月	INSTANT拡張	メタデータのみ	🟢 1秒未満	INSTANT制約緩和（任意位置・削除対応）

※1 LOCK=NONE指定時。指定しない場合はMySQLが自動的に最適なロックレベルを選択します。

補足：カラムの型変更はINSTANTでできない

カラムの型変更などデータの物理的なサイズが変わる変更は、全行を書き換える必要があるため COPY になります。これは MySQL 8.0 でも変わりません。本番環境で型変更を行う際は、事前に EXPLAIN ALTER で使用されるアルゴリズムを確認すると良さそうです

EXPLAIN ALTER TABLE users ADD COLUMN test INT;

また、以下のようなケースでもINSTANTは使用できません（内部構造が特殊なため）。

ROW_FORMAT=COMPRESSED（圧縮テーブル）を使用している場合
全文検索インデックス（FULLTEXT）を含むテーブル（8.0.29以降も制限あり）

詳細はドキュメントを確認してください

MySQL 8.0 リファレンスマニュアル - オンライン DDL の操作

まとめ

MySQLのスキーマ変更は、この10年以上で劇的に進化しましたね！

時期	状況
2010年頃（5.5）	カラム追加は一大イベント。深夜メンテナンス必須。
2013年（5.6）	Online DDLでサービス継続可能に。ただし時間は変わらず。
2015年（5.7）	安定性向上。カラム追加は5.6と同様だが、多くの本番環境で活躍。
2018年7月（8.0.12）	INSTANTで一瞬。ただし最後尾のみ。
2022年4月（8.0.29）	任意位置・削除もINSTANT対応。運用上ほぼ完成形。