コメントを追加するALTER TABLEでテーブルコピーは発生しない(InnoDB)

  • 4
    いいね
  • 0
    コメント
この記事は最終更新日から1年以上が経過しています。

TL;DR

コメントを変更するだけのALTER TABLEでは一時テーブルの生成(テーブルコピー)は発生しない。

前提

MySQLでテーブルまたはカラムにコメントをつけたい場合、以下の様なクエリを発行する。

mysql
ALTER TABLE users MODIFY name varchar(200) COMMENT '氏名';

ここで以下の疑問が湧く。
型を変更せずコメントだけを変更した場合、テーブルコピーは発生するか?

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

ほとんどの場合、ALTER TABLE は元のテーブルの一時的なコピーを作成します。MySQL は、そのテーブルを変更しているほかの操作を待ってから、処理を続行します。そのコピーに変更を組み込み、元のテーブルを削除したあと、新しいテーブルの名前を変更します。ALTER TABLE の実行中、ほかのセッションは元のテーブルを読み取ることができます (例外については、すぐあとに説明します)。ALTER TABLE 操作の開始後に開始されたテーブルへの更新や書き込みは、新しいテーブルの準備ができるまで停止されてから、どの更新も失敗することなく、新しいテーブルに自動的にリダイレクトされます。元のテーブルの一時的なコピーは、新しいテーブルのデータベースディレクトリ内に作成されます。これは、テーブルの名前を別のデータベースに変更した ALTER TABLE 操作のための元のテーブルのデータベースディレクトリとは異なる場合があります。

テーブルコピーを伴わないALTER TABLEの条件

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

一部の操作では、一時テーブルを必要としないインプレース ALTER TABLE が可能です。

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

サーバーが変更する必要があるのはテーブルの内容ではなく、テーブルの .frm ファイルだけであるため、テーブルデータを変更せず、テーブルメタデータだけを変更する変更はただちに実行されます。次の変更は、この方法で実行できる迅速な変更です。

カラムの名前変更。ただし、MySQL 5.6.6 より前の InnoDB ストレージエンジンを除きます。

カラムのデフォルト値の変更 (NDB テーブルを除きます。「MySQL Cluster オンライン操作の制限」を参照してください)。

有効なメンバー値のリストの最後に新しい列挙またはセットメンバーを追加することによる、ENUM または SET カラムの定義の変更。ただし、データ型のストレージサイズが変更される場合を除きます。たとえば、メンバー数が 8 の SET カラムにメンバーを追加すると、値ごとに必要なストレージが 1 バイトから 2 バイトに変更されます。これには、テーブルコピーが必要になります。リストの途中にメンバーを追加すると、既存のメンバーの番号が変更されます。これには、テーブルコピーが必要になります。

ADD PARTITION、DROP PARTITION、COALESCE PARTITION、REBUILD PARTITION、または REORGANIZE PARTITION を含む ALTER TABLE は、一時テーブルを作成しません (NDB テーブルで使用される場合を除きます)。ただし、これらの操作では、一時的なパーティションファイルが作成されます。

RANGE または LIST パーティションに対する ADD または DROP 操作は即座の操作か、ほぼ即座の操作です。HASH または KEY パーティションに対する ADD または COALESCE 操作では、LINEAR HASH または LINEAR KEY が使用されていないかぎり、すべてのパーティション間でデータがコピーされます。ADD または COALESCE 操作はパーティションごとに実行されますが、これは実質的に、新しいテーブルの作成と同じです。REORGANIZE 操作では変更されたパーティションのみがコピーされ、変更されていないものはそのままです。

インデックスの名前変更。InnoDB を除きます。

InnoDB と NDB に対するインデックスの追加または削除。

インプレースALTER TABLEの条件に、コメントの変更は入っていない。
おそらく.frmファイルで管理されている情報であればインプレースALTER TABLEになるが、.frmファイルはバイナリなので内容は確認できない。

検証

正確な検証ではないが、ALTER TABLEにかかる時間から、テーブルコピーが発生しているか調べた。

3000行のレコードが入ったテーブルに対して、以下のクエリを発行する。

mysql
ALTER TABLE users MODIFY name varchar(200) COMMENT '氏名';

かかった時間は0.3ms

型変更を伴うALTER TABLEではクエリの実行に数秒かかったため、
コメントを変更するだけのALTER TABLEでは一時テーブルの生成(テーブルコピー)は発生しない。