shading-jdbc數(shù)據(jù)庫讀寫分離

1. ShardingSphere

1.1 ShardingSphere簡介

ShardingSphere是一套開源的分布式數(shù)據(jù)庫中間件解決方案組成的生態(tài)圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（計(jì)劃中）這3款相互獨(dú)立的產(chǎn)品組成。他們均提供標(biāo)準(zhǔn)化的數(shù)據(jù)分片、分布式事務(wù)和數(shù)據(jù)庫治理功能，可適用于如Java同構(gòu)、異構(gòu)語言、云原生等各種多樣化的應(yīng)用場景。

ShardingSphere定位為關(guān)系型數(shù)據(jù)庫中間件，旨在充分合理地在分布式的場景下利用關(guān)系型數(shù)據(jù)庫的計(jì)算和存儲(chǔ)能力，而并非實(shí)現(xiàn)一個(gè)全新的關(guān)系型數(shù)據(jù)庫。它與NoSQL和NewSQL是并存而非互斥的關(guān)系。NoSQL和NewSQL作為新技術(shù)探索的前沿，放眼未來，擁抱變化，是非常值得推薦的。反之，也可以用另一種思路看待問題，放眼未來，關(guān)注不變的東西，進(jìn)而抓住事物本質(zhì)。關(guān)系型數(shù)據(jù)庫當(dāng)今依然占有巨大市場，是各個(gè)公司核心業(yè)務(wù)的基石，未來也難于撼動(dòng)，我們目前階段更加關(guān)注在原有基礎(chǔ)上的增量，而非顛覆。

ShardingSphere目前已經(jīng)進(jìn)入Apache孵化器，歡迎通過shardingsphere的dev郵件列表與我們討論。

1.2 Sharding-JDBC簡介

定位為輕量級(jí)Java框架，在Java的JDBC層提供的額外服務(wù)。它使用客戶端直連數(shù)據(jù)庫，以jar包形式提供服務(wù)，無需額外部署和依賴，可理解為增強(qiáng)版的JDBC驅(qū)動(dòng)，完全兼容JDBC和各種ORM框架。

適用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
支持任何第三方的數(shù)據(jù)庫連接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意實(shí)現(xiàn)JDBC規(guī)范的數(shù)據(jù)庫。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92標(biāo)準(zhǔn)的數(shù)據(jù)庫。

2. 讀寫分離簡介

2.1 背景

面對(duì)日益增加的系統(tǒng)訪問量，數(shù)據(jù)庫的吞吐量面臨著巨大瓶頸。對(duì)于同一時(shí)刻有大量并發(fā)讀操作和較少寫操作類型的應(yīng)用系統(tǒng)來說，將數(shù)據(jù)庫拆分為主庫和從庫，主庫負(fù)責(zé)處理事務(wù)性的增刪改操作，從庫負(fù)責(zé)處理查詢操作，能夠有效的避免由數(shù)據(jù)更新導(dǎo)致的行鎖，使得整個(gè)系統(tǒng)的查詢性能得到極大的改善。

通過一主多從的配置方式，可以將查詢請求均勻的分散到多個(gè)數(shù)據(jù)副本，能夠進(jìn)一步的提升系統(tǒng)的處理能力。使用多主多從的方式，不但能夠提升系統(tǒng)的吞吐量，還能夠提升系統(tǒng)的可用性，可以達(dá)到在任何一個(gè)數(shù)據(jù)庫宕機(jī)，甚至磁盤物理損壞的情況下仍然不影響系統(tǒng)的正常運(yùn)行。

與將數(shù)據(jù)根據(jù)分片鍵打散至各個(gè)數(shù)據(jù)節(jié)點(diǎn)的水平分片不同，讀寫分離則是根據(jù)SQL語義的分析，將讀操作和寫操作分別路由至主庫與從庫。

讀寫分離的數(shù)據(jù)節(jié)點(diǎn)中的數(shù)據(jù)內(nèi)容是一致的，而水平分片的每個(gè)數(shù)據(jù)節(jié)點(diǎn)的數(shù)據(jù)內(nèi)容卻并不相同。將水平分片和讀寫分離聯(lián)合使用，能夠更加有效的提升系統(tǒng)性能。

2.2 挑戰(zhàn)

