携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第16天，点击查看活动详情
概述
传统的将数据集中存储至单一数据节点的解决方案，在性能、可用性和运维成本这三方面已经难于满足互联网的海量数据场景。
数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中以达到提升性能瓶颈以及可用性的效果。 数据分片的有效手段是对关系型数据库进行分库和分表。分库和分表均可以有效的避免由数据量超过可承受阈值而产生的查询瓶颈。
分库还能够用于有效的分散对数据库单点的访问量；分表虽然无法缓解数据库压力，但却能够提供尽量将分布式事务转化为本地事务的可能，一旦涉及到跨库的更新操作，分布式事务往往会使问题变得复杂。 使用多主多从的分片方式，可以有效的避免数据单点，从而提升数据架构的可用性。
通过分库和分表进行数据的拆分来使得各个表的数据量保持在阈值以下，以及对流量进行疏导应对高访问量，是应对高并发和海量数据系统的有效手段。
环境说明
- Spring boot 2.5.3
- Druid 1.1.22
- mybatis-plus 3.4.3
- ShardingSphere 4.1.1
- MySQL 5.7.27
数据分片配置
- 引入依赖
<!-- sharding-jdbc for spring boot 4.1.1 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
- 添加数据源配置文件
需要删除掉单库单表的数据源配置信息。
# 数据源配置
spring.shardingsphere.datasource.names=ds0,ds1,ds2,ds3,ds4,ds5,ds6,ds7
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/order_db_0?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=xxx
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/order_db_1?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=xxx
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/order_db_2?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=xxx
spring.shardingsphere.datasource.ds3.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds3.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/order_db_3?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds3.username=root
spring.shardingsphere.datasource.ds3.password=xxx
spring.shardingsphere.datasource.ds4.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds4.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds4.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/order_db_4?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds4.username=root
spring.shardingsphere.datasource.ds4.password=xxx
spring.shardingsphere.datasource.ds5.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds5.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds5.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/order_db_5?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds5.username=root
spring.shardingsphere.datasource.ds5.password=xxx
spring.shardingsphere.datasource.ds6.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds6.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds6.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/order_db_6?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds6.username=root
spring.shardingsphere.datasource.ds6.password=xxx
spring.shardingsphere.datasource.ds7.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds7.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds7.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/order_db_7?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds7.username=root
spring.shardingsphere.datasource.ds7.password=xxx
spring.shardingsphere.sharding.tables.order_info_sharded_by_user_id_.actual-data-nodes=ds$->{0..7}.order_info_sharded_by_user_id_$->{0..7}
spring.shardingsphere.sharding.tables.order_info_sharded_by_user_id_.database-strategy.complex.sharding-columns=order_no,user_id
spring.shardingsphere.sharding.tables.order_info_sharded_by_user_id_.database-strategy.complex.algorithm-class-name=com.xinxin.order.sharding.algorithm.OrderDbShardingByUserAlgorithm
spring.shardingsphere.sharding.tables.order_info_sharded_by_user_id_.table-strategy.complex.sharding-columns=order_no,user_id
spring.shardingsphere.sharding.tables.order_info_sharded_by_user_id_.table-strategy.complex.algorithm-class-name=com.xinxin.order.sharding.algorithm.OrderTableShardingUserAlgorithm
spring.shardingsphere.sharding.tables.order_info_sharded_by_user_id_.key-generator.column=id
spring.shardingsphere.sharding.tables.order_info_sharded_by_user_id_.key-generator.type=SNOWFLAKE
#spring.shardingsphere.sharding.tables.order_info_sharded_by_user_id_.key-generator.props.worker.id=${workerId}
#spring.shardingsphere.sharding.tables.order_info_sharded_by_user_id_.key-generator.props.max.tolerate.time.difference.milliseconds=5
spring.shardingsphere.sharding.tables.order_item_detail_sharded_by_user_id_.actual-data-nodes=ds$->{0..7}.order_item_detail_sharded_by_user_id_$->{0..7}
spring.shardingsphere.sharding.tables.order_item_detail_sharded_by_user_id_.database-strategy.complex.sharding-columns=order_no,user_id
spring.shardingsphere.sharding.tables.order_item_detail_sharded_by_user_id_.database-strategy.complex.algorithm-class-name=com.xinxin.order.sharding.algorithm.OrderDbShardingByUserAlgorithm
spring.shardingsphere.sharding.tables.order_item_detail_sharded_by_user_id_.table-strategy.complex.sharding-columns=order_no,user_id
spring.shardingsphere.sharding.tables.order_item_detail_sharded_by_user_id_.table-strategy.complex.algorithm-class-name=com.xinxin.order.sharding.algorithm.OrderTableShardingUserAlgorithm
spring.shardingsphere.sharding.tables.order_item_detail_sharded_by_user_id_.key-generator.column=id
spring.shardingsphere.sharding.tables.order_item_detail_sharded_by_user_id_.key-generator.type=SNOWFLAKE
#spring.shardingsphere.sharding.tables.order_item_detail_sharded_by_user_id_.key-generator.props.worker.id=${workerId}
#spring.shardingsphere.sharding.tables.order_item_detail_sharded_by_user_id_.key-generator.props.max.tolerate.time.difference.milliseconds=5
spring.shardingsphere.props.sql.show=true
- 过滤掉默认数据源
主要是过滤掉DruidDataSourceAutoConfigure.class、DataSourceAutoConfiguration.class
@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class, DataSourceAutoConfiguration.class})
public class OrderApplication {
public static void main(String[] args) {
SpringApplication.run(OrderApplication.class, args);
}
}
- 自定义分库策略
采用多分片建复合分片的的方式，实现ComplexKeysShardingAlgorithm接口，重写doSharding()方法。
package com.xinxin.order.sharding.algorithm;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import java.util.Collection;
import java.util.Set;
import java.util.stream.Collectors;
@Slf4j
public class OrderDbShardingByUserAlgorithm implements ComplexKeysShardingAlgorithm<Comparable<?>> {
@Override
public Collection<String> doSharding(Collection<String> dbs, ComplexKeysShardingValue<Comparable<?>> complexKeysShardingValue) {
log.info("doSharding() called with parameters => 【dbs = {}】, 【complexKeysShardingValue = {}】", dbs, complexKeysShardingValue);
Collection<Comparable<?>> orderNos = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("order_no");
Collection<Comparable<?>> userIds = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("user_id");
Set<String> actualDbNames = null;
if (CollectionUtils.isNotEmpty(orderNos)) {
actualDbNames = orderNos.stream()
.map(orderNo -> getActualDbName(String.valueOf(orderNo), dbs))
.collect(Collectors.toSet());
} else if (CollectionUtils.isNotEmpty(userIds)) {
actualDbNames = userIds.stream()
.map(userId -> getActualDbName(String.valueOf(userId), dbs))
.collect(Collectors.toSet());
}
return actualDbNames;
}
private String getActualDbName(String shardingValue, Collection<String> dbs) {
String userIdSuffix = StringUtils.substring(shardingValue, shardingValue.length() - 3);
int dbSuffix = userIdSuffix.hashCode() % dbs.size();
log.info("" + dbSuffix);
for (String db : dbs) {
if (db.endsWith(String.valueOf(dbSuffix))) {
return db;
}
}
return null;
}
}
- 自定义分表策略
采用多分片建复合分片的的方式，实现ComplexKeysShardingAlgorithm接口，重写doSharding()方法。
package com.xinxin.order.sharding.algorithm;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import java.util.Collection;
import java.util.Set;
import java.util.stream.Collectors;
@Slf4j
public class OrderTableShardingUserAlgorithm implements ComplexKeysShardingAlgorithm<Comparable<?>> {
@Override
public Collection<String> doSharding(Collection<String> tables, ComplexKeysShardingValue<Comparable<?>> complexKeysShardingValue) {
log.info("doSharding() called with parameters => 【tables = {}】, 【complexKeysShardingValue = {}】", tables, complexKeysShardingValue);
Collection<Comparable<?>> orderNos = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("order_no");
Collection<Comparable<?>> userIds = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("user_id");
Set<String> actualTables = null;
if (CollectionUtils.isNotEmpty(orderNos)) {
actualTables = orderNos.stream()
.map(orderNo -> getActualTables(String.valueOf(orderNo), tables))
.collect(Collectors.toSet());
} else if (CollectionUtils.isNotEmpty(userIds)) {
actualTables = userIds.stream()
.map(userId -> getActualTables(String.valueOf(userId), tables))
.collect(Collectors.toSet());
}
return actualTables;
}
private String getActualTables(String shardingValue, Collection<String> tables) {
String userIdSuffix = StringUtils.substring(shardingValue, shardingValue.length() - 3);
int tableSuffix = userIdSuffix.hashCode() / tables.size() % tables.size();
log.info("" + tableSuffix);
for (String table : tables) {
if (table.endsWith(String.valueOf(tableSuffix))) {
return table;
}
}
return null;
}
}
- 修改表名称
将mapper等书写的SQL中的表名称修改为逻辑表名称，比如数据库中的表是order_info_sharded_by_user_id_0、order_info_sharded_by_user_id_1、order_info_sharded_by_user_id_2 ......为实际的表名称，那么SQL中的表名称就需要替换成order_info_sharded_by_user_id_。
启动项目
可以看到项目启动过程中初始化了所有的数据源、读取了分片规则配置、其他属性配置。
2022-08-10 10:29:19.701 INFO 6590 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2022-08-10 10:29:19.922 INFO 6590 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-2} inited
2022-08-10 10:29:20.098 INFO 6590 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-3} inited
2022-08-10 10:29:20.267 INFO 6590 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-4} inited
2022-08-10 10:29:20.450 INFO 6590 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-5} inited
2022-08-10 10:29:20.616 INFO 6590 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-6} inited
2022-08-10 10:29:20.796 INFO 6590 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-7} inited
2022-08-10 10:29:20.969 INFO 6590 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-8} inited
2022-08-10 10:29:21.245 INFO 6590 --- [ main] o.a.s.core.log.ConfigurationLogger : ShardingRuleConfiguration:
tables:
order_info_sharded_by_user_id_:
actualDataNodes: ds$->{0..7}.order_info_sharded_by_user_id_$->{0..7}
databaseStrategy:
complex:
algorithmClassName: com.xinxin.order.sharding.algorithm.OrderDbShardingByUserAlgorithm
shardingColumns: order_no,user_id
keyGenerator:
column: id
type: SNOWFLAKE
logicTable: order_info_sharded_by_user_id_
tableStrategy:
complex:
algorithmClassName: com.xinxin.order.sharding.algorithm.OrderTableShardingUserAlgorithm
shardingColumns: order_no,user_id
order_item_detail_sharded_by_user_id_:
actualDataNodes: ds$->{0..7}.order_item_detail_sharded_by_user_id_$->{0..7}
databaseStrategy:
complex:
algorithmClassName: com.xinxin.order.sharding.algorithm.OrderDbShardingByUserAlgorithm
shardingColumns: order_no,user_id
keyGenerator:
column: id
type: SNOWFLAKE
logicTable: order_item_detail_sharded_by_user_id_
tableStrategy:
complex:
algorithmClassName: com.xinxin.order.sharding.algorithm.OrderTableShardingUserAlgorithm
shardingColumns: order_no,user_id
2022-08-10 10:29:21.247 INFO 6590 --- [ main] o.a.s.core.log.ConfigurationLogger : Properties:
sql.show: 'true'
2022-08-10 10:31:29.724 INFO 6611 --- [ main] ShardingSphere-metadata : Loading 2 logic tables' meta data.
2022-08-10 10:31:29.935 INFO 6611 --- [ main] ShardingSphere-metadata : Meta data load finished, cost 343 milliseconds.
写入数据
写入数据的过程中，会先调用自定义的分库OrderDbShardingByUserAlgorithm.java类获取到实际的库，然后在调用自定义的分表类OrderTableShardingUserAlgorithm.java获取到实际的表，然后改写sql行sql。
查询数据
查询的sql改写逻辑和写入数据的改写逻辑一样，都是先计算库然后在计算表。因为外面订单系统C端的的分片键选择的是user_id，所以写入和查询的user_id的值相同的时候，数据库分片结果、数据表分片结果都是一样的。
