レコード数の多いテーブルのカラム追加は遅い？ MariaDBで誤解されがちなスキーマ変更

この記事は BABY JOB Advent Calendar 2024 の 12日目の記事です。

はじめに

データベース運用では、「テーブルスキーマ変更はレコード数が多いほど処理時間がかかる」という印象を持っている方も多いかもしれません。
しかしMariaDBでは一部のスキーマ変更がレコード数に依存せず、数千万レコード規模のテーブルでも即時に完了するケースがあります。
本記事ではMariaDBにフォーカスして、どのような操作がレコード数に影響されどのような操作が即時完了するのかを、実測しながら検証したいと思います。

準備

まずは、ダミーのテーブルを作成して、1,000万レコード追加していきます。
検証ではこの規模で顕著な効果が確認できたので十分なデータ量と考えています。
今回このダミーテーブルに対して、さまざまなスキーマ変更を試していきます。

-- ダミーテーブルの作成
CREATE TABLE dummy_table (
	id INT PRIMARY KEY AUTO_INCREMENT,
	data VARCHAR(255)
);
-- 1,000万レコードの挿入
INSERT INTO dummy_table (data) SELECT REPEAT('a', 255) FROM seq_1_to_10000000;

検証環境
バージョン：MariaDB 10.6
ストレージエンジン：InnoDB

検証

検証方法は作成したダミーテーブルに対してスキーマ変更を行なった後に、SHOW PROFILEステートメントを使用して実行時間を計測していきます。

パターン1:NULL許可カラムの追加

ALTER TABLE dummy_table ADD COLUMN dummy_column VARCHAR(100) NULL;

結果：即時完了

NOT NULLカラムの追加(デフォルト値なし)

ALTER TABLE dummy_table ADD COLUMN dummy_column VARCHAR(100) NOT NULL;

結果：即時完了

NOT NULLカラムの追加(デフォルト値あり)

ALTER TABLE dummy_table ADD COLUMN dummy_column VARCHAR(100) NOT NULL DEFAULT 'default_value';

結果：即時完了

パターン4:カラムのリネーム

ALTER TABLE dummy_table CHANGE COLUMN data new_dummy_column VARCHAR(255);

結果：即時完了

パターン5:カラムにインデックス追加

ALTER TABLE dummy_table ADD INDEX idx_data (data);

結果：処理時間がかなり長い

パターン6:カラムのデータ型変更

ALTER TABLE dummy_table MODIFY COLUMN data CHAR(255);

結果：処理時間が長い

パターン7:カラムの順序変更

ALTER TABLE dummy_table ADD COLUMN dummy_column VARCHAR(100) NULL;
ALTER TABLE dummy_table MODIFY COLUMN data AFTER dummy_column;

結果：即時完了

パターン8:カラムの削除

ALTER TABLE dummy_table DROP COLUMN data;

結果：即時完了

検証結果

以上の結果から、MariaDBでのテーブルスキーマ変更の処理時間は以下のように分類できます。

即時に完了する操作

• NULL許可カラムの追加
• NOT NULLカラムの追加(デフォルト値なし)
• NOT NULLカラムの追加(デフォルト値あり)
• カラムのリネーム
• カラムの順序変更
• カラムの削除

レコード数に依存して時間がかかる操作

• カラムにインデックス追加
• カラムのデータ型変更

まとめ

このようにスキーマ変更を行うSQLの実行でも、処理時間がレコード数に関係ないものも意外と多くあるというのがお分かりいただけたかと思います。
特にカラム追加ではNULL許可・非許可に関わらず即時完了していました。
これはデータベース操作がメタデータ変更のみで完結する場合や、MariaDBで導入されているインスタントDDLというテーブルスキーマ変更を高速化する機能によるものです。

ただしMariaDBのバージョンやストレージエンジン、テーブル構成によって挙動は変わることもあり、別の環境では同じ操作でテーブル再構築が発生する場合もありますので、利用中のバージョンやマニュアルをご確認いただければと思います。

レコード数に依存して処理時間が増加する操作の中でも、インデックス追加は特にその相関が顕著に現れていました。
またインデックス追加などは処理中にテーブルや行をロックしてしまったりするので通常アプリケーションを停止して行う必要があります。
オンラインDDLを活用することでダウンタイム無しでスキーマ変更することができるので、こうした機能を活用すれば、大量データを抱える本番環境であっても、サービスを停止せずにインデックスの追加やその他のスキーマ変更が可能となる場合があります。

今回はMariaDBについて解説させていただきましたが、多くのデータベースエンジンでも同じようにレコード数に関係なくスキーマ変更が可能な操作は多くありますので、ぜひチェックしていただければと思います！