MySQL 8.0 Online DDL 在线表变更

MySQL InnoDB Online DDL 提供了在线表变更的能力，在进行DDL操作的同时，不影响或者尽可能小的影响DML操作，相比于传统的表变更锁表，不允许写入，Online DDL最大程度地减少了对业务的影响。该功能在5.6版本引入，在5.7，8.0版本得到持续加强，尤其8.0版本实现的快速表变更，只需要修改元数据，就能完成表变更操作，比如添加字段，秒级完成，极大地解决了大表加字段耗时长的问题。

一、Online DDL优势

1. 大多数DDL操作期间，不阻塞DML的执行，减少对资源的锁定和等待时间，提高了DDL过程中表的可用性，减少对业务的影响。
2. 一些支持快速表变更的操作，只需要修改数据字典中的元数据，不会在表上长时间占用元数据锁，不会影响表数据，操作快速完成，不影响DML。
3. 相对于传统table-copy方式修改表结构，Online DDL能够减少CPU和IO负载，不会对整体数据库性能造成大的影响。
4. Online DDL操作能够更少的读取数据到buffer pool，减少内存页换出，避免DDL完成后，由于频繁页换出导致的性能下降。

二、Online DDL语法

相对于传统ALTER语句改表，Online DDL新增了ALGORITHM子句和LOCK子句，一个典型的Online DDL语法如下：

ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM 子句：

ALGORITHM 子句支持 INSTANT，INPLACE和COPY三种方式。

• INSTANT：快速改表
• INPLACE：原地改表
• COPY：拷贝方式改表

算法性能：INSTANT > INPLACE > COPY

ALGORITHM=INSTANT，快速表变更，支持快速添加字段等操作，该特性在 8.0.12 版本加入。

LOCK 子句：

默认条件下，Online DDL在一次改表过程中，尽可能少的使用锁，在inplace和copy方式下，如果需要锁，可以指定lock子句，以执行更严格的锁。如果lock指定的锁级别无法满足DDL操作，改表语句将会报错。

锁级别从低到高，依次如下：

• LOCK=NONE，允许DML
• LOCK=SHARED，允许读，禁止DML
• LOCK=DEFAULT，默认锁模式，在满足DDL操作前提下，设置锁模式将允许尽可能多的读和DML。
• LOCK=EXCLUSIVE，阻塞读和DML

Online DDL 语法并不需要显式地指定ALGORITHM和LOCK，通常不需要做任何配置就可以使用Online DDL。当然也可以在ALTER语句中显式地使用 ALGORITHM，LOCK子句来控制Online DDL具体使用的算法以及加锁类型。

三、Online DDL支持的操作

3.1 索引操作

操作类型	支持快速完成	原地改表	重建表	允许DML	只修改元数据
创建、添加二级索引	no	yes	no	yes	no
删除索引	no	yes	no	yes	yes
重命名索引	no	yes	no	yes	yes
添加全文索引	no	yes*	no*	no	no
添加空间索引	no	yes	no	no	no
修改索引类型	yes	yes	no	yes	yes

创建全文索引时，如果没有用户定义的FTS_DOC_ID字段，将会重建表。

创建或者添加二级索引，在索引创建的过程中，表仍然可以读写。在Online DDL创建索引的最后阶段，只有在访问表的所有事务完成之后才能结束，以便索引能够反映最新的表内容。

示例：

（1）创建索引：
CREATE INDEX name ON table (col_list);
ALTER TABLE tbl_name ADD INDEX name (col_list);

（2）删除索引：
DROP INDEX name ON table;
ALTER TABLE tbl_name DROP INDEX name;

（3）重命名索引：
ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;

（4）创建全文索引：
CREATE FULLTEXT INDEX name ON table(column);

（5）创建空间索引：
CREATE TABLE geom (g GEOMETRY NOT NULL);
ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;

（6）改变索引类型（USING {BTREE | HASH}）：
ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,…) USING BTREE, ALGORITHM=INSTANT;

3.2 主键操作

