Change the type and modify the sharding rule of a table ( DRDS mode )

PolarDB-X allows you to change the type and modify the sharding rule of a table. You can switch the type of a table among the non-sharded table, sharded table, and broadcast table types. To modify the sharding rule of a table, you can change the configurations of sharding functions or the sharding keys of the table. This topic describes the syntaxes that are used to shard a table and change the type of a table, and provides sample code to show how to use the syntaxes. This syntax applies to only DRDS mode databases.

Prerequisites

If you want to change the sharding rule of a table, the kernel version of the PolarDB-X instance in which the table is deployed must be V5.4.8 or later.
If you want to change the type of a table, the kernel version of the PolarDB-X instance in which the table is deployed must be V5.4.10 or later.
If you want to change the sharding rule or table type of a sharded table with GSI, the kernel minor version of the PolarDB-X 2.0 instance must be 5.4.13 or later.

View the version of PolarDbB-X instance

You can connect to a database in your PolarDB-X instance, and then run the select version() command to view the current version of the instance. The following example shows the output of the command:

select version();
+-----------------------------+
| VERSION()                   |
+-----------------------------+
| 5.6.29-TDDL-5.4.8-16079964  |
+-----------------------------+
1 row in set (0.00 sec)

Usage notes

The sharding rule of a sharded table that contains a global secondary index cannot be modified.
After you modify the sharding rule of a table that is automatically sharded based on the primary key, the table becomes a standard table. In this case, the automatic sharding rules and the index changing rules do not take effect on the table. For more information, see Automatic sharding.
If a table that is not sharded contains an auto-increment column, you must create a sequence for the table before you change the table to a broadcast table or a sharded table. For more information, see Explicit Usage.

In this topic, a table named t_order is used in the provided examples that show how to change the type of a table. You can use the following statement to create the t_order table:

CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table types

PolarDB-X instances support three types of tables: sharded tables, broadcast tables, and tables that are not sharded. You can use the ALTER TABLE statement to change the type of a table and modify the sharding rule of a table.

Sharded tables

You can specify a drds_partition_options clause to shard a table. You can use the following drds_partition_options clause to create database shards and table shards:
```
drds_partition_options:
DBPARTITION BY db_partition_algorithm
[TBPARTITION BY table_partition_algorithm [TBPARTITIONS number]]
```

In the preceding clause, db_partition_algorithm and table_partition_algorithm support different functions.

db_partition_algorithm supports the following functions:

db_partition_algorithm:
    HASH([col_name])
  | {YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)

table_partition_algorithm supports the following functions:

table_partition_algorithm:
    HASH(col_name)
  | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)

Note For more information about sharding functions, see Sharding functions.

Broadcast tables

You can use a BROADCAST clause to create a broadcast table. The system creates the same broadcast table on each database shard and uses the distributed transaction mode to ensure that data is consistent among tables. For more information, see CREATE TABLE.

Tables that are not sharded

If you do not specify sharding keys or include the BROADCAST clause in the statement that is used to create a table, the table that is created is not sharded. For more information, see CREATE TABLE.

Change a table that is not sharded or a broadcast table to a sharded table

Syntax

ALTER TABLE table_name drds_partition_options;

Note

For information about how to specify a drds_partition_options clause, see Table types.
If you want to change the type of a table that is not sharded and contains an auto-increment column, you must create a sequence for the table before you change the table to a sharded table.

Examples

For example, you used a table that is not sharded and is named t_order to store the data of your business. Due to business expansion, the table cannot handle all workloads of your business. To resolve this issue, you can use the following statement to change the table to a sharded table. In the statement, order_id is used as the shard key.
```
ALTER TABLE t_order dbpartition BY hash(`order_id`);
```

Change a table that is not sharded or a sharded table to a broadcast table

Syntax
```
ALTER TABLE table_name BROADCAST;
```

Note If you want to change the type of a table that is not sharded and contains an auto-increment column, you must create a sequence for the table before you change the table to a broadcast table.

Examples You can use the following statement to change a table that is not sharded or a sharded table to a broadcast table. The table that is used in the following example is named t_order.
```
ALTER TABLE t_order BROADCAST;
```

Change a broadcast table or a sharded table to a table that is not sharded

Syntax
```
ALTER TABLE table_name SINGLE;
```

Examples You can use the following statement to change a broadcast table or a sharded table to a table that is not sharded. The table that is used in the following example is named t_order.
```
ALTER TABLE t_order SINGLE;
```

Modify the sharding rule of a sharded table

Syntax

ALTER TABLE tbl_name drds_partition_options;

Examples You can use the following statement to create a sharded table named t_order in a PolarDB-X database. order_id is used as the shard key of the table.

CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
dbpartition BY hash(`order_id`);

You can make the following changes to the sharding rule of the t_order table:

Database sharding is performed based on the order_id column.
Table sharding is performed based on the buyer_id column.
Each database shard contains three table shards.

You can use the following statement to modify the sharding rule:

  ALTER TABLE t_order dbpartition BY hash(order_id) tbpartition BY hash(buyer_id) tbpartitions 3;

References

After the sharding rule is modified, you can use the following statements to view the new sharding rule and the topology of the table:

For more information about how to view the sharding rule of the table, see Rule and topology query statements.
For more information about how to view the table topology, see Rule and topology query statements.

FAQ

Why does the execution of a DDL statement that changes the shard keys of a table fail? How do I resolve this issue?

Errors such as instance failures or unique index conflicts can cause DDL execution failures. DDL execution failures do not impact data in the table or block the executions of DML statements or query statements. When the execution of a DDL statement that changes a shard key fails, you can use the ROLLBACK DDL statement to roll back the operation and then execute the DDL statement. For more information about the ROLLBACK DDL statement, see the related documentation. Note The RECOVER DDL statement cannot be used to restore a DDL execution that failed.

Change sharding rule(DRDS mode)