Java处理上千万数据量的数据

前言

最近项目中有一个需求,需要从一千多万的原始数据中,每条数据都是用户的办事数据,处理出事项与事项之间的关系

思路一

程序一

把数据表增加索引,提高查询效率

把数据排好序,测试查询速度 

 

 使用临时表保存排序好的数据

int size = groupByList.size();
        int time = size/100000;//提交次数
        // 开启事务
        String sql111 = "START TRANSACTION;";
        runner.execute(sql111);
        for (int i = 1; i <= time; i++) {
            StringBuilder insertSql1 = new StringBuilder("insert into jzts_item_temp (SPSXBH,HZSXBH,SBSJ,SQRZJHM) values ");
            for (int j = (i-1)*100000; j < i * 100000; j++) {
                insertSql1.append("(");
                insertSql1.append("'").append((String) groupByList.get(j).get("SPSXBH")).append("'");
                insertSql1.append(",");
                insertSql1.append("'").append((String) groupByList.get(j).get("HZSXBH")).append("'");
                insertSql1.append(",");
                insertSql1.append("'").append((Timestamp) groupByList.get(j).get("SBSJ")).append("'");
                insertSql1.append(",");
                insertSql1.append("'").append((String) groupByList.get(j).get("SQRZJHM")).append("'");
                insertSql1.append(")");
                if(j != i * 100000 -1){
                    insertSql1.append(",");
                }
            }
            runner.update(insertSql1.toString());
            long end1 = System.currentTimeMillis();
            logger.info("已处理{}0w条数据,花费 {} ms", i, (end1 - start));
        }
        // 提交事务
        String commitSql = "commit;";
        runner.execute(commitSql);

经过测试,处理插入100w条数据大概413秒,7分钟

理想情况下,推测1000w条数据大概要1个多小时能跑完。

程序二

4.在临时表的“事项编号”建索引,遍历临时表,根据事项分类,一个事项一个表,大概2000+个表,分别把包含后置事项的记录插入2000+个表中(看到这2000+个表也许解决崩溃了)

跑100w条数据大概210秒

QueryRunner runner = QueryRunnerUtils.getRunner("basicdb");
        QueryRunner runnerB = QueryRunnerUtils.getRunner("thing2thing");
        String sql = "select DISTINCT SPSXBH from jzts_item_temp";
        Set<String> set = new HashSet<>(1000);
        runner.query(sql,rs -> {
            while (rs.next()){
                set.add(rs.getString("SPSXBH"));
            }
            return null;
        });

        logger.info("所有事项为:{}",set);

//        set.forEach(s -> {
        for (String s : set) {

            if(!"1100236002".equals(s)){
                continue;
            }
            s = s.replaceAll(" ","");
            StringBuilder checkSql = new StringBuilder();
            checkSql.append("DROP TABLE IF EXISTS ").append("item_").append(s.replaceAll("-","_")).append(";");
            StringBuilder builder = new StringBuilder();
            builder.append("CREATE TABLE ").append("item_").append(s.replaceAll("-","_")).append(" (");
            builder.append("`id` int(11) NOT NULL AUTO_INCREMENT,");
            builder.append("`SPSXBH` varchar(50) DEFAULT NULL COMMENT '事项编号',");
            builder.append("`HZSXBH` varchar(1000) DEFAULT NULL COMMENT '后置事项编号',");
            builder.append("PRIMARY KEY (`id`)");
            builder.append(") ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;");

            try {
                runnerB.update(checkSql.toString());
                runnerB.update(builder.toString());
                logger.info(checkSql.toString());
                logger.info(builder.toString());
            } catch (SQLException e) {
                logger.error("发生异常的事件为{}",s);
                e.printStackTrace();
            }
            String insertSql = "INSERT INTO item_" + s.replaceAll("-","_") + " (SPSXBH,HZSXBH) SELECT SPSXBH,HZSXBH FROM jzpush.jzts_item_temp where SPSXBH = ? ";
            try {
                runnerB.update(insertSql,s);
                logger.info(insertSql);
            } catch (SQLException e) {
                logger.error("发生异常的事件为{}",s);
                e.printStackTrace();
            }
//        });
        }
        long end = System.currentTimeMillis();
        logger.info("处理1000w条数据花费 {} ms",(end - start));

程序三

5.跑一次全查的查询sql,查询出jzts_item_item需要的字段记录保存在list中

6.遍历5的记录,保存事项->对应的map信息,作为最后插入需要的信息使用

7.分别查询统计4的结果每个事项的表,统计查出每个事项的后置事项的数量,数量最大的且不为本身的,即为后置事项;如果后置事项是本身,则选择数量第二大的后置事项;无第二的则无后置事项

8.插入每个事项的后置事项的对应关系到jzts_item_item

final Logger logger = LoggerFactory.getLogger(Thing2ThingHandler6.class);
        QueryRunner runner = QueryRunnerUtils.getRunner("basicdb");
        QueryRunner runnerB = QueryRunnerUtils.getRunner("webdb");
        QueryRunner runnerC = QueryRunnerUtils.getRunner("thing2thing");
        long start = System.currentTimeMillis();
//        String sql = "select XZQHDM,SPSXBH,SPSXMC from A_APPLICATION1";
        String sql = "select XZQHDM,SPSXBH,SPSXMC from A_APPLICATION2";
        List<Map<String,Object>> list = runner.query(sql,new MapListHandler());
        logger.info(sql);
        // 保存翻译信息
        Map<String,Map<String,Object>> infoMap = new HashMap<>();
        list.forEach(map -> {
            String sxbh = (String) map.get("SPSXBH");
            sxbh = sxbh.replaceAll(" ","");
            if (!infoMap.containsKey(sxbh)) {
                infoMap.put(sxbh, map);
            }
        });

        Set<String> keySet = infoMap.keySet();
        keySet.forEach(key -> {
            String statSql = "select HZSXBH,COUNT(HZSXBH) from item_"+key.replaceAll("-","_") + " group by HZSXBH ORDER BY COUNT(HZSXBH) DESC";
            try {
                List<Map<String, Object>> query = runnerC.query(statSql, new MapListHandler());
                logger.info(statSql);
                String item2ItemSql = "insert into jzts_item_item (area,itemcode,itemname,afterarea,afteritemcode,afteritemname) value (?,?,?,?,?,?)";
                if(!query.isEmpty() && query.get(0)!=null && !"无".equals(query.get(0).get("HZSXBH")) && !key.equals(query.get(0).get("HZSXBH")) ){
                    runner.update(item2ItemSql,
                            infoMap.get(key).get("XZQHDM"),
                            key,
                            infoMap.get(key).get("SPSXMC"),
                            "无".equals(query.get(0).get("HZSXBH"))?"无":infoMap.get(query.get(0).get("HZSXBH")).get("XZQHDM"),
                            query.get(0).get("HZSXBH"),
                            "无".equals(query.get(0).get("HZSXBH"))?"无":infoMap.get(query.get(0).get("HZSXBH")).get("SPSXMC")
                    );
                }else if(!query.isEmpty() && query.get(0)!=null && key.equals(query.get(0).get("HZSXBH")) && query.size() > 1 && !"无".equals(query.get(1).get("HZSXBH"))){
                    runner.update(item2ItemSql,
                            infoMap.get(key).get("XZQHDM"),
                            key,
                            infoMap.get(key).get("SPSXMC"),
                            "无".equals(query.get(1).get("HZSXBH"))?"无":infoMap.get(query.get(1).get("HZSXBH")).get("XZQHDM"),
                            query.get(1).get("HZSXBH"),
                            "无".equals(query.get(1).get("HZSXBH"))?"无":infoMap.get(query.get(1).get("HZSXBH")).get("SPSXMC")
                    );
                }else{
                    runner.update(item2ItemSql,
                            infoMap.get(key).get("XZQHDM"),
                            key,
                            infoMap.get(key).get("SPSXMC"),
                            "无",
                            "无",
                            "无");
                }
                logger.info(item2ItemSql);
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
        });

        long end = System.currentTimeMillis();
        logger.info("处理10w条数据花费 {} ms",(end - start));

思路二

程序一:按照办理时间查询出每个事项的后置事项

第一步,在申办表相关字段添加索引,以加快查询效率;

第二步,查询出按照申办人和时间升序的数据;

第三步,对第二步的数据进行顺序读取,每遍历循环一次都保存当前记录的上一条记录;

第四步,在第三步每一次循环,都对当前遍历到的记录跟临时保存的上一条记录进行对比,若两者的申请人相同,则使用redis的jedis.hincrBy()方法,设置临时保存的上一条记录的后置事项;

第五步,以第四步类推将临时结果保存在redis。顺便记录总耗时和每一万条处理的时间。

QueryRunner runner = QueryRunnerUtils.getRunner("basicdb");
        long start = System.currentTimeMillis();

        String orderBySql = "SELECT SQRZJHM,SBSJ,SPSXBH FROM A_APPLICATION3 WHERE SQRLX = '2' ORDER BY SQRZJHM ASC,SBSJ ASC";
        // 人与人的事项关系
//        String orderBySql = "SELECT SQRZJHM,SBSJ,SPSXBH FROM A_APPLICATION1_PERSON ORDER BY SQRZJHM ASC,SBSJ ASC";
        // 企业与企业的事项关系
//        String orderBySql = "SELECT SQRZJHM,SBSJ,SPSXBH FROM A_APPLICATION1_ENTERPRISE ORDER BY SQRZJHM ASC,SBSJ ASC";

        // 使用ResultSet方式遍历不占用内存

        Jedis jedis = RedisUtils.getJedis();
        int insertSize = runner.query(orderBySql, rs -> {
            int allSize = 0;
            int orderSize = 0;
            int hold = 10000;
            int insertTime = 0;
            Map<String ,Object> last = new HashMap<>();
            while (rs.next()){
                allSize ++;
                orderSize ++;
                Map<String , Object> rowdata = toMap(rs);
                if(!last.isEmpty()) {
                    if (rowdata.get("SQRZJHM").equals(last.get("SQRZJHM"))) {
                        last.put("HZSXBH", rowdata.get("SPSXBH"));
                    } else {
                        last.put("HZSXBH", "无");
                    }
                    jedis.hincrBy("item_" + last.get("SPSXBH"), (String) last.get("HZSXBH"),1);
                    if(orderSize == hold){
                        long end1 = System.currentTimeMillis();
                        logger.info("已处理{}w条数据,花费 {} ms", ++insertTime, (end1 - start));
                        // 清空计数器
                        orderSize = 0;
                    }
                }
                last = rowdata;
            }

            return allSize;
        });

        RedisUtils.returnResource(jedis);
        long end = System.currentTimeMillis();
        logger.info("共处理 {} 条数据,耗时 {} ms",insertSize,(end-start));

程序二:对每个事项的后置事项进行统计,次数最多的为后置事项

第一步,查出所有申办表的数据,筛选出事项编码和事项名称等字段存于内存中,用于翻译;

第二步,取出程序一暂存在redis的所有key,即事项编码;

第三步,遍历第二步的每个key,使用redis的SortedSet的方法,计算排行榜,算出每个事项的后置事项出现的次数;

第四步,取出第三步得到的次数排行榜排名前三的key,判断key是否是本身或者是空,否则选择排名第三的的key作为当前key的后置事项;

第五步,在第四步分析完,分别对每个事项的信息和后置事项信息的信息通过取第一步的临时容器的数据进行填充,进行入库处理。

final Logger logger = LoggerFactory.getLogger(Thing2ThingHandlerRedisInsert.class);
        QueryRunner runner = QueryRunnerUtils.getRunner("basicdb");
        QueryRunner runnerB = QueryRunnerUtils.getRunner("webdb");
        long start = System.currentTimeMillis();
//        String sql = "select XZQHDM,SPSXBH,SPSXMC from A_APPLICATION1_PERSON";
//        String sql = "select XZQHDM,SPSXBH,SPSXMC from A_APPLICATION1_ENTERPRISE";
        String sql = "select XZQHDM,SPSXBH,SPSXMC from A_APPLICATION3";
        // 保存翻译信息
        Map<String,Map<String,Object>> infoMap = new HashMap<>();
        runner.query(sql,rs -> {
           while (rs.next()){
               Map<String, Object> map = toMap(rs);
               String sxbh = (String) map.get("SPSXBH");
//               sxbh = sxbh.replaceAll(" ","").replaceAll(",","").replaceAll("'","");
               if (!infoMap.containsKey(sxbh)) {
                   infoMap.put(sxbh, map);
               }
           }
            return null;
        });
        logger.info(sql);
        System.out.println(infoMap);

        Jedis jedis = RedisUtils.getJedis();
        // 获取所有事项的key
        Set<String> allItem = jedis.keys("item_*");
        String item2ItemSql = "insert into jzts_item_item_new (itemtype,area,itemcode,itemname,afterarea,afteritemcode,afteritemname) value (2,?,?,?,?,?,?)";
        allItem.forEach(s -> {
            logger.info("事项 {} ",s.substring(5));
            Map<String, String> map = jedis.hgetAll(s);
            map.forEach((key,value) -> {
                // 遍历所有的value,取出最大的value并且key不等于s
                jedis.zadd("range_rank",Double.parseDouble(value),key);
            });
            // sorted set实现排行榜,查询前三个
            Set<String> rangeSet = jedis.zrevrange("range_rank", 0, 2);
            List<String> list1 = new ArrayList<>();
            list1.addAll(rangeSet);
            int size = list1.size();
            // 截取掉item_
            s = s.substring(5);
            String key = "";
            if(size == 1){
                if(!s.equals(list1.get(0)) && !"无".equals(list1.get(0))){
                    // insert into item_to_item
                    key = list1.get(0);
                    logger.info("事项 {} 的后置事项为 {}",s,key);
                }else{
                    key = "无";
                    logger.info("事项 {} 的后置事项为 {}",s,key);
                }
            }else if(size == 2){
                if(!list1.get(0).equals(s) && !"无".equals(list1.get(0))){
                    key = list1.get(0);
                    logger.info("事项 {} 的后置事项为 {}",s,key);
                }else if(!list1.get(1).equals(s) && !"无".equals(list1.get(1))){
                    key = list1.get(1);
                    logger.info("事项 {} 的后置事项为 {}",s,key);
                }else{
                    key = "无";
                    logger.info("事项 {} 的后置事项为 {}",s,key);
                }
            }else if(size ==3){
                if(!list1.get(0).equals(s) && !"无".equals(list1.get(0))){
                    key = list1.get(0);
                    logger.info("事项 {} 的后置事项为 {}",s,key);
                }else if(!list1.get(1).equals(s) && !"无".equals(list1.get(1))){
                    key = list1.get(1);
                    logger.info("事项 {} 的后置事项为 {}",s,key);
                }else if(!list1.get(2).equals(s) && !"无".equals(list1.get(2))){
                    key = list1.get(2);
                    logger.info("事项 {} 的后置事项为 {}",s,key);
                }else{
                    key = "无";
                    logger.info("事项 {} 的后置事项为 {}",s,key);
                }
            }
            try {
                runnerB.update(item2ItemSql,
                        infoMap.get(s).get("XZQHDM"),
                        s,
                        infoMap.get(s).get("SPSXMC"),
                        "无".equals(key)?"无":infoMap.get(key).get("XZQHDM"),
                        key,
                        "无".equals(key)?"无":infoMap.get(key).get("SPSXMC")
                );
            } catch (SQLException e) {
                e.printStackTrace();
            }
            // 删除sorted set 的key
            jedis.del("range_rank");
        });
        // 清空所有key
        jedis.flushDB();
        RedisUtils.returnResource(jedis);
        long end = System.currentTimeMillis();
        logger.info("共处理 {} 事项,耗时 {} ms",allItem.size(),(end-start));

程序流程图

总结

这个任务前后尝试了几种方式实现,最后使用redis保存所有事项的处理情况,采用hash的数据结构,来代替创建以事项为表名的临时表方案。处理速度确实快了不少,纯内存操作比写入磁盘的临时表方案更快。

第二步计算办事次数,使用了redis的sorted set的方式来排序,比思路一使用Java的自定义排序compareTo方法,效率更高。

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页