Historically, many DDL operations
InnoDB tables were expensive. Many
ALTER TABLE operations worked by
creating a new, empty table defined with the requested table
options and indexes, then copying the existing rows to the new
table one-by-one, updating the indexes as the rows were inserted.
After all rows from the original table were copied, the old table
was dropped and the copy was renamed with the name of the original
MySQL 5.5, and MySQL 5.1 with the InnoDB Plugin, optimized
CREATE INDEX and
to avoid the table-copying behavior. That feature was known as
Creation. MySQL 5.6 enhances many other types of
ALTER TABLE operations to avoid
copying the table. Another enhancement allows
SELECT queries and
(DML) statements to proceed while
the table is being altered. This combination of features is now
known as online DDL.
This mechanism also means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without any secondary indexes, then adding the secondary indexes after the data is loaded.
Although no syntax changes are required in the
CREATE INDEX or
DROP INDEX commands, some factors
affect the performance, space usage, and semantics of this
operation (see Section 14.13.9, “Limitations of Online DDL”).
The online DDL enhancements in MySQL 5.6 improve many DDL
operations that formerly required a table copy or blocked DML
operations on the table, or both.
Table 14.8, “Summary of Online Status for DDL Operations” shows the
variations of the
statement and shows how the online DDL feature applies to each.
With the exception of
partitioning clauses, online DDL operations for partitioned
InnoDB tables follow the same rules that apply
InnoDB tables. For more information,
see Section 14.13.8, “Online DDL for Partitioned InnoDB Tables”.
The “In-Place?” column shows which operations allow the
ALGORITHM=INPLACEclause; the preferred value is “Yes”.
The “Copies Table?” column shows which operations are able to avoid the expensive table-copying operation; the preferred value is “No”. This column is mostly the reverse of the “In-Place?” column, except that a few operations allow
ALGORITHM=INPLACEbut still involve some amount of table copying.
The “Allows Concurrent DML?” column shows which operations can be performed fully online; the preferred value is “Yes”. You can specify
LOCK=NONEto assert that full concurrency is allowed during the DDL, but MySQL automatically allows this level of concurrency when possible. When concurrent DML is allowed, concurrent queries are also always allowed.
The “Allows Concurrent Queries?” column shows which DDL operations allow queries on the table while the operation is in progress; the preferred value is “Yes”. Concurrent query is allowed during all online DDL operations. It is shown with “Yes” listed for all cells, for reference purposes. You can specify
LOCK=SHAREDto assert that concurrent queries are allowed during the DDL, but MySQL automatically allows this level of concurrency when possible.
The “Notes” column explains any exceptions to the “Yes/No” values of the other columns, such as when the answer depends on the setting of a configuration option or some other clause in the DDL statement. The values “Yes*” and “No*” indicate that an answer depends on these additional notes.
Table 14.8 Summary of Online Status for DDL Operations
|Operation||In-Place?||Copies Table?||Allows Concurrent DML?||Allows Concurrent Query?||Notes|
|Yes*||No*||Yes||Yes||Some restrictions for |
|Yes||No*||No||Yes||Creating the first |
|Yes||No||Yes||Yes||Only modifies table metadata.|
|Set default value for a column||Yes||No||Yes||Yes||Only modifies table metadata.|
|Change auto-increment value for a column||Yes||No||Yes||Yes||Only modifies table metadata.|
|Add a foreign key constraint||Yes*||No*||Yes||Yes||To avoid copying the table, disable
|Drop a foreign key constraint||Yes||No||Yes||Yes||The |
|Rename a column||Yes*||No*||Yes*||Yes||To allow concurrent DML, keep the same data type and only change the column name.|
|Add a column||Yes*||Yes*||Yes*||Yes||Concurrent DML is not allowed when adding an
column. Although |
|Drop a column||Yes||Yes*||Yes||Yes||Although |
|Reorder columns||Yes||Yes||Yes||Yes||Although |
|Change ||Yes||Yes||Yes||Yes||Although |
|Change ||Yes||Yes||Yes||Yes||Although |
|Make column ||Yes||Yes||Yes||Yes||Although |
|Make column ||Yes*||Yes||Yes||Yes|
|Change data type of column||No||Yes||No||Yes|
|Add primary key||Yes*||Yes||Yes||Yes||Although |
|Drop primary key and add another||Yes||Yes||Yes||Yes|
|Drop primary key||No||Yes||No||Yes||Restrictions apply when you drop a primary key without adding a new one
in the same |
|Convert character set||No||Yes||No||Yes||Rebuilds the table if the new character encoding is different.|
|Specify character set||No||Yes||No||Yes||Rebuilds the table if the new character encoding is different.|
|Rebuild with ||Yes||Yes||Yes||Yes||Uses |
|Rebuild with “null” ||Yes||Yes||Yes||Yes||Uses |
|Set table-level persistent
(||Yes||No||Yes||Yes||Only modifies table metadata.|
The following sections shows the basic syntax, and usage notes related to online DDL, for each of the major operations that can be performed with concurrent DML, in-place, or both:
Creating and dropping secondary indexes on
InnoDB tables skips the table-copying behavior,
the same as in MySQL 5.5 and MySQL 5.1 with the
In MySQL 5.6 and higher, the table remains available for read and
write operations while the index is being created or dropped. The
CREATE INDEX or
DROP INDEX statement only finishes
after all transactions that are accessing the table are completed,
so that the initial state of the index reflects the most recent
contents of the table. Previously, modifying the table while an
index is being created or dropped typically resulted in a
deadlock that cancelled the
DELETE statement on the table.
Set a default value for a column:
Changing the auto-increment value for a column:
Especially in a distributed system using replication or sharding, you sometimes reset the auto-increment counter for a table to a specific value. The next row inserted into the table uses the specified value for its auto-increment column. You might also use this technique in a data warehousing environment where you periodically empty all the tables and reload them, and you can restart the auto-increment sequence from 1.
Renaming a column:
When you keep the same data type and
[NOT] NULLattribute, only changing the column name, this operation can always be performed online.
You can also rename a column that is part of a foreign key constraint. The foreign key definition is automatically updated to use the new column name. Renaming a column participating in a foreign key only works with the in-place mode of
ALTER TABLE. If you use the
ALGORITHM=COPYclause, or some other condition causes the command to use
ALGORITHM=COPYbehind the scenes, the
ALTER TABLEstatement will fail.
Adding or dropping a foreign key constraint:
referential_actions; ALTER TABLE
tblDROP FOREIGN KEY
If you do not know the names of the foreign key constraints on a particular table, issue the following statement and find the constraint name in the
CONSTRAINTclause for each foreign key:
show create table
Or, query the
information_schema.table_constraintstable and use the
constraint_typecolumns to identify the foreign key names.
You can also drop a foreign key and its associated index in a single statement:
tableDROP FOREIGN KEY
constraint, DROP INDEX
If foreign keys are
already present in the table being altered (that is, it is a
child table containing any
FOREIGN KEY ... REFERENCE clauses), additional
restrictions apply to online DDL operations, even those not
directly involving the foreign key columns:
ALTER TABLEon the child table could wait for another transaction to commit, if a change to the parent table caused associated changes in the child table through an
ON DELETEclause using the
In the same way, if a table is the parent table in a foreign key relationship, even though it does not contain any
FOREIGN KEYclauses, it could wait for the
ALTER TABLEto complete if an
DELETEstatement caused an
ON DELETEaction in the child table.
ALTER TABLE operation run with
ALGORITHM=COPY clause prevents concurrent
DML operations. Concurrent queries are still allowed. That is, a
table-copying operation always includes at least the concurrency
LOCK=SHARED (allow queries but
not DML). You can further restrict concurrency for such operations
LOCK=EXCLUSIVE, which prevents
DML and queries.
ALTER TABLE operations
allow concurrent DML but still require a table copy. However, the
table copy for these operations is faster than it was in MySQL 5.5
Adding, dropping, or reordering columns.
Adding or dropping a primary key.
KEY_BLOCK_SIZEproperties for a table.
Changing the nullable status for a column.
Rebuilding a table with the
Rebuilding a table using a “null”
ALTER TABLE ... ENGINE=INNODBstatement
As your database schema evolves with new columns, data types,
constraints, indexes, and so on, keep your
CREATE TABLE statements up to date
with the latest table definitions. Even with the performance
improvements of online DDL, it is more efficient to create stable
database structures at the beginning, rather than creating part of
the schema and then issuing
TABLE statements afterward.
The main exception to this guideline is for secondary indexes on tables with large numbers of rows. It is typically most efficient to create the table with all details specified except the secondary indexes, load the data, then create the secondary indexes. You can use the same technique with foreign keys (load the data first, then set up the foreign keys) if you know the initial data is clean and do not need consistency checks during the loading process.
Whatever sequence of
ALTER TABLE, and similar statements
went into putting a table together, you can capture the SQL needed
to reconstruct the current form of the table by issuing the
SHOW CREATE TABLE
\G required for tidy formatting). This output
shows clauses such as numeric precision,
CHARACTER SET that are
sometimes added behind the scenes, which you may want to leave out
when cloning the table on a new system or setting up foreign key
columns with identical type.