Sharding-JDBC搭建MySQL读写分离

原创

海风极客 2022-05-24 19:29:34 博主文章分类：项目实战经验 ©著作权

文章标签 mysql java go 读写分离高可用 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者海风极客的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

1 读写分离概述

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

Sharding-JDBC搭建MySQL读写分离_mysql

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

2.1 项目结构和表结构

Sharding-JDBC搭建MySQL读写分离_go_02

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));
    }
}