数据库读写分离

官方文档

1. ShardingSphere

1.1 ShardingSphere简介

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。

ShardingSphere定位为关系型数据库中间件，旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。 它与NoSQL和NewSQL是并存而非互斥的关系。NoSQL和NewSQL作为新技术探索的前沿，放眼未来，拥抱变化，是非常值得推荐的。反之，也可以用另一种思路看待问题，放眼未来，关注不变的东西，进而抓住事物本质。 关系型数据库当今依然占有巨大市场，是各个公司核心业务的基石，未来也难于撼动，我们目前阶段更加关注在原有基础上的增量，而非颠覆。

ShardingSphere目前已经进入Apache孵化器， 欢迎通过shardingsphere的dev邮件列表与我们讨论。

1.2 Sharding-JDBC简介

定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

• 适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
• 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
• 支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

2. 读写分离简介

2.1 背景

面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。

通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理能力。 使用多主多从的方式，不但能够提升系统的吞吐量，还能够提升系统的可用性，可以达到在任何一个数据库宕机，甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。

与将数据根据分片键打散至各个数据节点的水平分片不同，读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库。

读写分离的数据节点中的数据内容是一致的，而水平分片的每个数据节点的数据内容却并不相同。将水平分片和读写分离联合使用，能够更加有效的提升系统性能。

2.2 挑战

读写分离虽然可以提升系统的吞吐量和可用性，但同时也带来了数据不一致的问题。 这包括多个主库之间的数据一致性，以及主库与从库之间的数据一致性的问题。 并且，读写分离也带来了与数据分片同样的问题，它同样会使得应用开发和运维人员对数据库的操作和运维变得更加复杂。 下图展现了将分库分表与读写分离一同使用时，应用程序与数据库集群之间的复杂拓扑关系。

2.3 目标

透明化读写分离所带来的影响，让使用方尽量像使用一个数据库一样使用主从数据库集群，是ShardingSphere读写分离模块的主要设计目标。

2.4 核心概念

2.4.1 主库

添加、更新以及删除数据操作所使用的数据库，目前仅支持单主库

2.4.2 从库

查询数据操作所使用的数据库，可支持多从库。

2.4.3 主从同步

将主库的数据异步的同步到从库的操作。由于主从同步的异步性，从库与主库的数据会短时间内不一致。（这一步由数据库层实现，一般RDS数据库提供商，都会提供读写分离的服务）

2.4.4 负载均衡策略

通过负载均衡策略将查询请求疏导至不同从库。

2.5 核心功能

1. 提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用。
2. 独立使用读写分离支持SQL透传。
3. 同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性。
4. 基于Hint的强制主库路由。

2.6 不支持项

1. 主库和从库的数据同步。
2. 主库和从库的数据同步延迟导致的数据不一致。
3. 主库双写或多写。

3. 读写分离Demo

3.1 添加pom文件

<?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.2.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.tao.learn</groupId>
    <artifactId>m1-7-sharding</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>11</java.version>
    </properties>

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

        <!-- JPA -->


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

        <!-- postgresql -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.4.1212</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-core -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <!-- for spring boot -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <!-- for spring namespace -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-transaction-xa-core</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </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>
        <dependency>
            <groupId>org.jetbrains</groupId>
            <artifactId>annotations</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

3.2 配置文件

Mysql读写分离, 采用Druid作为datasource

# Mysql读写分离, 采用Druid作为datasource
spring.shardingsphere.datasource.names=master,slave0,slave1

spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/master?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://localhost:3306/slave0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456

spring.shardingsphere.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://localhost:3306/slave1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1

spring.shardingsphere.props.sql.show=true

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

PostgreSQL读写分离，采用druid作为datasource

# PostgreSQL读写分离，采用druid作为datasource
spring:
  shardingsphere:
    datasource:
      names: master,slave0,slave1
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: org.postgresql.Driver
        url: jdbc:postgresql://localhost:5432/slave0
        username: postgres
        password: postgres
      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: org.postgresql.Driver
        url: jdbc:postgresql://localhost:5432/slave1
        username: postgres
        password: postgres
      slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: org.postgresql.Driver
        url: jdbc:postgresql://localhost:5432/slave2
        username: postgres
        password: postgres
    masterslave:
      load-balance-algorithm-type: round_robin
      name: ms
      master-data-source-name: master
      slave-data-source-names: slave0,slave1
    props:
      sql.show: true
  jpa:
    properties:
      hibernate:
        dialect=org:
          hibernate:
            dialect:
              PostgreSQLDialect: org.hibernate.dialect.PostgreSQLDialect

PostgreSQL读写分离，采用hikari作为datasource

# PostgreSQL读写分离，采用hikari作为datasource

