java数据迁移，内存溢出

川长思鸟来 2022-04-10 01:22 173阅读 0赞

最近要将一个老系统的数据迁移到新系统来，当然是将老数据库中的数据放到新数据库中来。  
当查询数据的时候出现了jvm内存溢出，这里记录一下

原先的代码，这代码一看就有问题，按时间查询，查询出的东西是放在jvm内存里的，如果时间短，数据少那当然没啥问题，但是如果我一下子要迁移2年的数据，那数据量就非常大，内存就可能溢出了。

ResultSet select = JdbcHelper.select(con, "SELECT * FROM jc_content_ext cex \n" +
                                    "\t\tLEFT JOIN jc_content jcon ON jcon.`content_id` = cex.`content_id`\n" +
                                    "                LEFT JOIN jc_content_txt ctx ON cex.`content_id` = ctx.`content_id` \n" +
                                    "                LEFT JOIN jc_content_channel jcc ON cex.`content_id` = jcc.`content_id` \n" +
                                    "                LEFT JOIN jc_channel jc ON jcc.`channel_id` = jc.`channel_id` \n" +
                                    "                LEFT JOIN jc_channel_ext jct ON jct.`channel_id` = jcc.`channel_id`\n" +
                                    "                LEFT JOIN jc_user_ext jue ON jue.`user_id` = jcon.`user_id`\n" +
                                    "                WHERE 1 = 1 AND  cex.`release_date` BETWEEN '"+TAG_LAST_TIME+"' AND '"+TAG_THIS_TIME+"' AND jcon.`status` = 2 LIMIT " + pageIndex +"," + pageSize
                            );
                            handleResult(select, columnMap, con);

优化：还是分页查询，先查询出时间段内总共的记录数，根据记录数的范围，然后分页查询。  
优化后的代码。这里我按500/页来查询：  
注意1：这里查询数据总数的时候，还遇见了一些问题。  
如果直接count(\*),ResultSet 是无法通过列名得到总的数据数。  
这时得随便找一个列名，然后起个别名，之后，取总记录数的时候，用该别名才能得到。原因嘛，我们不是用mybatis，也不是在mysql直接查，它是将结果集放在了ResultsSet里，所以我们直接是拿不到数据的，  
不起别名的话是这样![在这里插入图片描述][20181221090614160.png]  
。这样就不好拿到数据了。这时的结果集，只有这个，如果起过别名![在这里插入图片描述][20181221090745808.png]，这时我们就可以直接通过rs.getString(“a”)来得到列明为a的数据了。  
当然也可以一次查询出所有数据，然后循环结果集rs。但是如果数据太大，还是会导致内存溢出。  
注意2：  
在用COUNT(cex.content\_id) as a取出数据的时候要，先 rs.next();，再rs.getString(“a”)。才能取到总的记录数。否则会异常。

ResultSet rs = JdbcHelper.select(con, "SELECT COUNT(cex.content_id) as a FROM jc_content_ext cex \n" +
                        "\t\tLEFT JOIN jc_content jcon ON jcon.`content_id` = cex.`content_id`\n" +
                        "                LEFT JOIN jc_content_txt ctx ON cex.`content_id` = ctx.`content_id` \n" +
                        "                LEFT JOIN jc_content_channel jcc ON cex.`content_id` = jcc.`content_id` \n" +
                        "                LEFT JOIN jc_channel jc ON jcc.`channel_id` = jc.`channel_id` \n" +
                        "                LEFT JOIN jc_channel_ext jct ON jct.`channel_id` = jcc.`channel_id`\n" +
                        "                LEFT JOIN jc_user_ext jue ON jue.`user_id` = jcon.`user_id`\n" +
                        "                WHERE 1 = 1 AND  cex.`release_date` BETWEEN '"+TAG_LAST_TIME+"' AND '"+TAG_THIS_TIME+"' AND jcon.`status` = 2"
                );
    
                System.out.println("总条数++++++"+ rs.getRow());
                System.out.println("总条数++++++"+ rs);
    
                rs.next();
                System.out.println(rs.getString("a"));
                int count = Integer.parseInt(rs.getString("a"));
                System.out.println(count);
    
    
                if(count > 0 ){
                    if(count <= 500 ){
                        System.out.println("小于500 ================== " + count);
                        ResultSet select = JdbcHelper.select(con, "SELECT * FROM jc_content_ext cex \n" +
                                "\t\tLEFT JOIN jc_content jcon ON jcon.`content_id` = cex.`content_id`\n" +
                                "                LEFT JOIN jc_content_txt ctx ON cex.`content_id` = ctx.`content_id` \n" +
                                "                LEFT JOIN jc_content_channel jcc ON cex.`content_id` = jcc.`content_id` \n" +
                                "                LEFT JOIN jc_channel jc ON jcc.`channel_id` = jc.`channel_id` \n" +
                                "                LEFT JOIN jc_channel_ext jct ON jct.`channel_id` = jcc.`channel_id`\n" +
                                "                LEFT JOIN jc_user_ext jue ON jue.`user_id` = jcon.`user_id`\n" +
                                "                WHERE 1 = 1 AND  cex.`release_date` BETWEEN '"+TAG_LAST_TIME+"' AND '"+TAG_THIS_TIME+"' AND jcon.`status` = 2"
                        );
                        handleResult(select, columnMap, con);
    
                        con.close();
                    }else{
                   
                        int row = count;
                        int pageCount = row / 500 ;
    
                        for(int i =0; i<pageCount; i++){
                            int pageIndex = i * 500;
                            int pageSize  = 500 ;
                            ResultSet select = JdbcHelper.select(con, "SELECT * FROM jc_content_ext cex \n" +
                                    "\t\tLEFT JOIN jc_content jcon ON jcon.`content_id` = cex.`content_id`\n" +
                                    "                LEFT JOIN jc_content_txt ctx ON cex.`content_id` = ctx.`content_id` \n" +
                                    "                LEFT JOIN jc_content_channel jcc ON cex.`content_id` = jcc.`content_id` \n" +
                                    "                LEFT JOIN jc_channel jc ON jcc.`channel_id` = jc.`channel_id` \n" +
                                    "                LEFT JOIN jc_channel_ext jct ON jct.`channel_id` = jcc.`channel_id`\n" +
                                    "                LEFT JOIN jc_user_ext jue ON jue.`user_id` = jcon.`user_id`\n" +
                                    "                WHERE 1 = 1 AND  cex.`release_date` BETWEEN '"+TAG_LAST_TIME+"' AND '"+TAG_THIS_TIME+"' AND jcon.`status` = 2 LIMIT " + pageIndex +"," + pageSize
                            );
                            handleResult(select, columnMap, con);
                        }
                        con.close();
    
                    }
                }

[20181221090614160.png]: /images/20220404/b26da19c3e654fc8a1fdb34aa09a3213.png
[20181221090745808.png]: /images/20220404/9fd37bd2d6734ee7a76c2d470ccb34f2.png