你没见过的分库分表原理解析和解决方案(二) - ToB企服应用市场:ToB评测及商务社交产业平台

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.15</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
</dependency>
<dependency>
<groupId>com.easy-query</groupId>
<artifactId>sql-processor</artifactId>
<version>1.1.7</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>com.easy-query</groupId>
<artifactId>sql-springboot-starter</artifactId>
<version>1.1.7</version>
<scope>compile</scope>
</dependency>

复制代码

server:
port: 8081
spring:
profiles:
active: dev
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding-order?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true
username: root
password: root
druid:
initial-size: 10
max-active: 100
easy-query:
enable: true
name-conversion: underlined
database: mysql
default-data-source-merge-pool-size: 60
default-data-source-name: ds0

复制代码

//分片表
@Data
@Table(value = "t_order", shardingInitializer = OrderInitializer.class)
@EntityProxy
public class OrderEntity {
@Column(primaryKey = true)
private String id;
private Integer orderNo;
private String userId;
@ShardingTableKey
@ShardingDataSourceKey
private LocalDateTime createTime;
}
//分片初始化器
@Component
public class OrderInitializer extends AbstractShardingMonthInitializer<OrderEntity> {
/**
* 分片起始时间
* @return
*/
@Override
protected LocalDateTime getBeginTime() {
return LocalDateTime.of(2020,1,1,0,0,0);
}
/**
* 格式化时间到数据源
* @param time
* @param defaultDataSource
* @return
*/
@Override
protected String formatDataSource(LocalDateTime time, String defaultDataSource) {
String year = DateTimeFormatter.ofPattern("yyyy").format(time);
int i = Integer.parseInt(year)-2020;
return "ds"+i;
}
@Override
public void configure0(ShardingEntityBuilder<OrderEntity> builder) {
}
}
//动态添加spring 启动后的动态数据源额外的ds1、ds2、ds3
@Component
public class ShardingInitRunner implements ApplicationRunner {
@Autowired
private EasyQuery easyQuery;
@Override
public void run(ApplicationArguments args) throws Exception {
Map<String, DataSource> dataSources = createDataSources();
DataSourceManager dataSourceManager = easyQuery.getRuntimeContext().getDataSourceManager();
for (Map.Entry<String, DataSource> stringDataSourceEntry : dataSources.entrySet()) {
dataSourceManager.addDataSource(stringDataSourceEntry.getKey(), stringDataSourceEntry.getValue(), 60);
}
System.out.println("初始化完成");
}
private Map<String, DataSource> createDataSources() {
HashMap<String, DataSource> stringDataSourceHashMap = new HashMap<>();
for (int i = 1; i < 4; i++) {
DataSource dataSource = createDataSource("ds" + i, "jdbc:mysql://127.0.0.1:3306/sharding-order" + i + "?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true", "root", "root");
stringDataSourceHashMap.put("ds" + i, dataSource);
}
return stringDataSourceHashMap;
}
private DataSource createDataSource(String dsName, String url, String username, String password) {
// 设置properties
Properties properties = new Properties();
properties.setProperty("name", dsName);
properties.setProperty("driverClassName", "com.mysql.cj.jdbc.Driver");
properties.setProperty("url", url);
properties.setProperty("username", username);
properties.setProperty("password", password);
properties.setProperty("initialSize", "10");
properties.setProperty("maxActive", "100");
try {
return DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new EasyQueryException(e);
}
}
}
//新建分库路由
@Component
public class OrderDataSourceRoute extends AbstractDataSourceRoute<OrderEntity> {
protected Integer formatShardingValue(LocalDateTime time) {
String year = time.format(DateTimeFormatter.ofPattern("yyyy"));
return Integer.parseInt(year);
}
public boolean lessThanTimeStart(LocalDateTime shardingValue) {
LocalDateTime timeYearFirstDay = EasyUtil.getYearStart(shardingValue);
return shardingValue.isEqual(timeYearFirstDay);
}
protected Comparator<String> getDataSourceComparator(){
return IgnoreCaseStringComparator.DEFAULT;
}
@Override
protected RouteFunction<String> getRouteFilter(TableAvailable table, Object shardingValue, ShardingOperatorEnum shardingOperator, boolean withEntity) {
//将分片键转成对应的类型
LocalDateTime shardingTime = (LocalDateTime)shardingValue ;
Integer intYear = formatShardingValue(shardingTime);
String dataSourceName="ds"+String.valueOf((intYear-2020));//ds0 ds1 ds2 ds3....
switch (shardingOperator) {
case GREATER_THAN:
case GREATER_THAN_OR_EQUAL:
return ds -> getDataSourceComparator().compare(dataSourceName, ds) <= 0;
case LESS_THAN: {
//如果小于月初那么月初的表是不需要被查询的如果小于年初也不需要查询
if (lessThanTimeStart(shardingTime)) {
return ds -> getDataSourceComparator().compare(dataSourceName, ds) > 0;
}
return ds -> getDataSourceComparator().compare(dataSourceName, ds) >= 0;
}
case LESS_THAN_OR_EQUAL:
return ds -> getDataSourceComparator().compare(dataSourceName, ds) >= 0;
case EQUAL:
return ds -> getDataSourceComparator().compare(dataSourceName,ds) == 0;
default:
return ds -> true;
}
}
}
//新建分表路由
//分表路由由系统提供默认按月分片
@Component
public class OrderTableRoute extends AbstractMonthTableRoute<OrderEntity> {
@Override
protected LocalDateTime convertLocalDateTime(Object shardingValue) {
return (LocalDateTime)shardingValue;
}
}
```
通过sql脚本我们创建好对应的数据库表结构
![](https://img2023.cnblogs.com/blog/1346660/202306/1346660-20230626215913917-1277133380.png)
初始化项目代码
``java
private final EasyProxyQuery easyProxyQuery;
@GetMapping("/init")
public Object init() {
long start = System.currentTimeMillis();
LocalDateTime beginTime = LocalDateTime.of(2020, 1, 1, 0, 0, 0);
LocalDateTime now = LocalDateTime.now();
ArrayList<OrderEntity> orderEntities = new ArrayList<>();
List<String> userIds = Arrays.asList("小明", "小红", "小蓝", "小黄", "小绿");
int i=0;
do {
OrderEntity orderEntity = new OrderEntity();
String timeFormat = DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(beginTime);
orderEntity.setId(timeFormat);
orderEntity.setOrderNo(i);
orderEntity.setUserId(userIds.get(i%5));
orderEntity.setCreateTime(beginTime);
orderEntities.add(orderEntity);
i++;
beginTime=beginTime.plusMinutes(1);
} while (beginTime.isBefore(now));
long end = System.currentTimeMillis();
long insertStart = System.currentTimeMillis();
long rows = easyProxyQuery.insertable(orderEntities).executeRows();
long insertEnd = System.currentTimeMillis();
return "成功插入:" + rows+",其中路由对象生成耗时:"+(end-start)+"(ms),插入耗时:"+(insertEnd-insertStart)+"(ms)";
}
```
![](https://img2023.cnblogs.com/blog/1346660/202306/1346660-20230626223148693-872730237.png)
数据初始化成功,接下来演示如何进行分组
# 分组聚合
分表分库下我们应该如何分组聚合
代码很简单就是查询userId in ["小明", "小绿"]的然后对userId分组求对应的订单号求和
````java
@GetMapping("/groupByWithSumOrderNo")
public Object groupByWithSumOrderNo() {
long start = System.currentTimeMillis();
List<String> userIds = Arrays.asList("小明", "小绿");
List<OrderGroupWithSumOrderNoVO> list = easyProxyQuery.queryable(OrderEntityProxy.DEFAULT)
.where((filter, t) -> filter.in(t.userId(), userIds))
.groupBy((group, t) -> group.column(t.userId()))
.select(OrderGroupWithSumOrderNoVOProxy.DEFAULT, (selector, t) -> selector.columnAs(t.userId(), r -> r.userId()).columnSumAs(t.orderNo(), r -> r.orderNoSum()))
.toList();
long end = System.currentTimeMillis();
return Arrays.asList(list,(end-start)+"(ms)");
}

复制代码

@GetMapping("/groupByWithSumOrderNoOrderByUserId")
public Object groupByWithSumOrderNoOrderByUserId() {
long start = System.currentTimeMillis();
List<String> userIds = Arrays.asList("小明", "小绿");
List<OrderGroupWithSumOrderNoVO> list = easyProxyQuery.queryable(OrderEntityProxy.DEFAULT)
.where((filter, t) -> filter.in(t.userId(), userIds))
.groupBy((group, t) -> group.column(t.userId()))
.orderByAsc((order,t)->order.column(t.userId()))
.select(OrderGroupWithSumOrderNoVOProxy.DEFAULT, (selector, t) -> selector.columnAs(t.userId(), r -> r.userId()).columnSumAs(t.orderNo(), r -> r.orderNoSum()))
.toList();
long end = System.currentTimeMillis();
return Arrays.asList(list,(end-start)+"(ms)");
}

复制代码

List<String> userIds = Arrays.asList("小明", "小绿");
List<OrderGroupWithAvgOrderNoVO> list = easyProxyQuery.queryable(OrderEntityProxy.DEFAULT)
.where((filter, t) -> filter.in(t.userId(), userIds))
.groupBy((group, t) -> group.column(t.userId()))
.orderByAsc((order, t) -> order.column(t.userId()))
.select(OrderGroupWithAvgOrderNoVOProxy.DEFAULT, (selector, t) -> selector.columnAs(t.userId(), r -> r.userId()).columnAvgAs(t.orderNo(), r -> r.orderNoAvg()))
.toList();
//生成的sql 会自动补齐count和sum来保证数据结果的正确性
SELECT t.`user_id` AS `user_id`,AVG(t.`order_no`) AS `order_no_avg`,COUNT(t.`order_no`) AS `orderNoRewriteCount`,SUM(t.`order_no`) AS `orderNoRewriteSum`
FROM `t_order_202211` t
WHERE t.`user_id` IN (?,?)
GROUP BY t.`user_id`
ORDER BY t.`user_id` ASC
[[{"orderNoAvg":916515.0000,"userId":"小明"},{"orderNoAvg":916516.5000,"userId":"小绿"}],"1924(ms)"]
分别对其进行求和和求count
[[{"orderNoSum":336000814605,"userId":"小明"}],"914(ms)"]
[[{"orderNoAvg":366607,"userId":"小明"}],"777(ms)"]
916515.0000=336000814605/366607 所以结果是正确的

复制代码

--原始sql
select * from order where time between 2020 and 2021 order by time limit 1,5
假如他被路由到2020和2021两张表那么要获取前10条数据应该怎么写
select * from order_2020 where time between 2020 and 2021 order by time limit 1,5
select * from order_2021 where time between 2020 and 2021 order by time limit 1,5

复制代码

--原始sql
select * from order where time between 2020 and 2021 order by time limit 2,5
假如他被路由到2020和2021两张表那么要获取前10条数据应该怎么写
--错误的做法
select * from order_2020 where time between 2020 and 2021 order by time limit 2,5
select * from order_2021 where time between 2020 and 2021 order by time limit 2,5

复制代码

--原始sql
select * from order where time between 2020 and 2021 order by time limit 2,5
假如他被路由到2020和2021两张表那么要获取前10条数据应该怎么写
--正确的做法
select * from order_2020 where time between 2020 and 2021 order by time limit 1,10
select * from order_2021 where time between 2020 and 2021 order by time limit 1,10

复制代码

int pageIndex=x
int pageSize=y;
那么重写后的sql应该是
int pageIndex=1
int pageSize=x*y;

复制代码

int pageIndex=x
int pageSize=y;
那么重写后的sql应该是
int pageIndex=1
int pageSize=x*y;

复制代码