MySQLのスキーマ変更をオンラインでやるための選択肢比較

MySQLを使っていて、スキーマを変更したい場合はAlterTableを流す必要があります。
すでに稼動しているサービスのスキーマを変更する際に、可能な限り稼働中システムに影響を与えずにスキーマの変更を行いたいです。

理想はオンライン状態を保った状態でAlterTableが全て完了することで、これが実現できれば少なくともスキーマ変更に伴うメンテナンスのためのダウンタイムが発生しません。

これを実現するためには幾つか方法が存在し、制約もあるみたいなので調べてまとめてみました。

その前にAlterTableのこれまでの挙動を調べる

オンラインでのスキーマ変更を調べる前に、まずはAlterTableのこれまでの挙動(MySQL5.1まで)について簡単にまとめてみます。これによってオンラインスキーマ変更のために何が問題になっているか？が理解できると思います。

MySQL5.1までのAlterTableは、変更したいスキーマ定義に沿った空のテーブルを作り、そこに古いテーブルのデータをコピーすることでスキーマの変更を行います。
この際にデータの不整合を防ぐため、まずは古いテーブルへのデータの書き込みをブロックするlock状態に入ります（ちなみに、古いテーブルからデータが読み込まれたとしても、新しいテーブルへのデータコピー時に不整合は起きないため、読み込みはブロックされません）。

つまり、AlterTable実行中は書き込みがlockされるわけです。
古いテーブルに大量のデータが入っている場合には、新しいテーブルへのデータコピーに時間が掛かるわけなので、書き込みlockによるメンテナンスのためのダウンタイムがどんどん伸びていくわけです。

これがMySQL5.1までのスキーマ変更の挙動でした。

参考にさせていただいた記事はこちら。

MySQL5.6からサポートされているオンラインDDL

DDLはデータ定義言語というもので、データベース自体を操作するためのSQL命令文のセットです。
AlterもDDLに含まれます。

多くのAlterTableの操作時に、上記のテーブルのコピーが発生しないようにする変更と、テーブルが変更されている時でも書き込みがロックされないようにした変更の2つを合わせてオンラインDDLと呼ばれています(ちなみに念のためですが、ロックが発生しないだけで、性能上の影響は当然出ます)。

あくまで 多くのAlterTable時であり、全てにおいてコピーが発生せず、書き込みがロックされないというわけではありません。
ざっくり書き込みロックされるされないで分類してみます。

書き込みロックされない

インデックスの作成,追加,削除
カラムのデフォルト値を設定する
カラムの自動インクリメント値を変更する
外部キー制約を追加,削除する
カラムを名前変更,追加※,削除,並べ替え
主キーを追加する
※オートインクリメントカラムの追加を除く

書き込みロックされる

全文検索インデックスを追加する
カラムを追加する(オートインクリメントカラムの追加)
カラムのデータ型を変更する
主キーを削除する
文字セットを変換する

参考にさせていただいたのはこちら。

つまり、MySQL5.6からは変更の内容によりますが、AlterTable実施時にロックが発生しないケースもあるということです。

pt-online-schema-change

Percona-Toolkitという有名なツールキットに同梱されているpt-online-schema-changeというツールがあり、こちらを使うことでも読み書きロックなしでオンラインスキーマ変更が可能です。

pt-online-schema-change(長いので以後PT)はMySQLのAlterTableの挙動を内部的に再現してスキーマの変更を行いますが、工夫することでロックなしでの動作を可能にしています。

MySQL5.1では古いテーブルの書き込みをロックしてデータの不整合を防いでいました。
これに対して、PTはトリガーの仕組みを使うことで、書き込みロックを行わずにデータの整合性を保ちます。
古いテーブルから新しいテーブルへのデータコピー中に古いテーブルに対して更新が走った場合、トリガーが古いテーブルのデータ更新に伴って新しいテーブルのデータも更新するわけです。

トリガーはInsert, Update, Deleteの3種類全てが貼られます。
そのため 1つのテーブルに対して同じ種類のトリガーは貼れないため、トリガーがすでに貼られているようなテーブルにはPTは使えません。
なお、トリガーはMySQL5.0.2からサポートされるので、PTはMySQL5.0.2以上で動作するそうです。

参考にさせていただいたのはこちら。

[20160721追記]
また、PTでは--dry-runオプションが使用できます。
これを使うと実際の変更DBに入れる前に前もって変更が正しく入るかどうかのチェックができるのでとても便利です。

比較とまとめ

MySQLのオンラインDDLでもPTでもそれぞれ制約はありますがオンラインでスキーマ変更が可能であることがわかりました。オンラインDLLは、オンラインで実行可能なスキーマ変更の種類に制約があり、PTでは既存トリガーの有無が制約になることがわかりました。
また、オンラインDDLはMySQL5.6から、PTはトリガーをサポートしているMySQL5.0.2から動作することがわかりました。

次に性能比較ですが、こちらの記事によると、スキーマ変更にかかる時間は対して変わらないものの、スキーマ変更中の影響という観点からはPTの方オンラインDDLよりも小さいそうです。
さらに、PTでは設定できるオプションが多く、既存システムへの影響を考えながらきめ細やかな実行戦略が組めそうです。
サイバーエージェントさんのこちらの記事にあるPTを慎重に使うためのオプションや過去の失敗事例もすごく参考になりました。

以上です。