DDL operation guide for PolarDB for MySQL - PolarDB - Alibaba Cloud Documentation Center

This topic is based on the cloud-native database PolarDB for MySQL and describes common DDL operations, helping you understand DDL behavior, evaluate the risks of DDL operations, and reduce the impact on your workloads.

Overview of DDL operations

In the MySQL ecosystem, DDL is a very complex category of operations, including index operations, primary key operations, column operations, table operations, foreign key operations, generated column operations, and many other types. DDL operations are not only time-consuming and resource-intensive, but also involve table locking. Improper operations may affect normal workloads and cause catastrophic impact.

PolarDB for MySQL has a DDL module that has made significant progress in performance and lock stability through years of experience accumulation and continuous feature iterations. This topic describes the behavior characteristics of common DDL operations in each version of PolarDB for MySQL from the following aspects:

Whether the table is locked (concurrent DML allowed): A non-locking DDL (Online DDL) requests the table mutex lock only when modifying metadata (generally lasting no more than 1 second). Reads and writes on the target table are allowed during the schema change, which improves response speed and availability in production environments. In contrast, statements that do not support Online DDL lock the table for the entire duration and do not support concurrent writes. If such a DDL operation lasts a long time, it may significantly impact business operations.
Whether the table is rebuilt (duration): Such a DDL needs to recreate the primary key and all secondary indexes according to the new table schema and usually takes a long time.

Note
Because PolarDB for MySQL supports the parallel DDL feature, the performance of running DDL with the kernel is much better than using third-party tools such as gh-ost or pt-osc.
Whether only metadata is modified (completes in seconds): Only metadata is modified and table data does not need to be modified. The execution time of such DDL operations does not increase with the table size and generally completes within seconds.
Whether parallel DDL is supported (multi-thread acceleration): For scenarios such as creating indexes or rebuilding tables on large tables, PolarDB supports parallel DDL, which uses multi-threading to improve DDL execution efficiency by up to 15 to 20 times. For more information, see Parallel DDL.
Whether performance is affected (lock-free change): Perform lockless schema changes using lockless change tickets does not cause service interruptions and does not affect your workloads during off-peak hours.

Note
We recommend that you perform this operation during off-peak hours. This operation may cause IOPS and CPU usage to increase.

DDL execution algorithms

PolarDB for MySQL supports the following three DDL execution algorithms:

INSTANT algorithm: When the INSTANT algorithm is used to run a DDL operation, only the metadata in the data dictionary needs to be modified. Existing data does not need to be modified or copied, and the table does not need to be rebuilt. Therefore, it is not affected by the table size, and the entire DDL process can complete within seconds.
INPLACE algorithm: When the INPLACE algorithm is used, the copying and rebuilding of data in the table are completed inside the engine, so the execution is faster. Meanwhile, the vast majority of DDL operations executed with the INPLACE algorithm allow concurrent reads and writes and have little impact on workloads. In addition, some DDL operations executed with the INPLACE algorithm, such as RENAME TABLE, ADD COMMENT, can modify only metadata without modifying data in the table and complete within seconds.
COPY algorithm: When the COPY algorithm is used to run a DDL operation, all data in the table must be copied to a new table. During the data copy, an SNW (SHARED_NO_WRITE) lock is held on the original table. Therefore, only read operations are supported and concurrent writes are not allowed during the DDL execution, which has a significant impact on workloads.

DDL operations that allow concurrent reads and writes are collectively called Online DDL. Online DDL has relatively little impact on workloads. In most cases, you do not need to manually specify the algorithm used by a DDL operation. PolarDB automatically selects the optimal algorithm in the order of INSTANT, INPLACE, and COPY. In addition, you can use the ALTER TABLE statement's ALGORITHM and LOCK clauses to fine-tune the behavior of a DDL operation:

ALGORITHM clause: To run a DDL statement with a specified algorithm, you can specify the ALGORITHM field. Valid values are DEFAULT, INSTANT, INPLACE, and COPY. If the DDL operation does not support the specified algorithm, an error is returned immediately.
LOCK clause: The LOCK clause is used to adjust concurrent access to the table during DDL execution. You can use the LOCK clause to control the concurrent read and write level while the table is being modified. The configurable options and their meanings are as follows:
- DEFAULT: The kernel allows the maximum degree of concurrent reads and writes based on the DDL type.
- NONE: Concurrent reads and writes are allowed during DDL execution. An error is returned if this is not supported.
- SHARED: Concurrent reads are allowed but writes are blocked. An error is returned if concurrent reads are not supported.
- EXCLUSIVE: All concurrent reads and writes are prohibited during DDL execution.

