RDS for MySQL Online DDL 使用

 

---

RDS for MySQL 5.6、5.7 版本支持 Online DDL 特性。

Online DDL 功能允许在表上执行 DDL 的操作（比如创建索引）的同时不阻塞并发的 DML (Insert、Update、Delete、Replace)操作 和 查询(select)操作。

注： 从 RDS for MySQL 5.5 升级到 RDS for MySQL 5.6，第一次执行 DDL 时有可能会因为表数据的文件格式仍旧是 5.5 版本而不支持 Online DDL 特性。这种情况可以通过执行下面的命令来转换下：

# InnoDB 引擎表

alter table tab_name engine=innodb;

1、Online DDL 的限制

#	操作	In-Place?	Rebuilds Table?	并发 DML?	仅修改 元数据?	注释
1	添加二级索引	支持	不需要	允许	否
2	删除索引	支持	不需要	允许	是	仅修改表元数据 metadata
3	重命名索引 (5.7)	支持	不需要	允许	是	仅修改表元数据 metadata
4	添加全文索引	支持	不需要	不允许	否	第一个全文索引需要通过 table copy 的方式创建；其后的全文索引可以通过 in-place 方式创建
5	添加空间索引 (5.7)	支持	不需要	不允许	否
6	修改索引类型	支持	不需要	允许	是	仅修改表元数据 metadata
7	添加主键	支持	需要	允许	否	仅当 SQL_MODE 参数设置包含 strict_trans_tables 或 strict_all_tables 才支持 algorithm=inplace 如果涉及的列需要转换为 not NULL，则不支持 algorithm=inplace
8	删除主键	不支持	需要	不允许	否
9	删除主键并添加新主键	支持	需要	允许	否	仅当在同一个 Alter Table 语句中（删除主键的 DDL语句）添加新主键才支持 algorithm=inplace 因为实质上需要重新组织数据，因此开销高昂
10	添加列	支持	需要	允许	否	在添加 auto_increment 自增列时，是不允许并发 DML 操作的 因为实质上需要重新组织数据，因此开销高昂
11	删除列	支持	需要	允许	否	因为实质上需要重新组织数据，因此开销高昂
12	重命名列	支持	不需要	允许	是	如果仅修改字段名称，不修改字段类型，则支持并发 DML 操作
13	修改列顺序	支持	需要	允许	否	因为实质上需要重新组织数据，因此开销高昂
14	设置列默认值	支持	不需要	允许	是	仅修改表云数据 metadata
15	修改列数据类型	不支持	需要	不允许	否	仅支持 algorithm=copy
16	增加 varchar 类型字段长度 (5.7)	支持	不需要	允许	是	仅在存储字段长度所需的字节数不变的情况下支持 algorithm=inplace，0 - 255 字节需要 1 个字节保存长度，256 字节及以上需要 2 个字节保存长度
17	删除列默认值	支持	不需要	允许	是
18	修改自增列值	支持	不需要	允许	否	仅修改内存中的保存值
19	设置列为空值 Null	支持	不需要	允许	否	因为实质上需要重新组织数据，因此开销高昂
20	设置列不为空值 NOT Null	支持	不需要	允许	否	仅当 SQL_MODE 参数设置包含 strict_trans_tables 或 strict_all_tables 才支持 algorithm=inplace； 如果列值中包含空值 NULL，则该 DDL 操作会失败 因为实质上需要重新组织数据，因此开销高昂
21	修改 ENUM 或 SET 列定义	支持	不需要	允许	是	如果增加的元素导致存储长度变化，会需要 table copy
22	添加一个 stored 列 (5.7)	不支持	需要	不允许	否	Generated Column
23	修改 stored 列顺序 (5.7)	不支持	需要	不允许	否	Generated Column
24	删除 stored 列 (5.7)	支持	需要	允许	否	Generated Column
25	添加一个 virtual 列 (5.7)	支持	不需要	允许	是	Generated Column 分区表不支持 inplace 方式 不能和其他 DDL 一起执行
26	修改 virtual 列顺序 (5.7)	不支持	需要	不允许	否	Generated Column
27	删除 virtual 列 (5.7)	支持	不需要	允许	是	Generated Column 分区表不支持 inplace 方式 不能和其他 DDL 一起执行
28	添加外键约束	支持	不需要	允许	是	必须 set foreign_key_checks=0; 关闭 foreign_key_checks，来支持 inplace 方式
29	删除外键约束	支持	不需要	允许	是	foreign_key_checks 选项开启或者关闭都可以
30	修改 Row_Format	支持	需要	允许	否	因为实质上需要重新组织数据，因此开销高昂
31	修改 Key_Block_Size	支持	需要	允许	否	因为实质上需要重新组织数据，因此开销高昂
32	设置表的 persistent statistics 选项	支持	不需要	允许	是	仅修改表的元数据 metadata
33	指定表字符集	支持	需要	不允许	否	如果新的字符集编码不同，需要重建表
34	转换表字符集	不支持	需要	不允许	否	如果新的字符集编码不同，需要重建表
35	optimize table	支持	需要	允许	否	如果表上创建有全文索引，则不支持 inplace 方式； optimize 语句不支持指定 algorithm 和 lock 选项
36	带 force 选项重建表	支持	需要	允许	否	如果表上有全文索引，则不支持 algorithm=inplace 选项; alter table table_name force, algorithm=inplace, lock=none
37	重建表	支持	需要	允许	否	如果表上有全文索引，则不支持 algorithm=inplace 选项; alter table table_name engine=innodb, algorithm=inplace, lock=none
38	重命名表	支持	不需要	允许	是	仅修改表的元数据 metadata; 表名修改后不保留对该表的特殊赋权，必须重新赋权

