Online DDL 是否锁表、是否rebuild表、inplace或copy算法的说明：

原文：https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html?spm=5176.100239.blogcont64664.13.SpL8lH

OperationIn-Place?Rebuilds Table?Permits Concurrent DML?Only Modifies Metadata?NotesCREATE INDEX,ADD INDEXYes*No*YesNoRestrictions apply forFULLTEXTindexes; see next row.ADD FULLTEXT INDEXYes*No*NoNoAdding the firstFULLTEXTindex rebuilds the table if there is no user-definedFTS_DOC_IDcolumn. SubsequentFULLTEXTindexes may be added on the same table without rebuilding the table.DROP INDEXYesNoYesYesOnly modifies table metadata.OPTIMIZE TABLEYes*YesYesNoPerformed in-place as of MySQL 5.6.17. In-place operation is not supported for tables withFULLTEXTindexes.Set column default valueYesNoYesYesOnly modifies table metadata.Changeauto-incrementvalueYesNoYesNo*Modifies a value stored in memory, not the data file.Addforeign key constraintYes*NoYesYesTheINPLACEalgorithm is supported whenforeign_key_checksis disabled. Otherwise, only theCOPYalgorithm is supported.Dropforeign key constraintYesNoYesYesforeign_key_checkscan be enabled or disabled.Rename columnYesNoYes*YesTo permit concurrent DML, keep the same data type and only change the column name.Add columnYesYesYes*NoConcurrent DML is not permitted when adding anauto-incrementcolumn. Data is reorganized substantially, making it an expensive operation.Drop columnYesYesYesNoData is reorganized substantially, making it an expensive operation.Reorder columnsYesYesYesNoData is reorganized substantially, making it an expensive operation.ChangeROW_FORMATpropertyYesYesYesNoData is reorganized substantially, making it an expensive operation.ChangeKEY_BLOCK_SIZEpropertyYesYesYesNoData is reorganized substantially, making it an expensive operation.Make columnNULLYesYes*YesNoRebuilds the table in place. Data is reorganized substantially, making it an expensive operation.Make columnNOT NULLYes*Yes*YesNoRebuilds the table in place.STRICT_ALL_TABLESorSTRICT_TRANS_TABLESSQL_MODEis 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. SeeSection13.1.7, “ALTER TABLE Syntax”. Data is reorganized substantially, making it an expensive operation.Change column data typeNoYesNoNoOnly supportsALGORITHM=COPYAddprimary keyYes*Yes*YesNoRebuilds the table in place. Data is reorganized substantially, making it an expensive operation.ALGORITHM=INPLACEis not permitted under certain conditions if columns have to be converted toNOT NULL.Dropprimary keyand add anotherYesYesYesNoData is reorganized substantially, making it an expensive operation.Dropprimary keyNoYesNoNoOnlyALGORITHM=COPYsupports dropping a primary key without adding a new one in the sameALTER TABLEstatement.Convert character setNoYes*NoNoRebuilds the table if the new character encoding is different.Specify character setNoYes*NoNoRebuilds the table if the new character encoding is different.Rebuild withFORCEoptionYes*YesYesNoUsesALGORITHM=INPLACEas of MySQL 5.6.17.ALGORITHM=INPLACEis not supported for tables withFULLTEXTindexes.“null”rebuild usingALTER TABLE ... ENGINE=INNODBYes*YesYesNoUsesALGORITHM=INPLACEas of MySQL 5.6.17.ALGORITHM=INPLACEis not supported for tables withFULLTEXTindexes.SetSTATS_PERSISTENT,STATS_AUTO_RECALC,STATS_SAMPLE_PAGESpersistent statisticsoptionsYesNoYesYesOnly modifie

其余可参考文章：https://yq.aliyun.com/articles/64664