すっかり2013年も終わりに近づいてきましたね。さて、今回はMySQLでカラムやインデックスの追加、削除をするときに知っておいた方がいいかなーと思った注意点の話です。

カラムやインデックスの追加、削除を行う際にはテーブルが共有ロックされる

まず、カラムやインデックスの追加、削除といったいわゆる ALTER TABLE に相当する処理を行うとテーブルが共有ロック(読み込みのみ受け付け、更新処理は受け付けないというロック)されるという話です。

この辺の話は @nippondanji さんが詳しく書かれています。

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

処理中は共有ロックされている(更新処理を受け付けない)ため、元のテーブルと新しいテーブルの中身が同じになることが保証されます。また、テンポラリテーブルを作ってからそれを RENAME するので、処理の途中で止めてもテンポラリテーブルのゴミが残るだけでデータの不整合は起こりません。

あとこれ重要なんですけど、ALTER TABLE コマンドではカラムやインデックスの追加、削除が1行でまとめて書けます。何度も何度も無駄にテンポラリテーブルを作る手間を省けますね。これ、先日も忘れていて別々にやってしまった。。

ほんと、テンポラリテーブルにデータをコピーするのが時間掛かりすぎて泣けます。データサイズとかもあると思いますが、参考までに先日250万件ほどのテーブルでカラムを追加したら50分ほど掛かりました。

例えば rake db:migrate で上記のような処理をしたときには、rake のプロセスを KILL しても処理は実行され続ける

もう一つ注意点として、例えば Rails などを使っていると rake db:migrate のようなコマンドで上記のような処理を実行できます。そういった場合に、「今走らせた処理が間違っているから処理を止めたい!」などとなったときには、rake のプロセスを KILL しただけでは処理は止まりません。

MySQL 側のプロセスに残ってしまっているのでこちらも KILL する必要があります。show processlist; して KILL したいプロセスのIDを調べてそれも KILL しましょう!

mysql> show processlist;
mysql> kill [ID];

そんな感じで ALTER TABLE をかわいがりましょう〜

参考:MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.1.2 ALTER TABLE 構文

15:01追記:
あっという間に大変参考になる情報を教えてもらえました。Twitter++


このエントリーをはてなブックマークに追加