分库分表之_读写分离

野性酷女 2023-07-20 12:30 22阅读 0赞

**前言**

Github：[https://github.com/HealerJean][https_github.com_HealerJean]

博客：[http://blog.healerjean.com][http_blog.healerjean.com]

# 1、开始Demo #

## 1.1、`hlj-01-read-write.sql` ##

drop database if exists ds_0;
    create database `ds_0` character set 'utf8' collate 'utf8_general_ci';
    use ds_0;
    
    drop table if exists user;
    create table `user`
    (
      `id`        bigint(20) unsigned not null,
      city        varchar(20)         not null default '',
      name        varchar(20)         not null default '',
      status      int(10)             not null default '0' comment '状态',
      create_time datetime            not null default current_timestamp comment '创建时间',
      update_time datetime            not null default current_timestamp on update current_timestamp comment '修改时间',
      primary key (`id`)
    ) engine = innodb
      default charset = utf8;
    
    
    drop database if exists ds_1;
    create database `ds_1` character set 'utf8' collate 'utf8_general_ci';
    use ds_1;
    drop table if exists user;
    create table `user`
    (
      `id`        bigint(20) unsigned not null,
      city        varchar(20)         not null default '',
      name        varchar(20)         not null default '',
      status      int(10)             not null default '0' comment '状态',
      create_time datetime            not null default current_timestamp comment '创建时间',
      update_time datetime            not null default current_timestamp on update current_timestamp comment '修改时间',
      primary key (`id`)
    ) engine = innodb
      default charset = utf8;

### 1.1.1、数据库图文 ###

![1585554177489][]

## 1.2、依赖 ##

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

## 1.3、配置文件：`application.properties` ##

server.port=8888
    
    
    # 配置 mybatis的一些配置，也可以在 application.properties 中配置，如果配置了就不需要了mybatis.xml
    #mybatis-plus.config-location=classpath:mybatis.xml
    #Maven 多模块项目的扫描路径需以 classpath*: 开头 （即加载多个 jar 包下的 XML 文件）
    mybatis-plus.mapper-locations=classpath*:mapper/*.xml
    mybatis-plus.type-aliases-package=com.healerjean.proj.pojo
    ##主键类型  0:"数据库ID自增，非常大", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
    mybatis-plus.id-type: 0
    #字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
    mybatis-plus.field-strategy: 2
    #数据库大写下划线转换
    mybatis-plus.capital-mode: true
    mybatis-plus.refresh-mapper: true
    
    
    
    
    # 显示SQL
    spring.shardingsphere.props.sql.show=true
    # #当遇到同样名字的时候，是否允许覆盖注册
    spring.main.allow-bean-definition-overriding=true
    
    # 读写分离 数据源定义(会向主数据库中插入数据，但是查询的话是从从表查的（不会出现主从复制的情况哦）)
    spring.shardingsphere.datasource.names=master,slave
    # 主数据源
    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/ds_0?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    spring.shardingsphere.datasource.master.username=root
    spring.shardingsphere.datasource.master.password=123456
    # 从数据源
    spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.slave.url=jdbc:mysql://localhost:3306/ds_1?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    spring.shardingsphere.datasource.slave.username=root
    spring.shardingsphere.datasource.slave.password=123456
    
    # 读写分离配置
    spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
    # 最终的数据源名称
    spring.shardingsphere.masterslave.name=dataSource
    # 主库数据源名称
    spring.shardingsphere.masterslave.master-data-source-name=master
    # 从库数据源名称列表，多个逗号分隔
    spring.shardingsphere.masterslave.slave-data-source-names=slave

## 1.4、具体测试方法和类 ##

### 1.4.1、User.java ###

@Data
    @Accessors(chain = true)
    public class User implements Serializable { 
    
        private static final long serialVersionUID = 1L;
    
        /** 主键 */
        private Long id;
        private String name;
        private String city;
        private String status;
        private Date createTime;
        private Date updateTime;
    }

### 1.4.2、`UserDTO.java` ###

@Data
    @Accessors(chain = true)
    @ApiModel(value = "demo实体类")
    @JsonInclude(JsonInclude.Include.NON_NULL)
    public class UserDTO { 
    
        @ApiModelProperty(value = "主键", hidden = true)
        @JsonSerialize(using = JsonLongSerializer.class )
        private Long id;
    
        @ApiModelProperty(value = "姓名")
        @NotBlank(message = "姓名不能为空", groups = ValidateGroup.HealerJean.class)
        private String name;
    
        @ApiModelProperty(value = "城市")
        private String city;
    
        @ApiModelProperty(value = "状态", hidden = true)
        private String status;
    
        @ApiModelProperty(value = "创建时间", hidden = true)
        @JsonFormat(pattern = DateUtils.YYYY_MM_dd_HH_mm_ss, timezone = "GMT+8")
        private Date createTime;
    
        @ApiModelProperty(value = "修改时间", hidden = true)
        @JsonFormat(pattern = DateUtils.YYYY_MM_dd_HH_mm_ss, timezone = "GMT+8")
        private Date updateTime;
    }

### 1.4.3、`UserMapper.java` ###

public interface UserMapper extends BaseMapper<User> { 
    
    
    }

### 1.4.4、`UserService.java` ###

public interface UserService { 
    
    
        UserDTO insert(UserDTO userDTO);
    
        UserDTO findById(Long id);
    
        List<UserDTO> list();
    
    }

### 1.4.5、`UserServiceImpl.java` ###

@Service
    @Slf4j
    public class UserServiceImpl implements UserService { 
    
        @Resource
        private UserMapper userMapper;
    
    
        @Override
        public UserDTO insert(UserDTO userDTO) { 
            User user = BeanUtils.dtoToUserDTO(userDTO);
            user.setStatus(StatusEnum.生效.code);
            userMapper.insert(user);
            userDTO.setId(user.getId());
            return userDTO;
        }
    
        @Override
        public UserDTO findById(Long id) { 
            User user = userMapper.selectById(id);
            return user == null ? null : BeanUtils.userToDTO(user);
        }
    
        @Override
        public List<UserDTO> list() { 
            List<User> users = userMapper.selectList(null);
            List<UserDTO> list = null;
            if (!EmptyUtil.isEmpty(users)) { 
                list = users.stream().map(BeanUtils::userToDTO).collect(Collectors.toList());
            }
            return list;
        }
    
    }

### 1.4.6、`UserController.java` ###

@ApiResponses(value = { 
            @ApiResponse(code = 200, message = "访问正常"),
            @ApiResponse(code = 301, message = "逻辑错误"),
            @ApiResponse(code = 500, message = "系统错误"),
            @ApiResponse(code = 401, message = "未认证"),
            @ApiResponse(code = 403, message = "禁止访问"),
            @ApiResponse(code = 404, message = "url错误")
    })
    @Api(description = "demo控制器")
    @Controller
    @RequestMapping("hlj/demo")
    @Slf4j
    public class UserController { 
    
    
    
        @Autowired
        private UserService userService;
    
        @ApiOperation(value = "insert",
                notes = "insert",
                consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,
                produces = MediaType.APPLICATION_JSON_VALUE,
                response = UserDTO.class)
        @PostMapping(value = "insert", produces = "application/json; charset=utf-8")
        @ResponseBody
        public ResponseBean insert(UserDTO userDTO) { 
            log.info("样例--------mybaits-plus添加demo实体------数据信息{}", userDTO);
            String validate = ValidateUtils.validate(userDTO, ValidateGroup.HealerJean.class);
            if (!validate.equals(CommonConstants.COMMON_SUCCESS)) { 
                throw new BusinessException(ResponseEnum.参数错误, validate);
            }
            return ResponseBean.buildSuccess(userService.insert(userDTO));
        }
    
    
        @ApiOperation(notes = "findById",
                value = "findById",
                consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,
                produces = MediaType.APPLICATION_JSON_VALUE,
                response = UserDTO.class)
        @ApiImplicitParams({ 
                @ApiImplicitParam(name = "id", value = "demo主键", required = true, paramType = "path", dataType = "long"),
        })
        @GetMapping("findById/{id}")
        @ResponseBody
        public ResponseBean findById(@PathVariable Long id) { 
            log.info("样例--------findById------数据：id：{}", id);
            return ResponseBean.buildSuccess(userService.findById(id));
        }
    
        @ApiOperation(notes = "list",
                value = "list",
                consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,
                produces = MediaType.APPLICATION_JSON_VALUE,
                response = UserDTO.class)
        @GetMapping("list")
        @ResponseBody
        public ResponseBean list() { 
            log.info("样例--------list------");
            return ResponseBean.buildSuccess(userService.list());
        }
    
    
    }

## 1.5、开始测试 ##

### 1.5.1、插入数据： `/hlj/demo/insert` ###

#### 1.5.1.1、测试数据 ####

#### 1.5.1.2、查询数据库中插入的数据 ####

###### 1.5.1.2.1、查询数据库ds\_0、user表 ######

sql> select * from ds_0.user
    [2020-03-30 15:49:57] 6 rows retrieved starting from 1 in 431 ms (execution: 9 ms, fetching: 422 ms)

<table> 
 <tbody> 
  <tr> 
   <th>id</th> 
   <th>city</th> 
   <th>name</th> 
   <th>status</th> 
   <th>create_time</th> 
   <th>update_time</th> 
  </tr> 
  <tr> 
   <td>1244531596683517954</td> 
   <td>a</td> 
   <td>1</td> 
   <td>10</td> 
   <td>2020-03-30 15:47:09</td> 
   <td>2020-03-30 15:47:09</td> 
  </tr> 
  <tr> 
   <td>1244531609899769858</td> 
   <td>b</td> 
   <td>2</td> 
   <td>10</td> 
   <td>2020-03-30 15:47:12</td> 
   <td>2020-03-30 15:47:12</td> 
  </tr> 
  <tr> 
   <td>1244531634532917249</td> 
   <td>c</td> 
   <td>3</td> 
   <td>10</td> 
   <td>2020-03-30 15:47:18</td> 
   <td>2020-03-30 15:47:18</td> 
  </tr> 
  <tr> 
   <td>1244531650509021186</td> 
   <td>d</td> 
   <td>4</td> 
   <td>10</td> 
   <td>2020-03-30 15:47:22</td> 
   <td>2020-03-30 15:47:22</td> 
  </tr> 
  <tr> 
   <td>1244531672914993153</td> 
   <td>c</td> 
   <td>5</td> 
   <td>10</td> 
   <td>2020-03-30 15:47:27</td> 
   <td>2020-03-30 15:47:27</td> 
  </tr> 
  <tr> 
   <td>1244531711733276673</td> 
   <td>d</td> 
   <td>6</td> 
   <td>10</td> 
   <td>2020-03-30 15:47:37</td> 
   <td>2020-03-30 15:47:37</td> 
  </tr> 
 </tbody> 
</table>

###### 1.5.1.2.2、查询数据库ds1\_1、user表 ######

sql> select * from ds_1.user
    [2020-03-30 15:50:31] 0 rows retrieved in 63 ms (execution: 8 ms, fetching: 55 ms)
    
    发现为空

#### 1.5.1.3、归纳总结 ####

> **我们发现所有的数据全部到了`ds_0`的`user`表中，因为是读写分离，`ds_0`是主表，用来写的表。而读表则是`ds_1`中的`user`表，读写分离测试成功**

### 1.5.2、根据Id查询：`/hlj/demo/findById` ###

#### 1.5.2.1、测试数据 ####

##### 1.5.2.1.1、Id：1244531596683517954 #####

http://127.0.0.1:8888/hlj/demo/findById/1244531596683517954
    
    { 
      "success": true,
      "result": null,
      "msg": "",
      "code": 200,
      "date": "1585554914827"
    }

##### 1.5.2.1.2、Id：1244531609899769858 #####

http://127.0.0.1:8888/hlj/demo/findById/1244531596683517954
    
    
    { 
      "success": true,
      "result": null,
      "msg": "",
      "code": 200,
      "date": "1585554965412"
    }

#### 1.5.2.2、归纳总结 ####

> 可以看到查询出来的数据是空的，所以可以验证我们的读写分离配置正确，因为我们没有网ds\_1库的user表放入数据，所以查不到数据 ，读写分离测试成功

##### 1.5.2.2.1、验证归纳 #####

**为了验证，我将`id`为`1244531596683517954`的数据放到`ds_1`的`user`表中进行测试，查询成功**

http://127.0.0.1:8888/hlj/demo/findById/1244531596683517954
    
    
    { 
      "success": true,
      "result": { 
        "id": "1244531596683517954",
        "name": "1",
        "city": "a",
        "status": "10",
        "createTime": "2020-03-30 15:47:09",
        "updateTime": "2020-03-30 15:47:09"
      },
      "msg": "",
      "code": 200,
      "date": "1585555387337"
    }

### 1.5.3、List查询：`/hlj/demo/list` ###

#### 1.5.3.1、测试 ####

> 继续使用1.5.2.2.1中的数据，在数据库`ds_1`的表`user`中插入的数据，id为：1244531596683517954

http://127.0.0.1:8888/hlj/demo/list
    
    { 
      "success": true,
      "result": [
        { 
          "id": "1244531596683517954",
          "name": "1",
          "city": "a",
          "status": "10",
          "createTime": "2020-03-30 15:47:09",
          "updateTime": "2020-03-30 15:47:09"
        }
      ],
      "msg": "",
      "code": 200,
      "date": "1585555442095"
    }

#### 1.5.3.2、归纳总结 ####

> 发现只有一条数据，而这条数据使我手动放到数据库`ds_1`的表`user`中的，读写分离测试成功

![ContactAuthor][]

[https_github.com_HealerJean]: https://github.com/HealerJean
[http_blog.healerjean.com]: http://HealerJean.github.io
[1585554177489]: /images/20230528/075f01e5cd464675b3dc77c9f3741500.png
[ContactAuthor]: /images/20230528/a5e92933f97b460e80f08c5f8fae7393.png