MySQL :: MySQL 8.4 参考手册 :: 25.6.12 NDB Cluster 中 ALTER TABLE 的在线操作

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 25 MySQL NDB 集群 8.4 / 25.6 NDB Cluster 的管理 / 25.6.12 NDB Cluster 中 ALTER TABLE 的在线操作

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

25.6.12 使用 ALTER TABLE 在 NDB 集群中进行在线操作25.6.12 Online Operations with ALTER TABLE in NDB Cluster

MySQL NDB 集群支持使用 ALTER TABLE ... ALGORITHM=DEFAULT|INPLACE|COPY 在线更改表结构。NDB 集群按照接下来的几段描述处理 COPY 和 INPLACE。MySQL NDB Cluster supports online table schema changes using ALTER TABLE ... ALGORITHM=DEFAULT|INPLACE|COPY. NDB Cluster handles COPY and INPLACE as described in the next few paragraphs.

对于 ALGORITHM=COPY，mysqld NDB 集群处理程序执行以下操作：For ALGORITHM=COPY, the mysqld NDB Cluster handler performs the following actions:

通知数据节点创建一个空表的副本，并对该副本进行所需的模式更改。Tells the data nodes to create an empty copy of the table, and to make the required schema changes to this copy.
从原始表中读取行，并将其写入副本。Reads rows from the original table, and writes them to the copy.
告诉数据节点删除原始表，然后重命名副本。Tells the data nodes to drop the original table and then to rename the copy.

We sometimes refer to this as a “copying”或者“offline” ALTER TABLE.We sometimes refer to this as a “copying” or “offline” ALTER TABLE.

在复制ALTER TABLE的同时，不允许执行 DML 操作。DML operations are not permitted concurrently with a copying ALTER TABLE.

执行复制 ALTER TABLE 语句的 mysqld 会获取元数据锁，但这仅在该 mysqld 上有效。其他 NDB 客户端在执行复制 ALTER TABLE 时可以修改行数据，从而导致不一致。The mysqld on which the copying ALTER TABLE statement is issued takes a metadata lock, but this is in effect only on that mysqld. Other NDB clients can modify row data during a copying ALTER TABLE, resulting in inconsistency.

对于 ALGORITHM=INPLACE，NDB 集群处理程序会告诉数据节点进行所需更改，而不执行任何数据复制。For ALGORITHM=INPLACE, the NDB Cluster handler tells the data nodes to make the required changes, and does not perform any copying of data.

We also refer to this as a “non-copying”或者“online” ALTER TABLE.We also refer to this as a “non-copying” or “online” ALTER TABLE.

非复制 ALTER TABLE 允许并发的 DML 操作。A non-copying ALTER TABLE allows concurrent DML operations.

ALGORITHM=INSTANT 不被 NDB 8.4 支持。ALGORITHM=INSTANT is not supported by NDB 8.4.

无论使用哪种算法，mysqld 在执行 ALTER TABLE 时会获取全局模式锁 (GSL)；这会阻止在集群中的此 SQL 节点或其他 SQL 节点上同时执行任何（其他）DDL 操作或备份。通常这不会是问题，除非 ALTER TABLE 花费的时间非常长。Regardless of the algorithm used, the mysqld takes a Global Schema Lock (GSL) while executing ALTER TABLE; this prevents execution of any (other) DDL or backups concurrently on this or any other SQL node in the cluster. This is normally not problematic, unless the ALTER TABLE takes a very long time.

Note

一些较旧版本的 NDB Cluster 使用了特定于 NDB 的在线 ALTER TABLE 操作语法。该语法已被移除。Some older releases of NDB Cluster used a syntax specific to NDB for online ALTER TABLE operations. That syntax has since been removed.

