14.11.1 オンライン DDL の概要

従来より、InnoDB テーブルでの多くの DDL 操作は高いコストを必要としました。多くの ALTER TABLE 操作は、要求されたテーブルオプションとインデックスを使用して定義された新しい空のテーブルを作成してから、既存の行を新しいテーブルに 1 つずつコピーし、行が挿入されるたびにインデックスを更新することによって機能しました。元のテーブルのすべての行がコピーされたあと、古いテーブルが削除され、そのコピーの名前が元のテーブルの名前に変更されました。

MySQL 5.5、および InnoDB Plugin を含む MySQL 5.1 は、テーブルコピー動作を行わないように CREATE INDEX と DROP INDEX を最適化しました。その機能は、高速インデックス作成と呼ばれました。MySQL 5.6 は、ほかのタイプの多くの ALTER TABLE 操作を、テーブルのコピーを行わないように拡張しています。別の拡張では、テーブルが変更されている最中に SELECT クエリーや INSERT、UPDATE、および DELETE (DML) ステートメントの処理を続行できるようになります。この機能の組み合わせは現在、オンライン DDL と呼ばれます。

この新しいメカニズムはまた、一般に、セカンダリインデックスなしでテーブルを作成し、データがロードされたあとにセカンダリインデックスを追加することによって、テーブルとそれに関連付けられたインデックスを作成およびロードするプロセス全体を高速化できることも示しています。

CREATE INDEX または DROP INDEX コマンドで構文の変更は必要ありませんが、この操作のパフォーマンス、領域使用量、およびセマンティクスに影響を与える要因がいくつかあります (セクション14.11.9「オンライン DDL の制限」を参照してください)。

MySQL 5.6 のオンライン DDL 拡張によって、以前はテーブルコピー、テーブルでの DML 操作のブロック、またはその両方を必要とした多くの DDL 操作が改善されます。表14.5「DDL 操作のオンラインステータスのサマリー」は、ALTER TABLE ステートメントの各種類と、オンライン DDL 機能がそれぞれにどのように適用されるかを示しています。

ALTER TABLE のパーティション化句を除き、パーティション化された InnoDB テーブルに対するオンライン DDL 操作は、通常の InnoDB テーブルに適用されるのと同じルールに従います。詳細は、セクション14.11.8「パーティション化された InnoDB テーブルに対するオンライン DDL」を参照してください。

• 「インプレース?」カラムは、どの操作の場合に ALGORITHM=INPLACE 句が許可されるかを示しています。推奨される値は「はい」です。

• 「テーブルをコピー?」カラムは、どの操作の場合にコストの高いテーブルコピー操作を回避できるかを示しています。推奨される値は「いいえ」です。ALGORITHM=INPLACE は許可されるが、ある程度の量のテーブルコピーが引き続き必要な操作がいくつかある点を除き、このカラムはほぼ、「インプレース?」カラムの反対です。

• 「並列 DML を許可?」カラムは、どの操作を完全にオンラインで実行できるかを示しています。推奨される値は「はい」です。DDL 中に完全な並列性が許可されることを表明するために LOCK=NONE を指定できますが、MySQL は、可能な場合は自動的にこのレベルの並列性を許可します。並列 DML が許可されている場合は、並列クエリーも常に許可されます。

• 「並列クエリーを許可?」カラムは、どの DDL 操作の場合に、その操作の進行中にテーブルに対するクエリーが許可されるかを示しています。推奨される値は「はい」です。並列クエリーは、すべてのオンライン DDL 操作中に許可されます。これは、参考のために、すべてのセルに表示されている「はい」で示されています。DDL 中に並列クエリーが許可されることを表明するために LOCK=SHARED を指定できますが、MySQL は、可能な場合は自動的にこのレベルの並列性を許可します。

• 「注」カラムは、構成オプションの設定または DDL ステートメント内のほかの句によって答えが異なる場合など、ほかのカラムの「はい/いいえ」の値に例外がある場合はそれについて説明します。「はい*」と「いいえ*」の値は、これらの追加の注によって答えが異なることを示します。

表 14.5 DDL 操作のオンラインステータスのサマリー

