1. 搭建环境
根据自己的需要,建立一个新的项目,测试shardingsphere 5的分表功能,单个库分表。
2. 配置pom文件
<!-- Mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- alibaba的druid数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<!-- 分库分表 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-extension</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
<!-- mybatis-plus.version -->
<mybatis-plus.version>3.3.2</mybatis-plus.version>
<!-- 雪花id -->
<snowflake.version>1.0.0</snowflake.version>
<!-- 数据源 -->
<alibaba.druid.version>1.2.6</alibaba.druid.version>
<!-- 分表 -->
<shardingsphere.version>5.0.0</shardingsphere.version>
3. 分表核心配置
id的值决定数据库,user_id的值决定哪一张表
id=偶数,选择数据库:shardingsphere_slave_first,user_id=偶数:选择表:demo_0
id=偶数,选择数据库:shardingsphere_slave_first,user_id=奇数:选择表:demo_1
id=奇数,选择数据库:shardingsphere_slave_second,user_id=偶数:选择表:demo_0
id=奇数,选择数据库:shardingsphere_slave_second,user_id=奇数:选择表:demo_1
spring:
shardingsphere:
# 数据源配置
datasource:
# 数据源名称,多数据源以逗号分隔
names: db0,db1
db0:
# 数据库驱动类名
driver-class-name: com.mysql.jdbc.Driver
# 数据库连接池类名称
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/shardingsphere_slave_first?userUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
username: root
password: ******
db1:
# 数据库驱动类名
driver-class-name: com.mysql.jdbc.Driver
# 数据库连接池类名称
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/shardingsphere_slave_second?userUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
username: root
password: ******
# 规则配置
rules:
sharding:
# 分片算法配置
sharding-algorithms:
# 分表算法名称
database-inline:
# 分片算法类型 https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/sharding/
type: INLINE
props:
# 分片算法的行表达式
algorithm-expression: db$->{ id % 2 }
# 分表算法名称
table-inline:
type: INLINE
props:
algorithm-expression: demo_$->{ user_id % 2 }
# 分布式序列算法配置
key-generators:
snowflake:
# 分布式序列算法(雪花算法:SNOWFLAKE; UUID:UUID)(注:UUID没有props配置) https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/keygen/
type: SNOWFLAKE
# 分布式序列算法属性配置
props:
# 工作机器唯一标识
worker-id: 1
tables:
# 逻辑表名称
demo:
# 行表达式标识符可以使用 ${...} 或 $->{...},但前者与 Spring 本身的属性文件占位符冲突,因此在 Spring 环境中使用行表达式标识符建议使用 $->{...}
actual-data-nodes: db$->{0..1}.demo_$->{0..1}
# 分表策略
database-strategy:
standard:
# 分片列名称
sharding-column: id
# 分片算法名称
sharding-algorithm-name: database-inline
table-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: table-inline
# 属性配置
props:
# 展示修改以后的sql语句
sql-show: true
4. 编写model层
public class Demo {
public Long id;
public Long userId;
public String username;
public String password;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
}
5.编写mapper层
@Repository
public interface DemoMapper extends BaseMapper<Demo> {
}
6.编程service层
@Service
@Transactional(rollbackFor = {Exception.class})
public class DemoServiceImpl extends ServiceImpl<DemoMapper, Demo> implements IDemoService {
@Override
public boolean batchInsert(List<Demo> demoDOList) {
return saveBatch(demoDOList);
}
@Override
public boolean insert(Demo demoDO) {
return save(demoDO);
}
@Override
public List<Demo> listDemos() {
return list();
}
}
7. 编写controller层
@RestController
@Api(tags = "分库分表测试")
@RequestMapping("shadings")
public class DemoController {
@Autowired
private IDemoService demoService;
@ApiOperation("保存")
@PostMapping("add")
public String saveDemo(@RequestBody Demo demo) {
boolean save = demoService.save(demo);
return "保存成功" + save;
}
@ApiOperation("查询所有")
@GetMapping("list")
public List<Demo> listDemo() {
return demoService.listDemos();
}
}
8. 实测
- [1] 启动项目,两个数据库各自有两张表,数据为空:
- [2] 添加数据
由于我们的分表键:
id=10,选择数据库:shardingsphere_slave_first,user_id=10:选择表:demo_0
id=12,选择数据库:shardingsphere_slave_first,user_id=11:选择表:demo_1
id=13,选择数据库:shardingsphere_slave_second,user_id=14:选择表:demo_0
id=105,选择数据库:shardingsphere_slave_second,user_id=17:选择表:demo_1
9. 总结
这里只是一个简单的测试、分片算法有很多种,这里只测了其中一种,你实际运用后你会发现,shardingsphere 5的配置很灵活,对代码的侵入性很小,代码很普通代码没有太大区别,同一个对象,你可以根据数据量、并发、实际业务的不同。来调整自己的分片策略。
而且shardingsphere是支持:读写分离、分表、分库以及分库分表等组合的。
分片算法类型:分片算法类型-见官网