建表类型
TDSQL集群支持创建集中式实例和分布式实例。在使用分布式实例的时候，可以创建以下几种类型的表：
- 单表
- 广播表
- 分区表
其中，分区表支持一级分区、二级分区。一级分区支持hash、range、list的方式，二级分区支持range、list的方式：
分区表类型.png
MySQL [test]> /*proxy*/ show status; ----------------------------- ----------------------------------------------------- | status_name | value | ----------------------------- -----------------------------------------------------
| cluster | group_1667742642_27 |
| set_1667742846_1:ip | dbip1:4002;s1@dbip2:4002@1@IDC_QCLOUD_GZ6@0 |
| set_1667742846_1:alias | s1 |
| set_1667742846_1:hash_range | 0---7 |
| set_1667742895_3:ip | dbip1:4003;s1@dbip2:4003@1@IDC_QCLOUD_GZ6@0 |
| set_1667742895_3:alias | s2 |
| set_1667742895_3:hash_range | 8---15 |
| set | set_1667742846_1,set_1667742895_3 |
----------------------------- -----------------------------------------------------
8 rows in set (0.00 sec)测试使用的实例是分布式实例，2个set（物理分片），16个shard（逻辑分片）
下面介绍一下不同表创建后，表结构和数据在不同set上的区别。
单表
- 单表不支持水平扩容，常用于存储相对独立、访问量小的业务数据。
- 所有单表都存放在分布式实例的第一个分片(set)上。 示例：
CREATE TABLE `t_single` (
`biz_id` int(10) unsigned NOT NULL COMMENT '业务id',
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
`times` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '统计次数',
`mtime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
PRIMARY KEY (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;建表语句跟普通的MySQL建表一样。建表后，可以通过/sets:allsets/ show tables;查看在各分片上的建表情况：
代码语言：txt复制MySQL [test]> /*sets:allsets*/ show tables;
---------------- ------------------
| Tables_in_test | info |
---------------- ------------------
| t_single | set_1667742846_1 |
---------------- ------------------
1 row in set (0.00 sec)可以看到，单表，只存在第一个set上。
广播表
- 常用于存储需联合查询、变更量小的业务数据
- 该表的所有操作都将广播到所有物理分片(set)中，每个 set 都有该表的全量数据
- 如果广播表数据量/变更量过大，所有物理分片(set)负载较高
- 方便和分表进行联表查询，与任意表做联表查询都无需跨物理分片(set)联表查询
- 使用分布式事务维护多个物理分片(set)间的数据一致性
- 不支持水平扩容 示例：
CREATE TABLE `t_broadcast` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`package_name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
`version` varchar(20) NOT NULL DEFAULT '' COMMENT '版本号',
`mtime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 shardkey=noshardkey_allset;注意到建表语句的最后，有一个shardkey=noshardkey_allset，表示创建的是广播表。查看一下广播表在set上的情况：
代码语言：txt复制MySQL [test]> /*sets:allsets*/ show tables; ---------------- ------------------ | Tables_in_test | info | ---------------- ------------------
| t_broadcast | set_1667742846_1 |
| t_single | set_1667742846_1 |
| t_broadcast | set_1667742895_3 |
---------------- ------------------
3 rows in set (0.00 sec)可以看到，跟单表不同，广播表t_broadcast在每个set上都存在。另外可以通过show create命令查看表结构，2个set上的结构是一样的。插入数据后，2个set上的表都会有相同的数据：
代码语言：txt复制MySQL [test]> /*sets:allsets*/ show create table t_broadcast;
------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------
| Table | Create Table | info |
------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------
| t_broadcast | CREATE TABLE `t_broadcast` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`package_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '名称',
`version` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '版本号',
`mtime` int unsigned NOT NULL DEFAULT '0' COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci | set_1667742846_1 |
| t_broadcast | CREATE TABLE `t_broadcast` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`package_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '名称',
`version` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '版本号',
`mtime` int unsigned NOT NULL DEFAULT '0' COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci | set_1667742895_3 |
------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------
2 rows in set (0.01 sec)
MySQL [test]> insert into t_broadcast(id,package_name,version,mtime) values(1,'one','v1',2022);
Query OK, 1 row affected (2.05 sec)
MySQL [test]> /*sets:allsets*/ select * from t_broadcast;
---- -------------- --------- ------- ------------------
| id | package_name | version | mtime | info |
---- -------------- --------- ------- ------------------
| 1 | one | v1 | 2022 | set_1667742895_3 |
| 1 | one | v1 | 2022 | set_1667742846_1 |
---- -------------- --------- ------- ------------------
2 rows in set (0.00 sec)分区表
- 常用于存储数据量大、访问量大的业务数据
- 根据shardkey将数据分布到不同的分片(set)
- 支持Hash、Range、List三种分片算法
- 需指定字段作为shardkey
- 主键和所有唯一索引必须包含shardkey
- INSERT / REPLACE 语句中字段必须包含shardkey，否则会报错拒绝
- SELECT 语句如果不带shardkey，需查询所有Set后聚合处理结果，影响执行效率
- 支持多个分表联合查询(Join) - 根据shardkey进行联合，无需跨物理分片(set)联表查询，执行效率较高 - 不根据shardkey进行联合，需要进行跨物理分片(set)联表查询，执行效率较低
- 支持水平扩容一级分区分区表有一级分区和二级分区的概念。一级分区主要是面向多个set来讲，对应数据在水平方向的划分。一级分区支持hash、list、range三种类型。shardkey的限制
- Shardkey 字段必须是主键以及所有唯一索引的一部分
- Shardkey字段的值不能为中文，因为Proxy不会转换字符集，所以不同字符集可能会路由到不同的分区
- 不支持Update shardkey字段的值，如需要可拆分成删除和新增2步操作
示例：
hash
代码语言：txt复制CREATE TABLE `t_hash` (
`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;注意到建表语句的最后，有一个shardkey=biz_id，表示创建的是分区表，分区键是biz_id（该字段同时是主q键和唯一索引的一部分）。查看一下在set上的情况：
代码语言：txt复制MySQL [test]> /*sets:allsets*/ show tables;
---------------- ------------------
| Tables_in_test | info |
---------------- ------------------
| t_hash | set_1667742846_1 |
| t_hash | set_1667742895_3 |
---------------- ------------------
2 rows in set (0.00 sec)可以看到，分区表在每个set上都存在。另外可以通过show create命令查看表结构，2个set上的结构有所区别：
代码语言：txt复制MySQL [test]> /*sets:allsets*/ show create table t_hash;
-------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------
| Table | Create Table | info |
-------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------
| t_hash | CREATE TABLE `t_hash` (
`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 | CREATE TABLE `t_hash` (
`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上的建表语句，多了这一段：
代码语言：txt复制/*!50100 PARTITION BY LIST (murmurHashCodeAndMod(`biz_id`,16))并且2个set上的paritition情况不同，第一个set是0-7，第2个set是8-15.（这个与实例配置是对应上的）：
代码语言：txt复制MySQL [test]> /*proxy*/ show status; ----------------------------- ----------------------------------------------------- | status_name | value | ----------------------------- -----------------------------------------------------
| cluster | group_1667742642_27 |
| set_1667742846_1:ip | dbip1:4002;s1@dbip2:4002@1@IDC_QCLOUD_GZ6@0 |
| set_1667742846_1:alias | s1 |
| set_1667742846_1:hash_range | 0---7 |
| set_1667742895_3:ip | dbip1:4003;s1@dbip2:4003@1@IDC_QCLOUD_GZ6@0 |
| set_1667742895_3:alias | s2 |
| set_1667742895_3:hash_range | 8---15 |
| set | set_1667742846_1,set_1667742895_3 |
----------------------------- -----------------------------------------------------
8 rows in set (0.00 sec)也就是说，针对一级分区表，使用hash分区类型的情况，对应的表会在每个set上创建一个list分区表（MySQL原生分区），并且分区规则与实例设置有关。
接下来看下插入数据后，数据的分布情况：
代码语言：txt复制MySQL [test]> insert into t_hash(id,biz_id,price,status) values(1,1,'1',2),(2,2,'2',2),(3,3,'3',3),(4,4,'4',4);
Query OK, 4 rows affected (0.04 sec)
MySQL [test]> /*sets:allsets*/ select * from t_hash;
---- -------- ------- -------- --------------------- ------------ --------------------- ------------ ------------------
| id | biz_id | price | status | create_time | created_by | update_time | updated_by | info |
---- -------- ------- -------- --------------------- ------------ --------------------- ------------ ------------------
| 1 | 1 | 1 | 2 | 2022-11-07 11:38:48 | | 2022-11-07 11:38:48 | | set_1667742846_1 |
| 2 | 2 | 2 | 2 | 2022-11-07 11:38:48 | | 2022-11-07 11:38:48 | | set_1667742895_3 |
| 4 | 4 | 4 | 4 | 2022-11-07 11:38:48 | | 2022-11-07 11:38:48 | | set_1667742895_3 |
| 3 | 3 | 3 | 3 | 2022-11-07 11:38:48 | | 2022-11-07 11:38:48 | | set_1667742895_3 |
---- -------- ------- -------- --------------------- ------------ --------------------- ------------ ------------------
4 rows in set (0.00 sec)可以看到，数据会根据分片算法路由到2个set上。每个set只保存整个表的部分数据，所有set的数据才组成完整的一个表。
list
代码语言：txt复制CREATE TABLE `t_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 = '人口统计表' TDSQL_DISTRIBUTED BY LIST(province)(
s1 VALUES IN('guangdong','hunan','fujian'),
s2 VALUES IN('shanxi','anhui','heilongjiang')
);注意到建表语句，跟hash类型不同，这里使用的是TDSQL_DISTRIBUTED BY 。查看一下在set上的情况：
代码语言：txt复制MySQL [test]> /*sets:allsets*/ show tables;
---------------- ------------------
| Tables_in_test | info |
---------------- ------------------
| t_list | set_1667742895_3 |
| t_list | set_1667742846_1 |
---------------- ------------------
2 rows in set (0.00 sec)可以看到，分区表在每个set上都存在。另外可以通过show create命令查看表结构，2个set上的结构是一样的：
代码语言：txt复制MySQL [test]> /*sets:allsets*/ show create table t_list;
-------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------
| Table | Create Table | info |
-------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------
| t_list | CREATE TABLE `t_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='人口统计表' | set_1667742895_3 |
| t_list | CREATE TABLE `t_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='人口统计表' | set_1667742846_1 |
-------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------
2 rows in set (0.00 sec)数据分布上，根据分区规则，会将数据根据province对应路由：
代码语言：txt复制TDSQL_DISTRIBUTED BY LIST(province)(
s1 VALUES IN('guangdong','hunan','fujian'),
s2 VALUES IN('shanxi','anhui','heilongjiang')
);range
代码语言：txt复制CREATE TABLE `t_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 = '日志流水表'
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')
);注意到建表语句，跟list类型类似，这里使用的是TDSQL_DISTRIBUTED BY RANGE。查看一下在set上的情况：
代码语言：txt复制MySQL [test]> /*sets:allsets*/show tables;
---------------- ------------------
| Tables_in_test | info |
---------------- ------------------
| t_range | set_1667742895_3 |
| t_range | set_1667742846_1 |
---------------- ------------------
2 rows in set (0.00 sec)可以看到，分区表在每个set上都存在。另外可以通过show create命令查看表结构，2个set上的结构是一样的：
代码语言：txt复制MySQL [test]> /*sets:allsets*/show create table t_range;
--------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------
| Table | Create Table | info |
--------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------
| t_range | CREATE TABLE `t_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='日志流水表' | set_1667742846_1 |
| t_range | CREATE TABLE `t_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='日志流水表' | set_1667742895_3 |
--------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------
2 rows in set (0.00 sec)数据分布上，根据分区规则，会将数据根据create_time对应路由：
代码语言：txt复制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一级分区表目前只支持hash、range、list三种规则。
- 创建一级list分区表语句中指定的s1和s2是每个set的别名，基于实现原理，s1、s2不能自定义，只能按照顺序依次命名为s1、s2…
- DB 5.7版本不支持TDSQL_DISTRIBUTED BY range|list的语法
- hash类型的分区比较特殊，每个set上会根据shard数创建不同的partition，list和range在每个set上表结构一致。
