sharding-jdbc 读写分离+水平分表

配置

sharding:
  jdbc:
    datasource:
      ds0:
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://ip:3307/pdms_paperless?user=${sharding.jdbc.datasource.ds0.username}&amp;password=${sharding.jdbc.datasource.ds0.password}&amp;useUnicode=true&amp;characterEncoding=UTF8&serverTimezone=UTC&useSSL=false
        username: root
      ds1:
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://ip:3308/pdms_paperless?user=${sharding.jdbc.datasource.ds1.username}&amp;password=${sharding.jdbc.datasource.ds0.password}&amp;useUnicode=true&amp;characterEncoding=utf8&characterSetResults=utf8&serverTimezone=UTC&useSSL=false
        username: root
      names: ds0,ds1
    config:
      props:
        sql.show: true
      sharding:
        tables:
          t_invoice_detail:
            #两月一张 20年08月开始
            actual-data-nodes: ds_0.t_invoice_detail_$->{202008..203007}
            table-strategy:
              standard:
                sharding-column: split_table_key
                precise-algorithm-class-name: com.gtmc.ssp.bff.paperless.config.InvoiceDetailPreciseShardingAlgorithmConfig
                range-algorithm-class-name: com.gtmc.ssp.bff.paperless.config.InvoiceDetailRangeModuloShardingTableAlgorithm
          t_invoice:
            #两年一张 2020年开始
            actual-data-nodes: ds_0.t_invoice_$->{2020..2030}
            table-strategy:
              standard:
                sharding-column: split_table_key
                precise-algorithm-class-name: com.gtmc.ssp.bff.paperless.config.InvoicePreciseShardingAlgorithmConfig
                range-algorithm-class-name: com.gtmc.ssp.bff.paperless.config.InvoiceRangeModuloShardingTableAlgorithm
          t_invoice_file:
            #一年一张 2020年开始
            actual-data-nodes: ds_0.t_invoice_file_$->{2020..2030}
            table-strategy:
              standard:
                sharding-column: split_table_key
                precise-algorithm-class-name: com.gtmc.ssp.bff.paperless.config.InvoiceFilePreciseShardingAlgorithmConfig
                range-algorithm-class-name: com.gtmc.ssp.bff.paperless.config.InvoiceFileRangeModuloShardingTableAlgorithm
          t_invoice_resume:
            #一年一张 2020年开始
            actual-data-nodes: ds_0.t_invoice_resume_$->{2020..2030}
            table-strategy:
              standard:
                sharding-column: split_table_key
                precise-algorithm-class-name: com.gtmc.ssp.bff.paperless.config.InvoiceResumePreciseShardingAlgorithmConfig
                range-algorithm-class-name: com.gtmc.ssp.bff.paperless.config.InvoiceResumeRangeModuloShardingTableAlgorithm
        master-slave-rules:
          ds_0:
            master-data-source-name: ds0
            slave-data-source-names: ds1
        binding-tables: t_invoice, t_invoice_detail,t_invoice_file,t_invoice_resume

分片规则

InvoiceDetailPreciseShardingAlgorithmConfig

package com.gtmc.ssp.bff.paperless.config;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.util.Collection;

/**
 * 两个月一张表，从0开始
 */
public class InvoiceDetailPreciseShardingAlgorithmConfig implements PreciseShardingAlgorithm<Integer> {

    public static Integer beginYear = 20;//年
    public static Integer beginMonth = 07;//月
    public static Integer beginYearMonth = 2007;//从20年7年开始
    public static String PreInvoiceDetailTableName = "t_invoice_detail";

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {


        int tableIndex = getTableIndex(shardingValue.getValue());

        if (availableTargetNames.toArray()[0].toString().startsWith(PreInvoiceDetailTableName)) {
            return PreInvoiceDetailTableName + "_" + tableIndex;
        }

        return PreInvoiceDetailTableName + tableIndex;
    }

    /**
     * 小等于 beginYearMonth 分到０表中，否则两个月分一张表中
     * @param splitTableKey
     * @return
     */
    public static int getTableIndex(Integer splitTableKey) {

        return splitTableKey%2==0?splitTableKey:splitTableKey+1;
    }

    public static void main(String[] args) {
//        System.out.println(getTableIndex(202007));
//        System.out.println(getTableIndex(202008));
//        System.out.println(getTableIndex(202009));
//        System.out.println(getTableIndex(202010));
//        System.out.println(getTableIndex(202011));
//        System.out.println(getTableIndex(202012));
//        System.out.println(getTableIndex(202109));
        System.out.println(getTableIndex(202111));
    }
}

InvoiceDetailRangeModuloShardingTableAlgorithm

package com.gtmc.ssp.bff.paperless.config;

