# lg-mysql-sharding

**Repository Path**: sunli1103_admin/lg-mysql-sharding

## Basic Information

- **Project Name**: lg-mysql-sharding
- **Description**: 【java训练营作业12-MySQL Sharding-JDBC分库分表】
第四阶段 大型分布式存储系统架构进阶
模块二 MySQL海量数据存储与优化（下）
本模块主要对MySQL海量数据处理中的分库分表架构、ShardingSphere、MyCat中间件实战应用、数据库实战规范、以及一些运维分析工具等内容进行讲解。
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2020-06-05
- **Last Updated**: 2022-03-04

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 作业说明

### 课程内容
> **第四阶段 大型分布式存储系统架构进阶**
> **模块二 MySQL海量数据存储与优化（下）**
> 本模块主要对MySQL海量数据处理中的分库分表架构、ShardingSphere、MyCat中间件实战应用、数据库实战规范、以及一些运维分析工具等内容进行讲解。



### 作业内容


> **采⽤Sharding-JDBC实现c_order表分库分表+读写分离**
>
> 1. 基于user_id对c_order表进⾏数据分⽚
> ![](https://images.gitee.com/uploads/images/2020/0620/224211_f347b5ff_1712191.png)
> 2. 分别对master1和master2搭建⼀主⼆从架构
> 3. 基于master1和master2主从集群实现读写分离
> 4.  c_order建表SQL如下：
```mysql
CREATE TABLE `c_order`(
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `is_del` bit(1) NOT NULL DEFAULT 0 COMMENT '是否被删
除',
 `user_id` int(11) NOT NULL COMMENT '⽤户id',
 `company_id` int(11) NOT NULL COMMENT '公司id',
 `publish_user_id` int(11) NOT NULL COMMENT 'B端⽤户id',
 `position_id` int(11) NOT NULL COMMENT '职位ID',
 `resume_type` int(2) NOT NULL DEFAULT 0 COMMENT '简历类型：
0附件 1在线',
 `status` varchar(256) NOT NULL COMMENT '投递状态 投递状态
WAIT-待处理 AUTO_FILTER-⾃动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝
ARRANGE_INTERVIEW-通知⾯试',
 `create_time` datetime NOT NULL COMMENT '创建时间',
 `update_time` datetime NOT NULL COMMENT '处理时间',
 PRIMARY KEY (`id`),
 KEY `index_userId_positionId` (`user_id`, `position_id`),
 KEY `idx_userId_operateTime` (`user_id`, `update_time`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
```



#### 目录结构

```
README.md      作业说明
resource
  sql          数据库sql
```



#### 软件版本

```
VirtualBox 6.1.8
CentOS 7.7
MySQL 5.7.28
SpringBoot 2.3.0
  SpringBoot Data JPA
  SpringBoot Test
Sharding-JDBC 4.1.0
```



#### 架构说明

**服务器信息**

| 角色    | IP            | 主机名    | server_id | 功能      |
| ------ | ------------- | -------- | --------- | -------- |
| Master | 192.168.3.111 | master1  | 11        | 接受写请求 |
| Slave  | 192.168.3.112 | slave1   | 12        | 接受读请求 |
| Master | 192.168.3.121 | master2  | 21        | 接受写请求 |
| Slave  | 192.168.3.122 | slave3   | 22        | 接受读请求 |



#### 注意事项
* 磁盘空间不足，只搭建了两个从库节点，分别为 `slave1`、`slave3`。
* 数据库主从搭建步骤已省略，参照上期作业内容。
* 分库采用随机生成 `user_id` 后奇偶划分，分表采用雪花算法生成 `id` 后奇偶划分。



#### 实现内容

* **Sharding-JDBC配置**

**application.properties**

```properties
spring.profiles.active=sharding
spring.shardingsphere.props.sql.show=true
```


**application-sharding.properties**

```properties
spring.shardingsphere.datasource.names=master1, slave1, master2, slave3

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.3.111:3306/lagou1
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.3.112:3306/lagou1
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root

spring.shardingsphere.datasource.master2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2.jdbc-url=jdbc:mysql://192.168.3.121:3306/lagou2
spring.shardingsphere.datasource.master2.username=root
spring.shardingsphere.datasource.master2.password=root

spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://192.168.3.122:3306/lagou2
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root

# 数据节点
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master$->{1..2}.c_order$->{1..2}
# 分库
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master$->{user_id % 2 + 1}
# 分表
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order$->{id % 2 + 1}

# 主键生成器
# @GeneratedValue(strategy = GenerationType.IDENTITY)
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE

# 读写分离
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave1
spring.shardingsphere.sharding.master-slave-rules.master2.master-data-source-name=master2
spring.shardingsphere.sharding.master-slave-rules.master2.slave-data-source-names=slave3
```



* **测试方法**

**ShardingApplicationTests.java**

```java
@ExtendWith(SpringExtension.class)
@SpringBootTest
class ShardingApplicationTests {

    @Autowired
    private OrderRepository orderRepository;

    /**
     * 通过插入数据测试分库分表
     */
    @Test
    @RepeatedTest(19)
    public void testAdd() {

        Random random = new Random();
        int userId = random.nextInt(10);

        COrder cOrder = new COrder();
        cOrder.setDel(false);
        cOrder.setUserId(userId);
        cOrder.setCompanyId(2);
        cOrder.setPositionId(3);
        cOrder.setPublishUserId(4);
        cOrder.setResumeType(0);
        cOrder.setStatus("WAIT");
        cOrder.setCreateTime(new Date());
        cOrder.setUpdateTime(new Date());

        orderRepository.save(cOrder);
    }

    /**
     * 通过查询数据测试读写分离
     */
    @Test
    public void testQuery() {
        List<COrder> cOrderList = orderRepository.findAll();

        cOrderList.forEach(cOrder -> {
            System.out.println(cOrder.toString());
        });

        System.out.println("查询记录总件数：" + cOrderList.size());
    }
}

```



* **测试步骤**

**1. 分库分表**

执行 `testAdd` 方法，插入20条数据。确认数据库数据。

**master1**

![image-20200607175911944](https://images.gitee.com/uploads/images/2020/0620/224211_731f0482_1712191.png)


**master1.c_order1**

只有偶数user_id，偶数id数据

![image-20200607180018656](https://images.gitee.com/uploads/images/2020/0620/224211_3ead9cd5_1712191.png)

**master1.c_order2**

只有偶数user_id，奇数id数据

![image-20200607180638501](https://images.gitee.com/uploads/images/2020/0620/224211_8af397fa_1712191.png)

**master2**

![image-20200607180753848](https://images.gitee.com/uploads/images/2020/0620/224211_9d63cd95_1712191.png)

**master2.c_order1**

只有奇数user_id，偶数id数据

![image-20200607180950807](https://images.gitee.com/uploads/images/2020/0620/224211_428cc302_1712191.png)

**master2.c_order2**

只有奇数user_id，奇数id数据

![image-20200607181026569](https://images.gitee.com/uploads/images/2020/0620/224211_7748f4b9_1712191.png)



**2. 读写分离**

只保留数据库从库中 `user_id` 和 `id` 都为偶数的数据（slave1.c_order1），清空其他表中奇数数据

![image-20200607181749601](https://images.gitee.com/uploads/images/2020/0620/224212_91d57d78_1712191.png)

执行查询测试方法，返回偶数数据

![image-20200607182104374](https://images.gitee.com/uploads/images/2020/0620/224212_7c106a6f_1712191.png)




#### 相关内容

**SQL**

建表

```mysql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for c_order1
-- ----------------------------
DROP TABLE IF EXISTS `c_order1`;
CREATE TABLE `c_order1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删除',
  `user_id` int(11) NOT NULL COMMENT '⽤户id',
  `company_id` int(11) NOT NULL COMMENT '公司id',
  `publish_user_id` int(11) NOT NULL COMMENT 'B端⽤户id',
  `position_id` int(11) NOT NULL COMMENT '职位ID',
  `resume_type` int(2) NOT NULL DEFAULT '0' COMMENT '简历类型(0:附件, 1:在线)',
  `status` varchar(256) NOT NULL COMMENT '投递状态(WAIT:待处理, AUTO_FILTER:⾃动过滤, PREPARE_CONTACT:待沟通, REFUSE:拒绝, ARRANGE_INTERVIEW:通知⾯试)',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '处理时间',
  PRIMARY KEY (`id`),
  KEY `idx_userId_positionId` (`user_id`,`position_id`),
  KEY `idx_userId_operateTime` (`user_id`,`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=476090669752909825 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for c_order2
-- ----------------------------
DROP TABLE IF EXISTS `c_order2`;
CREATE TABLE `c_order2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删除',
  `user_id` int(11) NOT NULL COMMENT '⽤户id',
  `company_id` int(11) NOT NULL COMMENT '公司id',
  `publish_user_id` int(11) NOT NULL COMMENT 'B端⽤户id',
  `position_id` int(11) NOT NULL COMMENT '职位ID',
  `resume_type` int(2) NOT NULL DEFAULT '0' COMMENT '简历类型(0:附件, 1:在线)',
  `status` varchar(256) NOT NULL COMMENT '投递状态(WAIT:待处理, AUTO_FILTER:⾃动过滤, PREPARE_CONTACT:待沟通, REFUSE:拒绝, ARRANGE_INTERVIEW:通知⾯试)',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '处理时间',
  PRIMARY KEY (`id`),
  KEY `idx_userId_positionId` (`user_id`,`position_id`),
  KEY `idx_userId_operateTime` (`user_id`,`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=476090669752909825 DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

```



#### 参考资料

[【拉勾课程速推指南】阶段四 模块二 MySQL海量数据存储与优化（下）](https://www.jianshu.com/p/3f926ee0385a)