Understanding MySQL 8.0 InnoDB DDL Operations and Their Cost Categories

Hello everyone, this episode discusses MySQL DDL with the background of MySQL 8.0+ InnoDB.

DDL (Data Definition Language) defines the structure, relationships, and permissions of data in a database, such as CREATE, ALTER, DROP, etc.

We focus on the question: What actually happens when MySQL 8.0 (using the InnoDB storage engine) modifies a table structure?

DDL and Table Structure

Since DDL changes the table structure, what does the table structure look like in the InnoDB engine? Logically, data in an InnoDB table is stored in primary‑key (clustered index) order, with each row placed sequentially. Physically, the data is organized as a B+‑tree.

Adding a column requires rearranging every row, which means rebuilding the entire table—a costly operation.

DDL Cost Classification

MySQL 8.0 classifies DDL into five dimensions:

Instant – the change completes "immediately".

In‑Place – the change is performed by InnoDB without using the redo log, saving overhead.

Rebuild Table – the change rebuilds the clustered index, usually needed when data changes.

Permits Concurrent DML – the change allows other DML operations on the same table during execution, affecting whether the operation blocks business for a long time.

Only Modifies Metadata – the change only updates metadata without touching the data.

Examples of Each Cost Level

Instant (lowest cost) : Changing a column's default value only updates metadata.

ALTER TABLE `t1` ALTER COLUMN `c1` SET DEFAULT '1';

Low‑cost (metadata only) : Dropping a secondary index marks it as deleted without physically removing it.

DROP INDEX `idx1`;

In‑Place (moderate cost) : Adding a secondary index requires creating the index structure and maintaining a DDL‑period change log for concurrent DML.

ALTER TABLE `t1` ADD INDEX `idx1`(`name`(10)) ASC;

Rebuild Table (high cost) : Dropping a column forces InnoDB to rebuild the clustered index, creating a temporary table and replaying DML changes.

DROP COLUMN `c1`;

Very High Cost : Modifying a column’s type cannot be done In‑Place; MySQL must copy the whole table at the server level, record redo logs, and block concurrent DML.

MODIFY COLUMN `c1` INTEGER;

Operational Recommendations

Explicitly specify ALGORITHM in DDL, trying from low‑cost (INSTANT) to high‑cost (COPY); MySQL will error if the chosen algorithm is not applicable, preventing accidental table rebuilds.

In older MySQL versions all DDL required table rebuilds, so multiple changes were combined into a single statement. In MySQL 8.0, the highest‑cost algorithm among combined changes determines the overall cost, so you should separate low‑cost changes when possible.

You can choose lock types and DDL types; for example, deleting a column defaults to In‑Place Rebuild with lock level NONE (allowing concurrent reads/writes). If the workload can tolerate blocking writes, setting the lock to SHARED can speed up the operation.

Thought Questions

How does the Instant algorithm achieve "instant" column addition?

Is Instant truly non‑blocking and "instant" for all workloads?

For In‑Place Rebuild, how does MySQL keep the rebuild process from interfering with concurrent DML?

These questions will be explored in upcoming articles of the series.

Community updates and links to other MySQL deep‑dive articles follow.