AUTO模式下的主键拆分

注意事项

• 除去创建表时指定为单表或广播表，其他表都默认创建为分区表，如不手动指定分区方式，默认按主键进行拆分。
• 主键拆分表仅支持在建表时指定主键，不支持对已有的表添加或删除主键。如果建表时没有指定主键，则会自动创建隐式主键。
• 主键拆分表的索引除非第一列不支持自动分区或指定为LOCAL索引，否则自动创建为全局二级索引。
• 分区策略变更后，主键拆分表将变成普通表（即不再适用原主键拆分表中的自动分区策略或索引转换规则）。如何变更分区策略，请参见变更表类型及分区策略。
• 自动分区数据库中，GSI名称不再要求唯一，建表语句中也不再要求强制指定GSI名称。所有GSI表会携带随机后缀，使用show full create table可以看到这些随机后缀。

语法

自动分区模式下的主键拆分会默认开启，建表语句无需添加特殊关键字，建表语法参见 CREATE TABLE。

下面通过几个例子展示分区效果，其中show create table会显示表的精简结构信息，而show full create table会显示完整的表结构。

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)

自动分区规则

• 如果目标表没有指定主键，PolarDB分布式版会启用隐式主键并将其作为拆分键，该主键为BIGINT类型的自增主键，且对用户不可见，使用show full create table可以查看完整的分区细节。
• 如果目标表指定了主键，PolarDB分布式版会使用该主键作为拆分键。如果为复合主键，会使用复合主键的所有列作为拆分键。
• 对于复合主键，按顺序作为KEY拆分方式的参数，如果遇到不支持的类型则跳过该列，如果第一列就不支持则会报错，必须手动指定为SINGLE表才能创建成功，SINGLE表建表语句参见单表与广播表。
• 自动分区使用分区表拆分算法，且拆分算法根据主键类型自动选择：