RDS/Aurora MySQL 的大表操作和管理最佳实践之大表 DDL

1. 前言

Amazon Relational Database Service（Amazon RDS）是一项 Web 服务，让用户能够在云上更轻松地设置、扩展和管理关系型数据库。Amazon RDS 支持 MySQL、PostgreSQL、MariraDB、Oracle、Microsoft SQL Server 等多种引擎。Amazon Aurora 则是一款 Amazon 自研的全托管类兼容 MySQL 和 PostgreSQL 的关系型数据库产品，在 RDS MySQL 和 Aurora MySQL 的日常使用过程中，大表的使用和维护经常成为数据库使用人员一个比较关注的一件事情。大表操作和管理的最佳实践系列博客主要想结合个人经验以及客户支持过程中在大表的 DDL、大表的 DML 及分表、大表冷热数据分离这 3 个常见的大表相关主题给出最佳操作实践。本篇博客主要介绍在 RDS/Aurora 的 MySQL 引擎中如何优雅的进行大表 DDL。

2. DDL 操作以及问题

DDL 操作会经常出现在我们使用关系型数据库的过程中。比如业务初期，我们需要创建业务相关的表结构；业务的扩展或收缩，我们需要增加或者减少表字段；数据库的负载升高，SQL 性能的降低，我们需要增加或调整相应的索引结构来支持更好的语句执行性能；随着表数据的增长和改变，我们可能面临着需要更改字段类型的场景；出于一些运维或者使用场景的需求，我们需要调整表的主键、外键甚至说需要变更常规表为分区表。业务或架构调整，我们可能需要删除表或者数据库；等等。这些操作都需要我们去执行对应的 DDL 语句来达到我们的目的。

这个时候我们一般比较关注的问题有：

1）操作时是否允许并发 DML
2）操作耗时多久
3）操作需预留的空间以及额外负载
4）是否有工具或者新特性帮助我们更好的完成 DDL 操作

2.1 DDL 操作时是否允许并发 DML

鉴于表的 DDL 操作关注度一直比较高，所以 MySQL 社区也一直致力于实现 DDL 的无阻塞以及快速操作。在 MySQL 5.5 和带有 InnoDB 插件的 MySQL 5.1 中优化了 Create Index 和 Drop Index 以避免表复制的行为。该功能被称为快速索引创建（Fast Index Create）也称为 INPLACE 算法，区别于 Copy 算法，这种方式不需要创建影子表并 Copy 数据，但这种方式还是需要给表加 S 锁，会阻塞 DDL 执行期间对应表上发生的 DML 操作并且只支持二级索引的操作。在 MySQL 5.6 开始，开始支持允许并发 DML 时增加二级索引，并扩展了“在线”特性至其他如改变自增值等操作，称为“Online DDL”。至 MySQL 8.0.12，Instant 类算法的出现实现了更加快速的 DDL，MySQL 8.0.29 支持快速加列到表的任意位置以及快速 drop 列，达到不需要修改存储层数据就可以快速完成 DDL 操作，让 MySQL 的大表 DDL 操作实现操作期间允许并发 DML 且更快速。

其中 Online DDL 操作的可以分为以下三个阶段：

第 1 阶段：初始阶段

在初始化阶段，服务器会考虑存储引擎的能力、语句中指定的操作以及用户指定的 ALGORITHM 和 LOCK 选项来确定操作期间允许的并发量。在此阶段，将采用共享的可升级元数据锁来保护当前的表定义。

第 2 阶段：执行阶段

在此阶段，准备并执行语句。元数据锁是否升级为独占取决于初始化阶段评估的因素。如果需要独占元数据锁，则仅在语句准备期间短暂使用。

第 3 阶段：提交表定义阶段

在提交表定义阶段，元数据锁升级为独占，以逐出旧表定义并提交新表定义。一旦授予，独占元数据锁的持续时间很短。

对于所有 Online DDL 操作，我们通常讨论的允许并发 DML 的时间窗口是指在执行阶段，除去短暂的独占元数据锁时间的其他时间。

2.1.1 常见的操作类型（MySQL 8.0）