To prevent the table from becoming inaccessible while executing ALTER TABLE, you can specify the LOCK clause in the ALTER TABLE statement. That is, if the lock behavior during DDL execution does not meet the specified condition, the operation is stopped immediately.

Preview DDL execution characteristics with EXPLAIN DDL

This topic lists the execution characteristics of common DDL operations. However, because PolarDB provides rich features, the actual DDL execution behavior may be affected by multiple factors, such as the structure of the target table, the parameter settings of the instance, and whether specific features are enabled.

To improve the safety and predictability of schema changes, we strongly recommend that you use the EXPLAIN DDL feature to learn about the execution characteristics of a DDL operation before running a complex DDL operation.

You can use the following syntax to view the execution characteristics of a DDL operation:

{ EXPLAIN | DESCRIBE | DESC } ALTER TABLE ...

With EXPLAIN DDL, you can obtain the following key information:

Whether the current DDL operation can be executed successfully.
The type of algorithm used by the current DDL operation. Valid values: INSTANT, INPLACE and COPY.
Whether the current DDL operation needs to rebuild the data of the entire table. A full table rebuild usually takes a long time.
Whether concurrent DML operations are allowed during the current DDL execution.
Whether the current DDL operation will be blocked by uncommitted transactions.
Whether the current DDL operation supports parallel DDL acceleration. If supported, the degree of parallelism of the current DDL operation is displayed.

By understanding this information in advance, you can assess the impact of a DDL operation more accurately, choose an appropriate execution time, and effectively avoid issues such as table locking and long execution time that affect online workloads. For more information, see EXPLAIN DDL.

DDL behavior characteristics

Index operations

PolarDB for MySQL 8.0.2

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Creating a secondary index	Yes	No	No	Supported
Dropping a secondary index	Yes	No	Yes	N/A
Renaming a secondary index	Yes	No	Yes	N/A
Adding a FULLTEXT index	No	No Note When you add the first full-text index to a table, an additional table rebuild is triggered if there is no user-defined FTS_DOC_ID column.	No	Not supported
Adding a SPATIAL index	No	No	No	Not supported

PolarDB for MySQL 8.0.1

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Creating a secondary index	Yes	No	No	Supported
Dropping a secondary index	Yes	No	Yes	N/A
Renaming a secondary index	Yes	No	Yes	N/A
Adding a FULLTEXT index	No	No Note When you add the first full-text index to a table, an additional table rebuild is triggered if there is no user-defined FTS_DOC_ID column.	No	Not supported
Adding a SPATIAL index	No	No	No	Not supported

PolarDB for MySQL 5.7

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Creating a secondary index	Yes	No	No	Supported
Dropping a secondary index	Yes	No	Yes	N/A
Renaming a secondary index	Yes	No	Yes	N/A
Adding a FULLTEXT index	No	No Note When you add the first full-text index to a table, an additional table rebuild is triggered if there is no user-defined FTS_DOC_ID column.	No	Not supported
Adding a SPATIAL index	No	No	No	Not supported

PolarDB for MySQL 5.6

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Creating a secondary index	Yes	No	No	Not supported
Dropping a secondary index	Yes	No	Yes	N/A
Adding a FULLTEXT index	No	No Note When you add the first full-text index to a table, an additional table rebuild is triggered if there is no user-defined FTS_DOC_ID column.	No	Not supported
Adding a SPATIAL index	No	No	No	Not supported

Primary key operations

PolarDB for MySQL 8.0.2

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a primary key	Yes	Yes	No	Supported
Dropping a primary key	No	Yes	No	Not supported
Dropping the existing primary key and adding a new primary key	Yes	Yes	No	Supported

PolarDB for MySQL 8.0.1

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a primary key	Yes	Yes	No	Supported
Dropping a primary key	No	Yes	No	Not supported
Dropping the existing primary key and adding a new primary key	Yes	Yes	No	Supported

PolarDB for MySQL 5.7

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a primary key	Yes	Yes	No	Supported
Dropping a primary key	No	Yes	No	Not supported
Dropping the existing primary key and adding a new primary key	Yes	Yes	No	Supported

PolarDB for MySQL 5.6

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a primary key	Yes	Yes	No	Not supported
Dropping a primary key	No	Yes	No	Not supported
Dropping the existing primary key and adding a new primary key	Yes	Yes	No	Not supported

Note

In the following scenarios, concurrent DML is allowed only when the cluster parameter sql_mode contains STRICT_TRANS_TABLES or STRICT_ALL_TABLES:

Adding a primary key
Dropping the existing primary key and adding a new primary key

Column operations

