MySQLのオンラインDDLについて

目次

お世話になっております。井出です。

開発する上でデータベースのテーブル構成を変更する事は
それなりの頻度で発生するかと思いますが
テーブルロックなどの知識が無いなかDDLを流すのは
運用する上で危険な行為になるため、整理も兼ねてまとめてみました。

オンラインDDLのお話をする前に、簡単にですが用語の説明を

DDLとは

DDLとは「Data Definition Language」の略で
データ定義言語という意味になります
※ definitionは「定義」

SQLでいうと以下がそれにあたります。
・CREATE
・DROP
・ALTER
・TRUNCATE

DMLとは

DMLとは「Data Manipulation Language」の略で
データ操作言語という意味になります。
※ manipulationは「操作」

SQLでいうと以下がそれにあたります。
・SELECT
・INSERT
・UPDATE
・DELETE

テーブルロックとは

テーブルをロックします。
コマンドでいうとこちら

解放はこちら

以下の状態でロックされるため、ロック解除されるまでDML操作が待ちの状態になります。

ロックの種類

ロック種別 ロックを行ったプロセス ロック状態で実行されたプロセス
READ READのみ可能
WRITEするとエラーになる
READのみ可能
WRITE READ/WRITE可能 READ/WRITE不可

(本題)オンラインDDLとは?

前置きが長くなりましたが、本題のオンラインDDLについて

テーブルのロックを取らないのでDDLをDB実行時に
DBに対して変更系のSQLを行ってもロックが掛からずサービスを停止することがなくなる。

※ ただしMySQLのバージョンによって動作が異なります。

もうサポートが切れているバージョンもありますが
5.5系以前から8系まででの動作をまとめています。

5.5系以前

オンラインDDLはMySQL5.6から実装されました。
そのためMySQL5.5以前のバージョンでは
ALTER TABLEを実行中は、テーブルがロックされるため、
DML実行するとロックが解除されるまで待ちとなります。

運用中のサービスがロック中、DB操作できなくなるため
サービスをメンテナンス画面にするなど対応が大変です。

5.6系

5.6からオンラインDDLが実装されました。
DDL実行中もテーブルロックされず、待ちが発生しなくなったので
サービス稼働中でも実施しやすくなりました。

ただ一切問題無いのかといわれるとそうでもなく
オンラインDDLの開始前と完了前にDDLの対象テーブルに
実行中のトランザクションが存在した場合、
対象のトランザクションがコミットまたはロールバックするまで待機します。

この待機状態になっているDDL、DML が Waiting for table metadata lock
という状態になり、
トランザクションが完了するまで待ちのキューが増え続けご臨終状態となります。

詳細は公式ドキュメントに
https://dev.mysql.com/doc/refman/5.6/ja/metadata-locking.html

もしWaiting for Table metadata lockとなった場合

を叩き、TRX_STARTED(トランザクション開始時刻)が古いものからKillするのが良いとの事
https://dev.mysql.com/doc/refman/5.6/ja/information-schema-innodb-trx-table.html

Alter Table実行時にオンラインDDL操作を制御するための
オプション(LOCKとALGORITHM句)も追加されています。

LOCK句

オプション名 説明
NONE クエリーと並列DML操作を許可
SHARED クエリーを許可・並列DML操作をブロック
EXCLUSIVE クエリーと並列DML操作をブロック
DEFAULT 使用可能なもっとも低いレベルのロックを使用

※ LOCK句を省略した場合はDEFAULTが設定される

ALGORITHM句

オプション名 説明
INPLACE インプレース方式
COPY テーブルコピー方式
DEFAULT インプレース方式を優先

※ ALGORITHM句を省略した場合はDEFAULTが設定される

ただすべてのDDLに対して有効では無いので、
この辺りは公式ドキュメントを参考にするのがベストです。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.html#innodb-online-ddl-summary-grid

※カラムのデータ型を変更については5.6では非対応のため注意が必要です

公式ドキュメントを見て有効、無効を判断するとともに
オプションを付けることにより、無効のものであればエラーになるので、
明示的にLOCK句とALGORITHM句を記述する設計にすれば大丈夫かもしれません。
カラムのデータ型を変更を行いたい場合の解決にはなっていませんが。。

5.7系

・varcharのカラム長の変更をサポート
※内容うす

MySQL8.0.12系以降

・Instant Add Columnがサポート

https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/
制限は色々とありますがadd columnの処理がかなり高速化されました。
※ 内容うっす!

補足:RailsのMigrationでオプションをつける方法

普段Railsメインで開発しているため、
Migrationで「ALGORITHM」「LOCK」のオプションをつける方法を

executeでSQL文実行しつつオプションをという感じで。分かりやすくてナイスですね。
rails love

公式ドキュメントを参照ばかりの記事になりましたが。。
ありがとうございました!

Back to Top