MySQL5.6 Online DDL 是否锁表、rebuild表、inplace的说明

Operation	In-Place?	Rebuilds Table?	Permits Concurrent DML?	Only Modifies Metadata?	Notes
`CREATE INDEX`, `ADD INDEX`	Yes*	No*	Yes	No	Restrictions apply for `FULLTEXT` indexes; see next row.
`ADD FULLTEXT INDEX`	Yes*	No*	No	No	Adding the first `FULLTEXT` index rebuilds the table if there is no user-defined `FTS_DOC_ID` column. Subsequent `FULLTEXT` indexes may be added on the same table without rebuilding the table.
`DROP INDEX`	Yes	No	Yes	Yes	Only modifies table metadata.
`OPTIMIZE TABLE`	Yes*	Yes	Yes	No	Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables with`FULLTEXT` indexes.
Set column default value	Yes	No	Yes	Yes	Only modifies table metadata.
Change auto-incrementvalue	Yes	No	Yes	No*	Modifies a value stored in memory, not the data file.
Add foreign key constraint	Yes*	No	Yes	Yes	The `INPLACE` algorithm is supported when `foreign_key_checks` is disabled. Otherwise, only the`COPY` algorithm is supported.
Drop foreign key constraint	Yes	No	Yes	Yes	`foreign_key_checks` can be enabled or disabled.
Rename column	Yes	No	Yes*	Yes	To permit concurrent DML, keep the same data type and only change the column name.
Add column	Yes	Yes	Yes*	No	Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation.
Drop column	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Reorder columns	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change `ROW_FORMAT`property	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change `KEY_BLOCK_SIZE`property	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Make column `NULL`	Yes	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.
Make column `NOT NULL`	Yes*	Yes*	Yes	No	Rebuilds the table in place. `STRICT_ALL_TABLES` or `STRICT_TRANS_TABLES` `SQL_MODE` is required for the operation to succeed. The operation fails if the column contains NULL values. As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See Section 13.1.7, “ALTER TABLE Syntax”. Data is reorganized substantially, making it an expensive operation.
Change column data type	No	Yes	No	No	Only supports `ALGORITHM=COPY`
Add primary key	Yes*	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.`ALGORITHM=INPLACE` is not permitted under certain conditions if columns have to be converted to`NOT NULL`.
Drop primary key and add another	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Drop primary key	No	Yes	No	No	Only `ALGORITHM=COPY` supports dropping a primary key without adding a new one in the same`ALTER TABLE` statement.
Convert character set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Specify character set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Rebuild with `FORCE`option	Yes*	Yes	Yes	No	Uses `ALGORITHM=INPLACE` as of MySQL 5.6.17. `ALGORITHM=INPLACE` is not supported for tables with`FULLTEXT` indexes.
“null” rebuild using`ALTER TABLE ... ENGINE=INNODB`	Yes*	Yes	Yes	No	Uses `ALGORITHM=INPLACE` as of MySQL 5.6.17. `ALGORITHM=INPLACE` is not supported for tables with`FULLTEXT` indexes.
Set `STATS_PERSISTENT`,`STATS_AUTO_RECALC`,`STATS_SAMPLE_PAGES`persistent statisticsoptions	Yes	No	Yes	Yes	Only modifie

Operation

In-Place?

Rebuilds Table?

Permits Concurrent DML?

Only Modifies Metadata?

Notes

CREATE INDEX, ADD INDEX

Yes*

No*

Yes

Restrictions apply for FULLTEXT indexes; see next row.

ADD FULLTEXT INDEX

Yes*

No*

Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Subsequent FULLTEXT indexes may be added on the same table without rebuilding the table.

DROP INDEX

Yes

Yes

Only modifies table metadata.

OPTIMIZE TABLE

Yes*

Yes

Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables withFULLTEXT indexes.

Set column default value

Yes

Yes

Only modifies table metadata.

Change auto-incrementvalue

Yes

No*

Modifies a value stored in memory, not the data file.

Add foreign key constraint

Yes*

Yes

The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only theCOPY algorithm is supported.

Drop foreign key constraint

Yes

foreign_key_checks can be enabled or disabled.

Rename column

Yes

Yes*

Yes

To permit concurrent DML, keep the same data type and only change the column name.

Add column

Yes

Yes*

Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation.

Drop column

Yes

Data is reorganized substantially, making it an expensive operation.

Reorder columns

Yes

Data is reorganized substantially, making it an expensive operation.

Change ROW_FORMATproperty

Yes

Data is reorganized substantially, making it an expensive operation.

Change KEY_BLOCK_SIZEproperty

Yes

Data is reorganized substantially, making it an expensive operation.

Make column NULL

Yes

Yes*

Yes

Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.

Make column NOT NULL

Yes*

Yes

Rebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See Section 13.1.7, “ALTER TABLE Syntax”. Data is reorganized substantially, making it an expensive operation.

Change column data type

Yes

Only supports ALGORITHM=COPY

Add primary key

Yes*

Yes

Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted toNOT NULL.

Drop primary key and add another

Yes

Data is reorganized substantially, making it an expensive operation.

Drop primary key

Yes

Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the sameALTER TABLE statement.

Convert character set

Yes*

Rebuilds the table if the new character encoding is different.

Specify character set

Yes*

Rebuilds the table if the new character encoding is different.

Rebuild with FORCEoption

Yes*

Yes

Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables withFULLTEXT indexes.

“null” rebuild usingALTER TABLE ... ENGINE=INNODB

Yes*

Yes

Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables withFULLTEXT indexes.

Set STATS_PERSISTENT,STATS_AUTO_RECALC,STATS_SAMPLE_PAGESpersistent statisticsoptions

Yes

Only modifie

相关阅读