java - 通过jdbc批量插入记录时内存不足

Question

我想将 originDB(sqlite3) 中的一个表(1000 万条记录)复制到另一个名为 targetDB 的数据库中。
我的方法的流程是:从源表读取数据并生成ResultSet，然后对每条记录生成相应的插入sql，并在记录数达到10000时执行commit批量插入。
代码如下:

public void transfer() throws IOException, SQLException {
    targetDBOperate.setCommit(false);//batch insert
    int count = 0;
    String[] cols = parser(propertyPath);//get fields of data table
    String query = "select * from " + originTable;
    ResultSet rs = originDBOperate.executeQuery(query);//get origin table
    String base = "insert into " + targetTable;
    while(rs.next()) {
        count++;
        String insertSql = buildInsertSql(base,rs,cols);//corresponding insert sql
        targetDBOperate.executeSql(insertSql);
        if(count%10000==0) {
            targetDBOperate.commit();// batch insert
        }
    }
    targetDBOperate.closeConnection();
}

下图是内存使用趋势，纵轴代表内存使用情况

我们可以说它会越来越大，直到内存不足。 stackoverflow 有一些相关问题，例如 Out of memory when inserting records in SQLite, FireDac, Delphi ，但是我还没有解决我的问题，因为我们使用了不同的实现方法。
我的假设是，当记录数没有达到10000时，这些相应的插入sql将被缓存在内存中，并且当它们没有被删除时默认执行commit？每一条建议都将不胜感激。

Answer 1

通过在 SQLite 或任何其他关系数据库中移动更多行，您应该遵循一些基本原则:

1) 将 autoCommit 设置为 false，即不提交每个插入

2)使用批量更新，即不要对每一行进行往返

3)使用准备好的语句，即不解析每个插入。

将它们放在一起，您将得到以下代码:

cn 是源连接，cn2 是目标连接。

对于每个插入的行，您调用 addBatch，但每个 batchSize 仅调用一次 executeBatch 来启动往返。

不要忘记循环结束时的最后一个executeBatch以及最后的commit。

cn2.setAutoCommit(false)

String SEL_STMT = "select id, col1,col2 from tab1"
String INS_STMT = "insert into tab2(id, col1,col2) values(?,?,?)"

def batchSize = 10000


def stmt = cn.prepareStatement(SEL_STMT)
def stmtIns = cn2.prepareStatement(INS_STMT) 

rs = stmt.executeQuery()

while(rs.next())
  {
    stmtIns.setLong(1,rs.getLong(1))
    stmtIns.setString(2,rs.getString(2))
    stmtIns.setTimestamp(3,rs.getTimestamp(3))
    stmtIns.addBatch();
    i += 1

    if (i == batchSize) {
        def insRec = stmtIns.executeBatch();
        i = 0
        }

  }
rs.close()
stmt.close()

def insRec = stmtIns.executeBatch();

stmtIns.close()
cn2.commit()

使用 sqlite-jdbc-3.23.1 根据您的大小进行示例测试:

inserted rows: 10000000
total time taken to insert the batch = 46848 ms

我没有观察到任何内存问题或大型事务的问题