Java 使用 MySQL 流式传输大型结果集

Streaming large result sets with MySQL

提问by configurator

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.

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

I tried using

我尝试使用

statement.setFetchSize(Integer.MIN_VALUE);

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 都挂了close()；在网上查看我发现发生这种情况是因为它在关闭 ResultSet 之前尝试加载任何未读的行，但事实并非如此，因为我这样做了：

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

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.

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

---

---

Stack trace of the hang:

挂起的堆栈跟踪：

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

SocketInputStream.socketRead0(FileDescriptor, byte[], int, int, int) 行：不可用 [本机方法]
SocketInputStream.read(byte[], int, int) 行：129
ReadAheadInputStream.fill(int) 行：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) 行：2882
MysqlIO.reuseAndReadPacket(Buffer) 行：2871
MysqlIO.checkErrorPacket(int) 行：3414
MysqlIO.checkErrorPacket() 行：910
MysqlIO.nextRow(Field[], int, boolean, int, boolean, boolean,布尔值，缓冲区）行：1405
RowDataDynamic.nextRecord() 行：413
RowDataDynamic.next() 行：392 RowDataDynamic.close() 行：170
JDBC4ResultSet(ResultSetImpl).realClose(boolean) 行：7473 JDBC4ResultSet(ResultSetImpl).close() 行：881 DelegatingResultSet () 行：152
DelegatingResultSet.close() 行：152
DelegatingPreparedStatement(DelegatingStatement).close() 行：163
（这是我的类） Database.close() 行：84

采纳答案by configurator

Don't close your ResultSets twice.

不要关闭你的ResultSets 两次。

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

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

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

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

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

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

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

我们已经将 all 替换ResultSet.close()为results.getStatement().close()并删除了所有Statement.close()s，现在问题解决了。

回答by BalusC

Only setting the fetch size is not the correct approach. The javadoc of Statement#setFetchSize()already states the following:

仅设置提取大小不是正确的方法。的javadocStatement#setFetchSize()已经说明以下内容：

Gives the JDBC driver a hintas to the number of rows that should be fetched from the database

为 JDBC 驱动程序提供有关应从数据库中提取的行数的提示

The driver is actually free to apply or ignore the hint. Some drivers ignore it, some drivers apply it directly, some drivers need more parameters. The MySQL JDBC driver falls in the last category. If you check the MySQL JDBC driver documentation, you'll see the following information (scroll about 2/3 down until header ResultSet):

驱动程序实际上可以自由应用或忽略提示。有些驱动程序忽略它，有些驱动程序直接应用它，有些驱动程序需要更多参数。MySQL JDBC 驱动程序属于最后一类。如果您查看MySQL JDBC 驱动程序文档，您将看到以下信息（向下滚动大约 2/3 直到标题ResultSet）：

To enable this functionality, you need to create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

要启用此功能，您需要按以下方式创建 Statement 实例：

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

Please read the entiresection of the document, it describes the caveats of this approach as well. Here's a relevant cite:

请阅读文档的整个部分，它也描述了这种方法的注意事项。这是一个相关的引用：

There are some caveats with this approach. You will have to read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.

(...)

If the statement is within scope of a transaction, then locks are released when the transaction completes (which implies that the statement needs to complete first). As with most other databases, statements are not complete until all the results pending on the statement are read or the active result set for the statement is closed.

这种方法有一些注意事项。您必须先读取结果集中的所有行（或关闭它），然后才能对连接发出任何其他查询，否则将引发异常。

（……）

如果语句在事务范围内，则在事务完成时释放锁（这意味着语句需要先完成）。与大多数其他数据库一样，直到读取了该语句上所有挂起的结果或该语句的活动结果集关闭时，语句才会完成。

If that doesn't fix the OutOfMemoryError(not Exception), then the problem is likely that you're storing all the data in Java's memory instead of processing it immediatelyas soon as the data comes in. This would require more changes in your code, maybe a complete rewrite. I've answered similar question before here.

如果这不能解决OutOfMemoryError(not Exception)，那么问题很可能是您将所有数据存储在 Java 的内存中，而不是在数据传入后立即对其进行处理。这可能需要对代码进行更多更改，也许完全重写。我之前在这里回答过类似的问题。

回答by kalpesh

If you are using spring jdbc then you need to use a preparedstatement creator in conjunction with SimpleJdbcTemplate to set the fetchSize as Integer.MIN_VALUE. Its described here http://neopatel.blogspot.com/2012/02/mysql-jdbc-driver-and-streaming-large.html

如果您使用的是 spring jdbc，那么您需要结合 SimpleJdbcTemplate 使用 Preparedstatement creator 将 fetchSize 设置为 Integer.MIN_VALUE。它在这里描述http://neopatel.blogspot.com/2012/02/mysql-jdbc-driver-and-streaming-large.html

回答by Rooney

In case someone has the same problem, I resolved it by using the LIMIT clause in my query.

如果有人遇到同样的问题，我会在查询中使用 LIMIT 子句来解决它。

This issue was reported to MySql as a bug (find it here http://bugs.mysql.com/bug.php?id=42929) which now has a status of "not a bug". The most pertinent part is:

此问题已作为错误报告给 MySql（在此处找到它http://bugs.mysql.com/bug.php?id=42929），现在状态为“不是错误”。最相关的部分是：

There's no way currently to close a result set "midstream"

目前无法关闭结果集“中游”

Since you have to read ALL rows, you will have to limit your query results using a clause like WHERE or LIMIT. Alternatively, try the following:

由于您必须读取所有行，因此您必须使用 WHERE 或 LIMIT 等子句来限制查询结果。或者，尝试以下操作：

ResultSet rs = ...
while(rs.next()) {
   ...
   if(bailOut == true) { break; }
}

while(rs.next()); // This will deplete the remaining rows on the stream

rs.close();

It may not be ideal, but at least it gets you past the hang on close.

它可能并不理想，但至少它可以让您摆脱困境。

回答by loic.jaouen

It hangs because even if you stop listening, the request still goes on. In order to close the ResultSet and Statement in the right order, try calling statement.cancel() first:

它挂起是因为即使您停止收听，请求仍在继续。为了以正确的顺序关闭 ResultSet 和 Statement，请先尝试调用 statement.cancel()：

public void close() {
    try {
        statement.cancel();
        if (resultSet != null)
            resultSet.close();
    } catch (SQLException e) {
        // ignore errors on closing
    } finally {
        try {
            statement.close();
        } catch (SQLException e) {
            // ignore errors on closing
        } finally {
            resultSet = null;
            statement = null;
        }
    }
}

回答by manu

Scrollable Resultset ignore fetchSize and fetches all the rows at once causing out of meory error.

可滚动结果集忽略 fetchSize 并立即获取所有行，从而导致内存不足错误。

For me it worked properly when setting useCursors=true, otherwise The Scrollable Resultset ignores all the implementations of fetch size, in my case it was 5000 but Scrollable Resultset fetched millions of records at once causing excessive memory usage. underlying DB is MSSQLServer.

对我来说，它在设置 useCursors=true 时工作正常，否则 Scrollable Resultset 会忽略所有获取大小的实现，在我的情况下它是 5000，但 Scrollable Resultset 一次获取了数百万条记录，导致内存使用过多。底层数据库是 MSSQLServer。

jdbc:jtds:sqlserver://localhost:1433/ACS;TDS=8.0;useCursors=true

jdbc:jtds:sqlserver://localhost:1433/ACS;TDS=8.0;useCursors=true