背景:
主从复制,主库负责增删改数据,从库负责 查数据,这样可以提高并发查询能力
官方链接:读写分离 :: ShardingSphere (apache.org)
实践:
前提:数据库配置了主从,(不配置也不耽误下面,就是会导致数据不一致)
①依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--sharding jdbc springboot-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</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>
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
</dependencies>
②配置文件 application.properties
#定义了 一个主数据库叫master,两个从数据库分别叫slave0,slave1(可以只有一个主 一个从,也可以一主多从)
spring.shardingsphere.datasource.names=master,slave0,slave1
#master的配置
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://192.168.11.131:3306/ds_02?zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&useUnicode=true
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=patpat
#slave0的配置
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://192.168.11.132:3306/ds_02?zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&useUnicode=true
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=patpat
#slave1的配置
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://192.168.11.132:3306/ds_02_copy?zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&useUnicode=true
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=patpat
#指定 查询算法,是轮询,即:每次查询 slave1、slave2轮流使用
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ms
#指定 哪个数据源才是真的 主库
spring.shardingsphere.masterslave.master-data-source-name=master
#指定 哪些数据源才是真正的 从库
spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1
③ 代码整体结构
表结构:
CREATE TABLE `order` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
`user_id` bigint(11) NOT NULL COMMENT '用户ID',
`address` varchar(32) NOT NULL COMMENT '收货地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
Order实体:
@Data
@TableName("`order`")
public class Order implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
private Long userId;
private String address;
}
mapper接口:
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}
service层
service接口:
public interface IOrderService extends IService<Order> {
}
service实现:
@Service
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements IOrderService {
}
mapper的xml文件
<?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="com.example.demo.mapper.OrderMapper">
</mapper>
启动类:
@SpringBootApplication
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
代码测试使用:
import com.example.demo.entity.Order;
import com.example.demo.mapper.OrderMapper;
import com.example.demo.service.IOrderService;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
class DemoApplicationTests {
// @Autowired
// private OrderMapper orderMapper;
@Autowired
private IOrderService orderService;
@Test
void fun() {
Order order = new Order();
order.setUserId(1L);
order.setAddress("insert_test");
// orderMapper.insert(order); 与orderService.save(order); 等价
orderService.save(order); //master中有6条数据
List<Order> listSlave1 = orderService.list();
System.out.println(listSlave1); //[Order(id=1, userId=1, address=order_132), Order(id=2, userId=2, address=order_132)]
List<Order> listSlave2 = orderService.list();
System.out.println(listSlave2); //[Order(id=1, userId=1, address=order_132_copy), Order(id=2, userId=2, address=order_132_copy)]
}
}
数据库数据对比:
结论:插入数据时,自动去 master库中插入,查询数据时,轮询查询slave0、slave1
代码: qaz1305820192/demo: 纯粹主从复制 (github.com)https://github.com/qaz1305820192/demo/tree/master