これはなに

この記事は米シリコンバレーでデータベースコンサルや教育事業を展開するKloudDB社がポストした『Understanding How ONLINE DDL (INPLACE) works in MySQL』の翻訳記事です。

この記事ではDDL（スキーマ変更クエリ）の内部処理について詳細に解説しています。DDLはシンプルに利用できるものの、一歩踏み込むと複雑怪奇で理解の難易度は高いものでした。この記事はそこに焦点を当てたものになります。

翻訳するにあたり、技術的な正確性を担保しつつ、日本語表現として自然になるよう努めました。もし記事の中で技術的な観点で不正確な箇所があれば訳者の責任ですので、コメント欄などでご指摘いただけると幸いです。
また、翻訳について許可を下さったSrinivasa R Inaganti氏（同社CEO）に感謝します。

以下、訳者による前書き的なものを挟んで、翻訳です。

この記事のねらい

この記事のねらい

MySQLのオンランDDLはちゃんと理解しようとすると難しい
- DDLの実行方式に３種類存在し、その中の一つであるINPLACE方式のDDLでもさまざまな種類が存在する
- それを表現したのがMySQL公式のこのページ
  - DDLの種類ごとに情報がテーブルで整理してあるが、上記を正確に理解するのは難しい
そんな課題感に対して「INPLACE方式の内部処理を理解することでこの複雑さが理解できますよ」と解決策を提示したのがこの記事
- 一方で読み手の前提知識や強い動機が求められる
- その点は訳者が適宜補足を入れたり、図を入れたりして読みやすくした
DDLについて調べてみたものの複雑で理解できないな、と思っている人はぜひ読んでください
- いまは読まなくても、この記事の存在を覚えておくといつか役に立つはず

導入

データベースはその稼働期間中、様々なスキーマ変更を受け付けます。これらの変更の頻度は、通常のDML作業負荷に比べれば少ないかもしれませんが、あらゆるDB作業負荷の重要かつ複雑な部分です。

MySQLは三つのアプローチを提供します。

COPY（シャドウテーブルの作成、コピー、スワップ）
INPLACE（DDLの進行中に並列ワークロードを可能にするオンラインDDL）
INSTANT（MySQL8系。DDLの即時実行を可能にする）

これらの方式はすべて同じ目標を達成するものですが、COPYは並列ワークロードをブロックし、INPLACE/INSTANTは並列ワークロードを許可するなど、並列ワークロードの実行方法が異なります。

MySQLのDDL実行で最も広く使用されているデフォルトの方式の1つがINPLACEです。この記事では、MySQLのINPLACE ALETRを取り上げ、内部処理における処理フロー、ロックモデル、作成される中間オブジェクトについて解説します。

なお、本稿はMySQLの特定バージョンにおける調査に基づいており、最近のバージョンではいくつかの違いがあるかもしれません。

INPLACE方式

COPY方式とその問題点

INPLACE方式ーーまたの名をオンラインDDLは、既存のCOPY方式を改善するためのより良い代替案としてMySQL5.6で導入されました。

COPY方式は既存のテーブルのコピーとなるシャドウテーブルを作成し、そこにデータを流し込みます。コピー処理が実行されている間、当該テーブルに対する並行DML作業は進行することができません。

INPLACEは、DDLの実行中に並行DML作業の実行を許可するために、この基本的な仕組みを変更しました。

INPLACE方式のバリエーション

INPLACE方式にはいくつかのバリエーションがあります。

メタデータのみを更新するINPLACE（カラム名の変更など）
既存の古いオブジェクトに触れることなく新しいオブジェクトを追加するINPLACE(インデックスの追加など)^[1]
テーブルの再構築を引き起こすINPLACE（既存のカラム定義の変更や新しいカラムの追加)）

INPLACE（またはONLINE DDL）の主な課題は、テーブルに対する進行中のDML変更をどのように追跡し、メインテーブルにどのように組み込むかということです。

特定の種類の詳細に入る前に、ALTERが一般的なレベルで実行するステップを理解しましょう。それを理解したら、どのステップが各バリアントに適用できるかをチェックします。

