# sharding

**Repository Path**: lisy/sharding

## Basic Information

- **Project Name**: sharding
- **Description**: 读写分离测试（sharding）
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2022-01-12
- **Last Updated**: 2022-05-25

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 读写分离相关

## 一、数据库sql

```sql
CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```
```sql
CREATE TABLE `user_info` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL DEFAULT '0',
  `msg1` varchar(255) DEFAULT NULL,
  `msg2` varchar(255) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```

## 二、说明

pom

```
        <!-- AOP依赖模块 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <!-- for spring boot -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <!-- for spring namespace -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>4.1.1</version>
        </dependency>
```

application.yml(一主一从)

```
server:
  port: 22483
spring:
  shardingsphere:
    datasource:
      master:
        driver-class-name: com.mysql.cj.jdbc.Driver
        password: dadfasdfas1234@NPW
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.6.216:33061/task?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
        username: testNew
      names: master,slave0
      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        password: dadfasdfas1234@NPW
        url: jdbc:mysql://192.168.6.216:33062/task?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
        username: testNew
    masterslave:
      master-data-source-name: master
      name: ms
      slave-data-source-names: slave0
    props:
      sql:
        show: true
```

`sharding-jdbc-core`可以实现代码层面的读写分离，插入和更新走主库，查询走从库。测试发现并没有像官方说的，同一个线程有插入语句后后面的查询走从库，官方有代码可以显示指定使用主库，把显示指定用主库的代码封装成了注解，在插入/更新立即查询的场景下使用该注解，保证数据一致性。示例代码参考`com.lisynet.tools.mytools.controller.UserController`
## 四、关于redis高可用
**搭建redis集群后，代码里面不要用线程池，否则程序还是会用旧的连接，导致连接异常**