数据库拆分5--使用sharding-jdbc来实现水平拆分

暗夜猎手-大魔王

已于 2022-12-19 21:02:18 修改

阅读量850

点赞数

分类专栏：数据库文章标签：数据库 mysql java

于 2022-12-18 18:41:15 首次发布

本文链接：https://blog.csdn.net/u014106644/article/details/128363511

版权

数据库专栏收录该内容

41 篇文章 6 订阅

订阅专栏

有三张表 user log order表，先将user log 和order垂直分库，然后将user表水平拆分

配置文件

spring.shardingsphere.enabled=true

spring.shardingsphere.datasource.names=wim-user,wim-order

spring.shardingsphere.datasource.wim-user.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.wim-user.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.wim-user.url=jdbc:mysql://127.0.0.1:3306/wim-user?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.wim-user.username=root
spring.shardingsphere.datasource.wim-user.password=123456

spring.shardingsphere.datasource.wim-order.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.wim-order.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.wim-order.url=jdbc:mysql://127.0.0.1:3306/wim-order?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.wim-order.username=root
spring.shardingsphere.datasource.wim-order.password=123456

spring.shardingsphere.sharding.tables.user_t.actual-data-nodes=wim-user.user_t_$->{0..1}
spring.shardingsphere.sharding.tables.log_t.actual-data-nodes=wim-user.log_t

spring.shardingsphere.sharding.tables.user_t.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.user_t.table-strategy.inline.algorithm-expression=user_t_$->{user_id % 2}

spring.shardingsphere.sharding.tables.log_t.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.log_t.table-strategy.inline.algorithm-expression=log_t


spring.shardingsphere.sharding.default-data-source-name=wim-order

spring.shardingsphere.props.sql.show=true

拆分的配置方法直接看一下对应代码和实现类

学习一下各种分片策略：

行表达式分片策略（InlineShardingStrategy）

YamlInlineShardingStrategyConfiguration

public final class YamlInlineShardingStrategyConfiguration implements YamlBaseShardingStrategyConfiguration {
    private String shardingColumn;
    private String algorithmExpression;

    public YamlInlineShardingStrategyConfiguration() {
    }

    @Generated
    public String getShardingColumn() {
        return this.shardingColumn;
    }

    @Generated
    public String getAlgorithmExpression() {
        return this.algorithmExpression;
    }

    @Generated
    public void setShardingColumn(String shardingColumn) {
        this.shardingColumn = shardingColumn;
    }

    @Generated
    public void setAlgorithmExpression(String algorithmExpression) {
        this.algorithmExpression = algorithmExpression;
    }
}

spring.shardingsphere.sharding.tables.user_t.actual-data-nodes=wim-user.user_t_$->{0..1}
spring.shardingsphere.sharding.tables.user_t.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.user_t.table-strategy.inline.algorithm-expression=user_t_$->{user_id % 2}

需要配置分片键和分片表达式

有两张user表 user_t_0 user_t_1 根据user_id%2来路由到不同数据库。

1）使用场景：只支持单分片健；支持对 SQL语句中的 = 、IN 等的分片操作，不支持between and。

2）使用方法：适用于做简单的分片算法，在配置中使用 Groovy 表达式，无需自定义分片算法，省去了繁琐的代码开发，是几种分片策略中最为简单的。

标准分片策略（StandardShardingStrategy）

YamlStandardShardingStrategyConfiguration

public final class YamlStandardShardingStrategyConfiguration implements YamlBaseShardingStrategyConfiguration {
    private String shardingColumn;
    private String preciseAlgorithmClassName;
    private String rangeAlgorithmClassName;

SQL 语句中有>，>=, <=，<，=，IN和BETWEEN AND操作符，都可以应用此分片策略。它只支持对单个分片健（字段）为依据的分库分表，并提供了两种分片算法 PreciseShardingAlgorithm（精准分片）和 RangeShardingAlgorithm（范围分片）

精准分库算法：实现 PreciseShardingAlgorithm 接口，并重写 doSharding() 方法。其中 Collection<String> 参数在几种分片策略中使用一致，在分库时值为所有分片库的集合 databaseNames，分表时为对应分片库中所有分片表的集合 tablesNames；PreciseShardingValue 为分片属性，其中 logicTableName 为逻辑表，columnName 分片健（字段），value 为从 SQL 中解析出的分片健的值；

假设log表需要根据id来分片大于1000的分片0 其他分片1

spring.shardingsphere.sharding.tables.log_t.actual-data-nodes=wim-user.log_t_$->{0..1}
spring.shardingsphere.sharding.tables.log_t.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.log_t.table-strategy.standard.precise-algorithm-class-name=com.chen.algorithm.MyPreciseShardingAlgorithm

public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm {
	
	@Override
	public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
		Object[] ds = collection.toArray();
		long logId = (long) preciseShardingValue.getValue();
		if (logId > 1000) {
			return (String) ds[0];
		} else {
			return (String) ds[1];
		}
	}
}

在doSharding方法中可以实现更加复杂的判断逻辑

范围分片算法：分片健字段用到 BETWEEN AND操作符会使用到此算法，会根据 SQL中给出的分片健值范围值处理分库、分表逻辑。自定义范围分片算法需实现 RangeShardingAlgorithm 接口，重写 doSharding() 方法，Collection<String> 在分库、分表时分别代表分片库名和表名集合，RangeShardingValue 这里取值方式稍有不同， lowerEndpoint 表示起始值， upperEndpoint 表示截止值

