RDS for MySQL Online DDL 使用-阿里云开发者社区

开发者社区> 阿里云支持与服务> 正文

RDS for MySQL Online DDL 使用

简介: RDS for MySQL Online DDL 使用   Online DDL 的限制 Online DDL 建议的选项 异常处理 RDS for MySQL 5.6 支持 Online DDL 特性。

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. 异常处理

A.  在对某些大表的 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 参数设置。

B.  在对大表的 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 1062 (23000): Duplicate entry

同时,由于 DDL 增量期间的数据被保存在日志文件中,在此过程中会临时忽略掉一致性检查,因此合并数据时候有可能会碰到 Duplicate Entry 重复数据的错误

对该错误只能通过重试来完成 DDL 的执行。

官方文档请参考 Online DDL 限制 (5.6)  Online DDL 限制 (5.7)

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:

分享阿里云支持与服务团队最佳实践、经典案例与故障排查。

官方博客
文档