使用 MySQL 流式传输大型结果集 [英] Streaming large result sets with MySQL

问题描述

我正在开发一个使用大型 MySQL 表的 spring 应用程序.加载大表时，我收到 OutOfMemoryException，因为驱动程序试图将整个表加载到应用程序内存中.

I'm developing a spring application that uses large MySQL tables. When loading large tables, I get an OutOfMemoryException, since the driver tries to load the entire table into application memory.

我尝试使用

statement.setFetchSize(Integer.MIN_VALUE);

但随后我打开的每个 ResultSet 都挂在 close() 上；在网上查看我发现发生这种情况是因为它在关闭 ResultSet 之前尝试加载任何未读的行，但事实并非如此，因为我这样做了:

but then every ResultSet I open hangs on close(); looking online I found that that happens because it tries loading any unread rows before closing the ResultSet, but that is not the case since I do this:

ResultSet existingRecords = getTableData(tablename);
try {
    while (existingRecords.next()) {
        // ...
    }
} finally {
    existingRecords.close(); // this line is hanging, and there was no exception in the try clause
}

小表(3 行)也会挂起，如果我不关闭 RecordSet(在一种方法中发生)，则 connection.close() 挂起.

The hangs happen for small tables (3 rows) as well, and if I don't close the RecordSet (which happened in one method) then connection.close() hangs.

挂起的堆栈跟踪:

SocketInputStream.socketRead0(FileDescriptor, byte[], int, int, int) 行:不可用 [本地方法]
SocketInputStream.read(byte[], int, int) 行:129
ReadAheadInputStream.fill(int) 行:113
ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(byte[], int, int) 行:160
ReadAheadInputStream.read(byte[], int, int) 行:188
MysqlIO.readFully(InputStream, byte[], int, int) 行:2428MysqlIO.reuseAndReadPacket(Buffer, int) 行:2882
MysqlIO.reuseAndReadPacket(Buffer) 行:2871
MysqlIO.checkErrorPacket(int) 行:3414
MysqlIO.checkErrorPacket() 行:910
MysqlIO.nextRow(Field[], int, boolean, int, boolean, boolean, boolean, Buffer) 行:1405
RowDataDynamic.nextRecord() 行:413
RowDataDynamic.next() 行:392RowDataDynamic.close() 行:170
JDBC4ResultSet(ResultSetImpl).realClose(boolean) 行:7473JDBC4ResultSet(ResultSetImpl).close() 行:881DelegatingResultSet.close() 行:152
DelegatingResultSet.close() 行:152
DelegatingPreparedStatement(DelegatingStatement).close() 行:163
(这是我的课) Database.close() 行:84

SocketInputStream.socketRead0(FileDescriptor, byte[], int, int, int) line: not available [native method]
SocketInputStream.read(byte[], int, int) line: 129
ReadAheadInputStream.fill(int) line: 113
ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(byte[], int, int) line: 160
ReadAheadInputStream.read(byte[], int, int) line: 188
MysqlIO.readFully(InputStream, byte[], int, int) line: 2428 MysqlIO.reuseAndReadPacket(Buffer, int) line: 2882
MysqlIO.reuseAndReadPacket(Buffer) line: 2871
MysqlIO.checkErrorPacket(int) line: 3414
MysqlIO.checkErrorPacket() line: 910
MysqlIO.nextRow(Field[], int, boolean, int, boolean, boolean, boolean, Buffer) line: 1405
RowDataDynamic.nextRecord() line: 413
RowDataDynamic.next() line: 392 RowDataDynamic.close() line: 170
JDBC4ResultSet(ResultSetImpl).realClose(boolean) line: 7473 JDBC4ResultSet(ResultSetImpl).close() line: 881 DelegatingResultSet.close() line: 152
DelegatingResultSet.close() line: 152
DelegatingPreparedStatement(DelegatingStatement).close() line: 163
(This is my class) Database.close() line: 84

推荐答案

不要关闭你的 ResultSet 两次.

Don't close your ResultSets twice.

显然，当关闭 Statement 时，它会尝试关闭相应的 ResultSet，正如您在堆栈跟踪中的这两行中看到的:

Apparently, when closing a Statement it attempts to close the corresponding ResultSet, as you can see in these two lines from the stack trace:

DelegatingResultSet.close() 行:152
DelegatingPreparedStatement(DelegatingStatement).close() 行:163

DelegatingResultSet.close() line: 152
DelegatingPreparedStatement(DelegatingStatement).close() line: 163

我原以为挂起是在 ResultSet.close() 中，但实际上是在 Statement.close() 中，它调用了 ResultSet.close().由于 ResultSet 已经关闭，它就挂了.

I had thought the hang was in ResultSet.close() but it was actually in Statement.close() which calls ResultSet.close(). Since the ResultSet was already closed, it just hung.

我们已将所有 ResultSet.close() 替换为 results.getStatement().close() 并删除所有 Statement.close()code>s，问题就解决了.

We've replaced all ResultSet.close() with results.getStatement().close() and removed all Statement.close()s, and the problem is now solved.

这篇关于使用 MySQL 流式传输大型结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！