Today I learned a MySQL term: online DDL.
The online DDL lets you alter tables efficiently, without making tables unavailable during the entire operation and exclusive table locks. The behavior of a DDL operation can be specified using the
LOCK clauses. You don't have to get overwhelmed by the mysterious terms. Actually, you may have used it before without knowing because MySQL will try to use it by default when you execute ordinary alter table statements.
By placing the
ALGORITHM clause to a DDL, you can tell MySQL how to process the operation. With the
COPY algorithm, the operation is performed on a copy of the original table.
INPLACE avoids copying table data and as a result, the operation will be more efficient.
INSTANT could be used for only modifying some metadata. Some of these algorithms have limitations. For example,
INSTANT do not support dropping primary keys, and
INSTANT does not support renaming columns.
LOCK clause lets you specify the restrictive level of the locking. The possible values are
EXCLUSIVE. In some operations like adding an auto-increment column,
SHARED or more restrictions are required.
When running a DDL without these clauses, MySQL would try to use
INPLACE for the algorithm with as little locking as possible. Even so, occasionally, you would want to specify them explicitly to avoid unexpected behavior, especially in production environments. Let's say the table that is going to be altered is large and the operation must be done as soon as possible without long-lived exclusive locks. So you want to avoid either expensive copy operations or lengthy locks. Although MySQL tries to run the operations in the least expensive way by default, you will want to make sure that. In that case, passing
LOCK clauses assure you that the operation will never run in an unexpected way.
ALTER TABLE users CHANGE sex gender integer, ALGORITHM=INPLACE, LOCK=NONE;
If the DDL cannot be executed with in-place operations without lock, MySQL will stop without executing it and tell you the reason.
mysql> ALTER TABLE users CHANGE sex gender integer, ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.