使用Hibernate的ScrollableResults逐条读取9000万条记录

Question

我只需要使用Hibernate从MySQL数据库中读取表中的每一行，并根据其编写一个文件。 但是有9000万行数据，这些数据非常庞大。所以以下操作似乎是合适的：

ScrollableResults results = session.createQuery("SELECT person FROM Person person")
            .setReadOnly(true).setCacheable(false).scroll(ScrollMode.FORWARD_ONLY);
while (results.next())
    storeInFile(results.get()[0]);

以上问题是由于前文提到的代码会在进入while循环之前尝试将所有9000万行数据加载到内存中，这将导致OutOfMemoryError: Java heap space异常。那么，滚动结果集（ScrollableResults）似乎并不是我想要的解决方案。有什么适当的方法来处理这个问题吗？我不介意这个while循环花费几天时间（虽然确实不希望）。我猜另一种处理方法是使用setFirstResult和setMaxResults来遍历结果，并且使用普通的Hibernate结果而不是滚动结果集。不过，这样做可能效率低下，而且当我在第8900万行上调用setFirstResult时，它会开始花费非常长的时间......更新：setFirstResult/setMaxResults不可行，因为像我担心的那样，到达偏移量需要太长时间。必须有解决方法！难道这不是相当标准的程序吗？我愿意放弃Hibernate使用JDBC或任何必要的工具。更新2：我想到的解决办法能够运行但效果一般，基本上是这种形式：

select * from person where id > <offset> and <other_conditions> limit 1

由于我有其他条件，即使都在一个索引中，仍然不如我希望的那么快...所以仍然需要其他建议...

- at.

---

你可以将数据进行分区，这样就不必一次性读取太多数据。参考：http://stackoverflow.com/questions/8325745/how-to-implement-several-threads-in-java-for-downloading-a-single-table-data/29502316#29502316 - rogerdpack

Answer 1

使用setFirstResult和setMaxResults是我所知道的唯一选择。

传统上，可滚动的结果集仅在需要时将行传输到客户端。不幸的是，MySQL Connector / J实际上是伪造的，它执行整个查询并将其传输到客户端，因此驱动程序实际上已将整个结果集加载到RAM中，并将其滴灌给您（由内存不足问题所证明）。您的想法是正确的，只是MySQL java驱动中存在缺陷。

我找不到解决办法，所以使用常规的setFirst / max方法加载大块数据。很抱歉要带来坏消息。

确保使用无状态会话，这样就没有会话级别的缓存或脏跟踪等。

注意：

除非退出MySQL J / Connector，否则您的UPDATE 2是您能得到的最好的结果。尽管您可以增加查询的限制，但必须具有足够的RAM来保存索引，这应该是一个相对便宜的操作。我会稍微修改它，一次获取一个批次，并使用该批次的最高id来获取下一个批次。

备注：如果< strong> other_conditions 使用相等性（不允许范围条件）并且将索引的最后一列作为< strong> id ，则此方法将有效。

select * 
from person 
where id > <max_id_of_last_batch> and <other_conditions> 
order by id asc  
limit <batch_size>

- Michael

---

使用StatelessSession是一个特别好的技巧！ - javashlook

setFirstResult和setMaxResults不是可行的选项。 我猜测这样做会变得无法使用，结果证明我是正确的。也许对于小表格来说这个方法可以运用，但很快它就会花费太长时间。您可以在MySQL控制台中通过运行“select * from anything limit 1 offset 3000000”进行测试。可能需要30分钟... - at.

运行“select * from geoplanet_locations limit 1 offset 1900000;”对YAHOO Geoplanet数据集（500万行）进行查询，返回结果仅需1.34秒。如果您有足够的RAM将索引保留在RAM中，那么我认为您30分钟的数字是错误的。有趣的是，“select * from geoplanet_locations where id > 56047142 limit 10;”几乎不需要时间（常规客户端只返回0.00）。 - Michael

