Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

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では一時テーブルの生成(テーブルコピー)は発生しない。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした