MySQL: ALTER TABLEのアルゴリズムと実行速度について

こんにちは。Legalscape (採用情報) でLLMを用いたWebアプリケーションのエンジニアをやっていますhiroendoreです。

三行まとめ

• MySQLのALTER TABLE操作にはINSTANT、INPLACE、COPYの3つのアルゴリズムがあり、性能が大きく異なる

• 1万〜100万レコードのテーブルで実測した結果、INSTANTはレコード数に関わらず一定時間、INPLACEとCOPYはレコード数に比例して増加した

• 本番環境でのALTER TABLE実行前には、使用されるアルゴリズムを確認し実行スケジュールについて検討が必要

はじめに

MySQLでテーブル構造を変更する際使用されるALTER TABLE文では、操作の種類によって実行時間が大きく異なります。

本記事では、MySQLのテーブル構造の変更の際にどのような仕組みが存在しているかを簡単に解説します。その上で各種ALTER TABLE操作の性能を実測した結果を紹介します。

背景

以前テーブル構造変更のマイグレーション作業を行った際に、想定よりも大幅に長く時間がかかってしまったということがありました。

アプリケーションのデプロイスケジュールの適切な見積もりのためにも、内部での動作についてあらためて調べた上で、簡単な実験でパフォーマンスを確認したいと考えました。

当初の予想として、

• indexを貼る

• カラム位置を指定する

の操作がそれぞれパフォーマンスに影響を与えているのではないかと考え、実際に調べたいと考えました。

オンラインDDL（Online DDL）とは

今回扱うテーブル構造の変更に対して、MySQLは処理速度の向上と可用性の向上のためオンラインDDLという仕組みを提供しています。

オンラインDDLとは：

MySQL (InnoDB)には オンラインDDLといって、 DDL (ALTER TABLE) で変更中のテーブルにそのままDMLによる変更操作(INSERT/UPDATE/DELETE)を許可する機能 があります。
これにより、システムを稼働させたままDBマイグレーションを実施することができます。 新バージョンリリースのたびにシステムを閉塞する必要がなく、とても便利ですね！

引用：https://qiita.com/ssc-ksaitou/items/451d1d462f52ad360860

ALTER TABLEの際に、テーブル全体をロックしてデータ参照や更新が行われなくなるような事態を防ぐ仕組みです。ロックを行わないというわけではなく、操作に応じて最適なアルゴリズムを選択し、最小限のロックを行うような仕組みになっています。（ロックについては今回は特に詳しく触れないのですが、最後にもコメントしている通り重要です）

 MySQLのDDLのアルゴリズムについて

アルゴリズムは下の3種類から選ばれます。

下記3つのうちINPLACEとINSTANTがいわゆるオンラインDDLにあたる仕組みです。

• COPY：元のテーブルのコピーに対して操作を実行し、テーブルデータを元のテーブルから新しいテーブルに行ごとにコピーします。DMLの同時実行は許可されません。（DML = SELECT, UPDATE, INSERT, DELETE）

• INPLACE：テーブルデータのコピーを避けますが、テーブルをその場で再構築する場合があります。操作の準備と実行フェーズの間、テーブルに対する排他的メタデータロックが短時間取得される場合があります。通常、DMLの同時実行が許可されます。

• INSTANT：データディクショナリ内のメタデータのみを変更します。操作の実行フェーズ中、テーブルに対する排他的メタデータロックが短時間取得される場合があります。テーブルデータは影響を受けないため、操作は瞬時に完了します。DMLの同時実行が許可されます。（MySQL 8.0.12で導入）

参考：https://dev.mysql.com/doc/refman/8.0/ja/alter-table.html

どのアルゴリズムが使われるか？

ALGORITHM句はオプションですが、指定することができます。

例：

ALTER TABLE test_table ADD COLUMN new_col3 INT AFTER col_a, ALGORITHM=INSTANT;

ALGORITHM句が省略された場合、選択されるアルゴリズムの優先順位としては、

ALGORITHM=INSTANT>ALGORITHM=INPLACE>ALGORITHM=COPY

の順になります。

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

INSTANTアルゴリズムについて

INSTANTアルゴリズムは、テーブルデータそのものを変更する代わりに、メタデータのみを変更するアルゴリズムです。

具体的には、行のバージョンを記録するメタデータの変更によってテーブル構造の変化を管理しています。

行バージョンは（クエリはサンプルですが）、

SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%test_db/test_table%";

のようなクエリで以下のようにチェックできます。

NAME	test_db/test_table
TOTAL_ROW_VERSIONS	0

例えば

