MySQL で大きなテーブルに ALTER TABLE を安全に実行する

数億行を超えるテーブルにカラム追加やインデックス変更を行う場面は避けて通れない。しかし、無計画に ALTER TABLE を実行するとテーブルが長時間ロックされ、サービスが停止する事態に陥る。MySQL のバージョンや変更内容によって挙動が大きく異なるため、事前の調査と戦略が不可欠だ。

ALTER TABLE の内部動作を理解する

MySQL の ALTER TABLE には大きく分けて 3 つの実行方式がある。どの方式が選ばれるかは変更内容とストレージエンジンによって決まる。

COPY 方式

テーブル全体を新しい構造でコピーし直す。最も遅く、処理中は書き込みがブロックされる。古い MySQL やカラムの型変更などで使われる。

INPLACE 方式

テーブルのコピーを作らずにメタデータやインデックス構造を直接変更する。多くの操作で DML（INSERT/UPDATE/DELETE）を並行実行できるが、開始時と終了時に短いメタデータロックが発生する。

INSTANT 方式

MySQL 8.0.12 以降で追加された最速の方式。メタデータの変更のみで完了し、テーブルサイズに関係なく一瞬で終わる。カラムの末尾追加など限定的な操作で利用可能。

どの方式が適用されるかは ALGORITHM 句で明示的に指定できる。

-- INSTANT を試み、無理なら失敗させる
ALTER TABLE orders ADD COLUMN memo VARCHAR(255), ALGORITHM=INSTANT;

-- INPLACE を指定
ALTER TABLE orders ADD INDEX idx_created (created_at), ALGORITHM=INPLACE;

-- ロックレベルも指定可能
ALTER TABLE orders ADD INDEX idx_status (status),
  ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INSTANT を指定して実行がエラーになれば、その操作は INSTANT に対応していないとすぐに判断できる。本番で試す前にステージング環境でこの確認をしておくのが安全だ。

INSTANT で済む操作を把握する

MySQL 8.0 では以下のような操作が INSTANT で実行できる。テーブルサイズに依存しないため、数億行のテーブルでも瞬時に完了する。

カラムの末尾追加（8.0.12〜）

カラムの任意位置への追加（8.0.29〜）

カラムの削除（8.0.29〜）

カラムのデフォルト値変更

ENUM/SET の末尾への値追加

生成カラムの追加・削除

カラムの RENAME（8.0〜）

逆に、カラムの型変更、主キーの変更、文字セットの変更などは INSTANT では実行できず、INPLACE か COPY が必要になる。

Online DDL の落とし穴

MySQL 5.6 以降の Online DDL（INPLACE + LOCK=NONE）は DML を並行処理できるが、万能ではない。見落としがちな問題点がいくつか存在する。

メタデータロックの待ち

ALTER TABLE の開始時と終了時にメタデータロックが必要になる。長時間実行中のクエリやトランザクションがあるとロック取得待ちが発生し、後続のクエリもすべてブロックされる連鎖が起きる。

ディスク容量の消費

INPLACE でも内部的にはログファイルやソート用の一時ファイルが作られる。大きなテーブルではテーブルサイズと同程度の空き容量が必要になる場合がある。

メタデータロックの連鎖は特に厄介で、ALTER TABLE 自体は LOCK=NONE でも、ロック取得を待っている間に後続のすべての SELECT がブロックされる。対策として lock_wait_timeout を短めに設定し、ロック取得に失敗したら時間をおいてリトライする方法がある。

-- ロック待ちのタイムアウトを 5 秒に設定
SET SESSION lock_wait_timeout = 5;

ALTER TABLE orders ADD INDEX idx_created (created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

pt-online-schema-change で安全に変更する

Percona Toolkit の pt-online-schema-change は、大規模テーブルへの ALTER TABLE を安全に実行するための定番ツールだ。内部的にはシャドウテーブルを作成し、トリガーで変更を同期しながらデータをコピーする仕組みになっている。

空のシャドウテーブルを新しいスキーマで作成

元テーブルにトリガーを設定して DML を同期

チャンク単位でデータをコピー

コピー完了後にテーブル名を入れ替え

基本的な使い方は以下のとおりだ。

pt-online-schema-change \
  --alter "ADD COLUMN memo VARCHAR(255)" \
  --execute \
  --chunk-size=1000 \
  --max-lag=1s \
  D=mydb,t=orders

--max-lag はレプリケーション遅延の上限を指定し、遅延が大きくなると自動的にコピーを一時停止してくれる。--chunk-size でコピーの粒度を調整すれば、負荷を分散できる。

gh-ost という選択肢

GitHub が開発した gh-ost はトリガーを使わない方式を採用しており、pt-online-schema-change のトリガーに起因する問題を回避できる。バイナリログを読み取って変更を同期するため、元テーブルへの影響が最小限に抑えられる。

pt-online-schema-change

トリガーベースで確実に同期。ただし、トリガーのオーバーヘッドが書き込み性能に影響する。既存のトリガーがあるテーブルには適用できない。

gh-ost

バイナリログベースで同期。元テーブルにトリガーを追加しないため、書き込み性能への影響が小さい。ただし、ROW 形式のバイナリログが必須。

実行前のチェックリスト

大きなテーブルに ALTER TABLE を実行する前に、以下の確認を習慣づけておくと事故を防げる。

対象テーブルの行数とデータサイズを確認する

ALGORITHM=INSTANT で実行できるか試す

ディスクの空き容量がテーブルサイズの 2 倍以上あるか確認する

実行中のロングトランザクションがないか SHOW PROCESSLIST で確認する

レプリカがある場合はレプリケーション遅延の監視体制を整える

ステージング環境で所要時間を計測する

本番環境で何時間もかかる ALTER TABLE を途中でキャンセルすると、ロールバックにも同程度の時間がかかることがある。事前の見積もりと計画がトラブルを未然に防ぐ鍵となる。