如何使用MySQL 5.6的新特性Online DDL_云数据库 RDS(RDS)-阿里云帮助中心

本文介绍如何使用MySQL 5.6及以上版本的新特性Online DDL。

RDS MySQL支持5.6及以上Online DDL特性。

Online DDL（在线DDL）功能允许在表上执行DDL的操作（例如创建索引）的同时不阻塞并发的DML操作和查询（select）操作。

说明

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

ALTER TABLE <表名> engine=innodb;

更多版本特性请参见AliSQL内核小版本发布记录。

重要

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

Online DDL的限制

操作	是否支持Inplace方式	是否需要Copy Table	是否允许并发DML	是否允许并发查询	备注
创建普通索引	支持	不需要	允许	允许	无。
创建全文索引	支持	不需要	不允许	允许	第一个全文索引需要通过Copy Table的方式创建；其后的全文索引可以通过Inplace方式创建。
删除索引	支持	不需要	允许	允许	仅修改表元数据metadata。
优化表	支持	需要	允许	允许	如果表上创建有全文索引，则不支持algorithm=inplace选项。
设置列默认值	支持	不需要	允许	允许	仅修改表元数据metadata。
修改自增列值	支持	不需要	允许	允许	仅修改表元数据metadata。
添加外键约束	支持	不需要	允许	允许	`set foreign_key_checks=0;` 来关闭 foreign_key_checks，避免拷贝表。
删除外键约束	支持	不需要	允许	允许	foreign_key_checks选项开启或者关闭都可以。
重命名列	支持	不需要	允许	允许	如果仅仅修改字段名称，而不要修改字段类型，是支持并发DML操作的。
添加列	支持	需要	允许	允许	在添加auto_increment自增列时，是不允许并发 DML 操作的。尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。
删除列	支持	需要	允许	允许	尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。
修改各列顺序	支持	需要	允许	允许	尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。
修改Row_Format属性	支持	需要	允许	允许	尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。
修改Key_Block_Size属性	支持	需要	允许	允许	尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。
设置列为空值Null	支持	需要	允许	允许	尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。
设置列不为空值NOT Null	支持	需要	允许	允许	该操作需要将SQL_MODE 参数设置为STRICT_ALL_TABLES或STRICT_TRANS_TABLES才能成功。如果列值中包含空值（NULL），则该DDL 操作会失败。尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。
修改列的数据类型	不支持	需要	不允许	允许	无。
添加主键	支持	需要	允许	允许	尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。如果涉及的列需要转换为NOT NULL，则不支持Algorithm=INPLACE。
删除主键并添加新主键	支持	需要	允许	允许	仅当在同一个Alter Table语句中（删除主键的DDL语句）添加新主键才支持Algorithm=INPLACE。因为数据实质上需要重新组织，因此操作的开销高昂。
删除主键	不支持	需要	不允许	允许	无。
Convert character set	不支持	需要	不允许	允许	如果新的字符集编码不同，需要重建表。
Specify character set	不支持	需要	不允许	允许	如果新的字符集编码不同，需要重建表。
带force选项重建表	支持	需要	允许	允许	如果表上有全文索引，则不支持Algorithm=Inplace选项。
重建表 alter table ... engine=innodb	支持	需要	允许	允许	如果表上有全文索引，则不支持Algorithm=Inplace选项。
设置表的 persistent statistics	支持	不需要	允许	允许	仅修改表的元数据metadata。
修改表注释	支持	不需要	允许	允许	无。

是否支持Inplace方式：对应DDL语句的Algorithm选项，通过Inplace方式执行DDL。相比Copy Table的方式，可以减少空间和I/O消耗。
是否需要Copy Table：对应DDL语句的Algorithm选项，通过Copy Table的方式执行DDL。DDL执行期间会占用更大的磁盘空间和消耗更多的I/O。
是否允许并发DML：对应DDL语句的Lock选项，DDL执行期间是否支持并发DML操作。
是否允许并发查询：DDL语句执行期间是否支持并发查询操作（通常都是支持的）。
MySQL官方文档请参见Online DDL 概览。
DDL操作执行时需要修改表的元数据（metadata），有可能会遇到等待表元数据锁的情况（waiting for table metadata lock），该情况的处理方式请参见解决MDL锁导致无法操作数据库的问题。
Inplace和Copy Table是相反的2种处理方式；但即使DDL支持Inplace选项，某些操作在整个执行过程中也会部分涉及到Copy Table，例如上表中的添加列操作。

Online DDL选项建议

Algorithm=Inplace ：为了避免Copy Table导致的实例性能问题（空间、I/O问题），建议在DDL中包含该选项。如果DDL操作不支持Algorithm=Inplace方式，DDL操作会立刻返回错误。
```
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操作会立刻返回错误。
```
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 MySQL会尽量使用algorithm=inplace以及lock=none来进行DDL操作，因此默认可以不指定这两个选项。但如果担心DDL操作对系统负载有影响或阻塞对目标表的DML操作，建议使用algorithm=inplace或lock=none选项来操作，这样如果系统对某一个选项不支持，会立刻返回错误，避免影响业务。

示例

ALTER TABLE area algorithm=inplace, lock=none, add index idx_fa (father);

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

ALTER TABLE语法请参见ALTER TABLE Syntax。

异常处理

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

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操作末尾阶段的锁定表应用日志中的数据的过程持续更长的时间。

解决方法

针对MySQL 5.6及以上版本，可以在控制台修改innodb_online_alter_log_max_size参数值，详细步骤请参见设置实例参数。