日百万任务分表存储设计

1、业务需求

系统随着业务的发展，客户规模在不断的增长，系统的数据也会同时在增长。为了响应集团的号召实现能力复用，公司决定构建统一的任务工单系统，将外呼任务单、摆摊任务单等任务单，进行统一的管理和调度，然后分派给一线操作员；根据现在的量进行估算每天大概有100万增量的任务工单，这些工单数据如果都存放在同一张表上，那么就会形成成一个巨大的表，那么在查询和修改数据的时候，即使加了索引已久会很慢。

2、技术选型

如何存储日百万增量的任务的任务库设置，是第一个要先解决的问题，基于这个问题，项目组展开一次技术选型的讨论，主要解决两个问题，1、使用什么数据库存储任务单；2、数据以什么形式进行存储。

（1）、数据库考虑了MySql和MongoDB

因为任务需要一线操作员（大概3万个渠道员工）通过手机和电脑的工作台上实时的查询到自己的待办任务，需要低延迟，且需要有一定的并发性，所以使用MongoDB的数据分片功能能很好的支撑大数据量的需求。但是MongoDB有以下几个问题，导致我们放弃使用它。

• 数据组件对接方面：公司现有的数据分析，主要是通过ETL将数据抽到数仓进行数据处理和指标制作，ETL的源端主要是关系性数据库源，例如MySql、Oracle等；

• 维护性方面：针对关系性数据库，团队有专门的维护团队，确保数据库的稳定性和高可用；且可视化客户端方面，公司有成熟可用的可视化客户端，方便数据查询。

基于上面的两点，团队决定使用MySql作为任务单的存储数据库

（2）、分表的形式进行数据存储

分表是将一份大的表数据进行拆分后存放至多个结构一样的拆分表中；这里将任务表的数据进行分表存储，每张表的数据包括一部分的数据，所有的表合并在一起就是全量的任务集。任务表的分表主要需要实现2个功能。

1. 避免跨表查询：前台查询任务的条件主要包括了任务的处理人、任务编号、处理类型、处理状态等条件，需要根据查询的条件来动态的决定查询哪一张表。

2. 避免数据分布不均匀：尽量通过分片策略，保证每张表的数据量差距不大。

3. 分片扩容的时候尽量减少数据迁移：尽量保证分片扩容的时候，历史数据迁移量最小。

4. 易于维护：表名最好可以解释，维护人员能够快速理解分片逻辑，能在不看代码的情况下，快速定位某一条数据在哪张表。

针对上面的功能需求，对任务单分表实现思路如下：

1. 确定分片主键：针对上面的2个功能，需要根据处理人、任务编号、处理类型、处理状态等条件信息，因为处理人、任务编号等和归属地市有关、处理类型和任务单类型有关，一般任务单不存在跨地市排单的情况，所有可以通过（任务处理人归属地市+任务类型）2个字段作为数据的分片主键。

2. 确定分片策略：数据的分片主键确定后，因为分片主键中归属地市可以穷举，但是任务类型可能会随时增加，所以数据表名可以设计为TASK_{地市ID}_{任务类型编号 % mod}，需要提前预估不同任务类型的数据量，尽量确保每张表的数据量均匀分布。

3. 分片扩容策略：数据需要扩容的时候，通过调整任务类型编号，进行分片数据的调整，以及通过调整mod（去模）进行表扩容，因为涉及到大量数据各界操作，mod取值尽量是2的倍数，这样可以把原先的表拆分成2张表，降低数据割接的复杂度。

   （3）、开发组件选型

   通过使用Sharding-JDBC作为分表的开发组件，Sharding-JDBC是一款开源的轻量级数据中间件，提供了数据分片功能，业务只需要通过指定分片策略，由Sharding-JDBC实现数据分片插入、查询、组合等功能。Sharding-JDBC相对于Sharding-Proxy更轻量级，不需要独立部署，和业务代码融合在一起就可以了，但是同时也对业务代码有一定侵入性，但是分表的业务或者服务并不会特别多，没有必要单独做个服务支撑分表业务，故采用Sharding-JDBC。

   通过在任务工单服务中加入sharingJDBC的依赖，设置分片主键和相关的分片算法，进行分表设置。

• SQL解析：将SQL语句转化成语法树；

• SQL路由：根据配置的分片规则（主键+算法）路由到对应的库+表；

• SQL改写：改写成对应数据库的语法；

• SQL执行引擎：保持数据库连接池，然后到对应数据库+表中查询数据；

• 结果合并：将数据库中查询出来的数据进行合并。

  3、代码实现

  （1）、配置分片规则

  spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cityCode, taskType
  spring.shardingsphere.sharding.sharding-algorithms.custom_sharding_algorithm.props.algorithm-class-name=CustomTableShardingAlgorithm

  （2）、自定义CustomTableShardingAlgorithm分配算法

  public class CustomTableShardingAlgorithm implements PreciseShardingAlgorithm<String> {  
    
      @Override  
      public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {  
          // 获取地市编号和任务类型  
          String cityCode = shardingValue.getValue();  
          String taskType = // 这里需要根据实际情况获取任务类型，可能是从shardingValue中提取，也可能是其他逻辑  
    
          // 构建分表键  
          String shardingKey = cityCode + taskType;  
    
          // 计算目标表名  
          int targetIndex = (shardingKey.hashCode() & Integer.MAX_VALUE) % 8;  
    
          // 返回对应的表名，这里假设表名格式为 task_0 到 task_7  
          return "task_" + targetIndex;  
      }  
  }