简介
Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar,这 3 款相互独立，却又能够混合部署配合使用的产品组成。 它们均提供标准化的数据分片、分布式事务和数据库治理功能，目前，数据分片、读写分离、数据加密、影子库压测等功能，以及 MySQL、PostgreSQL、SQLServer、Oracle 等 SQL 与协议的支持，均通过插件的方式织入项目，ShardingSphere 已于2020年4月16日成为 Apache 软件基金会的顶级项目。
背景
公司项目随着业务数据不断增多，单表数量达到五百W以上，查询新增速度不断变慢，严重影响客户使用，分库分表势在必行，大概看了下相关的技术，果断选择了ShardingJDBC（使用人多，稳定成熟，加Apache加持），本文主要围绕ShardingSphere 做分库分表，简单介绍下ShardingSphere。
公司先做的分表，后来数据增多，存储磁盘满足不了单个数据库的大小，后来才做的分库，所以可以结合着自己的实际业务去做考虑是否做需要做分库，如果分表已经能满足自己的三年内的业务需求，可以不做分库操作，直接分表即可。
分片场景
| 策略 | 表名称 | 分片字段 | 表数量 |
|---|---|---|---|
| 某字段分库分表 | sh_in_conf | task_id | 6 |
| 某字段分库分表 | sh_in_log | company_id | 4 |
| 通用默认某字段分库分表 | sh_in_toll | biz_id | 2 |
| 不分库分表 | sh_in_info | 只需在ds0库存在即可 | 1 |
库结构
ds0库
ds1库
Pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.sharding.test</groupId>
<artifactId>sharding-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-demo</name>
<description>分库分表Demo</description>
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<sharding.jdbc.version>4.0.0</sharding.jdbc.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.github.ulisesbocchio</groupId>
<artifactId>jasypt-spring-boot-starter</artifactId>
<version>1.16</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.74</version>
</dependency>
<!-- 与swagger一起使用,需要注意-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.8.0</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.8.0</version>
</dependency>
<!--mybatis plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.0</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.3.0</version>
</dependency>
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.29</version>
</dependency>
<!--前端-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<!-- hutool 工具包-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>4.6.1</version>
</dependency>
<!-- lang包-->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.4</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
分片算法测试
public static void main(String[] args) {
//分表表数量
Integer table_num = 4;
//分库数量
Integer ds_num = 2;
//分片内容
String content = "a1922929222a1";
Integer table = HashUtil.fnvHash(content) % table_num;
Integer data = HashUtil.fnvHash(content) % ds_num;
System.out.println("存入表："+table);
System.out.println("存入库："+data);
}
application.properties
server.port=8080
spring.application.name=sharding-demo
swagger2.enable=true
spring.profiles.active=sharding-db-table
##分库 数据库为偶数，每张表则保留偶数表 数据库为奇数表，则表为奇数表
spring.shardingsphere.datasource.names=ds0,ds1
#################################################### ds0 ####################################################
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://172.21.16.52:3306/release_degree?characterEncoding=UTF-8&serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=dev
spring.shardingsphere.datasource.ds0.password=21758E78331B20E4
spring.shardingsphere.datasource.ds0.maximum-pool-size=128
spring.shardingsphere.datasource.ds0.connection-timeout=30000
spring.shardingsphere.datasource.ds0.max-lifetime=1200000
spring.shardingsphere.datasource.ds0.connection-test-query=SELECT 'X'
spring.shardingsphere.datasource.ds0.minimum-idle=10
######################################################## db1 ####################################################
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://172.21.16.52:3306/sco_logging_core?characterEncoding=UTF-8&serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=dev
spring.shardingsphere.datasource.ds1.password=21758E78331B20E4
spring.shardingsphere.datasource.ds1.maximum-pool-size=128
spring.shardingsphere.datasource.ds1.connection-timeout=30000
spring.shardingsphere.datasource.ds1.max-lifetime=1200000
spring.shardingsphere.datasource.ds1.connection-test-query=SELECT 'X'
spring.shardingsphere.datasource.ds1.minimum-idle=10
##是否打印SQL
spring.shardingsphere.props.sql.show=true
application-sharding-db-table.properties
#分库时使用
# db type mysql
#mybatis-plus.global-config.db-config.db-type=mysql
# default data source ds0
spring.shardingsphere.sharding.default-data-source-name=ds0
#####根据biz_id分片
#分表
spring.shardingsphere.sharding.tables.sh_in_conf.actual-data-nodes=ds$->{0..1}.sh_in_conf_$->{0..5}
spring.shardingsphere.sharding.tables.sh_in_conf.table-strategy.standard.sharding-column=task_id
spring.shardingsphere.sharding.tables.sh_in_conf.table-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.OtherPreciseShardingAlgorithm
#分库
spring.shardingsphere.sharding.tables.sh_in_conf.database-strategy.standard.sharding-column=task_id
spring.shardingsphere.sharding.tables.sh_in_conf.database-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.OtherPreciseShardingAlgorithm
#####根据company_id分片
#分表
spring.shardingsphere.sharding.tables.sh_in_log.actual-data-nodes=ds$->{0..1}.sh_in_log_$->{0..3}
spring.shardingsphere.sharding.tables.sh_in_log.table-strategy.standard.sharding-column=company_id
spring.shardingsphere.sharding.tables.sh_in_log.table-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.OtherPreciseShardingAlgorithm
#分库
spring.shardingsphere.sharding.tables.sh_in_log.database-strategy.standard.sharding-column=company_id
spring.shardingsphere.sharding.tables.sh_in_log.database-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.OtherPreciseShardingAlgorithm
####默认分片策略
spring.shardingsphere.sharding.tables.sh_in_toll.actual-data-nodes=ds$->{0..1}.sh_in_toll_$->{0..1}
####用于单分片键的标准分片场景
#默认表分片策略
spring.shardingsphere.sharding.default-table-strategy.standard.sharding-column=biz_id
spring.shardingsphere.sharding.default-table-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.DefaultPreciseShardingAlgorithm
#默认库分片策略
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=biz_id
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.sharding.test.sharding.DefaultPreciseShardingAlgorithm
spring.shardingsphere.sharding.binding-tables=sh_in_toll
自定义分片类
package com.sharding.test.sharding;
import cn.hutool.core.util.HashUtil;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
/**
* @author leijie.gao
* @since 2020-05-27
*/
public class DefaultPreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> {
/**
* @param targetTableNames sharding 逻辑表 分表规则
* @param shardingVal PreciseShardingValue(logicTableName=真实表, columnName=company_id, value=company_id_value)
* @return {{@link String}}
*/
@Override
public String doSharding(Collection<String> targetTableNames, PreciseShardingValue<String> shardingVal) {
final String[] tables = targetTableNames.toArray(new String[targetTableNames.size()]);
return ArrayUtils.isEmpty(tables) ? tables[0] : tables[HashUtil.fnvHash(shardingVal.getValue()) % targetTableNames.size()];
}
}
结束语
- 每张表可以按照不同的分片策略，保存或者更新查询操作时，必须带着分片策略的条件。
- 建议分表规则和分库规则一致，代码通用率高
- 建表时,建议为偶数表,方便分库时，为数据迁移做准备，后期分库时，只需要在偶数库中保留偶数表数据，奇数库中保留奇数表数据
- 案例中为了方便，其实分片策略确定后，偶数库中保留偶数表，奇数库中保留奇数表即可，保留也不影响
