[MySQL]稼働中サービスにALTER TABLEを実行するときの注意点

mysql_logo

この記事には広告を含む場合があります。

記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。

カラムの変更を行う「ALTER TABLE」

テーブルのカラムを変更するときに使用する「ALTER TABLE」。仕様変更や機能拡張を行うと「ALTER TABLE」を実行する機会は多いのではないでしょうか? 特に稼働中のサービスに対して、何げなく「ALTER TABLE」を実行すると大きな痛手を負うことがあるため、注意点をまとめていきます。

 

ALTER TABLEの注意点

MySQLのバージョンにより「ALTER TABLE」の注意事項が異なります。

MySQL5.5以下で実行するALTER TABLEの注意点

MySQL5.5以下ではALTER TABLEを実行している最中は、基本的にテーブルがロックされるため、INSERTやDELETEが行えません。

特に数千万行を超える大量のレコードがあるテーブルに対してカラム変更を行うと

のように命令の実行完了までに時間がかかります。そのためカラムの変更を行うときは、メンテナンス時のみにしないとサービスの障害になることが想定されます。

 

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以降をインストールすることも考慮してみると良いと考えています。

 

 

ウェブプログラミングについては下記の本も参考になるので、スキルアップにお役立てください。

created by Rinker
徳丸 浩(著)
ページ数: 688ページ
出版社:SBクリエイティブ
発売日:2018/06/21

出典:amazon