この記事には広告を含む場合があります。
記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。
目次
カラムの変更を行う「ALTER TABLE」
テーブルのカラムを変更するときに使用する「ALTER TABLE」。仕様変更や機能拡張を行うと「ALTER TABLE」を実行する機会は多いのではないでしょうか？ 特に稼働中のサービスに対して、何げなく「ALTER TABLE」を実行すると大きな痛手を負うことがあるため、注意点をまとめていきます。
ALTER TABLEの注意点
MySQLのバージョンにより「ALTER TABLE」の注意事項が異なります。
MySQL5.5以下で実行するALTER TABLEの注意点
MySQL5.5以下ではALTER TABLEを実行している最中は、基本的にテーブルがロックされるため、INSERTやDELETEが行えません。
特に数千万行を超える大量のレコードがあるテーブルに対してカラム変更を行うと
|
1 2 |
MySQL [root]> ALTER TABLE `hogehoge` ADD `test_user2` int(11) DEFAULT '0' AFTER `test_user1`; Stage: 1 of 2 'copy to tmp table' 34.2% of stage done |
のように命令の実行完了までに時間がかかります。そのためカラムの変更を行うときは、メンテナンス時のみにしないとサービスの障害になることが想定されます。
MySQL5.6以上で実行するALTER TABLEの注意点
MySQL5.6は「オンラインDDL」の機能が実装されており、テーブルが変更されている最中にSELECTやINSERT、UPDATE、及びDELETEに関する処理が継続できるようになっています。
オンライン DDL の概要
https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.html
ただオンラインDDLに対応していても注意事項はあります。
ALTER TABLEの開始時と終了時に一瞬テーブルロックが発生する
案外知らない人が多いですが、オンラインDDLに対応していてもALTER TABLEの開始時と終了時に一瞬だけテーブルロックが発生します。通常は気にしなくても良いレベルですが、決済回りなどの重要なテーブルに対してALTER TABLEを実行するときは注意が必要です。
パラメータ「innodb_online_alter_log_max_size」の値に注意
MySQL5.6.6から実装されたパラメータ「innodb_online_alter_log_max_size」は、ALTER TABLE中のINSERTなどの命令を保管する一時ファイルの最大の大きさを指定します。つまりALTER TABLE中に「innodb_online_alter_log_max_size」のサイズを超えるコマンドが実行されたときは、ALTER TABLE自体が失敗します。そのため事前に「innodb_online_alter_log_max_size」に十分なサイズを設定しておく必要があります。
innodb_online_alter_log_max_size
https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html
一時ログファイルがサイズの上限を超えた場合は、ALTER TABLE 操作に失敗し、コミットされていない並列 DML 操作がすべてロールバックされます。したがって、このオプションの値を大きくすると、オンライン DDL 操作時に実行できる DML 数は多くなりますが、ログからデータを適用するためにテーブルがロックされると、DDL 操作の終了時の期間も長くなります。
さいごに
Linuxでは、まだデフォルトでMySQL(またはMariaDB)5.5以下を採用しているOSが多くあります。もしセキュリティの担保として、OSが提供しているMySQLを利用する方針のときは致し方ありません。ただ外部要因による決まりが無いときは、MySQL5.6以降をインストールすることも考慮してみると良いと考えています。
