即時 DDL (Aurora MySQL 第 3 版)快速 DDL (Aurora MySQL 第 2 版)

使用快速 DDL 更改 Amazon Aurora 中的資料表

Amazon Aurora 包括就地近乎執行 ALTER TABLE 作業的最佳化。此操作不需要複製資料表即可完成，且對其他 DML 陳述式沒有實質影響。由於此操作不會耗用複製資料表所需的暫存儲存體，即使是小型執行個體類別上的大型資料表，DDL 陳述式依然很實用。

Aurora MySQL 第 3 版與稱為即時 DLL 的社群 MySQL 8.0 功能相容。Aurora MySQL 第 2 版使用稱為快速 DDL 的不同實作。

即時 DDL (Aurora MySQL 第 3 版)

由 Aurora MySQL 第 3 版執行以提高一些 DDL 作業效率的最佳化稱為即時 DDL。

Aurora MySQL 第 3 版與來自社群 MySQL 8.0 的即時 DLL 相容。您可以使用子句 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 版)

在 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 我SQL實驗室模式。

快速 DDL 限制

快速 DDL 目前具有下列限制：

快速 DDL 僅支援將不帶預設值、可為 Null 的資料欄新增至現有資料表的結尾。
快速 DDL 不適用於分割資料表。
快速 DDL 不適用使用 REDUNDANT 原始格式的 InnoDB 資料表。
快速 DDL 不適用於具有全文搜尋索引的資料表。
如果 DDL 操作最大可能的記錄大小太大，即不會使用快速 DDL。如果記錄大小大於頁面大小的一半便太大。記錄的最大大小是透過增加所有資料欄的最大大小來計算。根據 InnoDB 標準，針對變動大小的資料欄，extern 位元組不會併入在計算中。

快速 DDL 語法


ALTER TABLE tbl_name ADD COLUMN col_name column_definition

此陳述式使用下列選項：

tbl_name—要修改之資料表的名稱。
col_name—要新增之資料欄的名稱。
col_definition — 要新增之資料欄的定義。

注意
您必須指定不帶預設值、可為 Null 的資料欄定義。否則，將不會使用快速 DDL。

快速 DDL 範例

下面範例示範了透過快速 DDL 操作的加速。第一個 SQL 範例會在大型資料表上執行 ALTER TABLE 陳述式，而不使用快速 DDL。此操作需要大量的時間。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)

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

測試 Amazon Aurora 我SQL使用故障注入查詢

顯示 Aurora 資料庫叢集的磁碟區狀態