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)

 

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

相关文章
【转】了解使用 ASP.NET AJAX 进行局部页面更新
简介 Microsoft的 ASP.NET 技术提供了一个面向对象、事件驱动的编程模型,并将其与已编译代码的优势结合起来。但其服务器端的处理模型仍存在技术本身所固有的几点不足: 进行页面更新需要往返服务器,因此需要页面刷新; 来回往返不会保留 Javascript 或其他客户端技术(如 Adobe Flash)生成的任何效果。
1322 0
PL/SQL11——DBMS_DDL包的使用
 http://blog.csdn.net/robinson_0612/article/details/6098248  为了便于建立性能良好的PL/SQL程序,Oracle提供了大量的系统包供使用。
757 0
智能媒体管理产品“文档标准型”使用介绍
智能媒体管理(Intelligent Media Management, IMM) 是阿里云的云服务,它通过 文档标准型 实例提供了 Cloud Native 架构的文档转换/预览功能,本文介绍功能使用、计费相关的配置。
1842 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
4505 0
CentOS下安装MySQL,Windows下使用Navicat for MySql连接
安装查看有没有安装过: yum list installed mysql* rpm -qa | grep mysql*查看有没有安装包: yum list mysql*安装mysql客户端: yum install mysql安装mysql 服务器端: ...
845 0
bboss热部署应用资源销毁监听器ApplicationLifeListener使用说明
bboss热部署应用资源销毁监听器ApplicationLifeListener使用说明 实现类org.frameworkset.web.listener.ApplicationLifeListener实现javax.servlet.ServletContextListener接口,当应用卸载时用来清除框架和应用系统的内存缓存资源,有效规避应用热部署时内存泄露和线程泄露风险。
640 0
+关注
田杰
专注于数据库领域技术
21
文章
46
问答
来源圈子
更多
作为全球云计算的领先者,阿里云为全球230万企业提供着云计算服务,服务范围覆盖200多个国家和地区。我们致力于为企业、政府等组织机构提供安全可靠的云计算服务,给用户带来极速愉悦的服务体验。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载