即时 DDL（Aurora MySQL 版本 3）快速 DDL（Aurora MySQL 版本 2）

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

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

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

主题

即时 DDL（Aurora MySQL 版本 3）
快速 DDL（Aurora MySQL 版本 2）

即时 DDL（Aurora MySQL 版本 3）

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

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

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


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）

Aurora MySQL 中的快速 DDL 是一种优化方法，旨在通过减少停机时间和资源使用来提高某些架构更改（例如添加或删除列）的性能。与传统的 DDL 方法相比，此方法可以更高效地完成这些操作。

重要

目前，您必须启用 Aurora 实验室模式才能使用快速 DDL。有关启用实验室模式的信息，请参阅 Amazon Aurora MySQL 实验室模式。

快速 DDL 优化最初在 Aurora MySQL 版本 2 的实验室模式中引入，用来提高某些 DDL 操作的效率。在 Aurora MySQL 版本 3 中，实验室模式停用，快速 DDL 已被 MySQL 8.0 Instant DDL 功能所取代。

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

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

创建表的完整副本
创建临时表，以处理并发数据操控语言 (DML) 操作
重建此表的所有索引
应用并发 DML 更改时应用表锁定
减慢并发 DML 吞吐量

在具有大型表或高事务量的环境中，这种性能影响可能尤其具有挑战性。快速 DDL 优化了架构更改，使得操作速度更快，耗费的资源更少，从而缓解这些挑战。

快速 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 会话并运行一些 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.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)

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

使用错误注入查询测试 Amazon Aurora MySQL

显示 Aurora 数据库集群的卷状态