MariaDB 10.6.11でテーブル末尾にデフォルト値NULLのフィールドを追加するとtmpテーブルへコピーが発生する理由

2024-04-02

MariaDB 10.6.11でテーブル末尾にデフォルト値NULLのフィールドを追加するとtmpテーブルへコピーが発生する理由

変更点

10.6.11以前では、ALTER TABLEコマンドでテーブルに新しいフィールドを追加する場合、以下の2つの方法がありました。

オンラインDDL：テーブルをロックせずに、新しいフィールドを末尾に追加します。

10.6.11では、デフォルト値NULLのフィールドを追加する場合、オンラインDDLではなくオフラインDDLが常に使用されます。

オフラインDDLを使用する理由は、以下の2つです。

データの一貫性: オンラインDDLでは、新しいフィールドにデフォルト値が設定される前に、データが挿入される可能性があります。これは、データの一貫性に問題を引き起こす可能性があります。
パフォーマンス: オフラインDDLは、テーブル全体をコピーすることで、新しいフィールドを挿入するよりも高速に処理できる場合があります。

tmpテーブルへのコピー

オフラインDDLでは、新しいフィールドを挿入するために、テーブル全体がtmpテーブルへコピーされます。これは、以下の理由によるものです。

テーブル構造を変更するには、テーブル全体をロックする必要があります。
ロックされたテーブルに直接変更を加えると、パフォーマンスが低下する可能性があります。
tmpテーブルを使用することで、元のテーブルをロックせずに、新しいフィールドを挿入することができます。

影響を受ける場合

以下のいずれかに該当する場合、tmpテーブルへのコピーが発生する可能性があります。

MariaDB 10.6.11を使用している
テーブルにデフォルト値NULLのフィールドを追加する
テーブルサイズが大きい

回避策

tmpテーブルへのコピーを回避するには、以下の方法があります。

オンラインDDLを使用できないかどうか確認する: オンラインDDLを使用できる場合は、tmpテーブルへのコピーは発生しません。
デフォルト値をNULL以外にする: デフォルト値をNULL以外にすることで、オフラインDDLを使用せずに新しいフィールドを追加することができます。
MariaDB 10.6.10以前のバージョンを使用する: 10.6.10以前のバージョンでは、tmpテーブルへのコピーは発生しません。

MariaDB 10.6.11でテーブル末尾にデフォルト値NULLのフィールドを追加すると、tmpテーブルへコピーが発生する可能性があります。これは、データの一貫性とパフォーマンスを向上させるための変更によるものです。tmpテーブルへのコピーを回避するには、上記の回避策を検討してください。

-- テーブル作成
CREATE TABLE test (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

-- デフォルト値NULLのフィールドを追加
ALTER TABLE test ADD COLUMN age INT NULL DEFAULT NULL;

-- テーブル内容確認
SELECT * FROM test;

-- tmpテーブルの存在確認
SHOW TABLES LIKE 'test_tmp';

testテーブルが作成されます。
デフォルト値NULLのフィールドageがtestテーブルに追加されます。
tmpテーブルtest_tmpが存在することが確認できます。

注意: 上記のコードは、MariaDB 10.6.11以降でのみ動作します。

MariaDB 10.6.11でテーブル末尾にデフォルト値NULLのフィールドを追加する他の方法

オンラインDDLを使用する

条件:

追加するフィールドがデフォルト値NULL以外の場合は、オンラインDDLを使用できます。
オンラインDDLは、テーブルをロックせずに新しいフィールドを追加するため、tmpテーブルへのコピーが発生しません。

方法:

ALTER TABLE test ADD COLUMN age INT NOT NULL DEFAULT 18;

注意: デフォルト値をNULLに設定すると、tmpテーブルへのコピーが発生します。

ALTER TABLE test ADD COLUMN age INT NULL DEFAULT NULL;

別のテーブルを作成してデータを移行する

新しいフィールドを含む新しいテーブルを作成します。
既存のテーブルのデータを新しいテーブルにコピーします。
既存のテーブルを削除します。
新しいテーブルの名前を既存のテーブルの名前に変更します。

例:

-- 新しいテーブルを作成
CREATE TABLE test_new (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age INT NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

-- データをコピー
INSERT INTO test_new (id, name)
SELECT id, name FROM test;

-- 既存のテーブルを削除
DROP TABLE test;

-- 新しいテーブルの名前を変更
RENAME TABLE test_new TO test;