Springboot+ShardingSphere-JDBC (二)：读写分离

Springboot+ShardingSphere-JDBC (二)：读写分离

目录

Springboot+ShardingSphere-JDBC (二)：读写分离

背景

实践

依赖

yml配置

实体类

Mapper

测试

---

ShardingSphere-JDBC(一)：分库分表

背景

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

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

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

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

实践

• 依赖

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

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


        <!-- shardingSphere 分库分表 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.2.0</version>
        </dependency>
    </dependencies>

• yml配置

server:
  port: 8080
spring:
  application:
    name: write
  shardingsphere:
    datasource:
      names: master,slave
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:13306/test?characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=true
        username: root
        password: root
      slave:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:13307/test?characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=true
        username: root
        password: root
    rules:
      sharding:
        sharding-algorithms:
          order-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{order_id % 2}
        key-generators:
          snowflake:
            type: SNOWFLAKE
        tables:
          t_order:
            actual-data-nodes: read-write.t_order_$->{0..1}
            tableStrategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order-inline
            key-generate-strategy:
              column: order_id
              key-generator-name: snowflake

      readwrite-splitting:
        data-sources:
          read-write:
            static-strategy:
              write-data-source-name: master
              read-data-source-names: slave
            load-balancer-name: load
        load-balancers:
          load:
            type: RANDOM




    props:
      sql-show: true
mybatis:
  configuration:
    map-underscore-to-camel-case: true

• 实体类

package com.study.sharding.entity;

import java.math.BigDecimal;

/**
 * @author Say Hello
 * @version 1.0.0
 * @Date 2023/7/18
 * @Description
 */
public class OrderPO {

    private Long orderId;

    private Long userId;

    private BigDecimal orderPrice;

    public OrderPO() {
    }

    public OrderPO(Long orderId, Long userId, BigDecimal orderPrice) {
        this.orderId = orderId;
        this.userId = userId;
        this.orderPrice = orderPrice;
    }

    public Long getOrderId() {
        return orderId;
    }

    public void setOrderId(Long orderId) {
        this.orderId = orderId;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public BigDecimal getOrderPrice() {
        return orderPrice;
    }

    public void setOrderPrice(BigDecimal orderPrice) {
        this.orderPrice = orderPrice;
    }
}

• Mapper

package com.study.sharding.dao;


import com.study.sharding.entity.OrderPO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;



/**
 * @author Say hello
 * @version 1.0.0
 * @Date 2023/7/18
 * @Description
 */
@Mapper
public interface OrderMapper {


    /**
     * 创建订单，雪花算法自动生成主键
     */
    @Insert("insert into t_order(order_price,user_id)values(#{order.orderPrice },#{order.userId})")
    int insertOrder(@Param("order") OrderPO po);

    /**
     * 根据id列表查询订单
     */
    @Select("select * from  t_order where order_id = #{orderId} ")
    OrderPO  selectById(@Param("orderId") Long orderId);

/**/
}

• 测试

package com.study.sharding.dao;

import com.study.sharding.entity.OrderPO;
import org.json.JSONArray;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import java.math.BigDecimal;

/**
 * @author Say Hello
 * @version 1.0.0
 * @Date 2023/7/18
 * @Description
 */
@SpringBootTest
class OrderMapperTest {
    @Resource
    OrderMapper orderMapper;

    @Test
    void testInsert() {
        for (int i = 0; i < 10; i++) {
            OrderPO po = new OrderPO();
            po.setUserId((long) i);
            po.setOrderPrice(new BigDecimal(i));
            orderMapper.insertOrder(po);
            Assertions.assertNotNull(po);
        }
    }

    @Test
    void testSelectById() {
        OrderPO po = orderMapper.selectById(888727597045776384L);
        Assertions.assertNotNull(po);
        System.out.println(po.getOrderId() + ":" + po.getUserId() + ":" + po.getOrderPrice());
    }
}

测试结果，插入到master数据库

 读取从slave读取