MySQL更新表结构操作时，数据库在线状态

操作	就地操作	表复制	是否支持并发	是否支持查询	注释
CREATE INDEX,ADD INDEX	Yes*	No*	Yes	Yes	Some restrictions for FULLTEXT index; see next row. Currently, the operation is not in-place (that is, it copies the table) if the same index being created was also dropped by an earlier clause in the sameALTER TABLE statement.
ADD FULLTEXT INDEX	Yes	No*	No	Yes	Creating the first FULLTEXT index for a table involves a table copy, unless there is a user-suppliedFTS_DOC_ID column. Subsequent FULLTEXT indexes on the same table can be created in-place.
DROP INDEX	Yes	No	Yes	Yes	Modifies .frm file only, not the data file.
OPTIMIZE TABLE	Yes	Yes	Yes	Yes	Uses ALGORITHM=INPLACE as of MySQL 5.6.17.ALGORITHM=COPY is used if old_alter_table=1 ormysqld--skip-new option is enabled. OPTIMIZE TABLE using online DDL (ALGORITHM=INPLACE) is not supported for tables with FULLTEXT indexes.
Set default value for a column	Yes	No	Yes	Yes	Modifies .frm file only, not the data file.
Change auto-increment value for a column	Yes	No	Yes	Yes	Modifies a value stored in memory, not the data file.
Add a foreign key constraint	Yes*	No*	Yes	Yes	To avoid copying the table, disable foreign_key_checks during constraint creation.
Drop a foreign key constraint	Yes	No	Yes	Yes	The foreign_key_checks option can be enabled or disabled.
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 auto-increment column. Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Drop a column	Yes	Yes	Yes	Yes	Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Reorder columns	Yes	Yes	Yes	Yes	Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Change ROW_FORMAT property	Yes	Yes	Yes	Yes	Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Change KEY_BLOCK_SIZE property	Yes	Yes	Yes	Yes	Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Make column NULL	Yes	Yes	Yes	Yes	Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Make column NOT NULL	Yes*	Yes	Yes	Yes	When SQL_MODE includesstrict_all_tables or strict_all_tables, the operation fails if the column contains any nulls. AlthoughALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Change data type of column	No	Yes	No	Yes
Add primary key	Yes*	Yes	Yes	Yes	Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.ALGORITHM=INPLACE is not allowed under certain conditions if columns have to be converted toNOT NULL. See Example 14.9, “Creating and Dropping the Primary Key”.
Drop primary key and add another	Yes	Yes	Yes	Yes	ALGORITHM=INPLACE is only allowed when you add a new primary key in the sameALTER TABLE; the data is reorganized substantially, so it is still an expensive operation.
Drop primary key	No	Yes	No	Yes	Restrictions apply when you drop a primary key primary key without adding a new one in the sameALTER TABLE statement.
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 FORCE option	Yes	Yes	Yes	Yes	Uses ALGORITHM=INPLACE as of MySQL 5.6.17.ALGORITHM=COPY is used if old_alter_table=1 ormysqld--skip-new option is enabled. Table rebuild using online DDL (ALGORITHM=INPLACE) is not supported for tables with FULLTEXT indexes.
Rebuild with “null”ALTER TABLE ... ENGINE=INNODB	Yes	Yes	Yes	Yes	Uses ALGORITHM=INPLACE as of MySQL 5.6.17.ALGORITHM=COPY is used if old_alter_table=1 ormysqld--skip-new option is enabled. Table rebuild using online DDL (ALGORITHM=INPLACE) is not supported for tables with FULLTEXT indexes.
Set table-level persistent statistics options (STATS_PERSISTENT,STATS_AUTO_RECALC STATS_SAMPLE_PAGES)	Yes	No	Yes	Yes	Modifies .frm file only, not the data file.