動的スキーマ変更 – MariaDB Server 10.4

spacer

2018年5月に GA となった MariaDB Server 10.3 では Instant ADD Column (既存テーブルの最後列に動的にカラムを追加する機能)が実装されましたが,2019年6月に GA となった MariaDB Server 10.4 ではさらに多くのスキーマ変更(Instant DDL)が可能となりました。本記事ではこれらの動的スキーマ変更機能に関して解説いたします。

インスタントDDL

MariaDB Server 10.4 では InnoDB ストレージエンジンを用いる場合,以下のようなスキーマの動的変更が可能となりました。

  • 列の追加 (10.3以降)
  • 列の削除
  • 列の順序を変更
  • データ型変更
  • NULLの使用を許可
  • デフォルトの設定と削除
  • 制約を追加または削除

下記機能に関して,以下に例を示します。

ALTER TABLE … ADD COLUMN : 列の追加

まず最初に以下のような a, b 列からなる t1 テーブルを作成します。

CREATE OR REPLACE TABLE t1 (
   a int PRIMARY KEY,
   b varchar(50)
);

ここで,alter_algorithm に INSTANT を指定し,列(カラム)を追加します。

SET SESSION alter_algorithm='INSTANT';
ALTER TABLE t1 ADD COLUMN c varchar(50);

DESC 文でテーブル定義を確認してみます。

MariaDB [test]> DESC t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | NO   | PRI | NULL    |       |
| b     | varchar(50) | YES  |     | NULL    |       |
| c     | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

正常に c 列が追加されていることが確認できました。ここまでは MariaDB Server 10.3 でも可能でしたが,10.4 では,最後列だけでなく,任意の位置に追加することができます。

CREATE OR REPLACE TABLE t2 (
   a int PRIMARY KEY,
   b varchar(50)
);
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE t2 ADD COLUMN c varchar(50) AFTER a;

DESC 文でテーブル定義を確認します。

MariaDB [test]> DESC t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | NO   | PRI | NULL    |       |
| c     | varchar(50) | YES  |     | NULL    |       |
| b     | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

意図したとおり,a 列の直後に c 列が追加されていることが確認できました。

ALTER TABLE … DROP COLUMN : 列の削除
CREATE OR REPLACE TABLE t3 (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE t3 DROP COLUMN c;

DESC 文で確認しますと,c 列が削除されていることが確認できます。

MariaDB [test]> DESC t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | NO   | PRI | NULL    |       |
| b     | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
ALTER TABLE … MODIFY COLUMN : 列の変更

MODIFY COLUMN では,列の順番の変更や,型(type)の変更が可能となっています。まず,順番の変更を行ってみます。

列の順番変更

CREATE OR REPLACE TABLE t4 (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE t4 MODIFY COLUMN c varchar(50) AFTER a;

c 列がテーブル作成時に指定した最後列ではなく,a 列の後に位置が変更されています。

MariaDB [test]> DESC t4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | NO   | PRI | NULL    |       |
| c     | varchar(50) | YES  |     | NULL    |       |
| b     | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

列のデータ型変更

現時点では,列のデータ型(type)変更には多くの制限があります。詳細は以下の公式ドキュメントを参照願います。

https://mariadb.com/kb/en/library/innodb-online-ddl-operations-with-algorithminstant/#changing-the-data-type-of-a-column

例えば以下のように,c 列を varchar から int に変更しようとしますと,

CREATE OR REPLACE TABLE t5 (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE t5 MODIFY COLUMN c int;

以下のエラーとなります。

ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY

可能な操作としては,varchar 型においてサイズを大きくすることが可能です。

CREATE OR REPLACE TABLE t6 (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
) CHARACTER SET=latin1;
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE t6 MODIFY COLUMN c varchar(100);

DESC 文で確認しますと意図通りに c 列が varchar(100) となっています。

MariaDB [test]> DESC t6;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | int(11)      | NO   | PRI | NULL    |       |
| b     | varchar(50)  | YES  |     | NULL    |       |
| c     | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
NULLを許可
CREATE OR REPLACE TABLE t7 (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50) NOT NULL
) ROW_FORMAT=REDUNDANT;

DESC 文で確認しますと,c 列は Null=No となっています。

MariaDB [test]> DESC t7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | NO   | PRI | NULL    |       |
| b     | varchar(50) | YES  |     | NULL    |       |
| c     | varchar(50) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

ここで,MODIFY COLUMN で NULL を許可してみます。

SET SESSION alter_algorithm='INSTANT';
ALTER TABLE t7 MODIFY COLUMN c varchar(50) NULL;

DESC 文で確認しますと,c 列は Null=YES となっており,即時変更されたことが確認できます。

MariaDB [test]> DESC t7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | NO   | PRI | NULL    |       |
| b     | varchar(50) | YES  |     | NULL    |       |
| c     | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

ただし,NULL から NOT NULL への変更はできませんので,留意願います。

ALTER TABLE … ALTER COLUMN

デフォルト値の変更

CREATE OR REPLACE TABLE t8 (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

各カラムのデフォルト値は NULLとなっています。

MariaDB [test]> DESC t8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | NO   | PRI | NULL    |       |
| b     | varchar(50) | YES  |     | NULL    |       |
| c     | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

ここで,以下の ALTER COLUMN を実行し,デフォルトの文字列を指定します。

SET SESSION alter_algorithm='INSTANT';
ALTER TABLE t8 ALTER COLUMN c SET DEFAULT 'No value explicitly provided.';

以下のようにデフォルトの文字列が設定されました。

MariaDB [test]> DESC t8;
+-------+-------------+------+-----+-------------------------------+-------+
| Field | Type        | Null | Key | Default                       | Extra |
+-------+-------------+------+-----+-------------------------------+-------+
| a     | int(11)     | NO   | PRI | NULL                          |       |
| b     | varchar(50) | YES  |     | NULL                          |       |
| c     | varchar(50) | YES  |     | No value explicitly provided. |       |
+-------+-------------+------+-----+-------------------------------+-------+
まとめ

MariaDB Server 10.4 で追加された動的スキーマ変更機能(Instant DDL)について解説させて頂きました。従来は大規模テーブルでは非常に長時間を必要としたテーブル変更操作がオンラインで即時に行えるようになり,DBA(データベース管理者)の方々にとっては有益な新機能と言えるかと存じます。