# 数据库拆分
**Repository Path**: cllyl/database-split
## Basic Information
- **Project Name**: 数据库拆分
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No
## Statistics
- **Stars**: 0
- **Forks**: 0
- **Created**: 2020-11-21
- **Last Updated**: 2020-12-19
## Categories & Tags
**Categories**: Uncategorized
**Tags**: None
## README
# MySQL分库分表实战
## 背景分析
#### 问题分析
- 用户请求并发量较大（对数据库的读写请求过多）
- 服务器TPS（Transaction Per Second）、QPS（Query Per Second）上限
- 服务器内存上限
- 服务器IO上限
- 单库数据量过大（表太多、单表数据量太大）导致占用空间大
- 单库对应的服务器的磁盘空间上限
- 单库的处理能力上限
- 单表的处理能力上限
- 单表数据量过大（记录数对、字段多）导致的单表占用空间大
- DML（Data Manipulation Language）数据操作(增、删、改)变慢
- DQL（Data Query Language）数据查询变慢
- DDL（Data Definition Language）添加索引、添加字段变慢
- 数据迁移大问题
#### 主从架构下的读写分离
- 每一个数据库中的数据都一致，每个数据库中的数据都是全部的数据
- 因为分离了读、写操作，将读写操作分离到不同的服务器（数据库）上，一定程度上提高了程序的写能力
- 主要解决了查询性能问题
#### 分库分表之数据分片
**数据节点**：分片之后的表的基本单元
**完整性**：字段完整性、表完整性
**记录数**：表中的记录的数量
分库分表的分片，可以分为水平分片，垂直分片。不论哪种分片方式，都会造成数据库的不完整。
**分库分表之后可能会出现的问题**
1. 分布式事务问题：跨多个库的事务
2. 跨库、跨表关联问题
3. 数据管理、运维问题
- 水平分库
- 将同一个库中的数据，分散到多个数据库中，所有数据库数据总和才是全部的数据
- 垂直分库
- 根据业务对数据库进行拆分，相应的对应用程序根据业务进行分模块，保证每个模块只使用一个数据库
- 水平分表
- 由于一张表中数据量过大，将一张表的数据拆分到多张表中，这些表都在同一个数据库中。可以进行表的union合并。
- 垂直分表
- 由于一张表中的字段过多，或者表中包含不常用的大字段，对表进行垂直拆分，拆分之后，表字段总和才是总字段
## 场景描述
当前项目为学习在项目中进行MySQL数据库水平分库分表。
分片规则(strategy) = 分片键(table_column) + 分片算法(algorithm)
#### 常见分片算法
###### 范围分片（RANGE）
- 根据时间进行分片：年、月、周、天等
- 地域：国家、区域（华南、华北）、省、市、县等
- 大小：1---1000万、1000万---2000万等
###### 等值分片（HASH）
- 根据ID取模：id % 8、id % 10等
- 根据某一个批次：batch = 201、batch=202等
###### 复杂业务场景分析
在单个分片键不能满足业务常见的情况，一般会有一些两种解决方案
- 复合分片键
- 进行数据分片的时候，根据多个分片键进行拆分写入**数据节点**
- 数据冗余
- 全部数据冗余
按照业务场景，对数据记录进行单个分片键分片，对不同的分片键都进行分片存储
- 关联关系冗余
按照业务场景，对多个分片键之间的关联关系进行冗余存储，业务上如果需要用到辅助分片键进行查询的时候，先根据分片键之间的关联关系查询出主分片键，然后根据主分片键进行查询。
#### 水平分库分表结果表现如下
用户端c_order分库分表
- 根据用户ID进行分库
- 根据用户ID对2进行取模，
- 根据订单ID进行分表
- 根据订单ID对2进行取模
- 查询说明
- 只有用户ID：不能定位到唯一数据节点，会出现同一个库内数据查询，多个表之间查询结果集合并
- 只有订单ID：不能定位到唯一数据节点，会出现不同库之间查询，多个库之间的查询结果集合并
- 用户ID+订单ID：可以定位到唯一的数据节点，实现单表查询
## 开发过程
#### sharding-jdbc-exampl模块开发
###### 1. 数据库服务器说明
| 服务器 | 描述信息 |
| ------------------ | ----------------------- |
| 192.168.0.120:3306 | YL-MASTER:分库1的主库 |
| 192.168.0.121:3306 | YL-SLAVE1:分库1的从库1 |
| 192.168.0.122:3306 | YL-SLAVE2:分库1的主库2 |
| 192.168.0.110:3306 | CLL-MASTER:分库2的主库 |
| 192.168.0.111:3306 | CLL-SLAVE1:分库2的从库1 |
| 192.168.0.112:3306 | CLL-SLAVE2:分库2的主库2 |
###### 2. 创建项目
开发项目的时候，通常情况下通用引入。这里是根据使用情况，尽可能少的引入第三方JAR。
> 第三方依赖以及版本说明
```xml
UTF-8
UTF-8
1.8
1.8
2.2.5.RELEASE
1.18.16
8.0.22
1.2.3
4.1.1
```
> pom.xml配置文件
```xml
database-split
com.cll.prototype
1.0-SNAPSHOT
4.0.0
sharding-jdbc-example
org.springframework.boot
spring-boot-starter-jdbc
org.springframework.boot
spring-boot-starter-data-jpa
slf4j-api
org.slf4j
mysql
mysql-connector-java
org.apache.shardingsphere
sharding-jdbc-spring-boot-starter
slf4j-api
org.slf4j
snakeyaml
org.yaml
org.springframework.boot
spring-boot-starter-logging
slf4j-api
org.slf4j
org.springframework.boot
spring-boot-starter-test
test
jakarta.activation-api
jakarta.activation
byte-buddy
net.bytebuddy
junit-jupiter-api
org.junit.jupiter
org.projectlombok
lombok
provided
org.apache.maven.plugins
maven-compiler-plugin
1.8
1.8
1.8
1.8
```
> 启动类
```java
package com.cll.prototype.sharding.jdbc;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* 描述信息:
*
* @author CLL
* @version 1.0
* @date 2020/11/21 22:44
*/
@SpringBootApplication
public class ShardingJdbcApplication {
public static void main(String[] args) {
}
}
```
> 核心配置文件application.properties
```properties
# 指定当前使用的辅助配置文件后缀名称
spring.profiles.active=sharding-database-master-slave
# 显示SQL
spring.shardingsphere.props.sql.show=true
```
> 分库分表+读写分离配置文件
```properties
# 分库示例
# 数据源配置
# 指定两个逻辑数据库
spring.shardingsphere.datasource.names=m0,s0,s1,m1,s2,s3
# 分库的主库配置
spring.shardingsphere.datasource.m0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.jdbc-url=jdbc:mysql://192.168.0.120:3306/db_split2?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Hongkong
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
spring.shardingsphere.datasource.s0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.jdbc-url=jdbc:mysql://192.168.0.121:3306/db_split2?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Hongkong
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=root
spring.shardingsphere.datasource.s1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s1.jdbc-url=jdbc:mysql://192.168.0.122:3306/db_split2?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Hongkong
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=root
# 分库的主库配置
spring.shardingsphere.datasource.m1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.jdbc-url=jdbc:mysql://192.168.0.110:3306/db_split1?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Hongkong
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
spring.shardingsphere.datasource.s2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.s2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s2.jdbc-url=jdbc:mysql://192.168.0.111:3306/db_split1?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Hongkong
spring.shardingsphere.datasource.s2.username=root
spring.shardingsphere.datasource.s2.password=root
spring.shardingsphere.datasource.s3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.s3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s3.jdbc-url=jdbc:mysql://192.168.0.112:3306/db_split1?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Hongkong
spring.shardingsphere.datasource.s3.username=root
spring.shardingsphere.datasource.s3.password=root
# 对用户版订单表订单表(c_order)进行分库之后分表配置
# 1. 先进行分库配置，基于用户ID进行分库配置，保证一个用户的订单只会出现在一个库里面
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=m$->{user_id % 2}
# 2. 再进行分表配置，基于订单ID进行分表配置，不能保证一个用户的订单只出现在一个表里面
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}
# 3. 配置真实的数据节点
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=m${0..1}.c_order${0..1}
# 4. 配置订单表的主键生成策略
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
# 5. 配置分库分表下的读写分离
# 指定当前分库数据源的的名称
#spring.shardingsphere.sharding.master-slave-rules.m0.name=db0
# 指定分库数据源的主节点
spring.shardingsphere.sharding.master-slave-rules.m0.master-data-source-name=m0
# 指定分库数据源的从节点
spring.shardingsphere.sharding.master-slave-rules.m0.slave-data-source-names=s0,s1
spring.shardingsphere.sharding.master-slave-rules.m0.load-balance-algorithm-type=ROUND_ROBIN
spring.shardingsphere.sharding.master-slave-rules.m1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.m1.slave-data-source-names=s2,s3
spring.shardingsphere.sharding.master-slave-rules.m1.load-balance-algorithm-type=ROUND_ROBIN
```
> 实体类
```java
package com.cll.prototype.sharding.jdbc.entity;
import lombok.Data;
import javax.persistence.*;
import java.util.Date;
/**
* 描述信息:
* 基于用户的订单表
* @author CLL
* @version 1.0
* @date 2020/11/22 15:03
*/
@Data
@Entity
@Table(name = "c_order")
public class COrder {
@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;
}
```
> 持久层
```java
package com.cll.prototype.sharding.jdbc.repository;
import com.cll.prototype.sharding.jdbc.entity.COrder;
import org.springframework.data.jpa.repository.JpaRepository;
public interface COrderRepository extends JpaRepository {
}
```
> 单元测试类
```java
package com.cll.prototype.sharding.jdbc.repository;
import com.cll.prototype.sharding.jdbc.entity.COrder;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Example;
import org.springframework.test.annotation.Repeat;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.Date;
import java.util.Optional;
import java.util.Random;
/**
* 描述信息:
*
* @author CLL
* @version 1.0
* @date 2020/11/22 14:56
*/
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class COrderRepositoryTest {
private static final Logger logger = LoggerFactory.getLogger(COrderRepositoryTest.class);
@Autowired
private COrderRepository cOrderRepository;
@Test
@Repeat(100)
public void testAddCOrder() {
Random random = new Random();
int userId = random.nextInt(10);
COrder order = new COrder();
order.setIsDel(false);
order.setCompanyId(33333);
order.setPositionId(3242342L);
order.setUserId(userId);
order.setPublishUserId(1111);
order.setResumeType(1);
order.setStatus("AUTO");
order.setCreateTime(new Date());
order.setUpdateTime(new Date());
COrder save = cOrderRepository.save(order);
logger.info("===>>> save c_order result = {}", save);
}
@Test
public void getCOrderByUserId(){
// 分库分表之后，只有用户ID+订单ID才能唯一确定一个表进行查询，才不会出现结果集合并
COrder queryParam = new COrder();
queryParam.setUserId(1);
Example example = Example.of(queryParam);
Optional cOrder = cOrderRepository.findOne(example);
logger.info("===>>> get result = {}", cOrder);
}
@Test
public void getCOrderById(){
// 分库分表之后，只有用户ID+订单ID才能唯一确定一个表进行查询，才不会出现结果集合并
COrder queryParam = new COrder();
queryParam.setId(537303916795133952L);
Example example = Example.of(queryParam);
Optional cOrder = cOrderRepository.findOne(example);
logger.info("===>>> get result = {}", cOrder);
}
@Test
public void getCOrderByIdAndUserId(){
// 分库分表之后，只有用户ID+订单ID才能唯一确定一个表进行查询，才不会出现结果集合并
COrder queryParam = new COrder();
queryParam.setUserId(1);
queryParam.setId(537303916795133952L);
Example example = Example.of(queryParam);
Optional cOrder = cOrderRepository.findOne(example);
logger.info("===>>> get result = {}", cOrder);
}
}
```
> 单元测试日志信息
> 新增数据
```sql
2020-11-22 16:45:04.103 INFO 16228 --- [ 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-11-22 16:45:04.104 INFO 16228 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: insert into c_order0 (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, update_time, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [33333, 2020-11-22 16:45:04.048, false, 3242342, 1111, 1, AUTO, 2020-11-22 16:45:04.048, 5, 537311750526074880]
2020-11-22 16:45:04.144 INFO 16228 --- [ main] ShardingSphere-SQL : Actual SQL: m0 ::: insert into c_order1 (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, update_time, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [33333, 2020-11-22 16:45:04.142, false, 3242342, 1111, 1, AUTO, 2020-11-22 16:45:04.142, 0, 537311750727401473]
2020-11-22 16:45:04.163 INFO 16228 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: insert into c_order1 (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, update_time, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [33333, 2020-11-22 16:45:04.161, false, 3242342, 1111, 1, AUTO, 2020-11-22 16:45:04.161, 5, 537311750807093249]
2020-11-22 16:45:04.206 INFO 16228 --- [ main] ShardingSphere-SQL : Actual SQL: m0 ::: insert into c_order0 (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, update_time, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [33333, 2020-11-22 16:45:04.204, false, 3242342, 1111, 1, AUTO, 2020-11-22 16:45:04.204, 6, 537311750987448320]
```
> 根据主键查询
```sql
2020-11-22 16:45:03.964 INFO 16228 --- [ main] ShardingSphere-SQL : Logic SQL: select corder0_.id as id1_1_, corder0_.company_id as company_2_1_, corder0_.create_time as create_t3_1_, corder0_.is_del as is_del4_1_, corder0_.position_id as position5_1_, corder0_.publish_user_id as publish_6_1_, corder0_.resume_type as resume_t7_1_, corder0_.status as status8_1_, corder0_.update_time as update_t9_1_, corder0_.user_id as user_id10_1_ from c_order corder0_ where corder0_.id=537303916795133952
2020-11-22 16:45:03.965 INFO 16228 --- [ main] ShardingSphere-SQL : Actual SQL: s0 ::: select corder0_.id as id1_1_, corder0_.company_id as company_2_1_, corder0_.create_time as create_t3_1_, corder0_.is_del as is_del4_1_, corder0_.position_id as position5_1_, corder0_.publish_user_id as publish_6_1_, corder0_.resume_type as resume_t7_1_, corder0_.status as status8_1_, corder0_.update_time as update_t9_1_, corder0_.user_id as user_id10_1_ from c_order0 corder0_ where corder0_.id=537303916795133952
2020-11-22 16:45:03.965 INFO 16228 --- [ main] ShardingSphere-SQL : Actual SQL: s2 ::: select corder0_.id as id1_1_, corder0_.company_id as company_2_1_, corder0_.create_time as create_t3_1_, corder0_.is_del as is_del4_1_, corder0_.position_id as position5_1_, corder0_.publish_user_id as publish_6_1_, corder0_.resume_type as resume_t7_1_, corder0_.status as status8_1_, corder0_.update_time as update_t9_1_, corder0_.user_id as user_id10_1_ from c_order0 corder0_ where corder0_.id=537303916795133952
```
> 根据用户ID和主键ID查询
```sql
2020-11-22 16:53:22.505 INFO 13664 --- [ main] ShardingSphere-SQL : Logic SQL: select corder0_.id as id1_1_, corder0_.company_id as company_2_1_, corder0_.create_time as create_t3_1_, corder0_.is_del as is_del4_1_, corder0_.position_id as position5_1_, corder0_.publish_user_id as publish_6_1_, corder0_.resume_type as resume_t7_1_, corder0_.status as status8_1_, corder0_.update_time as update_t9_1_, corder0_.user_id as user_id10_1_ from c_order corder0_ where corder0_.id=537303916795133952 and corder0_.user_id=1
2020-11-22 16:53:22.506 INFO 13664 --- [ main] ShardingSphere-SQL : Actual SQL: s2 ::: select corder0_.id as id1_1_, corder0_.company_id as company_2_1_, corder0_.create_time as create_t3_1_, corder0_.is_del as is_del4_1_, corder0_.position_id as position5_1_, corder0_.publish_user_id as publish_6_1_, corder0_.resume_type as resume_t7_1_, corder0_.status as status8_1_, corder0_.update_time as update_t9_1_, corder0_.user_id as user_id10_1_ from c_order0 corder0_ where corder0_.id=537303916795133952 and corder0_.user_id=1
```