操作	インプレース?	テーブルをコピー?	並列 DML を許可?	並列クエリーを許可?	メモ
CREATE INDEX、ADD INDEX	はい*	いいえ*	はい	はい	FULLTEXT インデックスにはいくつかの制限があります。次の行を参照してください。現在は、作成対象の同じインデックスも同じ ALTER TABLE ステートメント内の前の句によって削除された場合、この操作はインプレースではありません (つまり、テーブルをコピーします)。
ADD FULLTEXT INDEX	はい	いいえ*	いいえ	はい	ユーザーが指定した FTS_DOC_ID カラムがないかぎり、テーブルの最初の FULLTEXT インデックスの作成にはテーブルコピーが必要です。同じテーブル上の以降の FULLTEXT インデックスは、インプレースで作成できます。
DROP INDEX	はい	いいえ	はい	はい	データファイルではなく、.frm ファイルのみを変更します。
OPTIMIZE TABLE	はい	はい	はい	はい	MySQL 5.6.17 の時点では、ALGORITHM=INPLACE を使用します。old_alter_table=1 または mysqld --skip-new オプションが有効になっている場合は、ALGORITHM=COPY が使用されます。FULLTEXT インデックスを含むテーブルでは、オンライン DDL (ALGORITHM=INPLACE) を使用した OPTIMIZE TABLE はサポートされません。
カラムのデフォルト値を設定する	はい	いいえ	はい	はい	データファイルではなく、.frm ファイルのみを変更します。
カラムの自動インクリメント値を変更する	はい	いいえ	はい	はい	データファイルではなく、メモリーに格納された値を変更します。
外部キー制約を追加する	はい*	いいえ*	はい	はい	テーブルのコピーを行わないようにするには、制約の作成中に foreign_key_checks を無効にします。
外部キー制約を削除する	はい	いいえ	はい	はい	foreign_key_checks オプションを有効または無効にすることができます。
カラムを名前変更する	はい*	いいえ*	はい*	はい	並列 DML を許可するには、同じデータ型を維持し、カラム名のみを変更します。
カラムを追加する	はい	はい	はい*	はい	自動インクリメントカラムを追加する場合は、並列 DML が許可されません。ALGORITHM=INPLACE は許可されますが、データが大幅に再編成されるため、依然としてコストの高い操作です。
カラムを削除する	はい	はい	はい	はい	ALGORITHM=INPLACE は許可されますが、データが大幅に再編成されるため、依然としてコストの高い操作です。
カラムを並べ替える	はい	はい	はい	はい	ALGORITHM=INPLACE は許可されますが、データが大幅に再編成されるため、依然としてコストの高い操作です。
ROW_FORMAT プロパティーを変更する	はい	はい	はい	はい	ALGORITHM=INPLACE は許可されますが、データが大幅に再編成されるため、依然としてコストの高い操作です。
KEY_BLOCK_SIZE プロパティーを変更する	はい	はい	はい	はい	ALGORITHM=INPLACE は許可されますが、データが大幅に再編成されるため、依然としてコストの高い操作です。
カラム NULL を作成する	はい	はい	はい	はい	ALGORITHM=INPLACE は許可されますが、データが大幅に再編成されるため、依然としてコストの高い操作です。
カラム NOT NULL を作成する	はい*	はい	はい	はい	SQL_MODE に strict_all_tables または strict_all_tables が含まれている場合は、カラムに Null が含まれていると操作は失敗します。ALGORITHM=INPLACE は許可されますが、データが大幅に再編成されるため、依然としてコストの高い操作です。
カラムのデータ型を変更する	いいえ	はい	いいえ	はい
主キーを追加する	はい*	はい	はい	はい	ALGORITHM=INPLACE は許可されますが、データが大幅に再編成されるため、依然としてコストの高い操作です。カラムを NOT NULL に変換する必要がある場合は、特定の状況では ALGORITHM=INPLACE が許可されません。例14.9「主キーの作成および削除」を参照してください。
主キーを削除して別の主キーを追加する	はい	はい	はい	はい	ALGORITHM=INPLACE は、同じ ALTER TABLE で新しい主キーを追加する場合にのみ許可されます。データが大幅に再編成されるため、これは依然としてコストの高い操作です。
主キーを削除する	いいえ	はい	いいえ	はい	同じ ALTER TABLE ステートメントで新しい主キーを追加することなく主キーを削除する場合は、制限が適用されます。
文字セットを変換する	いいえ	はい	いいえ	はい	新しい文字エンコーディングが別のものである場合は、テーブルを再構築します。
文字セットを指定する	いいえ	はい	いいえ	はい	新しい文字エンコーディングが別のものである場合は、テーブルを再構築します。
FORCE オプションを使用して再構築する	はい	はい	はい	はい	MySQL 5.6.17 の時点では、ALGORITHM=INPLACE を使用します。old_alter_table=1 または mysqld --skip-new オプションが有効になっている場合は、ALGORITHM=COPY が使用されます。FULLTEXT インデックスを含むテーブルでは、オンライン DDL (ALGORITHM=INPLACE) を使用したテーブル再構築はサポートされません。
「null」 ALTER TABLE ... ENGINE=INNODB を使用して再構築する	はい	はい	はい	はい	MySQL 5.6.17 の時点では、ALGORITHM=INPLACE を使用します。old_alter_table=1 または mysqld --skip-new オプションが有効になっている場合は、ALGORITHM=COPY が使用されます。FULLTEXT インデックスを含むテーブルでは、オンライン DDL (ALGORITHM=INPLACE) を使用したテーブル再構築はサポートされません。
テーブルレベルの永続的統計オプション (STATS_PERSISTENT、STATS_AUTO_RECALC STATS_SAMPLE_PAGES) を設定する	はい	いいえ	はい	はい	データファイルではなく、.frm ファイルのみを変更します。

