技術めも

MariaDBの生成カラムをALTERするときの速度検証してみました。

確認バージョンは、10.3.8です。

 

準備として、以下のようなテーブルを作成し、データを投入しておきます。

create table user (
  id         integer not null auto_increment,
  name       varchar(64),
  money_free integer,
  money_paid integer,
  primary key (id)
);

insert into user () values ();
insert into user (id) select 0 from user;
: 上を20回くらい実行

update user set name = concat('name-', lpad(id, 10, '0')), money_free = ceil(rand() * 100), money_paid = ceil(rand() * 100);

MariaDB [test]> select count(*) from user;
+----------+
| count(*) |
+----------+
| 8388608  |
+----------+
1 row in set (1.579 sec)

下準備として、約800万件のデータを投入。

---

この状態で、通常・PERSITENT・VIRTUALのカラムをALTERで追加してみます。

通常カラムは、最後尾に追加すると、INSTANTが発動してしまうので、すべてAFTERで途中に差し込んでみます。

 

・通常

# カラム追加
MariaDB [test]> alter table user add column money_total_nornaml integer after name;
Query OK, 0 rows affected (17.338 sec)
Records: 0 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx01 on user (money_total_nornaml);
Query OK, 0 rows affected (20.096 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_nornaml;
Query OK, 0 rows affected (16.986 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加：17秒

インデックス追加：20秒

カラム削除：16秒

という結果でした。

 

・PERSISTENT

# カラム追加
MariaDB [test]> alter table user add column money_total_persitent integer as (money_free + money_paid) persistent after name;
Query OK, 8388608 rows affected (36.700 sec)
Records: 8388608 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx02 on user (money_total_persitent);
Query OK, 0 rows affected (19.660 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_persitent;
Query OK, 0 rows affected (16.810 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加：36秒

インデックス追加：19秒

カラム削除：16秒

という結果でした。

 

・VIRTUAL

# カラム追加
MariaDB [test]> alter table user add column money_total_virtual integer as (money_free + money_paid) virtual after name;
Query OK, 0 rows affected (0.012 sec)
Records: 0 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx03 on user (money_total_virtual);
Query OK, 0 rows affected (20.224 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_virtual;
Query OK, 0 rows affected (0.011 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加：0.01秒

インデックス追加：20秒

カラム削除：0.01秒

という結果でした。

 

PERSISTENTは、実データの生成も一緒に行われるので、

通常に比べて、ADDも遅くなるという結果でした。

VIRTUALは、実データの生成が無いので、

通常に比べて、ADD/DROPも圧倒的に高速ですね。

インデックスの作成速度は変わらないですね。

VIRTUALはPERISITENTに比べて、演算コストがかかるため、

そのあたりは注意が必要であると思いますが、

基本的には、VIRTUALを使っていく方針でよいのかな？という考察です。

 

以上