SQL Syntax for Online DDL

SQL Syntax for Online DDL
Typically, you do not need to do anything special to enable online DDL when using the ALTER TABLE
statement for InnoDB tables. See Table 5.9, “Summary of Online Status for DDL Operations” for the
kinds of DDL operations that can be performed in-place, allowing concurrent DML, or both. Some
variations require particular combinations of configuration settings or ALTER TABLE clauses.
You can control the various aspects of a particular online DDL operation by using the LOCK and
ALGORITHM clauses of the ALTER TABLE statement. These clauses come at the end of the statement,
separated from the table and column specifications by commas. The LOCK clause is useful for finetuning
the degree of concurrent access to the table. The ALGORITHM clause is primarily intended for
performance comparisons and as a fallback to the older table-copying behavior in case you encounter
any issues with existing DDL code. For example:
• To avoid accidentally making the table unavailable for reads, writes, or both, you could specify
a clause on the ALTER TABLE statement such as LOCK=NONE (allow both reads and writes) or
LOCK=SHARED (allow reads). The operation halts immediately if the requested level of concurrency is
not available.
• To compare performance, you could run one statement with ALGORITHM=INPLACE and another with
ALGORITHM=COPY, as an alternative to setting the old_alter_table [531] configuration option.
• To avoid the chance of tying up the server by running an ALTER TABLE that copied the table, you
could include ALGORITHM=INPLACE so the statement halts immediately if it cannot use the in-place
... zobacz całą notatkę