- 14.13.1 Overview of Online DDL
- 14.13.2 Performance and Concurrency Considerations for Online DDL
- 14.13.3 SQL Syntax for Online DDL
- 14.13.4 Combining or Separating DDL Statements
- 14.13.5 Examples of Online DDL
- 14.13.6 Implementation Details of Online DDL
- 14.13.7 How Crash Recovery Works with Online DDL
- 14.13.8 Online DDL for Partitioned InnoDB Tables
- 14.13.9 Limitations of Online DDL
Improved responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours is not practical.
The ability to adjust the balance between performance and concurrency during a DDL operations using the
LOCK=EXCLUSIVEblocks access to the table entirely.
LOCK=SHAREDallows queries but not DML.
LOCK=NONEallows full query and DML access to the table.
LOCK=DEFAULTor omitting the
LOCKclause permits as much concurrency as possible depending on the type of DDL operation.
Avoidance of disk space usage and I/O overhead associated with copying the table and reconstructing secondary indexes.
The MySQL Cluster
NDB storage engine
also supports online table schema changes, but uses its own syntax
that is not compatible with the syntax used for
InnoDB online operations. For more information,
see Section 184.108.40.206, “ALTER TABLE Online Operations in MySQL Cluster”.