项目中用到了分库分表对数据做了读写分离,分散了数据库服务的压力
依靠的中间件是shardingsphere开源产品,先演示最简单的读写分离下面上代码:
1、读写分离
- 首先搭建测试所需要的环境:两个数据库,当然也可以一主多从,根据需要来进行配置多个从库(搭建数据库就不做演示了)
引入依赖:
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
- 编写需要的实体类文件:model: Test数据库映射类、mapper: TestMapperDAO层接口、TestMapper.xmlDAO层sql文件、和一个TestController测试类。service层就暂且跳过了,因为service主要是根据项目中的业务来实现的,与demo关联不大
Test数据库映射类:
@Data
@Table(name = "test")
public class Test {
/**
* 主键 ID
*/
@Id
// 项目中采用了雪花算法来生成ID
@KeySql(genId = SnowflakeIdGenId.class)
private Long id;
/**
* 姓名
*/
private String name;
/**
* 创建时间
*/
@Column(name = "create_time")
private LocalDateTime createTime;
}
mapper: TestMapperDAO层接口:定义了两个查询来测试
public interface TestMapper extends MyMapper<Test, Long> {
/**
*
* 该方法用来测试分页功能是否完全
*/
List<Test> selectByPage();
/**
*
* 该方法测试如果不使用分片字段查询是否有影响
*/
Test> selectById();
}
TestMapper.xmlDAO层sql文件:xml中写了两个sql用来测试
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapper对应文件位置">
<select id="selectByPage" resultType="Test对应文件位置">
select id, name, create_time createTime from test order by create_time
</select>
<select id="selectById" resultType="Test对应文件位置">
select * from test where id = 1
</select>
</mapper>
TestController测试类
@Slf4j
@RestController
@RequestMapping("/test")
@Api(value = "测试", tags = {"测试"}, consumes = "application/json")
public class AOOrganController {
@Autowired
private TestMapper testMapper;
@PostMapping("/list")
@ApiOperation(value = "测试列表", notes = "测试列表", produces = "application/json")
public ResponseMessage list(@RequestBody AOListRequest request) {
// PageHelper.startPage(request.currentPageNum(), request.currentPageSize());
// List<Test> tests = testMapper.selectByPage();
Test tests = testMapper.selectById();
for (int i = 0; i < 10; i++) {
Test test = new Test();
test.setName("测试000");
test.setCreateTime(LocalDateTime.now());
testMapper.insertSelective(test);
}
System.out.println();
return ResponseMessage.ok();
}
}
- 接下来是配置文件中的属性:
spring:
shardingsphere:
props:
# 打印SQL属性为true
sql:
show: true
datasource:
# 定义数据源名称,名称支持自定义
names: master, slave
# 主数据源参数
master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test_1?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root
connectionProperties:
- druid.stat.mergeSql=true
- druid.stat.slowSqlMillis=5000
filters:
- stat
- wall
- slf4j
maxActive: 50
testOnReturn: false
validationQuery: SELECT 1 FROM dual
initial-size: 5
removeAbandoned: true
removeAbandonedTimeout: 180
logAbandoned: true
# 从数据源参数
slave:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test_2?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root
connectionProperties:
- druid.stat.mergeSql=true
- druid.stat.slowSqlMillis=5000
filters:
- stat
- wall
- slf4j
maxActive: 50
testOnReturn: false
validationQuery: SELECT 1 FROM dual
initial-size: 5
removeAbandoned: true
removeAbandonedTimeout: 180
logAbandoned: true
# 读写分离配置,
masterslave:
name: datasource
# 指定主数据库
master-data-source-name: master
# 指定从数据库 多个从数据库用 , 隔开:slave1, slave2
slave-data-source-names: slave
- 启动项目测试:
首先是查询,通过控制台打印的信息可以看出,选用的是Actual SQL: slave ::: select * from test order by create_time 从节点进行查询
[2022-05-31 16:47:53.684][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Logic SQL: select * from test order by create_time
[2022-05-31 16:47:53.685][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: slave ::: select * from test order by create_time
[2022-05-31 16:47:53.686][DEBUG][http-nio-9999-exec-5][com.web.mapper.simple.TestMapper.selectByPage :159] ==> Parameters:
[2022-05-31 16:47:53.691][DEBUG][http-nio-9999-exec-5][com.web.mapper.simple.TestMapper.selectByPage :159] <== Total: 41
下面写入
[2022-05-31 16:50:53.236][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Logic SQL: INSERT INTO test ( id,`name`,create_time ) VALUES( ?,?,? )
[2022-05-31 16:50:53.237][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: master ::: INSERT INTO test ( id,`name`,create_time ) VALUES( ?,?,? )
[2022-05-31 16:50:53.261][DEBUG][http-nio-9999-exec-5][com.web.mapper.simple.TestMapper.insertSelective :159] ==> Parameters: 1531558823779766272(Long), 测试000(String), 2022-05-31 16:50:53.228(Timestamp)
[2022-05-31 16:50:53.266][DEBUG][http-nio-9999-exec-5][com.web.mapper.simple.TestMapper.insertSelective :159] <== Updates: 1
放开for循环的断点可以看出 Actual SQL: master ::: INSERT INTO test ( id,name
,create_time ) VALUES( ?,?,? ) 是用过主节点进行写入的
到此,读写分离演示完毕。
2、分表策略
下面是对分库策略的演示,这里使用的环境是一个数据源:
- 在上面的映射类不改动的环境下,对配置文件的修改:
spring:
shardingsphere:
props:
# 打印SQL
sql:
show: true
# 规则配置
sharding:
tables:
# 逻辑表名称
test:
# 行表达式标识符可以使用 ${...} 或 $->{...},但前者与 Spring 本身的属性文件占位符冲突,因此在 Spring 环境中使用行表达式标识符建议使用 $->{...}
# 表的位置 在那个数据源(数据库),哪个表,表示表的范围,例如: test01,test02...
actual-data-nodes: master.test0$->{1..2}
# 分表策略
table-strategy:
inline:
# 分表列 通过id分表
sharding-column: id
# 分表规则 (id % 2 id求余 0则保存在第一个数据库 1则保存在第二个数据库)
algorithm-expression: test0$->{id % 2}
datasource:
names: master
master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/nucleic_acid_dev?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root
connectionProperties:
- druid.stat.mergeSql=true
- druid.stat.slowSqlMillis=5000
filters:
- stat
- wall
- slf4j
maxActive: 50
testOnReturn: false
validationQuery: SELECT 1 FROM dual
initial-size: 5
removeAbandoned: true
removeAbandonedTimeout: 180
logAbandoned: true
- 下面演示下通过selectById()该方法,测试id=1的数据是不是在test01的库里去查
由打印的sql:Actual SQL: master ::: select * from test01 where id = 1 ,可以看到,只针对test01库执行了一次sql
[2022-05-31 17:52:18.539][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Logic SQL: select * from test where id = 1
[2022-05-31 17:52:18.541][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: master ::: select * from test01 where id = 1
[2022-05-31 17:52:18.561][DEBUG][http-nio-9999-exec-5][com.web.mapper.simple.TestMapper.selectById :159] <== Total: 1
- 会有人问,是不是只去查了test01表,或者说分页怎么做的呢? 下面就演示下分页的情景。
这里先要强调下逻辑:首先它是根据id的规则去查询的test01表。
另外复合表的规则是:如果是查询10条数据,它会从两个库中分别查出符合分页条件的10条数据,然后会对其做个汇总,然后返回十条数据。
那么会有人问了,他通过什么规则返回失调,是平均取test01库中5条,test02中5条还是怎样的?
这就是第二层的问题,它对数据处理的顺序是怎样的。处理的顺序:如果没有指定order by 的字段,那么会优先取出test01的表数据,不足十条的才会去后面的表中拿数据。
我们继续来看演示
未指定order by时的查询,sql语句,数据库原有信息(注意看id为1、2、3、4的时间,后面排序的时候有用):
select * from test
执行结果可以看到只有test01中的十条数据:
[2022-05-31 18:06:31.363][INFO][http-nio-9999-exec-8][ShardingSphere-SQL :74] Logic SQL: select * from test LIMIT ?
[2022-05-31 18:06:31.364][INFO][http-nio-9999-exec-8][ShardingSphere-SQL :74] Actual SQL: master ::: select * from test01 LIMIT ? ::: [10]
[2022-05-31 18:06:31.364][INFO][http-nio-9999-exec-8][ShardingSphere-SQL :74] Actual SQL: master ::: select * from test02 LIMIT ? ::: [10]
- 指定order by 后,可以发现执行的sql是不变的,但是返回的数据却变了:
[2022-05-31 18:11:30.987][INFO][http-nio-9999-exec-4][ShardingSphere-SQL :74] Logic SQL: select * from test order by create_time LIMIT ?
[2022-05-31 18:11:30.987][INFO][http-nio-9999-exec-4][ShardingSphere-SQL :74] Actual SQL: master ::: select * from test01 order by create_time LIMIT ? ::: [10]
[2022-05-31 18:11:30.988][INFO][http-nio-9999-exec-4][ShardingSphere-SQL :74] Actual SQL: master ::: select * from test02 order by create_time LIMIT ? ::: [10]
原理:分页是两个表中都会取复合条件的相同条数的数据,然后再由框架进行处理后返回,并把分页信息给写入
3、分库策略
懒得写了,下面直接写 分库+ 分表
4、分库+分表策略
分库分表理解上会有点复杂,选用一个情景来演示:系统中某个业务表数据比较多,将根据id分为两个库,每个库依据月份来存储数据。
- 环境的配置:我这里用了两个数据源(同一个数据源,复制了一份数据库而已),然后将test01、test02表名修改成test_2022_1、test_2022_2,由于两份库中的两表完全一致,我只对其中一个库做展示。
- 代码调整:已有代码不做改动,对配置文件进行调整已经加入自定义分表策略方法处理类
配置文件:
spring:
shardingsphere:
props:
# 打印SQL
sql:
show: true
# 规则配置
sharding:
tables:
# 逻辑表名称
test:
# 行表达式标识符可以使用 ${...} 或 $->{...},但前者与 Spring 本身的属性文件占位符冲突,因此在 Spring 环境中使用行表达式标识符建议使用 $->{...}
actual-data-nodes: m$->{0..1}.test_2022_$->{1..2}
# 分库策略
database-strategy:
# 行内算法
inline:
# 分库列 (id % 2 id求余 0则保存在第一个数据库 1则保存在第二个数据库)
shardingColumn: id
algorithmExpression: m$->{id % 2}
# 分表策略
table-strategy:
standard:
# 分表列 通过create_time分表,按照数据的时间,每个月份存储一张表
sharding-column: create_time
# 分表规则 自定义分表策略方法
precise-algorithm-class-name: com.web.controller.admin.sys.TableShardingAlgorithm
datasource:
names: m1,m0
m1: # 数据源1
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root
connectionProperties:
- druid.stat.mergeSql=true
- druid.stat.slowSqlMillis=5000
filters:
- stat
- wall
- slf4j
maxActive: 50
testOnReturn: false
validationQuery: SELECT 1 FROM dual
initial-size: 5
removeAbandoned: true
removeAbandonedTimeout: 180
logAbandoned: true
m0: # 数据源2
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root
connectionProperties:
- druid.stat.mergeSql=true
- druid.stat.slowSqlMillis=5000
filters:
- stat
- wall
- slf4j
maxActive: 50
testOnReturn: false
validationQuery: SELECT 1 FROM dual
initial-size: 5
removeAbandoned: true
removeAbandonedTimeout: 180
logAbandoned: true
自定义分表策略方法
@Slf4j
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
// availableTargetNames,数据库的id规则选出哪个库后,库中表的集合 test_2022_$->{1..2}
System.out.println(StrUtil.format("actual node tables:{}", availableTargetNames));
// logicTableName: 逻辑表名 test,分表规则的字段 create_time
System.out.println(StrUtil.format("logic table name:{},rout column:{}", shardingValue.getLogicTableName(), shardingValue.getColumnName()));
String tbName = shardingValue.getLogicTableName() + "_";
// 根据插入对象的日期来分表
Date date = shardingValue.getValue();
String year = String.format("%tY", date);
// 去掉前缀0
String mon = String.valueOf(Integer.parseInt(String.format("%tm", date)));
// 选择表
tbName = tbName + year + "_" + mon;
System.out.println("tbName:" + tbName);
for (String each : availableTargetNames) {
// 匹配到后即返回
if (each.equals(tbName)) {
return each;
}
}
throw new IllegalArgumentException();
}
}
测试controller代码:
public ResponseMessage list(@RequestBody AOOrganListRequest request) {
PageHelper.startPage(request.currentPageNum(), request.currentPageSize());
List<Test> tests = testMapper.selectByPage();
// Test tests = testMapper.selectById();
for (int i = 0; i < 10; i++) {
Test test = new Test();
test.setName("测试000");
test.setCreateTime(LocalDateTime.now().withMonth(1));
testMapper.insertSelective(test);
}
System.out.println();
return ResponseMessage.ok();
}
- 进行测试演示:
先进行分页查询,从打印日志中可以看出每个库每个表都会查询十条记录,然后通过框架内部进行处理返回排序后的十条。
[2022-06-01 13:55:56.083][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m0 ::: select id, name, create_time createTime from test_2022_1 order by create_time LIMIT ? ::: [10]
[2022-06-01 13:55:56.083][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m0 ::: select id, name, create_time createTime from test_2022_2 order by create_time LIMIT ? ::: [10]
[2022-06-01 13:55:56.083][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m1 ::: select id, name, create_time createTime from test_2022_1 order by create_time LIMIT ? ::: [10]
[2022-06-01 13:55:56.084][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m1 ::: select id, name, create_time createTime from test_2022_2 order by create_time LIMIT ? ::: [10]
[2022-06-01 13:55:56.095][DEBUG][http-nio-9999-exec-5]
插入演示:debug进自定义分表逻辑方法中,shardingValue对象有三个属性:逻辑表名称、分表字段、属性值,availableTargetNames对象是一个集合,通过数据库的id规则选出哪个库后,库中表的集合这里是: test_2022_$->{1…2},后面通过代码中进行逻辑的处理后,返回响应的表名称即可。
结果:
m1 库根据id的规则计算后表中数据一定是基数:
m0 库根据id的规则计算后表中数据一定是偶数:
id精确查询,通过selectById()方法来查询,看看是怎么执行sql的:可以看到仅仅在符合id为基数的m1数据源中查询,查询两个库哪个中有id=1的数据
[2022-06-01 14:28:11.933][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Logic SQL: select * from test where id = 1
[2022-06-01 14:28:11.934][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m1 ::: select * from test_2022_1 where id = 1
[2022-06-01 14:28:11.934][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m1 ::: select * from test_2022_2 where id = 1
[2022-06-01 14:28:11.937][DEBUG][http-nio-9999-exec-5][com.nucleic.acid.web.mapper.simple.TestMapper.selectById :159] <== Total: 1
总结
从上面几个例子可以看出shardingsphere在读写分离上无代码侵入,还是比较好用的,缺点是两个数据源一定是主从复制,且同步数据的时效性比较重要,例如:账号修改密码功能是在主节点进行写入,从节点同步不够及时,登录时就会校验失败。
在分库分表上会有少量代码入侵,两者都需要配置文件进行配置,也符合springboot的理念:约定大于配置