mycat批量任务分库分表探索

业务场景

有一个终端配置的批量修改任务，每个任务创建时会导入一批需要修改配置的终端，任务运行后，依次去修改每个终端的配置，并记录原值、新值、修改情况。

表设计

任务表

DROP TABLE IF EXISTS `t_task`;
CREATE TABLE `t_task` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `task_name` varchar(64) NOT NULL DEFAULT '' COMMENT '任务名称',
  `task_status` varchar(1) NOT NULL DEFAULT '' COMMENT '状态(0未执行、1执行中、2已完成、3已暂停、4创建中、5创建失败)',
  `start_date` date NOT NULL COMMENT '任务开始执行日期',
  `end_date` date NOT NULL COMMENT '任务截止执行日期',
  `start_time` time NOT NULL COMMENT '任务开始执行时间',
  `end_time` time NOT NULL COMMENT '任务截止执行时间',
  `file_path` varchar(255) NOT NULL DEFAULT '' COMMENT '文件保存路径',
  `creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人',
  `task_desc` varchar(64) NOT NULL DEFAULT '' COMMENT '任务描述',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '任务创建时间',
  `modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '任务更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`) USING BTREE COMMENT '创建时间索引',
  KEY `idx_exec_time` (`start_date`,`end_date`,`start_time`,`end_time`) COMMENT '执行时间索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务表';

创建任务时，往t_task插入一条数据，异步解析file_path 对应文件将终端入库到t_task_terminal

任务-终端表

DROP TABLE IF EXISTS `t_task_terminal`;
CREATE TABLE `t_task_terminal` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '逻辑主键',
  `task_id` bigint(20) unsigned NOT NULL COMMENT '任务主键',
  `sn` varchar(32) NOT NULL COMMENT '终端序列号',
  `exec_status` varchar(1) NOT NULL DEFAULT '0' COMMENT '状态(0未执行、1执行成功、2执行失败)',
  `exec_desc` varchar(64) NOT NULL COMMENT '执行描述',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',
  `modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_taskid__status` (`task_id`,`exec_status`),
  KEY `idx_sn` (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务-终端明细表';

定时任务扫描t_task 表中药执行的任务，然后在t_task_terminal 表查询对应终端下发配置

任务-终端-执行表

DROP TABLE IF EXISTS `t_task_terminal_execution`;
CREATE TABLE `t_task_terminal_execution` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '逻辑主键',
  `task_detail_id` bigint(20) unsigned NOT NULL COMMENT '任务明细id',
  `sn` varchar(32) NOT NULL COMMENT 'sn',
  `old_vlue` varchar(1024) NOT NULL COMMENT '原值',
  `param_str` varchar(1024) NOT NULL COMMENT '下发内容',
  `exec_result` varchar(1) NOT NULL COMMENT '1-成功，2-失败',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_detail_id` (`task_detail_id`),
  KEY `idx_sn` (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务-终端执行明细表';

对于可以下发配置的终端，需将前后配置记录到执行表，方便后续问题排查。

分库分表

• 对于任务表，数据量相对较小，可以存放在单节点上

<table name="t_task" dataNode="data_node_0" ruleRequired="false"></table>

• 对于任务明细表，通常我们需要连续查询同一任务下的这批终端的数据，因此我们希望task_id相同的任务明细数据存放在同一数据节点上，因此可以采用id取模分片

  <table name="t_task_terminal" dataNode="rms_data_node_$0-n" rule="rule-task_terminal" primaryKey="id" />
  

  <tableRule name="rule-task_terminal">
  <rule>
  <columns>task_id</columns>
  <algorithm>mod-long-task_terminal</algorithm>
  </rule>
  </tableRule>
  
  <function name="mod-long-task_terminal" class="io.mycat.route.function.PartitionByMod">
  <property name="count">n</property>
  </function>
  

  其中n为子库节点数

• 对于执行明细表

  我们通常希望能够将t_task_terminal_execution 和t_task_terminal 关联使用，因此我们希望task_detail_id相同的数据能在同一数据节点上，从而提高查询效率，故可以考虑E-R分片

  <table name="t_task_terminal" dataNode="rms_data_node_$0-n" rule="rule-vlan-detail" primaryKey="id" >
  <childTable name="t_task_terminal_execution" primaryKey="id" joinKey="task_detail_id" parentKey="id" />
  </table>
  

  附：mycat E-R分片介绍

有一类业务，例如订单（order）跟订单明细（order_detail）,明细表会依赖于订单，也就是说会存在表的主从关系，这类似业务的切分可以抽象出合适的切分规则，比如根据用户 ID 切分,其他相关的表都依赖于用户 ID，再或者根据订单 ID 切分，总之部分业务总会可以抽象出父子关系的表。这类表适用于 ER 分片表，子表的记录与所关联的父表记录存放在同一个数据分片上，避免数据 Join 跨库操作。
以 order 与 order_detail 例子为例，schema.xml 中定义如下的分片配置,order,order_detail 根据 order_id进行数据切分，保证相同 order_id 的数据分到同一个分片上，在进行数据插入操作时，Mycat 会获取 order 所在的分片，然后将 order_detail 也插入到 order 所在的分片。

<table name="order" dataNode="dn$1-32" rule="mod-long">
	<childTable name="order_detail" primaryKey="id" joinKey="order_id" parentKey="order_id" />
</table>