ShardingSphere 5.1.1 读写分离实战 | sharding-jdbc | 详细步骤
读写分离
- 项目源码
- ShardingSphere 5.1.1 读写分离实战 | sharding-jdbc | 详细步骤
- mysql 垂直分表实战 | sharding-jdbc | 详细步骤
- mysql 垂直分库 | sharding-jdbc 实战详细步骤
- mysql 水平分表（不分库）| shardingsphere 实战详细步骤
- mysql 水平分表（分库）| sharding-jdbc实战详细步骤
- 多表关联 shardingsphere 实战详细步骤
- 广播表 shardingsphere实战详细步骤
1.1创建表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`age` int NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
1.2创建springboot项目
添加maven依赖
<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.5</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>readwrite</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>readwrite</name>
<description>readwrite</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.1</version>
</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>3.3.1</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>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
1.3配置ShardingSphere
1.3.1 配置运行模式
内存模式
application.properties
# 运行模式
spring.shardingsphere.mode.type=Memory
1.3.2 配置数据源
# 配置真实数据源
spring.shardingsphere.datasource.names=master,slave1,slave2
# 配置第 1 个数据源
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://mysql3:3306/mytest
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=密码
# 配置第 2 个数据源
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://mysql4:3306/mytest
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=密码
# 配置第 3 个数据源
spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://mysql5:3306/mytest
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=密码
1.3.3 静态读写分离配置
# 读写分离
# 读写分离类型，如: Static，Dynamic
spring.shardingsphere.rules.readwrite-splitting.data-sources.rw_ds.type=Static
# 写库数据源名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.rw_ds.props.write-data-source-name=master
# 读库数据源列表，多个从数据源用逗号分隔
spring.shardingsphere.rules.readwrite-splitting.data-sources.rw_ds.props.read-data-source-names=slave1,slave2
# 负载均衡算法名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.rw_ds.load-balancer-name=my_load_balancer
# 负载均衡算法配置
# 负载均衡算法类型
spring.shardingsphere.rules.readwrite-splitting.load-balancers.my_load_balancer.type=ROUND_ROBIN
1.3.4 打印sql日志
# 打印SQl
spring.shardingsphere.props.sql-show=true
1.4 代码
1.4.1 创建实体类
@TableName("student")
@Data
public class Student {
@TableId(type = IdType.*AUTO*)
private int id;
private String name;
private int age;
}
1.4.2 创建mapper
public interface StudentMapper extends BaseMapper<Student> {
}
1.4.3 扫描mapper
@SpringBootApplication
@MapperScan("com.example.readwrite.mapper")
public class ReadwriteApplication {
public static void main(String[] args) {
SpringApplication.*run*(ReadwriteApplication.class, args);
}
}
1.5测试
1.5.1 测试插入
@SpringBootTest
public class StudentMapperTest {
@Autowired
private StudentMapper studentMapper;
@Test
public void testInert() {
Student stu = new Student();
stu.setName("zhaoliu");
stu.setAge(23);
studentMapper.insert(stu);
Student student = studentMapper.selectById(stu.getId());
Assertions.*assertEquals*(student, stu);
}
}
1.5.2 测试事务
@SpringBootTest
public class StudentMapperTest {
@Autowired
private StudentMapper studentMapper;
@Test
@Transactional
public void testTransaction() {
Student stu = new Student();
stu.setName("tianyi");
stu.setAge(23);
studentMapper.insert(stu);
Student student = studentMapper.selectById(stu.getId());
Assertions.*assertEquals*(student, stu);
}
}
输出日志
2022-11-20 12:34:15.886 INFO 9140 --- [ main] o.s.t.c.transaction.TransactionContext : Began transaction (1) for test context
…
2022-11-20 12:34:18.351 INFO 9140 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO student ( name,
age ) VALUES ( ?,
? )
2022-11-20 12:34:18.352 INFO 9140 --- [ main] ShardingSphere-SQL : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2022-11-20 12:34:18.353 INFO 9140 --- [ main] ShardingSphere-SQL : Actual SQL: master ::: INSERT INTO student ( name,
age ) VALUES ( ?,
? ) ::: [tianyi, 23]
2022-11-20 12:34:18.702 INFO 9140 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,name,age FROM student WHERE id=?
2022-11-20 12:34:18.702 INFO 9140 --- [ main] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-11-20 12:34:18.702 INFO 9140 --- [ main] ShardingSphere-SQL : Actual SQL: master ::: SELECT id,name,age FROM student WHERE id=? ::: [7]
2022-11-20 12:34:18.871 INFO 9140 --- [ main] o.s.t.c.transaction.TransactionContext : Rolled back transaction for test:
在事务中，读写都是操作master数据源
@Transactional在Junit环境下会自动回滚，不需要额外添加@Rollback注解
1.5.3 测试负载均衡
@Test
public void testLoadBalance() {
Student student2 = studentMapper.selectById(4);
*log*.info(String.*valueOf*(student2));
Student student3 = studentMapper.selectById(5);
*log*.info(String.*valueOf*(student3));
Student student = studentMapper.selectById(2);
*log*.info(String.*valueOf*(student));
Student student1 = studentMapper.selectById(3);
*log*.info(String.*valueOf*(student1));
}
2022-11-20 13:01:02.712 INFO 3812 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,name,age FROM student WHERE id=?
2022-11-20 13:01:02.712 INFO 3812 --- [ main] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-11-20 13:01:02.712 INFO 3812 --- [ main] ShardingSphere-SQL : Actual SQL: **slave1** ::: SELECT id,name,age FROM student WHERE id=? ::: [4]
2022-11-20 13:01:02.791 INFO 3812 --- [ main] com.example.readwrite.StudentMapperTest : Student(id=4, name=chenglong, age=22)
2022-11-20 13:01:02.792 INFO 3812 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,name,age FROM student WHERE id=?
2022-11-20 13:01:02.792 INFO 3812 --- [ main] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-11-20 13:01:02.792 INFO 3812 --- [ main] ShardingSphere-SQL : Actual SQL: **slave2** ::: SELECT id,name,age FROM student WHERE id=? ::: [5]
2022-11-20 13:01:02.816 INFO 3812 --- [ main] com.example.readwrite.StudentMapperTest : Student(id=5, name=六七, age=23)
2022-11-20 13:01:02.817 INFO 3812 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,name,age FROM student WHERE id=?
2022-11-20 13:01:02.817 INFO 3812 --- [ main] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-11-20 13:01:02.817 INFO 3812 --- [ main] ShardingSphere-SQL : Actual SQL: **slave1** ::: SELECT id,name,age FROM student WHERE id=? ::: [2]
2022-11-20 13:01:02.820 INFO 3812 --- [ main] com.example.readwrite.StudentMapperTest : Student(id=2, name=lisi, age=20)
2022-11-20 13:01:02.821 INFO 3812 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,name,age FROM student WHERE id=?
2022-11-20 13:01:02.821 INFO 3812 --- [ main] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-11-20 13:01:02.822 INFO 3812 --- [ main] ShardingSphere-SQL : Actual SQL: **slave2** ::: SELECT id,name,age FROM student WHERE id=? ::: [3]
2022-11-20 13:01:02.825 INFO 3812 --- [ main] com.example.readwrite.StudentMapperTest : Student(id=3, name=wangwu, age=21)