PolarDB-X provides the automatic sharding feature. PolarDB-X shards data of a table based on the data type of the columns on which the primary key of the table is created. You can modify the CREATE TABLE statement to create a sharded table. The system can automatically select a shard key and a sharding rule based on the primary key and index keys. This way, a single-node database service is changed to a distributed database service.

Prerequisites

  • The version of your PolarDB-X instance is V5.4.9 or later.
  • The MODE parameter is set to drds/sharding when you create the database. For more information about how to select a mode for partitioning when you create a database, see CREATE DATABASE.

Usage notes

  • You must specify a primary key for a table when you create the table. After the table is created, a primary key cannot be created for the table, and the primary key of the table cannot be deleted.
  • You must specify names for all non-local indexes.
  • After you modify the sharding rule for a sharded table, the table becomes a standard table, and the automatic sharding rules and the index setting rules that are used for sharded tables do not take effect on the table. For more information about how to modify the sharding rule of a table, see Change the type and modify the sharding rule of a table.

Syntax

The PARTITION keyword is added to CREATE TABLE. This way, you can use the CREATE PARTITION TABLE statement to create a table of which the primary key is used as the shard key. You can use the LOCAL, GLOBAL, or CLUSTERED keyword to specify the type of the index in the clause that defines an index. 

CREATE PARTITION TABLE [IF NOT EXISTS] tbl_name
                (create_definition, ...)
                [table_options]
                [drds_partition_options]
                create_definition:
                col_name column_definition
                | mysql_create_definition
                | [UNIQUE] [LOCAL | GLOBAL | CLUSTERED] INDEX index_name [index_type] (index_col_name,...)
                [global_secondary_index_option]
                [index_option] ...
  • LOCAL: specifies that the index you want to create is a local index.
  • GLOBAL: specifies that the index you want to create is a global secondary index.
  • CLUSTERED: specifies that the index you want to create is a clustered index. For more information, see Clustered indexes.
Note For more information about the CREATE TABLE statement, see CREATE TABLE.

If you want to use the CREATE TABLE syntax to create a sharded table, you can configure the auto_partition variable to enable the automatic sharding feature. The following steps describe how to enable and disable the automatic sharding feature when you create a table.

  1. Run the set @auto_partition=1; command to enable the automatic sharding feature.
  2. Use the CREATE TABLE statement to create a table. The PARTITION keyword is not required in the statement. The created table is a sharded table.
  3. If you do not want to shard the table after the table is created, you can run the set @auto_partition=0; command to disable the automatic sharding feature.

Automatic sharding rules

  • If no primary key is specified for a table, PolarDB-X creates an implicit primary key for the table and uses the implicit primary key as the shard key. The implicit primary key column is an auto-increment key that contains data of the BIGINT type. The implicit primary key cannot be viewed by users.
  • If a primary key is specified for a table, PolarDB-X uses the primary key of the table as the shard key. If the primary key is created on multiple columns, the first column from the left side is used as the shard key.
  • The automatic sharding feature can create only database shards and the sharding algorithm is selected based on the data type of the primary key. This feature does not create table shards. The following table describes sharding algorithms for primary keys of different data types.
    Data type Sharding algorithm
    TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, CHAR, or VARCHAR HASH
    DATE, DATETIME, or TIMESTAMP YYYYDD
    BIT, FLOAT, DOUBLE, TIME, YEAR, BLOB, ENUM, DECIMAL, BINARY, TEXT, SET, or GEOMETRY If the primary key of a table is of one of these data types, the primary key cannot be used as a shard key.

Index conversion rules

  • If you specify the LOCAL keyword when you create an index, the index is a local index.
  • If you do not specify the LOCAL keyword when you create an index for a sharded table, a global secondary index that does not contain covering columns is created and the first column from the left side in the index table is used as the shard key. If you want to create a local index, you must specify the LOCAL keyword.
  • When you create a global secondary index or a clustered index, a local index whose name contains the _local_ prefix is automatically created. When you delete a global secondary index, PolarDB-X automatically deletes the local index that corresponds to the global secondary index.
  • If you do not specify a shard key for a global secondary index or a clustered index, PolarDB-X uses the first column from the left side in the index table as the shard key based on the automatic sharding rules.

The following sample code shows how to specify indexes for a table: 

