遇到的情况时也一张表数据类型不是long,不能直接计算,又不能随便改主表,所以先拉临时表,先查主表总条数,分成10页,分别插入到10张临时表,插入前先删除临时表数据,这样,第一步对临时表进行聚合统计,再把所以临时表数据再进行一次聚合得到想要的数据。写了2天,把部分代码贴在下面。
测试时插入2000万,耗时40分钟,每分钟大约50万条。网络传输大约需要1M/s,cpu,内存消耗正常 当日条数分拆成11张临时表,再对每张表group by,再对结果表最后一次group by,得到总结果. 理论上没有问题,关键在于插入速度慢。
后面又修改又单次插入50条到200条,内存原8G加大一倍,每分钟大约135万插入,上传速度可达2.7m/s。
后面进行了优化,再插入主表时同时插入另一张主表的备份表,修改数据类型,直接可以用聚合。
// @Override
// public WaterConsumptionTemp findNewestConsumptionTemp() {
// Query query = new Query();
// query.limit(1);
// List<WaterConsumptionTemp1> list = mongoTemplate.find(query, WaterConsumptionTemp1.class);
// if (list != null && list.size() > 0) {
// return list.get(0);
// }
// return null;
// }
//
// @Override
// public int insertConsumptionTemp(int MaxCollectionCount, String collectionNamePre) {
// initCollection();
// if (MaxCollectionCount < 1 || StringUtils.isEmpty(collectionNamePre)) {
// throw new IllegalArgumentException("参数有误");
// }
// long start = System.currentTimeMillis();
// logger.info("insertConsumptionTemp start");
// Query query = initConsumptionTempQuery();
// long count = mongoTemplate.count(query, WaterConsumption.class);
// if (count < 1) {
// return 1;
// }
//
// int pageSize = (int) (count / (MaxCollectionCount - 1));
if(count>1000*10000){
logger.error("insertConsumptionTemp 单表数据过大,可能会变慢,谨慎操作 pageSize:"+pageSize);
}
// int pageNums = MaxCollectionCount;
// logger.info("insertConsumptionTemp :pageSize" + pageSize + ",page " + pageNums + ",count " + count);
// for (int i = 1; i <= MaxCollectionCount; i++) {
// WriteResult removeResult = mongoTemplate.remove(new Query(), collectionNamePre + i);
// logger.info("removeResult:" + removeResult);
// }
// for (int i = 1; i <= pageNums; i++) {
// insertConsumptionByCollectionName(collectionNamePre + i, i - 1, pageSize);
// }
// logger.info("insertConsumptionTemp end,cost:" + (System.currentTimeMillis() - start));
// return 10;
// }
//
//
// public void initCollection() {
// if (!mongoTemplate.collectionExists(WaterConsumption.class)) {
// mongoTemplate.createCollection(WaterConsumption.class);
// }
// if (!mongoTemplate.collectionExists(WaterConsumptionTemp1.class)) {
// mongoTemplate.createCollection(WaterConsumptionTemp1.class);
// }
// if (!mongoTemplate.collectionExists(WaterConsumptionTemp2.class)) {
// mongoTemplate.createCollection(WaterConsumptionTemp2.class);
// }
// if (!mongoTemplate.collectionExists(WaterConsumptionTemp3.class)) {
// mongoTemplate.createCollection(WaterConsumptionTemp3.class);
// }
// if (!mongoTemplate.collectionExists(WaterConsumptionTemp4.class)) {
// mongoTemplate.createCollection(WaterConsumptionTemp4.class);
// }
// if (!mongoTemplate.collectionExists(WaterConsumptionTemp5.class)) {
// mongoTemplate.createCollection(WaterConsumptionTemp5.class);
// }
// if (!mongoTemplate.collectionExists(WaterConsumptionTemp6.class)) {
// mongoTemplate.createCollection(WaterConsumptionTemp6.class);
// }
// if (!mongoTemplate.collectionExists(WaterConsumptionTemp7.class)) {
// mongoTemplate.createCollection(WaterConsumptionTemp7.class);
// }
// if (!mongoTemplate.collectionExists(WaterConsumptionTemp8.class)) {
// mongoTemplate.createCollection(WaterConsumptionTemp8.class);
// }
// if (!mongoTemplate.collectionExists(WaterConsumptionTemp9.class)) {
// mongoTemplate.createCollection(WaterConsumptionTemp9.class);
// }
// if (!mongoTemplate.collectionExists(WaterConsumptionTemp10.class)) {
// mongoTemplate.createCollection(WaterConsumptionTemp10.class);
// }
// if (!mongoTemplate.collectionExists(WaterConsumptionTemp11.class)) {
// mongoTemplate.createCollection(WaterConsumptionTemp11.class);
// }
// }
//
// @Override
// public void initTestData() {
// long start = System.currentTimeMillis();
// logger.error("initTestData start:" + start);
// int startUserid = 1595;
// for (int i = startUserid; i < 2000; i++) {
// for (int j = 0; j < 1000; j++) {
// List<WaterConsumption> tempList = new ArrayList<>(20);
// for (int type = 0; type < 20; type++) {
// WaterConsumption waterConsumption = new WaterConsumption();
// waterConsumption.setType("" + type);
// waterConsumption.setUserId("" + i);
// waterConsumption.setStatus("1");
// waterConsumption.setUpdateTime(new Date());
// waterConsumption.setCreateTime(new Date());
// waterConsumption.setCount("" + type);
// tempList.add(waterConsumption);
// }
// mongoTemplate.insert(tempList, WaterConsumption.class);
// }
// }
// logger.error("initTestData end:" + (System.currentTimeMillis() - start));
// }
//
// public Query initConsumptionTempQuery() {
// Query query = new Query();
// Date yestdayStart = DateUtil.getYestdayStart();
// Date yestdayEnd = DateUtil.getYestdayEnd();
// Criteria criteria = Criteria.where("createTime").gte(yestdayStart).lte(yestdayEnd);
// query.addCriteria(criteria);
// return query;
// }
//
// public void handlConsumptionTempData(int collectionCount, String collectionNamePre) {
// long start = System.currentTimeMillis();
// Aggregation aggregation = Aggregation.newAggregation(Aggregation.group("userId", "type", "status", "dayInt").sum("count").as("count"));
// for (int i = 1; i <= collectionCount; i++) {
// long start1 = System.currentTimeMillis();
// AggregationResults<WaterConsumptionTemp> aggregationResults = mongoTemplate.aggregate(aggregation, collectionNamePre + i, WaterConsumptionTemp.class);
// List<WaterConsumptionTemp> list = aggregationResults.getMappedResults();
// int InsertCount = 500;
// List tempList = new ArrayList(InsertCount + 1);
// int count = 0;
// for (WaterConsumptionTemp waterConsumptionTemp : list) {
// count++;
// tempList.add(waterConsumptionTemp);
// if (count >= InsertCount) {
// mongoTemplate.insert(tempList, WaterConsumptionTemp.class);
// tempList.clear();
// count = 0;
// }
// }
// mongoTemplate.insert(tempList, WaterConsumptionTemp.class);
// tempList.clear();
// long end1 = System.currentTimeMillis();
// logger.info("handlConsumptionTempData first step cost:" + (collectionNamePre + i) + "," + (end1 - start1));
// }
// long end = System.currentTimeMillis();
// logger.info("handlConsumptionTempData first step cost:" + (end - start));
//
// //最后再对各个分表整合到总表中去
// AggregationResults<WaterConsumptionTemp> aggregationResults = mongoTemplate.aggregate(aggregation, collectionNamePre, WaterConsumptionTemp.class);
// List<WaterConsumptionTemp> list = aggregationResults.getMappedResults();
// int InsertCount = 500;
// List tempList = new ArrayList(InsertCount + 1);
// int count = 0;
// for (WaterConsumptionTemp waterConsumptionTemp : list) {
// count++;
// tempList.add(waterConsumptionTemp);
// if (count >= InsertCount) {
// mongoTemplate.insert(tempList, ConsumptionDailyReport.class);
// tempList.clear();
// count = 0;
// }
// }
// mongoTemplate.insert(tempList, ConsumptionDailyReport.class);
// tempList.clear();
// }
//
// public void insertConsumptionByCollectionName(String collectionName, int collectionPage, int collectionSize) {
// long start = System.currentTimeMillis();
// logger.info("insertConsumptionTemp start");
//
// Query query = initConsumptionTempQuery();
// //第collectionPage页到collectionSize条数
// Pageable collectionPageable = new PageRequest(collectionPage, collectionSize, null);
// query.with(collectionPageable);
// long count = collectionSize;
//
// int pageSize = 5000;
// int pageNums = (int) (count / pageSize + 1);
// logger.info("collectionName:" + collectionName + ",insertConsumptionTemp :pageSize" + pageSize + ",page " + pageNums + ",count " + count);
//
// //分页查询
// List<WaterConsumption> list = null;
// Pageable pageable = null;
// int InsertCount = 500;
// int tempInsertCount = 0;
//
// int pageIndex = collectionPage * collectionSize / pageSize + 1;
// pageNums = (collectionPage + 1) * collectionSize / pageSize;
// for (; pageIndex < pageNums; pageIndex++) {
// pageable = new PageRequest(pageIndex, pageSize, null);
// query.with(pageable);
// list = mongoTemplate.find(query, WaterConsumption.class);
// List<WaterConsumptionTemp> tempList = new ArrayList<>(InsertCount + 1);
// for (WaterConsumption waterConsumption : list) {
// WaterConsumptionTemp temp = copyFromWaterConsumption(waterConsumption);
// tempInsertCount++;
// tempList.add(temp);
// if (tempInsertCount >= InsertCount) {
// mongoTemplate.insert(tempList, collectionName);
// tempList.clear();
// tempInsertCount = 0;
// }
// }
// mongoTemplate.insert(tempList, collectionName);
// tempList.clear();
// tempInsertCount = 0;
// }
// logger.info("insertConsumptionTemp end,cost:" + (System.currentTimeMillis() - start));
//
// }
//
//
// private WaterConsumptionTemp copyFromWaterConsumption(WaterConsumption waterConsumption) {
// WaterConsumptionTemp temp = new WaterConsumptionTemp();
// if (waterConsumption.getUserId() != null) {
// temp.setUserId(Long.valueOf(waterConsumption.getUserId()));
// }
// if (waterConsumption.getType() != null) {
// temp.setType(Integer.valueOf(waterConsumption.getType()));
// }
// if (waterConsumption.getCount() != null) {
// temp.setCount(Long.valueOf(waterConsumption.getCount()));
// }
// if (waterConsumption.getStatus() != null) {
// temp.setStatus(Integer.valueOf(waterConsumption.getStatus()));
// }
// temp.setCreateTime(waterConsumption.getCreateTime());
// temp.setUpdateTime(waterConsumption.getUpdateTime());
// temp.setSource(waterConsumption.getSource());
// if (waterConsumption.getCreateTime() != null) {
// temp.setDayInt(DateUtil.formateDateToInt(waterConsumption.getCreateTime()));
// }
//
// return temp;
// }