基本的なALTERの実行ステップ

ここではALTERの実行ステップを解説します。5.7を基本として解説し、8.0での差分はのちに解説します。

1. 構文解析

ALTER文を解析し、以降の実行フロー中で利用するコンテキストを用意する。

2. 変更するオブジェクトの必要なロックを取得する

変更するオブジェクトの必要なロックを取得する（以下全て）。

No.	オブジェクト	ロックタイプ	期間
1	グローバル	占有インテンション	ステートメント
2	スキーマ	占有インテンション	トランザクション
3	テーブル	昇格可能な共有ロック	トランザクション

3. ALTER実行後のテーブル定義をメモリ上で表現する

ここではALTER実行後のテーブル定義構造をメモリ上で表現しています。また、このステップはALTER文のバリデーション（同じカラムを重複作成しているだとか、存在しないカラムのインデックスを作成しているだとかのチェック）をする役割もあります。

4. 変更後の定義でMySQLサブシステムに一時テーブルを作成する

次に、新しい定義で一時的な中間テーブルを作成します。

バージョン5.7まで、MySQLは２つのサブシステムで同じテーブル定義を展開させていました。それがMySQLサブシステムとInnoDB SE（ストレージエンジン）サブシステムです。これらのサブシステムではそれぞれのデータディクショナリ上に自前のテーブル定義をキャッシュして持つことになります。この時点ではMySQLサブシステムにのみテーブル定義が作成され、frmファイルが作成されます。

なお、InnoDB SEのデータディクショナリにはまだテーブル定義が登録されておらず、このデータ保持状態の差分がDDLの不整合を引き起こす原因となりました。（MySQL8系にてデータディクショナリ自体が統合され解決）

5. INPLACEとして実行可能かどうかのチェック

操作がINPLACE方式で実行可能かどうかをチェックします。

全ての操作がINPLACE方式で実行可能なわけではなく、ステートメントによって実行中に必要なロックが異なります。あるステートメントではALTERを流している間ずっと（古いテーブルデータをコピーする間を含めて）占有ロックをとりますし、他のステートメントでは短い期間だけの占有ロックで済むこともあります。

これらのロックは全て、このステップの一部として存在します。ユーザが特定のロックモードでALTERを実行するよう求めた場合、それが実現できるかどうかを相互に検証します。ユーザは必要なロックより強いロックを要求することはできますが、弱いロックを求めることはできません（たとえばステートメントが占有ロックを必要としているのに、ユーザが共有ロックを要求する場合など）。

!

訳者補足

これはオンラインDDLの構文で指定できるLOCK構文のこと。DDLでは以下のようにLOCKを指定できるが、ステートメントがそのロックを利用できない場合、エラーとなる。

ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=NONE;
// エラー

6. 占有ロックへの昇格

ステップ２で取得していたテーブルへの昇格可能な共有ロックを占有ロックへ昇格します。

これにより他のスレッドやセッションが同テーブルへアクセスすることを防ぎ、ALTERのセッションが占有的にアクセスできるようにします。

なお当初は上記のMySQLサブシステムが取得したロック情報をもとにInnoDBもファイルロックを取得するはずでしたが、単にステートメントの開始を示すだけとなりました。

7. ALTER処理の準備

InnoDB SEで新しいテーブル定義を作成します。このテーブルは最終的には既存のテーブルと入れ替わるため、一時的な中間テーブルと呼ばれます。

ステップ４でMySQLサブシステムにテーブルを作成しましたが、このステップではInnoDB SEサブシステムで同じことを行います。ここではデータディクショナリへテーブルオブジェクトを新規追加したり、テーブルデータを格納するためのibdファイルを低レベルで作成したりします。

次に、古いテーブルはこの時並行して更新される可能性があります。これは一時的なステージングエリアに記録する必要があります。このステージングエリアは必要な更新を記録するために古いテーブルに添えられます。

最後に、リードビューを割り当てます。ALTERは古いテーブルから新しいテーブルにデータをコピーする必要があります。一貫性のあるデータを読み取るために、ALTERは指定されたタイムスタンプでの読み取りスナップショットを必要とします。

