背景:公司项目采用的是sharding-jdbc+pagehelper+mysql分表,共有3个模块,每个模块的原子表都建了366天的天表。原来数据量不大都是单表查询,没有暴露什么问题,刚好外场来个需求到我这里,要求查询范围1年也就是说要多表联查,经过测试发现数据量一大就超时。
假设有表base_call_0101 - base_call_1231 共366张表,原来是pagehelper给算总数,查数据,特别特别慢。
改在后速度在200ms左右,数据量在1000W左右
下面说下思路
1、弃用pagehelper,自己查总数
2、查询表数据量使用count(1)后union all汇总得到每个表的行数
3、根据请求参数内的页码、大小计算命中到哪张表
4、对齐每张表的偏移量
5、查询数据汇总
6、返回给前端
第4点说明一下,前端请求过来假设pageNo=5000、pageSize=100,那对于整个数据来说它的起始点在(5000*100)-1, 500,刚好命中的100条数据需要几张表,那就需要算到每张表的起始位置及大小了
public PageInfo<xxx> getRecords(SearchCondition searchCondition) {
Integer pageSize = searchCondition.getPageSize();
Integer pageNo = searchCondition.getPageNo();
searchCondition = supTable(searchCondition);
LinkedHashMap<String, TableParam> hitTableMap = new LinkedHashMap<>(); // 命中表列表
int totalNum = 0; // 总数
int leftPoint = (pageNo - 1) * pageSize; // 左侧偏移点
int rightPoint = leftPoint + pageSize; // 右侧偏移点
boolean hitFirstFlag = false; // 命中左侧偏移点标识
boolean hitLastFlag = false; // 命中右侧偏移点标识
int unprocessedSize = 0;
List<HashMap> tableList = voiceDao.getCallRecordsTotal(searchCondition);
for (HashMap table : tableList) {
Integer tableTotal = Math.toIntExact((Long) table.get("tableTotal")); // 单表总数
String tableName = (String) table.get("tableName"); // 表名
if (0 == tableTotal) { // 0 数据量跳过处理
continue;
}
int lastCount = totalNum; // 上次合计数
totalNum += tableTotal; // 累加
if (totalNum > leftPoint && !hitLastFlag) {
int tableOffset = leftPoint - lastCount; // 单表偏移量
int tableSize = tableTotal - tableOffset; // 单表大小
// 第一次命中
if (!hitFirstFlag) {
if (tableSize < pageSize) {
unprocessedSize = tableSize < pageSize ? pageSize - tableSize : unprocessedSize;
} else {
tableSize = pageSize;
}
hitFirstFlag = true;
} else {
tableOffset = 0;
if (tableTotal > unprocessedSize) {
tableSize = unprocessedSize;
} else {
tableSize = tableTotal;
unprocessedSize = unprocessedSize - tableSize;
}
}
hitTableMap.put(tableName, new TableParam(tableName, tableOffset, tableSize));
}
if (totalNum > rightPoint && !hitLastFlag) {
hitLastFlag = true;
}
}
List<xxx> result = new ArrayList<>();
for (Map.Entry<String, TableParam> table : hitTableMap.entrySet()) {
searchCondition.setTableName(table.getValue().getTableName());
searchCondition.setOffset(table.getValue().getOffset());
searchCondition.setPageSize(table.getValue().getPageSize());
result.addAll(voiceDao.getCallRecords(searchCondition));
}
PageInfo<xxx> pageInfo = new PageInfo<>(result);
pageInfo.setPageSize(pageSize);
pageInfo.setPageNum(pageNo);
pageInfo.setTotal(totalNum);
return pageInfo;
}
算法改进后,更为重要的是索引的合理性,这就需要看需求,我这里基础表在前端加了很多筛选条件,那只能多建立索引了。虽然说速度上来了,但是代价就是占用空间变大了,100M的数据200M的索引,空间换时间。不要一味的相信网上的资料,还是要多动手实践,选择最合适的。其实还能再继续优化,但是我脑瓜子疼好使就行了。