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