import com.google.common.collect.Range;
import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;
import jnr.ffi.annotations.In;
import org.joda.time.DateTime;
import org.joda.time.Months;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.stereotype.Component;

import javax.swing.text.DateFormatter;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;

@Component
/*
范围查询时条件可以在这里实现写分区的逻辑
 */
public class InvoiceDetailRangeModuloShardingTableAlgorithm implements RangeShardingAlgorithm<Integer> {

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Integer> rangeShardingValue) {


        Range<Integer> valueRange = rangeShardingValue.getValueRange();
        return getRangeTableIndex(valueRange.lowerEndpoint(), valueRange.upperEndpoint());

    }

    public Collection<String> getRangeTableIndex(int from, int end) {
        DateTimeFormatter formatter = DateTimeFormat.forPattern("yyyyMM");
        HashSet<String> hashSet = new HashSet<>();
        DateTime startDate = formatter.parseDateTime(String.valueOf(from));
        DateTime endDate = formatter.parseDateTime(String.valueOf(end));

        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMM");

        while (endDate.isAfter(startDate) || endDate.equals(startDate)) {
            String time = dateFormat.format(startDate.toDate()); //可以把日期转换转指定格式的字符串

            if (Integer.parseInt(time) % 2 == 1) {
                hashSet.add(InvoiceDetailPreciseShardingAlgorithmConfig.PreInvoiceDetailTableName + "_" + (Integer.parseInt(time) + 1));
            }

            startDate = startDate.plusMonths(1);
        }

        return hashSet;
    }

    public static void main(String[] args) {

        InvoiceDetailRangeModuloShardingTableAlgorithm ta = new InvoiceDetailRangeModuloShardingTableAlgorithm();

        for (String rangeTableIndex : ta.getRangeTableIndex(202001, 202101)) {
            System.out.println(rangeTableIndex);
        }
    }
}

InvoiceFilePreciseShardingAlgorithmConfig

package com.gtmc.ssp.bff.paperless.config;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.util.Collection;

/**
 * 一年一张表
 */
public class InvoiceFilePreciseShardingAlgorithmConfig implements PreciseShardingAlgorithm<Integer> {

    public static Integer beginYear = 20;
    public static String PreTableName = "t_invoice_file";

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {


        int tableIndex = getTableIndex(shardingValue.getValue());

        if (availableTargetNames.toArray()[0].toString().startsWith(PreTableName)) {
            return PreTableName + "_" + tableIndex;
        }

        return PreTableName + tableIndex;
    }

    public static int getTableIndex(Integer targetYear) {
        int tableIndex = 0;
        tableIndex = targetYear/100;
        return tableIndex;
    }

    public static void main(String[] args) {
        System.out.println(getTableIndex(202006));
        System.out.println(getTableIndex(202108));



    }
}

InvoiceFileRangeModuloShardingTableAlgorithm

package com.gtmc.ssp.bff.paperless.config;

import com.google.common.collect.Range;
import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;
import org.springframework.stereotype.Component;

import java.util.Collection;
import java.util.HashSet;

@Component
/*
范围查询时条件可以在这里实现写分区的逻辑
 */
public class InvoiceFileRangeModuloShardingTableAlgorithm implements RangeShardingAlgorithm<Integer> {

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Integer> rangeShardingValue) {

        Range<Integer> valueRange = rangeShardingValue.getValueRange();
        return getRangeTableIndex(valueRange.lowerEndpoint(), valueRange.upperEndpoint());

    }

    public Collection<String> getRangeTableIndex(int from, int end) {

        HashSet<String> hashSet = new HashSet<>();
        int fromY = InvoiceFilePreciseShardingAlgorithmConfig.getTableIndex(from);
        int endY = InvoiceFilePreciseShardingAlgorithmConfig.getTableIndex(end);

        for (int i = fromY; i <= endY; i++) {
            hashSet.add(InvoiceFilePreciseShardingAlgorithmConfig.PreTableName + "_" + i);
        }


        return hashSet;
    }

    public static void main(String[] args) {
        InvoiceFileRangeModuloShardingTableAlgorithm ta=new InvoiceFileRangeModuloShardingTableAlgorithm();

        for (String rangeTableIndex : ta.getRangeTableIndex(202002, 202102)) {
            System.out.println(rangeTableIndex);
        }
    }
}

InvoicePreciseShardingAlgorithmConfig

package com.gtmc.ssp.bff.paperless.config;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.util.Collection;

/**
 * 两年一张表
 */
public class InvoicePreciseShardingAlgorithmConfig implements PreciseShardingAlgorithm<Integer> {

    public static Integer beginYear = 2020;
    public static String PreTableName = "t_invoice";

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {


        int tableIndex = getTableIndex(shardingValue.getValue());

        if (availableTargetNames.toArray()[0].toString().startsWith(PreTableName)) {
            return PreTableName + "_" + tableIndex;
        }

        return PreTableName + tableIndex;
    }