操作类型	支持快速完成	原地改表	重建表(临时表）	允许DML	只修改元数据
添加主键	no	yes*	yes*	yes	no
删除主键	no	no	yes	no	no
删除主键并添加新的主键	no	yes	yes	yes	no

示例：

（1）添加主键
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

（2）删除主键：
ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;

（3）删除主键，然后添加新的主键：
ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

3.3 字段操作

操作类型	支持快速完成	原地改表	重建表(临时表）	允许DML	只修改元数据
添加字段	yes*	yes	no*	yes*	no
删除字段	no	yes	yes	yes	no
重命名字段	no	yes	no	yes*	yes
字段顺序变更	no	yes	yes	yes	no
字段设置默认值	yes	yes	no	yes	yes
修改字段数据类型	no	no	yes	no	no
修改varchar字段大小	no	yes	no	yes	yes
删除字段默认值	yes	yes	no	yes	yes
修改auto-increment值	no	yes	no	yes	no*
修改字段可以为NULL	no	yes	yes*	yes	no
修改字段不可以为NULL	no	yes*	yes*	yes	no
修改enum,set定义	yes	yes	no	yes	yes

示例：

（1）添加字段：
ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INSTANT;

（2）添加多个字段：
ALTER TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT, ALGORITHM=INSTANT;

添加字段的INSTANT算法有如下限制：

1. 如果一个ALTER语句包含多处表修改，其他的修改不支持INSTANT算法时，添加字段也不能使用INSTANT算法。
2. 只能在表的最后一个字段之后添加新的字段，否则无法使用INSTANT算法。
3. 使用行格式压缩ROW_FORMAT=COMPRESSED时，无法使用INSTANT算法添加字段。
4. 表中存在全文索引时，无法使用INSTANT算法添加字段。
5. 不能使用INSTANT算法给临时表添加字段，临时表只支持COPY算法。
6. 不能使用INSTANT算法给数据字典表空间中的表添加字段。
7. 添加字段时，不会检查行尺寸大小限制，但是DML操作，插入或者更新数据时，会进行检查。

（3）删除字段：
ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;

（4）重命名字段：
ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;

字段数据类型不改变，只修改名称，不影响DML。

（5）修改字段顺序：
ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

（6）修改字段数据类型：
ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

修改字段数据类型，只支持COPY算法。

（7）修改varchar类型大小：
ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

varchar小于256时，使用1个字节存储大小，大于等于256时使用2个字节存储大小。在长度存储没有改变时，比如varhcar size 从 10 变为 20，可以使用inplace 方式修改。如果长度存储发生改变，比如varchar size 从 10 变为 1000，那么只能使用copy方式修改。否则会报错，如下：

ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

减少varchar size，只能使用copy算法。

（8）字段设置默认值：
ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INSTANT;

只修改元数据，默认值存储在数据字典里。

（9）删除字段默认值：
ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INSTANT;

（10）修改表的auto-increment值：
ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

（11）修改字段可以为NULL：
ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

（12）修改字段为NOT NULL：
ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

（13）修改enum,set类型的定义：
CREATE TABLE t1 (c1 ENUM(‘a’, ‘b’, ‘c’));
ALTER TABLE t1 MODIFY COLUMN c1 ENUM(‘a’, ‘b’, ‘c’, ‘d’), ALGORITHM=INSTANT;

3.4 表操作

操作类型	支持快速完成	原地改表	重建表(临时表）	允许DML	只修改元数据
修改ROW_FORMAT	no	yes	yes	yes	no
修改KEY_BLOCK_SIZE	no	yes	yes	yes	no
设置表统计信息持久化	no	yes	no	yes	yes
指定字符集	no	yes	yes*	no	no
转换字符集	no	no	yes*	no	no
Optimizing table	no	yes*	yes	yes	no
强制模式重建表	no	yes*	yes	yes	no
重建表	no	yes*	yes	yes	no
重命名表	yes	yes	no	yes	yes

示例：

（1）修改行格式ROW_FORMAT：
ALTER TABLE tbl_name ROW_FORMAT = row_format, ALGORITHM=INPLACE, LOCK=NONE;

（2）修改KEY_BLOCK_SIZE：
ALTER TABLE tbl_name KEY_BLOCK_SIZE = value, ALGORITHM=INPLACE, LOCK=NONE;

（3）设置表统计信息持久化选项：
ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;

（4）指定字符集：
ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;

如果指定的字符集与表当前的字符集不同，则需要重建表。

（5）转换字符集：
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;
如果指定的字符集与表当前的字符集不同，则需要重建表。

（6）Optimizing table：
OPTIMIZE TABLE tbl_name;
表中有全文索引，不支持inplace方式。

（7）带有FORCE选项的重建表：
ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

表中包含全文索引，不支持Inplace方式。

（8）重建表：
ALTER TABLE tbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
表中包含全文索引，不支持Inplace方式。

（9）重命名表：
ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INSTANT;

3.6 其他改表操作

Online DDL还支持其他改表操作，不再赘述，详情可参考官方文档。

• Generated Column Operations
• Foreign Key Operations
• Tablespace Operations
• Partitioning Operations

四、Online DDL 磁盘空间要求

以下列出的磁盘空间要求仅针对in-place方式的在线改表，不适用instant和copy方式。

1. 临时日志文件的空间。临时日志文件用于记录改表期间产生的dml操作，如果临时日志文件超过最大值(innodb_online_alter_log_max_size)，DDL操作将会失败，未提交的DML操作，将会回滚。调高临时日志文件的最大值，在DDL期间，将允许更多的DML操作，但是也会导致DDL结束阶段应用DML日志时，花费更多的时间，表也同时被锁更多的时间。
2. 临时排序文件的空间。online ddl操作，创建索引重建表时，将会在临时目录(tmpdir)写入临时排序文件。临时排序文件不能创建在包含原始表的目录里，每个临时排序文件必须能够存储完整的一个字段的数据，在这些数据被合并到最终的表或者索引中之后，这些临时排序文件将被删除。涉及临时排序文件时，需要的空间大致为表的数据量加上索引的数据量。
3. 中间表文件的空间。有一些ddl操作在重建表时，需要在原始表所在目录中创建中间表文件，通常一个中间表文件需要与原表文件相同大小的磁盘空间，文件名以#sql-ib开头，在online ddl过程中短暂地存在。

五、Online DDL，合并多个表变更到一个SQL语句

在引入online ddl之前，通常建议将同一个表的多个变更合并到一个alter语句中执行，多个变更，表只需要重建一次，能有效提高表变更的效率。

对于online ddl场景，可以将多个不同的表变更，分为几个相对独立的alter语句，以便于更好的管理和维护，同时不会牺牲执行效率。

比如：

ALTER TABLE t1 ADD INDEX i1(c1), ADD UNIQUE INDEX i2(c2), CHANGE c4_old_name c4_new_name INTEGER UNSIGNED;

可以拆分为：

ALTER TABLE t1 ADD INDEX i1(c1);
ALTER TABLE t1 ADD UNIQUE INDEX i2(c2);
ALTER TABLE t1 CHANGE c4_old_name c4_new_name INTEGER UNSIGNED NOT NULL;

在有一些场景，仍然可以将多个表变更写成一个alter语句：

1. ddl 必须按顺序执行，比如创建一个索引，紧接着一个外键约束要使用这个索引。
2. ddl 操作使用同样的锁模式，他们组成一个组，要么都成功，要么都失败。
3. ddl 操作无法使用online方式，只能使用表拷贝（table-copy）。
4. online ddl 指定 ALGORITHM=COPY 或者 old_alter_table=1，强制表拷贝，以便于在某些场景中，可以向后兼容。

六、Online DDL 可能失败的场景

有一些典型的场景，会导致online ddl执行失败，汇总如下：

1. 指定的algorithm与ddl操作需要的类型不兼容，比如ddl操作需要重建表，而指定的algorithm是INSTANT，会导致online ddl执行失败。
2. 指定的锁等级与ddl操作需要的锁等级不兼容，比如lock子句指定 SHARED 或者 NONE，实际需要EXCLUSIVE。
3. online ddl 的开始和结束阶段，需要短暂地获取表的排他锁，如果获取锁超时，也会导致online ddl执行失败。
4. tmpdir和innodb_tmpdir没有足够的磁盘空间，online ddl 在某个场景，比如创建索引，需要在临时目录写入临时的排序文件，如果磁盘空间不足，则会导致online ddl 失败。
5. online ddl 执行时间很长，在此过程中，dml产生的临时在线日志超过了配置innodb_online_alter_log_max_size，将导致online ddl失败。
6. 在online ddl期间，dml语句使用原表的定义进行操作，而不兼容新的表结构，当online ddl 执行到最后阶段，应用dml在线日志时，发生错误。比如dml语句插入了相同的值，而ddl正是对该字段加了唯一索引，此时online ddl 将会失败回滚。

七、Online DDL使用限制

1. 临时表创建索引，使用copy方式
2. 如果表上有约束条件ON…CASCADE 或者 ON…SET NULL，Online DDL的LOCK=NONE 子句不允许使用
3. in-place方式在线改表，在结束之前，必须等待持有元数据锁的事务提交或者回滚。一个online ddl操作，在它执行阶段，可能会短暂地请求一个排他的元数据锁，而在最后更新表结构阶段，它总是会请求排他的元数据锁。因此，如果一个事务持有元数据锁，将会导致online ddl被阻塞，事务可能在online ddl之前或者执行过程中持有元数据锁，一个长事务，不管其是正在运行，还是休眠事务，都有可能导致online ddl操作超时。
4. 当执行一个in-place方式的online ddl，执行online ddl的线程会应用其他dml线程产生的在线dml日志，当dml操作被应用后，有可能会出现键冲突，即使这个冲突是临时的，可能在后面被修复，但也会导致ddl失败。
5. optimize table 命令对于InnoDB表，相当于执行了一个alter语句重建表，更新索引统计信息，释放聚簇索引中不使用的空间。二级索引的创建效率不高，因为键是按它们在主键中出现的顺序插入的
6. MySQL 5.6 之前创建的表，包含时间字段（DATE, DATETIME or TIMESTAMP），并且没有使用copy方式重建，这样的表不支持in-place方式。
7. 大表online ddl，需要重建表时，有以下限制：
   • 在online ddl操作过程中，没有办法暂停，也不能限制cpu/io的使用。
   • 如果online ddl失败，回滚操作开销很大。
   • 如果online ddl执行时间很长，将会导致复制延迟。online ddl必须在主库执行完成之后，才能到从库执行，同样地，在主库上执行的DML，必须等从库DDL执行完成后，才能在从库应用DML。