Sharding-JDBC搭建MySQL读写分离

官网：https://shardingsphere.apache.org/document/5.0.0/cn

1 读写分离概述

读写分离，顾名思义就是将Java应用对数据库的读和写操作进行区分执行，可以是在不同的数据节点也可能是在不同的数据库，这是在业务压力较大的情况下对数据库能够更好的承受压力的常用做法。

2 Spring Boot整合Sharding-JDBC实现读写分离

2.1 项目结构和表结构

CREATE TABLE `student` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=781 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT;

2.2 依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- sharding-jdbc-->
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>3.0.0.M3</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.9</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.1.2</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

2.3 配置文件

#允许将bean进行重写，如果没有该配置则可能会出现类似于bean冲突的异常
spring.main.allow-bean-definition-overriding=true
#主从数据库命名
sharding.jdbc.datasource.names=db-master-1,db-slave-1
#主数据源-写操作
sharding.jdbc.datasource.db-master-1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.db-master-1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.db-master-1.url=jdbc:mysql://127.0.0.1:3306/test
sharding.jdbc.datasource.db-master-1.username=root
sharding.jdbc.datasource.db-master-1.password=12345
#从数据源-读操作
sharding.jdbc.datasource.db-slave-1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.db-slave-1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.db-slave-1.url=jdbc:mysql://127.0.0.1:3307/test
sharding.jdbc.datasource.db-slave-1.username=root
sharding.jdbc.datasource.db-slave-1.password=12345
# 读写分离配置 算法：round_robin（轮询）和random（随机）
sharding.jdbc.config.masterslave.load-balance-algorithm-type=round_robin
sharding.jdbc.config.masterslave.name=dataSource
sharding.jdbc.config.masterslave.master-data-source-name=db-master-1
sharding.jdbc.config.masterslave.slave-data-source-names=db-slave-1

2.4 代码

StudentMapper.java

/**
 * @desc: Mapper层接口
 * @author: YanMingXin
 * @create: 2021/11/27-17:03
 **/
@Mapper
public interface StudentMapper extends BaseMapper<Student> {
}

Student.java

/**
 * @desc: 实体类
 * @author: YanMingXin
 * @create: 2021/11/27-17:01
 **/
@AllArgsConstructor
@NoArgsConstructor
@Data
@Accessors(chain = true)
public class Student {

    private Integer id;

    private String name;

    private Integer age;

    private String address;

}

StudentService.java

/**
 * @desc: 接口
 * @author: YanMingXin
 * @create: 2021/11/27-17:03
 **/
public interface StudentService {

    void saveStudent(Student student);

    Student findStudentById(Integer id);

    List<Student> findAllStudent();

}

StudentServiceImpl.java

/**
 * @desc: 接口实现类
 * @author: YanMingXin
 * @create: 2021/11/27-17:03
 **/
@Service
public class StudentServiceImpl implements StudentService {

    @Resource
    private StudentMapper studentMapper;

    @Override
    public void saveStudent(Student student) {
        studentMapper.insert(student);
    }

    @Override
    public Student findStudentById(Integer id) {
        return studentMapper.selectById(id);
    }

    @Override
    public List<Student> findAllStudent() {
        return studentMapper.selectList(null);
    }
}

SpShardingjdbcApplicationTests.java

@SpringBootTest
class SpShardingjdbcApplicationTests {

    @Autowired
    private StudentService studentService;

    @Test
    void contextLoads() {
        studentService.saveStudent(new Student(1,"zs",12,"bj"));
    }

    @Test
    void selectStudent(){
        System.out.println(studentService.findStudentById(1));
        System.out.println(studentService.findStudentById(2));
    }
}

2.5 测试结果

先插入一条数据，然后手动将从库插入一条数据（因为本次实验的主从没有进行主从复制，也为了能演示出读写分离的效果）

主库：

从库：

进行查询：

3 读写分离原理

Sharding-jdbc实现Java对MySQL的读写分离核心点是代理，就是将应用对MySQL数据库的操作转化为对Sharding-jdbc的操作，再由Sharding-JDBC进行规则的定义和SQL操作的转发，按照开发人员自定义的规则进行SQL操作的解析、改写、路由和执行。

欢迎关注公众号