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}&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://ip: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:
#两月一张 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);
}
}
}