spring:
  shardingsphere:
    datasource:
      names: master,slave0,slave1
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: org.postgresql.Driver
        jdbc-url: jdbc:postgresql://localhost:5432/master
        username: postgres
        password: postgres
      slave0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: org.postgresql.Driver
        jdbc-url: jdbc:postgresql://localhost:5432/slave0
        username: postgres
        password: postgres
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: org.postgresql.Driver
        jdbc-url: jdbc:postgresql://localhost:5432/slave1
        username: postgres
        password: postgres
    masterslave:
      load-balance-algorithm-type: round_robin
      name: ms
      master-data-source-name: master
      slave-data-source-names: slave0,slave1
    props:
      sql.show: true
  jpa:
    properties:
      hibernate:
        temp:
          use_jdbc_metadata_defaults: false
        dialect: org.hibernate.dialect.PostgreSQLDialect

PostgreSQL采用hikari作为datasource，datasource下的url改为jdbc-url

application.yml

spring:
  jpa:
    properties:
      hibernate:
        hbm2ddl:
          auto: update
        show_sql: true

  profiles:
    active: master-slave-postgresql-hikari
  main:
    allow-bean-definition-overriding: true

3.3 编写CRUD基本操作

entity

/**
 * @author Jiantao Yan
 * @description: User实体类
 * @title: UserServiceImpl
 * @date 2019/12/24 15:03
 */
@Data
@Entity
@Table(name = "tb_user")
@EntityListeners(AuditingEntityListener.class)
public class User {
    /**
     * id
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    /**
     * 姓名
     */
    private String name;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 性别
     */
    private Integer sex;

    /**
     * 创建时间
     */
    @CreatedDate
    private LocalDateTime createTime;

    /**
     * 更新时间
     */
    @LastModifiedDate
    private LocalDateTime updateTime;
}

dao

/**
 * @author Jiantao Yan
 * @description: 用户dao层
 * @title: UserServiceImpl
 * @date 2019/12/24 15:03
 */
@Component
public interface UserDao extends JpaRepository<User, Long> {
}

service

/**
 * @author Jiantao Yan
 * @description: User Service接口层
 * @title: UserService
 * @date 2019/12/24 15:03
 */
public interface UserService {

    List<User> findAll();

    User save(User user);

    void update(User user);

    void delete(Long id);

    List<User> transaction(User user);
}