在 NDB 表的可变宽度列上添加和删除索引的操作是在线进行的。在线操作是非复制的，也就是说，它们不需要重新创建索引。它们不会锁定正在被更改的表，从而阻止其他 NDB 集群的 API 节点访问（但请参见本节后面的 NDB 在线操作的限制）。在具有多个 API 节点的 NDB 集群中，这类操作不需要单用户模式来更改 NDB 表；在在线 DDL 操作期间，事务可以继续不中断地运行。Operations that add and drop indexes on variable-width columns of NDB tables occur online. Online operations are noncopying; that is, they do not require that indexes be re-created. They do not lock the table being altered from access by other API nodes in an NDB Cluster (but see Limitations of NDB online operations, later in this section). Such operations do not require single user mode for NDB table alterations made in an NDB cluster with multiple API nodes; transactions can continue uninterrupted during online DDL operations.

ALGORITHM=INPLACE 可用于在 NDB 表上执行在线 ADD COLUMN、ADD INDEX（包括 CREATE INDEX 语句）和 DROP INDEX 操作。还支持 NDB 表的在线重命名。ALGORITHM=INPLACE can be used to perform online ADD COLUMN, ADD INDEX (including CREATE INDEX statements), and DROP INDEX operations on NDB tables. Online renaming of NDB tables is also supported.

基于磁盘的列无法在线添加到NDB表中。这意味着，如果您希望向使用表级STORAGE DISK选项的NDB表添加内存列，则必须明确声明新列使用基于内存的存储。例如——假设您已经创建了表空间ts1——假设您按如下方式创建表t1：Disk-based columns cannot be added to NDB tables online. This means that, if you wish to add an in-memory column to an NDB table that uses a table-level STORAGE DISK option, you must declare the new column as using memory-based storage explicitly. For example—assuming that you have already created tablespace ts1—suppose that you create table t1 as follows:

mysql> CREATE TABLE t1 (
     >     c1 INT NOT NULL PRIMARY KEY,
     >     c2 VARCHAR(30)
     >     )
     >     TABLESPACE ts1 STORAGE DISK
     >     ENGINE NDB;
