从数据库检索顺序数据的最快方法是什么？ [英] What is the fastest way to retrieve sequential data from database?
问题描述
我在数据库中有很多行，必须处理，但由于内存限制，我无法检索所有数据到内存。
I have a lot of rows in a database and it must be processed, but I can't retrieve all the data to the memory due to memory limitations.
目前，我使用LIMIT和OFFSET来检索数据，以便在某些特定的时间间隔内获取数据。
At the moment, I using LIMIT and OFFSET to retrieve the data to get the data in some especified interval.
我想知道是更快的方式还是另一个方法从数据库中的表获取所有数据。无过滤器将被执行，所有行将被处理。
I want to know if the is the faster way or have another method to getting all the data from a table in database. None filter will be aplied, all the rows will be processed.
推荐答案
SELECT * FROM table ORDER BY column
没有理由将整个表吸入RAM。只需打开一个光标并开始读取。
There's no reason to be sucking the entire table in to RAM. Simply open a cursor and start reading. You can play games with fetch sizes and what not, but the DB will happily keep its place while you process your rows.
附录：
好吧，如果你使用Java，那么我有一个好主意你的问题是什么。
Ok, if you're using Java then I have a good idea what your problem is.
首先，重新使用光标。这基本上是一个ResultSet在Java中。一些结果集比其他结果集更灵活，但是其中99％是简单的，只转发结果集，你称为下一个获取每一行。
First, just by using Java, you're using a cursor. That's basically what a ResultSet is in Java. Some ResultSets are more flexible than others, but 99% of them are simple, forward only ResultSets that you call 'next' upon to get each row.
问题在于Postgres JDBC驱动程序。我不知道为什么他们这样做，也许它的规格，也许是别的，但不管，Postgres有一个好奇的特性，如果你的连接的autoCommit设置为true，那么Postgres决定吸收整个结果集在执行方法或第一个下一个方法。不是真的很重要的地方，只有如果你有一个gazillion行，你会得到一个很好的OOM异常。没有帮助。
The problem is specifically with the Postgres JDBC driver. I don't know why they do this, perhaps it's spec, perhaps it's something else, but regardless, Postgres has the curious characteristic that if your Connection has autoCommit set to true, then Postgres decides to suck in the entire result set on either the execute method or the first next method. Not really important as to where, only that if you have a gazillion rows, you get a nice OOM exception. Not helpful.
这很容易就是你所看到的，我很感激它是相当令人沮丧和混乱。
This can easily be exactly what you're seeing, and I appreciate how it can be quite frustrating and confusing.
大多数连接默认为autoCommit = true。只需将autoCommit设置为false。
Most Connection default to autoCommit = true. Instead, simply set autoCommit to false.
Connection con = ...get Connection...
con.setAutoCommit(false);
PreparedStatement ps = con.prepareStatement("SELECT * FROM table ORDER BY columm");
ResultSet rs = ps.executeQuery();
while(rs.next()) {
String col1 = rs.getString(1);
...and away you go here...
}
rs.close();
ps.close();
con.close();
注意缺少异常处理，留给读者做练习。
Note the distinct lack of exception handling, left as an exercise for the reader.
如果您想要更多地控制一次将多少行提取到内存中，可以使用：
If you want more control over how many rows are fetched at a time into memory, you can use:
ps.setFetchSize(numberOfRowsToFetch);
玩这个游戏可能会提高你的表现。
Playing around with that might improve your performance.
如果您关心排序，请确保您在ORDER BY中使用的列上有适当的索引。
Make sure you have an appropriate index on the column you use in the ORDER BY if you care about sequencing at all.
这篇关于从数据库检索顺序数据的最快方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