!

 訳者補足：単語の補足をいくつか。
データディクショナリ
メタデータ的なもののこと

MySQL公式ドキュメントにも記載があるので、もっと詳しく知りたい人はこちらを読んで欲しい

ibdファイル
「デフォルト設定では１テーブルに対して同じ名前の１つのibdファイルが作成される。テーブルの構成要素であるカラム、行、インデックスなど全て同じファイルに情報が記録される。テーブルスペースファイルと呼ばれる」（『MySQL運用・管理[実践]入門』p46より引用）

リードビュー
「個々のトランザクション（REPEATABLE-READの場合）あるいはクエリ（READ-COMMITEDの場合）が参照できるデータのスナップショットは、リードビュー（Read View）と呼ばれる」（『詳解MySQL5.7』p138より引用）
ここでは、コピー元となるスナップショットとしてリードビューを利用するという意図

8. アップグレード可能共有ロックへの降格

ALTER処理の準備が終わったら、ロックを昇格可能な共有ロックに戻します。準備フェーズではデータディクショナリを変更するためのクリティカルな操作が行われました。すでに変更はデータディクショナリに登録されたため、今後実行される操作が競合する可能性があったとしてもブロックされることはありません。（いくつかの種類のALTERは占有ロックを取るのでその限りではありません）

9. ALTER処理のコピーフェーズ

古いテーブルから新しいテーブルへデータをコピーします。この操作の詳細を見ていきましょう。

まずInnoDBはデータをインデックスの形で保存するため、コピー操作は実質的にクラスタ化インデックスを含む全てのインデックスの再構築となります。

InnoDBのクラスタ化インデックスは全てのカラムを持っています。そのため、それぞれのインデックスから各インデックスを再構築するよりも、クラスタ化インデックスをスキャン/パースして新たなクラスタ化インデックスと他のインデックスを構築する方が良いです。これにより一度の読み取りパスでインデックスを作成できます。

クラスタ化インデックスは、ソートされた状態でエントリを格納します。InnoDBはこのプロパティを使用し、Bulk-Btree-Buildと呼ばれる構築アルゴリズムでクラスタ化インデックスの再構築を行います。この再構築方法によって、再構築後のツリーが最適な構造を保つのにも役立ちます。

他のインデックスについてもクラスタ化インデックスから作成されます。このインデックスのデータは、キーに対してソートされていない可能性があります。ただ、十分な数のデータが読み込まれると、その時点でデータがソートされ、マージファイルという中間ファイルに書き込まれます。このようなN個の中間バッファは全体でソートされた状態となるためにマージソートで再マージされます。

9(a). 並行DMLの適用第一フェーズ

ステップ９はコピー操作を行うため、非常に長い時間がかかるかもしれません。そのため、ここではまだ並行DMLを許しています。

コピー操作が完了する前でも、並行DMLによって更新されたデータはインデックスに反映されます。テーブルデータはまだ変更される可能性があるため、これは最終的な取り込み操作ではありません。ただこのコピーフェーズはALTER処理の中で最も長いフェーズであるため、ほとんどの並行DMLはこの時点で取り込まれます。

10. 占有ロックへの昇格

最後のコミット処理を開始する前に、昇格可能な共有ロックが再び占有ロックに昇格されます。これにより、該当テーブル上にDMLが実行されないことが保証され、ALTERセッションも適用操作の最終フェーズを完了することができます。

このときInnoDB SEサブシステムでも占有ロックを取得します。

11. ALTER処理のコミットフェーズ

他のスレッドが修正対象のテーブルへアクセスするのをブロックします。

並行DMLの適用操作の最終/第二フェーズを完了します。これによりALTERフロー中にテーブルに対して行われた全ての並行DMLが新しく作成された変更後のテーブルに適用されます。そこではibdファイルの名前をInnoDB SEのレベルで変更します。この操作はSWAPアルゴリズムを利用しており、それによって古いファイルの名前と新しいファイルの名前が交換されます。具体的には

古いファイルは一時テーブル名のものに
一時テーブルのibdファイルは古い（本来の）テーブル名に

