使用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）行：不可用[native method]

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）行：2428
MysqlIO.reuseAndReadPacket（Buffer，int）行：2882 < br>
MysqlIO.reuseAndReadPacket（缓冲区）行：2871

MysqlIO.checkErrorPacket（int）行：3414

MysqlIO.checkErrorPacket（）行：910

MysqlIO.nextRow（Field []，int，boolean，int，boolean，boolean，boolean，Buffer）行：1405

RowDataDynamic.nextRecord （）行：413

RowDataDynamic.next（）行：392
RowDataDynamic.close（）行：170

JDBC4ResultSet（ResultSetImpl）.realClose（boolean）行： 7473
JDBC4ResultSet（ResultSetImpl）.close（）行：881
DelegatingResultSet.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:

Dele gatingResultSet.close（）行：152

DelegatingPreparedStatement（DelegatingStatement）.close（）行：163

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

I曾经认为挂起是在 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（） with results.getStatement（）。close（）并删除所有 Statement.close（） 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屋！