ALTER TABLE test_table
ADD COLUMN test_column_2 INT UNSIGNED NULL AFTER test_column_1;

を実行すると、この操作はINSTANTアルゴリズムが採用されるので

SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%test_db/test_table%";

NAME	test_db/test_table
TOTAL_ROW_VERSIONS	1

のようにバージョンがインクリメントされます。

なお、これについては未検証ですが、https://blogs.oracle.com/mysql-jp/mysql-80-instant-add-drop-columns-jp には

このような行バージョンの更新と維持にはバックグラウンドでのメンテナンスが必要なので、制限事項を設けています。行バージョンとして管理できるのは、最大64バージョンです。つまり一つのテーブルに対して最大64回のALTER TABLE … ADD/DROP操作が可能であるということです。

という記述があり一定回数を超えるとテーブルの再構築が必要となる可能性があるので注意が必要そうです。

また、INSTANTアルゴリズムで使用可能な操作は少しずつ拡充されています。

上記で示したAFTERを用いたカラム位置指定や、DROP COLUMNについてはMySQL 8.0.29からINSTANTアルゴリズムが使えるようになりました。（https://blogs.oracle.com/mysql-jp/mysql-80-instant-add-drop-columns-jp ）

INSTANTアルゴリズムを使える操作については、https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.htmlに表があります。

例えば、現状indexを追加、foreign key制約を追加するといった操作はINSTANTアルゴリズムを使えないようです。

実験環境

以下の環境で実験を実施しました：

• MySQL 8.0.42

• テストテーブル：10,000件、100,000件、1,000,000件の3パターン

• テーブル構造：id（主キー）、col_a（VARCHAR）、col_b（INT）、col_c（DATE）

CREATE DATABASE IF NOT EXISTS alter_table_experiment;
USE alter_table_experiment;

CREATE TABLE test_10k (
  id INT AUTO_INCREMENT PRIMARY KEY,
  col_a VARCHAR(100),
  col_b INT,
  col_c DATE
) ENGINE=InnoDB;

CREATE TABLE test_100k (
  id INT AUTO_INCREMENT PRIMARY KEY,
  col_a VARCHAR(100),
  col_b INT,
  col_c DATE
) ENGINE=InnoDB;

CREATE TABLE test_1m (
  id INT AUTO_INCREMENT PRIMARY KEY,
  col_a VARCHAR(100),
  col_b INT,
  col_c DATE
) ENGINE=InnoDB;

測定した操作パターン

• ADD COLUMN

3つのアルゴリズム（INSTANT、 INPLACE、 COPY）で比較しました。

• ADD COLUMN AFTER

1と同様に3つのアルゴリズムでカラム位置を指定しました。

• ADD INDEX

この操作はINSTANTアルゴリズムは使えないため、INPLACE、 COPYのアルゴリズムで計測しました。

• ADD COLUMN … AFTER … ADD INDEX

この操作はINSTANTアルゴリズムは使えないため、INPLACE,、COPYのアルゴリズムで計測しました。カラム位置を指定するという操作がどのようにパフォーマンスに影響するかを検証するため、このケースを加えました（ADD INDEXするだけでなく、さらにカラム位置を指定するとどうなるのか？）

実験結果

実験結果

（両対数グラフであることに注意）

考察：

• ADD COLUMNの操作について、INSTANTアルゴリズムは、データ件数に関わらず常に0.1秒以下で完了しました。データ件数に比例しないパフォーマンスになっているように見えます。これは、実際のテーブルデータには一切手を加えず、メタデータのみを更新するためだと考えられます。一方、INPLACEとCOPYアルゴリズムは、データ件数に比例して実行時間が増加しています。

### test_10k
tbl       algo      op          seconds
test_10k  INSTANT   ADD_COLUMN  0.0393
test_10k  INPLACE   ADD_COLUMN  0.0725
test_10k  COPY      ADD_COLUMN  0.0742

### test_100k
tbl        algo      op          seconds
test_100k  INSTANT   ADD_COLUMN  0.0128
test_100k  INPLACE   ADD_COLUMN  0.2426
test_100k  COPY      ADD_COLUMN  0.2283

### test_1m
tbl      algo      op          seconds
test_1m  INSTANT   ADD_COLUMN  0.0720
test_1m  INPLACE   ADD_COLUMN  2.4213
test_1m  COPY      ADD_COLUMN  1.7140

• ADD COLUMN … AFTERの場合も、INSTANTの場合はカラム指定していない場合と同様にデータ量に関わらず1秒未満で完了しており、データ件数に比例しないパフォーマンスになっているように見えます（MySQL 8.0.29以降では、AFTER句を使用したカラムの位置指定追加もINSTANTアルゴリズムで実行できるようになりました）。