変更されます。

「一時テーブル」という名前になった古いテーブルは削除されます。データ辞書から古いテーブルオブジェクトのコピーを削除します。必要な変更はテーブルスペースにも行われ、統計情報を更新やMySQLサブシステム上でのfrmファイルの置き換えが行われます。

これらによって新しいテーブルが利用開始になります。

12. その他

binlogへのALTER適用、クエリーキャッシュの無効化、メタデータロックの解放など。

INPLACEを理解する

では、最初に紹介したINPLACEの３パターンに沿って見ていきましょう。

1. メタデータのみを更新するINPLACE

例：alter table sbtest1 change column col1 col2 int not null default 1000;

メタデータのみの更新になるため、テーブルの再構築が不要であり、コピー操作をスキップできます。限られた操作であるため、競合するロックでALTERが遅延しない限りは瞬時に完了します。

手順に関しては、コピー手順を除いて、上記のすべての手順がリストされた順に実行されます。ibdファイルは再作成されず、テーブルスペースIDも変更されません。

2. 既存カラムに触らずオブジェクトを追加するINPLACE

例：alter table sbtest1 add index k_1(k);

インデックスなど、新しいオブジェクトを追加する操作を指します。これは既存のテーブルを再構築する必要がありませんが、一部のカラム追加操作はテーブルの再構築が必要となります。

ここでもALTERは基本的なステップを踏みますが、今回はステップ９のCOPYフェーズが新しいオブジェクトの追加に限定されます。コピーのアルゴリズム自体は同じです。つまり、クラスタ化インデックスからエントリを読み取り、セカンダリインデックスエントリを構築し、それらを一時/中間マージファイルにキャッシュします。これらのエントリをソートしてからBulk BTree Buildやソート済みビルドを使用して挿入します。メインのクラスタ化インデックスを再構築しないため、操作は非常に高速で同じ.ibdファイル（テーブルスペースIDも）を再利用できます。

元のテーブルが変更されないため、並行DMLの変更は元のテーブルを変更し続けます。したがって、並行DML操作を再マージ/適用する必要がありません。

3. テーブルの再構築を伴うINPLACE

例：alter table sbtest1 add column col int not null default 1000;

上記の基本パターンをなぞり、クラスタ化インデックスが変更されテーブルの再構築も伴います。

MySQL8.XからのUnified Data-Dictionary

MySQL8系ではMySQL5.7までに存在したバグの要因となっていたデータディレクショナリの二重管理が変更になっています。

5.7まででは、テーブルメタデータはMySQLサブシステムに.frmファイルの形でひとつ、InnoDBストレージエンジンにデータディレクショナリの形でひとつ保存されていました。これまで見てきたように、この両者が一致しない場合、テーブルは簡単に矛盾した状態になってしまいます。

MySQL8系からは.frmファイルが廃止され、Unified Data-Dictionaryが導入されました。これにより、テーブルメタデータが単一の一貫したコピーを取るようになり、テーブルに関連した複数の不整合の解消に役立ちます。MySQL8系では、DDLの原子性も確保されており、実行中に問題が発生した場合に変更をロールバックできます。（訳者注：トランザクションを張ってロールバックするというのはできない。あくまで実行中に問題があったら自動的にロールバックされるという話）

では、この変更がALTER処理の実行フローをどう変更したか見ていきましょう。

MySQL8系は5.7と同様に、基本的な実行フローで概説された全てのステップを実行しますが、いくつか異なるアクションもあります。これらの変更点を確認しましょう。

基本的なDDL実行フロー：MySQL8以降の主な変更点

「ステップ2.変更するオブジェクトの必要なロックを取得する」に関するもの

以下の通り、いくつかのロックが追加されました。

No.	オブジェクト	ロックタイプ	期間
4	TABLE^[3]	占有インテンション	トランザクション
5	BACKUP LOCK	占有インテンション	トランザクション

TABLEのロックはMySQL5.7でも存在しましたが、.frmファイルの不具合により正しくロックが取得されませんでした。TABLEロックはALTERの実行中に他のテーブルスペースレベルの操作が許可されないようにします。

