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

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

Amazon Aurora 包括就地(几乎是瞬时的)运行 ALTER TABLE 操作的优化。完成该操作无需复制表,对其他 DML 语句也没有重大影响。由于该操作不会在表复制中使用临时存储,因此,甚至可以对小型实例类中的大型表使用 DDL 语句。

Aurora MySQL 版本 3 与社群 MySQL 即时 DDL 功能兼容。Aurora MySQL 版本 2 使用称为快速 DDL 的不同实现。

即时 DDL(Aurora MySQL 版本 3)

Aurora MySQL 版本 3 为提高某些 DDL 操作效率而执行的优化称为即时 DDL。

Aurora MySQL 版本 3 与社群 MySQL 8.0 的即时 DDL 兼容。您可以将子句 ALGORITHM=INSTANTALTER TABLE 语句结合使用来执行即时 DDL 操作。有关即时 DDL 的语法和用法详细信息,请参阅 MySQL 文档中的 ALTER TABLE在线 DDL 操作

以下示例演示了即时 DDL 功能。ALTER TABLE 语句可以添加列以及更改默认列值。这些示例包括常规列和虚拟列,以及常规表和分区表。在每个步骤中,都可以通过发布 SHOW CREATE TABLEDESCRIBE 语句查看结果。

mysql> CREATE TABLE t1 (a INT, b INT, KEY(b)) PARTITION BY KEY(b) PARTITIONS 6; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20, -> ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t3 (a INT, b INT) PARTITION BY LIST(a)( -> PARTITION mypart1 VALUES IN (1,3,5), -> PARTITION MyPart2 VALUES IN (2,4,6) -> ); Query OK, 0 rows affected (0.03 sec) mysql> ALTER TABLE t3 ALTER COLUMN a SET DEFAULT 20, ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t4 (a INT, b INT) PARTITION BY RANGE(a) -> (PARTITION p0 VALUES LESS THAN(100), PARTITION p1 VALUES LESS THAN(1000), -> PARTITION p2 VALUES LESS THAN MAXVALUE); Query OK, 0 rows affected (0.05 sec) mysql> ALTER TABLE t4 ALTER COLUMN a SET DEFAULT 20, -> ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) /* Sub-partitioning example */ mysql> CREATE TABLE ts (id INT, purchased DATE, a INT, b INT) -> PARTITION BY RANGE( YEAR(purchased) ) -> SUBPARTITION BY HASH( TO_DAYS(purchased) ) -> SUBPARTITIONS 2 ( -> PARTITION p0 VALUES LESS THAN (1990), -> PARTITION p1 VALUES LESS THAN (2000), -> PARTITION p2 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.10 sec) mysql> ALTER TABLE ts ALTER COLUMN a SET DEFAULT 20, -> ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec)

快速 DDL(Aurora MySQL 版本 2)

在 MySQL 中,很多数据定义语言 (DDL) 操作会对性能产生明显影响。

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

  • 创建表的完整副本

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

  • 重建此表的所有索引

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

  • 减慢并发 DML 吞吐量

Aurora MySQL 版本 2 为提高某些 DDL 操作的效率而执行的优化称为快速 DDL。

在 Aurora MySQL 版本 3 中,Aurora 使用称为即时 DDL 的 MySQL 8.0 功能。Aurora MySQL 版本 2 使用称为快速 DDL 的不同实现。

重要

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

快速 DDL 限制

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

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

  • 快速 DDL 无法作用于分区表。

  • 快速 DDL 无法作用于使用 REDUNDANT 行格式的 InnoDB 表。

  • 快速 DDL 无法作用于具有全文搜索索引的表。

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

快速 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 会话并运行一些 AWS CLI 命令或使用 AWS 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

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

$ aws rds create-db-cluster-parameter-group \ --db-parameter-group-family aurora5.7 \ --db-cluster-parameter-group-name lab-mode-enabled-57 --description 'TBD' $ aws rds describe-db-cluster-parameters \ --db-cluster-parameter-group-name lab-mode-enabled-57 \ --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-57 \ --parameters ParameterName=aurora_lab_mode,ParameterValue=1,ApplyMethod=pending-reboot { "DBClusterParameterGroupName": "lab-mode-enabled-57" } # 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-57 { "DBClusterIdentifier": "tpch100g", "DBClusterParameterGroup": "lab-mode-enabled-57", "Engine": "aurora-mysql", "EngineVersion": "5.7.mysql_aurora.2.10.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-57 $ aws rds describe-db-cluster-parameters \ --db-cluster-parameter-group-name lab-mode-enabled-57 \ --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)