# stage04-02

**Repository Path**: null_631_9084/stage04-02

## Basic Information

- **Project Name**: stage04-02
- **Description**: stage04-02
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2020-09-23
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

Mysql下：作业题
采⽤Sharding-JDBC实现c_order表分库分表+读写分离
1. 1. 基于user_id对c_order表进⾏数据分⽚
2. 分别对master1和master2搭建⼀主⼆从架构
3. 基于master1和master2主从集群实现读写分离
4. c_order建表SQL如下：
```sql
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;
```
### 作业解析
1.2套Mysql 1主2从
2. 对表进行分库

#### 作业
##### 建表 c_user c_order_0,c_order_1
```sql

CREATE TABLE `c_user` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=515528099455041538 DEFAULT CHARSET=utf8mb4;


```
* c_order_0
```sql

CREATE TABLE `c_order_0` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删除',
  `user_id` bigint(20) 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 AUTO_INCREMENT=9207734426943835669 DEFAULT CHARSET=utf8mb4;
```
* c_order_1
```sql

CREATE TABLE `c_order_1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删除',
  `user_id` bigint(20) 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 AUTO_INCREMENT=9207734426943835669 DEFAULT CHARSET=utf8mb4;
```

对c_user做分库 分库键为id
对c_order 进行分库分表 c_order_0,c_order_1


#### 实现类
```java

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "c_user")
public class CUser implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "name")
    private String name;



}

@NoArgsConstructor
@AllArgsConstructor
@Data
@Entity
@Table(name = "c_order")
public class COrder implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "is_del")
    private Boolean isDel;

    @Column(name = "user_id")
    private long userId;

    @Column(name = "company_id")
    private Integer companyId;



    @Column(name = "publish_user_id")
    private Integer publishUserId;


    @Column(name = "position_id")
    private long positionId;

    @Column(name = "resume_type")
    private Integer resumeType;

    @Column(name = "status")
    private String status;

    @Column(name = "create_time")
    private Date createTime;

    @Column(name = "update_time")
    private Date updateTime;

}
```
#### 自定义分片策略
```java
package com.liu.id;


import org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator;

import java.util.Properties;
import java.util.concurrent.ThreadLocalRandom;


public class MyShardingKeyGenerator implements ShardingKeyGenerator {
    //private SnowflakeShardingKeyGenerator snow = new SnowflakeShardingKeyGenerator();
private ThreadLocalRandom random = ThreadLocalRandom.current();
    @Override
    public Comparable<?> generateKey() {
       // System.out.println("------执行了自定义主键生成器MyKEY-------");
        long currentTimeMillis = System.currentTimeMillis();
        long nextLong = random.nextLong();
        System.out.println("------执行了自定义主键生成器MYKEY  "+nextLong+"   currentTimeMillis "+currentTimeMillis);
        return Math.abs(nextLong)+currentTimeMillis;
    }

    @Override
    public String getType() {
        return "MYKEY";
    }

    @Override
    public Properties getProperties() {
        return null;
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

```
在resources目录下建立META-INF/services目录
建立文件
org.apache.shardingsphere.spi.keygen
内容
```
com.liu.id.MyShardingKeyGenerator
```

#### 配置文件
```properties

#datasoure
spring.shardingsphere.datasource.names=master0,slave1,slave2,master1,slave3,slave4

#master0
spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://192.168.181.135:3306/lagou1?characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=root
#slave1
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.181.134:3306/lagou1?characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root

#slave2
spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://192.168.181.128:3306/lagou1?characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=root


#master1
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.181.138:3306/lagou1?characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root

#slave3
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.181.139:3306/lagou1?characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root

#slave4
spring.shardingsphere.datasource.slave4.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave4.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave4.jdbc-url=jdbc:mysql://192.168.181.137:3306/lagou1?characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.slave4.username=root
spring.shardingsphere.datasource.slave4.password=root

#master0
spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave1,slave2
spring.shardingsphere.sharding.master-slave-rules.master0.load-balance-algorithm-type=ROUND_ROBIN
#master1
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave3,slave4
spring.shardingsphere.sharding.master-slave-rules.master1.load-balance-algorithm-type=ROUND_ROBIN


#c_user 分库
spring.shardingsphere.sharding.tables.c_user.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.c_user.database-strategy.inline.algorithm-expression=master${id%2}
spring.shardingsphere.sharding.tables.c_user.key-generator.column=id
spring.shardingsphere.sharding.tables.c_user.key-generator.type=SNOWFLAKE


#c_order 分库+分表
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}

#c_order id生成
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
#spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.c_order.key-generator.type=MYKEY


#数据节点
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master${0..1}.c_order_${0..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}

```