    /**
     * 只存偶数年，来控制两年一张表
     * @param targetYear
     * @return
     */
    public static int getTableIndex(Integer targetYear) {
        targetYear = targetYear / 100;
        return targetYear % 2 == 0 ? targetYear : targetYear - 1;
    }

    public static void main(String[] args) {
        System.out.println(getTableIndex(202006));
        System.out.println(getTableIndex(202108));
        System.out.println(getTableIndex(202210));
        System.out.println(getTableIndex(202312));

    }
}

InvoiceRangeModuloShardingTableAlgorithm

package com.gtmc.ssp.bff.paperless.config;

import com.google.common.collect.Range;
import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;
import io.shardingsphere.core.metadata.datasource.dialect.PostgreSQLDataSourceMetaData;
import org.springframework.stereotype.Component;

import java.util.Collection;
import java.util.HashSet;

@Component
/*
范围查询时条件可以在这里实现写分区的逻辑
 */
public class InvoiceRangeModuloShardingTableAlgorithm implements RangeShardingAlgorithm<Integer> {

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Integer> rangeShardingValue) {

        Range<Integer> valueRange = rangeShardingValue.getValueRange();
        return getRangeTableIndex(valueRange.lowerEndpoint(), valueRange.upperEndpoint());

    }

    public Collection<String> getRangeTableIndex(int from, int end) {

        HashSet<String> hashSet = new HashSet<>();
        from = InvoicePreciseShardingAlgorithmConfig.getTableIndex(from);
        end = InvoicePreciseShardingAlgorithmConfig.getTableIndex(end);
        while (from<=end){
            hashSet.add(InvoicePreciseShardingAlgorithmConfig.PreTableName + "_" + from);
            from+=2;
        }



        return hashSet;
    }

    public static void main(String[] args) {
        InvoiceRangeModuloShardingTableAlgorithm ta=new InvoiceRangeModuloShardingTableAlgorithm();

        for (String rangeTableIndex : ta.getRangeTableIndex(202102, 203102)) {
            System.out.println(rangeTableIndex);
        }
    }

}

InvoiceResumePreciseShardingAlgorithmConfig

package com.gtmc.ssp.bff.paperless.config;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.util.Collection;

/**
 * 一年一张表
 */
public class InvoiceResumePreciseShardingAlgorithmConfig implements PreciseShardingAlgorithm<Integer> {

    public static Integer beginYear = 20;
    public static String PreTableName = "t_invoice_resume";

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {


        int tableIndex = getTableIndex(shardingValue.getValue());

        if (availableTargetNames.toArray()[0].toString().startsWith(PreTableName)) {
            return PreTableName + "_" + tableIndex;
        }

        return PreTableName + tableIndex;
    }

    public static int getTableIndex(Integer targetYear) {
        return targetYear/100;
    }

    public static void main(String[] args) {
        System.out.println(getTableIndex(202006));
        System.out.println(getTableIndex(202106));
    }
}

InvoiceResumeRangeModuloShardingTableAlgorithm

package com.gtmc.ssp.bff.paperless.config;

import com.google.common.collect.Range;
import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;
import org.apache.commons.collections.set.ListOrderedSet;
import org.springframework.stereotype.Component;

import java.util.Collection;
import java.util.HashSet;

@Component
/*
范围查询时条件可以在这里实现写分区的逻辑
 */
public class InvoiceResumeRangeModuloShardingTableAlgorithm implements RangeShardingAlgorithm<Integer> {

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Integer> rangeShardingValue) {

        Range<Integer> valueRange = rangeShardingValue.getValueRange();
        return getRangeTableIndex(valueRange.lowerEndpoint(), valueRange.upperEndpoint());

    }

    public Collection<String> getRangeTableIndex(int from, int end) {

        HashSet<String> hashSet = new HashSet<>();

        int fromY = InvoiceResumePreciseShardingAlgorithmConfig.getTableIndex(from);
        int endY = InvoiceResumePreciseShardingAlgorithmConfig.getTableIndex(end);
        for (int i = fromY; i <= endY; i++) {

            hashSet.add(InvoiceResumePreciseShardingAlgorithmConfig.PreTableName + "_" + i);

        }

        return hashSet;
    }

    public static void main(String[] args) {
        InvoiceResumeRangeModuloShardingTableAlgorithm ta=new InvoiceResumeRangeModuloShardingTableAlgorithm();

        for (String rangeTableIndex : ta.getRangeTableIndex(202007, 202009)) {
            System.out.println(rangeTableIndex);
        }


    }
}

posted on 2020-07-02 13:08 wolf12 阅读(323) 评论(0) 编辑收藏举报