MySQL 8.0 Online DDL 在线表变更
MySQL InnoDB Online DDL 提供了在线表变更的能力，在进行DDL操作的同时，不影响或者尽可能小的影响DML操作，相比于传统的表变更锁表，不允许写入，Online DDL最大程度地减少了对业务的影响。该功能在5.6版本引入，在5.7，8.0版本得到持续加强，尤其8.0版本实现的快速表变更，只需要修改元数据，就能完成表变更操作，比如添加字段，秒级完成，极大地解决了大表加字段耗时长的问题。
一、Online DDL优势
- 大多数DDL操作期间，不阻塞DML的执行，减少对资源的锁定和等待时间，提高了DDL过程中表的可用性，减少对业务的影响。
- 一些支持快速表变更的操作，只需要修改数据字典中的元数据，不会在表上长时间占用元数据锁，不会影响表数据，操作快速完成，不影响DML。
- 相对于传统table-copy方式修改表结构，Online DDL能够减少CPU和IO负载，不会对整体数据库性能造成大的影响。
- 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算法有如下限制：
- 如果一个ALTER语句包含多处表修改，其他的修改不支持INSTANT算法时，添加字段也不能使用INSTANT算法。
- 只能在表的最后一个字段之后添加新的字段，否则无法使用INSTANT算法。
- 使用行格式压缩ROW_FORMAT=COMPRESSED时，无法使用INSTANT算法添加字段。
- 表中存在全文索引时，无法使用INSTANT算法添加字段。
- 不能使用INSTANT算法给临时表添加字段，临时表只支持COPY算法。
- 不能使用INSTANT算法给数据字典表空间中的表添加字段。
- 添加字段时，不会检查行尺寸大小限制，但是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方式。
- 临时日志文件的空间。临时日志文件用于记录改表期间产生的dml操作，如果临时日志文件超过最大值(innodb_online_alter_log_max_size)，DDL操作将会失败，未提交的DML操作，将会回滚。调高临时日志文件的最大值，在DDL期间，将允许更多的DML操作，但是也会导致DDL结束阶段应用DML日志时，花费更多的时间，表也同时被锁更多的时间。
- 临时排序文件的空间。online ddl操作，创建索引重建表时，将会在临时目录(tmpdir)写入临时排序文件。临时排序文件不能创建在包含原始表的目录里，每个临时排序文件必须能够存储完整的一个字段的数据，在这些数据被合并到最终的表或者索引中之后，这些临时排序文件将被删除。涉及临时排序文件时，需要的空间大致为表的数据量加上索引的数据量。
- 中间表文件的空间。有一些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语句：
- ddl 必须按顺序执行，比如创建一个索引，紧接着一个外键约束要使用这个索引。
- ddl 操作使用同样的锁模式，他们组成一个组，要么都成功，要么都失败。
- ddl 操作无法使用online方式，只能使用表拷贝（table-copy）。
- online ddl 指定 ALGORITHM=COPY 或者 old_alter_table=1，强制表拷贝，以便于在某些场景中，可以向后兼容。
六、Online DDL 可能失败的场景
有一些典型的场景，会导致online ddl执行失败，汇总如下：
- 指定的algorithm与ddl操作需要的类型不兼容，比如ddl操作需要重建表，而指定的algorithm是INSTANT，会导致online ddl执行失败。
- 指定的锁等级与ddl操作需要的锁等级不兼容，比如lock子句指定 SHARED 或者 NONE，实际需要EXCLUSIVE。
- online ddl 的开始和结束阶段，需要短暂地获取表的排他锁，如果获取锁超时，也会导致online ddl执行失败。
- tmpdir和innodb_tmpdir没有足够的磁盘空间，online ddl 在某个场景，比如创建索引，需要在临时目录写入临时的排序文件，如果磁盘空间不足，则会导致online ddl 失败。
- online ddl 执行时间很长，在此过程中，dml产生的临时在线日志超过了配置innodb_online_alter_log_max_size，将导致online ddl失败。
- 在online ddl期间，dml语句使用原表的定义进行操作，而不兼容新的表结构，当online ddl 执行到最后阶段，应用dml在线日志时，发生错误。比如dml语句插入了相同的值，而ddl正是对该字段加了唯一索引，此时online ddl 将会失败回滚。
七、Online DDL使用限制
- 临时表创建索引，使用copy方式
- 如果表上有约束条件ON...CASCADE 或者 ON...SET NULL，Online DDL的LOCK=NONE 子句不允许使用
- in-place方式在线改表，在结束之前，必须等待持有元数据锁的事务提交或者回滚。一个online ddl操作，在它执行阶段，可能会短暂地请求一个排他的元数据锁，而在最后更新表结构阶段，它总是会请求排他的元数据锁。因此，如果一个事务持有元数据锁，将会导致online ddl被阻塞，事务可能在online ddl之前或者执行过程中持有元数据锁，一个长事务，不管其是正在运行，还是休眠事务，都有可能导致online ddl操作超时。
- 当执行一个in-place方式的online ddl，执行online ddl的线程会应用其他dml线程产生的在线dml日志，当dml操作被应用后，有可能会出现键冲突，即使这个冲突是临时的，可能在后面被修复，但也会导致ddl失败。
- optimize table 命令对于InnoDB表，相当于执行了一个alter语句重建表，更新索引统计信息，释放聚簇索引中不使用的空间。二级索引的创建效率不高，因为键是按它们在主键中出现的顺序插入的
- MySQL 5.6 之前创建的表，包含时间字段（DATE, DATETIME or TIMESTAMP），并且没有使用copy方式重建，这样的表不支持in-place方式。
- 大表online ddl，需要重建表时，有以下限制：
- 在online ddl操作过程中，没有办法暂停，也不能限制cpu/io的使用。
- 如果online ddl失败，回滚操作开销很大。
- 如果online ddl执行时间很长，将会导致复制延迟。online ddl必须在主库执行完成之后，才能到从库执行，同样地，在主库上执行的DML，必须等从库DDL执行完成后，才能在从库应用DML。
参考资料：
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
文章评论