AUTO模式下的主鍵拆分 - PolarDB

本文主要介紹了在AUTO模式資料庫下主鍵拆分表的自動分區規則和索引變換規則。

在自動分區模式的資料庫中，如不手動指定分區方式，預設按主鍵進行拆分。

前提條件

PolarDB-X版本為5.4.13及以上。
建立資料庫時選擇了AUTO模式，即“MODE”選擇為“auto/partitioning”，關於建立資料庫時的模式選擇，請參見CREATE DATABASE。

注意事項

除去建立表時指定為單表或廣播表，其他表都預設建立為分區表，如不手動指定分區方式，預設按主鍵進行拆分。
主鍵拆分表僅支援在建表時指定主鍵，不支援對已有的表添加或刪除主鍵。如果建表時沒有指定主鍵，則會自動建立隱式主鍵。
主鍵拆分表的索引除非第一列不支援自動分區或指定為LOCAL索引，否則自動建立為全域二級索引。
分區策略變更後，主鍵拆分表將變成普通表（即不再適用原主鍵拆分表中的自動分區策略或索引轉換規則）。如何變更分區策略，請參見變更表類型及分區策略（AUTO模式）。
自動分區資料庫中，GSI名稱不再要求唯一，建表語句中也不再要求強制指定GSI名稱。所有GSI表會攜帶隨機尾碼，使用show full create table可以看到這些隨機尾碼。

文法

自動分區模式下的主鍵拆分會預設開啟，建表語句無需添加特殊關鍵字，建表文法參見 CREATE TABLE（AUTO模式）。

下面通過幾個例子展示分區效果，其中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-X會啟用隱式主鍵並將其作為拆分鍵，該主鍵為BIGINT類型的自增主鍵，且對使用者不可見，使用show full create table可以查看完整的分區細節。
如果目標表指定了主鍵，PolarDB-X會使用該主鍵作為拆分鍵。如果為複合主鍵，會使用複合主鍵的所有列作為拆分鍵。
對於複合主鍵，按順序作為KEY拆分方式的參數，如果遇到不支援的類型則跳過該列，如果第一列就不支援則會報錯，必須手動指定為SINGLE表才能建立成功，SINGLE表建表語句參見單表。
自動分區使用分區表拆分演算法，且拆分演算法根據主鍵類型自動選擇：

主鍵類型	拆分演算法
bit, float, double, time, year, tinyblob, blob, mediumblob, longblob, enum, decimal, binary, varbinary, tinytext, text, mediumtext, longtext, set, geometry	不支援
其他	KEY分區

索引轉換規則

主鍵拆分表上建立索引，除非明確指定為LOCAL索引，否則會自動轉換為全域二級索引，具體轉換規則如下：

如果指定了LOCAL關鍵字，即強制指定索引為本地索引。
對主鍵拆分表執行建立索引操作時，如果未指定LOCAL關鍵字，該操作將被自動地轉變為建立無覆蓋列（covering）的全域二級索引，並且索引列會依據上述的自動分區規則進行自動分區。如果需要建立普通的局部索引，您需要指定LOCAL關鍵字。
在對全域二級索引產生拆分演算法時，會使用全部的索引鍵進行拆分，同時，對於非UNIQUE約束，會附帶上主鍵作為拆分演算法參數。
建立全域二級索引和聚簇索引時，會建立一個帶 _local_ 首碼的本地索引。如果刪除全域二級索引，PolarDB-X會自動同步刪除對應的本地索引。
主鍵拆分表可以不指定全域二級索引、聚簇索引的分區方式，PolarDB-X會根據自動分區原則對索引鍵執行分區。

下述語句及其注釋為您展示了索引的轉換規則。

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`), -- 強制指定為本地索引
    ->   UNIQUE LOCAL INDEX `l_order` using btree (`order_id`), -- 強制指定為本地唯一索引
    ->   INDEX `i_seller` using btree (`seller_id`), -- 會被替換為GSI，自動分區
    ->   UNIQUE INDEX `i_order` using btree (`order_id`), -- 會被替換為UGSI，自動分區
    ->   GLOBAL INDEX `g_seller` using btree (`seller_id`), -- 自動分區
    ->   UNIQUE GLOBAL INDEX `g_order` using btree (`order_id`), -- 自動分區
    ->   CLUSTERED INDEX `c_seller` using btree (`seller_id`), -- 自動分區聚簇
    ->   UNIQUE CLUSTERED INDEX `c_order` using btree (`order_id`) -- 自動分區聚簇
    -> );
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限制

如需對主鍵拆分表執行下述DDL操作，存在一些限制。

DDL類別	DDL子句	說明與限制
CREATE INDEX	無	如果使用了`[UNIQUE] LOCAL INDEX`子句，PolarDB-X將建立本地索引，且會自動同步到聚簇索引表中。如果使用了`[UNIQUE] INDEX`子句，PolarDB-X將其轉換為對應的全域二級索引，同時，自動建立一個帶`_local_`首碼的本地索引並同步到聚簇索引表中。如果使用了`[UNIQUE] GLOBAL \| CLUSTERED INDEX`子句，PolarDB-X會自動建立一個帶`_local_`首碼的本地索引並同步到聚簇索引表中。說明如果未指定拆分方式則會自動拆分。
ALTER TABLE	ADD {INDEX \| KEY} [index_name] [index_type] (key_part,...) [index_option] ...
	ADD [COLUMN] (col_name column_definition,...)	PolarDB-X將自動添加對應的列到聚簇全域二級索引（CGSI）和帶唯一約束的聚簇全域二級索引（UCGSI）。支援復原操作。
	DROP [COLUMN] col_name	不允許刪除主鍵、主表拆分鍵、索引表拆分鍵和複合UNIQUE約束中的列。
	CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST \| AFTER col_name]	不允許重新命名主鍵、主表拆分鍵、索引表拆分鍵。不允許重新命名複合UNIQUE約束中的列。說明其它列的限制可通過執行Hint：/+TDDL: cmd_extra(ALLOW_LOOSE_ALTER_COLUMN_WITH_GSI=true)/命令解除。更多資訊，請參見如何使用HINT。如果僅需修改default資訊，推薦使用SET DEFAULT或DROP DEFAULT子句。
	MODIFY [COLUMN] col_name column_definition [FIRST \| AFTER col_name]
	ALTER TABLE tbl_name ALTER [COLUMN] col_name { SET DEFAULT {literal \| (expr)} \| DROP DEFAULT }	支援復原操作。說明如果列的預設值為current_timestamp，則不支援復原操作。