BACKUP LOCKは実行中インスタンスのアクティブなバックアップを取得するためのものです。ALTERステートメントはBACKUP LOCKを取得し、テーブルの変更中に、バックアップ処理が不整合なバックアップを取得するのを防ぎます。

「ステップ4.変更後の定義でMySQLサブシステムに一時テーブルを作成する」に関するもの

MySQL8系では.frmファイルがないため、メモリ上で変更されたテーブルオブジェクトを作成し続けます。これはUnified Data-Dictionaryに永続化します。

（永続化は、ストレージエンジンがInnoDBのようにアトミックDDLをサポートしている場合は延期されます。）

「ステップ7.ALTER処理の準備フェーズ」に関するもの

ステップ7ではInnoDB SEでメモリ上にテーブルオブジェクトが作成され、データディクショナリへのエントリも作成されていました。このステップはMySQL8系でも引き続き重要です。

InnoDB互換のインメモリテーブルオブジェクトが作成された後、中間テーブル（と、テーブルスペース）が作成され、上記のエントリが統一されたデータディクショナリに永続化されます。既存のエントリは削除され、新しいspace idを持つ新しいテーブルスペースに対応する新しいエントリが追加されます。

このステップの後でinformation_schema.innodb_tablespaceを見ると、新しいテーブルスペースIDが表示されます。

その他

ステップ９（コピー、並行DMLの適用）、ステップ１１（コミット）などの残りのステップは以前と変わりありません。

MySQL8系では作成された一時/中間テーブルに対してもロックを取得します。

統合データディクショナリテーブルの原子性が確保されている（トランザクションストレージエンジンにキャッシュされている）ので、ALTERが失敗してもトランザクション全体がロールバックされ、統合データディクショナリテーブルが元に戻ります。

ALTER実行中にクラッシュして再起動すると、テーブルの元の状態が復元され、中間ファイル（テーブルスペースファイル）は削除されます。

よくある疑問点とその回答

^[4]

1. ALTERにかなりの時間がかかり、その間に並行DMLがテーブルを大幅に変更する場合について、これらの変更は適用フェーズのためにどこでどのようにキャッシュされるのでしょうか？

DMLフローは、当該テーブル上でアクティブなオンラインDDLが実行されているかどうかを検出します。もしオンラインDDLが実行中であれば、DMLのアクションは一時バッファに記録されます。このバッファはinnodb_sort_buffer_size（default = 1M）で制限されます。バッファが一杯になると、次は一時ファイルに書き込まれます。このファイルの最大サイズはinnodb_online_alter_log_max_size（default = 128M)で制限されます。

ファイルはMySQLの一時ディレクトリ（環境変数TMPDIRまたは-tmpdirで定義される。デフォルトは/tmp）に存在します。これは一時ファイルであり、作成時にすぐにリンクが解除され、alterセッションの参照のみが維持されるため、表示されないことがあります。

2. なぜGLOBALオブジェクトにINTENTION_EXCLUSIVEロックをかけるのですか？

これは、ALTERの実行中にFLUSH TABLES WITH READ LOCKのような一般的なアクションから保護するのに役立ちます。

3. 並行DDLが当該テーブル上でアクティブでないことを保証するものは何ですか？

すべてのDDLコマンドは、必要なロックのセットを所定の順序で取得する必要があります。メタデータロックの昇格可能な共有ロックは、別のセッションからの同じタイプのロックとは互換性がなく、既存のセッションが完了するまで待たされることとなります。一方、並行DML（UPDATEなど）では、メタデータロックの昇格可能な共有ロックと互換性のあるメタデータロックの共有書き込みロックを要求することがよくあります。

4. 当該DBオブジェクトで取得されたMDLロックを調べる方法はありますか？

performance_schema.metadata_locksに詳細があります。なお、metadata_locksテーブルを利用するにはperformance-schema-instrument='wait/lock/metadata/sql/mdl=ON'の設定が必要です。

以下はmetadata_locksを表示させた例です。DMLとDDLを同時に実行しています。

# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
-- 先行するDML
TABLE, database, sample_locks, , 281473151502592, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 75, 219
TABLE, database, chapters, , 281473147654240, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 75, 219

-- あとから流したDDL
GLOBAL, , , , 281472362471408, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_base.cc:5476, 78, 15
BACKUP LOCK, , , , 281472359621936, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5483, 78, 15
SCHEMA, database, , , 281472359617088, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5463, 78, 15
TABLE, database, sample_locks, , 281472359637200, SHARED_UPGRADABLE, TRANSACTION, GRANTED, sql_parse.cc:6142, 78, 15
TABLESPACE, , database/sample_locks, , 281472362409152, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, lock.cc:807, 78, 15
SCHEMA, database, , , 281472362367824, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:1312, 78, 15
TABLE, database, chapters, , 281472359665536, SHARED_UPGRADABLE, STATEMENT, GRANTED, sql_table.cc:1304, 78, 15
TABLE, database, #sql-1_29, , 281472362442256, EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:17126, 78, 15
TABLE, database, sample_locks, , 281472360716240, EXCLUSIVE, TRANSACTION, PENDING, mdl.cc:3762, 78, 16

引用元

LOCK_STATUS列でロックの付与状態を確認できます。
DDLはほとんどのロックを取得することができていますが、先行するDMLとテーブルレベルで競合するため、最後のsample_locksテーブルに対する占有ロックが取得できていません。^[5]

5. ALTERの実行中にDBが再起動した場合はどうなりますか？

DBが再起動する間、ALTERがどの段階にあるかによって大きく異なりますが、ほとんどの場合、簡単にテーブルが一貫性のない状態になってしまいます。以下の例では、InnoDBでテーブルがリネームされた後、MySQLサブシステムでリネームされる前に再起動しています。再起動後のテーブルにはアクセスできません。

mysql> select * from sbtest1 limit 10;
ERROR 1146（42S02): Table 'test.sbtest1' doesn't exist

2019-11-27T12:58:09.327136Z 2 [Warning] InnoDB: Table test/sbtest1 contains 5 user defined columns in InnoDB, but 4 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

バックアップからのリストアは最も安全な方法ですが、ALTERクラッシュの段階によっては、いくつかのショートカットが有効です。上記のケースのように、古いテーブルの.frmファイルの代わりに新しい変更された.frmファイルを手動でコピーし、テーブルをリストアしました。もちろん、このようなオプションはリスクが高く、うまくいくかもしれないし、いかないかもしれないので、専門家に相談することをお勧めします。

6. ファイルシステム上のdataディレクトリに一時ファイルが2つあります。どうしてそんなことが可能なのでしょうか？

答えはMySQLのリネーム操作にあります。ステップ１１を再確認してみましょう。

以下にふたつのファイルがあります。

sbtest1.ibd（alterをかけたいibdファイル)
#sql-ib43-2591602943.ibd（新しいテーブル定義で再構築された一時的な中間ibdファイル)

コミット中、フローはsbtest1.ibdをX.ibd（別の一時ファイル名）にリネームし、#sql-ib44-2591602944.ibdをsbtest1.ibdにリネームする。そのため、２番目のステップがまだ実行されておらず、１番目のステップが実行された時点では、ユーザーは２つの一時ファイル名を目にすることがあります。その際元のtable.ibdファイルは表示されません。

ls -l *.ibd
#sql-ib43-2591602943.ibd
#sql-ib44-2591602944.ibd（renamed version of sbtest1.ibd).

7. 中間DMLアクションのキャッシュ一時ファイルが最大サイズ(innodb_online_alter_log_max_size)に達するとどうなりますか？

DMLはこの状態を検出し、それ以上データを追加しないようにします。ALTERはこの状態を検出し、DDLの失敗をエンドユーザに失敗を報告します。

なお、DMLアクションがこの理由で失敗することはありません。

8. ALTERセッションとともに2つのアクティブなDMLセッションがある場合、それぞれのDMLセッションはデータをキャッシュするために別の中間ファイルを取得するのですか？

すべてのDMLセッションが同じファイルに追加されます。

