ALTER TABLEを上手に使いこなそう。

テーブル定義を変更したい。インデックスが壊れてしまったので再作成したい。そんな場合はALTER TABLEを使う。ALTER TABLEはテーブル定義を変更するお馴染みのコマンドであるが、その挙動は意外と知られていない。（エキスパートとおぼしき方々からも度々質問を受ける。）そんなわけで、今日はALTER TABLEについて解説しようと思う。

まず結論から言うと、なんとMySQLのALTER TABLEはテーブルのデータを全てコピーし直すのである。なんて無駄なことを！？と思うかも知れないが、テーブル定義（スキーマ）の変更を動的に行うには、ストレージエンジンによるサポートが必要であり、動的なスキーマ変更をサポートしているストレージエンジンはまだ少ないのである。（動的スキーマ変更をサポートしているのはMySQL Clusterぐらいだ。しかも追加だけ。）デフォルトで利用出来るMyISAMはInnoDBでは、全てのデータをコピーし直すのである。その場合の動作は次の通りだ。

1. テーブルをTL_WRITE_ALLOW_READロックする。このロックは特殊なロックで、テーブルロックの一種であるが、他のセッションからのREADを許可し、WRITEをブロックする。
2. 新しいテーブル定義を使って空のテンポラリテーブルを作成する。
3. 古いテーブルから新しいテーブルへデータをコピーする。
4. テーブルの名前を付け替えて、新しいテーブルを古いテーブルと同じ名前にする。古いテーブルは破棄する。
5. 新しいテーブルへブロックしていたWRITEをリダイレクトする。

そんなわけで、大きなテーブルをALTERするときには長時間WRITEがブロックされてしまうので注意しよう。また、ALTER TABLEでは完全なテーブルのコピーを作成する必要があるので、元のテーブルのサイズと同じぐらいのディスク空き容量が必要であることにも注意しなければいけない。空き容量が足りないとALTER TABLEコマンドは失敗してしまう。

このようなALTER TABLEコマンドであるが、使いようによっては便利な面もある。コマンドの実行中もテーブルからREADが出来るからだ。テーブルが壊れた場合にはREPAIR TABLEコマンドを使うし、最適化したい場合にはOPTIMIZE TABLEコマンドを使うのだが、これらのコマンドはWRITEだけでなくREADもブロックしてしまう。従って、メンテナンス中にWRITEは出来なくてもREADだけは可能にしたい、というような場合には、まずはALTER TABLEを試して見るといいだろう。テーブル定義の変更をせずに、テーブルを再作成したい場合には、次のようにALTER TABLEコマンドを実行するといい。

mysql> ALTER TABLE tbl1 ENGINE InnoDB;
mysql> ALTER TABLE tbl2 ENGINE MyISAM;

このようにストレージエンジンを指定してやると、ALTER TABLEコマンドによってテーブルの再作成が行われるのである。もちろん、ストレージエンジンは元のテーブルと同じものを指定する必要がある。（SHOW CREATE TABLEまたはSHOW TABLE STATUSコマンドでストレージエンジンを確認しよう。）

ALTER TABLEコマンドは意外と速い。テーブルの単純なコピーが行われるだけだからだ。MySQL公式のemployeesデータベースのsalariesテーブルには2844047行のデータが格納されているが、そのテーブルでも次の通りである。

mysql> ALTER TABLE salaries ENGINE InnoDB;
Query OK, 2844047 rows affected (49.76 sec)
Records: 2844047  Duplicates: 0  Warnings: 0

ただし、この値はあくまでも参考値ということで捕らえて欲しい。この値を測定したのはノートパソコン（ディスクは5400rpmのSATA）上であるので、H/W RAIDやSSDなどを使っている場合にはもっと良い数値になるだろう。また、行数が増えた場合には所要時間は線形に増加するわけでない点にも注意しよう。インデックスの再構築にはO(N log N)の時間がかかるからだ。10倍の行数がある場合には、10倍以上の時間がかかる。メンテナンス時間を見積もるには、試験環境で同程度の行数のテーブルを使ってテストしなければならない。

ちなみに、ALTER TABLEは操作が正常に完了するまでは元のテーブルに対して一切の変更を加えない。たとえコマンド実行中にマシンがクラッシュしても、テンポラリテーブルの残骸が残るだけで、元のテーブルは元通りである。そういう意味では、ALTER TABLEはとても安全な操作であるとも言える。

上記のようなメンテナンスでなく、本当にスキーマの変更をしたい場合には注意するべき点が一つある。コマンドが一回で終わるように、カラムやインデックスの追加・削除を一行で書くということである。一行でALTER TABLEを書くには、加えるべき変更をカンマで区切って羅列する。次のように。

mysql> ALTER TABLE tbl1 DROP INDEX idx1, DROP INDEX idx2... ADD INDEX (idxX);

こうすることで、コピーが一回で済むわけである。テーブルサイズが大きくなればなるほど、操作を一回で済ませることのメリットは大きくなる。

MySQLのALTER TABLEでは、カラムを任意の場所に追加することが出来る。何も指定しない場合は最後尾に追加されるが、FIRSTまたはAFTERオプションを指定することで、先頭または任意のカラムの後にカラムを挿入できるのである。これは、テーブルを再作成することのメリットである。

mysql> ALTER TABLE tbl1 ADD colY VARCHAR(100) AFTER colX, ADD colA INT UNSIGNED FIRST;

というわけで、今日はALTER TABLEコマンドを使う際のテクニックを紹介した。このエントリを読んでくれた方は、MySQL DBAにとって必須のコマンドであるALTER TABLEを上手に使いこなして欲しい。