您的位置: 首页  >  文章  >  MySQL5.6 Online DDL 是否锁表、rebuild表、inplace的说明

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

分类: 文章 • 2022-04-30 18:55:49

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

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 withFULLTEXT 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 theCOPY 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_FORMATproperty Yes Yes Yes No Data is reorganized substantially, making it an expensive operation.
Change KEY_BLOCK_SIZEproperty 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 toNOT 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 sameALTER 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 FORCEoption Yes* Yes Yes No 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 Yes No 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 No Yes Yes Only modifie

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


相关推荐