次の各セクションでは、並列 DML、インプレース、またはその両方で実行できる主な操作のそれぞれについて、オンライン DDL に関連した基本的な構文と使用上の注意を示します。

セカンダリインデックス

• セカンダリインデックスを作成する: CREATE INDEX name ON table (col_list) または ALTER TABLE table ADD INDEX name (col_list)。(FULLTEXT インデックスの作成にはテーブルのロックが引き続き必要です。)

• セカンダリインデックスを削除する: DROP INDEX name ON table; または ALTER TABLE table DROP INDEX name

InnoDB テーブルでのセカンダリインデックスの作成および削除では、MySQL 5.5 や InnoDB Plugin を含む MySQL 5.1 と同様に、テーブルコピー動作がスキップされます。

MySQL 5.6 以降では、インデックスが作成または削除されている間も、そのテーブルの読み取りおよび書き込み操作は可能なままです。CREATE INDEX または DROP INDEX ステートメントは、インデックスの初期状態にテーブルの最新の内容が反映されるように、そのテーブルにアクセスしているすべてのトランザクションが完了したあとでのみ完了します。以前は、インデックスが作成または削除されている間にテーブルを変更すると、通常はデッドロックが発生し、それによりテーブルでの INSERT、UPDATE、または DELETE ステートメントが取り消されました。

カラムのプロパティー

• カラムのデフォルト値を設定する: ALTER TABLE tbl ALTER COLUMN col SET DEFAULT literal または ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT

  カラムのデフォルト値は、InnoDB データディクショナリではなく、そのテーブルの .frm ファイルに格納されます。

• カラムの自動インクリメント値の変更: ALTER TABLE table AUTO_INCREMENT=next_value;

  特に、レプリケーションまたはシャーディングを使用した分散システムでは、テーブルの自動インクリメントカウンタを特定の値にリセットする場合があります。テーブルに挿入された次の行は、その自動インクリメントカラムの指定された値を使用します。この手法はまた、すべてのテーブルを定期的に空にしてリロードするデータウェアハウス環境でも使用できます。それにより、自動インクリメントのシーケンスを 1 から再開できます。

• カラムの名前変更: ALTER TABLE tbl CHANGE old_col_name new_col_name datatype

  同じデータ型と [NOT] NULL 属性を維持して、カラム名のみを変更する場合、この操作は常にオンラインで実行できます。

  この拡張の一部として、外部キー制約の一部であるカラムを名前変更できるようになりました。これは、以前は許可されていませんでした。外部キー定義は、新しいカラム名を使用するように自動的に更新されます。外部キーに参加しているカラムの名前変更は、ALTER TABLE のインプレースモードでのみ機能します。ALGORITHM=COPY 句を使用するか、またはその他の何らかの条件によってコマンドが内部的に ALGORITHM=COPY を使用した場合、ALTER TABLE ステートメントは失敗します。

外部キー