注意：图中 * 表示大多数对应类 DDL 操作为图示方式操作，但存在例外；如 MySQL 8.0.11 之后，允许 INSTANT 方式加列，但是如果是增加自增列则只能使用 INPLACE 方式，且不允许并发 DML。Optimize table 操作一般使用 INPLACE 方式切允许并发 DML 操作，但是如果表中有 FULLTEXT 索引，则不支持 INPLACE 方式。具体可对照官方文档查看例外情况。

关于主键的变更，建议 DROP 主键操作与 ADD 主键操作在同一语句中完成，否则在 DROP PRIMARY KEY 阶段只能使用 COPY 方式，会造成不允许 DML 的阻塞情况。

ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

常见的 DDL 操作我们可以将其分为三种方式：

方式一：COPY

MySQL 5.6 之前版本常见的 COPY，该方式会阻塞 DML 操作，并且需要复制表数据至影子表，代价较高。

方式二：INSTANT

该方式为 MySQL 8.0 引入的 INSTANT 方式，是三种常见方式中最快速，代价最小的一种方式，不涉及表数据的复制或重建且操作期间不会阻塞 DML 操作，在下一章节也会详细介绍其实现原理及性能。

方式三：INPLACE

这种方式是 MySQL 5.6 版本引入的，避免复制表数据，可能需要重建表，需短暂获取元数据锁，大多操作不阻塞 DML 操作，这一类别可以再进行细分为以下 3 类。

1）只更新元数据类
仅更新元数据意味着无需通过复制来重建表，所以当没有其他阻塞的时候，这个几乎是立即发生的且允许并发 DML 操作，这类操作在 MySQL 8.0 中大多移至使用 INSTANT 算法这一类别，比如修改列名。

2）增加某些新的对象但是不改变表原有数据
这一类即为上图中的 INPLACE-No-Rebuild 类，这一类操作中也会发生数据的复制，但是仅限于新的对象数据，例如增加二级索引，则会从簇索引中读取条目，构建二级索引条目并将它们缓存在临时文件中，对这些条目进行排序并使用批量构建或排序构建插入。这一类操作主聚集索引不会被重新构建，所以整体操作非常快，并且可以重用相同的表 ibd，并且由于原始表没有被触及，并行 DML 更改会继续修改原始表，因此不需要重新合并或者说应用 DML 操作，也不存在说操作期间会阻塞表的 DML 操作了。比如增加二级索引操作。

3）需要重建表
这一类对应上图中的 INPLACE-Rebuild 类操作，需要重建聚簇索引即表数据，并且在这个过程中捕获 DML 条目在重建完成后应用这期间的 DML 操作，通过这种方式实现短暂的独占元数据锁之外的时间不阻塞 DML 操作的目的。比如 optimize table 操作。

所以针对 DDL 期间是否允许并发 DML 操作的问题，我们可以参照上图以及不同方式的实现原理来判断 DDL 操作是否会在 DDL 操作期间允许并发 DML 操作。另外我们也发现在允许并发 DML 这一块，MySQL 版本越高，DDL 操作期间，允许并发 DML 操作的 DDL 操作类型越多，建议能在条件允许的情况下，尽量升级到较高版本，实现更快速影响更小的方式执行 DDL，特别是对大表，快速且负载影响低显得尤为重要。如果 DDL 操作类型无 online 方式，则可以考虑使用 pt-osc 或者 go-ost 等工具进行操作，可避免操作期间的 DML 阻塞。

2.2 RDS Aurora for MySQL 中增加列的三种方式耗时分析

2.2.1 三种方式耗时测试

在 Aurora MySQL 版本 2 中，Lab 模式下，支持一种名为 FastDDL 的方式添加字段。未开启 Lab 模式时，Aurora2 则使用默认的 INPLACE 方式添加字段。在 Aurora MySQL 版本 3 中，Aurora 默认使用称为 Instant DDL 的 MySQL 8.0 功能添加字段，是 Aurora MySQL 版本 2 使用称为快速 DDL 的不同实现。你也可以使用 ALGORITHM=INPLACE 指定使用 INPLACE 方式添加。三种方式具体的耗时我们也可以通过下面的测试了解一下。

检查表大小以及表定义

