使用shardingJDBC对mysql按年月份水平分表

因为我有一个表每天要接收大约五十万条数据而且主要和时间有关系，所以考虑在三就使用了shardingJDBC进行了按年月份进行水平分表。我这里只进行了分表没有进行分库操作。

所需依赖：

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- Druid连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>

        <!-- Mysql驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!-- MybatisPlus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>

        <!-- Sharding-JDBC -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>

    </dependencies>

配置文件application.properties

# 配置存放到内存中
spring.shardingsphere.mode.type=Memory
# 打印sql日志
spring.shardingsphere.props.sql.show=true
# 配置数据源
spring.shardingsphere.datasource.names=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds.url=jdbc:mysql://localhost:3306/user_db?characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123456

# 数据节点，按年月日进行水平分表
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds.user_$->{2022..2100}0$->{1..9},ds.user_$->{2022..2100}1$->{0..2}
# 配置分片字段，这个字段例如202201
spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=time
# 配置精确、范围查询分片算法，com.example.demo.config.TimeAlgorithm这个类路径是你自定义的分片规则的类路径千万别和我一样因为你的路径和我的应该是不一样的，看你自己放在哪里了
spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.example.demo.config.TimeAlgorithm
spring.shardingsphere.sharding.tables.user.table-strategy.standard.range-algorithm-class-name=com.example.demo.config.TimeAlgorithm
# 配置主键以及生成算法
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
#防止bean重复报错
spring.main.allow-bean-definition-overriding=true

自定义的分片规则

package com.example.demo.config;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;

/**
 * 分片算法，按月分片
 */
public class TimeAlgorithm implements PreciseShardingAlgorithm<String>, RangeShardingAlgorithm<String> {

    /**
     * 需要空构造方法
     */
    public TimeAlgorithm() {
    }

    /**
     * 时间格式
     */
    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");

    /**
     * 精确分片
     *
     * @param collection
     * @param preciseShardingValue
     * @return
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        return buildShardingTable(preciseShardingValue.getLogicTableName(), preciseShardingValue.getValue());
    }

    /**
     * 构建分片后的表名
     *
     * @param logicTableName
     * @param date
     * @return
     */
    private String buildShardingTable(String logicTableName, String date) {
        StringBuffer stringBuffer = new StringBuffer(logicTableName).append("_").append(date, 0, 6);
        return stringBuffer.toString();
    }

    /**
     * 范围分片
     *
     * @param collection
     * @param rangeShardingValue
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        Range<String> valueRange = rangeShardingValue.getValueRange();
        String lower = valueRange.lowerEndpoint();
        String upper = valueRange.upperEndpoint();

        LocalDate start = LocalDate.parse(lower, DATE_TIME_FORMATTER);
        LocalDate end = LocalDate.parse(upper, DATE_TIME_FORMATTER);

        Collection<String> tables = new ArrayList<>();
        while (start.compareTo(end) <= 0) {
            tables.add(buildShardingTable(rangeShardingValue.getLogicTableName(), start.format(DATE_TIME_FORMATTER)));
            start = start.plusMonths(1L);
        }

        // collection配置的数据节点表，这里是排除不存在配置中的表
        collection.retainAll(tables);
        return collection;
    }

}

user实体类：

package com.example.demo.entity;
import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    private String name;
    private String time;
    private Date createtime;
}

mapper层

package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface UserMapper extends BaseMapper<User> {
    @Select("select * from user ")
    public List<User>getAll();
}

编写controller类

@PostMapping("/insert")
 public Object insert() {
     User user=new User();
     user.setCreatetime(new Date());
     user.setTime(new SimpleDateFormat("yyyyMMdd").format(new Date()));
     return userMapper.insert(user);
 }

表结构：

插入成功：