MySQL的onlineDDL

注：带星号表示有限制条件。

1. 索引操作

Table 15.16 Online DDL Support for Index Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Creating or adding a secondary index	No	Yes	No	Yes	No
Dropping an index	No	Yes	No	Yes	Yes
Renaming an index	No	Yes	No	Yes	Yes
Adding a FULLTEXT index	No	Yes*	No*	No	No
Adding a SPATIAL index	No	Yes	No	No	No
Changing the index type	Yes	Yes	No	Yes	Yes

2. 主键操作

Table 15.17 Online DDL Support for Primary Key Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a primary key	No	Yes*	Yes*	Yes	No
Dropping a primary key	No	No	Yes	No	No
Dropping a primary key and adding another	No	Yes	Yes	Yes	No

3. column操作

Table 15.18 Online DDL Support for Column Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a column	Yes*	Yes	No*	Yes*	Yes
Dropping a column	Yes*	Yes	Yes	Yes	Yes
Renaming a column	Yes*	Yes	No	Yes*	Yes
Reordering columns	No	Yes	Yes	Yes	No
Setting a column default value	Yes	Yes	No	Yes	Yes
Changing the column data type	No	No	Yes	No	No
Extending VARCHAR column size	No	Yes	No	Yes	Yes
Dropping the column default value	Yes	Yes	No	Yes	Yes
Changing the auto-increment value	No	Yes	No	Yes	No*
Making a column NULL	No	Yes	Yes*	Yes	No
Making a column NOT NULL	No	Yes*	Yes*	Yes	No
Modifying the definition of an ENUM or SET column	Yes	Yes	No	Yes	Yes

测试（MySQL 8.0.20，表数据量三千万）：

mysql> alter table history_uint add column username varchar(10) default null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 增加字段瞬间完成

mysql> alter table history_uint modify column username varchar(10) default '';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 修改默认值瞬间完成

mysql> alter table history_uint drop username;
ERROR 2013 (HY000): Lost connection to MySQL server during query
-- 删除字段超时被kill了

4. 生成列操作

Table 15.19 Online DDL Support for Generated Column Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a STORED column	No	No	Yes	No	No
Modifying STORED column order	No	No	Yes	No	No
Dropping a STORED column	No	Yes	Yes	Yes	No
Adding a VIRTUAL column	Yes	Yes	No	Yes	Yes
Modifying VIRTUAL column order	No	No	Yes	No	No
Dropping a VIRTUAL column	Yes	Yes	No	Yes	Yes

5. 表操作

Table 15.21 Online DDL Support for Table Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Changing the ROW_FORMAT	No	Yes	Yes	Yes	No
Changing the KEY_BLOCK_SIZE	No	Yes	Yes	Yes	No
Setting persistent table statistics	No	Yes	No	Yes	Yes
Specifying a character set	No	Yes	Yes*	Yes	No
Converting a character set	No	No	Yes*	No	No
Optimizing a table	No	Yes*	Yes	Yes	No
Rebuilding with the FORCE option	No	Yes*	Yes	Yes	No
Performing a null rebuild	No	Yes*	Yes	Yes	No
Renaming a table	Yes	Yes	No	Yes	Yes

6. 表空间操作

Table 15.22 Online DDL Support for Tablespace Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Renaming a general tablespace	No	Yes	No	Yes	Yes
Enabling or disabling general tablespace encryption	No	Yes	No	Yes	No
Enabling or disabling file-per-table tablespace encryption	No	No	Yes	No	No

7. 分区操作

Table 15.23 Online DDL Support for Partitioning Operations

Partitioning Clause	Instant	In Place	Permits DML	Notes
PARTITION BY	No	No	No	Permits ALGORITHM=COPY, `LOCK={DEFAULT
ADD PARTITION	No	Yes*	Yes*	`ALGORITHM=INPLACE, LOCK={DEFAULT
DROP PARTITION	No	Yes*	Yes*	`ALGORITHM=INPLACE, LOCK={DEFAULT
DISCARD PARTITION	No	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
IMPORT PARTITION	No	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
TRUNCATE PARTITION	No	Yes	Yes	Does not copy existing data. It merely deletes rows; it does not alter the definition of the table itself, or of any of its partitions.
COALESCE PARTITION	No	Yes*	No	`ALGORITHM=INPLACE, LOCK={DEFAULT
REORGANIZE PARTITION	No	Yes*	No	`ALGORITHM=INPLACE, LOCK={DEFAULT
EXCHANGE PARTITION	No	Yes	Yes
ANALYZE PARTITION	No	Yes	Yes
CHECK PARTITION	No	Yes	Yes
OPTIMIZE PARTITION	No	No	No	ALGORITHM and LOCK clauses are ignored. Rebuilds the entire table. See Section 24.3.4, “Maintenance of Partitions”.
REBUILD PARTITION	No	Yes*	No	`ALGORITHM=INPLACE, LOCK={DEFAULT
REPAIR PARTITION	No	Yes	Yes
REMOVE PARTITIONING	No	No	No	Permits ALGORITHM=COPY, `LOCK={DEFAULT

总的来看，有几个是支持online且瞬间完成的：

• 修改索引类型
• 设置字段默认值
• 删除字段默认值
• 增加和删除虚拟列
• 表的重命名

参考文档：

1. MySQL :: MySQL 8.0 Reference Manual :: 15.12.1 Online DDL Operations