Springboot 集成 sharding sphere,mybatis-plus 实现分库分表
【摘要】 创建spring boot 项目
1.添加依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <...
创建spring boot 项目
1.添加依赖
-
<dependency>
-
<groupId>org.springframework.boot</groupId>
-
<artifactId>spring-boot-starter-web</artifactId>
-
</dependency>
-
-
<dependency>
-
<groupId>mysql</groupId>
-
<artifactId>mysql-connector-java</artifactId>
-
<scope>runtime</scope>
-
</dependency>
-
<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-generator</artifactId>
-
<version>${mybatis-plus.version}</version>
-
</dependency>
-
<dependency>
-
<groupId>org.projectlombok</groupId>
-
<artifactId>lombok</artifactId>
-
<optional>true</optional>
-
</dependency>
-
<dependency>
-
<groupId>org.springframework.boot</groupId>
-
<artifactId>spring-boot-starter-test</artifactId>
-
<scope>test</scope>
-
</dependency>
-
-
<dependency>
-
<groupId>org.apache.shardingsphere</groupId>
-
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
-
<version>4.0.0-RC1</version>
-
</dependency>
-
-
<dependency>
-
<groupId>org.apache.shardingsphere</groupId>
-
<artifactId>sharding-jdbc-spring-namespace</artifactId>
-
<version>4.0.0-RC1</version>
-
</dependency>
2.编写 application.yaml 配置文件
-
server:
-
port: 8001
-
-
spring:
-
profiles:
-
active: master-slave
-
application:
-
name: exam-manage
-
main:
-
allow-bean-definition-overriding: true
-
-
#mybatis配置
-
mybatis-plus:
-
mapperLocations: classpath*:mapper/*.xml
-
typeAliasesPackage: com.dzx.ccb.exam.exammanage.entity
-
configuration:
-
map-underscore-to-camel-case: true
-
cache-enabled: true
-
-
logging:
-
level:
-
com.dzx.ccb.exam.exammanage.mapper: DEBUG
3. 编写测试接口
-
package com.dzx.ccb.exam.exammanage.controller;
-
-
import com.baomidou.mybatisplus.core.metadata.IPage;
-
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
-
import com.dzx.ccb.exam.exammanage.entity.Student;
-
import com.dzx.ccb.exam.exammanage.service.StudentService;
-
import com.dzx.ccb.exam.exammanage.util.JsonReponse;
-
import groovy.transform.AutoClone;
-
import org.springframework.beans.factory.annotation.Autowired;
-
import org.springframework.validation.annotation.Validated;
-
import org.springframework.web.bind.annotation.*;
-
-
/**
-
* @author DuanZhaoXu
-
* @ClassName:
-
* @Description:
-
* @date 2021年05月21日 12:52:20
-
*/
-
@RestController
-
@RequestMapping("/student")
-
public class StudentController {
-
-
@Autowired
-
private StudentService studentService;
-
-
@PostMapping("/add")
-
public JsonReponse<Boolean> addStudent(@RequestBody Student student) {
-
boolean result = studentService.save(student);
-
return JsonReponse.success(result);
-
}
-
-
@GetMapping("/getById")
-
public JsonReponse<Student> addStudent(@RequestParam("id") Long id) {
-
return JsonReponse.success(studentService.getById(id));
-
}
-
-
@GetMapping("/list")
-
public JsonReponse<Student> list() {
-
return JsonReponse.success(studentService.list());
-
}
-
-
@GetMapping("pageList")
-
public JsonReponse<Student> pageList(@RequestParam("current") long current, @RequestParam("size") long size) {
-
Page<Student> iPage = new Page<Student>(current, size);
-
iPage.setDesc("id");
-
return JsonReponse.success(studentService.page(iPage));
-
}
-
-
}
4. 简单 分库配置文件 ：application-sharding-databases.yaml 配置文件
ds0和ds1两个数据库下都创建student表
-
#分库配置
-
spring:
-
shardingsphere:
-
datasource:
-
names: ds0,ds1
-
ds0:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/ds0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
ds1:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
sharding:
-
default-database-strategy:
-
inline:
-
sharding-column: id
-
algorithm-expression: ds$->{id % 2}
-
tables:
-
student:
-
actual-data-nodes: ds$->{0..1}.student
-
key-generator:
-
column: id
-
type: SNOWFLAKE
-
props:
-
worker:
-
id: 33
-
props:
-
sql:
-
show: true
-
-
5. 简单分表配置： application-sharding-tables.yaml
ds2数据库中创建 student0 和 student1 表
-
#不分库，只分表
-
spring:
-
shardingsphere:
-
datasource:
-
names: ds2
-
ds2:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/ds2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
sharding:
-
tables:
-
student:
-
actual-data-nodes: ds2.student$->{0..1}
-
table-strategy:
-
inline:
-
sharding-column: id
-
algorithm-expression: student$->{id % 2}
-
key-generator:
-
column: id
-
type: SNOWFLAKE
-
props:
-
worker:
-
id: 33
-
props:
-
sql:
-
show: true
-
-
6.分库 + 分表配置 ： application-sharding-databases-tables.yaml
ds0 , ds1 ,ds2 三个数据库中 分别创建 student0 和 student1 表
-
# 同时分库，分表
-
spring:
-
shardingsphere:
-
datasource:
-
names: ds0,ds1,ds2
-
ds0:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/ds0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
ds1:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
ds2:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/ds2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
sharding:
-
default-database-strategy:
-
inline:
-
sharding-column: id
-
algorithm-expression: ds$->{id % 3}
-
tables:
-
student:
-
actual-data-nodes: ds$->{0..2}.student$->{0..1}
-
table-strategy:
-
inline:
-
sharding-column: id
-
algorithm-expression: student$->{id % 2}
-
key-generator:
-
column: id
-
type: SNOWFLAKE
-
props:
-
worker:
-
id: 33
-
props:
-
sql:
-
show: true
-
-
7. 简单主从数据库配置 ：application-master-slave.yaml
ds0为 主库，ds1 和 ds2 为从库，数据库架构配置了主从读写分离的情况下
-
#主从数据库配置
-
spring:
-
shardingsphere:
-
datasource:
-
names: ds0,ds1,ds2
-
ds0:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/ds0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
ds1:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
ds2:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/ds2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
masterslave:
-
load-balance-algorithm-type: random
-
name: student_ms
-
master-data-source-name: ds0
-
slave-data-source-names: ds1,ds2
-
props:
-
sql:
-
show: true
-
-
8. 主从数据库+ 分库分表配置：application-sharding-master-slave.yaml
两主 + 四从
主库 dsmaster0 -> 从库 dsmaster0-slave0, dsmaster0-slave1
主库 dsmaster1 -> 从库 dsmaster1-slave0, dsmaster1-slave1
-
spring:
-
shardingsphere:
-
datasource:
-
names: dsmaster0,dsmaster1,dsmaster0-slave0,dsmaster0-slave1,dsmaster1-slave0,dsmaster1-slave1
-
dsmaster0:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/dsmaster0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
dsmaster0-slave0:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/dsmaster0-slave0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
dsmaster0-slave1:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/dsmaster0-slave1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
dsmaster1:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/dsmaster1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
dsmaster1-slave0:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/dsmaster1-slave0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
dsmaster1-slave1:
-
type: com.zaxxer.hikari.HikariDataSource
-
driverClassName: com.mysql.cj.jdbc.Driver
-
jdbc-url: jdbc:mysql://127.0.0.1:3306/dsmaster1-slave1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
username: root
-
password: 123456
-
sharding:
-
default-database-strategy:
-
inline:
-
sharding-column: id
-
algorithm-expression: dsmaster$->{id % 2}
-
tables:
-
student:
-
actual-data-nodes: dsmaster$->{0..1}.student$->{0..1}
-
table-strategy:
-
inline:
-
sharding-column: id
-
algorithm-expression: student$->{id % 2}
-
key-generator:
-
column: id
-
type: SNOWFLAKE
-
props:
-
worker:
-
id: 33
-
master-slave-rules:
-
dsmaster0:
-
master-data-source-name: dsmaster0
-
slave-data-source-names: dsmaster0-slave0, dsmaster0-slave1
-
dsmaster1:
-
master-data-source-name: dsmaster1
-
slave-data-source-names: dsmaster1-slave0, dsmaster1-slave1
另外可以使用绑定表和广播表配置项进行性能调优
spring.shardingsphere.sharding.binding-tables
spring.shardingsphere.sharding.broadcast-tables
设置绑定表和广播表
接下来我们需要设置绑定表。绑定表（BindingTable）是 ShardingSphere 中提出的一个新概念，我来给你解释一下。
所谓绑定表，是指与分片规则一致的一组主表和子表。例如，在我们的业务场景中，health_record 表和 health_task 表中都存在一个 record_id 字段。如果我们在应用过程中按照这个 record_id 字段进行分片，那么这两张表就可以构成互为绑定表关系。
引入绑定表概念的根本原因在于，互为绑定表关系的多表关联查询不会出现笛卡尔积，因此关联查询效率将大大提升。举例说明，如果所执行的为下面这条 SQL：
SELECT record.remark_name FROM health_record record JOIN health_task task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
如果我们不显式配置绑定表关系，假设分片键 record_id 将值 1 路由至第 1 片，将数值 2 路由至第 0 片，那么路由后的 SQL 应该为 4 条，它们呈现为笛卡尔积：
-
SELECT record.remark_name FROM health_record0 record JOIN health_task0 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
-
-
SELECT record.remark_name FROM health_record0 record JOIN health_task1 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
-
-
SELECT record.remark_name FROM health_record1 record JOIN health_task0 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
-
-
SELECT record.remark_name FROM health_record1 record JOIN health_task1 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
然后，在配置绑定表关系后，路由的 SQL 就会减少到 2 条：
-
SELECT record.remark_name FROM health_record0 record JOIN health_task0 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
-
-
SELECT record.remark_name FROM health_record1 record JOIN health_task1 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
请注意，如果想要达到这种效果，互为绑定表的各个表的分片键要完全相同。在上面的这些 SQL 语句中，我们不难看出，这个需要完全相同的分片键就是 record_id。
让我们回到案例中的场景，显然，health_record 和 health_task 应该互为绑定表关系。所以，我们可以在配置文件中添加对这种关系的配置：
spring.shardingsphere.sharding.binding-tables=health_record, health_task
介绍完绑定表，再来看广播表的概念。所谓广播表（BroadCastTable），是指所有分片数据源中都存在的表，也就是说，这种表的表结构和表中的数据在每个数据库中都是完全一样的。广播表的适用场景比较明确，通常针对数据量不大且需要与海量数据表进行关联查询的应用场景，典型的例子就是每个分片数据库中都应该存在的字典表。
同样回到我们的场景，对于 health_level 表而言，由于它保存着有限的健康等级信息，可以认为它就是这样的一种字典表。所以，我们也在配置文件中添加了对广播表的定义，在下面这段代码中你可以看到：
spring.shardingsphere.sharding.broadcast-tables=health_level
文章来源: blog.csdn.net，作者：血煞风雨城2018，版权归原作者所有，如需转载，请联系作者。
原文链接：blog.csdn.net/qq_31905135/article/details/117127914
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）