This topic describes the automatic sharding rules and index conversion rules of tables that are automatically sharded based on primary keys in automatic partitioning mode.

By default, if you do not specify a partitioning method when you create a database in automatic partitioning mode, tables in the database are sharded based on primary keys.

Prerequisites

  • The PolarDB-X version is 5.4.13 or later.
  • The MODE parameter is set to auto/partitioning 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 can specify a table as a single table or replicated table when you create the table. By default, all tables are partitioned tables when they are created, and the tables are sharded based on primary keys.
  • You must specify a primary key for a table that is automatically sharded based on primary keys 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. If no primary key is specified when you create a table, an implicit primary key is automatically created.
  • By default, the index of a table that is automatically sharded based on primary keys is a global secondary index except that the first column does not support automatic sharding or the index is specified as a local index.
  • 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 conversion rules do not take effect on the table. For more information about how to change sharding rules, see Change the type and modify the partitioning rule of a table.
  • In databases that use the automatic partitioning mode, the name of a global secondary index (GSI) is optional and does not need to be unique in statements used to create a table. Random suffixes are assigned to all GSI tables. You can run the show full create table command to view the suffixes.

Syntax

In automatic partitioning mode, automatic sharding is enabled by default. You do not need to add specific keywords to a statement used to create a table. For more information about the syntax of the statement used to create a table, see CREATE TABLE.

The following examples show how tables are sharded by using automatic sharding. In these examples, show create table is run to show the simple schema information of the tables. The show full create table command is run to show the complete schema information of the tables. 

CREATE TABLE `tb` (
    ->   `x` int NOT NULL AUTO_INCREMENT,
    ->   `y` int NOT NULL,
    ->   `z` float NOT NULL,
    ->   `d` int NOT NULL,
    ->   PRIMARY KEY (x,y,z,d)
    -> );
Query OK, 0 rows affected (0.42 sec)