PolarDB for MySQL 8.0.2

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a column	Yes	No¹	Yes¹	Supported¹
Dropping a column	Yes	Yes	No	Supported
Renaming a column	Yes	No	Yes	N/A
Reordering columns	Yes	Yes	No	Supported
Setting the default value of a column	Yes	No	Yes	N/A
Changing a column comment	Yes	No	Yes	N/A
Changing a column type	No	Yes	No	Not supported
Extending the length of a VARCHAR column	Yes²	No	Yes	N/A
Changing the character set from UTF8mb3 to UTF8mb4	No	No³	Yes³	Not supported
Dropping the default value of a column	Yes	No	Yes	N/A
Changing the auto-increment value	Yes	No	Yes	N/A
Changing a column to NULL	Yes	Yes	No	Supported
Changing a column to NOT NULL	No	Yes	No	Not supported
Modifying the definition of an ENUM/SET column	Yes	No	Yes⁴	N/A

Instant column addition at the end of a table feature supports adding columns only to the end of the table. If the table has no explicit primary key, set the implicit_primary_key parameter to OFF to prevent the add column operation from failing due to the implicit primary key column at the end of the table. In addition, instant add column is not supported on compressed tables (ROW_FORMAT=COMPRESSED), tables with full-text indexes, or temporary tables. If the cluster does not support the instant add column feature, adding a column uses the INPLACE algorithm to run the DDL, which requires a full table rebuild. Concurrent reads and writes are allowed during the rebuild. You can also use the parallel DDL feature to accelerate the operation.
When extending the length of a VARCHAR column, the number of bytes required to store the length of the VARCHAR column must remain consistent for fast column extension to be supported. Specifically, a VARCHAR column of 0 to 255 bytes requires only one byte to store the length, whereas a VARCHAR column of 256 bytes or more requires two bytes. Only when you keep the extension of the VARCHAR length within the same byte range, for example within 0 to 255 bytes or from 256 bytes to a larger range, can the execution of ALTER TABLE modify only metadata. That is, when you extend the length of a VARCHAR column from less than 256 bytes to 256 bytes or more, PolarDB uses Copy DDL by default, which locks the table for the entire duration. DML write operations are not supported and only read operations are allowed.

If you are not sure whether the range of your VARCHAR modification meets the preceding conditions, you can use ALGORITHM=INPLACE to specify the INPLACE algorithm for the current DDL operation. In this case, if fast column extension is not supported, an error is reported directly. Example:
```
ALTER TABLE table_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);

ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
VARCHAR is a variable-length storage type, and the disk stores only the actual length. Therefore, when using VARCHAR fields, we recommend that you consider setting the maximum length directly to 256 bytes or more to avoid the possible use of the COPY algorithm when extending the field.
When the following conditions are met, changing the character set of a column from UTF8mb3 to UTF8mb4 modifies only metadata and does not modify data. Otherwise, the COPY algorithm is required to rebuild the table, and the table is locked for the entire rebuild duration. The target table can only be read and writes are not allowed.
- The column type is CHAR, VARCHAR, ENUM, or TEXT.
- No index exists on the modified column.
- The maximum storage length of the column is less than 256 bytes or greater than 255 bytes both before and after the character set conversion.
You can specify ALGORITHM=INPLACE to force the DDL to run without rebuilding the table. If the operation requires the COPY algorithm to run, an error is returned immediately. Example:
```
ALTER TABLE test modify column b char(1) CHARACTER SET utf8mb4 default null,algorithm = inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
Only when the storage size of the data type does not change and elements are appended to the end of the ENUM or SET can the operation modify only metadata without rebuilding the entire table. Otherwise, the COPY algorithm is required to rebuild the table.

PolarDB for MySQL 8.0.1

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a column	Yes	No¹	Yes¹	Supported¹
Dropping a column	Yes	Yes	No	Supported
Renaming a column	Yes	No	Yes	N/A
Reordering columns	Yes	Yes	No	Supported
Setting the default value of a column	Yes	No	Yes	N/A
Changing a column comment	Yes	No	Yes	N/A
Changing a column type	No	Yes	No	Not supported
Extending the length of a VARCHAR column	Yes²	No	Yes	N/A
Changing the character set from UTF8mb3 to UTF8mb4	No	No³	Yes³	Not supported
Dropping the default value of a column	Yes	No	Yes	N/A
Changing the auto-increment value	Yes	No	Yes	N/A
Changing a column to NULL	Yes	Yes	No	Supported
Changing a column to NOT NULL	No	Yes	No	Not supported
Modifying the definition of an ENUM/SET column	Yes	No	Yes⁴	N/A

