セカンダリインデックスの作成または追加

CREATE INDEX name ON table (col_list);

ALTER TABLE tbl_name ADD INDEX name (col_list);

このテーブルは、インデックスの作成中も読取りおよび書込み操作に使用できます。 CREATE INDEX ステートメントは、テーブルにアクセスしているすべてのトランザクションが完了した後にのみ終了するため、インデックスの初期状態にはテーブルの最新の内容が反映されます。

セカンダリインデックスを追加するためのオンライン DDL サポートとは、通常、セカンダリインデックスのないテーブルを作成してからデータのロード後にセカンダリインデックスを追加することで、テーブルおよび関連するインデックスの作成およびロードのプロセス全体を高速化できることを意味します。

新しく作成されたセカンダリインデックスには、CREATE INDEX または ALTER TABLE ステートメントの実行が終了した時点でテーブルにコミットされたデータのみが含まれます。 コミットされていない値や古いバージョンの値、または削除対象としてマークされているが、まだ古いインデックスから削除されていない値は含まれていません。

この操作のパフォーマンス、領域使用量およびセマンティクスに影響する要因もあります。 詳細は、セクション15.12.6「オンライン DDL の制限事項」を参照してください。

インデックスの削除

DROP INDEX name ON table;

ALTER TABLE tbl_name DROP INDEX name;

このテーブルは、インデックスの削除中も読取りおよび書込み操作に使用できます。 DROP INDEX ステートメントは、テーブルにアクセスしているすべてのトランザクションが完了した後にのみ終了するため、インデックスの初期状態にはテーブルの最新の内容が反映されます。

インデックスの名前変更

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;

FULLTEXT インデックスの追加

CREATE FULLTEXT INDEX name ON table(column);

ユーザー定義の FTS_DOC_ID カラムがない場合は、最初の FULLTEXT インデックスを追加するとテーブルが再構築されます。 テーブルを再構築せずに、FULLTEXT インデックスを追加できます。

SPATIAL インデックスの追加

CREATE TABLE geom (g GEOMETRY NOT NULL);
ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;

インデックスタイプの変更 (USING {BTREE | HASH})

ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INSTANT;

主キーの追加

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

テーブルを適切に再構築します。 データは大幅に再編成され、コストのかかる操作になります。 カラムを NOT NULL に変換する必要がある場合、特定の条件下で ALGORITHM=INPLACE は許可されません。

clustered index を再構築するには、常にテーブルデータのコピーが必要です。 したがって、後で ALTER TABLE ... ADD PRIMARY KEY を発行するのではなく、テーブルの作成時に primary key を定義することをお薦めします。

UNIQUE または PRIMARY KEY インデックスを作成したとき、MySQL は、いくつかの追加の作業を行う必要があります。 UNIQUE インデックスの場合、MySQL は、テーブルに重複したキーの値が含まれていないことをチェックします。 PRIMARY KEY インデックスの場合も、MySQL は、どの PRIMARY KEY カラムにも NULL が含まれていないことをチェックします。

ALGORITHM=COPY 句を使用して主キーを追加すると、MySQL は関連付けられたカラムの NULL 値をデフォルト値に変換: 数値の場合は 0、文字ベースのカラムおよび BLOB の場合は空の文字列、DATETIME の場合は 0000-00-00 00:00:00。 これは非標準の動作であるため、これに依存しないようにすることをお勧めします。 ALGORITHM=INPLACE を使用した主キーの追加は、SQL_MODE 設定に strict_trans_tables または strict_all_tables フラグが含まれている場合にのみ許可されます。SQL_MODE 設定が厳密な場合、ALGORITHM=INPLACE は許可されますが、リクエストされた主キーカラムに NULL 値が含まれている場合、ステートメントは失敗する可能性があります。 ALGORITHM=INPLACE の動作は、より標準に準拠しています。

主キーなしでテーブルを作成すると、InnoDB によってテーブルが選択されます。これは、NOT NULL カラムに定義されている最初の UNIQUE キーまたはシステム生成キーです。 余分な非表示カラムの不確実性および潜在的な領域要件を回避するには、CREATE TABLE ステートメントの一部として PRIMARY KEY 句を指定します。

MySQL では、既存のデータを元のテーブルから目的のインデックス構造を持つ一時テーブルにコピーすることで、新しいクラスタインデックスが作成されます。 データが一時テーブルに完全にコピーされると、元のテーブルの名前は別の一時テーブル名に変更されます。 新しいクラスタ化されたインデックスで構成される一時テーブルの名前が元のテーブルの名前に変更され、元のテーブルはデータベースから削除されます。

セカンダリインデックスでの操作に適用されるオンラインパフォーマンスの拡張は、主キーインデックスには適用されません。 InnoDB テーブルの行は、主キーに基づいて編成されたクラスタ化されたインデックスに格納されます。これにより、一部のデータベースシステムで「インデックス編成テーブル」と呼ばれるものが形成されます。 テーブル構造は主キーに密接に関連付けられているため、主キーを再定義するには引き続きデータをコピーする必要があります。

