springboot+shardingsphere实现读写分离和分表

参考:https://blog.csdn.net/weixin_44606481/article/details/140955787
前提:数据库配置了主从数据同步

1、依赖

<dependencies>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.4</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3</version>
        </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>
    </dependencies>

2、读写分离

2.1 数据表:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `name` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2.2 controller、service、Mapper、entity按平时的建立

package com.cgy.daily.controller;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.cgy.daily.entity.Iuser;
import com.cgy.daily.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class CesController {

    @Autowired
    UserMapper userMapper;

    @GetMapping("/cs/test")
    public String test() {
        Iuser iuser = new Iuser();
        iuser.setName("tom-1");
        iuser.setAge(100);
        iuser.setEmail("tt@qq.com");
        userMapper.insert(iuser);
        return "test";
    }

    @GetMapping("/cs/list")
    public String list() {

        List<Iuser> iusers = userMapper.selectList(new LambdaQueryWrapper<>());
        System.out.println();
        return "test";
    }
}

2.3 配置application.yml文件

server:
  port: 9901
spring:
  shardingsphere:
    props:
      sql-show: true
    datasource:
      names: ds1,ss0,ss1
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: root
        password: 12345aass
      ss0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: root
        password: 12345aass
      ss1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: root
        password: 12345aass
    rules:
      readwrite-splitting:
        data-sources:
          ms:
            static-strategy:
              write-data-source-name: ds1
              read-data-source-names:
                - ss0
                - ss1
            load-balancer-name: round_robin
        load-balancers:
          round_robin:
            type: ROUND_ROBIN

当插入的时候,插入主库,查询的时候,轮询查询从库
image

image

3、分表

3.1 配置yaml文件

server:
  port: 9901
spring:
  shardingsphere:
    # 开启sql显示
    props:
      sql-show: true
    datasource:
      #全部数据源名称,多个用逗号隔开
      names: ds1,ss0,ss1
      #主数据源
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: root
        password: 12345aass
      # 从数据源0
      ss0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: root
        password: 12345aass
      #从数据源1
      ss1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: root
        password: 12345aass
    rules:
      sharding:
        tables:
          # user为需要分的表user表
          user:
            actual-data-nodes: ds1.user_$->{0..1}
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: user-inline
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          user-inline:
            type: INLINE
            props:
              # 分片键为id,分片算法为INLINE,表达式为id % 2,即根据id的奇偶性决定数据存储在哪个表中
              algorithm-expression: user_$->{id % 2}
        key-generators:
          snowflake:
            # 使用SNOWFLAKE作为全局唯一ID生成器,确保分布式环境下的ID唯一性
            type: SNOWFLAKE
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

image

4、读写分离+分表

4.1 整合后的yml文件

server:
  port: 9901
spring:
  shardingsphere:
    # 开启sql显示
    props:
      sql-show: true
    datasource:
      #全部数据源名称,多个用逗号隔开
      names: ds1,ss0,ss1
      #主数据源
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: root
        password: 12345aass
      # 从数据源0
      ss0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: root
        password: 12345aass
      #从数据源1
      ss1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: root
        password: 12345aass
    rules:
      readwrite-splitting:
        data-sources:
          # cgy是逻辑数据源名称,用于读写分离 自己定义
          cgy:
            # 写操作将路由到主数据源ds1,读操作将路由到从数据源ss0和ss1
            static-strategy:
              write-data-source-name: ds1
              read-data-source-names:
                - ss0
                - ss1
            load-balancer-name: round_robin
        load-balancers:
          # 使用ROUND_ROBIN负载均衡策略在多个从数据源之间分发读请求。
          round_robin:
            type: ROUND_ROBIN
      sharding:
        tables:
          # user为需要分的表user表
          user:
            # user表被分片到ms.user_0和ms.user_1两个实际物理表中,cgy为逻辑数据源名称
            actual-data-nodes: cgy.user_$->{0..1}
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: user-inline
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          user-inline:
            type: INLINE
            props:
              # 分片键为id,分片算法为INLINE,表达式为id % 2,即根据id的奇偶性决定数据存储在哪个表中
              algorithm-expression: user_$->{id % 2}
        key-generators:
          snowflake:
            # 使用SNOWFLAKE作为全局唯一ID生成器,确保分布式环境下的ID唯一性
            type: SNOWFLAKE
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

查询从从库里分表查,插入到主库的分表。
image
image

posted @ 2025-01-08 11:34  spiderMan1-1  阅读(19)  评论(0编辑  收藏  举报