Online DDL操作类型汇总表

下表根据官方 Summary of Online Status for DDL Operations 整理挑选的常用操作。

• In-Place为Yes是优选项，说明该操作支持INPLACE
• Copies Table为No是优选项，因为为Yes需要重建表。大部分情况与In-Place是相反的
• Allows Concurrent DML?为Yes是优选项，说明ddl期间表依然可读写，可以指定 LOCK=NONE（如果操作允许的话mysql自动就是NONE）
• Allows Concurrent Query?默认所有DDL操作期间都允许查询请求，放在这只是便于参考
• Notes会对前面几列Yes/No带*号的限制说明

Operation	In-Place?	Copies Table?	Allows Concurrent DML?	Allows Concurrent Query?	Notes
添加索引	Yes*	No*	Yes	Yes	对全文索引的一些限制
删除索引	Yes	No	Yes	Yes	仅修改表的元数据
OPTIMIZE TABLE	Yes	Yes	Yes	Yes	从 5.6.17开始使用ALGORITHM=INPLACE，当然如果指定了old_alter_table=1或mysqld启动带--skip-new则将还是COPY模式。如果表上有全文索引只支持COPY
对一列设置默认值	Yes	No	Yes	Yes	仅修改表的元数据
对一列修改auto-increment 的值	Yes	No	Yes	Yes	仅修改表的元数据
添加 foreign key constraint	Yes*	No*	Yes	Yes	为了避免拷贝表，在约束创建时会禁用foreign_key_checks
删除 foreign key constraint	Yes	No	Yes	Yes	foreign_key_checks 不影响
改变列名	Yes*	No*	Yes*	Yes	为了允许DML并发, 如果保持相同数据类型，仅改变列名
添加列	Yes*	Yes*	Yes*	Yes	尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作。当添加列是auto-increment，不允许DML并发
删除列	Yes	Yes*	Yes	Yes	尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作
修改列数据类型	No	Yes*	No	Yes	修改类型或添加长度，都会拷贝表，而且不允许更新操作
更改列顺序	Yes	Yes	Yes	Yes	尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作
修改ROW_FORMAT 和KEY_BLOCK_SIZE	Yes	Yes	Yes	Yes	尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作
设置列属性NULL 或NOT NULL	Yes	Yes	Yes	Yes	尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作
添加主键	Yes*	Yes	Yes	Yes	尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作。 如果列定义必须转化NOT NULL，则不允许INPLACE
删除并添加主键	Yes	Yes	Yes	Yes	在同一个 ALTER TABLE 语句删除就主键、添加新主键时，才允许inplace；数据大幅重组,所以它仍然是一项昂贵的操作。
删除主键	No	Yes	No	Yes	不允许并发DML，要拷贝表，而且如果没有在同一 ATLER TABLE 语句里同时添加主键则会收到限制
变更表字符集	No	Yes	No	Yes	如果新的字符集编码不同，重建表

从表看出，In-Place为No，DML一定是No，说明ALGORITHM=COPY一定会发生拷贝表，只读。但ALGORITHM=INPLACEE也要可能发生拷贝表，但可以并发DML:

• 添加、删除列，改变列顺序
• 添加或删除主键
• 改变行格式ROW_FORMAT和压缩块大小KEY_BLOCK_SIZE
• 改变列NULL或NOT NULL
• 优化表OPTIMIZE TABLE
• 强制 rebuild 该表

不允许并发DML的情况有：修改列数据类型、删除主键、变更表字符集，即这些类型操作ddl是不能online的。

另外，更改主键索引与普通索引处理方式是不一样的，主键即聚集索引，体现了表数据在物理磁盘上的排列，包含了数据行本身，需要拷贝表；而普通索引通过包含主键列来定位数据，所以普通索引的创建只需要一次扫描主键即可，而且是在已有数据的表上建立二级索引，更紧凑，将来查询效率更高。

修改主键也就意味着要重建所有的普通索引。删除二级索引更简单，修改InnoDB系统表信息和数据字典，标记该所以不存在，标记所占用的表空间可以被新索引或数据行重新利用。