MySQL [lff]> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 37326600 |
+----------+
1 row in set (0.01 sec)
MySQL [lff]>   SELECT t.table_schema, t.table_name, ROUND(t.DATA_LENGTH/1024/1024/1024,2)  "size in GB", ROUND(t.DATA_FREE/1024/1024/1024,2) "fragmented ize in GB"  FROM information_schema.tables t  WHERE t.table_name ='sbtest1';  
+--------------+---------------------------+------------+----------------------+
| table_schema | table_name                | size in GB | fragmented ize in GB |
+--------------+---------------------------+------------+----------------------+
| lff          | sbtest1                   |       5.34 |                 0.00 |
+--------------+---------------------------+------------+----------------------+
1 row in set (0.00 sec)
MySQL [lff]> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` text NOT NULL,
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=41287051 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

测试结果

# Aurora 2.11.2 非Lab模式下，默认INPLACE方式添加列

MySQL [lff]> show variables like '%lab%'
    -> ;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| aurora_lab_mode | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

MySQL [lff]> alter table sbtest1 add column add_column_test  varchar(32);
Query OK, 0 rows affected (3 min 1.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

# Aurora 2.11.2 Lab模式下Fast DDL方式添加列

MySQL [lff]> show variables like '%lab%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| aurora_lab_mode | ON    |
+-----------------+-------+
1 row in set (0.01 sec)

MySQL [lff]>  alter table sbtest1 add column add_column_test  varchar(32);  
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

# Aurora 3.03.1，默认使用INSTANT方式添加列

MySQL [lff]>  alter table sbtest1 add column add_column_test  varchar(32);  
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# Aurora 3.03.1，通过ALGORITHM指定使用INPLACE方式添加列

MySQL [lff]>  alter table sbtest1 add column add_column_test  varchar(32),algorithm=inplace;  
Query OK, 0 rows affected (1 min 11.62 sec)
Records: 0  Duplicates: 0  Warnings: 0

通过以上测试还是可以较明显的发现开启 Lab 模式 Aurora2 中增加列效果和 Aurora3 中的 INSTANT 方式耗时相近，和 Aurora2 中非 Lab 模式下的 INPLACE 方式的 DDL 操作时间相差较大。那为什么会有这样的差异呢？主要是因为三种方式的实现原理是不一样的。

2.2.2 Aurora2 中的 Fast DDL

在非 Lab 模式的 Aurora 2.11.2 中采用的是使用 INPLACE 算法进行的 Online DDL，在对应版本中，除非你是增加自增列字段，其他类别增加列操作均允许并发 DML 操作，根据上述常见 DDL 操作表我们可以知道该操作为需要 Rebuild 类操作。整个过程会重建表结构并应用期间并发的 DML 日志，相对时间较长且会给实例带来额外的负载。

在 Aurora2 中为了提高某些 DDL 操作效率而执行的优化称之为 Fast DDL，主要是通过优先更新表相关信息，然后应用增量 DML，不涉及表数据的迁移或重建，所以整体速度是非常快的且 DDL 操作期间无其他额外负载。但是 Fast DDL 的使用还是具有一定的限制的，比如上面例子中可以看出只支持在 Lab 模式下将没有默认值且可为空的列添加到现有表的最后的时候才可以使用 Fast DDL，但是其他类型 Alter 目前还不支持，并且对表的类型以及部分属性也有限制，比如不能应用于分区表并且无法作用于 REDUNDANT 行格式的 InnoDB 表等。
所以虽然说 Aurora2 中有 Fast DDL 功能，但是还是不建议在生产环境中使用 Lab 模式，如果在 DDL 特别是新增列这一块需要类似的快速方案，可考虑升级至 Aurora3 利用 Instant DDL 功能。

2.2.3 Aurora3 中的 Instant DDL

Aurora MySQL 版本 3 与社群 MySQL 8.0 的即时 DDL 兼容。这种方式也是不涉及表数据的迁移或重建，所以整体速度是非常快的且 DDL 操作期间无其他额外负载。您可以将子句 ALGORITHM=INSTANT 与 ALTER TABLE 语句结合使用来执行即时 DDL 操作。MySQL 8.0.12 开始，为了支持 Instant add column，引入了新的记录格式，如果使用 Instant 算法添加过 column，那么会在记录已有的 info bit 区域使用一个 bit 位存储 INSTANT_FLAG 信息，并且会在记录内存储第一次 instant 方式增加字段前表的字段个数，同时在系统表中存储通过 Instant 算法添加的 column 默认值等信息。如果没有执行过 Instant DDL，则表的行记录格式保持不变。通过这种方式在查询时，老的记录只需要添加新字段对应默认值，新记录则按照存储格式进行解析返回即可。对应原理也决定了只支持表在最后加列的限制。针对 Aurora3 中的 INSTANT 加列对后续操作的性能影响，我这边也做了一个压测，从结果可以看出，INSTANT 和 INPLACE 两种加列方式对后续表的相关操作性能存在一定影响，特定并发下，INSTANT 表现略优于 INPLACE，但整体差异不大，以下表格为 32 并发下的 QPS 压测数据，可供参考。

测试实例	Aurora 版本	MySQL 版本	机型	测试表个数	测试表行数	测试表大小	只读	只写	读写
InplaceDDL 加 1 列	8.0.mysql_aurora.3.03.1	8.0.26	db.r6g.4xlarge	10	100000000	20G	21619	3044	9351
InstantDDL 加 1 列	8.0.mysql_aurora.3.03.1	8.0.26	db.r6g.4xlarge	10	100000000	20G	22248	3244	10495
InplaceDDL 加 2 列	8.0.mysql_aurora.3.03.1	8.0.26	db.r6g.4xlarge	10	100000000	20G	21961	2628	9523
InstantDDL 加 2 列	8.0.mysql_aurora.3.03.1	8.0.26	db.r6g.4xlarge	10	100000000	20G	21500	3208	10294

2.2.4 MySQL Instant DDL 的进一步扩展

MySQL 8.0.29 开始，MySQL 进一步提升 Instant 的操作范围，支持任意位置添加列，以及删除列重命名列等操作。为了此项支持， MySQL 在系统表 INFORMATION_SCHEMA.INNODB_TABLES 新增了 TOTAL_ROW_VERSIONS 列来追踪当前表执行 Intsant add/drop 的次数，最大值为 64。同时在表的行格式中也会有对应版本号的记录，同样也是支持最大版本 64，如果超出则需要执行 table rebuild 类操作才可以。除了存储元数据表结构和记录格式的变更外，为支持 Instant add /drop，redo log 格式也发生了较大的变化，新增了 redo log 记录版本、所有列长度以及使用 Instant 方式增加的列的具体信息等。

当前支持 Instant 方法操作的 DDL 有：
1）修改索引类型
2）增加列
3）删除列
4）重命名列
5）设置列的默认值
6）删除列的默认值
7）修改 ENUM 或者 SET 的列定义

所以 DDL 操作的耗时与其选择执行 DDL 操作的方式息息相关，明白各种方式其中的原理结合自己的具体操作，然后辅以表大小，机器 IO 性能以及历史 DDL 经验等，可以简单进行所需时间的估算。

2.3 DDL 操作需预留的空间以及额外负载

DDL 操作是否需要预留一定量的空间，这个问题对于大表来说非常值得关注，因为如果空间不足不仅则会造成 DDL 失败甚至可能影响整体实例可用性。那么如何在 DDL 操作之前判断 DDL 操作是否要预留空间，要预留多大的空间呢？下面是一个具体的大表 DDL 示例。

# RDS MySQL5.7.33 db.m5.xlarge
MySQL [sbtest]> SELECT t.table_schema, t.table_name, ROUND(t.DATA_LENGTH/1024/1024/1024,2)  "size in GB", ROUND(t.DATA_FREE/1024/1024/1024,2) "fragmented ize in GB"  FROM information_schema.tables t  WHERE t.table_name ='sbtest1';                                                            
+--------------+------------+------------+----------------------+
| table_schema | table_name | size in GB | fragmented ize in GB |
+--------------+------------+------------+----------------------+
| sbtest       | sbtest1    |      19.50 |                 0.00 |
+--------------+------------+------------+----------------------+
1 row in set (0.01 sec)
MySQL [sbtest]> use sbtest;alter table  sbtest1 add column  test_inplace_ddl varchar(10) not null default 'test';
Database changed
Query OK, 0 rows affected (53 min 52.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

上述示例中是在版本为 MySQL 5.7.33 实例类型为 db.m5.xlarge 的 RDS 实例中进行的一个约 20GB 表的加列操作，可根据常见 DDL 操作表定位到该操作为 INPLACE-Rebuild 类操作，需要重建表。通常来说，我们至少需要预留一倍待操作表大小的空间，除此之外，还需考虑日志类存储空间。在上图一中 FreeStorageSpace 监控项中可以看出，DDL 操作期间，空间占用在一倍表大小左右，且在操作完成后恢复近操作前水平，从上图 FreeStorageSpace 监控项也可以看出，操作过程，剩余空间由 154G 到 127G 再恢复至 150G 左右。根据上图 CPUUtilization 监控项可以看出，DDL 操作期间 CPU 最高占用率在 11%左右，同时结合 DBLoad、IO throughput 等监控项也可以看到 DDL 操作对 CPU、IO 资源都存储占用，也即会带来额外的负载，具体大小和实例类型以及操作表大小均有关系，需综合判断。

2.4 其他问题

DDL 操作带来的锁表以及额外的性能影响等在表大小较大时，则会更加突出，并且可能会出现一些小表 DDL 时不常见的问题。如：

1）大表 DDL 操作导致 RDS/MySQL 从库延迟

当我们的 RDS 从库上有查询类任务时需要额外注意这一点，一般单线程复制时，从库的延迟时间会和 DDL 在主库的执行时间相近，如果对该类延迟较为敏感且不能接受，则需要考虑使用 pt-online-schema-change 或者 gh-ost 等工具进行操作。另 Aurora for MySQL 则可以较好的避免该类问题。

2）大表 DDL 操作 DB_ONLINE_LOG_TOO_BIG 错误导致 DDL 失败

这种一般发生在 INPLACE-Rebuild 方式执行的 DDL 操作中，该类操作一般是通过采用的创建临时文件并扫描聚簇索引记录生成新的 B+树后，应用存放在增量日志结构中此期间的发生在变更表上的 DML 记录，后原地替换原来表文件来完成。所以这里需要额外注意的是，如果 DDL 操作耗时较长，且并发 DML 对表的修改过多，导致临时日志文件的大小超过 innodb_online_alter_log_max_size 的值，则在线 DDL 操作会失败，出现 DB_ONLINE_LOG_TOO_BIG 错误，未提交的并发 DML 操作将回滚。那为了避免该类问题，是否可以将 innodb_online_alter_log_max_size 设置的越大越好呢？其实不是的，较大的 innodb_online_alter_log_max_size 设置允许在线 DDL 操作期间有更多并发 DML 操作，它也会延长 DDL 操作结束时表被锁定以应用并发 DML 的时间，所以建议尽量选择在业务低峰期做大表的 DDL 操作，并设置合适的 innodb_online_alter_log_max_size 值。

3）大表 DDL 操作唯一键冲突的错误

INPLACE-Rebuild 方式的 Online DDL 操作中，还有一类经常出现的错误便是 ERROR 1062 (23000): Duplicate entry，也就是键值冲突，以下是两种出现这种错误的示例。

示例一，在 Aurora 2.11.2 版本中测试，会发现 DDL 操作期间，开启另一个会话成功执行 REPLACE 操作，但是却导致了 DDL 操作的失败。

#以下测试前表中已存在trace_id='752346930794741249951669247776'以及id=18413420的记录。
# Aurora 2.11.2
MySQL [lff]> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` text NOT NULL,
  `pad` char(60) NOT NULL DEFAULT '',
  `trace_id` varchar(30) NOT NULL COMMENT 'unique',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_1` (`trace_id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=41287051 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