9. ALTERの進行中に並行DMLが新しいエントリを追加し続けた場合、ALTERの適用フェーズが追いつくことはあるのだろうか。

ALTER処理のおけるDMLの適用フェーズはふたつあります。

最初のフェーズはコピーフェーズの一部で、並列してキャッシュされたDML操作のほとんどがまとめて実行されます。この時点で後続/保留中のDML操作はふたつ目のフェーズであるCOMMITフェーズでまとめて適用するために残されます。

COMMITはEXCLUSIVEロックを確立するので、コミットがテーブルに対して見るビューは最終的なものであり、並行DMLはALTERが終了するまで、キャッシュにDML操作を追加し続けることはできません。

10. これはすべて良いことですが、キャッシュされたDMLエントリを適用するALTERの最初のフェーズでは、並行DMLからのエントリが増え続ける可能性があります。ALTERはこの増え続ける並列エントリをどのように扱うのでしょうか？

ALTERはこれらのDMLエントリをバッチで適用します。

ブロック(innodb_sort_buffer_sizeで制限される)に収まるN個のエントリを読み込み、次のエントリセットを読み込む前に適用します。次のブロックに移動する前に、ALTERは一瞬EXCLUSIVE内部クラスタ化インデックスレベルのロックを取ります。これはALTERがDML操作によって並列に変更されているリストの一貫したビューを取得するのに役立ちます。

このバッチによる一時停止アプローチにより、ALTERは最終的には確実にDMLに追いつくことができます。

おわりに

ALTER TABLE操作は非常に複雑な操作であり、ALTERにかかる時間、並行DMLの影響、並行DMLへの影響、競合する操作のブロックなど、複数の要因を考慮して実行計画を慎重に立てる必要があります。

良いALTER生活を！

参考文献

翻訳元

参考にした書籍

『詳解MySQL5.7』
- https://www.shoeisha.co.jp/book/detail/9784798147406
『MySQL徹底入門第4版 MySQL8.0対応』
- https://www.shoeisha.co.jp/book/detail/9784798161488
『MySQL運用・管理[実践]入門』
- https://gihyo.jp/book/2024/978-4-297-14184-4

参考にしたWeb上の情報

15.12 InnoDB とオンライン DDL - MySQL公式
- https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl.html
ALTER TABLEを上手に使いこなそう。 - 漢のコンピュータ道
- https://nippondanji.blogspot.com/2009/05/alter-table.html
叩いて学ぶ！　そのSQLどんなロックを取ってんの！？
- https://zenn.dev/mconfjp/scraps/dc217e1c1c568b
- 参考というか今回の調査ログのひとつ
FLUSH構文について - MySQL道普請便り
- https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0080
Information Schema METADATA_LOCK_INFO Table - MariaDB
- https://mariadb.com/kb/en/information-schema-metadata_lock_info-table/

その他の面白そうな記事

An Introduction to MySQL Parallel Query and DDL - Aribaba Cloud
- https://www.alibabacloud.com/blog/an-introduction-to-mysql-parallel-query-and-ddl_601920
MySQL Deep Dive - Implementation and Acquisition Mechanism of Metadata Locking - Aribaba Cloud
- https://www.alibabacloud.com/blog/mysql-deep-dive---implementation-and-acquisition-mechanism-of-metadata-locking_599191

脚注

カラム追加も既存のオブジェクトに触れることなく追加しているように思えるが、対象外。理由は後述 ↩︎
おそらく削除や順序の入れ替えも？　削除や入れ替えは並行DMLが許可される ↩︎
原文ではTABLESPACEレベルのロックとなっているが、文脈的にテーブルのロックと推測できる。また実際に確認したところメタデータロックでTABLESPACEというオブジェクト名でのロックは取得されていなかったため、TABLEレベルと記載した。 ↩︎
元記事では「INPLACEを理解する」の章の上にFAQがあったんですが、そこじゃないだろと思って場所を移しました ↩︎
翻訳元の画像を使えず、例に出したクエリも変更している。同時に解説の文章も全て書き直してあるため、元の文章が知りたい人は元記事を読んでみてほしい ↩︎

Discussion