Analysis of the Internal Structure and Performance Impact of the MySQL 8.0 Instant DDL Algorithm

MySQL 8.0 introduced the Instant DDL algorithm, allowing columns to be added and dropped instantly regardless of table size. The initial version (8.0.12) only supported adding columns at the end, but starting from 8.0.29, the concept of ROW_VERSION was introduced to allow adding or dropping columns regardless of position.

Internal Working Principle of Instant DDL

Instant DDL works by modifying only metadata without changing physical data. In this process, a ROW_VERSION value is recorded for each row, and VERSION_ADDED and VERSION_DROPPED information is stored in the table metadata. When executing a SELECT query, MySQL compares this metadata to resolve discrepancies between the row's actual data and the current table structure. For example, if a column's VERSION_ADDED value is greater than the row's ROW_VERSION, the default value for that column is returned.

Key Limitations and Performance Considerations

There are several important constraints for Instant DDL.

• ROW_VERSION Limit: The ROW_VERSION per table can increase up to a maximum of 64; if exceeded, it switches to the COPY or INPLACE algorithm. Rebuilding the table is required to reset it.
• Unsupported Environments: It cannot be used on tables with FTS (Full-Text-Search) indexes, tables with a ROW_FORMAT of COMPRESSED, or temporary tables.
• Performance Impact: While performance degradation of SELECT queries is a concern due to the complex Fetch logic based on metadata, Kakao's Sysbench test results confirmed that no significant performance degradation occurred in standard configurations.