sharding-jdbc 按月份分表

作者: simians | 来源:发表于2019-03-10 22:32 被阅读0次

sharding-jdbc 按月份分表
SpringBoot使用Sharding-JDBC分库分表
分库分表开源中间件之Sharding-JDBC使用体验
sharding-jdbc与Sharding-Proxy
别说我不告诉你！如何用分库分表的 9种分布式主键ID 生成方案？
常见分库分表
sharding-jdbc配置之后,提示table does n
sharding-jdbc 分库分表报java.lang.Boo
Sharding-JDBC源码初探
sharding-jdbc

更新：按月分表查询更新

更新内容：添加范围搜索比如201901~201605五个月的数据，之前是只能查询单月的数据

        <!--sharding-jdbc -->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>
        <!--sharding-jdbc结束-->

sharding-jdbc 按月份分表需要自己实现。需要实现两个接口PreciseShardingAlgorithm，RangeShardingAlgorithm。并在配置文件里添加实现路径
如下：com.simianBook.conf.TimeShardingTableAlgorithm

TimeShardingTableAlgorithm路径

那么yml 里的配置路径如下

sharding:
  jdbc:
    datasource:
      names: user-0,user-1
      user-0:   #springboot  在yml 配置里key不支持 '_'  推荐使用'-'
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/t_user_0?characterEncoding=UTF-8&serverTimezone=GMT
        username: root
        password: hou1147646079
      user-1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/t_user_1?characterEncoding=UTF-8&serverTimezone=GMT
        username: root
        password: hou1147646079
    config:
      sharding: #分片
        default-database-strategy:#数据库默认分库
          inline:
            sharding-column: user_id
            algorithm-expression: user ->${user_id % 2}
        props:
          sql.show: true
        tables:
          user:
            key-generator-column-name: user_id #user 表的主键
            database-strategy:
              inline:
                shardingColumn: user_id  #数据库分片测略
                algorithm-expression: user-${user_id % 2} #
 #           actual-data-nodes: user-${0..1}.user_${0..1} #设置的datasource 的名字 如user-0,user-1,user_${0..1} 数据库中的
            table-strategy:
              standard: # 单列sharidng算法,需要配合对应的preciseShardingAlgorithm,rangeShardingAlgorithm接口的实现使用,目前无生产可用实现
                shardingColumn: user_id # 列名,允许单列
                precise-algorithm-class-name: com.simianBook.config.TimeShardingTableAlgorithm # preciseShardingAlgorithm接口的实现类
            #    rangeShardingAlgorithm: # rangeShardingAlgorithm接口的实现类
#              inline:
#                sharding-column: user_id
#                algorithm-expression: user_${user_id % 2}
    defaultTableStrategy:
      none:
    defaultKeyGenerator:
      type: SNOWFLAKE

按月分表查询更新

因为SimpleDateFormat 不是线程安全的需要修改为DateTimeFormatter

import com.google.common.collect.Range;
import com.simianBook.tool.GenericTool;
import com.simianBook.tool.ParaseShardingKeyTool;
import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;
import io.shardingsphere.core.keygen.DefaultKeyGenerator;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.LinkedHashSet;
import java.util.stream.Stream;

/**
 * 搜查多表
 * 范围搜索时（跨表）应传递时间戳并左移22位
 */
public class TimeRangeShardingAlgorithm implements RangeShardingAlgorithm<Long> {
    private DateTimeFormatter dateformat = DateTimeFormatter.ofPattern("yyyyMM");
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<String>();
        Range<Long> shardingKey = shardingValue.getValueRange();
        long startShardingKey = shardingKey.lowerEndpoint();
        long endShardingKey = shardingKey.upperEndpoint();
        //获取到开始时间戳
        String startTimeString = ParaseShardingKeyTool.getYearAndMonth(startShardingKey);
        //获取结束时间戳
        String endTimeString = ParaseShardingKeyTool.getYearAndMonth(endShardingKey);
        Calendar cal = Calendar.getInstance();
        //获取开始的年月
        //时间戳
        LocalDateTime startLocalDate = GenericTool.getLocalDate(startTimeString);
        //获取结束的年月
        LocalDateTime endLocalDate = GenericTool.getLocalDate(endTimeString);
        //进行判断 获取跨月份的表 如201901,201902,201903 三个月的表
        //目前只支持同一年内的查询，跨年不支持
        int end = Integer.valueOf(dateformat.format(endLocalDate));
        int start = Integer.valueOf(dateformat.format(startLocalDate));
        while(start <= end){
            StringBuffer tableName = new StringBuffer();
            tableName.append(shardingValue.getLogicTableName())
                    .append("_").append(start);
            result.add(tableName.toString());
            start++；
        }
        return result;
    }
}