Instant column addition at the end of a table feature supports adding columns only to the end of the table. If the table has no explicit primary key, set the implicit_primary_key parameter to OFF to prevent the add column operation from failing due to the implicit primary key column at the end of the table. In addition, instant add column is not supported on compressed tables (ROW_FORMAT=COMPRESSED), tables with full-text indexes, or temporary tables. If the cluster does not support the instant add column feature, adding a column uses the INPLACE algorithm to run the DDL, which requires a full table rebuild. Concurrent reads and writes are allowed during the rebuild. You can also use the parallel DDL feature to accelerate the operation.

In addition, if your table has an In-Memory Column Index (IMCI), instant add column is not supported on the table because adding a column requires rebuilding the IMCI. You can set the loose_imci_enable_add_column_instant_ddl parameter to ON to enable instant add column. In this case, PolarDB rebuilds the IMCI asynchronously in the background. The IMCI is temporarily unavailable during the rebuild. For more information, see Execute DDL statements to dynamically create and delete an IMCI.
When extending the length of a VARCHAR column, the number of bytes required to store the length of the VARCHAR column must remain consistent for fast column extension to be supported. Specifically, a VARCHAR column of 0 to 255 bytes requires only one byte to store the length, whereas a VARCHAR column of 256 bytes or more requires two bytes. Only when you keep the extension of the VARCHAR length within the same byte range, for example within 0 to 255 bytes or from 256 bytes to a larger range, can the execution of ALTER TABLE modify only metadata. That is, when you extend the length of a VARCHAR column from less than 256 bytes to 256 bytes or more, PolarDB uses Copy DDL by default, which locks the table for the entire duration. DML write operations are not supported and only read operations are allowed.

If you are not sure whether the range of your VARCHAR modification meets the preceding conditions, you can use ALGORITHM=INPLACE to specify the INPLACE algorithm for the current DDL operation. In this case, if fast column extension is not supported, an error is reported directly. Example:
```
ALTER TABLE table_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);

ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
VARCHAR is a variable-length storage type, and the disk stores only the actual length. Therefore, when using VARCHAR fields, we recommend that you consider setting the maximum length directly to 256 bytes or more to avoid the possible use of the COPY algorithm when extending the field.
When the loose_innodb_support_instant_modify_charset parameter is set to ON, and the following conditions are met, changing the character set of a column from UTF8mb3 to UTF8mb4 modifies only metadata and does not modify data. Otherwise, the COPY algorithm is required to rebuild the table, and the table is locked for the entire rebuild duration. The target table can only be read and writes are not allowed.
- The column type is CHAR, VARCHAR, ENUM, or TEXT.
- No index exists on the modified column.
- The maximum storage length of the column is less than 256 bytes or greater than 255 bytes both before and after the character set conversion.
You can specify ALGORITHM=INPLACE to force the DDL to run without rebuilding the table. If the operation requires the COPY algorithm to run, an error is returned immediately. Example:
```
ALTER TABLE test modify column b char(1) CHARACTER SET utf8mb4 default null,algorithm = inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
Only when the storage size of the data type does not change and elements are appended to the end of the ENUM or SET can the operation modify only metadata without rebuilding the entire table. Otherwise, the COPY algorithm is required to rebuild the table.

PolarDB for MySQL 5.7

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a column	Yes	No¹	Yes¹	Supported¹
Dropping a column	Yes	Yes	No	Supported
Renaming a column	Yes	No	Yes	N/A
Reordering columns	Yes	Yes	No	Supported
Setting the default value of a column	Yes	No	Yes	N/A
Changing a column comment	Yes	No	Yes	N/A
Changing a column type	No	Yes	No	Not supported
Extending the length of a VARCHAR column	Yes²	No	Yes	N/A
Changing the character set from UTF8mb3 to UTF8mb4	No	Yes	No	Not supported
Dropping the default value of a column	Yes	No	Yes	N/A
Changing the auto-increment value	Yes	No	Yes	N/A
Changing a column to NULL	Yes	Yes	No	Supported
Changing a column to NOT NULL	No	Yes	No	Not supported
Modifying the definition of an ENUM/SET column	Yes	No	Yes³	N/A

