JDBC使用MySQL选择批处理/获取大小 [英] JDBC Select batching/fetch-size with MySQL

问题描述

我有一个使用JDBC的Java应用程序，该应用程序每天在服务器上运行一次，并与也在同一服务器上运行的MySQL数据库(v5.5)进行交互.该应用正在查询并迭代表中的所有行.

I have a Java application using JDBC that runs once per day on my server and interacts with a MySQL database (v5.5) also running on the same server. The app is querying for and iterating through all rows in a table.

该表目前很小(约5000行)，但将无限期地增长.我的服务器内存有限，我不认为应用程序的内存消耗不确定.

The table is reasonably small at the moment (~5000 rows) but will continue to grow indefinitely. My servers memory is limited and I don't like the idea of the app's memory consumption being indeterminate.

如果在运行查询之前使用statement.setFetchSize(n)，则我不清楚这里发生了什么.例如，如果我使用类似的内容:

If I use statement.setFetchSize(n) prior to running the query, it's unclear to me what is happening here. For example, if I use something like:

PreparedStatement query = db.prepareStatement("SELECT x, y FROM z");
query.setFetchSize(n);
ResultSet result = query.executeQuery();
while ( result.next() ){
    ...
}

这是如何适当控制潜在的大型选择查询吗?这里发生了什么事?如果n是1000，那么MySQL一次只能将1000行读入内存(知道它停在哪里)，然后每次需要获取下1000行(或很多)吗?

Is this how to appropriately control potentially large select queries? What's happening here? If n is 1000, then will MySQL only pull 1000 rows into memory at a time (knowing where it left off) and then grab the next 1000 (or however many) rows each time it needs to?

修改:
现在很明显，设置获取大小对我没有用.请记住，我的应用程序和MySQL服务器都在同一台计算机上运行.如果MySQL将整个查询结果都拉入内存，那么这也会影响应用程序，因为它们都共享相同的物理内存.

It's clear to me now that setting the fetch size is not useful for me. Remember that my application and MySQL server are both running on the same machine. If MySQL is pulling in the entire query result into memory, then that affects the app too since they both share the same physical memory.

推荐答案

除非将获取大小设置为Integer.MIN_VALUE，否则MySQL Connector/J驱动程序将获取所有行.一次将获取一行(AFAIK).参见MySQL Connector/J JDBC API实现 ResultSet 下的注释.

The MySQL Connector/J driver will fetch all rows, unless the fetch size is set to Integer.MIN_VALUE (in which case it will fetch one row at a time AFAIK). See the MySQL Connector/J JDBC API Implementation Notes under ResultSet.

如果您希望内存使用成为问题(或实际上是问题)，则还可以使用LIMIT子句(而不是使用setFetchSize(Integer.MIN_VALUE))实现分页.

If you expect memory usage to become a problem (or when it actually becomes a problem), you could also implement paging using the LIMIT clause (instead of using setFetchSize(Integer.MIN_VALUE)).

这篇关于JDBC使用MySQL选择批处理/获取大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！