本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 使用快速 DDL 更改 Amazon Aurora 中的資料表 Amazon Aurora 包括就地近乎執行 `ALTER TABLE` 作業的最佳化。此操作不需要複製資料表即可完成，且對其他 DML 陳述式沒有實質影響。由於此操作不會耗用複製資料表所需的暫存儲存體，即使是小型執行個體類別上的大型資料表，DDL 陳述式依然很實用。 Aurora MySQL 第 3 版與稱為即時 DLL 的社群 MySQL 8.0 功能相容。Aurora MySQL 第 2 版使用稱為快速 DDL 的不同實作。 **Topics** + [即時 DDL (Aurora MySQL 第 3 版)](#AuroraMySQL.mysql80-instant-ddl) + [快速 DDL (Aurora MySQL 第 2 版)](#AuroraMySQL.Managing.FastDDL-v2) ## 即時 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](https://dev.mysql.com/doc/refman/8.0/en/alter-table.html) 和[線上 DDL 操作](https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html)。 下列範例會示範即時 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 實驗室模式](AuroraMySQL.Updates.LabMode.md)。 快速 DDL 最佳化最初是在 Aurora MySQL 第 2 版的實驗室模式中推出，以增強特定 DDL 操作的效率。在 Aurora MySQL 第 3 版中，實驗室模式已停止使用，而 Fast DDL 已由 MySQL 8.0 即時 DDL 功能取代。 在 MySQL 中，許多資料定義語言 (DDL) 操作有明顯的效能影響。 例如，假設您使用 `ALTER TABLE` 操作來將資料欄新增至資料表。根據為操作指定的演算法，此操作可能牽涉下列： + 建立資料表的完整複本 + 建立暫存資料表來處理並行的資料處理語言 (DML) 操作 + 重新建置資料表的所有索引 + 套用並行 DML 變更時套用資料表鎖定 + 顯示並行的 DML 傳輸量 在具有大型資料表或高交易量的環境中，這種效能影響可能特別具有挑戰性。快速 DDL 可透過最佳化結構描述變更來協助緩解這些挑戰，從而實現更快速且較不耗資源的操作。 ### 快速 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 管理主控台。 ``` 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) ```