需求
采⽤Sharding-JDBC实现c_order表分库分表+读写分离
- 基于user_id对c_order表进⾏数据分⽚
- 分别对master1和master2搭建⼀主⼆从架构
- 基于master1和master2主从集群实现读写分离
- c_order建表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;
分析
我们需要对 c_order表进行分片,需求中并没有详细描述具体分片要求,所以我们可以采用分库,再分表对其中的数据进行分片。其次对于读写分离,Sharding-JDBC也有相应的配置。对于这六个数据库我们不需要做读写分离的操作,我们相当于是在程序端控制读写分离。
操作
1.首先在两个master 库创建表,然后创建idea项目,添加相关依赖
父工程
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.compile.sourceEncoding>UTF-8</project.compile.sourceEncoding>
<shardingsphere.version>4.1.0</shardingsphere.version>
<springboot.version>2.2.5.RELEASE</springboot.version>
</properties>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>${springboot.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>11</source>
<target>11</target>
<testSource>11</testSource>
<testTarget>11</testTarget>
</configuration>
<version>3.8.1</version>
</plugin>
</plugins>
</build>
子工程
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-transaction-xa-core</artifactId>
<version>4.0.0-RC2</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-transaction-base-saga</artifactId>
<version>4.0.0-RC2</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
2.添加jpa对象
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
@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 Integer 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;
}
3.创建Repository
import entity.COrder;
import org.springframework.data.jpa.repository.JpaRepository;
public interface COrderRepository extends JpaRepository<COrder,Long> {
}
4.先分库
#datasource 配置源
spring.shardingsphere.datasource.names=master0,master1
#分布配置相关得数据源连接参数
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.192.130:3306/lagou
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=root
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.192.129:3306/lagou
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root
#sharding-database 针对库的分库策略配置
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}
#配置主建生成策略
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
5.再分表,需要创建c_order0,c_order1两张表
#datasource 配置源
spring.shardingsphere.datasource.names=master0,master1
#分布配置相关得数据源连接参数
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.192.130:3306/lagou
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=root
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.192.129:3306/lagou
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root
#sharding-database 针对库的分库策略配置
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}
#sharding-database 针对库的分表策略配置
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}
#配置虚拟表与物理表的映射
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master${0..1}.c_order${0..1}
#配置主建生成策略
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
6.实现读写分离
#datasource 配置源
spring.shardingsphere.datasource.names=master0,master1,slave0,slave1,slave2,slave3
#分布配置相关得数据源连接参数
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.192.130:3306/lagou
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=root
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.192.129:3306/lagou
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://192.168.192.131:3306/lagou
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.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.192.132:3306/lagou
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root
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.192.134:3306/lagou
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.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.192.135:3306/lagou
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root
#sharding-database 针对库的分库策略配置
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}
#sharding-database 针对库的分表策略配置
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}
#配置虚拟表与物理表的映射
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master${0..1}.c_order${0..1}
#配置主建生成策略
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.master0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave0, slave1
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave2, slave3