INSTANT ADD COLUMN
Context
Generally, DDL operations on large tables have great impact on workloads. They need to be performed during off-peak hours. MySQL 5.7 supports the native DDL tool COPY and INPLACE algorithms and the open-source DDL tool gh-ost, reducing blocked DML operations during DDL execution. But it still takes a long time to perform DDL operations on large tables.
INSTANT ADD COLUMN eliminates the need to rebuild the entire table when adding columns. It only needs to record the basic information about the new columns in the table metadata. However, only a limited number of DDL operations are supported.
Syntax
If ALGORITHM=INSTANT is added to the end of the ALTER statement, the INSTANT algorithm is used. Here is an example:
ALTER TABLE *tbl_name* ADD COLUMN *column_name* *column_definition*, ALGORITHM=INSTANT;
Constraints
This algorithm can only be used when you:
- Add, delete, or rename columns (for versions later than MySQL 8.0.28) in certain scenarios.
- Set or delete the default value of a column.
- Modify the definition of the ENUM or SET column.
- Change the index type (B-Tree | hash).
- Add or delete a virtual column.
- Rename a table.
Constraints on adding or deleting columns:
- An ALTER TABLE statement cannot combine the addition of a column with other actions that do not support the INSTANT algorithm.
- New columns are placed at the end and the column sequence cannot be changed. (In versions later than MySQL 8.0.29, columns can be added to any position.)
- Columns cannot be quickly added to or deleted from a table whose row format is COMPRESSED.
- Columns cannot be quickly added to or deleted from a table that has a full-text index.
- Columns cannot be quickly added to or deleted from a temporary table.
Constraints on renaming columns:
- Columns referenced by other tables cannot be renamed.
- The operation of renaming a column and the operation of generating or deleting a virtual column cannot be in the same statement.
Constraints on modifying the ENUM or SET column.
- The storage space occupied by the ENUM or SET column data type cannot be changed.
Constraints on adding or deleting virtual columns:
- Virtual columns cannot be added to or deleted from partitioned tables.
New Data Dictionary Information
When INSTANT ADD COLUMN is executed, MySQL saves the number of fields before INSTANT ADD COLUMN is executed for the first time and the default value of the column added each time to the se_private_data field in the tables system table.
- dd::Table::se_private_data::instant_col: indicates the number of columns in the table before INSTANT ADD COLUMN is executed for the first time.
- dd::Column::se_private_data::default_null: indicates whether the default value of the instant column is NULL.
- dd::Column::se_private_data::default: indicates the default value stored when the default value of the instant column is not NULL.
Importing Data Dictionary
When MySQL reads table definitions from system tables, it loads instant column information to the InnoDB table object dict_table_t and index object dict_index_t.
- dict_table_t::n_instant_cols: indicates the number of non-virtual fields (including system columns) before INSTANT ADD COLUMN is executed for the first time.
- dict_index_t::instant_cols: indicates whether there is an instant column.
- dict_index_t::n_instant_nullable: indicates the number of fields that can be null before INSTANT ADD COLUMN is executed for the first time.
- dict_col_t::instant_default: indicates the default value and length of the instant column.
Record Format
To support INSTANT ADD COLUMN, a new record format is introduced for the COMPACT and DYNAMIC types to record the number of fields.
- If INSTANT ADD COLUMN has not been performed, the row record format of the table remains unchanged.
- If INSTANT ADD COLUMN has been performed, a special flag is set for each new record, and the number of fields is stored in the records.
INSTANT_FLAG uses a bit in info bits. If a record is inserted after the first execution of INSTANT ADD COLUMN, the flag is set to 1.

Query
The query process remains unchanged. For instant columns that are not stored in records, use the default value.
Insertion
After INSTANT ADD COLUMN is executed, the format of the old data does not change and the newly inserted data is stored in the new format. If a bit in the info bits of a new record is set to REC_INFO_INSTANT_FLAG, the record is created after INSTANT ADD COLUMN is executed.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot