跳转至

04. MySQL 在线 DDL

DDL 变更

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

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

在数据库的发展历史中，DDL (Data Definition Language) 操作一直是一个重要的挑战。在主流商业数据库的早期版本中，传统的 DDL 操作，往往都需要对数据表进行锁定，以防止在操作过程中发生数据不一致。

这种锁定操作会导致数据库在 DDL 操作期间无法提供服务，对于需要 7*24 运行的大型现代应用来说，这是难以接受的。

为了解决这个问题，ORACLE 自 9i 引入 Online Table Redefinition，MySQL 自 5.6 引入 OnlineDDL，都旨在显著减少（或消除）更改数据库对象所需的应用程序停机时间（该特性以下统称 OnlineDDL）。

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 8.0 进一步引入了 Instant 算法，这是 DDL 操作的一个重大突破。Instant DDL 允许立即执行某些 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 在完成临时表的写入之后，用临时表替换掉源表。这个算法主要被早期（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 算法

当表发生 DDL 操作，可能会出现该表格数分钟甚至数小时不可访问，性能及响应异常，为了有效改善这个情况，MySQL 在 5.6.7 版本推出了Online DDL新特性。

我们常说的 Online DDL，其实是从 DML 操作的角度描述的，如果 DDL 操作不阻塞 DML，那么这个 DDL 就是 Online 的。

在Online DDL中，也包含了copy跟nplace两种方式，对于不支持Online DDL的DDL SQL，则采用 COPY 方式；对于支持 Online DDL 的 DDL SQL，则采用 Inplace 方式，这里的 Inplace 又区分为 2 类：是否需要 rebuild 表格，判断标准为：是否修改行记录格式。

  • 如果修改了行记录格式，则需要 rebuild 表格，比如修改列类型、增减列等；
  • 如果没有修改行记录格式，仅修改表的元数据，则不需要 rebuild 表格，仅修改元数据 metadata，比如删除索引、设置默认值及重命名列名等。

它的过程和上面的 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，两者的主要差异在于是否需要重建源表。

存储引擎层"就地"重建表，虽然不阻塞 DML 操作，但对于大表变更可能导致长时间的主从不一致。对此类 DDL 语句，如果想使 DDL 过程更加可控，且对从库延迟比较敏感，建议使用第三方在线改表工具 ptosc 完成。

INSTANT DDL