跳转至

3.在线DDL

DDL变更

DDL（Data Definition Language）是数据库内部的对象进行创建、删除、修改的操作语言，主要包括：加减列、更改列类型、加减索引等类型。

数据库的模式(schema)会随着业务的发展不断变化，如果没有高效的DDL功能，每一次变更都有可能影响业务，甚至产生故障。

MySQL在8.0以前就已经支持Online DDL，在执行时能够不阻塞其它DML（Insert/Update/Delete）操作，但许多重要的DDL操作，如加减列等，仍旧需要等待很长时间（根据数据量的大小）才会生效。

DDL变更是应用版本部署过程中的常见操作，在核心业务系统中DDL操作不能保证在线完成，是会影响业务7x24小时的。

最常见的操作莫过于在线加一个字段或者索引，如果数据量比较大的话，伴随而来的往往是长时间的等待，操作期间对表的访问都会不可用，所以一般只能等到凌晨操作，简直就是梦魇一般的存在。

发展历程

在 MySQL 5.5 版本中就加入了 INPLACE DDL 方式，但是因为实现的问题，依然会阻塞 INSERT、UPDATE、DELETE 操作，这也是 MySQL 早期版本长期被吐槽的原因之一。

在 MySQL 5.6 中，官方开始支持更多的 ALTER TABLE 类型操作来避免数据拷贝，同时支持了在线上 DDL 的过程中不阻塞 DML 操作，真正意义上的实现了 Online DDL。

MySQL DDL的注意事项

MySQL 在大表上的 DDL 会带来耗时较久、负载较高、额外空间占用、MDL、主从同步延时等情况。需要特别引起重视。

  • DDL所需时间 DDL 的执行时间，和很多因素相关，如果需要比较精确的时间预估，建议在测试环境提前做测试。 可以新建一个测试实例，将备份数据导出到测试实例，执行 DDL 操作，判断执行时间，作为对线上执行的一个估计。但是请注意，该估计仍然可能不准确，因为线上实例的负载可能会比测试实例高。

  • 负载 所有方式对大表做 DDL 都会增加负载，只是程度的不同，主要为 IO 的负载。如果是 IO 使用非常高的实例，建议在 IO 较小的时间段执行 DDL 操作。 如果Online DDL执行失败，则回滚有可能会是一项昂贵的操作；

  • 额外空间占用 copy、inplace rebuild-table、gh-ost、pt-online-schema-change，都会将表完整复制一份出来再做 DDL 变更，因此会使用和原表空间一样大（甚至更大，如果是加列的操作的话）的额外空间，另外还会生成大量的临时日志。要特别注意剩余空间，确保空间充裕，不然可能导致 DDL 过程中磁盘写满。

  • 临时日志文件大小(innodb_online_alter_log_max_size)，当DDL执行过程当中允许并发执行DML操作时的日志大小需求。 临时排序文件大小(tmpdir)，当DDL执行过程中表需要rebuild时临时排序文件是放在tmpdir指定的路径下的，需要保证该路径下的磁盘空间充足。 临时排序文件都足够容纳所有二级索引以及聚簇索引的主键列，最终合并到新表或索引后，临时排序文件会被删除。 在MySQL5.7.11及之后版本当中新增系统参数(innodb_tmpdir)专门用来指定Online DDL产生排序文件的路径。
    临时中间表文件大小当有些DDL执行过程中表需要rebuild时会在当前表所在路径下产生临时中间表文件，临时中间表文件大小可能需要与原表大小一致，在DDL执行过程当中产生。

MDL锁

所有方式做 DDL 均会产生 MDL（metadata lock）。除了 copy 模式会有持续性的锁（DDL 的整个过程期间无法向该表写入任何数据）之外，其他方式的 MDL 均为短暂的锁。

DDL语法

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  ....
  | ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}

 --备注:
 -- 1.DEFAULT：MySQL自己选择锁定资源最少的方式
 -- 2.INSTANT：只需要更新数据字典中的元数据, 很快完成
 -- 3.INPLACE：此变更由InnoDB引擎独立完成, 不需要使用Redo log等, 可以节省开销
 -- 4.COPY：此变更会重建聚簇索引, 执行DDL的时候会创建临时表

MySQL DDL 各种算法

copy算法

较简单较低级的实现方法，MySQL会在建立一个新的临时表，把源表的所有数据写入到临时表，在此期间无法对源表进行数据写入。

MySQL 在完成临时表的写入之后，用临时表替换掉源表。这个算法主要被早期（<=5.5）版本所使用。

MySQL内部具体实现步骤如下：

  1. 按照表 A 的定义新建一个临时表 B
  2. 对表 A 加读锁，不允许执行DML（期间表A只读）
  3. 在临时表 B 上执行 DDL 指定的操作（比如增加和删除列等）
  4. 逐行查出将表 A 中的数据查出来并插入到表 B
  5. 删除表 A
  6. 将表 B 重命名为 A

在 2-4 的过程中，如果表 A 数据量比较大，拷贝到表 B 的过程会消耗大量时间，并占用额外的存储空间。此外，由于 DDL 操作占用了表 A 的读锁，所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务。

ALTER TABLE table1 ADD COLUMN column1 int ALGORITHM=COPY;

inplace算法

MySQL Online DDL这个特性是在MySQL5.6.7开始支持的，我们常说的online DDL，其实是从DML操作的角度描述的，如果DDL操作不阻塞DML，那么这个DDL就是online的。

它的过程和上面的Copy算法有些不同：

  1. Online DDL过程中，从表A提取B+树，并存储到一个中间文件tmp-file，而不是中间表tmp-A

  2. 步骤1执行过程中，对表A的写入，都会记录到row log中

  3. 步骤1执行完毕后，对tmp-file应用所有的row log，得到一个与表A数据相同的数据文件

  4. 利用数据文件tmp-file替换表A的数据文件即可。

常用的 DDL 都默认使用这个算法。inplace 算法包含两类：inplace-no-rebuild 和 inplace-rebuild，两者的主要差异在于是否需要重建源表。