讀寫分離雖然可以提升系統(tǒng)的吞吐量和可用性，但同時(shí)也帶來了數(shù)據(jù)不一致的問題。這包括多個(gè)主庫之間的數(shù)據(jù)一致性，以及主庫與從庫之間的數(shù)據(jù)一致性的問題。并且，讀寫分離也帶來了與數(shù)據(jù)分片同樣的問題，它同樣會(huì)使得應(yīng)用開發(fā)和運(yùn)維人員對(duì)數(shù)據(jù)庫的操作和運(yùn)維變得更加復(fù)雜。下圖展現(xiàn)了將分庫分表與讀寫分離一同使用時(shí)，應(yīng)用程序與數(shù)據(jù)庫集群之間的復(fù)雜拓?fù)潢P(guān)系。

2.3 目標(biāo)

透明化讀寫分離所帶來的影響，讓使用方盡量像使用一個(gè)數(shù)據(jù)庫一樣使用主從數(shù)據(jù)庫集群，是ShardingSphere讀寫分離模塊的主要設(shè)計(jì)目標(biāo)。

2.4 核心概念

2.4.1 主庫

添加、更新以及刪除數(shù)據(jù)操作所使用的數(shù)據(jù)庫，目前僅支持單主庫

2.4.2 從庫

查詢數(shù)據(jù)操作所使用的數(shù)據(jù)庫，可支持多從庫。

2.4.3 主從同步

將主庫的數(shù)據(jù)異步的同步到從庫的操作。由于主從同步的異步性，從庫與主庫的數(shù)據(jù)會(huì)短時(shí)間內(nèi)不一致。（這一步由數(shù)據(jù)庫層實(shí)現(xiàn)，一般RDS數(shù)據(jù)庫提供商，都會(huì)提供讀寫分離的服務(wù)）

2.4.4 負(fù)載均衡策略

通過負(fù)載均衡策略將查詢請求疏導(dǎo)至不同從庫。

2.5 核心功能

提供一主多從的讀寫分離配置，可獨(dú)立使用，也可配合分庫分表使用。
獨(dú)立使用讀寫分離支持SQL透傳。
同一線程且同一數(shù)據(jù)庫連接內(nèi)，如有寫入操作，以后的讀操作均從主庫讀取，用于保證數(shù)據(jù)一致性。
基于Hint的強(qiáng)制主庫路由。

2.6 不支持項(xiàng)

主庫和從庫的數(shù)據(jù)同步。
主庫和從庫的數(shù)據(jù)同步延遲導(dǎo)致的數(shù)據(jù)不一致。
主庫雙寫或多寫。

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實(shí)體類
 * @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;

    /**
     * 創(chuàng)建時(shí)間
     */
    @CreatedDate
    private LocalDateTime createTime;

    /**
     * 更新時(shí)間
     */
    @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 實(shí)現(xiàn)層
 * @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 '創(chuàng)建時(shí)間',
  `update_time` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改時(shí)間',
  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 插入操作

使用主庫進(jìn)行插入操作

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 查詢操作

使用從庫查詢，并且兩個(gè)從庫進(jìn)行輪詢

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 事務(wù)操作

該線程只要有一個(gè)是增刪改，則該線程的后續(xù)操作全部采用主庫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

全部使用主庫的前提的要有一個(gè)增刪改操作，后續(xù)才使用主庫，而不是所有的事務(wù)操作都會(huì)使用主庫

例如：

查->改->刪->查

該操作所對(duì)應(yīng)的數(shù)據(jù)庫為：從->主->主->主

改->查->刪->增->查

該操作所對(duì)應(yīng)的數(shù)據(jù)庫為：主->主->主->主->主

4. 結(jié)語

采用sharding-jdbc做讀寫分離的好處有以下幾點(diǎn)：

對(duì)項(xiàng)目代碼零侵入，不用修改業(yè)務(wù)層的代碼
從庫可設(shè)置路由策略
可獨(dú)立使用，也可配合以后分庫分表作為使用
同一線程且同一數(shù)據(jù)庫連接內(nèi)，如有寫入操作，以后的讀操作均從主庫讀取，用于保證數(shù)據(jù)一致性

缺點(diǎn)：

主庫讀寫策略不是根據(jù)事務(wù)來劃分，而是根據(jù)同一線程且同一數(shù)據(jù)庫連接內(nèi)，如有寫入操作，以后的讀操作均從主庫讀取，用于保證數(shù)據(jù)一致性
主從庫數(shù)據(jù)不支持（不影響）
主庫雙寫或多寫（不影響）

源碼位置：learn

posted @ 2024-07-12 10:15 booleandev 閱讀(51) 評(píng)論(0) 收藏舉報(bào)

刷新頁面返回頂部

Jantor的博客