Shardingsphere的分库分表+读写分离+分页条件查询

Shardingsphere的分库分表与读写分离

导入依赖

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.31</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.10</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

创建数据库，前提是两个服务器做好了主从复制

spring:
  shardingsphere:
    mode:
      type: Memory                                                   # 内存模式，元数据保存在当前进程中
    datasource:
      names: master$->{0..1},slave$->{0..1}
      master0:                                                # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource                 # 连接池
        url: jdbc:mysql://xxxxxx/test0                       # 连接url
        username: root
        password: 123456
      master1:                                                  # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://xxxxxx/test1
        username: root
        password: 123456
      slave0:                                                   # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://xxxxxx/test0
        username: root
        password: 123456
      slave1:                                                   # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://xxxxxx/test1
        username: root
        password: 123456

    rules:                                                           # 配置分库分表以及读写分离的规则
      sharding:                                                      # 配置分库分表规则
        tables:
          user:                                                      # 这个可以随便取，问题不大
            actual-data-nodes: master$->{0..1}.user$->{0..2}    # 实际节点名称，格式为 库名$->{0..n1}.表名$->{0..n2}
              # 其中n1、n2分别为库数量-1和表数量-1
              # 也可以使用${0..n1}的形式，但是会与Spring属性文件占位符冲突
            # 所以使用$->{0..n1}的形式

            database-strategy:                                       # 分库策略
              standard:                                              # 标准分库策略
                sharding-column: age                                 # 分库列名
                sharding-algorithm-name: age-mod                     # 分库算法名字
            table-strategy:                                          # 分表策略
              standard:                                              # 标准分表策略
                sharding-column: id                                  # 分表列名
                sharding-algorithm-name: id-mod                      # 分表算法名字

        sharding-algorithms:                                         # 配置分库和分表的算法
          age-mod:                                                   # 分库算法名字
            type: MOD                                                # 算法类型为取模
            props:                                                   # 算法配置的键名，所有算法配置都需要在props下
              sharding-count: 2                                      # 分片数量
          id-mod:                                                    # 分表算法名字
            type: MOD                                                # 算法类型为取模
            props:                                                   # 算法配置的键名，所有算法配置都需要在props下
              sharding-count: 3                                      # 分片数量

      readwrite-splitting:                                           # 配置读写分离规则
        data-sources:                                                # 数据源
          master0:                                              # 这个可以随便取，带有区分意义即可，比如这里表示的是主库test0的规则
            type: Static                                             # 静态类型
            load-balancer-name: round_robin                          # 负载均衡算法名字
            props:                                                   # 具体读写数据源需要配置在props下
              write-data-source-name: master0                   # 写数据源
              read-data-source-names: slave0                    # 读数据源
          master1:
            type: Static                                             # 静态类型
            load-balancer-name: round_robin                          # 负载均衡算法名字
            props:                                                   # 具体读写数据源需要配置在props下
              write-data-source-name: master1                   # 写数据源
              read-data-source-names: slave1                    # 读数据源
        load-balancers:                                              # 负载均衡配置
          round_robin:                                               # 跟上面负载均衡算法的名字对应
            type: ROUND_ROBIN                                        # 负载均衡算法
    props:
      sql-show: true                                                 # 打印SQL

测试

package com.ycz.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.ycz.entity.User;
import com.ycz.service.IUserService;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Random;

@RestController
@RequestMapping("/user")
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class UserController {
    private final IUserService userService;

    @GetMapping("/insert")
    public boolean insert() {
        User user = new User();
        user.setName("啊实打实的撒");
        user.setAge(new Random().nextInt(100)+1);
        System.out.println(user);
        return userService.save(user);
    }

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

    @GetMapping("/select/{id}")
    public Object getById(@PathVariable String id){
        return userService.getById(id);
    }

    @GetMapping("/update/{id}")
    public Object update(@PathVariable String id){
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.lambda().set(User::getName , "大萨达十大");
        updateWrapper.eq("id" , id);
        return userService.update(updateWrapper);
    }
}

插入

可以发现写的是主库 master

3311

3312

查询

可以发现读的是从库

分页条件查询

yml

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath*:com/ycz/**/xml/*Mapper.xml

配置config

package com.ycz.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @Description:
 * @Author: Alex
 * @Date 2022-06-29-21:58
 * @Version: V1.0
 **/
@Configuration
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }
}

 @GetMapping("/select")
    public Object select(@RequestParam(value = "pageNo" ,required = false , defaultValue = "0")Integer pageNo ,
                         @RequestParam(value ="pageSize" ,required = false , defaultValue = "5")Integer pageSize) {

        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("age");
        Page<User> page = new Page<>(pageNo  , pageSize);

        IPage<User> pageList = userService.page(page ,queryWrapper);

        return pageList;
    }

查询结果：

{
    "records": [
        {
            "id": "1540666225414160385",
            "name": "test1_user0_2",
            "age": 89
        },
        {
            "id": "1540665503343718401",
            "name": "test1_user0_1",
            "age": 85
        },
        {
            "id": "1540666229851734017",
            "name": "test1_user2_2",
            "age": 81
        },
        {
            "id": "1540665515347816450",
            "name": "test0_user1_1",
            "age": 78
        },
        {
            "id": "1540669670086983682",
            "name": "test0_user1_3",
            "age": 78
        }
    ],
    "total": 15,
    "size": 5,
    "current": 1,
    "orders": [],
    "optimizeCountSql": true,
    "hitCount": false,
    "countId": null,
    "maxLimit": null,
    "searchCount": true,
    "pages": 3
}

我们看看其sql语句

sql语句是这样的
slave0 ::: SELECT  id,name,age  FROM user0 ORDER BY age DESC LIMIT ? ::: [5]
slave0 ::: SELECT  id,name,age  FROM user1 ORDER BY age DESC LIMIT ? ::: [5]
slave0 ::: SELECT  id,name,age  FROM user2 ORDER BY age DESC LIMIT ? ::: [5]
slave1 ::: SELECT  id,name,age  FROM user0 ORDER BY age DESC LIMIT ? ::: [5]
slave1 ::: SELECT  id,name,age  FROM user1 ORDER BY age DESC LIMIT ? ::: [5]
slave1 ::: SELECT  id,name,age  FROM user2 ORDER BY age DESC LIMIT ? ::: [5]

sharding JDBC 默认使用limit进行分页，在不指定路由字段时，分页将会全库全表全数据捞取，然后进行排序。