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

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.

For information about how to view the kernel version of a PolarDB-X instance, see View the version of an instance.

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.
• 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 HASH.
• 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.

• 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.

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

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.