一、Sharding-jdbc项目介绍:
- 项目地址
https://shardingsphere.apache.org/document/current/cn/quick-start/shardingsphere-jdbc-quick-start/ - 项目优势:
- 整体架构:
.
二、确定需要分表的原始表
暂定为t_ocp_order_info、t_ocp_order_sku_info两个表
三、确定分表策略
- 确定分表键:以cbo单号作为分表键 (如:CBO2310181000001280)
- 对比分表策略:
- 历史订单表数据统计
按年、月分组统计ocp订单表
SELECT
year(FROM_UNIXTIME(create_time/1000)) AS year,
MONTH(FROM_UNIXTIME(create_time/1000)) AS month,
COUNT(*) AS count
FROM t_ocp_order_info
GROUP BY year,month;
sql结果:
综上确认分表策略:每个月数据量大概在30w左右,在双十一双十二大促时,单量也没有相差特别大,可以使用按月分表的策略
四、项目接入步骤
- 引入pom依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
- 引入ocp-sharding-db.yaml配置文件
sharding:
jdbc:
datasource:
names: ds0
ds0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: ****
username: ***
password: ***
initial-size: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=2000;druid.mysql.usePingMethod=false
config:
#开启sharding的日志
props:
sql.show: true
sharding:
binding-tables: t_ocp_order_info,t_ocp_order_sku_info
tables:
t_ocp_order_info:
actual-data-nodes: ds0.t_ocp_order_info_${2022..2024}_${1..12}
key-generator:
type: MY_SNOWFLAKE
column: id
table-strategy:
standard:
#分表键(路由键)
sharding-column: ocp_order_no
#自定义的分表策略
precise-algorithm-class-name: com.*.sharding.OcpOrderShardingAlgorithm
t_ocp_order_sku_info:
actual-data-nodes: ds0.t_ocp_order_sku_info_${2022..2024}_${1..12}
key-generator:
type: MY_SNOWFLAKE
column: id
table-strategy:
standard:
#分表键(路由键)
sharding-column: ocp_order_no
#自定义的分表策略
precise-algorithm-class-name: com.*.sharding.OcpOrderShardingAlgorithm
- 自定义sharing分表策略
@Slf4j
public class OcpOrderShardingAlgorithm implements PreciseShardingAlgorithm<String> {
private static final String UNDERLINE = "_";
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
String ocpOrderNo = preciseShardingValue.getValue();
//如果不是CBO单号 直接查原表t_ocp_order_info
if (!ocpOrderNo.contains(OcpOrderPreConstants.OCP_BASE_ORDER)) {
return preciseShardingValue.getLogicTableName();
}
//去除cbo前缀
String removeCbo = ocpOrderNo.replace(OcpOrderPreConstants.OCP_BASE_ORDER, "");
//截取到年和月
String dateStr = removeCbo.substring(0, 4);
//转换
LocalDate localDate = DateUtils.dateStrToLocalDate(dateStr);
//拼接表名 如: t_ocp_order_info + _ + 年 + _ + 月
return preciseShardingValue.getLogicTableName() + UNDERLINE + localDate.getYear() + UNDERLINE + localDate.getMonthValue();
}
}
- 创建分表