To enable the Instant column addition at the end of a table feature, set the loose_innodb_support_instant_add_column parameter to ON parameter to ON. Only adding columns to the end of the table is supported. If the table has no explicit primary key, set the implicit_primary_key parameter to OFF to prevent the add column operation from failing due to the implicit primary key column at the end of the table. In addition, instant add column is not supported on compressed tables (ROW_FORMAT=COMPRESSED), tables with full-text indexes, or temporary tables. If the cluster does not support the instant add column feature, adding a column uses the INPLACE algorithm to run the DDL, which requires a full table rebuild. Concurrent reads and writes are allowed during the rebuild. You can also use the parallel DDL feature to accelerate the operation.
When extending the length of a VARCHAR column, the number of bytes required to store the length of the VARCHAR column must remain consistent for fast column extension to be supported. Specifically, a VARCHAR column of 0 to 255 bytes requires only one byte to store the length, whereas a VARCHAR column of 256 bytes or more requires two bytes. Only when you keep the extension of the VARCHAR length within the same byte range, for example within 0 to 255 bytes or from 256 bytes to a larger range, can the execution of ALTER TABLE modify only metadata. That is, when you extend the length of a VARCHAR column from less than 256 bytes to 256 bytes or more, PolarDB uses Copy DDL by default, which locks the table for the entire duration. DML write operations are not supported and only read operations are allowed.

