PolarDB:Change table types and sharding rules (DRDS mode)

Prerequisites

Before you begin, ensure that you have:

• A PolarDB-X instance running kernel version V5.4.8 or later (required to modify the sharding rule of a table)

• A PolarDB-X instance running kernel version V5.4.10 or later (required to change the type of a table)

To check the kernel version of your instance, see View the version of an instance.

Usage notes

• The sharding rule of a sharded table that contains a global secondary index (GSI) cannot be modified.

• After you modify the sharding rule of a table that was automatically sharded based on the primary key, the table becomes a standard table. Automatic sharding rules and index-changing rules no longer apply. For more information, see Automatic sharding.

• If a non-sharded table contains an auto-increment column, create a sequence for the table before converting it to a broadcast table or a sharded table.

Table types

PolarDB-X supports three table types: sharded tables, broadcast tables, and non-sharded tables. Use ALTER TABLE to switch between types or update sharding rules.

Sharded tables

Specify a drds_partition_options clause to shard a table across database shards and table shards:

drds_partition_options:
    DBPARTITION BY db_partition_algorithm
    [TBPARTITION BY table_partition_algorithm [TBPARTITIONS number]]

db_partition_algorithm supports:

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:

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)

For details on sharding functions, see HASH.

Broadcast tables

Use the BROADCAST clause to create a broadcast table. PolarDB-X replicates the table on every database shard and uses distributed transactions to keep data consistent across all copies.

Non-sharded tables

A table created without a shard key or the BROADCAST clause is non-sharded.

Convert to a sharded table

Applies to: non-sharded tables and broadcast tables.

Syntax

ALTER TABLE table_name drds_partition_options;

If the table contains an auto-increment column, create a sequence for it before running this statement.

Example

The t_order table was created as a non-sharded table to store order data. After business growth caused performance issues, the following statement converts it to a sharded table using order_id as the shard key:

ALTER TABLE t_order dbpartition BY hash(`order_id`);

The example table schema used throughout this topic:

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;

Convert to a broadcast table

Applies to: non-sharded tables and sharded tables.

Syntax

ALTER TABLE table_name BROADCAST;

If the table is non-sharded and contains an auto-increment column, create a sequence for it before running this statement.

Example

ALTER TABLE t_order BROADCAST;

Convert to a non-sharded table

Applies to: broadcast tables and sharded tables.

Syntax

ALTER TABLE table_name SINGLE;

Example

ALTER TABLE t_order SINGLE;

Modify the sharding rule of a sharded table

Applies to: sharded tables only. Cannot be used on tables that contain a global secondary index (GSI).

Syntax

ALTER TABLE tbl_name drds_partition_options;

Example

Start with a sharded table that uses order_id as the shard key:

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`);

To update the sharding rule so that database sharding uses order_id, table sharding uses buyer_id, and each database shard contains three table shards:

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

What's next

After modifying the sharding rule, verify the result:

• View the new sharding rule: Rule and topology query statements

• View the table topology: Rule and topology query statements

FAQ

Why did my DDL statement fail when changing shard keys, and how do I fix it?

DDL failures are typically caused by instance failures or unique index conflicts. The failure does not affect existing data or block DML and query statements. To recover, run ROLLBACK DDL to roll back the failed operation, then re-execute the DDL statement.

RECOVER DDL cannot restore a failed DDL execution. Use ROLLBACK DDL instead.