第209回 MySQLのALTER TABLEステートメント実行時の注意点

今回はALTER TABLEステートメント実行時の注意点について紹介します。

MySQLを運用していると、サービスの仕様変更や新機能のために既存のテーブルに対してテーブル定義を変更することが多いと思います。その際によく起こる問題や注意点に説明します。

ALTER TABLEステートメント

まずは、簡単にALTER TABLE ステートメントについて紹介します。テーブル定義を変更するにはALTER TABLEステートメントを利用します。MySQL 5.6以降からInnoDBのテーブルにおいて、オンラインDDLが可能になっています。本連載においても第30回 InnoDBオンラインDDLについてにて紹介しています。

オンラインDDLでは、DDL実行に最小限の時間のロックを取得することで、そのDDLと並列で実行されるDMLの待機も最小限になります。そのため、サービス無停止でALTER TABLEステートメントを実行しても問題ない場合もあります。しかし、操作の種類によってはオンラインでの実行ができないケースがあります。MySQLのALTER TABLEステートメントは大きく分けて3つの方式があります。

インスタント方式
インプレース方式
コピー方式

どのような操作がどの方式に当てはまるかは、15.12.1 Online DDL Operationsを参考にしてください。

インスタント方式

インスタント方式はテーブル定義の変更をデータディクショナリのメタデータのみを書き換えるため、テーブルのサイズの大小に関わらず即時に完了します。インスタント方式で動作する操作には以下のようなものがあります。

カラムの追加
カラム名の変更
インデックスの削除

構文としては、ALTER TABLEステートメントにALGORITHM=INSTANTをつけるとインスタント方式で動作します。インスタント方式で実行できない操作の場合はエラーとなります。

ALGORITHM=INSTANTの例

mysql> ALTER TABLE t0 ADD col1 int, ALGORITHM=INSTANT;

補足として、カラムの追加においては最後のカラムに追加する場合は、MySQL 8.0以降からインスタント方式で追加可能です。AFTER句やFIRST句を用いた中間にカラムを追加する場合はインプレース方式で動作していました。MySQL 8.0.30以降からは中間にカラムを追加する際もインスタント方式で可能になりました。また、MySQL 8.0.30以降はカラムの削除もインスタント方式で可能です。

インプレース方式

インプレース方式はテーブル定義をインプレースで変更します。並列のDMLはロックされず、オンラインでの実行が可能です。テーブルの再構築を伴う操作と伴わない操作がありますが、再構築を伴う操作はテーブルサイズに比例して実行時間は大きくなります。このあと紹介するコピー方式よりも高速で処理を完了します。

インプレース方式で動作する操作は以下のようなものがあります。

インデックスの追加
カラムのNULLまたはNOT NULLへの変更

構文としては、ALTER TABLEステートメントにALGORITHM=INPLACEをつけるとインプレース方式で動作します。インプレース方式で実行できない操作の場合はエラーとなります。

ALGORITHM=INPLACEの例

mysql> ALTER TABLE t0 ADD KEY t0_idx1(col1), ALGORITHM=INPLACE;

コピー方式

コピー方式はテーブル定義の変更をテーブルのデータを読み取り、コピーし再構築します。ALTER TABLEステートメント実行中はそのテーブルをロックします。そのため並列でのDMLは待機されるので、これはオンラインDDLではありません。大きなテーブルに対してステートメントを実行するには、サービスのメンテナンスが必要になるでしょう。

コピー方式で動作する操作は以下のようなものがあります。

カラムのデータ型変更
FULLTEXTインデックスの作成
キャラクターセットの変換

構文としては、ALTER TABLEステートメントにALGORITHM=COPYをつけるとコピー方式で動作します。

ALGORITHM=COPYの例

mysql> ALTER TABLE t0 CONVERT TO CHARACTER SET utf8mb4_bin, ALGORITHM=COPY;

ステートメントにALGORITHM句を付与せずに実行すると、インスタンス方式→インプレース方式→コピー方式の順に、その操作が実行可能なものから選択されます。