假设log表当查询时间范围包含2021年时则从两个数据库查询合并结果返回否则取第一个数据库数据返回

spring.shardingsphere.sharding.tables.log_t.actual-data-nodes=wim-user.log_t_$->{0..1}
spring.shardingsphere.sharding.tables.log_t.table-strategy.standard.sharding-column=log_date
spring.shardingsphere.sharding.tables.log_t.table-strategy.standard.range-algorithm-class-name=com.chen.algorithm.MyRangeShardingAlgorithm
spring.shardingsphere.sharding.tables.log_t.table-strategy.standard.precise-algorithm-class-name=com.chen.algorithm.MyPreciseShardingAlgorithm

public class MyRangeShardingAlgorithm implements RangeShardingAlgorithm<Date> {
	
	@Override
	public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Date> rangeShardingValue) {
		Range range = rangeShardingValue.getValueRange();
		boolean flag = range.contains("2021");
		if (flag) {
			return collection;
		} else {
			return Arrays.asList((String) collection.toArray()[0]);
		}
	}
}

RangeShardingAlgorithm算法需要和PreciseShardingAlgorithm一同实现来使用 否则算法加载会报错 好奇怪的逻辑

 if (null != yamlConfiguration.getStandard()) {
            ++shardingStrategyConfigCount;
            if (null == yamlConfiguration.getStandard().getRangeAlgorithmClassName()) {
                result = new StandardShardingStrategyConfiguration(yamlConfiguration.getStandard().getShardingColumn(), (PreciseShardingAlgorithm)ShardingAlgorithmFactory.newInstance(yamlConfiguration.getStandard().getPreciseAlgorithmClassName(), PreciseShardingAlgorithm.class));
            } else {
                result = new StandardShardingStrategyConfiguration(yamlConfiguration.getStandard().getShardingColumn(), (PreciseShardingAlgorithm)ShardingAlgorithmFactory.newInstance(yamlConfiguration.getStandard().getPreciseAlgorithmClassName(), PreciseShardingAlgorithm.class), (RangeShardingAlgorithm)ShardingAlgorithmFactory.newInstance(yamlConfiguration.getStandard().getRangeAlgorithmClassName(), RangeShardingAlgorithm.class));
            }
        }

复合分片策略

YamlComplexShardingStrategyConfiguration

public final class YamlComplexShardingStrategyConfiguration implements YamlBaseShardingStrategyConfiguration {
    private String shardingColumns;
    private String algorithmClassName;

SQL 语句中有>，>=, <=，<，=，IN 和 BETWEEN AND 等操作符，且复合分片策略支持对多个分片健操作。自定义复合分片策略要实现 ComplexKeysShardingAlgorithm 接口，重新 doSharding()方法

假设log表 user_id log_date userId=1 log_date包含2022的到表0中查询其他两张表查询合并

spring.shardingsphere.sharding.tables.log_t.actual-data-nodes=wim-user.log_t_$->{0..1}
spring.shardingsphere.sharding.tables.log_t.table-strategy.complex.sharding-columns=user_id, log_date
spring.shardingsphere.sharding.tables.log_t.table-strategy.complex.algorithm-class-name=com.chen.algorithm.MyComplexKeysShardingAlgorithm

public class MyComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm {
	
	@Override
	public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
		System.out.println(complexKeysShardingValue);
		List<String> list = (List<String>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("user_id");
		Range range = (Range) complexKeysShardingValue.getColumnNameAndRangeValuesMap().get("log_date");
		boolean flag1 = list.contains("1");
		boolean flag2 = range.contains("2022");
		if (flag1 && flag2) {
			return Arrays.asList((String) collection.toArray()[0]);
		}
		return collection;
	}
}

Hint分片策略

YamlHintShardingStrategyConfiguration

public final class YamlHintShardingStrategyConfiguration implements YamlBaseShardingStrategyConfiguration {
    private String algorithmClassName;

这种分片策略无需配置分片健，分片健值也不再从 SQL中解析，而是由外部指定分片信息，让 SQL在指定的分库、分表中执行。ShardingSphere 通过 Hint API实现指定操作，实际上就是把分片规则tablerule 、databaserule由集中配置变成了个性化配置。

实现 HintShardingAlgorithm 接口并重写 doSharding()方法

在请求的上下文中直接指定数据库和表

spring.shardingsphere.sharding.tables.log_t.actual-data-nodes=wim-user.log_t_$->{0..1}
spring.shardingsphere.sharding.tables.log_t.table-strategy.hint.algorithm-class-name=com.chen.algorithm.MyHintShardingAlgorithm

HintManager

@GetMapping("/query/{id}")
	public String query(@PathVariable Long id) {
		HintManager hintManager = HintManager.getInstance();
		hintManager.addTableShardingValue("log_t","1");
		Log logT = logMapper.selectById(id);
		log.info("query log {}", logT);
		if (logT == null) {
			return "cannot find user";
		}
		return logT.toString();
	}

public class MyHintShardingAlgorithm implements HintShardingAlgorithm {
	
	@Override
	public Collection<String> doSharding(Collection collection, HintShardingValue hintShardingValue) {
		Object[] ds = collection.toArray();
		Object[] v = hintShardingValue.getValues().toArray();
		int index = Integer.valueOf((String) v[0]);
		return Arrays.asList((String) ds[index]);
	}
}