Query OK, 0 rows affected (1.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

您可以按如下方式在线向此表添加新的内存列：You can add a new in-memory column to this table online as shown here:

mysql> ALTER TABLE t1
     >     ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC STORAGE MEMORY,
     >     ALGORITHM=INPLACE;
Query OK, 0 rows affected (1.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果省略STORAGE MEMORY选项，则此语句会失败：This statement fails if the STORAGE MEMORY option is omitted:

mysql> ALTER TABLE t1
     >     ADD COLUMN c4 INT COLUMN_FORMAT DYNAMIC,
     >     ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason:
Adding column(s) or add/reorganize partition not supported online. Try
ALGORITHM=COPY.

如果省略了 COLUMN_FORMAT DYNAMIC 选项，则会自动使用动态列格式，但会发出警告，如下所示：If you omit the COLUMN_FORMAT DYNAMIC option, the dynamic column format is employed automatically, but a warning is issued, as shown here:

mysql> ALTER ONLINE TABLE t1 ADD COLUMN c4 INT STORAGE MEMORY;
Query OK, 0 rows affected, 1 warning (1.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1478
Message: DYNAMIC column c4 with STORAGE DISK is not supported, column will
become FIXED


mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) NOT NULL,
  `c2` varchar(30) DEFAULT NULL,
  `c3` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL,
  `c4` int(11) /*!50606 STORAGE MEMORY */ DEFAULT NULL,
  PRIMARY KEY (`c1`)
) /*!50606 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.03 sec)

Note

STORAGE 和 COLUMN_FORMAT 关键字仅在 NDB Cluster 中受支持；在 MySQL 的任何其他版本中，如果尝试在 CREATE TABLE 或 ALTER TABLE 语句中使用这些关键字，将导致错误。The STORAGE and COLUMN_FORMAT keywords are supported only in NDB Cluster; in any other version of MySQL, attempting to use either of these keywords in a CREATE TABLE or ALTER TABLE statement results in an error.

It is also possible to use the statement ALTER TABLE ... REORGANIZE PARTITION, ALGORITHM=INPLACE with no partition_names INTO (partition_definitions) option on NDB tables. This can be used to redistribute NDB Cluster data among new data nodes that have been added to the cluster online. This does 在多线程系统中 perform any defragmentation, which requires an OPTIMIZE TABLE or null ALTER TABLE statement. For more information, see Section 25.6.7, “Adding NDB Cluster Data Nodes Online”.It is also possible to use the statement ALTER TABLE ... REORGANIZE PARTITION, ALGORITHM=INPLACE with no partition_names INTO (partition_definitions) option on NDB tables. This can be used to redistribute NDB Cluster data among new data nodes that have been added to the cluster online. This does not perform any defragmentation, which requires an OPTIMIZE TABLE or null ALTER TABLE statement. For more information, see Section 25.6.7, “Adding NDB Cluster Data Nodes Online”.

NDB 在线操作的限制Limitations of NDB online operations

不支持在线 DROP COLUMN 操作。Online DROP COLUMN operations are not supported.

在线 ALTER TABLE、CREATE INDEX 或 DROP INDEX 语句中添加列或添加/删除索引受以下限制：Online ALTER TABLE, CREATE INDEX, or DROP INDEX statements that add columns or add or drop indexes are subject to the following limitations:

给定的在线 ALTER TABLE 语句只能使用 ADD COLUMN、ADD INDEX 或 DROP INDEX 中的一种。单条语句中可以在线添加一个或多个列；单条语句中只能在线创建或删除一个索引。A given online ALTER TABLE can use only one of ADD COLUMN, ADD INDEX, or DROP INDEX. One or more columns can be added online in a single statement; only one index may be created or dropped online in a single statement.
被修改的表对于除正在执行在线 ALTER TABLE ADD COLUMN、ADD INDEX 或 DROP INDEX 操作（或 CREATE INDEX 或 DROP INDEX 语句）的 API 节点之外的其他 API 节点没有锁定。然而，在执行在线操作期间，该表对于来自同一 API 节点的任何其他操作是锁定的。The table being altered is not locked with respect to API nodes other than the one on which an online ALTER TABLE ADD COLUMN, ADD INDEX, or DROP INDEX operation (or CREATE INDEX or DROP INDEX statement) is run. However, the table is locked against any other operations originating on the same API node while the online operation is being executed.
要修改的表必须有明确的主键；由 NDB 存储引擎创建的隐藏主键不足以用于此目的。The table to be altered must have an explicit primary key; the hidden primary key created by the NDB storage engine is not sufficient for this purpose.
表使用的存储引擎无法在线更改。The storage engine used by the table cannot be changed online.
表使用的表空间无法在线更改。像 ALTER TABLE ndb_table ... ALGORITHM=INPLACE, TABLESPACE=new_tablespace 这样的语句是被明确禁止的。The tablespace used by the table cannot be changed online. Statements such as ALTER TABLE ndb_table ... ALGORITHM=INPLACE, TABLESPACE=new_tablespace are specifically disallowed.
当与 NDB Cluster 磁盘表一起使用时，无法在线更改列的存储类型（DISK 或 MEMORY）。这意味着，当以在线方式添加或删除索引，并且您希望更改列的存储类型时，必须在添加或删除索引的语句中使用 ALGORITHM=COPY。When used with NDB Cluster Disk Data tables, it is not possible to change the storage type (DISK or MEMORY) of a column online. This means, that when you add or drop an index in such a way that the operation would be performed online, and you want the storage type of the column or columns to be changed, you must use ALGORITHM=COPY in the statement that adds or drops the index.

在线添加的列不能使用 BLOB 或 TEXT 类型，并且必须满足以下条件：Columns to be added online cannot use the BLOB or TEXT type, and must meet the following criteria:

列必须是动态的；也就是说，必须可以使用 COLUMN_FORMAT DYNAMIC 来创建它们。如果省略 COLUMN_FORMAT DYNAMIC 选项，将会自动使用动态列格式。The columns must be dynamic; that is, it must be possible to create them using COLUMN_FORMAT DYNAMIC. If you omit the COLUMN_FORMAT DYNAMIC option, the dynamic column format is employed automatically.

列必须允许 NULL 值，并且除了 NULL 之外不能有任何显式默认值。在线添加的列会自动创建为 DEFAULT NULL，如下所示：The columns must permit NULL values and not have any explicit default value other than NULL. Columns added online are automatically created as DEFAULT NULL, as can be seen here:

mysql> CREATE TABLE t2 (
     >     c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
     >     ) ENGINE=NDB;
Query OK, 0 rows affected (1.44 sec)

mysql> ALTER TABLE t2
     >     ADD COLUMN c2 INT,
     >     ADD COLUMN c3 INT,
     >     ALGORITHM=INPLACE;
Query OK, 0 rows affected, 2 warnings (0.93 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t2` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

列必须在现有列之后添加。如果尝试在任何现有列之前在线添加列，或使用 FIRST 关键字，语句将以错误告终。The columns must be added following any existing columns. If you attempt to add a column online before any existing columns or using the FIRST keyword, the statement fails with an error.
现有表列不能在线重新排序。Existing table columns cannot be reordered online.

对于 ALTER TABLE 在 NDB 表上的在线操作，当固定格式列被在线添加，或者在线创建或删除索引时，它们会被转换为动态，如下所示（为了清晰起见，这里重复显示了刚才的 CREATE TABLE 和 ALTER TABLE 语句）：For online ALTER TABLE operations on NDB tables, fixed-format columns are converted to dynamic when they are added online, or when indexes are created or dropped online, as shown here (repeating the CREATE TABLE and ALTER TABLE statements just shown for the sake of clarity):

mysql> CREATE TABLE t2 (
     >     c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
     >     ) ENGINE=NDB;
Query OK, 0 rows affected (1.44 sec)

mysql> ALTER TABLE t2
     >     ADD COLUMN c2 INT,
     >     ADD COLUMN c3 INT,
     >     ALGORITHM=INPLACE;
Query OK, 0 rows affected, 2 warnings (0.93 sec)

mysql> SHOW WARNINGS;
*************************** 1. row ***************************
  Level: Warning
   Code: 1478
Message: Converted FIXED field 'c2' to DYNAMIC to enable online ADD COLUMN
*************************** 2. row ***************************
  Level: Warning
   Code: 1478
Message: Converted FIXED field 'c3' to DYNAMIC to enable online ADD COLUMN
2 rows in set (0.00 sec)

只有将要在线添加的列必须是动态的。现有列不必是动态的；这包括表的主键，它也可以是 FIXED，如下面所示：Only the column or columns to be added online must be dynamic. Existing columns need not be; this includes the table's primary key, which may also be FIXED, as shown here:

mysql> CREATE TABLE t3 (
     >     c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED
     >     ) ENGINE=NDB;
Query OK, 0 rows affected (2.10 sec)

mysql> ALTER TABLE t3 ADD COLUMN c2 INT, ALGORITHM=INPLACE;
Query OK, 0 rows affected, 1 warning (0.78 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW WARNINGS;
*************************** 1. row ***************************
  Level: Warning
   Code: 1478
Message: Converted FIXED field 'c2' to DYNAMIC to enable online ADD COLUMN
1 row in set (0.00 sec)

通过重命名操作，列不会从 FIXED 转换为 DYNAMIC 列格式。有关 COLUMN_FORMAT 的更多信息，请参见第 15.1.20 节，“CREATE TABLE 语句”。Columns are not converted from FIXED to DYNAMIC column format by renaming operations. For more information about COLUMN_FORMAT, see Section 15.1.20, “CREATE TABLE Statement”.

KEY、CONSTRAINT 和 IGNORE 关键字在使用 ALGORITHM=INPLACE 的 ALTER TABLE 语句中受支持。The KEY, CONSTRAINT, and IGNORE keywords are supported in ALTER TABLE statements using ALGORITHM=INPLACE.

通过在线 ALTER TABLE 语句将 MAX_ROWS 设置为 0 是不允许的。必须使用复制 ALTER TABLE 来执行此操作。（Bug #21960004）Setting MAX_ROWS to 0 using an online ALTER TABLE statement is disallowed. You must use a copying ALTER TABLE to perform this operation. (Bug #21960004)

首页上一级下一节