MySQL8.0の Instant Add Columnで追加したカラムを全行updateするとデータサイズとか実行時間ととかどうなるか試してみた。

まず、 Instant Add Columnとは...
データディクショナリのみを変更することで、add column処理が即時で完了する操作。

ALTER TABLE t2 ADD id3 int not null default 0 , ALGORITHM = INSTANT ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

こんな感じで実行する。

ALGORITHM句を省略すると、 instant -> inplace -> copyの順番で可能なものを実行する

INSTANTで実行可能なもの一覧
- Adding a column
- Dropping the column default value
- Modifying the definition of an ENUM or SET column
- Adding a VIRTUAL column
Add columnでも INSTANTで実行できないもの
- テーブルの間にカラムを追加すること(必ず最後の列に追加すること)
- ALGORITHM=COPY/INPLACE が必要な他のalter tableと一緒に実行
- ROW_FORMAT=COMPRESSED
- a FULLTEXT indexのあるテーブル
- Temporary tables
- Table in the data dictionary tablespace

Instant Add Columnで追加したカラムを全行updateする

NULLABLE(DEFAULTありなし)、とNOT NULL (DEFAULTありなし)のカラムを追加して試してみる。 NOT NULLのDEFAULTありなしはどっちも同じだと思ったけど、一応追加。

テーブル作成

instant_null テーブル : ALGORITHM = INSTANTでNULLABLEなカラムを追加
inplace_null テーブル : ALGORITHM = INPLACEでNULLABLEなカラムを追加
instant_notnull テーブル : ALGORITHM = INSTANTでNOT NULLなカラムを追加
inplace_notnull テーブル : ALGORITHM = INPLACEでNOT NULLなカラムを追加
instant_null_default テーブル : ALGORITHM = INSTANTでNULLABLEでDEFAULT値付きなカラムを追加
inplace_null_default テーブル : ALGORITHM = INPLACEでNULLABLEでDEFAULT値付きなカラムを追加
instant_notnull_default テーブル : ALGORITHM = INSTANTでNOT NULLでDEFAULT値付きなカラムを追加
inplace_notnull_default テーブル : ALGORITHM = INPLACEでNOT NULLでDEFAULT値付きなカラムを追加

CREATE TABLE instant_null (id serial,id2 int);
insert into instant_null (id2) values (1);
insert into instant_null (id2) select id2 from instant_null;　繰り返しで数回実行

CREATE TABLE inplace_null like instant_null;
CREATE TABLE instant_notnull like instant_null;
CREATE TABLE inplace_notnull like instant_null;
CREATE TABLE inplace_null_default like instant_null;
CREATE TABLE instant_null_default like instant_null;
CREATE TABLE inplace_notnull_default like instant_null;
CREATE TABLE instant_notnull_default like instant_null;

insert into inplace_null (id2) select id2 from instant_null;
insert into instant_notnull (id2) select id2 from instant_null;
insert into inplace_notnull (id2) select id2 from instant_null;
insert into inplace_null_default (id2) select id2 from instant_null;
insert into instant_null_default (id2) select id2 from instant_null;
insert into inplace_notnull_default (id2) select id2 from instant_null;
insert into instant_notnull_default (id2) select id2 from instant_null;

データの状態

buffer pool に載った状態で、データサイズは同じ。

select 
  (select count(*) from instant_null) as instant_null,
  (select count(*) from inplace_null) as inplace_null, 
  (select count(*) from instant_notnull) as instant_notnull,
  (select count(*) from inplace_notnull) as inplace_notnull,
  (select count(*) from instant_null_default) as instant_null_default,
  (select count(*) from inplace_null_default) as inplace_null_default,
  (select count(*) from instant_notnull_default) as instant_notnull_default,
  (select count(*) from inplace_notnull_default) as inplace_notnull_default;