主キーに対する操作で ALGORITHM=INPLACE が使用される場合は、データが引き続きコピーされるにもかかわらず、次の理由で ALGORITHM=COPY を使用するより効率的です。

主キーの削除

ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;

同じ ALTER TABLE ステートメントに新しい主キーを追加せずに主キーを削除できるのは、ALGORITHM=COPY のみです。

主キーの削除および別の主キーの追加

ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

データは大幅に再編成され、コストのかかる操作になります。

カラムの追加

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INSTANT;

INSTANT アルゴリズムを使用してカラムを追加する場合は、次の制限が適用されます:

同じ ALTER TABLE ステートメントに複数のカラムを追加できます。 例:

ALTER TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT, ALGORITHM=INSTANT;

INFORMATION_SCHEMA.INNODB_TABLES および INFORMATION_SCHEMA.INNODB_COLUMNS は、即時に追加されたカラムのメタデータを提供します。 INFORMATION_SCHEMA.INNODB_TABLES.INSTANT_COLS では、最初のインスタントカラムを追加する前に、テーブルのカラム数が表示されます。 INFORMATION_SCHEMA.INNODB_COLUMNS.HAS_DEFAULT および DEFAULT_VALUE は、即時に追加されたカラムのデフォルト値に関するメタデータを提供します。

auto-increment カラムを追加する場合、同時 DML は許可されません。 データは大幅に再編成され、コストのかかる操作になります。 少なくとも、ALGORITHM=INPLACE, LOCK=SHARED が必要です。

ALGORITHM=INPLACE を使用してカラムを追加すると、テーブルが再構築されます。

カラムの削除

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;

データは大幅に再編成され、コストのかかる操作になります。

カラム名の変更

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;

同時 DML を許可するには、同じデータ型を保持し、カラム名のみを変更します。

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

外部キー制約の一部であるカラムの名前を変更することもできます。 外部キー定義は、新しいカラム名を使用するように自動的に更新されます。 外部キーに参加するカラムの名前の変更は、ALGORITHM=INPLACE でのみ機能します。 ALGORITHM=COPY 句を使用した場合、または他のなんらかの状況でコマンドがバックグラウンドで ALGORITHM=COPY を使用する場合、ALTER TABLE ステートメントは失敗します。

ALGORITHM=INPLACE では、generated column の名前の変更はサポートされていません。

カラムの並替え

カラムの順序を変更するには、CHANGE または MODIFY 操作で FIRST または AFTER を使用します。

ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

データは大幅に再編成され、コストのかかる操作になります。

カラムのデータ型の変更

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

カラムのデータ型の変更は、ALGORITHM=COPY でのみサポートされます。

VARCHAR カラムサイズの拡張

ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

VARCHAR カラムに必要な長さバイト数は、同じままにする必要があります。 サイズが 0 から 255 バイトの VARCHAR カラムの場合、値のエンコードには長さバイトが必要です。 256 バイト以上の VARCHAR カラムの場合は、長さが 2 バイト必要です。 その結果、インプレース ALTER TABLE では、VARCHAR カラムサイズの 0 から 255 バイト、または 256 バイトからそれより大きいサイズへの増加のみがサポートされます。 インプレース ALTER TABLE では、VARCHAR カラムのサイズを 256 バイト未満から 256 バイト以上に増やすことはサポートされていません。 この場合、必要な長さバイト数は 1 から 2 に変更され、テーブルコピー (ALGORITHM=COPY) でのみサポートされます。 たとえば、シングルバイト文字セットの VARCHAR カラムサイズを、インプレース ALTER TABLE を使用して VARCHAR(255) から VARCHAR(256) に変更しようとすると、次のエラーが返されます:

ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
column type INPLACE. Try ALGORITHM=COPY.

インプレース ALTER TABLE を使用した VARCHAR サイズの縮小はサポートされていません。 VARCHAR サイズを小さくするには、テーブルコピー (ALGORITHM=COPY) が必要です。

カラムのデフォルト値の設定

ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INSTANT;

テーブルメタデータのみを変更します。 デフォルトのカラム値は data dictionary に格納されます。

カラムのデフォルト値の削除

ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INSTANT;

自動インクリメント値の変更

ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

データファイルではなく、メモリーに格納された値を変更します。

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

カラムの NULL 化

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

テーブルを適切に再構築します。 データは大幅に再編成され、コストのかかる操作になります。

カラムの NOT NULL 化

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

テーブルを適切に再構築します。 操作を成功させるには、STRICT_ALL_TABLES または STRICT_TRANS_TABLES SQL_MODE が必要です。 カラムに NULL 値が含まれている場合、操作は失敗します。 サーバーは、参照整合性が失われる可能性がある外部キーカラムの変更を禁止します。 セクション13.1.9「ALTER TABLE ステートメント」を参照してください。 データは大幅に再編成され、コストのかかる操作になります。

ENUM または SET カラムの定義の変更

CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INSTANT;

