This feature can use DDL operations to alter ultra big tables in online businesses within seconds.
The instant DDL feature can quickly modify columns in big tables while avoiding data replication. This feature does not replicate the data or use disk capacity or I/O, and can implement changes within seconds during peak hours.
Instant DDL supports the following operations:
INSTANT ADD COLUMN operation description
- INSTANT ADD COLUMN syntax
algorithm=instant clause to
ALTER TABLE to add a column as follows:
ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT;
innodb_alter_table_default_algorithm parameter is added, which can be set to
This parameter is
inplace by default and can be configured to adjust the default algorithm of
ALTER TABLE as follows:
If no algorithm is specified, the default algorithm configured by this parameter will be used for
ALTER TABLE operations.
Restrictions on INSTANT ADD COLUMN
- A statement can contain only column addition operations.
- A new column will be added to the end, and column order cannot be changed.
- INSTANT ADD COLUMN is not supported in tables whose row format is COMPRESSED.
- INSTANT ADD COLUMN is not supported in tables with a full-text index.
- INSTANT ADD COLUMN is not supported for temp tables.
INSTANT MODIFY COLUMN operation description
- Its usage is similar to that of INSTANT ADD COLUMN. You can set
innodb_alter_table_default_algorithm=instant or specify the
ALGORITHM=instant keyword when modifying a column.
ALTER TABLE t1 MODIFY COLUMN c BIGINT, ALGORITHM=INSTANT;
cdb_instant_modify_column_enabled switch parameter is added, and the above parameters can take effect only after the switch is on. If the switch is off, the INSTANT MODIFY COLUMN feature will also be disabled.
After the switch is turned off, tables modified with INSTANT MODIFY COLUMN can be used normally.
Restrictions on INSTANT MODIFY COLUMN
- INSTANT MODIFY COLUMN supports modifying only the column type. It supports modifying the
default attribute of fields but not the
- Modification is supported only for certain types, and the length can be increased only. Currently, conversions between
varbinary, and among
bigint are supported.
- One column can be modified with INSTANT MODIFY COLUMN only once, and multiple columns can be modified with it at the same time. After a column is added/modified with INSTANT ADD/MODIFY COLUMN for the first time, it can be modified only in the non-instant manner.
- You can run INSTANT ADD COLUMNS and INSTANT MODIFY COLUMNS separately. You can run INSTANT ADD COLUMNS first and then run INSTANT MODIFY COLUMNS or vice versa. However, you cannot perform INSTANT MODIFY COLUMN on a column that is added with INSTANT ADD COLUMN.
- You cannot modify the column name and column type at the same time. Instead, you can modify the column name first and then column type.
- INSTANT MODIFY COLUMN does not support import/export and index column modification.
- INSTANT MODIFY COLUMN does not support encryption, storage, and redundant format.
- INSTANT ADD COLUMN and INSTANT MODIFY COLUMN are performed on the same table with about 5 million data records (12 GB). As can be seen, normal column addition and modification take 2 and 21 minutes respectively, while INSTANT ADD COLUMN and INSTANT MODIFY COLUMN can be done almost instantly.