### test_10k
tbl       algo      op                seconds
test_10k  INSTANT   ADD_COLUMN_AFTER  0.0194
test_10k  INPLACE   ADD_COLUMN_AFTER  0.0655
test_10k  COPY      ADD_COLUMN_AFTER  0.0674

### test_100k
tbl        algo      op                seconds
test_100k  INSTANT   ADD_COLUMN_AFTER  0.0089
test_100k  INPLACE   ADD_COLUMN_AFTER  0.2311
test_100k  COPY      ADD_COLUMN_AFTER  0.3245

### test_1m
tbl      algo      op                seconds
test_1m  INSTANT   ADD_COLUMN_AFTER  0.0186
test_1m  INPLACE   ADD_COLUMN_AFTER  1.7575
test_1m  COPY      ADD_COLUMN_AFTER  1.5608

• インデックスの追加は、データ件数に比例して時間がかかっています。これは、インデックス作成には、全データをスキャンしてインデックス構造を構築する必要があるためと考えられます。

• インデックス追加＋カラム位置の指定も計測しましたが、単にインデックスを追加した場合と同様な特性を見せておりカラム位置の指定の有無は関係ないようです。

まとめ

結論としてはINDEX貼るのとかは処理重いよねという至ってありきたりな結論になっていますが・・・

とはいえ、ALTER TABLEは使用されるアルゴリズムによって性能が大きく異なるのでどのような操作をしようとしているのか改めてチェックしておくことが必要です。

使用するアルゴリズムを明示してあらかじめテスト実行しておくことで、予想外の動作を防ぐことができるでしょう。

その上で、重いアルゴリズムを用いる操作をするときには事前に実行時間を見積もるなどの工夫がサービス運用の観点から必要だと考えられます。

この記事で書かなかったこと

今回は実行時間が予想外に伸びたという困りごとから出発して調査したので、DDLにおいてALGORITHMと同様に重要であるLOCKについては触れることができませんでした。

（正直、DDLに関して調べてみるとどこまでも深い海が広がっていることが分かり、今回は本当に触りしか触れることができませんでした）

今回私が実行したDDLでは特に問題はなかったのですが、テーブルサイズが大きい、テーブルが参照・更新される頻度が高いなどの場合は、LOCKについても気にしておく必要があるでしょう。

（最初に触れましたがオンラインDDLにおいても、ロックは実行されるためです）

LOCKの方式についても明示した上で実行することができるので、事前に試してみる価値がありそうです。

DDLの実行に関するトラブル例や、より包括的なオンラインDDLの解説をいくつか参考文献に載せておりますのでご参照ください。

特に、最新版では直っているようですが、　https://blog.s-style.co.jp/2024/12/13360/ にて言及されている通りオンラインDDLのバグも一部バージョンであるようですので気をつけたほうが良さそうです。

（この内容も含めて保証はできませんので実際のサービスでの実行等はあくまでご自身の責任でお願いいたします）

しかし、実行中に更新を許可する ALGORITHM=INPLACE でかつ LOCK=NONE であるオンラインDDLには、以下のようなバグが存在しています。
Bug #115608 Inplace ALTER TABLE might cause lost rows if concurrent purge
Bug #115511 Inplace ALTER TABLE might fail with duplicate key error if concurrent insertions
上記バグについては、DDL実行中に予期せず行が削除されるというものと、プライマリキーやユニークキーの重複が発生していないのにも関わらず、重複キーエラーでDDLの実行がエラーとなるといったものです。

引用：https://blog.s-style.co.jp/2024/12/13360/

参考文献

• https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
• https://blogs.oracle.com/mysql-jp/mysql-80-instant-add-drop-columns-jp
• https://y-asaba.hatenablog.com/entry/2018/12/21/002811
• https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html
• https://dev.mysql.com/doc/refman/8.0/ja/alter-table.html
• https://kaminashi-developer.hatenablog.jp/entry/2023/10/23/mysql-online-ddl
• https://blog.s-style.co.jp/2024/12/13360/
• https://zenn.dev/levtech/articles/226fcb30e99e70
• https://tech.andpad.co.jp/entry/2020/08/18/132130
• https://qiita.com/ssc-ksaitou/items/451d1d462f52ad360860

宣伝

Legalscapeではエンジニア全方面で募集中です。MySQLやその他ミドルウェアの設定に興味がある方もそうでない方もぜひカジュアル面談からでもご応募お願いします！

www.legalscape.jp