上亿数据同步的方法分享

前言

在工作中遇到这样一样场景，我们要从第三方同步一批数据这批数据 对方用的是oracle数据库。 其中一个表里有5亿多数据， 有一百多个字段 ，其中有个clob字段。而且对方的数据库除了主键外 没有任何其他索引 查询很慢。对方给我们开了视图。
一开始通过分页的方式同步 一次取2000 前几页数据同步很快 大概一页也就几毫秒就能查询出来 。但是分页越往后 随着rownum的变大 查询就越来越慢了。到后面预计查询一页要一个多小时 。按照这个速度下去 全部同步完需要三年。而且 分页查询的数据 会不断减慢 ，照此下去 公司倒闭了也同步不完。于是开始研究其他方案了。

kettle 同步

最开始想到的是kettle 首先遇到的问题是 我们用的是某国产数据库 kettle不支持需要先同步到 其他支持的数据库 。于是先测试一下 性能。也是 每次取两万数据进行同步 平均速度大概是十分钟一页。五亿多数据 大概是25000多页 如果时间均匀的话 大概需要 70天左右。至少不会等到公司倒闭。同步了大概十多个小时。之后就开始 内存溢出了。而且这个速度依然很慢。至少赶不上项目验收之前。

jdbc流式查询

于是经过多次研究 从网上看到了这个帖子

链接: https://www.jianshu.com/p/c7c5dbe63019.
讲的是jdbc 流式查询 大概意思是 数据在查询之前先在数据库端准备好。 然后在不断的往出取数据。和读取文件差不多 或者类似 es的游标操作。
核心代码大概如下

int fs=20000;
Connection conn = null;
        PreparedStatement stmt= null;
        ResultSet rs=null;
        conn=conn();
        long st=System.currentTimeMillis();
        try{
            stmt=conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
            int maxRow=stmt.getMaxRows();
            fs=fs> maxRow?fs: maxRow;
           
            stmt.setFetchSize(fs);
            stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
            rs=stmt.executeQuery();
            rs.setFetchSize(fs);
            logger.info(name+" "+DateUtil.getTimeDes(System.currentTimeMillis()-st));
            while(rs.next()){
            }
        }catch (Exception e){
            e.printStackTrace();
            logger.error(e.getMessage(),e);
        }finally {
            JdbcDao.close(rs,stmt,conn);
        }

具体什么意思 还是看原贴吧。原贴介绍比较详细。
改造之后如下

 public List<Map<String,Object>> queryForFetch(String sql, String domain, int fs) throws SQLException, ClassNotFoundException {

        Connection conn = null;
        PreparedStatement stmt= null;
        ResultSet rs=null;
        conn=conn();
        long st=System.currentTimeMillis();
        try{
            stmt=conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
            int maxRow=stmt.getMaxRows();
            fs=fs> maxRow?fs: maxRow;
  
            stmt.setFetchSize(fs);
            stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
            rs=stmt.executeQuery();
            rs.setFetchSize(fs);
            logger.info(name+" "+DateUtil.getTimeDes(System.currentTimeMillis()-st));
            addList(rs);
        }catch (Exception e){
            e.printStackTrace();
            logger.error(e.getMessage(),e);
        }finally {
            JdbcDao.close(rs,stmt,conn);
        }
        logger.info(name+" "+"结束 "+DateUtil.getTimeDes(System.currentTimeMillis()-st));
        return null;
    }

    private void addList(ResultSet rs) throws Exception {
        ResultSetMetaData rsmd = rs.getMetaData();
        int size=rsmd.getColumnCount();
        long start=System.currentTimeMillis();
        List list=new ArrayList();
        while(rs.next()){
            Map<String,Object> map=new HashMap<String,Object>();
            for(int i=1;i<=size;i++){
                String name=rsmd.getColumnLabel(i);
                Object obj=rs.getObject(name);
                map.put(name, obj);
            }
            
            list.add(map);
            if(list.size()==20000){
            	//够2000条执行一次保存
                executeSave(list);
                list=new ArrayList();
                long end=System.currentTimeMillis();
                logger.info(name+" "+i+" " +list.size()+ " "+ DateUtil.getTimeDes(end-start));
                start=end;
                i++;
            }
        }
        //最后在执行一次保存
        executeSave(list);
    }

executeSave 这个方法就不贴了 也是调用jdbc保存数据。

这种方法一开始执行查询慢 大概用了20多分钟。 之后 数据准备好之后 开始用rs.next() 从服务器获取数据 这个时候大概 3-5分钟能取出20000条 最终 用了70多个小时 数据全部同步完成。
这已经是我认知范围内最快的方式了。毕竟 源库是别人的 没索引 没有任何支持。只有一个 只读的账户和一张50亿数据的表。

如果有其他说明更好的方法 欢迎交流。