MySQL の ALTER TABLE は、操作によってかかる時間がまったく違います。列の追加は数千万行のテーブルでも一瞬で終わります。ところが列の型を変えると、テーブルを丸ごと作り直すので、その間は書き込みが止まります。MySQL 8.0 には、サービスを動かしたままテーブルの定義を変えるオンライン DDL の仕組みがあります。ALGORITHM=INSTANT・INPLACE・COPY の違いと、操作ごとにどれが使えるかの早見表、作業の前に確かめる手順までを実行結果つきで説明します。
以下の結果は MySQL 8.0 で実際に実行したものです。
このシナリオで考える
ネットショップの注文テーブル orders を例にします。本番では数千万行ある想定ですが、ここでは動きを見るために1000行だけ入れます。「注文にメモを書けるようにしたい」「金額の列が INT では足りなくなりそう」という相談が来た場面です。
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
amount INT NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL
) DEFAULT CHARSET = utf8mb4;
INSERT INTO orders (user_id, amount, status, created_at)
WITH RECURSIVE seq (n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 1000
)
SELECT n % 50 + 1, n * 10, 'paid', '2026-11-01 00:00:00' + INTERVAL n MINUTE
FROM seq;
SELECT COUNT(*) FROM orders;+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+ALGORITHM で決まる3つのやり方
ALTER TABLE の最後に ALGORITHM を書くと、MySQL がテーブルをどう変えるかを指定できます。3つのやり方は、行のデータに手を入れるかどうかと、作業中に書き込めるかどうかが違います。
| ALGORITHM | 何をするか | 作業中の書き込み | かかる時間 |
|---|---|---|---|
| INSTANT | テーブルの定義だけを書きかえる | 止まらない | 行数に関係なく一瞬 |
| INPLACE | テーブルの中で作業する（作り直す操作と作り直さない操作がある） | LOCK=NONE なら止まらない | 行数に応じて長くなる |
| COPY | 新しいテーブルを作り、全行を写す | 止まる（読むことはできる） | いちばん長い |
ALGORITHM を書かないと、MySQL は INSTANT、INPLACE、COPY の順に使えるものを選びます。手軽ですが、INSTANT で済むと思っていた操作が黙って COPY になっても気づけません。本番では ALGORITHM と LOCK を必ず書いておきます。使えないやり方を指定すると、MySQL は実行せずにエラーを返すので、テーブルが止まる前に気づけます。
LOCK は、作業中にほかの接続へどこまで許すかの指定です。LOCK=NONE は読み書きとも許し、LOCK=SHARED は読むだけを許します。LOCK=EXCLUSIVE にすると読むこともできなくなります。
INSTANT で列を足す
列の追加は、MySQL 8.0.12 から INSTANT で行えます。定義を書きかえるだけなので、既存の行には触りません。8.0.29 からは、好きな位置に列を足せるようになりました。
ALTER TABLE orders ADD COLUMN note VARCHAR(100) NULL, ALGORITHM=INSTANT;INSTANT で列を足したり消したりするたびに、InnoDB はテーブルに行のバージョンを1つ記録します。この数は information_schema.INNODB_TABLES の TOTAL_ROW_VERSIONS で確かめられます。
SELECT NAME, TOTAL_ROW_VERSIONS
FROM information_schema.INNODB_TABLES
WHERE NAME = 'demo/orders';+-------------+--------------------+
| NAME | TOTAL_ROW_VERSIONS |
+-------------+--------------------+
| demo/orders | 1 |
+-------------+--------------------+行のバージョンは64までです。64に達すると、次の INSTANT の追加・削除はエラーになり、INPLACE か COPY を使うしかなくなります。列の追加をくり返すテーブルでは、ときどきこの数を見ておくと作業の当日に慌てずに済みます。テーブルを作り直す操作（COPY や OPTIMIZE TABLE）をすると0に戻ります。
INSTANT が使えないテーブルもあります。FULLTEXT インデックスがあるテーブル、ROW_FORMAT=COMPRESSED のテーブル、一時テーブルでは、INSTANT で列を足せません。
INPLACE と LOCK=NONE でインデックスを足す
インデックスの追加は INPLACE で行えます。LOCK=NONE を付ければ、作っている間も注文を受けつけられます。テーブル本体は作り直さず、インデックスだけを新しく組み立てます。
ALTER TABLE orders ADD INDEX idx_user (user_id), ALGORITHM=INPLACE, LOCK=NONE;書き込みは止まりませんが、行数が多いほど組み立てに時間がかかります。その間はディスクと CPU を使うので、混む時間帯は避けます。式に貼る関数インデックスは「MySQLの生成列と関数インデックスで式やJSONの検索を速くする」で説明しています。
列の型を変えるときは COPY しかない
INT を BIGINT に変えるような型の変更は、INSTANT でも INPLACE でもできません。実際に指定すると、どちらもエラーになります。
ALTER TABLE orders MODIFY amount BIGINT NOT NULL, ALGORITHM=INSTANT;
ALTER TABLE orders MODIFY amount BIGINT NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.INSTANT のエラーは INPLACE を試すように言っていますが、型の変更では INPLACE も通りません。COPY を指定すると成功します。ただし新しいテーブルに全行を写し終えるまで書き込みが止まります。1000行なら一瞬でも、数千万行なら数十分以上かかることがあります。
ALTER TABLE orders MODIFY amount BIGINT NOT NULL, ALGORITHM=COPY;
SELECT NAME, TOTAL_ROW_VERSIONS
FROM information_schema.INNODB_TABLES
WHERE NAME = 'demo/orders';+-------------+--------------------+
| NAME | TOTAL_ROW_VERSIONS |
+-------------+--------------------+
| demo/orders | 0 |
+-------------+--------------------+テーブルを作り直したので、さっき1だった TOTAL_ROW_VERSIONS が0に戻っています。型の変更をどうしても止めずに行いたいときは、pt-online-schema-change や gh-ost という外部のツールを使う方法もあります。どちらも新しい定義のテーブルを裏で作り、少しずつ行を写してから入れかえます。
VARCHAR を広げるときの255バイトの境目
VARCHAR の長さを広げるのは INPLACE でできます。ただし、列の最大バイト数が255以下から256以上に変わる場合は INPLACE が使えません。MySQL は VARCHAR の長さを、255バイトまでなら1バイト、256バイト以上なら2バイトで記録しているためです。utf8mb4 は1文字を最大4バイトで数えるので、文字数では63と64の間が境目になります。
| 変更 | 最大バイト数（utf8mb4） | INPLACE |
|---|---|---|
| VARCHAR(20) から VARCHAR(60) | 80 から 240 | 使える |
| VARCHAR(60) から VARCHAR(64) | 240 から 256 | 使えない（COPY になる） |
ALTER TABLE orders MODIFY status VARCHAR(60) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders MODIFY status VARCHAR(64) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.1つ目は成功し、2つ目だけがエラーになりました。たった4文字広げるだけでも、境目をまたぐと COPY が必要です。VARCHAR(255) の列をさらに広げる場合は、もともと2バイトの側なので INPLACE で行えます。気をつけたいのは VARCHAR(50) のような短い列を100に広げるときです。縮める変更も COPY になります。
既定値の変更と列の削除も INSTANT
既定値（DEFAULT）の変更は定義だけの変更なので、INSTANT で終わります。列の削除も 8.0.29 から INSTANT で行えます。
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new', ALGORITHM=INSTANT;
ALTER TABLE orders DROP COLUMN note, ALGORITHM=INSTANT;
SELECT NAME, TOTAL_ROW_VERSIONS
FROM information_schema.INNODB_TABLES
WHERE NAME = 'demo/orders';+-------------+--------------------+
| NAME | TOTAL_ROW_VERSIONS |
+-------------+--------------------+
| demo/orders | 1 |
+-------------+--------------------+既定値の変更では数が増えず、列の削除で0から1になりました。ここまでの変更を終えたテーブルの定義は次のとおりです。
SHOW CREATE TABLE orders\GCREATE TABLE `orders` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`amount` bigint NOT NULL,
`status` varchar(60) NOT NULL DEFAULT 'new',
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci操作ごとの早見表
MySQL 8.0 の公式リファレンスをもとに、よく使う操作をまとめました。作り直しと書いたものは、INPLACE でも行数に応じて時間がかかります。
| 操作 | INSTANT | INPLACE | 作業中の書き込み |
|---|---|---|---|
| 列の追加 | 使える（8.0.12 から） | 使える（作り直し） | 止まらない |
| 列の削除 | 使える（8.0.29 から） | 使える（作り直し） | 止まらない |
| 列名の変更（型はそのまま） | 使える（8.0.28 から） | 使える | 止まらない |
| 既定値の変更 | 使える | 使える | 止まらない |
| インデックスの追加 | 使えない | 使える | 止まらない |
| VARCHAR を広げる | 使えない | 255バイトの境目をまたがなければ使える | 止まらない |
| 列の並びかえ | 使えない | 使える（作り直し） | 止まらない |
| NOT NULL にする | 使えない | 使える（作り直し） | 止まらない |
| 列の型の変更 | 使えない | 使えない | 止まる（COPY） |
よくある落とし穴：メタデータロック待ちで問い合わせが並ぶ
LOCK=NONE や INSTANT でも、ALTER TABLE は始めと終わりの短い間だけ、テーブルの排他メタデータロック（定義を守るためのロック）を取ります。同じテーブルを使う長いトランザクションが残っていると、ALTER TABLE はその終わりを待ちます。
困るのは、待っている ALTER TABLE の後ろに、ふつうの SELECT や INSERT まで並んでしまうことです。SHOW PROCESSLIST には Waiting for table metadata lock がずらりと出て、一瞬で終わるはずの INSTANT でもサービスが止まったように見えます。作業の前に、長く続いているトランザクションがないかを確かめておきます。
SELECT trx_id, trx_started, trx_mysql_thread_id
FROM information_schema.INNODB_TRX
ORDER BY trx_started;あわせて、ALTER TABLE を流す接続で lock_wait_timeout を短くしておきます。既定値は31536000秒（1年）なので、そのままだと待ち続けます。5秒にしておけば、ロックが取れないときは5秒でエラーになり、後ろに並んだ問い合わせも動き出します。
SET SESSION lock_wait_timeout = 5;
SELECT @@SESSION.lock_wait_timeout;+-----------------------------+
| @@SESSION.lock_wait_timeout |
+-----------------------------+
| 5 |
+-----------------------------+レプリカ（複製先）を使っている場合は、レプリカの遅れにも気をつけます。時間のかかる ALTER TABLE は、元のサーバーで終わってから、レプリカでも同じくらいの時間をかけて流れます。その間、レプリカでは後続の更新の反映が遅れます。大量の行を消すときに起きる同じ問題は「大量データのDELETE・UPDATEをLIMITで分割する方法」で説明しています。
本番で流す前に確かめる手順
- 検証用のサーバーで、ALGORITHM と LOCK を付けた同じ ALTER TABLE を流し、エラーにならないかを見る
- INPLACE や COPY になる操作は、本番に近い行数のデータでかかる時間を測る
- INSTANT で列を足す・消すときは、TOTAL_ROW_VERSIONS が64に近くないかを見る
- 本番で INNODB_TRX を見て、長いトランザクションが残っていないことを確かめる
- lock_wait_timeout を短くしてから流す
まとめ
列の追加・削除と既定値の変更は INSTANT で、インデックスの追加と VARCHAR の小さな拡張は INPLACE と LOCK=NONE で、サービスを止めずに行えます。列の型の変更と、utf8mb4 で63文字と64文字の境目をまたぐ VARCHAR の拡張は COPY になり、書き込みが止まります。本番では ALGORITHM と LOCK を必ず書き、長いトランザクションがないことを確かめてから流してください。
もっと学ぶなら【PR】
- MySQL徹底入門 第5版：MySQL の機能を広く解説した本。日付関数などの細かい動きを調べたいときに
※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。
参考リンク
- MySQL 8.0 Reference Manual: Online DDL Operations
- MySQL 8.0 Reference Manual: Online DDL Performance and Concurrency
- MySQL 8.0 Reference Manual: Metadata Locking
アイキャッチ画像: Photo by Matthew Moloney on Unsplash
