腾讯TDSQL分区表介绍(2/2)

胖五斤 • 2023-01-02 • 云技术社区 • 274 阅读

二级分区

二级分区的情况，相比一级分区复杂一些。下面我们来看下不同的组合情况。（其中，一级hash的情况是比较特殊的，我们先来看下）

一级hash二级range分区

CREATE TABLE `t_hash_range` (
  `id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` BIGINT ( 20 ) NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `price` VARCHAR ( 30 ) NOT NULL DEFAULT '' COMMENT '价格',
  `status` INT ( 11 ) NOT NULL DEFAULT '0' COMMENT '0初始化中 1失败 2成功',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` VARCHAR ( 100 ) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '创建人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `updated_by` VARCHAR ( 100 ) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '更新人',
PRIMARY KEY ( `id`, `biz_id` ),
UNIQUE KEY `uk_biz_id` ( `biz_id` )) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表' shardkey = biz_id
PARTITION BY RANGE (month(create_time)) (
  PARTITION p0 VALUES LESS THAN ('2022-01-01 00:00:00'),
  PARTITION p1 VALUES LESS THAN ('2023-01-01 00:00:00')
);

可以看到，除了指定shardkey外，还增加了partition by range定义。查看一下在set上的情况：

MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_hash_range   |
+----------------+
1 row in set (0.00 sec)

MySQL [test]> /*sets:allsets*/show tables;
+--------------------------+------------------+
| Tables_in_test           | info             |
+--------------------------+------------------+
| t_hash_range             | set_1667742846_1 |
| t_hash_range_tdsql_subp0 | set_1667742846_1 |
| t_hash_range_tdsql_subp1 | set_1667742846_1 |
| t_hash_range             | set_1667742895_3 |
| t_hash_range_tdsql_subp0 | set_1667742895_3 |
| t_hash_range_tdsql_subp1 | set_1667742895_3 |
+--------------------------+------------------+
6 rows in set (0.00 sec)

在proxy层看到的只有一个表，但是在实际set上，每个set对应会有多个表（根据二级分区定义的partition情况）。看下表结构情况：

MySQL [test]> /*sets:allsets*/show create table t_hash_range_tdsql_subp0;
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| Table                    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | info             |
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| t_hash_range_tdsql_subp0 | CREATE TABLE `t_hash_range_tdsql_subp0` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `price` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '价格',
  `status` int NOT NULL DEFAULT '0' COMMENT '0初始化中 1失败 2成功',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '创建人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `updated_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '更新人',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表'
/*!50100 PARTITION BY LIST (murmurHashCodeAndMod(`biz_id`,16))
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
 PARTITION p4 VALUES IN (4) ENGINE = InnoDB,
 PARTITION p5 VALUES IN (5) ENGINE = InnoDB,
 PARTITION p6 VALUES IN (6) ENGINE = InnoDB,
 PARTITION p7 VALUES IN (7) ENGINE = InnoDB) */ | set_1667742846_1 |
| t_hash_range_tdsql_subp0 | CREATE TABLE `t_hash_range_tdsql_subp0` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `price` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '价格',
  `status` int NOT NULL DEFAULT '0' COMMENT '0初始化中 1失败 2成功',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '创建人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `updated_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '更新人',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表'
/*!50100 PARTITION BY LIST (murmurHashCodeAndMod(`biz_id`,16))
(PARTITION p8 VALUES IN (8) ENGINE = InnoDB,
 PARTITION p9 VALUES IN (9) ENGINE = InnoDB,
 PARTITION p10 VALUES IN (10) ENGINE = InnoDB,
 PARTITION p11 VALUES IN (11) ENGINE = InnoDB,
 PARTITION p12 VALUES IN (12) ENGINE = InnoDB,
 PARTITION p13 VALUES IN (13) ENGINE = InnoDB,
 PARTITION p14 VALUES IN (14) ENGINE = InnoDB,
 PARTITION p15 VALUES IN (15) ENGINE = InnoDB) */ | set_1667742895_3 |
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

可以看到，不同set里的表，基于MySQL原生hash list再做partition。

一级hash二级list分区

CREATE TABLE `t_hash_list` (
  `id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` BIGINT ( 20 ) NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `price` VARCHAR ( 30 ) NOT NULL DEFAULT '' COMMENT '价格',
  `status` INT ( 11 ) NOT NULL DEFAULT '0' COMMENT '0初始化中 1失败 2成功',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` VARCHAR ( 100 ) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '创建人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `updated_by` VARCHAR ( 100 ) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '更新人',
PRIMARY KEY ( `id`, `biz_id` ),
UNIQUE KEY `uk_biz_id` ( `biz_id` )) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表' shardkey = biz_id
PARTITION BY LIST (status) (
  PARTITION p0 VALUES IN (1,2,3),
  PARTITION p1 VALUES IN (4,5,6)
);

可以看到，除了指定shardkey外，还增加了partition by list定义。查看一下在set上的情况：

MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_hash_list    |
+----------------+
1 row in set (0.00 sec)

MySQL [test]> /*sets:allsets*/show tables;
+-------------------------+------------------+
| Tables_in_test          | info             |
+-------------------------+------------------+
| t_hash_list             | set_1667742895_3 |
| t_hash_list             | set_1667742846_1 |
| t_hash_list_tdsql_subp0 | set_1667742846_1 |
| t_hash_list_tdsql_subp1 | set_1667742846_1 |
| t_hash_list_tdsql_subp0 | set_1667742895_3 |
| t_hash_list_tdsql_subp1 | set_1667742895_3 |
+-------------------------+------------------+
6 rows in set (0.01 sec)

类似的，每个set对应会有多个表（根据二级分区定义的partition情况）。看下表结构情况：

MySQL [test]> /*sets:allsets*/show create table t_hash_list_tdsql_subp0;
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| Table                   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | info             |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| t_hash_list_tdsql_subp0 | CREATE TABLE `t_hash_list_tdsql_subp0` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `price` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '价格',
  `status` int NOT NULL DEFAULT '0' COMMENT '0初始化中 1失败 2成功',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '创建人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `updated_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '更新人',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表'
/*!50100 PARTITION BY LIST (murmurHashCodeAndMod(`biz_id`,16))
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
 PARTITION p4 VALUES IN (4) ENGINE = InnoDB,
 PARTITION p5 VALUES IN (5) ENGINE = InnoDB,
 PARTITION p6 VALUES IN (6) ENGINE = InnoDB,
 PARTITION p7 VALUES IN (7) ENGINE = InnoDB) */ | set_1667742846_1 |
| t_hash_list_tdsql_subp0 | CREATE TABLE `t_hash_list_tdsql_subp0` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `price` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '价格',
  `status` int NOT NULL DEFAULT '0' COMMENT '0初始化中 1失败 2成功',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '创建人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `updated_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '更新人',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表'
/*!50100 PARTITION BY LIST (murmurHashCodeAndMod(`biz_id`,16))
(PARTITION p8 VALUES IN (8) ENGINE = InnoDB,
 PARTITION p9 VALUES IN (9) ENGINE = InnoDB,
 PARTITION p10 VALUES IN (10) ENGINE = InnoDB,
 PARTITION p11 VALUES IN (11) ENGINE = InnoDB,
 PARTITION p12 VALUES IN (12) ENGINE = InnoDB,
 PARTITION p13 VALUES IN (13) ENGINE = InnoDB,
 PARTITION p14 VALUES IN (14) ENGINE = InnoDB,
 PARTITION p15 VALUES IN (15) ENGINE = InnoDB) */ | set_1667742895_3 |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
2 rows in set (0.01 sec)

可以看到，不同set里的表，基于MySQL原生hash list再做partition。

一级range二级range分区

CREATE TABLE `t_range_range` (
  `id` BIGINT (20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` BIGINT (20) NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `message` VARCHAR (30) NOT NULL DEFAULT '' COMMENT '内容',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`, `biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '日志流水表' 
PARTITION BY RANGE (mod(biz_id,10)) (
  PARTITION p0 VALUES LESS THAN (5),
  PARTITION p1 VALUES LESS THAN (10)
)
TDSQL_DISTRIBUTED BY RANGE(create_time)(
  s1 VALUES LESS THAN('2022-01-01 00:00:00'),
  s2 VALUES LESS THAN('2023-01-01 00:00:00')
);

注意TDSQL_DISTRIBUTED BY RANGE要放在建表语句的最后。查看一下在set上的情况：

MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_range_range  |
+----------------+
1 row in set (0.00 sec)

MySQL [test]> /*sets:allsets*/show tables;
+----------------+------------------+
| Tables_in_test | info             |
+----------------+------------------+
| t_range_range  | set_1667742846_1 |
| t_range_range  | set_1667742895_3 |
+----------------+------------------+
2 rows in set (0.00 sec)

在每个set上都有创建相同的的，表结构情况如下：

MySQL [test]> /*sets:allsets*/show create table t_range_range;
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | info             |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| t_range_range | CREATE TABLE `t_range_range` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `message` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '内容',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='日志流水表'
/*!50100 PARTITION BY RANGE ((`biz_id` % 10))
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */                      | set_1667742846_1 |
| t_range_range | CREATE TABLE `t_range_range` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `message` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '内容',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='日志流水表'
/*!50100 PARTITION BY RANGE ((`biz_id` % 10))
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */                      | set_1667742895_3 |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

数据根据create_time被路由到不同的set上，然后在具体set上基于biz_id再通过MySQL原生partition规则访问。

一级range二级list分区

CREATE TABLE `t_range_list` (
  `id` BIGINT (20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` BIGINT (20) NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `message` VARCHAR (30) NOT NULL DEFAULT '' COMMENT '内容',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`, `biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '日志流水表' 
PARTITION BY LIST (mod(biz_id,2)) (
  PARTITION p0 VALUES IN (0),
  PARTITION p1 VALUES IN (1)
)
TDSQL_DISTRIBUTED BY RANGE(create_time)(
  s1 VALUES LESS THAN('2022-01-01 00:00:00'),
  s2 VALUES LESS THAN('2023-01-01 00:00:00')
);

跟t_range_range情况类似：

MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_range_list   |
+----------------+
1 row in set (0.00 sec)

MySQL [test]> /*sets:allsets*/show tables;
+----------------+------------------+
| Tables_in_test | info             |
+----------------+------------------+
| t_range_list   | set_1667742846_1 |
| t_range_list   | set_1667742895_3 |
+----------------+------------------+
2 rows in set (0.00 sec)

MySQL [test]> /*sets:allsets*/show create table t_range_list;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | info             |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| t_range_list | CREATE TABLE `t_range_list` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `message` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '内容',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='日志流水表'
/*!50100 PARTITION BY LIST ((`biz_id` % 2))
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB) */                      | set_1667742846_1 |
| t_range_list | CREATE TABLE `t_range_list` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `message` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '内容',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='日志流水表'
/*!50100 PARTITION BY LIST ((`biz_id` % 2))
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB) */                      | set_1667742895_3 |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

一级list二级range分区

一级list下的二级分区跟一级range下的二级分区情况类似。

CREATE TABLE `t_list_range` (
  `id` BIGINT (20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` BIGINT (20) NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `cid` VARCHAR (30) NOT NULL DEFAULT '' COMMENT '证件号',
  `name` VARCHAR (30) NOT NULL DEFAULT '' COMMENT '姓名',
  `province` VARCHAR (30) NOT NULL DEFAULT '' COMMENT '省份',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`, `biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '人口统计表' 
PARTITION BY RANGE (mod(biz_id,10)) (
  PARTITION p0 VALUES LESS THAN (5),
  PARTITION p1 VALUES LESS THAN (10)
)
TDSQL_DISTRIBUTED BY LIST(province)(
  s1 VALUES IN('guangdong','hunan','fujian'),
  s2 VALUES IN('shanxi','anhui','heilongjiang')
);

查看在set上的情况：

MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_list_range   |
+----------------+
1 row in set (0.00 sec)

MySQL [test]> /*sets:allsets*/show tables;
+----------------+------------------+
| Tables_in_test | info             |
+----------------+------------------+
| t_list_range   | set_1667742895_3 |
| t_list_range   | set_1667742846_1 |
+----------------+------------------+
2 rows in set (0.00 sec)

MySQL [test]> /*sets:allsets*/show create table t_list_range;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | info             |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| t_list_range | CREATE TABLE `t_list_range` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `cid` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '证件号',
  `name` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `province` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '省份',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='人口统计表'
/*!50100 PARTITION BY RANGE ((`biz_id` % 10))
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */                           | set_1667742846_1 |
| t_list_range | CREATE TABLE `t_list_range` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `cid` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '证件号',
  `name` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `province` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '省份',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='人口统计表'
/*!50100 PARTITION BY RANGE ((`biz_id` % 10))
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */                           | set_1667742895_3 |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

一级list二级list分区

CREATE TABLE `t_list_list` (
  `id` BIGINT (20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` BIGINT (20) NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `cid` VARCHAR (30) NOT NULL DEFAULT '' COMMENT '证件号',
  `name` VARCHAR (30) NOT NULL DEFAULT '' COMMENT '姓名',
  `province` VARCHAR (30) NOT NULL DEFAULT '' COMMENT '省份',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`, `biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '人口统计表' 
PARTITION BY LIST (mod(biz_id,2)) (
  PARTITION p0 VALUES IN (0),
  PARTITION p1 VALUES IN (1)
)
TDSQL_DISTRIBUTED BY LIST(province)(
  s1 VALUES IN('guangdong','hunan','fujian'),
  s2 VALUES IN('shanxi','anhui','heilongjiang')
);

查看在set上的情况：

MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_list_list    |
+----------------+
1 row in set (0.00 sec)

MySQL [test]> /*sets:allsets*/show tables;
+----------------+------------------+
| Tables_in_test | info             |
+----------------+------------------+
| t_list_list    | set_1667742895_3 |
| t_list_list    | set_1667742846_1 |
+----------------+------------------+
2 rows in set (0.00 sec)

MySQL [test]> /*sets:allsets*/show create table t_list_list;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | info             |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| t_list_list | CREATE TABLE `t_list_list` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `cid` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '证件号',
  `name` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `province` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '省份',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='人口统计表'
/*!50100 PARTITION BY LIST ((`biz_id` % 2))
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB) */                           | set_1667742895_3 |
| t_list_list | CREATE TABLE `t_list_list` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_id` bigint NOT NULL DEFAULT '0' COMMENT '业务主键-订单编号',
  `cid` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '证件号',
  `name` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `province` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '省份',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`biz_id`),
  UNIQUE KEY `uk_biz_id` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='人口统计表'
/*!50100 PARTITION BY LIST ((`biz_id` % 2))
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB) */                           | set_1667742846_1 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

总结

本文针对TDSQL支持的建表类型做了测试，并通过/sets:allsets/透传到各sets上查看表的存储情况。

  • 单表：存在第一个set上
  • 广播表：在每一个set上都有相同的表结构和数据
  • 分区表：有一级分区、二级分区；不同分区情况不一样。其中：
    - 一级分区决定数据去哪个set
    - 二级分区决定数据去哪个物理分区或者是物理表
  • 一级分区：
    - hash：按set数拆分，底层set上的表使用MySQL原生的hash list分区。
    - range：按range分布到set上的单表。
    - list：按list分布到set上的单表。
  • 二级分区：
    - 一级hash二级range：按set数拆分，底层set上，range会分布为多个物理表，每个表使用MySQL原生的hash list分区
    - 一级hash二级list：按set数拆分，底层set上，list会分布为多个物理表，每个表使用MySQL原生的hash list分区
    - 一级range二级range：按range分布到set上的单表，单表再根据二级range进行原生分区。
    - 一级range二级list：按range分布到set上的单表，单表再根据二级list进行原生分区。
    - 一级list二级range：按list分布到set上的单表，单表再根据二级range进行原生分区。
    - 一级list二级list：按list分布到set上的单表，单表再根据二级list进行原生分区。
本站文章资源均来源自网络，除非特别声明，否则均不代表站方观点，并仅供查阅，不作为任何参考依据！
如有侵权请及时跟我们联系，本站将及时删除！
如遇版权问题，请查看 本站版权声明
THE END
分享
二维码
海报
腾讯TDSQL分区表介绍(2/2)
二级分区的情况，相比一级分区复杂一些。下面我们来看下不同的组合情况。（其中，一级hash的情况是比较特殊的，我们先来看下）
<<上一篇
下一篇>>