+--------------+--------------+-----------------+-----------------+----------------------+----------------------+-------------------------+-------------------------+
| instant_null | inplace_null | instant_notnull | inplace_notnull | instant_null_default | inplace_null_default | instant_notnull_default | inplace_notnull_default |
+--------------+--------------+-----------------+-----------------+----------------------+----------------------+-------------------------+-------------------------+
|      8388608 |      8388608 |         8388608 |         8388608 |              8388608 |              8388608 |                 8388608 |                 8388608 |
+--------------+--------------+-----------------+-----------------+----------------------+----------------------+-------------------------+-------------------------+

select NAME,FILE_SIZE from information_schema.INNODB_TABLESPACES where NAME like '%t/instant%' or  NAME like '%t/inplace%';

+---------------------------+-----------+
| NAME                      | FILE_SIZE |
+---------------------------+-----------+
| t/instant_null            | 297795584 |
| t/inplace_null            | 297795584 |
| t/instant_notnull         | 297795584 |
| t/inplace_notnull         | 297795584 |
| t/inplace_null_default    | 297795584 |
| t/instant_null_default    | 297795584 |
| t/inplace_notnull_default | 297795584 |
| t/instant_notnull_default | 297795584 |
+---------------------------+-----------+

SELECT * FROM sys.innodb_buffer_stats_by_table WHERE object_name like 'instant%' or  object_name like 'inplace%';

+---------------+-------------------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | object_name             | allocated  | data       | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------------------+------------+------------+-------+--------------+-----------+-------------+
| t             | instant_notnull         | 271.73 MiB | 248.30 MiB | 17391 |            0 |      6419 |     8405998 |
| t             | instant_notnull_default | 271.73 MiB | 248.30 MiB | 17391 |            0 |      6419 |     8405998 |
| t             | instant_null            | 271.73 MiB | 248.30 MiB | 17391 |            0 |      6096 |     8405998 |
| t             | instant_null_default    | 271.73 MiB | 248.30 MiB | 17391 |            0 |      6419 |     8405998 |
| t             | inplace_notnull         | 271.73 MiB | 248.30 MiB | 17391 |            0 |      6420 |     8405998 |
| t             | inplace_notnull_default | 271.73 MiB | 248.30 MiB | 17391 |            0 |      6427 |     8405998 |
| t             | inplace_null            | 271.73 MiB | 248.30 MiB | 17391 |            0 |      6419 |     8405998 |
| t             | inplace_null_default    | 271.73 MiB | 248.30 MiB | 17391 |            0 |      6421 |     8405998 |
+---------------+-------------------------+------------+------------+-------+--------------+-----------+-------------+

bigint typeをAdd columnする

ALTER TABLE instant_null            ADD col bigint NULL , ALGORITHM = instant; 
Query OK, 0 rows affected (0.01 sec)
ALTER TABLE inplace_null            ADD col bigint NULL , ALGORITHM = inplace; 
Query OK, 0 rows affected (8.49 sec)
ALTER TABLE instant_notnull         ADD col bigint NOT NULL , ALGORITHM = instant; 
Query OK, 0 rows affected (0.01 sec)
ALTER TABLE inplace_notnull         ADD col bigint NOT NULL , ALGORITHM = inplace; 
Query OK, 0 rows affected (9.09 sec)
ALTER TABLE instant_null_default    ADD col bigint NULL DEFAULT 100, ALGORITHM = instant;　
Query OK, 0 rows affected (0.01 sec)
ALTER TABLE inplace_null_default    ADD col bigint NULL DEFAULT 100, ALGORITHM = inplace; 
Query OK, 0 rows affected (9.37 sec)
ALTER TABLE instant_notnull_default ADD col bigint NOT NULL DEFAULT 100, ALGORITHM = instant; 
Query OK, 0 rows affected (0.01 sec)
ALTER TABLE inplace_notnull_default ADD col bigint NOT NULL DEFAULT 100, ALGORITHM = inplace;
Query OK, 0 rows affected (9.05 sec)

サイズ