使用到的方法

import io.shardingsphere.core.keygen.DefaultKeyGenerator;

import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;

public class ParaseShardingKeyTool {
    private static  DateTimeFormatter yearAndMonth =  DateTimeFormatter.ofPattern("yyyyMM");
    private static  DateTimeFormatter year = DateTimeFormatter.ofPattern("yyyy");
    public static String getYearAndMonth(long shardingKey){
        Instant instant =  Instant.ofEpochMilli(DefaultKeyGenerator.EPOCH+(Long.valueOf(shardingKey+"")>>22));
        LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
        return  yearAndMonth.format(localDateTime);
    }
    public static String getYear(long shardingKey){
        Instant instant =  Instant.ofEpochMilli(DefaultKeyGenerator.EPOCH+(Long.valueOf(shardingKey+"")>>22));
        LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
        return  year.format(localDateTime);
    }

    public static void main(String[] args) {
        DefaultKeyGenerator defaultKeyGenerator = new DefaultKeyGenerator();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMM");
        System.out.println(simpleDateFormat.format(System.currentTimeMillis()));
        System.out.println(ParaseShardingKeyTool.getYearAndMonth(Long.valueOf(defaultKeyGenerator.generateKey()+"")));
        System.out.println(ParaseShardingKeyTool.getYearAndMonth(Long.valueOf(defaultKeyGenerator.generateKey()+"")));
    }
}

下面需要来编写按单月分表的方法

package com.simianBook.config;


import com.simianBook.tool.ParaseShardingKeyTool;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
import java.util.Collection;


public class TimeShardingTableAlgorithm implements PreciseShardingAlgorithm<Long> {
    private DateTimeFormatter dateformat = DateTimeFormatter.ofPattern("yyyyMM");
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        StringBuffer tableName = new StringBuffer();
        tableName.append(shardingValue.getLogicTableName())
                 .append("_").append(ParaseShardingKeyTool.getYearAndMonth(shardingValue.getValue()));

        return tableName.toString();
    }
}

我是使用sharding-jdbc 自带的雪花算法来生成主键的，雪花算法的实现逻辑
因此我在得到分片键时对分片键进行逆推可以推出分片键的时间戳。再根据时间戳得到创建此条数据创建的年月进而定位到那个表位置（或者说表名）
DefaultKeyGenerator.EPOCH+(Long.valueOf(shardingKey+"")>>22)
DefaultKeyGenerator.EPOCH 表示起始时间。在雪花算法当中生成的时间戳需要减去起始时间在进行左移22位在进行或运算
sharding-jdbc 的雪花实现方法如下版本3.0 该版本有bug 并发量低的时候生成的分片键始终为偶数

    public synchronized Number generateKey() {
        long currentMillis = timeService.getCurrentMillis();
        Preconditions.checkState(this.lastTime <= currentMillis, "Clock is moving backwards, last time is %d milliseconds, current time is %d milliseconds", new Object[]{this.lastTime, currentMillis});
        if (this.lastTime == currentMillis) { //最新时间与当前时间相同（发生并发）
            // //sequence+1  如果suquence&4095L==0时条件成立时 
            if (0L == (this.sequence = this.sequence + 1L & 4095L)) {
                currentMillis = this.waitUntilNextTime(currentMillis);//获取最新时间
            }
        } else {
            this.sequence = 0L;  //此处有bug 并发量低的时候this.sequence始终为0L的
        }

        this.lastTime = currentMillis;
        return currentMillis - EPOCH << 22 | workerId << 12 | this.sequence;
    }