If you are not sure whether the range of your VARCHAR modification meets the preceding conditions, you can use ALGORITHM=INPLACE to specify the INPLACE algorithm for the current DDL operation. In this case, if fast column extension is not supported, an error is reported directly. Example:
```
ALTER TABLE table_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);

ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
VARCHAR is a variable-length storage type, and the disk stores only the actual length. Therefore, when using VARCHAR fields, we recommend that you consider setting the maximum length directly to 256 bytes or more to avoid the possible use of the COPY algorithm when extending the field.
Only when the storage size of the data type does not change and elements are appended to the end of the ENUM or SET can the operation modify only metadata without rebuilding the entire table. Otherwise, the COPY algorithm is required to rebuild the table.

PolarDB for MySQL 5.6

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a column	Yes	No¹	Yes¹	Not supported
Dropping a column	Yes	Yes	No	Not supported
Renaming a column	Yes	No	Yes	N/A
Reordering columns	Yes	Yes	No	Not supported
Setting the default value of a column	Yes	No	Yes	N/A
Changing a column comment	Yes	No	Yes	N/A
Changing a column type	No	Yes	No	Not supported
Extending the length of a VARCHAR column	No	Yes	No	Not supported
Changing the character set from UTF8mb3 to UTF8mb4	No	Yes	No	Not supported
Dropping the default value of a column	Yes	No	Yes	N/A
Changing the auto-increment value	Yes	No	Yes	N/A
Changing a column to NULL	Yes	Yes	No	Not supported
Changing a column to NOT NULL	No	Yes	No	Not supported
Modifying the definition of an ENUM/SET column	Yes	No	Yes²	N/A

To enable the Instant column addition at the end of a table feature, set the loose_innodb_support_instant_add_column parameter to ON parameter to ON. Only adding columns to the end of the table is supported. If the table has no explicit primary key, set the implicit_primary_key parameter to OFF to prevent the add column operation from failing due to the implicit primary key column at the end of the table. In addition, instant add column is not supported on compressed tables (ROW_FORMAT=COMPRESSED), tables with full-text indexes, temporary tables, or partitioned tables. If the cluster does not support the instant add column feature, adding a column uses the INPLACE algorithm to run the DDL, which requires a full table rebuild. Concurrent reads and writes are allowed during the rebuild.

Note
PolarDB for MySQL instant add column is in the canary release stage. To use it, go to Quota Center, use Quota ID polardb_mysql_iac_56 to locate the quota, and click Apply in the Actions column to enable this feature.
Only when the storage size of the data type does not change and elements are appended to the end of the ENUM or SET can the operation modify only metadata without rebuilding the entire table. Otherwise, the COPY algorithm is required to rebuild the table.

Table operations

PolarDB for MySQL 8.0.2

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Changing ROW_FORMAT	Yes	Yes	No	Supported
Changing KEY_BLOCK_SIZE	Yes	Yes	No	Supported
Configuring persistent statistics	Yes	No	Yes	N/A
Declaring the character set	Yes	No	Yes	N/A
Converting the character set	No	Yes	No	Not supported
Optimizing a table	Yes	Yes Note When `ALTER TABLE table_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE` is used to defragment a table, tables with full-text indexes do not support the INPLACE algorithm.	No	Supported
Rebuilds table	Yes	Yes	No	Supported
Renaming a table	Yes	No	Yes	N/A
Changing a table comment	Yes	No	Yes	N/A
Creating a table (CREATE TABLE)	\	\	Yes	N/A

PolarDB for MySQL 8.0.1

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Changing ROW_FORMAT	Yes	Yes	No	Supported
Changing KEY_BLOCK_SIZE	Yes	Yes	No	Supported
Configuring persistent statistics	Yes	No	Yes	N/A
Declaring the character set	Yes	No	Yes	N/A
Converting the character set	No	Yes	No	Not supported
Optimizing a table	Yes	Yes Note When `ALTER TABLE table_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE` is used to defragment a table, tables with full-text indexes do not support the INPLACE algorithm.	No	Supported
Rebuilds table	Yes	Yes	No	Supported
Renaming a table	Yes	No	Yes	N/A
Changing a table comment	Yes	No	Yes	N/A
Creating a table (CREATE TABLE)	\	\	Yes	N/A

PolarDB for MySQL 5.7

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Changing ROW_FORMAT	Yes	Yes	No	Supported
Changing KEY_BLOCK_SIZE	Yes	Yes	No	Supported
Configuring persistent statistics	Yes	No	Yes	N/A
Declaring the character set	Yes	No	Yes	N/A
Converting the character set	No	Yes	No	Not supported
Optimizing a table	Yes	Yes Note When `ALTER TABLE table_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE` is used to defragment a table, tables with full-text indexes do not support the INPLACE algorithm.	No	Supported
Rebuilds table	Yes	Yes	No	Supported
Renaming a table	Yes	No	Yes	N/A
Changing a table comment	Yes	No	Yes	N/A
Creating a table (CREATE TABLE)	\	\	Yes	N/A

PolarDB for MySQL 5.6

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Changing ROW_FORMAT	Yes	Yes	No	Not supported
Changing KEY_BLOCK_SIZE	Yes	Yes	No	Not supported
Configuring persistent statistics	Yes	No	Yes	N/A
Declaring the character set	Yes	No	Yes	N/A
Converting the character set	No	Yes	No	Not supported
Optimizing a table	Yes	Yes Note When `ALTER TABLE table_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE` is used to defragment a table, tables with full-text indexes do not support the INPLACE algorithm.	No	Not supported
Rebuilds table	Yes	Yes	No	Not supported
Renaming a table	Yes	No	Yes	N/A
Changing a table comment	Yes	No	Yes	N/A
Creating a table (CREATE TABLE)	\	\	Yes	N/A

When running CREATE TABLE statements in batches, we recommend that you run them in smaller batches during off-peak hours and avoid maintenance windows such as cluster scale-outs. Running them intensively may briefly occupy metadata lock (MDL) resources but has no direct impact on existing data or workloads.

Generated column operations

PolarDB for MySQL 8.0.2

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a STORED column	No Note Adding a STORED column expression involves the SQL/Server layer, so Online DDL is not supported when adding a STORED column.	Yes	No	Not supported
Reordering STORED columns	No	Yes	No	Not supported
Dropping a STORED column	Yes	Yes	No	Supported
Adding a VIRTUAL column	Yes	No	Yes	N/A
Reordering VIRTUAL columns	No	Yes	No	Not supported
Dropping a VIRTUAL column	Yes	No	Yes	N/A

PolarDB for MySQL 8.0.1

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a STORED column	No Note Adding a STORED column expression involves the SQL/Server layer, so Online DDL is not supported when adding a STORED column.	Yes	No	Not supported
Reordering STORED columns	No	Yes	No	Not supported
Dropping a STORED column	Yes	Yes	No	Supported
Adding a VIRTUAL column	Yes	No	Yes	N/A
Reordering VIRTUAL columns	No	Yes	No	Not supported
Dropping a VIRTUAL column	Yes	No	Yes	N/A

PolarDB for MySQL 5.7

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a STORED column	No Note Adding a STORED column expression involves the SQL/Server layer, so Online DDL is not supported when adding a STORED column.	Yes	No	Not supported
Reordering STORED columns	No	Yes	No	Not supported
Dropping a STORED column	Yes	Yes	No	Supported
Adding a VIRTUAL column	Yes	No	Yes	N/A
Reordering VIRTUAL columns	No	Yes	No	Not supported
Dropping a VIRTUAL column	Yes	No	Yes	N/A

PolarDB for MySQL 5.6

PolarDB for MySQL 5.6 does not support generated columns.

Foreign key operations

PolarDB for MySQL 8.0.2

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a foreign key	Yes¹	No¹	Yes¹	N/A
Dropping a foreign key	Yes¹	No¹	Yes¹	N/A

PolarDB for MySQL 8.0.1

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a foreign key	Yes¹	No¹	Yes¹	N/A
Dropping a foreign key	Yes¹	No¹	Yes¹	N/A

PolarDB for MySQL 5.7

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a foreign key	Yes¹	No¹	Yes¹	N/A
Dropping a foreign key	Yes¹	No¹	Yes¹	N/A

PolarDB for MySQL 5.6

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding a foreign key	Yes¹	No¹	Yes¹	N/A
Dropping a foreign key	Yes¹	No¹	Yes¹	N/A

INPLACE DDL is supported only when the foreign_key_checks switch is disabled and only metadata is modified. Otherwise, only COPY DDL is supported, and the table is locked for the entire duration.

Partitioned table operations

PolarDB for MySQL 8.0.2

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding partitions (ADD)	Yes¹	No²	Yes	Not supported
Dropping partitions (DROP)	Yes¹	No²	No	Not supported
Discarding partition tablespaces (DISCARD)	No	No	No	Not supported
Importing partition tablespaces (IMPORT)	No	No	No	Not supported
Truncating partitions (TRUNCATE)	Yes	No	No	Not supported
Coalescing partitions (COALESCE)	No	Yes³	No	Not supported
Redistributing partitions (REORGANIZE)	Yes¹	No⁷	No	Not supported
Exchanging partitions (EXCHANGE)	Yes¹	No	Yes	Not supported
Analyzing partitions (ANALYZE)	Yes	No	No⁸	Not supported
Checking partitions (CHECK)	Yes	No	No⁹	Not supported
Optimizing partitions (OPTIMIZE)	Yes⁴	Yes⁴	No	Supported⁴
Rebuilding partitions (REBUILD)	Yes¹	No⁷	No	Not supported
Repairing partitions (REPAIR)	Yes	No¹⁰	No	Not supported
Converting a table into a partition	No	Yes	Yes⁵	Not supported
Converting a partition into a table	No	Yes	No	Not supported
Creating a PARTIAL INDEX	Yes	No⁶	No	Supported

Introduces partition-level metadata locks (MDL). After you set the loose_partition_level_mdl_enabled parameter to true, running DDL operations does not affect DML on partitions that are not involved. For more information, see Online partition maintenance.
RANGE and LIST partitions do not require a table rebuild when you add or delete partitions.HASH and KEY partitions require a table rebuild when you add partitions. Deleting partitions from HASH and KEY partitions is not supported.
Only HASH and KEY partitions are supported.
When you run the OPTIMIZE PARTITION operation on a table in the InnoDB engine, the entire partitioned table is rebuilt. Reads and writes on the target table are supported during the rebuild. In this case, you can set the innodb_parallel_build_primary_index parameter to ON, and use the Parallel DDL feature to speed up the rebuild.
Instant conversion supports only Convert a non-partitioned table into a range-partitioned table.
PolarDB for MySQL supports creating and deleting indexes at partition granularity. For more information, see Partial indexes.
Redistributing or rebuilding partitions rebuilds only the specified partitions that require data redistribution or rebuild. Other partitions are not affected.
Analyzing a partition modifies only the statistics and does not modify the metadata or data of the table.
Checking a partition does not modify metadata or data.
Repairing a partition rebuilds only the specified partitions that need repair.

PolarDB for MySQL 8.0.1

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding partitions (ADD)	No	No¹	Yes	Not supported
Dropping partitions (DROP)	No	No²	No	Not supported
Discarding partition tablespaces (DISCARD)	No	No	No	Not supported
Importing partition tablespaces (IMPORT)	No	No	No	Not supported
Truncating partitions (TRUNCATE)	Yes	No	No	Not supported
Coalescing partitions (COALESCE)	No	Yes	No	Not supported
Redistributing partitions (REORGNIZATE)	No	No⁴	No	Not supported
Exchanging partitions (EXCHANGE)	Yes	Yes	Yes	Not supported
Analyzing partitions (ANALYZE)	Yes	Yes	No⁵	Not supported
Checking partitions (CHECK)	Yes	No	No⁶	Not supported
Optimizing partitions (OPTIMIZE)	Yes³	Yes³	No	Supported³
Rebuilding partitions (REBUILD)	No	No⁴	No	Not supported
Repairing partitions (REPAIR)	Yes	No⁴	No	Not supported
Converting a table into a partition	No	Yes	No	Not supported
Converting a partition into a table	No	Yes	No	Not supported

RANGE and LIST partitions do not require a table rebuild when you add partitions.HASH and KEY partitions require a table rebuild when you add partitions.
HASH and KEY partitions is not supported.
When you run the OPTIMIZE PARTITION operation on a table in the InnoDB engine, the entire partitioned table is rebuilt. Reads and writes on the target table are supported during the rebuild. In this case, you can set the innodb_parallel_build_primary_index parameter to ON, and use the Parallel DDL feature to speed up the rebuild.
Redistributing, rebuilding, and repairing partitions rebuild only the specified partitions that require redistribution, rebuild, or repair. Other partitions are not involved.
Analyzing a partition modifies only the statistics and does not modify the metadata or data of the table.
Checking a partition does not modify metadata or data.

PolarDB for MySQL 5.7

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding partitions (ADD)	No	No¹	Yes	Not supported
Dropping partitions (DROP)	No	No¹	No	Not supported
Discarding partition tablespaces (DISCARD)	No	No	No	Not supported
Importing partition tablespaces (IMPORT)	No	No	No	Not supported
Truncating partitions (TRUNCATE)	Yes	No	No	Not supported
Coalescing partitions (COALESCE)	No	Yes	No	Not supported
Redistributing partitions (REORGNIZATE)	No	No²	No	Not supported
Exchanging partitions (EXCHANGE)	Yes	No	Yes	Not supported
Analyzing partitions (ANALYZE)	Yes	No	No³	Not supported
Checking partitions (CHECK)	Yes	No	No⁴	Not supported
Optimizing partitions (OPTIMIZE)	No	Yes	No	Not supported
Rebuilding partitions (REBUILD)	No	No²	No	Not supported
Repairing partitions (REPAIR)	Yes	No²	No	Not supported
Converting a table into a partition	No	Yes	No	Not supported
Converting a partition into a table	No	Yes	No	Not supported

RANGE and LIST partitions do not require a table rebuild when you add or delete partitions.HASH and KEY partitions require a table rebuild when you add partitions. Deleting partitions from HASH and KEY partitions is not supported.
Redistributing, rebuilding, and repairing partitions rebuild only the specified partitions that require redistribution, rebuild, or repair. Other partitions are not involved.
Analyzing a partition modifies only the statistics and does not modify the metadata or data of the table.
Checking a partition does not modify metadata or data.

PolarDB for MySQL 5.6

Operation	Concurrent DML allowed	Rebuilds table	Metadata only	Parallel DDL supported
Adding partitions (ADD)	No	No¹	Yes	Not supported
Dropping partitions (DROP)	No	No¹	No	Not supported
Discarding partition tablespaces (DISCARD)	No	No	No	Not supported
Importing partition tablespaces (IMPORT)	No	No	No	Not supported
Truncating partitions (TRUNCATE)	Yes	No	No	Not supported
Coalescing partitions (COALESCE)	No	Yes	No	Not supported
Redistributing partitions (REORGNIZATE)	No	No²	No	Not supported
Exchanging partitions (EXCHANGE)	Yes	No	Yes	Not supported
Analyzing partitions (ANALYZE)	Yes	No	No³	Not supported
Checking partitions (CHECK)	Yes	No	No⁴	Not supported
Optimizing partitions (OPTIMIZE)	No	Yes	No	Not supported
Rebuilding partitions (REBUILD)	No	No²	No	Not supported
Repairing partitions (REPAIR)	Yes	No²	No	Not supported
Converting a table into a partition	No	Yes	No	Not supported
Converting a partition into a table	No	Yes	No	Not supported

RANGE and LIST partitions do not require a table rebuild when you add or delete partitions.HASH and KEY partitions require a table rebuild when you add partitions. Deleting partitions from HASH and KEY partitions is not supported.
Redistributing, rebuilding, and repairing partitions rebuild only the specified partitions that require redistribution, rebuild, or repair. Other partitions are not involved.
Analyzing a partition modifies only the statistics and does not modify the metadata or data of the table.
Checking a partition does not modify metadata or data.

DDL execution methods

When PolarDB for MySQL uses the INPLACE or INSTANT algorithm to run a DDL operation, we recommend that you preferentially use the kernel method (Online DDL). This method is fast in execution and high in stability.
When PolarDB for MySQL uses the COPY algorithm to run a DDL operation, the table is locked for the entire duration and the target table cannot be read or written during execution. In this case, you can consider using third-party tools such as DMS lock-free change or gh-ost to run the DDL. These third-party tools allow reads and writes during DDL execution, but they are usually slower. On large tables or in high-concurrency scenarios, execution may fail if there is too much incremental data.

The following table describes the differences between running DDL operations with the kernel method and with third-party tools:

Execution method	Concurrent reads and writes allowed	Execution speed	Binlog required	Parallel acceleration
Kernel (Online DDL)	Yes	Fast	No	Supported
Third-party tools (DMS lock-free change, gh-ost, etc.)	Yes	Slow	Yes	Not supported

Note

Even if you use third-party tools to run DDL operations, an MDL-X lock is acquired during the table switch (metadata modification), which causes a brief table lock. You can enable the Nonblocking DDL statements feature or the Preemptive DDL feature to avoid table locking issues.

Contact us

If you have any questions about DDL operations, Contact us.