mysql> select NAME,FILE_SIZE from information_schema.INNODB_TABLESPACES where NAME like '%t/instant%' or  NAME like '%t/inplace%';
+---------------------------+-----------+
| NAME                      | FILE_SIZE |
+---------------------------+-----------+
| t/instant_null            | 297795584 |
| t/instant_notnull         | 297795584 |
| t/instant_null_default    | 297795584 |
| t/instant_notnull_default | 297795584 |
| t/inplace_null            | 335544320 |
| t/inplace_notnull         | 419430400 |
| t/inplace_null_default    | 419430400 |
| t/inplace_notnull_default | 419430400 |
+---------------------------+-----------+

全行UPDATEする

UPDATE instant_null SET col=99999;
Query OK, 8388608 rows affected (4 min 9.00 sec)
UPDATE inplace_null SET col=99999;
Query OK, 8388608 rows affected (6 min 26.18 sec)
UPDATE instant_notnull SET col=99999;
Query OK, 8388608 rows affected (8 min 41.67 sec)
UPDATE inplace_notnull SET col=99999;
Query OK, 8388608 rows affected (1 min 3.24 sec)
UPDATE instant_null_default SET col=99999;
Query OK, 8388608 rows affected (7 min 37.58 sec)
UPDATE inplace_null_default SET col=99999;
Query OK, 8388608 rows affected (1 min 4.49 sec)
UPDATE instant_notnull_default SET col=99999;
Query OK, 8388608 rows affected (6 min 21.13 sec)
UPDATE inplace_notnull_default SET col=99999;
Query OK, 8388608 rows affected (1 min 7.99 sec)

サイズ

mysql> select NAME,FILE_SIZE from information_schema.INNODB_TABLESPACES where NAME like '%t/instant%' or  NAME like '%t/inplace%';
+---------------------------+-----------+
| NAME                      | FILE_SIZE |
+---------------------------+-----------+
| t/instant_null            | 666894336 |
| t/instant_notnull         | 666894336 |
| t/instant_null_default    | 666894336 |
| t/instant_notnull_default | 666894336 |
| t/inplace_null            | 666894336 |
| t/inplace_notnull         | 419430400 |
| t/inplace_null_default    | 419430400 |
| t/inplace_notnull_default | 419430400 |
+---------------------------+-----------+

まとめ

table	元々のサイズ	Add col実行時間	Add col後サイズ	UPDATE実行時間	UPDATE後サイズ
instant_null	297,795,584	0.01 sec	297,795,584	4 min 9.00 sec	666,894,336
inplace_null	297,795,584	8.49 sec	335,544,320	6 min 26.18 sec	666,894,336
instant_notnull	297,795,584	0.01 sec	297,795,584	8 min 41.67 sec	666,894,336
inplace_notnull	297,795,584	9.09 sec	419,430,400	1 min 3.24 sec	419,430,400
instant_null_default	297,795,584	0.01 sec	297,795,584	7 min 37.58 sec	666,894,336
inplace_null_default	297,795,584	9.37 sec	419,430,400	1 min 4.49 sec	419,430,400
instant_notnull_default	297,795,584	0.01 sec	297,795,584	6 min 21.13 sec	666,894,336
inplace_notnull_default	297,795,584	9.05 sec	419,430,400	1 min 7.99 sec	419,430,400

instantはAdd col後サイズは増えない。metadataだけ変えるっていうもんね。
inplaceはdefault値があれば、Add col後サイズとUPDATE後サイズが変わらない。ただし、defaultなしのNULLABLEのカラムは別。
UPDATE実行時間は1回しかとってないので誤差はあると思うけど、inplaceのdefault値ありが3倍ぐらい早い。
基本的にはUPDATE後サイズはinstantのほうが大きくなる。
inplaceのdefaultなしのNULLABLEはUPDATE後サイズはinstantと同じ値になった。
inplaceのdefaultなしのNULLABLEとinstantの更新処理はほぼ同じ動きなんだろうなと思った。

参考

https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/
https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-tables-table.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-columns-table.html

kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

Instant Add Columnして全行UPDATEしてみる

Instant Add Columnで追加したカラムを全行updateする

テーブル作成

データの状態

bigint typeをAdd columnする

サイズ

全行UPDATEする

サイズ

まとめ

参考