You can perform several kinds of
online DDL operations on
InnoDB tables: that is, allowing
DML operations and
queries on the table while the
DDL is in progress, performing the
operation “in-place” without rebuilding the entire
table, or both. This enhancement has the following benefits:
It improves responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours whenever you modify its indexes or column definitions is not practical.
It lets you adjust the balance between performance and
concurrency during the DDL operation, by choosing whether to
block access to the table entirely
LOCK=EXCLUSIVE clause), allow queries but
not DML (
LOCK=SHARED clause), or allow full
query and DML access to the table (
clause). When you omit the
LOCK clause or
LOCK=DEFAULT, MySQL allows as much
concurrency as possible depending on the type of operation.
By doing the changes in-place where possible, rather than creating a new copy of the table, it avoids temporary increases in disk space usage and the I/O overhead of copying the table and reconstructing all the secondary indexes.