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  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
... zobacz całą notatkę