CREATE PARTITION TABLE `t_order` (
  `x` int,
  `order_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  LOCAL INDEX 'l_seller' using btree ('seller_id'), -- Create a local index.
  UNIQUE LOCAL INDEX 'l_order' using btree ('order_id'), -- Create a unique local index.
  INDEX 'i_seller' using btree ('seller_id'), -- Create a global secondary index. The index table is automatically sharded based on the first index column from the left side.
  UNIQUE INDEX 'i_order' using btree ('order_id'), -- Create a unique global secondary index. The index table is automatically sharded based on the first index column from the left side.
  GLOBAL INDEX 'g_seller' using btree ('seller_id'), -- Create a global index. The index table is automatically sharded based on the first index column from the left side.
  UNIQUE GLOBAL INDEX 'g_order' using btree ('order_id'), -- Create a unique global index. The index table is automatically sharded based on the first index column from the left side.
  CLUSTERED INDEX 'c_seller' using btree ('seller_id'), -- Create a clustered index. The index table is automatically sharded based on the first index column from the left side.
  UNIQUE CLUSTERED INDEX 'c_order' using btree ('order_id') -- Create a unique clustered index. The index table is automatically sharded based on the first index column from the left side.
);

After the table is created, you can run the SHOW CREATE TABLE t_order; statement to view the schema of the sharded table. 

+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                            |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE PARTITION TABLE `t_order` (
  `x` int(11) DEFAULT NULL,
  `order_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  UNIQUE LOCAL KEY `l_order` USING BTREE (`order_id`),
  UNIQUE LOCAL KEY `_local_i_order` USING BTREE (`order_id`),
  UNIQUE LOCAL KEY `_local_g_order` USING BTREE (`order_id`),
  UNIQUE LOCAL KEY `_local_c_order` USING BTREE (`order_id`),
  LOCAL KEY `l_seller` USING BTREE (`seller_id`),
  LOCAL KEY `_local_i_seller` USING BTREE (`seller_id`),
  LOCAL KEY `_local_g_seller` USING BTREE (`seller_id`),
  LOCAL KEY `_local_c_seller` USING BTREE (`seller_id`),
  UNIQUE CLUSTERED KEY `c_order` USING BTREE (`order_id`) DBPARTITION BY HASH(`order_id`),
  CLUSTERED INDEX `c_seller` USING BTREE(`seller_id`) DBPARTITION BY HASH(`seller_id`),
  UNIQUE GLOBAL KEY `g_order` USING BTREE (`order_id`) DBPARTITION BY HASH(`order_id`),
  GLOBAL INDEX `g_seller` USING BTREE(`seller_id`) DBPARTITION BY HASH(`seller_id`),
  UNIQUE GLOBAL KEY `i_order` USING BTREE (`order_id`) DBPARTITION BY HASH(`order_id`),
  GLOBAL INDEX `i_seller` USING BTREE(`seller_id`) DBPARTITION BY HASH(`seller_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4   |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

Limits and description of DDL statements that can be executed on sharded tables

The following table describes the DDL statements that can be executed and the operations that the DDL clauses can perform on sharded tables.

DDL statement Clause Description
CREATE INDEX None
  • If you use the [UNIQUE] LOCAL INDEX clause, PolarDB-X creates a local index and automatically adds the local index to the clustered index of the table.
  • If you use the [UNIQUE] INDEX clause, PolarDB-X creates a global secondary index and creates a corresponding local index whose name contains the _local_ prefix. The local index is automatically added to the clustered index of the table.
  • If you use the [UNIQUE] GLOBAL | CLUSTERED INDEX clause, PolarDB-X creates a unique global secondary index or clustered index based on your configurations and automatically creates a local index whose name contains the _local_ prefix and adds the local index to the clustered index of the table.
    Note If you do not specify a shard key, the system uses the automatic sharding rules.
ALTER TABLE ADD {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option] ...
ADD [COLUMN] (col_name column_definition,...)
  • If you use this clause to add a column to a table, PolarDB-XPolarDB-X automatically adds the new column to the clustered global secondary index and the unique clustered global secondary index of the table.
  • You can roll back the operation.
DROP [COLUMN] col_name You cannot use this clause to delete the primary key or shard key of a table, the shard key of an index table, or the unique columns of a composite index.
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]
  • You cannot use these clauses to rename the primary key or shard key of a table, or the shard key of an index table.
  • You cannot use these clauses to rename the unique columns of a composite index.
    Note You can use the /*+TDDL: cmd_extra(ALLOW_LOOSE_ALTER_COLUMN_WITH_GSI=true)*/ hint to remove the constraints on columns. The constraints on the unique columns of the composite index cannot be removed. For more information, see How to use hints
  • If you want to change the default value of a column, we recommend that you use the SET DEFAULT or DROP DEFAULT clause.
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
ALTER TABLE tbl_name ALTER [COLUMN] col_name { SET DEFAULT {literal | (expr)} | DROP DEFAULT } You can roll back the operation.
Note If the data in the specified column is of the CURRENT_TIMESTAMP type, you cannot roll back operations that are performed on the column.