• In-Place？：对应 DDL语句的 Algorithm 选项，通过 inplace 方式执行 DDL。相比表拷贝方式，可以减少空间和 I/O 消耗。
• 允许并发 DML？：对应 DDL语句的 Lock 选项，DDL 执行期间是否支持并发 DML 操作。
• 仅修改元数据？：DDL 语句执行期间是否仅修改存储在表的 .frm 文件中的元数据信息。
• MySQL官方文档请参考：Online DDL 操作 (5.6)  Online DDL 操作 (5.7)
• DDL 操作执行时需要修改表的元数据（metadata），有可能会遇到等待表元数据锁的情况（waiting for table metadata lock），该情况的处理方式请参考：RDS MySQL 表上 Metadata lock 的产生和处理
• Inplace 和 Copy Table 是相反的 2 种处理方式；但即使 DDL 支持 Inplace 选项，某些操作在整个执行过程中也会部分涉及到表拷贝。

2. Online DDL 建议的选项

• Algorithm=Inplace ：为了避免表拷贝导致的实例性能问题（空间、I/O问题），建议在 DDL 中包含该选项。如果 DDL 操作不支持 Algorithm=Inplace 方式，DDL 操作会立刻返回错误。

-- 修改字段数据类型不支持 algorithm=inplace 选项

alter table area_bak algorithm=inplace, modify father text;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

• Lock=None ：为了在 DDL 操作过程中不影响业务的 DML 操作，建议在 DDL 中包含该选项。如果 DDL 操作不支持 Lock=None （允许并行 DML 操作）选项，DDL  操作会立刻返回错误。

-- 转换字符集不支持并发 DML 操作

alter table area ALGORITHM=copy, lock=none,CONVERT TO CHARACTER SET utf8mb4;

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

默认情况下 RDS for MySQL 会尽量使用 algorithm=inplace , lock=none 来进行 DDL 操作。因此默认可以不指定这两个选项。

但如果担心 DDL 操作对系统负载有影响或阻塞对目标表的 DML 操作，建议使用 algorithm=inplace ,和 lock=none 选项来操作；这样如果系统对某一个选项不支持，会立刻返回错误，避免影响业务。

所有的 DDL 操作均建议在 业务低峰期 进行，避免对业务产生影响。

--使用 algorithm=inplace, lock=none 选项成功创建索引的例子

alter table area algorithm=inplace, lock=none, add index idx_fa (father);

对不支持 Online DDL 的操作（比如 RDS for MySQL 5.5），可以考虑通过 Percona 的 Schema Online Change 工具来操作。

Alter Table 语法请参考： ALTER TABLE Syntax

3. 异常处理

在对某些大表的 Online DDL 过程中，有时会碰到下面的错误：

-- 在 DML 操作频繁的 rd_order_rec 表上创建 idx_cr_time_detail 索引

alter table rd_order_rec add index idx_cr_time_detail (cr_time,detail);

ERROR 1799(HY000): Creating index 'idx_cr_time_detail' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

原因：

在进行 Online DDL（不阻塞并发 DML） 的过程中，每个被修改的表或者创建的索引都会使用一个临时日志来保存 DDL 过程中并发 DML 操作的记录。该临时日志文件的大小可以根据需要从参数 innodb_sort_buffer_size 指定的大小扩展到参数 innodb_online_alter_log_max_size 指定的大小。

如果有临时日志文件大小超过上限，则该 DDL 语句返回失败并且所有没有提交的并发 DML 操作会被回滚。因此增加 innodb_online_alter_log_max_size 参数的大小可以允许 DDL 过程中更多的并发 DML 操作，但是较大的值也会使在 DDL 操作末尾阶段的锁定表应用日志中的数据的过程持续更长的时间。

#	参数名称	默认值	最小值	最大值	作用
1	innodb_online_alter_log_max_size	134217728	134217728	2147483647	Online DDL 存储并发 DML 信息的日志文件尺寸最大值，单位字节。默认值 128 MB，最大值 2047 MB。

解决：

在 RDS 控制台  参数设置调高 innodb_online_alter_log_max_size 参数设置。