java_读写分离_shardingsphere

背景：

主从复制，主库负责增删改数据，从库负责 查数据，这样可以提高并发查询能力

官方链接：读写分离 :: 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