# TEST1 REPLACE操作，唯一键冲突，DML Client无报错，DDL失败:
T1时刻 - Session1:
MySQL [lff]> alter table sbtest1 add column text_duplicate_key varchar(32) NOT NULL;

T2时刻 - Session2:
MySQL [lff]> replace into sbtest1 (id,trace_id)values(18413420,752346930794741249951669247776);                                                                                                                                                                    
Query OK, 2 rows affected, 1 warnings (0.04 sec)

T3时刻 - Session1:
ERROR 1062 (23000): Duplicate entry '752346930794741249951669247776' for key 'idx_1'

示例二，在 Aurora 2.11.2 版本中测试，DDL 操作期间，开启另一个会话执行有唯一键冲突的 INSERT 操作，这个有冲突未成功执行的 INSERT 操作却导致了 DDL 操作的失败。

# TEST2 INSERT操作，唯一键冲突，DML Client报错，DDL失败:
 T1时刻 - Session1:
 MySQL [lff]> alter table sbtest1 add column text_duplicate_key varchar(32) NOT NULL;

 T2时刻 - Session2:
 MySQL [lff]> insert into sbtest1  (id,trace_id)values(18413421,880652762838315375263125053852);
 ERROR 1062 (23000): Duplicate entry '880652762838315375263125053852' for key 'idx_1'

 T3时刻 - Session1:
 ERROR 1062 (23000): Duplicate entry '880652762838315375263125053852' for key 'idx_1'

