前言
shardingsphere是一款分库分表的框架,可以简化我们写分库分表的逻辑
需求
- 存5000万订单数据数据
- 支持按订单ID,买家ID查询订单
- 可支持通用配置
技术设计
- mysql单表存储超过2000W性能会明显下降,把数据拆分到4张表中,分别在2个库里。库的数量,是需要根据QPS来定,每个库的用户链接数是有上限的,如果QPS很高,需要增加分库数量
- 订单号的设计,最后几位采用买家ID,按买家ID分库分表,这样能同时支持按订单ID和卖家ID查询
- 每个库都放一个global_config表,作为字典表,存放相同数据
表结构设计
create database oms1;
create database oms2;
CREATE TABLE torder_1 (
order_id BIGINT(20) PRIMARY KEY,
status varchar(20) NOT NULL,
gmt_create datetime NOT NULL,
sku_code VARCHAR(50) NOT NULL,
sku_name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
buyer_id BIGINT(20) NOT NULL
);
CREATE TABLE torder_2 (
order_id BIGINT(20) PRIMARY KEY,
status varchar(20) NOT NULL,
gmt_create datetime NOT NULL,
sku_code VARCHAR(50) NOT NULL,
sku_name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
buyer_id BIGINT(20) NOT NULL
);
CREATE TABLE global_config (
id BIGINT(20) PRIMARY KEY,
status varchar(20) NOT NULL,
gmt_create datetime NOT NULL,
config_key VARCHAR(50) NOT NULL,
config_value VARCHAR(50) NOT NULL,
config_desc VARCHAR(100) NOT NULL
);
二方包
<!-- shardingJDBC核心依赖 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<!--XA 分布式事务 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-transaction-xa-core</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<!-- 数据源连接池 -->
<!--注意不要用这个依赖,他会创建数据源,跟上面ShardingJDBC的SpringBoot集成依赖有冲突 -->
<!-- <dependency>-->
<!-- <groupId>com.alibaba</groupId>-->
<!-- <artifactId>druid-spring-boot-starter</artifactId>-->
<!-- <version>1.1.20</version>-->
<!-- </dependency>-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
<!-- mysql连接驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- mybatisplus依赖 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
实体
package com.wgp.oms.dal.entity;
import java.math.BigDecimal;
import java.util.Date;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
/**
* @author gangpeng.wgp
* @date 2023/6/27 下午9:58
*/
@Data
//特别注意,不能踩坑:表名不能叫order,是mysql关键字。否则shardingsphere会启动失败
public class Torder {
@TableId
private Long orderId;
private String status;
private Date gmtCreate;
private String skuCode;
private String skuName;
private BigDecimal price;
private Long buyerId;
}
数据源
在application.properties中定义2个数据源m1、m2,分别对应2个库oms1、oms2
#各种分库分表策略
#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/oms1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=12345678
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/oms2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=12345678
#真实表分布,分库,分表
spring.shardingsphere.sharding.tables.torder.actual-data-nodes=m$->{1..2}.torder_$->{1..2}
分片策略
分片策略总共有4种:
1、inline分片策略:支持按单列分片 2、standard标准分片策略:支持按单列分片,SQL语句中有>,>=, <=,<,=,IN 和 BETWEEN AND 操作符 3、complex复杂分片策略:支持多key,SQL语句中有>,>=, <=,<,=,IN 和 BETWEEN AND 操作符4、hint强制路由策略:不按SQL来路由,由代码强制指定
inline分片策略
这里的分库分表表达式,就是把数据能均衡的离散到2个库4张表里
#inline分片策略,支持单单key,
spring.shardingsphere.sharding.tables.torder.database-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.torder.database-strategy.inline.algorithm-expression=m$->{((order_id%4+1).intdiv(3))+1}
spring.shardingsphere.sharding.tables.torder.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.torder.table-strategy.inline.algorithm-expression=torder_$->{((order_id%4+1)%2)+1}
stardard分片策略
#standard标准分片策略, 支持单key,SQL 语句中有>,>=, <=,<,=,IN 和 BETWEEN AND 操作符
spring.shardingsphere.sharding.tables.torder.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.torder.table-strategy.standard.precise-algorithm-class-name=com.wgp.oms.dal.algorithem.MyPreciseTableShardingAlgorithm
spring.shardingsphere.sharding.tables.torder.table-strategy.standard.range-algorithm-class-name=com.wgp.oms.dal.algorithem.MyRangeTableShardingAlgorithm
spring.shardingsphere.sharding.tables.torder.database-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.torder.database-strategy.standard.precise-algorithm-class-name=com.wgp.oms.dal.algorithem.MyPreciseDBShardingAlgorithm
spring.shardingsphere.sharding.tables.torder.database-strategy.standard.range-algorithm-class-name=com.wgp.oms.dal.algorithem.MyRangeDBShardingAlgorithm
package com.wgp.oms.dal.algorithem;
import java.util.Collection;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
/**
* @author : gangpeng.wgp
* @date : 2023/6/28
*/
public class MyPreciseDBShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> databaseNames, PreciseShardingValue<Long> shardingValue) {
for (String databaseName : databaseNames) {
//m$->{((order_id%4+1).intdiv(3))+1}
String value = "m" + (((shardingValue.getValue() % 4 + 1)/3) +1);
if (databaseName.equalsIgnoreCase(value)) {
return databaseName;
}
}
throw new IllegalArgumentException();
}
}
package com.wgp.oms.dal.algorithem;
import java.util.Collection;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
/**
* @author : gangpeng.wgp
* @date : 2023/6/28
*/
public class MyPreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue) {
for (String tableName : tableNames) {
//torder_$->{((order_id%4+1)%2)+1}
String value = "torder_" + (((shardingValue.getValue() % 4 + 1) % 2) + 1);
if (tableName.equalsIgnoreCase(value)) {
return tableName;
}
}
throw new IllegalArgumentException();
}
}
complex分片策略
spring.shardingsphere.sharding.tables.torder.table-strategy.complex.sharding-columns= order_id, buyer_id
spring.shardingsphere.sharding.tables.torder.table-strategy.complex.algorithm-class-name=com.wgp.oms.dal.algorithem.MyComplexTableShardingAlgorithm
spring.shardingsphere.sharding.tables.torder.database-strategy.complex.sharding-columns=order_id, buyer_id
spring.shardingsphere.sharding.tables.torder.database-strategy.complex.algorithm-class-name=com.wgp.oms.dal.algorithem.MyComplexDSShardingAlgorithm
package com.wgp.oms.dal.algorithem;
import java.util.Collection;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
public class MyComplexDSShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
/**
* @param availableTargetNames 目标数据源 或者 表 的值。
* @param shardingValue logicTableName逻辑表名 columnNameAndShardingValuesMap 分片列的精确值集合。 columnNameAndRangeValuesMap 分片列的范围值集合
* @return
*/
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
return availableTargetNames;
}
}
package com.wgp.oms.dal.algorithem;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
/**
* 复合分片
* 使用场景:需要按多个字段进行分表,比如:既可以按order_id分表,也可以按buyer_id分表
* @author : gangpeng.wgp
* @date : 2023/6/28
*/
public class MyComplexTableShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
Collection<Long> orderIdList = shardingValue.getColumnNameAndShardingValuesMap().get("order_id");
Collection<Long> buyerIdList = shardingValue.getColumnNameAndShardingValuesMap().get("buyer_id");
List<String> result = new ArrayList<>();
if(CollectionUtils.isNotEmpty(orderIdList)){
//实现自定义分片逻辑
for (Long id : orderIdList) {
//torder_$->{((order_id%4+1)%2)+1}
String value = "torder_" + (((id % 4 + 1) % 2) + 1);
result.add(value);
}
}
if(CollectionUtils.isNotEmpty(buyerIdList)){
//实现自定义分片逻辑
for (Long id : buyerIdList) {
//torder_$->{((buyer_id%4+1)%2)+1}
String value = "torder_" + (((id % 4 + 1) % 2) + 1);
if(!result.contains(value)){
result.add(value);
}
}
}
return result;
}
}
hint分片策略
spring.shardingsphere.sharding.tables.torder.database-strategy.hint.algorithm-class-name=com.wgp.oms.dal.algorithem.MyHintTableShardingAlgorithm
package com.wgp.oms.dal.algorithem;
import java.util.ArrayList;
import java.util.Collection;
import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;
public class MyHintTableShardingAlgorithm implements HintShardingAlgorithm<Long> {
@Override
public Collection<String> doSharding(
Collection<String> availableTargetNames,
HintShardingValue<Long> shardingValue) {
// 添加分库或分表路由逻辑
Collection<String> result = new ArrayList<>();
for (String each : availableTargetNames) { //代表:分片目标,对哪些数据库、表分片。如果是对分库路由,表示ds0,ds1;
for (Long value : shardingValue.getValues()) { // 代表:分片值; 可以HintManager设置多个分片值,所以是个集合。
if (each.endsWith(String.valueOf(value ))) { // 分库路由
result.add(each );
}
}
}
return result;
}
}
/**
* 强制指定库路由查询
*
* 注意:需在application.properties中开启hint查询策略,且注释掉其他查询策略
*/
@Test
public void queryOrderListWithHit() {
//注意:入参的类型需要和MyHintTableShardingAlgorithm中的泛型类型一致,否则会报错
HintManager.getInstance().setDatabaseShardingValue(1L);
List<Torder> orderList = orderMapper.queryAllOrder();
assertNotNull(orderList);
orderList.forEach(System.out::println);
System.out.println("结果数:" + orderList.size());
}
单测
package com.wgp.oms.service;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import com.wgp.oms.dal.entity.Torder;
import com.wgp.oms.dal.mapper.OrderMapper;
import org.apache.shardingsphere.api.hint.HintManager;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
/**
* @author gangpeng.wgp
* @date 2023/6/27 下午10:40
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class TorderServiceTest {
@Resource
private OrderService orderService;
@Resource
private OrderMapper orderMapper;
@Test
public void queryOrderById() {
Torder order = orderService.queryOrderById(1L);
assertNotNull(order);
System.out.println(order);
}
@Test
public void queryOrderList() {
List<Torder> orderList = orderMapper.queryAllOrder();
assertNotNull(orderList);
orderList.forEach(System.out::println);
}
@Test
public void queryByOrderIdRange() {
List<Torder> orderList = orderService.queryByOrderIdRange(1L, 2L);
assertNotNull(orderList);
orderList.forEach(System.out::println);
}
@Test
public void queryByIds() {
List<Torder> orderList = orderMapper.queryByIds(Arrays.asList(1L, 5L));
assertNotNull(orderList);
orderList.forEach(System.out::println);
}
/**
* 配合Complex策略
*/
@Test
public void queryByBuyerIds() {
List<Torder> orderList = orderMapper.queryByBuyerIds(Arrays.asList(1L, 5L));
assertNotNull(orderList);
orderList.forEach(System.out::println);
}
/**
* 强制指定库路由查询
*
* 注意:需在application.properties中开启hint查询策略,且注释掉其他查询策略
*/
@Test
public void queryOrderListWithHit() {
//注意:入参的类型需要和MyHintTableShardingAlgorithm中的泛型类型一致,否则会报错
HintManager.getInstance().setDatabaseShardingValue(1L);
List<Torder> orderList = orderMapper.queryAllOrder();
assertNotNull(orderList);
orderList.forEach(System.out::println);
System.out.println("结果数:" + orderList.size());
}
//插入数据会进行分片
@Test
public void addOrder() {
delete();
for (int i = 1; i < 100; i++) {
Torder c = new Torder();
c.setBuyerId((long)i);
c.setGmtCreate(new Date());
c.setStatus("INIT");
c.setPrice(BigDecimal.valueOf(1000.10));
c.setSkuCode("sku_" + i);
c.setSkuName("商品_" + i);
c.setOrderId((long)i);
orderMapper.insert(c);
}
}
private void delete() {
Map<String, Object> param = new HashMap<>();
//param.putIfAbsent("cname", "java");
orderMapper.deleteByMap(param);
}
@Test
public void update() {
Torder o = new Torder();
o.setOrderId(1L);
o.setPrice(BigDecimal.valueOf(9999.00));
int cnt = orderMapper.updateById(o);
assertEquals(cnt, 1);
Torder order = orderService.queryOrderById(1L);
assertNotNull(order);
assertEquals(9999.00D, order.getPrice().doubleValue());
}
}
字典表
#广播表配置(字典表,每个库都有一个,表名一样,数据一样)
spring.shardingsphere.sharding.broadcast-tables=global_config
spring.shardingsphere.sharding.tables.global_config.key-generator.column=id
spring.shardingsphere.sharding.tables.global_config.key-generator.type=SNOWFLAKE
package com.wgp.oms.dal.entity;
import java.util.Date;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
/**
* 全局字典表
* @author gangpeng.wgp
* @date 2023/6/27 下午9:58
*/
@Data
public class GlobalConfig {
@TableId
private Long id;
private String status;
private String configKey;
private String configValue;
private String configDesc;
private Date gmtCreate;
}
单测
package com.wgp.oms.service;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.annotation.Resource;
import com.wgp.oms.dal.entity.GlobalConfig;
import com.wgp.oms.dal.mapper.GlobalConfigMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import static org.junit.jupiter.api.Assertions.assertNotNull;
/**
* 注意:这个测试类,不能放到com.wgp.oms.dal.mapper包下,否则会被当做mybatis的mapper
*
* @author gangpeng.wgp
* @date 2023/6/28 3:11 下午
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class GlobalConfigTest {
@Resource
private GlobalConfigMapper globalConfigMapper;
@Test
public void selectById() {
GlobalConfig order = globalConfigMapper.selectById(1L);
assertNotNull(order);
System.out.println(order);
}
//插入数据会进行分片
@Test
public void addOrder() {
delete();
for (int i = 1; i < 100; i++) {
GlobalConfig c = new GlobalConfig();
c.setStatus("INIT");
c.setConfigKey("conf_" + i);
c.setConfigValue("value_" + i);
c.setConfigDesc("这是配置" + i);
c.setGmtCreate(new Date());
globalConfigMapper.insert(c);
}
}
private void delete() {
Map<String, Object> param = new HashMap<>();
//param.putIfAbsent("cname", "java");
globalConfigMapper.deleteByMap(param);
}
}
注意事项
- 表名一定不要和mysql的关键字冲突!比如:订单表,开始起名为order,后来应用一直报错,浪费了半天时间
- shardingsphere没有禁止全表扫描的功能,如果没有命中分片规则,可能会全表扫描,这在生产环境是致命的
- 对于使用自增主键的表,需要注意在分片键上不能使用该字段。因为自增主键在分布式系统下无法保证唯一性。