shardingsphere（5.0.0） 实现分表，采用分段发号器，适用机器量不太多的情况

maven依赖

<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.0.0</version>
        </dependency>

yml

server:
  port: 8888
spring:
#  datasource:
#    driver-class-name: com.mysql.cj.jdbc.Driver
#    url: jdbc:mysql://localhost:3306/learn?zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useTimezone=true&serverTimezone=GMT%2B8
#    username: root
#    password: 123456
  shardingsphere:
    datasource:
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/learn?zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useTimezone=true&serverTimezone=GMT%2B8
        username: root
        password: 123456
      names: ds0

    rules:
      sharding:
        default-table-strategy:
          none:
        sharding-algorithms:
          democeshi:
            type: INLINE
            props:
              sql-show: true
              algorithm-expression: emp_$->{empno%4}
              allow-range-query-with-inline-sharding: false
        tables:
          emp:
            actual-data-nodes: ds0.emp_$->{0..3}
            table-strategy:
              standard:
                sharding-algorithm-name: democeshi
                sharding-column: empno

logging:
  level:
   com.epean.zfh.mapper: debug

建表语句

CREATE TABLE `emp_0` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `empname` varchar(20) NOT NULL DEFAULT '',
  `job` varchar(9) NOT NULL DEFAULT '',
  `mgr` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `hiredate` datetime NOT NULL,
  `sal` decimal(7,2) NOT NULL,
  `comn` decimal(7,2) NOT NULL,
  `depno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

剩下建表省略

发号器

package com.epean.zfh.idgen;

import com.epean.zfh.entity.Sequence;
import com.epean.zfh.utils.SpringContextUtils;
import com.mchange.v2.util.CollectionUtils;
import lombok.extern.slf4j.Slf4j;
import tk.mybatis.mapper.genid.GenId;
import tk.mybatis.mapper.util.Assert;

import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.ConcurrentHashMap;

/**
 *  * 分布式id生成器，号段模式
 *  * 1、同jvm不同bizTag ConcurrentHashMap处理缓存并发
 *  * 2、同jvm 相同bizTag synchronized (bizTag.intern())处理缓存并发
 *  * 3、不同jvm 相同bizTag 缓存无并发，行锁处理数据库并发
 *  实现步骤：
 *  1.实现genid接口目的是为了后续直接使用keysql
 *  2.实现id的方案，可以通过uuid ，或者通过号段模式
 * @author zfh
 * @version 1.0
 * @date 2022/7/22 11:06
 */
@Slf4j
public class SegmentId implements GenId<Integer> {
    private volatile static SegmentIDHelper segmentIDHelper;

    //对于存在多个表要分表的情况进行分别处理
    private Map<String, List<Integer>> idMapCache =new ConcurrentHashMap<>();
    @Override //两个属性是表名和列名
    public Integer genId(String table, String column) {
        return genIdFromCache(table);
    }
    private Integer genIdFromCache(String table){
        Integer id;
        //stirng.intern() 判断常量池中是否存在存在直接引用不存在创建
        synchronized (table.intern()){
             if (Objects.isNull(idMapCache.get(table)))  {
                 this.initSegment(table);
             }
             id= idMapCache.get(table).get(0);
             idMapCache.get(table).remove(0);
        }
        Assert.notNull(id,"生成id失败");
        return id;
    }

    /**
     * 加载号段到缓存
     * @param table
     */
    private void initSegment(String table){
        Sequence sequence = getSequenceMapper().genIdSegment(table);
        Integer startId = sequence.getNextId()-sequence.getStep();
        log.info("初始化id：{}，{}-{}",table, startId,sequence.getNextId()-1);
        if(idMapCache.get(table) ==null){
            idMapCache.put(table, new LinkedList<>());
        }
        while (startId < sequence.getNextId()){
            idMapCache.get(table).add(startId++);
        }
    }

    /**
     * 获取sequenceGenIdHelper的单例，与controller返回json一致
     * @return
     */
    private static SegmentIDHelper getSequenceMapper(){
        if(segmentIDHelper == null){
            synchronized (SegmentId.class){
                if(segmentIDHelper == null){
                    segmentIDHelper = SpringContextUtils.getBean(SegmentIDHelper.class);
                }
            }
        }
        return segmentIDHelper;
    }

}

查询号段

package com.epean.zfh.idgen;

import com.epean.zfh.entity.Sequence;
import com.epean.zfh.mapper.SequenceMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import javax.annotation.Resource;

/**
 * @author zfh
 * @version 1.0
 * @date 2022/7/22 11:59
 */
@Component
@Slf4j
public class SegmentIDHelper {
    @Resource
    private SequenceMapper sequenceMapper;
    /**
     * 从数据库加载一个step的号段
     * 注意方法事务独立提交
     * @param table
     */
    @Transactional(rollbackFor = Exception.class,propagation = Propagation.REQUIRES_NEW)
    public Sequence genIdSegment(String table){
        sequenceMapper.initNextId(table);
        Sequence sysSequence = sequenceMapper.selectByPrimaryKey(table);
        Assert.notNull(sysSequence, "号段不存在："+table);
        return sysSequence;

    }
}

#获取bean

package com.epean.zfh.utils;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * @author zfh
 * @version 1.0
 * @date 2022/7/22 14:00
 */
@Component
public class SpringContextUtils implements ApplicationContextAware {

    private static ApplicationContext applicationContext = null;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        if(SpringContextUtils.applicationContext == null){
            SpringContextUtils.applicationContext  = applicationContext;
        }
    }
    public static void init(ApplicationContext applicationContext){
        SpringContextUtils.applicationContext = applicationContext;
    }

    /**
     * 取得存储在静态变量中的ApplicationContext.
     */
    public static ApplicationContext getApplicationContext() {
        checkApplicationContext();
        return applicationContext;
    }

    /**
     * 从静态变量ApplicationContext中取得Bean, 自动转型为所赋值对象的类型.
     */
    @SuppressWarnings("unchecked")
    public static <T> T getBean(String name) {
        checkApplicationContext();
        return (T) applicationContext.getBean(name);
    }

    /**
     * 从静态变量ApplicationContext中取得Bean, 自动转型为所赋值对象的类型.
     * 如果有多个Bean符合Class, 取出第一个.
     */
    public static <T> T getBean(Class<T> requiredType) {
        checkApplicationContext();
        return applicationContext.getBean(requiredType);
    }

    /**
     * 清除applicationContext静态变量.
     */
    public static void cleanApplicationContext() {
        applicationContext = null;
    }

    private static void checkApplicationContext() {
        if (applicationContext == null) {
            throw new IllegalStateException("applicaitonContext is not init!");
        }
    }
}

发号器建表

CREATE TABLE `sequence` (
  `table_name` varchar(50) NOT NULL DEFAULT '' COMMENT '表名',
  `next_id` int(20) NOT NULL DEFAULT '1' COMMENT '表的主键id',
  `step` int(10) NOT NULL DEFAULT '1000' COMMENT '步长',
  `remark` varchar(255) DEFAULT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='自增id顺序表，用于分表场景获取自增ID';

测试controller

package com.epean.zfh.controller;

import com.epean.zfh.entity.Emp;
import com.epean.zfh.mapper.EmpMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.util.Date;

/**
 * @author zfh
 * @version 1.0
 * @date 2022/6/29 9:16
 */
@RestController
@RequestMapping("First")
@Api("测试")
public class FirstController {
    @Autowired
    private EmpMapper empMapper;

    @ApiOperation(value = "测试")
    @GetMapping("cat")
    public String getHello(){
        return "Hello";
    }

    @Autowired
    private DataSource dataSource;

    @GetMapping("user")
    public void save(){
        for (int i = 0; i < 1000; i++) {
            Emp emp = new Emp();
            emp.setEmpno(i%4);
            emp.setEmpname(" s");
            emp.setJob("sss");
            emp.setMgr(1);
            emp.setHiredate(new Date());
            emp.setSal(new BigDecimal(11.00));
            emp.setComn(new BigDecimal(22));
            emp.setDepno(1);
            empMapper.insert(emp);
        }

    }


}

实体表主要是发号器注解

package com.epean.zfh.entity;





import com.epean.zfh.idgen.SegmentId;
import tk.mybatis.mapper.annotation.KeySql;

import javax.persistence.Id;
import javax.persistence.Table;
import java.math.BigDecimal;
import java.util.Date;

@Table(name = "emp")
public class Emp {
    @Id
    @KeySql(genId = SegmentId.class)
    private Integer id;

    private Integer empno;

    private String empname;

    private String job;

    private Integer mgr;

    private Date hiredate;

    private BigDecimal sal;

    private BigDecimal comn;

    private Integer depno;

    /**
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * @return empno
     */
    public Integer getEmpno() {
        return empno;
    }

    /**
     * @param empno
     */
    public void setEmpno(Integer empno) {
        this.empno = empno;
    }

    /**
     * @return empname
     */
    public String getEmpname() {
        return empname;
    }

    /**
     * @param empname
     */
    public void setEmpname(String empname) {
        this.empname = empname == null ? null : empname.trim();
    }

    /**
     * @return job
     */
    public String getJob() {
        return job;
    }

    /**
     * @param job
     */
    public void setJob(String job) {
        this.job = job == null ? null : job.trim();
    }

    /**
     * @return mgr
     */
    public Integer getMgr() {
        return mgr;
    }

    /**
     * @param mgr
     */
    public void setMgr(Integer mgr) {
        this.mgr = mgr;
    }

    /**
     * @return hiredate
     */
    public Date getHiredate() {
        return hiredate;
    }

    /**
     * @param hiredate
     */
    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    /**
     * @return sal
     */
    public BigDecimal getSal() {
        return sal;
    }

    /**
     * @param sal
     */
    public void setSal(BigDecimal sal) {
        this.sal = sal;
    }

    /**
     * @return comn
     */
    public BigDecimal getComn() {
        return comn;
    }

    /**
     * @param comn
     */
    public void setComn(BigDecimal comn) {
        this.comn = comn;
    }

    /**
     * @return depno
     */
    public Integer getDepno() {
        return depno;
    }

    /**
     * @param depno
     */
    public void setDepno(Integer depno) {
        this.depno = depno;
    }
}

mapper 部分省略