• 外部キー制約の追加または削除:

  ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1) REFERENCES tbl2(col2) referential_actions;
  ALTER TABLE tbl DROP FOREIGN KEY fk_name;

  外部キーの削除は、foreign_key_checks オプションが有効または無効になった状態でオンラインで実行できます。外部キーをオンラインで作成するには、foreign_key_checks が無効になっている必要があります。

  特定のテーブル上の外部キー制約の名前がわからない場合は、次のステートメントを発行し、各外部キーに対する CONSTRAINT 句で制約名を見つけます。

  show create table table\G

  または、information_schema.table_constraints テーブルをクエリーし、constraint_name および constraint_type カラムを使用して外部キー名を識別します。

  この拡張の結果として、外部キーとそれに関連付けられたインデックスを 1 つのステートメントで削除することも可能になりました。これは以前、厳密な順序で並べられた個別のステートメントを必要としました。

  ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;

変更されるテーブル内に外部キーがすでに存在する (つまり、そのテーブルがいずれかの FOREIGN KEY ... REFERENCE 句を含む子テーブルである) 場合は、外部キーカラムに直接関連しない操作であっても、オンライン DDL 操作には次の追加の制限が適用されます。

• このような子テーブルでのオンライン DDL 操作中は、並列 DML が許可されません。(この制限はバグとして評価中であり、解除される可能性があります。)

• 親テーブルが変更されたために、CASCADE または SET NULL パラメータを使用した ON UPDATE または ON DELETE 句によって子テーブル内で関連する変更が発生した場合は、子テーブルに対する ALTER TABLE が別のトランザクションのコミットも待機する可能性があります。

同様に、あるテーブルが外部キー関係にある親テーブルである場合、そこには FOREIGN KEY 句が含まれていなくても、INSERT、UPDATE、または DELETE ステートメントによって子テーブル内で ON UPDATE または ON DELETE アクションが発生した場合は、そのテーブルが ALTER TABLE の完了を待機する可能性があります。

ALGORITHM=COPY に関する注意

ALGORITHM=COPY 句で実行される ALTER TABLE 操作はすべて、並列 DML 操作を妨げます。並列クエリーは、引き続き許可されます。つまり、テーブルコピー操作には常に、少なくとも LOCK=SHARED (クエリーを許可するが、DML は許可しない) の並列性の制限が含まれます。LOCK=EXCLUSIVE (DML とクエリーを妨げる) を指定することによって、このような操作の並列性をさらに制限できます。

並列 DML ではあるが、テーブルコピーが引き続き必要

その他の一部の ALTER TABLE 操作では、並列 DML は許可されますが、テーブルコピーが引き続き必要です。ただし、これらの操作のテーブルコピーは MySQL 5.5 以前の処理と比べて高速です。

• カラムの追加、削除、または並べ替え。

• 主キーの追加または削除。

• テーブルの ROW_FORMAT または KEY_BLOCK_SIZE プロパティーの変更。

• カラムの Null にできるステータスの変更。

• OPTIMIZE TABLE

• FORCE オプションを使用したテーブルの再構築

• 「null」 ALTER TABLE ... ENGINE=INNODB ステートメントを使用したテーブルの再構築

注記

新しいカラム、データ型、制約、インデックスなどによってデータベーススキーマが進化するにつれ、CREATE TABLE ステートメントを最新のテーブル定義が適用されるように維持してください。オンライン DDL のパフォーマンス向上があったとしても、スキーマの一部を作成し、そのあとに ALTER TABLE ステートメントを発行するより、最初から安定したデータベース構造を作成する方が効率的です。

このガイドラインの主な例外は、多数の行を含むテーブル上のセカンダリインデックスに関するものです。通常は、セカンダリインデックスを除き、すべての詳細が指定された状態でテーブルを作成し、データをロードしてから、セカンダリインデックスを作成する方法がもっとも効率的です。初期のデータがクリーンであることがわかっていて、ロードプロセス中に一貫性チェックが必要ない場合は、外部キーでも同じ手法を使用できます (最初にデータをロードしてから、外部キーを設定します)。

CREATE TABLE、CREATE INDEX、ALTER TABLE、および同様のステートメントのどのようなシーケンスでテーブルを作成した場合でも、ステートメント SHOW CREATE TABLE table\G (正式な形式には大文字の \G が必要です) を発行することによって、現在の形式のテーブルを再構築するために必要な SQL を取得できます。この出力には、場合によっては内部的に追加され、また新しいシステム上でのテーブルのクローニングや、同一の型を持つ外部キーカラムの設定を行うときに通常であれば省略する可能性がある数値精度、NOT NULL、CHARACTER SET などの句が示されます。