ALTER TABLEステートメント実行時の注意点

ALTER TABLEステートメントを実行する前に確認しておくべき注意点があります。

メタデータロック

メタデータロックとはデータベースオブジェクトへの同時アクセスを制御し一貫性を確保するために、オブジェクトのメタデータに対して取得するロックです。詳しくはリファレンスマニュアルの8.11.4 メタデータのロック、または本連載で紹介した第180回 MySQLのメタデータロックについてをご参考ください。

ALTER TABLEステートメントを実行すると、対象のテーブルに排他メタデータロックを取得します。この排他メタデータロックは前述の方式ごとに取得するタイミングとロックの保持期間が異なります。

方式ごとのメタデータロック取得

方式	ロック取得タイミング	サービスへの影響度
インスタント	データディクショナリの排他メタデータロックのみ取得。ただし、ステートメント操作は即時で完了	低
インプレース	ステートメント開始時と完了時の短期間だけ排他メタデータロックを取得	中
コピー	ステートメント実行開始から完了するまで、排他メタデータロックを取得	高

運用中によく起こる問題としては、他セッションとのメタデータロックの競合があります。コピー方式では“⁠ステートメント実行開始から完了するまで排他メタデータロックを取得⁠”するため、他セッションからのDMLはその処理が完了されるまで待機します。コピー方式がテーブルロックとなるのはメタデータロックによるものです。

インプレース方式では“⁠ステートメント開始時と完了時の短期間だけ排他メタデータロックを取得⁠”するため、他セッションからのDMLはわずかなロック待ちまたは待機なしで完了するケースが多いです。しかし、タイミングによっては問題が起こりうるので注意が必要です。インプレース方式のALTER TABLEステートメントを開始したあとに、対象のテーブルに対してロングクエリが実行されたとします。そのロングクエリは実行されたままだとALTER TABLEステートメント完了時に取得が必要なメタデータロックが取得できず待機し、timeoutとなり失敗するケースがあります。

そして、ALTER TABLEステートメントがメタデータロック取得に待機することで、別の問題も起こります。

他セッションがトランザクション中に触ったテーブル（SELECTのみを含む）は共有メタデータロックを取得します。そのテーブルに対してALTER TABLEステートメントを実行すると、そのトランザクションが終了するまでメタデータロックを取得できず待機します。さらに、ALTER TABLEステートメントが待機しているとそのテーブルに対しての他セッションからの後続のクエリもすべて待機されてしまい、サービス障害が起こる可能性があります。

よって、ALTER TABLEステートメント実行する前には対象のテーブルに対して実行中のロングトランザクションやクエリがないかを確認してから実行する必要があります。メタデータロックを取得しているセッションを探すには、performance_schema.metadata_locksテーブルを確認しましょう。

また、レプリカにおいても同様です。レプリカにて対象テーブルへの長時間実行されているクエリがあると待機し、レプリケーション遅延を招く恐れがあります。

ディスク容量

再構築を伴うインプレース方式やコピー方式においては、一時的に対象のテーブルと同じサイズのテーブルを内部で作成することになります。そのため、ディスクの空き領域が対象のテーブルサイズ以上残っていることを確認しましょう。

レプリケーション遅延

インスタント方式や小さなテーブルであれば問題ありませんが、インプレースやコピー方式で大きなテーブルであるとレプリケーション遅延は発生します。サービスに読み取り専用レプリカを利用していて、レプリケーション遅延が許容されないのであればサービス停止が必要になるでしょう。

まとめ

今回はALTER TABLEステートメント実行時の注意点について紹介しました。安全にALTER TABLEステートメントを完了させるために、筆者はこれら注意点を必ず確認するようにしています。また、今回は詳しく触れませんが、インプレース方式やコピー方式によるレプリケーション遅延やテーブルロックを回避したいことがあると思います。その場合はpt-online-schema-changeやgh-ostのようなオンラインマイグレーションツールを利用するといいでしょう。これらのツールを利用する際も、ディスク容量とメタデータロックに関して同じく注意しなければいけません。