自分が現在携わっているサービスのテーブル定義に変更があったので、DDL操作によるテーブル定義変更を行っていました。しかし、その中で特定の操作の処理に時間がかかり、最終的にはDBとのコネクションが切れてしました。
原因を調べたところ、テーブル定義変更対象のテーブルの件数が少ない場合はDDL操作の処理時間に問題ないが、件数が多くなると処理時間が長くなっていることが判明しました。
上記の件からDML操作1だけではなくDDL操作の処理時間もデータ件数によって差が生じることを知ることができましたが、自分の中に以下の疑問が湧きました。
「DDL操作によってデータ件数が多い場合に処理時間がそこまで増加しない場合と一気に増加する場合があるけど、なんでだろう？」
そこで、MySQLのDDL操作の処理時間について自分が調べ、実際に複数のDDL操作をする中で処理時間にどのような差が出るのかを検証してみました。
ALTER TABLEステートメントの３つの方式
MySQLのDDL操作には3つの方式があり、方式ごとにDDL操作で行われる処理が異なります。
インスタント方式
DDL操作において、テーブル定義の変更をデータディクショナリ2のメタデータ3のみを書き換える方式です。
この方式によるDDL操作はテーブルの大小に関わらずすぐに完了します。
もっとも迅速かつサービスへの影響が少ない方式です。
以下のDDL操作はこの方式で行うことができます
- カラム名の追加
- カラム名の変更
- インデックスの削除
インプレース方式
テーブルデータのコピーをせずにテーブル定義の変更を行う方式です。
テーブル定義の再構築を伴う操作と伴わない操作があり、再構築を伴う操作はテーブルサイズに比例して実行時間は長くなります。
後述するコピー方式よりは速いかつサービスへの影響が少ない方式です。
以下のDDL操作はこの方式で行うことができます。
- インデックスの追加
- カラムのNULLまたはNOT NULLへの変更
コピー方式
テーブルのデータを読み取り、コピーと再構築を行うことでテーブル定義の変更を行う方式です。DDL操作対象のテーブルをロックするため、その間でのDML操作は待機することになります。
DDL操作の時間が遅くかつサービスへの影響が大きい方式です。
以下のDDL操作はこの方式で行うことができます。
- カラムのデータ型変更
- FULLTEXTインデックスの作成
- キャラクターセットの変換
ここまでのまとめ
MySQLのDDL操作には3つの方式があり、DDL操作の処理時間の長さおよびサービスへの影響の大きさがインスタント< インプレース < コピーとなります。
参考資料の「MySQLのALTER TABLEステートメント実行時の注意点」を見ていただけると、方式ごとの違いがより分かると思います。
参考資料
方式の違いで実際のDDL操作のパフォーマンスにどれほど差が出るのか？
DDL操作の処理時間は方式ごとに本当に異なるのでしょうか？
また異なる場合はどのような差が出るのでしょうか？
この疑問を解消するために以下のような環境・条件の下で検証をしてみました。
検証内容
目的
- 方式ごとのDDL操作によって処理時間に差は出るのか？
- データ量が増加した場合、それぞれの方式でのDDL操作の処理時間にどのような差があるのか？
検証環境
MySQLバージョン: 8.4.4
検証対象のテーブル
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
検証対象のテーブルのデータ件数
- 1,000
- 10,000
- 100,000
- 1,000,000
検証するDDL操作
- カラムの追加(インスタント方式)
- セカンダリインデックスの作成(再構築を行わないインプレース方式)
- カラムの追加(再構築を行うインプレース方式)
- カラムのNULL化(コピー方式)
検証結果
カラムの追加(インスタント方式)
ALTER TABLE user ADD COLUMN age INT;
| 件数 | sec |
|---|---|
| 1,000 | 0.24 |
| 10,000 | 0.22 |
| 100,000 | 0.25 |
| 1,000,000 | 0.32 |
データの件数によって処理時間の増加は見られますが、最小と最大の差は0.1秒なのでデータの増加による処理時間の増加の問題はなさそうですね。
セカンダリインデックスの作成(再構築を行わないインプレース方式)
ALTER TABLE user ADD INDEX email (email);
| 件数 | sec |
|---|---|
| 1,000 | 0.81 |
| 10,000 | 0.74 |
| 100,000 | 3.34 |
| 1,000,000 | 7.38 |
100,000あたりから処理時間に差が出てきました。しかし1,000,000でも8秒かかってないので、データ数が多くても処理時間的には問題なさそうです。
カラムのNULL化(再構築を行うインプレース方式)
ALTER TABLE user MODIFY COLUMN name varchar(10) NOT NULL;
| 件数 | sec |
|---|---|
| 1,000 | 1.92 |
| 10,000 | 5.45 |
| 100,000 | 8.71 |
| 1,000,000 | 25.32 |
10,000から処理時間に差が出てきました。1,000,000で20秒を超えるようになってきたので、稼働しているサービスのデータが多い場合には少し注意が必要かもしれません。
カラムのデータ型の変更(コピー方式)
ALTER TABLE user MODIFY COLUMN age BIGINT;
| 件数 | sec |
|---|---|
| 1,000 | 2.05 |
| 10,000 | 7.27 |
| 100,000 | 10.12 |
| 1000000 | 65.25 |
1,000,000の処理時間の増加幅が他の方式に比べて段違いに大きくなりました。ここまで処理時間が大きくなると、データ数が多い場合は注意して行うのは当然のこととして、DDL操作の取りやめも検討すべきかもしれません。
検証結果のまとめ
今回検証したことから以下のことが分かりました。
- DDL操作に対する処理時間は、インスタント < 再構築を行わないインプレース < 再構築を行うインプレース < コピーとなる
- データ件数が増加するごとにDDL操作の処理時間も増加するが、処理時間の増加幅の大きさはインスタント方式 < 再構築を行わないインプレース方式 < 再構築を行うインプレース方式 < コピー方式となる
-
データ操作言語。INSERT、UPDATE および DELETE 操作を実行するための一連の SQL ステートメント MySQL 用語集 ↩
-
データベースに関するメタデータを格納するシステムテーブルの集まり 【MySQL】データディクショナリを調べる ↩
-
「データに関するデータ」。 データベースについて記述しているすべてのものがメタデータであり、データベースの内容ではない MySQL8.0 リファレンスマニュアル 10.2.2 メタデータ用の UTF-8 ↩