共计 11283 个字符，预计需要花费 29 分钟才能阅读完成。
本文描述在本地数据库模拟分库分表、读写分离的整合实现，假定会员数据按照 ID 取模进行分库分表，分为 2 个主库，每个库分配一个读库，累计 100 张表。如下表所示：
库
主 / 从
表
user_1
主
t_user_00 ~ t_user_49
user_slave_1
从
t_user_00 ~ t_user_49
user_2
主
t_user_50 ~ t_user_99
user_slave_2
从
t_user_50 ~ t_user_99
本文主要展示核心代码，部分如 Controller、Service 层的测试代码实现非常简单，故而省略这部分代码。
依赖版本
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>2.1.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.1.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
<version>2.1.3.RELEASE</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc</artifactId>
<version>3.0.0.M1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.41</version>
</dependency>
数据准备
use user_1;
CREATE TABLE `t_user_00` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user_01` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user_02` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
use user_2;
CREATE TABLE `t_user_50` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user_51` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user_52` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
use user_slave_1;
CREATE TABLE `t_user_00` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user_01` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user_02` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
use user_slave_2;
CREATE TABLE `t_user_50` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user_51` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user_52` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
代码实现
数据源配置
server:
port: 23333
spring:
application:
name: pt-framework-demo
datasource:
type: com.alibaba.druid.pool.DruidDataSource
datasource:
default:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3307/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&autoReconnect=true&failOverReadOnly=false
username: root
password: root
test-on-borrow: false
test-while-idle: true
time-between-eviction-runs-millis: 18800
filters: mergeStat,wall,slf4j
connectionProperties: druid.stat.slowSqlMillis=2000
validationQuery: SELECT 1
poolPreparedStatements: true
user:
master:
user1:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3307/user_1?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&autoReconnect=true&failOverReadOnly=false
username: root
password: root
test-on-borrow: false
test-while-idle: true
time-between-eviction-runs-millis: 18800
filters: mergeStat,wall,slf4j
connectionProperties: druid.stat.slowSqlMillis=2000
validationQuery: SELECT 1
poolPreparedStatements: true
user2:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3307/user_2?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&autoReconnect=true&failOverReadOnly=false
username: root
password: root
test-on-borrow: false
test-while-idle: true
time-between-eviction-runs-millis: 18800
filters: mergeStat,wall,slf4j
connectionProperties: druid.stat.slowSqlMillis=2000
validationQuery: SELECT 1
poolPreparedStatements: true
slave:
user1:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3307/user_slave_1?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&autoReconnect=true&failOverReadOnly=false
username: root
password: root
test-on-borrow: false
test-while-idle: true
time-between-eviction-runs-millis: 18800
filters: mergeStat,wall,slf4j
connectionProperties: druid.stat.slowSqlMillis=2000
validationQuery: SELECT 1
poolPreparedStatements: true
user2:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3307/user_slave_2?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&autoReconnect=true&failOverReadOnly=false
username: root
password: root
test-on-borrow: false
test-while-idle: true
time-between-eviction-runs-millis: 18800
filters: mergeStat,wall,slf4j
connectionProperties: druid.stat.slowSqlMillis=2000
validationQuery: SELECT 1
poolPreparedStatements: true
主从、读写分离
/**
* Created by Captain on 01/03/2019.
*/
@Configuration
@MapperScan(basePackages = {“com.xxxx.framework.usermapper”}, sqlSessionFactoryRef = “userShardingSqlSessionFactory”)
public class UserShardingDBConfiguration {
@Value(“${spring.datasource.type}”)
private Class<? extends DataSource> dataSourceType;
private static final String USER_1_MASTER = “dsUser1Master”;
private static final String USER_1_SLAVE = “dsUser1Slave”;
private static final String USER_2_MASTER = “dsUser2Master”;
private static final String USER_2_SLAVE = “dsUser2Slave”;
private static final String USER_SHARDING_1 = “dsMasterSlave1”;
private static final String USER_SHARDING_2 = “dsMasterSlave2”;
private static final String USER_SHARDING_DATA_SOURCE = “userSharding”;
@Bean(USER_1_MASTER)
@ConfigurationProperties(prefix = “datasource.user.master.user1”)
public DataSource dsUser1(){
return DataSourceBuilder.create().type(dataSourceType).build();
}
@Bean(USER_2_MASTER)
@ConfigurationProperties(prefix = “datasource.user.master.user2”)
public DataSource dsUser2(){
return DataSourceBuilder.create().type(dataSourceType).build();
}
@Bean(USER_1_SLAVE)
@ConfigurationProperties(prefix = “datasource.user.slave.user1”)
public DataSource dsUserSlave1(){
return DataSourceBuilder.create().type(dataSourceType).build();
}
/**
* user_2
* @return
*/
@Bean(USER_2_SLAVE)
@ConfigurationProperties(prefix = “datasource.user.slave.user2”)
public DataSource dsUserSlave2(){
return DataSourceBuilder.create().type(dataSourceType).build();
}
@Bean(USER_SHARDING_1)
public DataSource masterSlave1(@Qualifier(USER_1_MASTER) DataSource dsUser1,@Qualifier(USER_1_SLAVE) DataSource dsUserSlave1) throws Exception {
Map<String,DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put(USER_1_MASTER, dsUser1);
dataSourceMap.put(USER_1_SLAVE, dsUserSlave1);
MasterSlaveRuleConfiguration ruleConfiguration = new MasterSlaveRuleConfiguration(“dsUser1”, USER_1_MASTER, Lists.newArrayList(USER_1_SLAVE));
return MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, ruleConfiguration, new ConcurrentHashMap<>());
}
@Bean(USER_SHARDING_2)
public DataSource masterSlave2(@Qualifier(USER_2_MASTER) DataSource dsUser2,@Qualifier(USER_2_SLAVE) DataSource dsUserSlave2) throws Exception {
Map<String,DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put(USER_2_MASTER, dsUser2);
dataSourceMap.put(USER_2_SLAVE, dsUserSlave2);
MasterSlaveRuleConfiguration ruleConfiguration = new MasterSlaveRuleConfiguration(“dsUser2”, USER_2_MASTER, Lists.newArrayList(USER_2_SLAVE));
return MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, ruleConfiguration, new ConcurrentHashMap<>());
}
@Bean(USER_SHARDING_DATA_SOURCE)
public DataSource dsUser(@Qualifier(USER_SHARDING_1) DataSource dsUser1, @Qualifier(USER_SHARDING_2) DataSource dsUser2) throws Exception {
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put(“dsUser1”, dsUser1);
dataSourceMap.put(“dsUser2”, dsUser2);
ShardingRuleConfiguration userRule = getUserRule();
userRule.setDefaultDataSourceName(“dsUser”);
return ShardingDataSourceFactory.createDataSource(dataSourceMap, userRule, new ConcurrentHashMap<>(), new Properties());
}
/**
* 配置分片规则
* @return
*/
private ShardingRuleConfiguration getUserRule(){
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration(“id”, new MemberIdShardingSchemeAlgorithm()));
shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration(“id”,new MemberIdShardingTableAlgorithm()));
shardingRuleConfig.getBindingTableGroups().add(“t_user”);
return shardingRuleConfig;
}
@Bean(“userShardingSqlSessionFactory”)
public SqlSessionFactory userSqlSessionFactory(@Qualifier(USER_SHARDING_DATA_SOURCE) DataSource dataSource) throws Exception{
MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(“classpath:usermapper/*.xml”));
return sqlSessionFactoryBean.getObject();
}
@Bean(“userTransaction”)
public DataSourceTransactionManager userTransactionManager(@Qualifier(USER_SHARDING_DATA_SOURCE) DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
}
分库策略
/**
* CoreUser 分库策略
* Created by Captain on 01/03/2019.
*/
public class MemberIdShardingSchemeAlgorithm implements PreciseShardingAlgorithm<Integer> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
for (String str : availableTargetNames){
int index = shardingValue.getValue() % 100;
return str + (index > 49 ? “2” : “1”);
}
return null;
}
}
分表策略
/**
* 会员信息分表策略，按照 id 分表
* Created by Captain on 04/03/2019.
*/
public class MemberIdShardingTableAlgorithm implements PreciseShardingAlgorithm<Integer> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
int index = shardingValue.getValue() % 100;
return shardingValue.getLogicTableName() + “_” + (index < 10 ? “0” + index : index + “”);
}
}
实体类
/**
* Created by Captain on 01/03/2019.
*/
@TableName(“t_user”)
public class User {
@TableId(type = IdType.INPUT)
private Integer id;
private String name;
private Integer age;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
Mapper
/**
* Created by Captain on 04/03/2019.
*/
public interface UserMapper extends BaseMapper<User> {
}
测试预期
模拟过程没有实际做主从同步，写入“主库”中的数据并不能自动同步至“从库”，因此，插入数据后，需要手动写入数据至对应的从库，并且可对数据进行差异写入，测试查询时可根据差异来判断读写分离是否生效。
测试用例
预期结果
插入数据 id 指定为 8902
user_1 中数据写入成功
插入数据 id 指定为 8952
user_2 中数据写入成功
查询 id 为 8902 的数据
查询到 user_slave_1 中的结果
查询 id 为 8952 的数据
查询到 user_slave_2 中的结果