3.1.0 版本解决了自己可以看一下有什么不同

    public synchronized Number generateKey() {
        long currentMilliseconds = timeService.getCurrentMillis();
        if (this.waitTolerateTimeDifferenceIfNeed(currentMilliseconds)) {
            currentMilliseconds = timeService.getCurrentMillis();
        }

        if (this.lastMilliseconds == currentMilliseconds) {
            if (0L == (this.sequence = this.sequence + 1L & 4095L)) {
                currentMilliseconds = this.waitUntilNextTime(currentMilliseconds);
            }
        } else {
            this.vibrateSequenceOffset();
            this.sequence = (long)this.sequenceOffset;
        }

        this.lastMilliseconds = currentMilliseconds;
        return currentMilliseconds - EPOCH << 22 | workerId << 12 | this.sequence;
    }

工具类

package com.simianBook.tool;

import io.shardingsphere.core.keygen.DefaultKeyGenerator;
import org.apache.commons.lang.StringUtils;

import java.lang.management.ManagementFactory;
import java.math.BigDecimal;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.regex.Pattern;

/**
 * @program: simianBook1
 * @description: 通用工具类
 * @author: houzi
 * @create: 2019-03-14 10:54
 */
public class GenericTool {
    private static final String EMAIL_FORMAT =" /^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$/";
    public static long getWorkId(){
        String name = ManagementFactory.getRuntimeMXBean().getName();
        long workId =Long.valueOf(name.split("@")[0]);
        return  workId;
    }

    /**
     *
     * @return
     */
    public static long getSahrdingKey(){
        long workId = GenericTool.getWorkId();
        DefaultKeyGenerator.setWorkerId(workId);
        DefaultKeyGenerator defaultKeyGenerator = new DefaultKeyGenerator();
        GenericTool.getWorkId();

        return Long.valueOf(defaultKeyGenerator.generateKey()+"");
    }

    /**
     * 通过时间戳获取时间
     * @param timestamp
     * @return
     */
    public static LocalDateTime getLocalDate(String timestamp){
        Instant startInstant = Instant.ofEpochMilli(Long.valueOf(timestamp));
        LocalDateTime localDate = LocalDateTime.ofInstant(startInstant, ZoneId.systemDefault());
        return localDate;
    }

    public static boolean isEmail(String email){
        if(StringUtils.isBlank(email)){
            return false;
        }
        return Pattern.matches(EMAIL_FORMAT,email);
    }
    
    

}

表的命名

user_201907

image.png

sharding-jdbc 按月份分表
更新：按月分表查询更新更新内容：添加范围搜索比如201901~201605五个月的数据，之前是只能查询单月的数据...
SpringBoot使用Sharding-JDBC分库分表
本文介绍SpringBoot使用当当Sharding-JDBC进行分库分表。 1.有关Sharding-JDBC ...
分库分表开源中间件之Sharding-JDBC使用体验
分库分表开源中间件之Sharding-JDBC使用体验数据库分片思想垂直切分垂直切分就是把表按模块划分到不同...
sharding-jdbc与Sharding-Proxy
Sharding-JDBC Sharding-JDBC 采用在 JDBC 层扩展分库分表，支持读写分离，是一个以 ...
别说我不告诉你！如何用分库分表的 9种分布式主键ID 生成方案？
前言《sharding-jdbc 分库分表的 4种分片策略》中我们介绍了 sharding-jdbc 4种分片策...
常见分库分表
SpringBoot使用Sharding-JDBC分库分表shardingjdbc原理分析 sql引擎 1.sql...
sharding-jdbc配置之后,提示table does n
背景: 最近使用springboot2.x + sharding-jdbc + mybatis 测试分库分表方案,...
sharding-jdbc 分库分表报java.lang.Boo
项目简介最近公司mysql有个表数据量3000多W决定分表，用sharding-jdbc插件做分表，根据字段ta...
Sharding-JDBC源码初探
Sharding-JDBC是当当开源的分库分表中间件，通过重写jdbc api的方式为Java应用提供分库分表的能...
sharding-jdbc
1 sharding-jdbc 1 分库分表为什么要分库表呢？高并发。单库单表没法承受单并发。数据量大。磁盘...