Sharding-JDBC实现单数据库按照月份分片存储数据

pom.xml

只添加了重点引用的jar

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.8.0</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.0</version>
</dependency>

bootstrap.yml

指定分片策略类全路径 precise-algorithm-class-name range-algorithm-class-name

server:
  port: 8078

spring:
  application:
    name: test_app

  cloud:
    nacos:
      config:
        enable: true
        server-addr: localhost:8848
      discovery:
        enabled: true
        server-addr: localhost:8848


  shardingsphere:
    datasource:
      names: testdb # 最好不要有特殊字符
      testdb:
        type: org.apache.commons.dbcp2.BasicDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/testdb
        username: root
        password: root

    sharding:
      tables:
        t_user: # 表名
          table-strategy:
            standard:
              sharding-column: created_time # 分片字段
              precise-algorithm-class-name: com.test.common.algorithm.UserShardingAlgorithm
              range-algorithm-class-name: com.test.common.algorithm.UserShardingAlgorithm
          actual-data-nodes: testdb.t_user_$->{20..20}${(1..12).collect{t ->t.toString().padLeft(2,'0')}}

    props:
      sql:
        show: true

仍需解决的是，如果指定了user表的范围，需要整个范围都创建表，否则全量查询的时候，出现表不存在异常

创建分片规则类

RangeShardingAlgorithm 解决按范围查找 比如> >= < <= between.and …
PreciseShardingAlgorithm 解决精确查找 =
实现两个接口类，并返回对应参数应该查询的表名称即可

package com.smartdevice.common.algorithm;

import com.google.common.collect.Range;
import org.apache.commons.lang.time.DateUtils;
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.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

public class UserShardingAlgorithm implements RangeShardingAlgorithm<Date>, PreciseShardingAlgorithm<Date> {
	// 此处只做测试使用 与数据库表名对应 实际使用时可以写死开始时间 或者在yml中通过表名规则设置起始表名
    private static final Date START_TIME = DateUtils.addDays(new Date(), -10);

    private static final String TABLE_NAME = "t_user_%s";

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Date> rangeShardingValue) {
        SimpleDateFormat formatter = new SimpleDateFormat("yyMM");
        Range<Date> dates = rangeShardingValue.getValueRange();
        Date startTime = null, endTime = null;
        if (dates != null && dates.hasLowerBound() && dates.lowerEndpoint() != null) {
            startTime = dates.lowerEndpoint();
        } else {
            startTime = START_TIME;
        }
        if (dates != null && dates.hasUpperBound() && dates.upperEndpoint() != null) {
            endTime = dates.upperEndpoint();
        } else {
            endTime = new Date();
        }
        Set<String> tables = new HashSet<>();
        tables.add(String.format(TABLE_NAME, formatter.format(startTime)));
        while (true) {
            startTime = DateUtils.addMonths(startTime, 1);
            if (startTime.before(endTime)) {
                tables.add(String.format(TABLE_NAME, formatter.format(startTime)));
            } else {
                break;
            }
        }
        tables.add(String.format(TABLE_NAME, formatter.format(endTime)));
        return tables;
    }

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        SimpleDateFormat formatter = new SimpleDateFormat("yyMM");
        return String.format(TABLE_NAME, formatter.format(preciseShardingValue.getValue()));
    }
}

其他Service、Mapper、DTO、DAO可以和平常一起使用

一般情况下，按日期分片存储用来存储log日志，只插入和查询，效率比较高，查询时应该尽量指定起止时间，避免大批量查询、数据整合，提升效率。