@Michael，你是怎么发现MySQL连接器伪造了滚动的？有没有相关的资料可以查阅？我对此很感兴趣，因为我喜欢在NHibernate中使用滚动功能，并且我正在使用.NET的mysql连接器，想检查一下Mysql .Net连接器是否也在伪造滚动，或者这取决于版本？ - darpet

有人知道MySQL连接器是否仍在伪造滚动条吗？ - kibowki

Answer 2

你可以使用 ScrollableResults，尽管它需要一些神秘的咒语才能与 MySQL 协同工作。我在博客文章中详细介绍了我的发现(http://www.numerati.com/2012/06/26/reading-large-result-sets-with-hibernate-and-mysql/)，但我在这里进行概述：

"[JDBC] 文档说：

To enable this functionality, 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);

在Hibernate API 3.2+版本中，可以使用Query接口（对于Criteria也应该适用）来实现：

Query query = session.createQuery(query);
query.setReadOnly(true);
// MIN_VALUE gives hint to JDBC driver to stream results
query.setFetchSize(Integer.MIN_VALUE);
ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);
// iterate over results
while (results.next()) {
    Object row = results.get();
    // process row then release reference
    // you may need to evict() as well
}
results.close();

这样可以让你在结果集中进行流式传输，但是Hibernate仍然会在Session中缓存结果，因此您需要定期调用session.evict()或session.clear()。如果您只是读取数据，则可以考虑使用StatelessSession，但应事先阅读其文档。

- Sean S.

---

为什么在只读会话中要使用 Session#flush()？你确定你不是想使用 Session#evict(row) 或 Session#clear() 以帮助控制一级缓存的大小。 - Darryl Miles

（对于追随者，代码示例用于提及flush，但现在提及evict或clear） - rogerdpack

我在使用Postgres时，省略了setFetchSize。如果你将它保留在代码中，会出现错误。 - Brent Fisher

Answer 3

请按如下方式设置查询中的获取大小。

此外，当不需要缓存时，最好使用StatelessSession。

ScrollableResults results = session.createQuery("SELECT person FROM Person person")
        .setReadOnly(true)
        .setFetchSize( 1000 ) // <<--- !!!!
        .setCacheable(false).scroll(ScrollMode.FORWARD_ONLY)

- Haris

---

这是正确的方法。请参考http://javaquirks.blogspot.dk/2007/12/mysql-streaming-result-set.html获取更多信息。 - sbrattla

你们是说对于MySQL使用Integer.MIN_VALUE，但对于Oracle或其他数据库应将获取大小设置为一个合理的数字吗？ - markthegrea

此解决方案不依赖于数据库。对于任何数据库都适用。 - Haris

Answer 4

FetchSize必须是Integer.MIN_VALUE，否则它将无法工作。

必须从官方参考文献中直接引用：https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html

- ChechuHa

Answer 5

如果你使用了这里提到的答案，实际上你可以得到你想要的东西——MySQL低内存可滚动结果：

使用MySQL流式处理大型结果集

请注意，你会遇到Hibernate懒加载的问题，因为它会在滚动完成之前执行任何查询时抛出异常。

- einnocent

Answer 6

有9000万条记录，看起来你应该批量选择。我在使用Oracle进行初始加载到分布式缓存时就这样做了。查看MySQL文档，相当的方法似乎是使用LIMIT子句：http://dev.mysql.com/doc/refman/5.0/en/select.html

这里有一个例子：

SELECT * from Person
LIMIT 200, 100

这将返回 Person 表的第 201 到 300 行。

您需要先从表中获取记录计数，然后将其除以批处理大小并从那里计算出循环和 LIMIT 参数。

这样做的另一个好处是并行性 - 您可以在此同时执行多个线程以加快处理速度。

处理 9000 万条记录似乎也不是使用 Hibernate 的最佳选择。

- SteveD

---

那个也不管用……试试使用批量选择（无论是批处理还是其他方式）并设置偏移量为百万级别，这将花费很长时间。我愿意绕过Hibernate，有没有不使用Hibernate的建议？ - at.

