Implementation Details of Online DDL

Implementation Details of Online DDL
Each ALTER TABLE operation for an InnoDB table is governed by several aspects:
• Whether there is any change to the physical representation of the table, or whether it purely a
change to metadata that can be done without touching the table itself.
• Whether the volume of data in the table stays the same, increases, or decreases.
• Whether a change in table data involves the clustered index, secondary indexes, or both.
• Whether there are any foreign key relationships between the table being altered and some other
table. The mechanics differ depending on whether the foreign_key_checks [497] configuration
option is enabled or disabled.
• Whether the table is partitioned. Partitioning clauses of ALTER TABLE are turned into low-level
operations involving one or more tables, and those operations follow the regular rules for online DDL.
• Whether the table data must be copied, whether the table can be reorganized “in-place”, or a
combination of both.
• Whether the table contains any auto-increment columns.
• What degree of locking is required, either by the nature of the underlying database operations, or a
LOCK clause that you specify in the ALTER TABLE statement.
This section explains how these factors affect the different kinds of ALTER TABLE operations on
InnoDB tables.
... zobacz całą notatkę