使用快速 DDL 在 Amazon Aurora 中修改表 - Amazon Aurora
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

使用快速 DDL 在 Amazon Aurora 中修改表

在 MySQL 中,很多数据定义语言 (DDL) 操作会对性能产生明显影响。即使经过最近的在线 DDL 改进,还会对性能产生影响。

例如,假设您使用 ALTER TABLE 操作在表中添加一列。根据指定的算法,该操作可涉及以下步骤:

  • 创建表的完整副本

  • 创建临时表,以处理并发数据操控语言 (DML) 操作

  • 重建此表的所有索引

  • 应用并发 DML 更改时应用表锁定

  • 减慢并发 DML 吞吐量

在 Amazon Aurora 中,您可以使用快速 DDL 立即就地运行 ALTER TABLE 操作。完成该操作无需复制表,对其他 DML 语句也没有重大影响。由于该操作不会在表复制中使用临时存储,因此,甚至可以对小型实例类中的大型表使用 DDL 语句。

重要

目前,必须启用 Aurora 实验室模式才能为 Aurora MySQL 使用快速 DDL。我们不建议为生产数据库集群使用快速 DDL。有关启用 Aurora 实验室模式的信息,请参阅Amazon Aurora MySQL 实验室模式

快速 DDL 限制

目前,快速 DDL 具有以下限制:

  • 快速 DDL 仅支持将没有默认值且可为空的列添加到现有表的最后。

  • 快速 DDL 不支持分区表。

  • 快速 DDL 不支持使用 REDUNDANT 行格式的 InnoDB 表。

  • 如果 DDL 操作的最大可能的记录大小太大,则不会使用快速 DDL。如果大于页面大小的一半,则说明记录大小太大。记录的最大大小是将所有列的最大大小相加得出的。对于大小可变的列,按照 InnoDB 标准,不会在计算中包含外部字节。

    注意

    在 Aurora 1.15 中添加了最大记录大小检查。

快速 DDL 语法

ALTER TABLE tbl_name ADD COLUMN col_name column_definition

该语句具有以下选项:

  • tbl_name要修改的表的名称。

  • col_name要添加的列的名称。

  • col_definition要添加的列的定义。

    注意

    您必须指定不带默认值且可为空的列定义,否则无法使用快速 DDL。

快速 DDL 示例

以下示例演示了快速 DDL 操作带来的加速。第一个 SQL 示例在不使用快速 DDL 的情况下在大型表上运行 ALTER TABLE 语句。这项操作需要大量时间。CLI 示例说明了如何为集群启用快速 DDL。然后,另一个 SQL 示例在相同的表上运行相同的 ALTER TABLE 语句。启用快速 DDL 后,操作会非常快。

本示例使用 TPC-H 基准测试中的 ORDERS 表,其中包含 1.5 亿行。此集群有意使用相对较小的实例类来演示无法使用快速 DDL 时 ALTER TABLE 语句可能需要多长时间。该示例创建包含相同数据的原始表的克隆。检查 aurora_lab_mode 设置确认集群没有使用快速 DDL,因为实验室模式未启用。然后,ALTER TABLE ADD COLUMN 语句需要大量时间才能在表格末尾添加新列。

mysql> create table orders_regular_ddl like orders; Query OK, 0 rows affected (0.06 sec) mysql> insert into orders_regular_ddl select * from orders; Query OK, 150000000 rows affected (1 hour 1 min 25.46 sec) mysql> select @@aurora_lab_mode; +-------------------+ | @@aurora_lab_mode | +-------------------+ | 0 | +-------------------+ mysql> ALTER TABLE orders_regular_ddl ADD COLUMN o_refunded boolean; Query OK, 0 rows affected (40 min 31.41 sec) mysql> ALTER TABLE orders_regular_ddl ADD COLUMN o_coverletter varchar(512); Query OK, 0 rows affected (40 min 44.45 sec)

此示例与前一个示例一样准备了大型表。但是,您不能简单地在交互式 SQL 会话中启用实验室模式。必须在自定义参数组中启用该设置。这样做需要断开 mysql 会话并运行一些 Amazon CLI 命令或使用 Amazon Web Services Management Console。

mysql> create table orders_fast_ddl like orders; Query OK, 0 rows affected (0.02 sec) mysql> insert into orders_fast_ddl select * from orders; Query OK, 150000000 rows affected (58 min 3.25 sec) mysql> set aurora_lab_mode=1; ERROR 1238 (HY000): Variable 'aurora_lab_mode' is a read only variable

为集群启用实验室模式需要使用参数组进行一些操作。此 Amazon CLI 示例使用集群参数组,以确保集群中的所有数据库实例对实验室模式设置使用相同的值。

$ aws rds create-db-cluster-parameter-group \ --db-parameter-group-family aurora5.6 \ --db-cluster-parameter-group-name lab-mode-enabled-56 --description 'TBD' $ aws rds describe-db-cluster-parameters \ --db-cluster-parameter-group-name lab-mode-enabled-56 \ --query '*[*].[ParameterName,ParameterValue]' \ --output text | grep aurora_lab_mode aurora_lab_mode 0 $ aws rds modify-db-cluster-parameter-group \ --db-cluster-parameter-group-name lab-mode-enabled-56 \ --parameters ParameterName=aurora_lab_mode,ParameterValue=1,ApplyMethod=pending-reboot { "DBClusterParameterGroupName": "lab-mode-enabled-56" } # Assign the custom parameter group to the cluster that's going to use fast DDL. $ aws rds modify-db-cluster --db-cluster-identifier tpch100g \ --db-cluster-parameter-group-name lab-mode-enabled-56 { "DBClusterIdentifier": "tpch100g", "DBClusterParameterGroup": "lab-mode-enabled-56", "Engine": "aurora", "EngineVersion": "5.6.mysql_aurora.1.22.2", "Status": "available" } # Reboot the primary instance for the cluster tpch100g: $ aws rds reboot-db-instance --db-instance-identifier instance-2020-12-22-5208 { "DBInstanceIdentifier": "instance-2020-12-22-5208", "DBInstanceStatus": "rebooting" } $ aws rds describe-db-clusters --db-cluster-identifier tpch100g \ --query '*[].[DBClusterParameterGroup]' --output text lab-mode-enabled-56 $ aws rds describe-db-cluster-parameters \ --db-cluster-parameter-group-name lab-mode-enabled-56 \ --query '*[*].{ParameterName:ParameterName,ParameterValue:ParameterValue}' \ --output text | grep aurora_lab_mode aurora_lab_mode 1

以下示例显示了参数组更改生效后的剩余步骤。它将测试 aurora_lab_mode 设置以确保集群可以使用快速 DDL。然后,它将运行 ALTER TABLE 语句以将列添加到另一个大型表的末尾。这一次,语句结束的非常快。

mysql> select @@aurora_lab_mode; +-------------------+ | @@aurora_lab_mode | +-------------------+ | 1 | +-------------------+ mysql> ALTER TABLE orders_fast_ddl ADD COLUMN o_refunded boolean; Query OK, 0 rows affected (1.51 sec) mysql> ALTER TABLE orders_fast_ddl ADD COLUMN o_coverletter varchar(512); Query OK, 0 rows affected (0.40 sec)