show create table tb;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE                                                                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb    | CREATE TABLE `tb` (
        `x` int(11) NOT NULL AUTO_INCREMENT,
        `y` int(11) NOT NULL,
        `z` float NOT NULL,
        `d` int(11) NOT NULL,
        PRIMARY KEY (`x`, `y`, `z`, `d`),
        LOCAL KEY `auto_shard_key_x_y_d` USING BTREE (`x`, `y`, `d`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

show full create table tb;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE                                                                          |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb    | CREATE PARTITION TABLE `tb` (
        `x` int(11) NOT NULL AUTO_INCREMENT,
        `y` int(11) NOT NULL,
        `z` float NOT NULL,
        `d` int(11) NOT NULL,
        PRIMARY KEY (`x`, `y`, `z`, `d`),
        LOCAL KEY `auto_shard_key_x_y_d` USING BTREE (`x`, `y`, `d`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`x`,`y`,`d`)
PARTITIONS 16
/* tablegroup = `tg8` */ |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

CREATE TABLE `t_order` (
    -> `x` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> `order_id` varchar(20) DEFAULT NULL,
    -> `seller_id` varchar(20) DEFAULT NULL,
    -> INDEX (`seller_id`),
    -> UNIQUE INDEX (`order_id`)
    -> );
Query OK, 0 rows affected (0.84 sec)

show create table `t_order`;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE   | CREATE TABLE                                                                                                                                                                                                                                                             |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE TABLE `t_order` (
        `x` int(11) NOT NULL AUTO_INCREMENT,
        `order_id` varchar(20) DEFAULT NULL,
        `seller_id` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`x`),
        INDEX `i_0` (`seller_id`),
        UNIQUE INDEX `i_1` (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

show full create table `t_order`;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE   | CREATE TABLE          |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE PARTITION TABLE `t_order` (
        `x` int(11) NOT NULL AUTO_INCREMENT,
        `order_id` varchar(20) DEFAULT NULL,
        `seller_id` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`x`),
        GLOBAL INDEX /* i_0_$cff4 */ `i_0` (`seller_id`) PARTITION BY KEY (`seller_id`, `x`) PARTITIONS 16,
        UNIQUE GLOBAL INDEX /* i_1_$1782 */ `i_1` (`order_id`) PARTITION BY KEY (`order_id`) PARTITIONS 16,
        UNIQUE LOCAL KEY `_local_i_1` (`order_id`),
        LOCAL KEY `_local_i_0` (`seller_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`x`)
PARTITIONS 16
/* tablegroup = `tg8` */ |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Automatic sharding rules

  • If the table that you want to shard is not configured with a primary key, PolarDB-X uses an implicit primary key as the shard key. The shard key is a BIGINT auto-increment primary key and is invisible to users. You can run show full create table to view the sharding details.
  • If a primary key is specified for the table that you want to shard, PolarDB-X uses the primary key of the table as the shard key. If a composite key is specified for the table that you want to shard, all columns in the composite key are used as the shard key.
  • Assume that a composite key and a parameter that is used to partition data by key in order are specified. If the data type of a column is not supported, the column is skipped. If the data type of the first column is not supported, errors are reported. In this case, you must manually specify the table as the single table. For more information about the statement used to create a single table, see Create a non-partitioned table and a broadcast table.
  • Automatic sharding uses the sharding algorithms based on partitioned tables. The system automatically selects sharding algorithms based on the data types of primary keys.
Primary key data type Sharding algorithm
bit, float, double, time, year, tinyblob, blob, mediumblob, longblob, enum, decimal, binary, varbinary, tinytext, text, mediumtext, longtext, set, geometry Not supported
Other data types Key partitioning

Index conversion rules

You can specify the index of a table automatically sharded based on primary keys as a local index. By default, the index of the table is a global secondary index. To convert index types, use the following 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 table that is automatically sharded based on primary keys, a global secondary index based on covering is created and sharded based on the automatic sharding rules described in the preceding section. If you want to create a local index, you must specify the LOCAL keyword.
  • When a sharding algorithm is generated for a global secondary index, all index keys are used for sharding. At the same time, the primary key is used as the sharding algorithm parameter for non-UNIQUE constraint scenarios.
  • 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 index key 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 .
    ->   UNIQUE INDEX `i_order` using btree (`order_id`), -- Create a unique global secondary index. The index table is automatically sharded.
    ->   GLOBAL INDEX `g_seller` using btree (`seller_id`), -- Create a global index. The index table is automatically sharded.
    ->   UNIQUE GLOBAL INDEX `g_order` using btree (`order_id`), -- Create a unique global index. The index table is automatically sharded.
    ->   CLUSTERED INDEX `c_seller` using btree (`seller_id`), -- Create a clustered index. The index table is automatically sharded.
    ->   UNIQUE CLUSTERED INDEX `c_order` using btree (`order_id`) -- Create a unique clustered index. The index table is automatically sharded.
    -> );
Query OK, 0 rows affected (1.49 sec)

show create table `t_order`;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE   | CREATE TABLE                              |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE TABLE `t_order` (
        `x` int(11) DEFAULT NULL,
        `order_id` varchar(20) DEFAULT NULL,
        `seller_id` varchar(20) DEFAULT NULL,
        UNIQUE CLUSTERED INDEX `c_order` USING BTREE (`order_id`),
        CLUSTERED INDEX `c_seller` USING BTREE (`seller_id`),
        UNIQUE INDEX `g_order` USING BTREE (`order_id`),
        INDEX `g_seller` USING BTREE (`seller_id`),
        UNIQUE INDEX `i_order` USING BTREE (`order_id`),
        INDEX `i_seller` USING BTREE (`seller_id`),
        UNIQUE LOCAL KEY `l_order` USING BTREE (`order_id`),
        LOCAL KEY `l_seller` USING BTREE (`seller_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

show full create table `t_order`;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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,
        `_drds_implicit_id_` bigint(20) NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`_drds_implicit_id_`),
        UNIQUE CLUSTERED INDEX /* c_order_$1ba0 */ `c_order` USING BTREE (`order_id`) PARTITION BY KEY (`order_id`) PARTITIONS 16,
        CLUSTERED INDEX /* c_seller_$1e39 */ `c_seller` USING BTREE (`seller_id`) PARTITION BY KEY (`seller_id`, `_drds_implicit_id_`) PARTITIONS 16,
        UNIQUE GLOBAL INDEX /* g_order_$d57f */ `g_order` USING BTREE (`order_id`) PARTITION BY KEY (`order_id`) PARTITIONS 16,
        GLOBAL INDEX /* g_seller_$6ed5 */ `g_seller` USING BTREE (`seller_id`) PARTITION BY KEY (`seller_id`, `_drds_implicit_id_`) PARTITIONS 16,
        UNIQUE GLOBAL INDEX /* i_order_$ab2f */ `i_order` USING BTREE (`order_id`) PARTITION BY KEY (`order_id`) PARTITIONS 16,
        GLOBAL INDEX /* i_seller_$2b4b */ `i_seller` USING BTREE (`seller_id`) PARTITION BY KEY (`seller_id`, `_drds_implicit_id_`) PARTITIONS 16,
        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`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`_drds_implicit_id_`)
PARTITIONS 16
/* tablegroup = `tg11` */ |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Limits and description of DDL statements that can be executed on tables automatically sharded based on primary keys

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 N/A
  • 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-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 use this clause to specify or delete the default value of a column. 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.