shardingjdbc简介
上一篇介绍了分库分表的理论，本篇基于官网的example整理下分库分表的demo。能跑起来，满足业务需求。
官网地址：https://github.com/sharding-sphere/sharding-sphere-example
下面是架构图，可见分库分表涉及的有分库路由，分库规则，分库关键字等。其余的框架给我们实现了。
二 demo
整合只需添加三个地方（pom.xml、application.xml、策略类）。
版本是2.0.3，做了分库分表。
pom.xml
/sharding-jdbc-spring-namespace-example/pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>io.shardingjdbc</groupId>
<artifactId>sharding-jdbc-example</artifactId>
<version>2.1.0-SNAPSHOT</version>
</parent>
<artifactId>sharding-jdbc-spring-namespace-example</artifactId>
<packaging>pom</packaging>
<modules>
<module>sharding-jdbc-spring-namespace-mybatis-example</module>
<module>sharding-jdbc-spring-namespace-jpa-example</module>
</modules>
<dependencies>
<dependency>
<groupId>io.shardingjdbc</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>2.0.3</version>
</dependency>
<dependency>
<groupId>io.shardingjdbc</groupId>
<artifactId>sharding-jdbc-core-spring-namespace</artifactId>
<version>2.0.3</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring-framework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring-framework.version}</version>
</dependency>
</dependencies>
</project>
namespace要加入版本，不然提示缺包。
<?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:tx="http://www.springframework.org/schema/tx"
xmlns:sharding="http://shardingjdbc.io/schema/shardingjdbc/sharding"
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
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://shardingjdbc.io/schema/shardingjdbc/sharding
http://shardingjdbc.io/schema/shardingjdbc/sharding/sharding.xsd">
<context:component-scan base-package="io.shardingjdbc.example.spring.namespace.mybatis" />
<bean id="demo_ds_0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3612/test"/>
<property name="username" value="root"/>
<property name="password" value=""/>
</bean>
<bean id="demo_ds_1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3612/test_db"/>
<property name="username" value="root"/>
<property name="password" value=""/>
</bean>
<bean id="demo_ds_2" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3612/test1"/>
<property name="username" value="root"/>
<property name="password" value=""/>
</bean>
<sharding:standard-strategy id="databaseShardingStrategy" sharding-column="user_id" precise-algorithm-class="io.shardingjdbc.example.spring.namespace.mybatis.algorithm.PreciseModuloDatabaseShardingAlgorithm" />
<sharding:standard-strategy id="tableShardingStrategy" sharding-column="order_id" precise-algorithm-class="io.shardingjdbc.example.spring.namespace.mybatis.algorithm.PreciseModuloTableShardingAlgorithm" />
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="demo_ds_0,demo_ds_1,demo_ds_2">
<sharding:table-rules>
<sharding:table-rule logic-table="t_order" actual-data-nodes="demo_ds_${0..2}.t_order_${0..7}" database-strategy-ref="databaseShardingStrategy" table-strategy-ref="tableShardingStrategy" generate-key-column="order_id" />
<sharding:table-rule logic-table="t_order_item" actual-data-nodes="demo_ds_${0..2}.t_order_item_${0..7}" database-strategy-ref="databaseShardingStrategy" table-strategy-ref="tableShardingStrategy" generate-key-column="order_item_id" />
</sharding:table-rules>
<sharding:binding-table-rules>
<sharding:binding-table-rule logic-tables="t_order, t_order_item"/>
</sharding:binding-table-rules>
</sharding:sharding-rule>
</sharding:data-source>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="shardingDataSource" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="shardingDataSource"/>
<property name="mapperLocations" value="classpath*:META-INF/mappers/*.xml"/>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="io.shardingjdbc.example.spring.namespace.mybatis"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
</beans>
对应的分库逻辑代码：
/*
* Copyright 1999-2015 dangdang.com.
* <p>
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
* </p>
*/
package io.shardingjdbc.example.spring.namespace.mybatis.algorithm;
import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import java.util.Collection;
public final class PreciseModuloDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
@Override
public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Integer> shardingValue) {
int businessid =shardingValue.getValue();
if(businessid == 701){
return "demo_ds_0";
}
else if(businessid == 803){
return "demo_ds_1";
}else if(businessid == 912){
return "demo_ds_2";
}
// for (String each : availableTargetNames) {
// if (each.endsWith(shardingValue.getValue() % 2 + "")) {
// return each;
// }
// }
throw new UnsupportedOperationException();
}
}
下面注释掉的是取模的分库，这里业务场景是：不同的业务线不同的数据库，所以根据业务线ID做了硬编码。
如果类似订单中心这种不分表，只分库，所有业务线都在拆分到不同库，也可以去取模。看需求而定。
这里是为了业务线要求，数据隔离。
分表代码：
/*
* Copyright 1999-2015 dangdang.com.
* <p>
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
* </p>
*/
package io.shardingjdbc.example.spring.namespace.mybatis.algorithm;
import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import java.util.Collection;
public final class PreciseModuloTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Long> shardingValue) {
for (String each : availableTargetNames) {
System.out.println(shardingValue.getValue()+":"+new InnerHashing().hash(shardingValue.getValue()+"") % 8);
if (each.endsWith(new InnerHashing().hash(shardingValue.getValue()+"") % 8+ "")) {
return each;
}
}
throw new UnsupportedOperationException();
}
}
分表是，每个业务线分库后，每个库再拆分多个表。参见配置文件，分表是按照orderid进行水平拆分的。
关于分表，考虑到shardingjdbc自带的id生成方法：snowflake 生成的偶数较多。所以有做了hash处理后再取模，是为了数据拆分的均匀。
/**
*
*/
package io.shardingjdbc.example.spring.namespace.mybatis.algorithm;
import java.nio.charset.Charset;
import org.apache.commons.lang3.builder.HashCodeBuilder;
/**
* @author daojia
*
*/
public class InnerHashing implements Hashing {
static Charset charset = Charset.forName("utf-8");
@Override
public long hash(String key) {
return hash(key.getBytes(charset));
}
@Override
public long hash(byte[] key) {
int hashcode = new HashCodeBuilder().append(key).toHashCode();
return hashcode & 0x7FFFFFFF;
}
}
innerhash是从jedis截取的代码，如果用别的id生成方法比较均匀。可以不用hash直接取模。
自己测试的时候可以看看，比如生成8W条数据，是不是均匀分到8个表。
其余代码是demo自带的。
数据库也是demo表自带的。t_order，t_order_item
另外：
分表后不推荐主键使用数据库自增。
考虑现有数据量及数据增长量，通常分表后单表数据量一年内不超过1000W。考虑使用字符变量等，可以800W以下。
官网demo比较多，主从，只分库，只分表，分库分表。需要结合自己业务来。
另外单纯的取模分表，可以在yaml配置，好处是下次扩容的时候，只是修改配置文件重启即可，不用再修改代码发版本。
版权声明：本文不是「本站」原创文章，版权归原作者所有 | 原文地址: