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

2022-09-01 22:00:26

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

配置

sharding:
  jdbc:
    datasource:
      ds0:
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://172.16.1.173:3307/pdms_paperless?user=${sharding.jdbc.datasource.ds0.username}&password=${sharding.jdbc.datasource.ds0.password}&useUnicode=true&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://172.16.1.173:3308/pdms_paperless?user=${sharding.jdbc.datasource.ds1.username}&password=${sharding.jdbc.datasource.ds0.password}&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&serverTimezone=UTC&useSSL=false
        username: root
      names: ds0,ds1
    config:
      props:
        sql.show: true
      sharding:
        tables:
          t_invoice_detail:
            actual-data-nodes: ds_0.t_invoice_detail_$->{0..5}
            table-strategy:
              standard:
                sharding-column: split_table_key
                precise-algorithm-class-name: com.gtmc.ssp.bff.admin.config.InvoiceDetailPreciseShardingAlgorithmConfig
                range-algorithm-class-name: com.gtmc.ssp.bff.admin.config.InvoiceDetailRangeModuloShardingTableAlgorithm
          t_invoice:
            actual-data-nodes: ds_0.t_invoice_$->{0..5}
            table-strategy:
              standard:
                sharding-column: split_table_key
                precise-algorithm-class-name: com.gtmc.ssp.bff.admin.config.InvoicePreciseShardingAlgorithmConfig
                range-algorithm-class-name: com.gtmc.ssp.bff.admin.config.InvoiceRangeModuloShardingTableAlgorithm
          t_invoice_file:
            actual-data-nodes: ds_0.t_invoice_file_$->{0..5}
            table-strategy:
              standard:
                sharding-column: split_table_key
                precise-algorithm-class-name: com.gtmc.ssp.bff.admin.config.InvoiceFilePreciseShardingAlgorithmConfig
                range-algorithm-class-name: com.gtmc.ssp.bff.admin.config.InvoiceFileRangeModuloShardingTableAlgorithm
          t_invoice_resume:
            actual-data-nodes: ds_0.t_invoice_resume_$->{0..5}
            table-strategy:
              standard:
                sharding-column: split_table_key
                precise-algorithm-class-name: com.gtmc.ssp.bff.admin.config.InvoiceResumePreciseShardingAlgorithmConfig
                range-algorithm-class-name: com.gtmc.ssp.bff.admin.config.InvoiceResumeRangeModuloShardingTableAlgorithm

        master-slave-rules:
          ds_0:
            master-data-source-name: ds0
            slave-data-source-names: ds1

分片规则

InvoiceDetailPreciseShardingAlgorithmConfig

package com.gtmc.ssp.bff.admin.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) {
        int tableIndex = 0;

        Integer targetYear = splitTableKey / 100;
        Integer targetMonth = splitTableKey - targetYear * 100;

        if (splitTableKey > beginYearMonth) {
            tableIndex = ((targetYear - beginYear) * 12 + targetMonth - beginMonth) / 2;
        }

        return tableIndex;
    }
}

InvoiceDetailRangeModuloShardingTableAlgorithm

package com.gtmc.ssp.bff.admin.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 InvoiceDetailRangeModuloShardingTableAlgorithm implements RangeShardingAlgorithm<Integer> {

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


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

    }

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

        HashSet<String> hashSet=new HashSet<>();
        for (int i=from;i<=end;i++){

            final int tableIndex = InvoiceDetailPreciseShardingAlgorithmConfig.getTableIndex(i);
            hashSet.add(InvoiceDetailPreciseShardingAlgorithmConfig.PreInvoiceDetailTableName+"_"+tableIndex);

        }


        return hashSet;
    }
}

InvoiceFilePreciseShardingAlgorithmConfig

