二级分区
二级分区的情况，相比一级分区复杂一些。下面我们来看下不同的组合情况。（其中，一级hash的情况是比较特殊的，我们先来看下）
一级hash二级range分区
代码语言：txt复制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上的情况：
代码语言：txt复制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情况）。看下表结构情况：
代码语言：txt复制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分区
代码语言：txt复制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上的情况：
代码语言：txt复制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情况）。看下表结构情况：
代码语言：txt复制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分区
代码语言：txt复制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上的情况：
代码语言：txt复制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上都有创建相同的的，表结构情况如下：
代码语言：txt复制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分区
代码语言：txt复制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情况类似：
代码语言：txt复制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下的二级分区情况类似。
代码语言：txt复制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上的情况：
代码语言：txt复制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分区
代码语言：txt复制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上的情况：
代码语言：txt复制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进行原生分区。