/**
 * @author Jiantao Yan
 * @description: User Service 实现层
 * @title: UserServiceImpl
 * @date 2019/12/24 15:03
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public List<User> findAll() {

        return userDao.findAll();
    }

    @Override
    public User save(User user) {
        return userDao.save(user);
    }

    @Override
    public void update(User user) {
        userDao.save(user);
    }

    @Override
    public void delete(Long id) {
        userDao.deleteById(id);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public List<User> transaction(User user) {
        userDao.deleteById(user.getId());
        return userDao.findAll();
    }
}

controller

/**
 * @author Jiantao Yan
 * @description: 用户Controller层
 * @title: UserController
 * @date 2019/12/24 15:13
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/")
    public List<User> findAll() {
        return userService.findAll();
    }

    @GetMapping("/transaction")
    public List<User> transaction(@RequestBody User user) {
        return userService.transaction(user);
    }

    @PostMapping("/")
    public User save(@RequestBody User user) {
        return userService.save(user);
    }

    @PutMapping("/")
    public Object update(User user) {
        userService.update(user);
        Map<String, Object> result = new HashMap<>(2);
        result.put("code", 200);
        result.put("msg", "更新成功");
        return result;
    }

    @DeleteMapping("/{id}")
    public Object save(@PathVariable(name = "id") Long id) {
        userService.delete(id);
        Map<String, Object> result = new HashMap<>(2);
        result.put("code", 200);
        result.put("msg", "更新成功");
        return result;
    }
}

3.4 SQL

MySql建表语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名字',
  `age` mediumint(9) NOT NULL DEFAULT 0 COMMENT '年龄',
  `sex` tinyint(4) NOT NULL DEFAULT 0 COMMENT '性别：0无，1男，2女',
  `create_time` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 398790076873572353 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表0' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

PostgreSql建表语句

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS "public"."tb_user";
CREATE TABLE "public"."tb_user" (
  "id" int8 NOT NULL DEFAULT nextval('tb_user_id_seq'::regclass),
  "age" int4,
  "create_time" timestamp(6),
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "sex" int4,
  "update_time" timestamp(6)
)
;

-- ----------------------------
-- Primary Key structure for table tb_user
-- ----------------------------
ALTER TABLE "public"."tb_user" ADD CONSTRAINT "tb_user_pkey" PRIMARY KEY ("id");

3.5 测试

3.5.1 插入操作

使用主库进行插入操作

2020-01-03 10:33:34.532  INFO 2584 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Rule Type: master-slave
2020-01-03 10:33:34.532  INFO 2584 --- [nio-8080-exec-2] ShardingSphere-SQL                       : SQL: insert into tb_user (age, create_time, name, sex, update_time) values (?, ?, ?, ?, ?) ::: DataSources: master
Hibernate: select currval('tb_user_id_seq')
2020-01-03 10:33:34.545  INFO 2584 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Rule Type: master-slave
2020-01-03 10:33:34.546  INFO 2584 --- [nio-8080-exec-2] ShardingSphere-SQL                       : SQL: select currval('tb_user_id_seq') ::: DataSources: master

3.5.2 查询操作

使用从库查询，并且两个从库进行轮询

2020-01-03 10:33:34.545  INFO 2584 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Rule Type: master-slave
2020-01-03 10:33:34.546  INFO 2584 --- [nio-8080-exec-2] ShardingSphere-SQL                       : SQL: select currval('tb_user_id_seq') ::: DataSources: master
Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.create_time as create_t3_0_, user0_.name as name4_0_, user0_.sex as sex5_0_, user0_.update_time as update_t6_0_ from tb_user user0_
2020-01-03 10:36:23.344  INFO 2584 --- [nio-8080-exec-5] ShardingSphere-SQL                       : Rule Type: master-slave
2020-01-03 10:36:23.345  INFO 2584 --- [nio-8080-exec-5] ShardingSphere-SQL                       : SQL: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.create_time as create_t3_0_, user0_.name as name4_0_, user0_.sex as sex5_0_, user0_.update_time as update_t6_0_ from tb_user user0_ ::: DataSources: slave1
Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.create_time as create_t3_0_, user0_.name as name4_0_, user0_.sex as sex5_0_, user0_.update_time as update_t6_0_ from tb_user user0_
2020-01-03 10:36:57.317  INFO 2584 --- [nio-8080-exec-6] ShardingSphere-SQL                       : Rule Type: master-slave
2020-01-03 10:36:57.317  INFO 2584 --- [nio-8080-exec-6] ShardingSphere-SQL                       : SQL: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.create_time as create_t3_0_, user0_.name as name4_0_, user0_.sex as sex5_0_, user0_.update_time as update_t6_0_ from tb_user user0_ ::: DataSources: slave0

3.5.3 事务操作

该线程只要有一个是增删改，则该线程的后续操作全部采用主库master

Hibernate: select user0_.id as id1_0_0_, user0_.age as age2_0_0_, user0_.create_time as create_t3_0_0_, user0_.name as name4_0_0_, user0_.sex as sex5_0_0_, user0_.update_time as update_t6_0_0_ from tb_user user0_ where user0_.id=?
2020-01-03 10:39:20.562  INFO 2584 --- [nio-8080-exec-3] ShardingSphere-SQL                       : Rule Type: master-slave
2020-01-03 10:39:20.563  INFO 2584 --- [nio-8080-exec-3] ShardingSphere-SQL                       : SQL: select user0_.id as id1_0_0_, user0_.age as age2_0_0_, user0_.create_time as create_t3_0_0_, user0_.name as name4_0_0_, user0_.sex as sex5_0_0_, user0_.update_time as update_t6_0_0_ from tb_user user0_ where user0_.id=? ::: DataSources: slave1
Hibernate: delete from tb_user where id=?
2020-01-03 10:39:20.565  INFO 2584 --- [nio-8080-exec-3] ShardingSphere-SQL                       : Rule Type: master-slave
2020-01-03 10:39:20.565  INFO 2584 --- [nio-8080-exec-3] ShardingSphere-SQL                       : SQL: delete from tb_user where id=? ::: DataSources: master
Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.create_time as create_t3_0_, user0_.name as name4_0_, user0_.sex as sex5_0_, user0_.update_time as update_t6_0_ from tb_user user0_
2020-01-03 10:39:20.567  INFO 2584 --- [nio-8080-exec-3] ShardingSphere-SQL                       : Rule Type: master-slave
2020-01-03 10:39:20.567  INFO 2584 --- [nio-8080-exec-3] ShardingSphere-SQL                       : SQL: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.create_time as create_t3_0_, user0_.name as name4_0_, user0_.sex as sex5_0_, user0_.update_time as update_t6_0_ from tb_user user0_ ::: DataSources: master

全部使用主库的前提的要有一个增删改操作，后续才使用主库，而不是所有的事务操作都会使用主库

例如：

1. 查->改->删->查

   该操作所对应的数据库为：从->主->主->主

2. 改->查->删->增->查

   该操作所对应的数据库为：主->主->主->主->主

4. 结语

采用sharding-jdbc做读写分离的好处有以下几点：

• 对项目代码零侵入，不用修改业务层的代码
• 从库可设置路由策略
• 可独立使用，也可配合以后分库分表作为使用
• 同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性

缺点：

• 主库读写策略不是根据事务来划分，而是根据同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性
• 主从库数据不支持（不影响）
• 主库双写或多写（不影响）

源码位置：learn