Sharding-jdbc实现分库分表+读写分离
1 环境介绍
软件版本
1. VMware 15 pro
2. 虚机系统 SentOS7
3. shell工具 FinalShell3.6.2
4. mysql5.7.31
虚机作用
| 角色 | IP | 主机名 | server_id | 作用 |
|---|---|---|---|---|
| Master1 | 192.168.1.201 | myql-master | 1 | 主库-写请求 |
| Slave1 | 192.168.1.202 | myql-slave1 | 2 | 从库-读请求,已开启bin-log |
| Slave2 | 192.168.1.203 | myql-slave2 | 3 | 从库-读请求,已开启bin-log |
| Master2 | 192.168.1.211 | myql-master | 1 | 主库-写请求 |
| Slave3 | 192.168.1.212 | myql-slave1 | 2 | 从库-读请求,已开启bin-log |
| Slave4 | 192.168.1.213 | myql-slave2 | 3 | 从库-读请求,已开启bin-log |
2 项目介绍
-
引入依赖
<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> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> </dependency> </dependencies> </dependencyManagement> 配置实体+DAO
@Data
@Entity
@Table(name = "c_order",
indexes = {@Index(name = "idx_userId_positionId", columnList = "user_id,position_id"),
@Index(name = "idx_userId_operateTime", columnList = "user_id,update_time")} )
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;
//简历类型： 0附件 1在线
@Column(name = "resume_type")
private Integer resumeType;
// 投递状态 投递状态
// WAIT-待处理 AUTO_FILTER-⾃动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝
// ARRANGE_INTERVIEW-通知⾯试
@Column(name = "status")
private String status;
@Column(name = "create_time")
private Date createTime;
@Column(name = "`update_time`")
private Date updateTime;
}
public interface COrderRepository extends JpaRepository<COrder, Long> {
}
-
项目启动
import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication public class RunBoot { }
- 单元测试
import com.dd.edu.entity.COrder;
import com.dd.edu.repository.COrderRepository;
import org.apache.shardingsphere.core.strategy.keygen.SnowflakeShardingKeyGenerator;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Repeat;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
import java.util.Random;
@RunWith(SpringRunner.class)
@SpringBootTest
public class ShardingMasterSlaveTest {
@Resource
private COrderRepository orderRepository;
@Test
public void testShardingCOrder(){
Random random = new Random();
for (int i = 0; i < 30; i++) {
int companyId = random.nextInt(10);
int userId = random.nextInt(10);
COrder order = new COrder();
order.setIsDel(false);
order.setCompanyId(companyId);
order.setPositionId(3242342);
order.setUserId(userId);
order.setPublishUserId(1111);
order.setResumeType(1);
order.setStatus("AUTO");
order.setCreateTime(new Date());
order.setUpdateTime(new Date());
orderRepository.save(order);
}
testShardingDataQuery();
}
@Test
public void testShardingDataQuery(){
System.out.println("______________________查询所有结果数据_______________________");
List<COrder> all = orderRepository.findAll();
for (COrder cOrder : all) {
System.out.println(cOrder);
}
}
}
- sharding-jdbc配置
#数据源
spring.shardingsphere.datasource.names=master0,slave1,slave2,master1,slave3,slave4
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.1.201:3306/master?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.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.1.202:3306/master
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.1.203:3306/master
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.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.1.211:3306/master?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.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.1.212:3306/master
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root
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.1.213:3306/master
spring.shardingsphere.datasource.slave4.username=root
spring.shardingsphere.datasource.slave4.password=root
#分库分表
#id : 通过雪花算法自动生成主键
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
## 分库字段 ： 根据id取模2，id为奇数的存入master1，偶数的存入master0
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master${id % 2 }
## 这里配置实际对应的数据库节点与表，所有的库与表必须实际存在，否则会抛异常：no table route info
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master${0..1}.c_order${0..1}
## 分表字段 ： 根据user_id取模2，id为奇数的存入c_order1，偶数的存入c_order0
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order${user_id % 2}
#读写分离：物理主从数据库配置
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.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave3,slave4
3 项目验证
执行单元测试方法：com.dd.edu.ShardingMasterSlaveTest#testShardingCOrder()
可以看到如下日志信息：
2020-08-04 21:55:56.266 INFO 37160 --- [main] ShardingSphere-SQL : Logic SQL: insert into c_order (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
2020-08-04 21:55:56.267 INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: master0 ::: insert into c_order0 (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [3, 2020-08-04 21:55:55.464, false, 3242342, 1111, 1, AUTO, 2020-08-04 21:55:55.464, 8, 497527317707358208]
2020-08-04 21:55:56.341 INFO 37160 --- [main] ShardingSphere-SQL : Logic SQL: insert into c_order (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
2020-08-04 21:55:56.342 INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: master1 ::: insert into c_order0 (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [9, 2020-08-04 21:55:56.339, false, 3242342, 1111, 1, AUTO, 2020-08-04 21:55:56.339, 6, 497527318495887361]
2020-08-04 21:55:56.363 INFO 37160 --- [main] ShardingSphere-SQL : Logic SQL: insert into c_order (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
2020-08-04 21:55:56.364 INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: master0 ::: insert into c_order1 (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [7, 2020-08-04 21:55:56.361, false, 3242342, 1111, 1, AUTO, 2020-08-04 21:55:56.361, 9, 497527318588162048]
可以看出执行插入的sql，会插入到master主库中；根据id字段值的奇数偶数的不同，自动解析到 Actual SQL: master0 或者 Actual SQL: master1 ；根据user_id字段值的奇数偶数的不同，自动解析到 c_order0 或 c_order1 。
______________________查询所有结果数据_______________________
2020-08-04 21:55:56.755 INFO 37160 --- [main] ShardingSphere-SQL : Logic SQL: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.`update_time` as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order corder0_
2020-08-04 21:55:56.756 INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: slave1 ::: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.`update_time` as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order0 corder0_
2020-08-04 21:55:56.756 INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: slave2 ::: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.`update_time` as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order1 corder0_
2020-08-04 21:55:56.756 INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: slave3 ::: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.`update_time` as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order0 corder0_
2020-08-04 21:55:56.756 INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: slave4 ::: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.`update_time` as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order1 corder0_
在查询所有数据时，会从所有的从库中查询数据，然后组合后返回给查询客户端；以上日志将查询sql，分解为4个从库查询sql；关键点Actual SQL: slave1 Actual SQL: slave2 Actual SQL: slave3 Actual SQL: slave4