示例三，在 Aurora 2.11.2 版本中测试，DDL 操作期间，开启另一个会话执行有主键冲突的 INSERT 操作，这个有冲突未成功执行的 INSERT 操作并未导致了 DDL 操作的失败。这里便可以看出主键冲突和唯一键冲突对 DDL 操作的影响是不一样的。

# TEST3 主键冲突，DML Client报错，DDL成功:
T1时刻 - Session1:
MySQL [lff]> alter table sbtest1 add column text_duplicate_key varchar(32) NOT NULL;

T2时刻 - Session2:
MySQL [lff]> insert into sbtest1  (id,trace_id)values(18413420,88065276283831537526312505)；                                                                                                                                                                                        
ERROR 1062 (23000): Duplicate entry '18413420' for key 'PRIMARY'

T3时刻 - Session1:
Query OK, 0 rows affected (2 min 1.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

一般常见的是示例一中此类问题，因为 Client 无报错信息但是 DDL 却报出唯一键冲突的问题，会让人比较迷惑。针对这个问题，官方解释道这并非为一个 BUG，而是一个类似于外键约束的 Online DDL 的限制。

When running an online ALTER TABLE operation, the thread that runs the ALTER TABLE operation will apply an “online log” of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the “online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.

所以，针对有唯一键且日常操作 REPLACE 类操作较多的大表，在 DDL 的时候需要额外注意一下可能出现的这个错误。可以通过在业务代码层修改 Replace 语句为先判断记录是否存在再进行操作的逻辑，存在则更新，不存在则进行插入。

4）大表 DDL 操作时间过长

针对大表 DDL 操作时间过长的问题，如果待操作 DDL 为在 MySQL 8.0 中加入到 INSTANT 类的操作，建议升级到 MySQL 8.0 对应的 RDS 或 Aurora 版本。如果为非支持 INSTANT 类操作，则可进一步根据上述 DDL 操作分类判断，如需要使用 INPLACE_Rebuild 或 COPY 方式操作，则建议在业务低峰期进行操作或者使用 pt-osc 或者 go-ost 等工具进行操作，避免长时间的 DDL 带来的负载过高或主从延迟等问题。特别是 COPY 方式操作的 DDL，还需考虑不允许并发 DML 这一点，使用 pt-osc 或者 go-ost 等工具进行操作则可避免该问题。

5）大表 DDL 操作需要获取元数据锁导致大量 Query 阻塞致数据库实例高负载

我们通常说的 DDL 不阻塞 DML 操作多是指 DDL 操作执行中除独占元数据所之外的时间，如果在执行或者提交阶段 DDL 获取元数据锁时恰好有其他会话持有该锁导致 DDL 阻塞，那么后续针对该表的所有操作都会被阻塞。判断 DDL 操作是否被阻塞可以通过 show processlist 命令来查看，一般这种情况可以看到 DDL 操作的 state 为“Waiting for table metadata lock”，也可以通过 show processlist 输出结合相关锁信息判断出阻塞 DDL 操作的会话，找到后可选择通过 CALL mysql.rds_kill(thread-ID)命令强制结束阻塞 DDL 操作的会话，恢复 DDL 操作，被阻塞的其他操作导致实例高负载的情况也将得到缓解。

所以我们通常说的 Online DDL 不阻塞 DML 也并不是绝对的一种说法，短暂的独占元数据锁获取阶段也有可能导致阻塞，所以还是需要谨慎对待 DDL 操作，尽量选择业务低峰期操作。

3. DDL 操作工具

在使用 Amazon Aurora MySQL 或者 Amzon RDS MySQL 的时候，如果日常操作中大表 DDL，则建议尽量使用兼容 MySQL 8.0 以及更高版本数据库，同时根据上述关注问题以及常见问题，选择业务低峰期操作，某些情况下还需修改业务代码逻辑或者 SQL 执行方式。

如果我们在使用非 Amazon Aurora3、Amzon RDS MySQL 8.0 版本，或者使用 Amazon Aurora3、Amzon RDS MySQL 8.0 版本但是需要执行的 DDL 类操作不能使用 INSTANT 或者 INPLACE-No-Rebuild 等较轻量方式执行的话，无法避免锁表或者操作时间过长造成主从延迟等问题时，我们可以选择第三方工具来帮助我们执行相关操作。常见的工具有 pt-online-schema-change 和 gh-ost 两种，接下来会对这两个工具进行进一步展开。

3.1 pt-online-schema-change

pt-online-schema-change 工具是 Percona 开发的工具包 percona-toolkit 中的一款在线表变更工具，其通过新建影子表并 copy 源表数据至影子表，同时在源表上创建 INSERT、DELETE、UPDATE 3 种操作的触发器来应用源表上的 DML 操作至影子表，后 rename 影子表为源表名并删除触发器，以此实现 DDL 操作不阻塞 DML 操作的目的。

3.1.1 pt-online-schema-change 工具的使用条件

1）表必须有主键或者唯一键
2）表上无触发器
3）增加的字段如果为 NOT NULL 则必须添加默认值
4）支持外键但是操作会更复杂并有风险

3.1.2 pt-online-schema-change工具的使用示例

# RDS MySQL5.7.33 db.m5.xlarge
MySQL [sbtest]> SELECT t.table_schema, t.table_name, ROUND(t.DATA_LENGTH/1024/1024/1024,2)  "size in GB", ROUND(t.DATA_FREE/1024/1024/1024,2) "fragmented ize in GB"  FROM information_schema.tables t  WHERE t.table_name='sbtest1';   
+--------------+------------+------------+----------------------+
| table_schema | table_name | size in GB | fragmented ize in GB |
+--------------+------------+------------+----------------------+
| sbtest       | sbtest1    |      24.58 |                 0.00 |
+--------------+------------+------------+----------------------+
1 row in set (0.01 sec)

MySQL [sbtest]> select count(*) from sbtest1;
+-----------+
| count(*)  |
+-----------+
| 100000000 |
+-----------+
1 row in set (1 min 49.67 sec)
./pt-online-schema-change --user=admin --password=123456 --host=your-instance.us-east-2.rds.amazonaws.com --alter "add column name int default 1" D=sbtest,t=sbtest1  --print --execute --charset=utf8 --chunk-size=5000 --max-load=Threads_running=100 --critical-load=Threads_running=400

3.1.3 pt-online-schema-change 工具的使用耗时以及资源消耗

测试实例为 Amazon RDS for MySQL 机型为 db.m5.xlarge，一个节点，Multi-AZ 架构，控制表数据复制阶段每批次大小的参数 chunk-size=5000 的设置下 100000000 行 24.58GB 的表耗时 85 分钟，无其他任务，仅 DDL 操作，实例最大 CPU 使用率 19%，同样需要占用一倍表大小左右的空间。

3.2 gh-ost

gh-ost 是一款无触发器形式的 MySQL 在线模式变更的工具，可测试并且支持暂停以及动态控制等灵活性操作。和 pt-online-schema-change 类似的是 gh-ost 也是使用创建影子表并应用增量 DML 操作到影子表然后 rename 影子表到原表名的方式来实现在线不阻塞 DML 操作，不同的是，其使用的不再是触发器，避免了 DDL 期间 Trigger 对原表的影响并减轻了整个实例的负载压力。gh-ost 使用的是基于 binlog 的增量应用修改的方式，将其异步应用到中间表中，是心啊将迁移的写入负载与主服务器的工作负载分离。

3.2.1 gh-ost 工具的使用条件

1）实例需开启 binlog
2）表必须存在主键或唯一键
3）不支持外键

3.2.2 gh-ost 工具的使用示例

./gh-ost/usr/bin/gh-ost --allow-on-master --assume-rbr --max-load=Threads_running=100 --critical-load=Threads_running=400 --critical-load-hibernate-seconds=60 --chunk-size=5000  --nice-ratio=0  --user="admin" --password="123456" --host=your-instance.rds.amazonaws.com' --port=3306 --database="sbtest" --table="sbtest1" --alter= "add column name int default 1" --cut-over=default --cut-over-lock-timeout-seconds=1 --dml-batch-size=10  --default-retries=10 --heartbeat-interval-millis=2000    --throttle-additional-flag-file=/tmp/gh-ost-throttle_ghost.sbtest1   --execute >> /tmp/rebuild_t1.log 2>&1 &

注意 gh-ost 需要开启 binlog，且设置 binlog_format=ROW。如果使用 RDS Aurora 且未开启 binlog 情况下，则需要修改参数组并重启集群，否则报错如下：

FATAL your-instance.rds.amazonaws.com:3306 must have binary logs enabled

3.2.3 gh-ost 工具的使用耗时以及资源消耗

测试实例为 Amazon RDS for MySQL 机型为 db.m5.xlarge，一个节点，Multi-AZ 架构，同样在控制表数据复制阶段每批次大小的参数 chunk-size=5000 的设置下 100000000 行 24.58GB 的表耗时 74 分钟，无其他任务，仅 DDL 操作，实例最大 CPU 使用率 18%，同样需要占用一倍表大小左右的空间。

3.3 DDL 工具的使用总结

gh-ost 工具相较 pt-online-schema-change 不需要创建触发器，而是通过 binlog 来应用增量 DML，这种方式对原表影响更小，以上示例中 DDL 操作期间无并发 DML 所以未体现出这一点。另外 gh-ost 工具可以通过 chunk-size 和 dml-batch-size 来控制每次数据复制阶段以及应用 DML 阶段的大小，通过 max-load、critical-load 以及 critical-load-hibernate-seconds 定义数据库实例的负载阈值，帮助我们在 DDL 期间监测实例状态，必要时终止 gh-ost 操作，避免给实例带来过大的压力影响正常业务流量。通过使用 panic-flag-file 和 postpone-cutover-flag 文件，依托文件哨兵方法来实现 gh-ost 任务的终止和 cut-over 阶段的控制。相较于 pt-online-schema-change 更加灵活与轻便。和原生 Online DDL 方式相比，工具可以更灵活的控制我们的 DDL 过程，也可以避免阻塞以及延迟等问题，但是在部分负载控制策略下执行时间可能会相对更长，并发 DML 较小时，对实例的额外负载和原生 Online DDL 方式相当。当有大量 DML 在 DDL 操作期间并行发生时，pt-online-schema-change 对负载的影响可能会比 gh-ost 工具更大。可具体根据使用场景和工具要求确定具体使用哪种方式更合适。

4. 总结

无论在 RDS MySQL 还是 Aurora MySQL 中，大表 DDL 都是一个需要我们额外关注的操作。本篇博客叙述了我们的 RDS MySQL 以及 Aurora MySQL 中常见的 DDL 操作以及关注和常见的问题，同时结合 Online DDL 、Fast DDL 、Instant DDL 等不同的 DDL 方式的原理，帮助读者更好的了解并执行 DDL 操作。另外 MySQL 8.0 的 Instant DDL 出现，一定程度上帮助我们更快速完成某些操作。第三方工具如 gh-ost 以及 pt-online-schema-change 等常用工具的正确使用也可以帮助我们避免锁表以及延迟等业务影响。

亚马逊AWS官方博客