mysql8.0添加操作表结构显式online ddl参数

显式online ddl参数(此功能只适用于 InnoDB 表)

可以在执行online DDL语句的时候，使用ALGORITHM和LOCK关键字，这两个关键字在DDL语句的最后面，用逗号隔开。

ALGORITHM有如下选项：

• INPLACE：直接在原表上面执行DDL的操作。
• COPY：使用临时表。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间，表不允许DML的操作。
• DEFAULT：默认方式，由MySQL自己选择，优先使用INPLACE的方式。

LOCK有如下选项：

• SHARE：共享锁，执行DDL的表可以读，但是不可以写。
• NONE：没有任何限制，执行DDL的表可读可写。
• EXCLUSIVE：排它锁，执行DDL的表不可以读，也不可以写。
• DEFAULT：默认值，也就是在DDL语句中不指定LOCK子句的时候使用的默认值，由MySQL自动判断，优先使用NONE的方式。

例句如下，参数间使用逗号隔开：

-- 添加字段
alter table test add col int,ALGORITHM=INPLACE,LOCK=DEFAULT;

-- 添加索引(单个索引)
alter table test add INDEX `index_user_id` (`user_id`),ALGORITHM=INPLACE,LOCK=DEFAULT;

-- 添加索引(联合索引)
alter table test add INDEX `index_id_code_status` (`user_id`,`fund_code`,`loan_status`),ALGORITHM=INPLACE,LOCK=DEFAULT;

执行DDL操作时，显式参数可以不指定，mysql会自动选择合适的方式去执行，优先使用inplace，none的方式，效果与指定ALGORITHM=DEFAULT,LOCK=DEFAULT一样。但是如果显式指定了这两个参数，则必须按照指定的方式来执行，不支持的话则直接报错。

总结分析

从上面的内容来看，online ddl添加索引正常应该是很快的，但并不是完全不会加锁。在对表元数据加互斥锁的时候，会阻塞dml操作。但从整体来看，这个时间应该是极短的，所以官方才有online ddl操作不会加锁的说法。

上面说到在inplace模式的online ddl操作时，会申请一个缓存空间，用于存放在此期间的dml操作。这个缓存大小由参数innodb_online_alter_log_max_size控制，默认为128mb，支持动态修改。如果更新的表比较大，并且在ddl过程中有大量的写操作，就可能遇到空间不足的情况，会抛出相应的错误。

另外，如果ddl操作的目标表上有未结束的事务或者有锁没有释放，那么在加元数据独占锁(mdl)时就会等待前面的锁释放，这个时候的状态为：waiting for table metadata lock。又因为独占锁的优先权限，后面的DML操作都要排队等待。从而导致db操作阻塞。

最终的结论是：如果表数据量较小，或者加索引的表数量较少，online ddl操作是可以接受的，建议显式指定algorithm和lock参数。但如果数据量较大，或者加索引的表比较多，那么就需要充分考虑上面说到的问题，最好在业务流量低的时候执行。

原文参考：Mysql在线添加索引_xiaoyi52的专栏-CSDN博客_mysql在线创建索引