背景
最近在搞数据迁移,之前存储的介质为mongo,集团层面在推动mongo下线,所以迁移到mySql是主流趋势,mongo目前共6分片,总数大概40亿左右,mySql承接的话,设计为32个库,每个库128个表。数据库的分片,中间件团队已经做了,我们需要处理的是路由到表这块的处理
前置官方资料
https://shardingsphere.apache.org/document/ 选择5.0.0版本进行阅读
使用Spring boot 能很快结束开发,使用Spring mvc实属无奈,但是旧系统升级重构时间长,改造成本大,时间紧迫,只能先接入再说后续升级。
写在前面的话
为什么选择5.0.0版本作为最终的使用的版本
-
之前在很久之前,使用过dangdang 1.4.1版本的sharding jdbc,版本太老了,而且这个dangdang的版本已经不维护了。
-
所以决定使用 sharding -jdbc 3.x.x ,这个时候坐标变成了 io.shardingsphere 但是在网上查询发现,有内存泄漏的问题,于是转战4.x.x
-
到了4.x.x版本,此时坐标变成了org.apache.shardingsphere,又发现启动特别慢,启动的时候回查询所有表的信息,非常不合理,我们表又特别多,大概5000张以上,这个时候,启动就要5分钟,非常不能接受
-
今年11月份,发布了5.0.0,解决了上面的一些问题,终于能够正常使用了,但是又遇到了与数据库连接池、guava版本兼容性的问题,但好在,能用。
-
sharding jdbc 也算尝试了很多个版本了,发现最大的问题就是,版本之间基本上没什么关系,包括namespace 、配置方式 、tag标 每个版本区别多很大,而且官方介绍的文档不是很详细,从github专门去下载了 example ,发现没有覆盖想要的例子,定眼一看,没有我用的版本!
下面开始搞事情!
接入shardingSphere
maven依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>${sharding.sphere.version}</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-namespace</artifactId>
<version>${sharding.sphere.version}</version>
</dependency>
<properties>
<sharding.sphere.version>5.0.0</sharding.sphere.version>
</properties>
已知版本冲突问题
shardingSphere5 对druid 的版本有要求,低版本有不兼容的情况,我目前使用的版本为1.2.8,没有发现兼容问题。使用其他连接池需要自行测试兼容性
同时对guava的版本也存在兼容性问题,我使用的版本为27.1-jre,没有发现问题。
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>27.1-jre</version>
</dependency>
配置原有数据源
此时配置的是原始的数据库连接,和sharding jdbc还没有关联,正常使用什么连接池都可以
<bean id="dataSourcePro" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${ldb.username}"/>
<property name="password" value="${db.password}"/>
<property name="timeBetweenEvictionRunsMillis" value="3600000"/>
<property name="minEvictableIdleTimeMillis" value="3600000"/>
<property name="maxActive" value="150"/>
<property name="minIdle" value="3"/>
<property name="maxIdle" value="10"/>
<property name="validationQuery" value="SELECT 1"/>
</bean>
nameSpace配置:
两个nameSpace分别对应shardingSphere的数据源处理与分片规则的处理
xmlns:shardingsphere="http://shardingsphere.apache.org/schema/shardingsphere/datasource"
xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
scheme配置:
http://shardingsphere.apache.org/schema/shardingsphere/datasource
http://shardingsphere.apache.org/schema/shardingsphere/datasource/datasource.xsd
http://shardingsphere.apache.org/schema/shardingsphere/sharding http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
配置sharding的dataSource
data-source-names填写刚才定义的dataSource,因为我这边只用到了单库所以只配置了一个数据库名称
具体分表的规则见:shardingRule
<!-- 配置ShardingSphereDataSource -->
<shardingsphere:data-source id="shardingDataSource" data-source-names="dataSourcePro"
rule-refs="shardingRule">
<props>
<prop key="sql-show">false</prop>
</props>
</shardingsphere:data-source>
配置sharding 的rule
- logic-table 指的是不加后缀的原始表
- actual-data-nodes 具体的表,从0到127,通过表达式表达出来,且前边需要加上数据源,为原本的数据源
- table-strategy-ref指的是分表策略
<!-- 配置sharding策略 -->
<!--默认不分库(default-database-strategy-ref) 不分表(default-table-strategy-ref)-->
<sharding:rule id="shardingRule" default-database-strategy-ref="" default-table-strategy-ref="">
<sharding:table-rules>
<sharding:table-rule logic-table="trx_lot_relation_group"
actual-data-nodes="dataSourcePro.trx_lot_relation_group_$->{0..127}"
table-strategy-ref="groupLotRelationTableStrategy"
/>
</sharding:table-rules>
</sharding:rule>
表的路由规则
- sharding-column 指的是分表字段
- algorithm-ref 指的是分表具体的算法
<!-- 表路由规则-->
<sharding:standard-strategy id="groupLotRelationTableStrategy" sharding-column="sku_id"
algorithm-ref="groupLotRelationTableAlgorithmRef"/>
具体的算法规则
- type=“CLASS_BASED” 指的是基于java类的处理
- standard 指的是标准分片,包含着精确分片和区间分片(range)
- algorithmClassName 为具体的实现类
<sharding:sharding-algorithm id="groupLotRelationTableAlgorithmRef" type="CLASS_BASED">
<props>
<prop key="strategy">standard</prop>
<prop key="algorithmClassName">com.lbm.core.mysql.sharding.table.GroupLotRelationTableAlgorithm</prop>
</props>
</sharding:sharding-algorithm>
mybatis session处理
这个时候dataSource填写刚才配置的shardingDataSource
<!--sharding datasource 接管 pro数据源 -->
<bean id="sessionFactoryPro" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="shardingDataSource"/>
<property name="configLocation" value="classpath:spring/mybatis-config.xml"/>
<property name="mapperLocations" value="classpath:mapper/*.xml"/>
</bean>
配置事务
此时dataSource为新配置的shardingDataSource,否则事务不会生效
<bean id="transactionManagerPro"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="shardingDataSource"/>
<qualifier value="transactionManagerPro"/>
</bean>
<tx:annotation-driven transaction-manager="transactionManagerPro" proxy-target-class="true"/>
mybatis mapperConfig配置
mapper扫描与session注入
<!--JED主库配置-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.lbm.core.mysql.lbmpro"/>
<property name="sqlSessionFactoryBeanName" value="sessionFactoryPro"/>
</bean>
全部源码配置
数据源配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
<bean id="lbmDataSourcePro" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="${lbm.pro.db.url}"/>
<property name="username" value="${lbm.pro.db.username}"/>
<property name="password" value="${lbm.pro.db.password}"/>
<property name="timeBetweenEvictionRunsMillis" value="3600000"/>
<property name="minEvictableIdleTimeMillis" value="3600000"/>
<property name="maxActive" value="150"/>
<property name="minIdle" value="3"/>
<property name="maxIdle" value="10"/>
<property name="validationQuery" value="SELECT 1"/>
</bean>
<!--JED主库配置-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.lbm.core.mysql.lbmpro"/>
<property name="sqlSessionFactoryBeanName" value="sessionFactoryPro"/>
</bean>
<bean id="transactionManagerPro"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="shardingDataSource"/>
<qualifier value="transactionManagerPro"/>
</bean>
<tx:annotation-driven transaction-manager="transactionManagerPro" proxy-target-class="true"/>
<!--sharding datasource 接管 pro数据源 -->
<bean id="sessionFactoryPro" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="shardingDataSource"/>
<property name="configLocation" value="classpath:spring/mybatis-config.xml"/>
<property name="mapperLocations" value="classpath:mapper/*.xml"/>
</bean>
<!--sharding jdbc配置-->
<import resource="classpath:spring/spring-config-sjdbc.xml"/>
</beans>
shardingJdbc配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
xmlns:shardingsphere="http://shardingsphere.apache.org/schema/shardingsphere/datasource"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.apache.org/schema/shardingsphere/sharding
http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
http://shardingsphere.apache.org/schema/shardingsphere/datasource
http://shardingsphere.apache.org/schema/shardingsphere/datasource/datasource.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<!-- 事务关系表路由规则-->
<sharding:standard-strategy id="groupLotRelationTableStrategy" sharding-column="sku_id"
algorithm-ref="groupLotRelationTableAlgorithmRef"/>
<sharding:sharding-algorithm id="groupLotRelationTableAlgorithmRef" type="CLASS_BASED">
<props>
<prop key="strategy">standard</prop>
<prop key="algorithmClassName">com.lbm.core.mysql.sharding.table.GroupLotRelationTableAlgorithm</prop>
</props>
</sharding:sharding-algorithm>
<context:property-placeholder ignore-unresolvable="true"/>
<!-- 配置ShardingSphereDataSource -->
<shardingsphere:data-source id="shardingDataSource" data-source-names="lbmDataSourcePro"
rule-refs="shardingRule">
<props>
<prop key="sql-show">false</prop>
</props>
</shardingsphere:data-source>
<!-- 配置sharding策略 -->
<!--默认不分库(default-database-strategy-ref) 不分表(default-table-strategy-ref)-->
<sharding:rule id="shardingRule" default-database-strategy-ref="" default-table-strategy-ref="">
<sharding:table-rules>
<sharding:table-rule logic-table="trx_lot_relation_group"
actual-data-nodes="lbmDataSourcePro.trx_lot_relation_group_$->{0..127}"
table-strategy-ref="groupLotRelationTableStrategy"
/>
</sharding:table-rules>
</sharding:rule>
</beans>
具体的分片算法
package com.lbm.core.mysql.sharding.table;
import java.util.Collection;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import org.springframework.stereotype.Component;
/**
* groupLotRelation分表策略
*
* @author wangzy-nice
* @date 2021-12-08 22:02
*/
@Component
public class GroupLotRelationTableAlgorithm implements StandardShardingAlgorithm<String> {
/**
* 区间分片
*
* @param tableNames
* @param rangeShardingValue
* @return
*/
@Override
public Collection<String> doSharding(Collection<String> tableNames,
RangeShardingValue<String> rangeShardingValue) {
throw new UnsupportedOperationException("暂未支持区间查询");
}
/**
* 精确分片
*
* @param tableNames
* @param preciseShardingValue
* @return
*/
@Override
public String doSharding(Collection<String> tableNames,
PreciseShardingValue<String> preciseShardingValue) {
for (String key : tableNames) {
if (key.endsWith(String.valueOf(Long.parseLong(preciseShardingValue.getValue()) % 128))) {
return key;
}
}
throw new UnsupportedOperationException();
}
@Override
public void init() {
}
@Override
public String getType() {
return "GROUP_LOT_RELATION";
}
}