尝试阅读这篇文章，以解决LIMIT性能问题：http://www.facebook.com/note.php?note_id=206034210932 - SteveD

Answer 7

我建议提供一个基于Hibernate的查询模板，不仅包含示例代码，还能解决分页、滚动和清除 Hibernate 会话的问题。该模板也可轻松适配使用EntityManager。

- smalbequi

Answer 8

问题可能是Hibernate在会话中保留所有对象的引用，直到您关闭会话。这与查询缓存无关。也许在将对象写入文件后，从会话中清除对象(evict())会有所帮助。如果它们不再被会话引用，垃圾收集器可以释放内存，您就不会再遇到内存不足的问题了。

- Reboot

---

问题在于 Hibernate 查询不会返回任何数据，直到所有行都被检索完毕，因此在所有数据加载完成之前，我甚至无法执行 evict() 操作。 - at.

抱歉，我在问题中漏掉了那个。如果这确实是MySQL驱动程序的问题，那么除了将查询自己拆分成多个查询之外，可能没有其他选择，因为这已经被发布过了。我正在使用jTDS驱动程序与MSSQL一起使用ScrollableResults，并且在处理来自数据库的大型数据集时，它有助于防止OutOfMemoryErrors，因此这个想法本身可能并没有错。 - Reboot

Answer 9

最近我遇到了一个类似的问题，并且我写了一篇关于如何解决这个问题的博客。我希望这对任何人都有帮助。 我使用了惰性列表的方法和部分获取。我将查询的限制和偏移量或分页替换为手动分页。 在我的例子中，选择语句返回了1000万条记录，我将它们获取并插入到一个“临时表”中：

create or replace function load_records ()
returns VOID as $$
BEGIN
drop sequence if exists temp_seq;
create temp sequence temp_seq;
insert into tmp_table
SELECT linea.*
FROM
(
select nextval('temp_seq') as ROWNUM,* from table1 t1
 join table2 t2 on (t2.fieldpk = t1.fieldpk)
 join table3 t3 on (t3.fieldpk = t2.fieldpk)
) linea;
END;
$$ language plpgsql;

之后，我可以使用分配的序列而不是计算每一行来进行分页：

select * from tmp_table where counterrow >= 9000000 and counterrow <= 9025000

从Java的角度来看，我通过使用惰性列表实现了部分采集的分页。也就是说，我使用了一个从Abstract List继承并实现get()方法的列表。get方法可以使用数据访问接口来继续获取下一组数据并释放内存堆：

@Override
public E get(int index) {
  if (bufferParcial.size() <= (index - lastIndexRoulette))
  {
    lastIndexRoulette = index;
    bufferParcial.removeAll(bufferParcial);
    bufferParcial = new ArrayList<E>();
        bufferParcial.addAll(daoInterface.getBufferParcial());
    if (bufferParcial.isEmpty())
    {
        return null;
    }

  }
  return bufferParcial.get(index - lastIndexRoulette);<br>
}

另一方面，数据访问接口使用查询进行分页，并实现了一种逐步迭代的方法，每25000条记录完成一次。

这种方法的结果可以在这里看到： http://www.arquitecturaysoftware.co/2013/10/laboratorio-1-iterar-millones-de.html

- user2928872

---

请注意，仅链接答案是不被鼓励的，SO答案应该是寻找解决方案的终点（而不是另一个参考站点，随着时间的推移往往会变得陈旧）。请考虑在此处添加独立的摘要，将链接作为参考。 - kleopatra

Answer 10

我以前成功地使用过Hibernate滚动功能，而不需要读取整个结果集。有人说MySQL不支持真正的滚动游标，但它声称基于JDBC dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)，并且在搜索周围时似乎其他人也使用了它。确保它没有在会话中缓存Person对象 - 我已经在没有实体可缓存的SQL查询上使用它。您可以在循环结束时调用evict来确保或使用sql查询进行测试。还可以尝试使用setFetchSize来优化到服务器的传输次数。

- Brian Deterling