新しい列挙を追加するか、有効なメンバー値のリストの end にメンバーを設定して、ENUM または SET カラムの定義を変更すると、データ型の記憶域サイズが変更されないかぎり、すぐに実行することも、その場で実行することもできます。 たとえば、8 つのメンバーを持つ SET カラムにメンバーを追加すると、値ごとに必要な記憶域が 1 バイトから 2 バイトに変更されます。これにはテーブルのコピーが必要です。 リストの途中にメンバーを追加すると、既存のメンバーの番号が変更されます。これには、テーブルコピーが必要になります。

STORED カラムの追加

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED), ALGORITHM=COPY;

式はサーバーによって評価される必要があるため、ADD COLUMN はストアドカラムのインプレース操作ではありません (一時テーブルを使用せずに実行されます)。

STORED カラムの順序の変更

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED FIRST, ALGORITHM=COPY;

テーブルを適切に再構築します。

STORED カラムの削除

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

テーブルを適切に再構築します。

VIRTUAL カラムの追加

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL), ALGORITHM=INSTANT;

仮想カラムの追加は、即時に実行することも、パーティション化されていないテーブルに対して適切に実行することもできます。

VIRTUAL の追加は、パーティションテーブルのインプレース操作ではありません。

VIRTUAL カラムの順序の変更

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL FIRST, ALGORITHM=COPY;

VIRTUAL カラムの削除

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;

VIRTUAL カラムの削除は、即時に実行することも、パーティション化されていないテーブルに対して適切に実行することもできます。

外部キー制約の追加

INPLACE アルゴリズムは、foreign_key_checks が無効な場合にサポートされます。 それ以外の場合は、COPY アルゴリズムのみがサポートされます。

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 オプションが有効または無効になった状態でオンラインで実行できます。

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

SHOW CREATE TABLE table\G

または、INFORMATION_SCHEMA.TABLE_CONSTRAINTS テーブルをクエリーして、CONSTRAINT_NAME カラムおよび CONSTRAINT_TYPE カラムを使用して外部キー名を識別します。

単一のステートメントで外部キーとそれに関連付けられたインデックスを削除することもできます:

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

ROW_FORMAT の変更

ALTER TABLE tbl_name ROW_FORMAT = row_format, ALGORITHM=INPLACE, LOCK=NONE;

データは大幅に再編成され、コストのかかる操作になります。

ROW_FORMAT オプションの詳細は、テーブルオプション を参照してください。

KEY_BLOCK_SIZE の変更

ALTER TABLE tbl_name KEY_BLOCK_SIZE = value, ALGORITHM=INPLACE, LOCK=NONE;

データは大幅に再編成され、コストのかかる操作になります。

KEY_BLOCK_SIZE オプションの詳細は、テーブルオプション を参照してください。

永続テーブル統計オプションの設定

ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;

テーブルメタデータのみを変更します。

永続統計には、STATS_PERSISTENT、STATS_AUTO_RECALC および STATS_SAMPLE_PAGES が含まれます。 詳細は、セクション15.8.10.1「永続的オプティマイザ統計のパラメータの構成」を参照してください。

文字セットの指定

ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;

新しい文字エンコーディングが別のものである場合は、テーブルを再構築します。

文字セットの変換

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;

新しい文字エンコーディングが別のものである場合は、テーブルを再構築します。

テーブルの最適化

OPTIMIZE TABLE tbl_name;

インプレース操作は、FULLTEXT インデックスのあるテーブルではサポートされていません。 この操作では INPLACE アルゴリズムを使用しますが、ALGORITHM および LOCK 構文は許可されていません。

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

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

MySQL 5.6.17 . ALGORITHM=INPLACE is not supported for tables with FULLTEXT インデックスの時点で ALGORITHM=INPLACE を使用します。

「null」再構築の実行

ALTER TABLE tbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

MySQL 5.6.17 の時点では、ALGORITHM=INPLACE を使用します。 ALGORITHM=INPLACE は、FULLTEXT インデックスのあるテーブルではサポートされていません。

テーブル名の変更

ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INSTANT;

テーブルの名前変更は、即時に実行することも、インプレースで実行することもできます。 MySQL は、コピーを作成せずに、テーブル tbl_name に対応するファイルの名前を変更します。 (RENAME TABLE ステートメントを使用してテーブルの名前を変更することもできます。 セクション13.1.36「RENAME TABLE ステートメント」を参照してください。) 名前を変更したテーブル専用に付与された権限は、新しい名前に移行されません。 それらは、手動で変更する必要があります。

一般テーブルスペースの名前の変更

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;

ALTER TABLESPACE ... RENAME TO は INPLACE アルゴリズムを使用しますが、ALGORITHM 句はサポートしていません。

一般的なテーブルスペース暗号化の有効化または無効化

ALTER TABLESPACE tablespace_name ENCRYPTION='Y';

ALTER TABLESPACE ... ENCRYPTION は INPLACE アルゴリズムを使用しますが、ALGORITHM 句はサポートしていません。

関連情報については、セクション15.13「InnoDB 保存データ暗号化」を参照してください。

file-per-table テーブルスペース暗号化の有効化または無効化

ALTER TABLE tbl_name ENCRYPTION='Y', ALGORITHM=COPY;

関連情報については、セクション15.13「InnoDB 保存データ暗号化」を参照してください。