package com.gtmc.ssp.bff.admin.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()/100);

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

        return PreTableName + tableIndex;
    }

    public static int getTableIndex(Integer targetYear) {
        int tableIndex = 0;

        if (targetYear > beginYear) {
            tableIndex = targetYear - beginYear;
        }

        return tableIndex;
    }

    public static void main(String[] args) {
        System.out.println(getTableIndex(2006));
        System.out.println(getTableIndex(2007));
        System.out.println(getTableIndex(2108));
        System.out.println(getTableIndex(2109));
        System.out.println(getTableIndex(2210));
        System.out.println(getTableIndex(2211));
        System.out.println(getTableIndex(2312));
        System.out.println(getTableIndex(2301));

    }
}

InvoiceFileRangeModuloShardingTableAlgorithm

package com.gtmc.ssp.bff.admin.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.ArrayList;
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 = from / 100;
        int endY = end / 100;
        for (int i = fromY; i <= endY; i++) {
            final int tableIndex = InvoiceFilePreciseShardingAlgorithmConfig.getTableIndex(i);
            hashSet.add(InvoiceFilePreciseShardingAlgorithmConfig.PreTableName + "_" + tableIndex);

        }


        return hashSet;
    }
}

InvoicePreciseShardingAlgorithmConfig

package com.gtmc.ssp.bff.admin.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 = 20;
    public static String PreTableName = "t_invoice";

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


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

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

        return PreTableName + tableIndex;
    }

    public static int getTableIndex(Integer targetYear) {
        int tableIndex = 0;

        if (targetYear > beginYear) {
            tableIndex = (targetYear - beginYear) / 2;
        }

        return tableIndex;
    }

    public static void main(String[] args) {
        System.out.println(getTableIndex(2006));
        System.out.println(getTableIndex(2007));
        System.out.println(getTableIndex(2108));
        System.out.println(getTableIndex(2109));
        System.out.println(getTableIndex(2210));
        System.out.println(getTableIndex(2211));
        System.out.println(getTableIndex(2312));
        System.out.println(getTableIndex(2301));

    }
}

InvoiceRangeModuloShardingTableAlgorithm

package com.gtmc.ssp.bff.admin.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.ArrayList;
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 = from / 100;
        end = end / 100;
        for (int i = from; i <= end; i++) {

            final int tableIndex = InvoicePreciseShardingAlgorithmConfig.getTableIndex(i);
            hashSet.add(InvoicePreciseShardingAlgorithmConfig.PreTableName + "_" + tableIndex);

        }


        return hashSet;
    }
}

InvoiceResumePreciseShardingAlgorithmConfig

package com.gtmc.ssp.bff.admin.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()/100);

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

        return PreTableName + tableIndex;
    }

    public static int getTableIndex(Integer targetYear) {
        int tableIndex = 0;

        if (targetYear > beginYear) {
            tableIndex = targetYear - beginYear;
        }

        return tableIndex;
    }

    public static void main(String[] args) {
        System.out.println(getTableIndex(2006));
        System.out.println(getTableIndex(2007));
        System.out.println(getTableIndex(2108));
        System.out.println(getTableIndex(2109));
        System.out.println(getTableIndex(2210));
        System.out.println(getTableIndex(2211));
        System.out.println(getTableIndex(2312));
        System.out.println(getTableIndex(2301));

    }
}

InvoiceResumeRangeModuloShardingTableAlgorithm

package com.gtmc.ssp.bff.admin.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.ArrayList;
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 = from / 100;
        int endY = end / 100;
        for (int i = fromY; i <= endY; i++) {

            final int tableIndex = InvoiceResumePreciseShardingAlgorithmConfig.getTableIndex(i);
            hashSet.add(InvoiceResumePreciseShardingAlgorithmConfig.PreTableName + "_" + tableIndex);

        }

        return hashSet;
    }
}

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

上一篇：postgresql数据库 to_date()函数，to_timestamp()函数


下一篇：使用compose构建Docker多容器应用(Nginx+PHP+MySQL+Redis)