Auto Partitioning Sharding Rules & Index Conversion Overview - PolarDB - Alibaba Cloud - PolarDB

This topic describes the automatic partitioning rules and index transform rules for tables that are automatically sharded by their primary key in an AUTO mode database.

In an AUTO mode database, tables are sharded by their primary key by default if you do not specify a partitioning method.

Prerequisites

PolarDB-X instance is version 5.4.13 or later.
You selected AUTO mode when you created the database. This means you set MODE to auto/partitioning. For more information about database modes, see CREATE DATABASE.

Notes

By default, tables are created as partitioned tables unless you specify them as single or broadcast tables. If you do not specify a partitioning method, the tables are sharded by their primary key by default.
For tables that are automatically sharded, you can only specify a primary key when you create the table. You cannot add or delete a primary key for an existing table. If you do not specify a primary key when you create the table, an implicit primary key is automatically created.
Indexes on tables that are automatically sharded are created as global secondary indexes (GSIs) by default. This rule does not apply if the first column does not support automatic partitioning or if the index is specified as a LOCAL index.
If you change the partitioning policy, a table that was automatically sharded becomes a standard table. This means the original automatic partitioning policy and index transform rules no longer apply. For more information about how to change the partitioning policy, see Change table types and partitioning policies (AUTO mode).
In an AUTO mode database, GSI names are no longer required to be unique. You are also not required to specify a GSI name in the CREATE TABLE statement. All GSI tables have a random suffix. You can view these random suffixes by running the show full create table command.

Syntax

Automatic sharding by primary key is enabled by default in AUTO mode. You do not need to add special keywords to the CREATE TABLE statement. For more information about the syntax, see CREATE TABLE (AUTO mode).

The following examples show how partitioning works. The show create table command displays a simplified table schema, while the show full create table command displays the complete table schema.

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 partitioning rules

If you do not specify a primary key for the target table, PolarDB-X creates an implicit primary key and uses it as the shard key. This primary key is an auto-increment primary key of the BIGINT type and is not visible to users. You can run the show full create table command to view the complete partitioning details.
If you specify a primary key for the target table, PolarDB-X uses that primary key as the shard key. If it is a composite primary key, all columns of the composite primary key are used as the shard key.
For a composite primary key, the columns are used in order as parameters for KEY partitioning. If a column of an unsupported type is encountered, that column is skipped. If the first column is of an unsupported type, an error is reported. You must manually specify the table as a SINGLE table to create it successfully. For more information about the CREATE TABLE statement for a single table, see Single tables.
Automatic partitioning uses a partitioned table sharding algorithm. The sharding algorithm is automatically selected based on the primary key type:

Primary key type	Sharding algorithm
bit, float, double, time, year, tinyblob, blob, mediumblob, longblob, enum, decimal, binary, varbinary, tinytext, text, mediumtext, longtext, set, geometry	Not supported
Others	KEY partitioning

Index transform rules

When you create an index on a table that is automatically sharded, the index is automatically transformed into a GSI unless it is explicitly specified as a LOCAL index. The specific transform rules are as follows:

If you specify the LOCAL keyword, the index is forced to be a local index.
When you create an index on a table that is automatically sharded, if you do not specify the LOCAL keyword, the operation is automatically transformed to create a GSI without covering columns. The index columns are automatically partitioned based on the automatic partitioning rules described earlier. To create a standard local index, specify the LOCAL keyword.
When a sharding algorithm is generated for a GSI, all index keys are used for sharding. For non-UNIQUE constraints, the primary key is also included as a parameter for the sharding algorithm.
When you create a GSI or a clustered index, a local index with the _local_ prefix is also created. If you delete the GSI, PolarDB-X automatically deletes the corresponding local index.
For a table that is automatically sharded, you do not need to specify the partitioning method for GSIs or clustered indexes. PolarDB-X partitions the index keys based on the automatic partitioning rules.

The following statement and its comments show the index transform rules.

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`), -- Force the index to be a local index
    ->   UNIQUE LOCAL INDEX `l_order` using btree (`order_id`), -- Force the index to be a unique local index
    ->   INDEX `i_seller` using btree (`seller_id`), -- Replaced with a GSI, automatically partitioned
    ->   UNIQUE INDEX `i_order` using btree (`order_id`), -- Replaced with a UGSI, automatically partitioned
    ->   GLOBAL INDEX `g_seller` using btree (`seller_id`), -- Automatically partitioned
    ->   UNIQUE GLOBAL INDEX `g_order` using btree (`order_id`), -- Automatically partitioned
    ->   CLUSTERED INDEX `c_seller` using btree (`seller_id`), -- Automatically partitioned and clustered
    ->   UNIQUE CLUSTERED INDEX `c_order` using btree (`order_id`) -- Automatically partitioned and clustered
    -> );
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)

DDL restrictions for automatically sharded tables

The following restrictions apply when you perform DDL operations on automatically sharded tables.

DDL category	DDL clause	Description and restrictions
CREATE INDEX	N/A	If you use the `[UNIQUE] LOCAL INDEX` clause, PolarDB-X creates a local index and automatically synchronizes it to the clustered index table. If you use the `[UNIQUE] INDEX` clause, PolarDB-X transforms it into the corresponding GSI. At the same time, a local index with the `_local_` prefix is automatically created and synchronized to the clustered index table. If you use the `[UNIQUE] GLOBAL \| CLUSTERED INDEX` clause, PolarDB-X automatically creates a local index with the `_local_` prefix and synchronizes it to the clustered index table. Note If you do not specify a partitioning method, the index is automatically partitioned.
ALTER TABLE	ADD {INDEX \| KEY} [index_name] [index_type] (key_part,...) [index_option] ...
	ADD [COLUMN] (col_name column_definition,...)	PolarDB-X automatically adds the corresponding columns to clustered global secondary indexes (CGSIs) and unique clustered global secondary indexes (UCGSIs). Rollback operations are supported.
	DROP [COLUMN] col_name	You cannot delete columns that are part of a primary key, a primary table shard key, an index table shard key, or a composite UNIQUE constraint.
	CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST \| AFTER col_name]	You cannot rename columns that are part of a primary key, a primary table shard key, or an index table shard key. You cannot rename columns that are part of a composite UNIQUE constraint. Note You can bypass the restrictions on other columns by running the Hint: /+TDDL: cmd_extra(ALLOW_LOOSE_ALTER_COLUMN_WITH_GSI=true)/ command. For more information, see How to use HINTs. If you only need to modify the default information, 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 }	Rollback operations are supported. Note If the default value of the column is current_timestamp, rollback operations are not supported.