Use Cases
This feature can use DDL operations to alter ultra big tables in online businesses within seconds.
Overview
The instant DDL feature can quickly modify columns in big tables while avoiding data replication. This feature does not replicate the data or consume disk capacity or I/O, and can implement changes within seconds during peak hours.
Supported Versions
MySQL 5.7 with kernel minor version 2.1.3 or later.
MySQL 8.0 with kernel minor version 3.1.1 or later.
Instructions
Instant DDL supports the ADD COLUMN operation.
Description of INSTANT ADD COLUMN
MySQL 8.0 kernel minor version from 3.1.1 to 3.1.10 operation instructions
MySQL 8.0 kernel minor version 3.1.12 and later operation instructions
Syntax of INSTANT ADD COLUMN.
To add a column, you can use the following statements by including the new 'algorithm = instant' clause:
ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT;
Added parameter innodb_alter_table_default_algorithm, which can be set to inplace or instant, with the default value being instant.
Column addition can be done with the following statement:
ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000;
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 with the row format being COMPRESSED.
INSTANT ADD COLUMN is not supported in tables with a full-text index.
INSTANT ADD COLUMN is not supported for temp tables.