SELECT语句使用JDBC和Hibernate批量获取

介绍

现在，我已经介绍了Hibernate对INSERT ， UPDATE和DELETE语句的批处理支持，是时候分析SELECT语句结果集的批量提取了。

JDBC ResultSet提供了一个客户端代理游标，用于获取当前语句的返回数据。 执行该语句后，必须将结果从数据库游标传输到客户端。 此操作可以立即执行，也可以根据需要执行。

ResultSet游标有三种类型 ：

游标类型	描述
TYPE_FORWARD_ONLY	这是默认的ResultSet游标类型。 结果集只能向前移动，并且结果数据可以一次获取，也可以在迭代游标时检索。 数据库可以决定在查询开始时还是在获取时获取可用的数据。
TYPE_SCROLL_INSENSITIVE	可以向前和向后滚动结果集，并且结果数据对游标仍处于打开状态时发生的并发更改不敏感
TYPE_SCROLL_SENSITIVE	可以向前和向后滚动结果集，并且结果数据对在游标仍处于打开状态时发生的并发更改敏感 。 因此，数据是按需获取的，而不是从数据库游标缓存中获取的

并非所有数据库驱动程序都实现所有游标类型，并且批处理获取行为是通过JDBC语句 fetchSize属性控制的，根据Javadoc ：

当此Statement生成的ResultSet对象需要更多行时，向JDBC驱动程序提供有关应该从数据库中获取的行数的提示。 如果指定的值为零，则忽略提示。 默认值为零。

因此，默认的获取策略是特定于数据库的，从应用程序性能的角度来看，这方面在调整数据访问层时非常重要：

• 甲骨文
  默认情况下， Oracle JDBC运行查询时，它一次从数据库游标中检索10行的结果集。根据Oracle JDBC驱动程序文档 ：“合理的”取决于应用程序的详细信息。 Oracle建议fetchSize不超过100，尽管在某些情况下可能更合适。 对于某些查询，即使返回许多行， fetchSize可能也会过大。
• 的MySQL
  默认情况下， 结果集是完全检索和存储在内存中。 在大多数情况下，这是最有效的操作方式，并且由于MySQL网络协议的设计，因此更易于实现。
• SQL服务器
  通常，当用于SQL Server的Microsoft JDBC驱动程序执行查询时，驱动程序会将所有结果从服务器检索到应用程序内存中。 尽管这种方法最大程度地减少了SQL Server上的资源消耗，但它可以在JDBC应用程序中引发产生非常大结果的查询的OutOfMemoryError 。
• PostgreSQL的
  默认情况下，驱动程序会立即收集查询的所有结果。 这对于大型数据集可能很不方便，因此JDBC驱动程序提供了一种将ResultSet基于数据库游标并且仅获取少量行的方法。
• DB2
  默认情况下，驱动程序会立即收集查询的所有结果。 这对于大型数据集可能很不方便，因此JDBC驱动程序提供了一种将ResultSet基于数据库游标并且仅获取少量行的方法。 fetchSize属性与queryDataSize属性不同。 fetchSize影响返回的行数，而queryDataSize影响返回的字节数。

  例如，如果结果集大小为50 KB，而queryDataSize的值为32767（32KB），则需要两次到数据库服务器的行程才能检索结果集。 但是，如果将queryDataSize设置为65535（64 KB），则只需一趟数据源即可检索结果集。

Java Persistence Query接口通过Query.getResultList（）方法调用仅提供全结果检索。

Hibernate还通过其特定的Query.scroll（） API支持可滚动的ResultSet游标。

可滚动的ResultSets唯一明显的优点是，由于可以按需获取数据，因此我们可以避免客户端的内存问题。 这听起来似乎是很自然的选择，但实际上，由于以下原因，您不应该获取大型结果集：

• 结果集较大会占用大量数据库服务器资源，并且由于数据库是高度并发的环境 ，因此可能会妨碍可用性和可伸缩性
• 表的大小趋于增长，适度的结果集可能很容易变成很大的表。 这种情况发生在生产系统中，很早就发布了应用程序代码。 由于用户只能浏览整个结果集中的一小部分，因此分页是一种更具可伸缩性的数据提取方法
• 过于常见的偏移分页不适用于大型结果集（因为响应时间随页码线性增加），并且在遍历大型结果集时应考虑键集分页 。 键集分页提供了恒定的响应时间 ，对正在获取的页面的相对位置不敏感
• 即使对于批处理作业 ，将处理项目限制为适当的批处理大小总是比较安全的。 大批量可能导致内存问题或导致长时间运行的事务，从而增加了撤消/重做事务日志的大小

测试时间

我们的域实体模型如下所示：

以下测试将用于验证各种结果集的获取行为：

@Test
public void testFetchSize() {
    doInTransaction(session -> {
        int batchSize = batchSize();
        for(int i = 0; i < itemsCount(); i++) {
            Post post = new Post(String.format(
                "Post no. %d", i));
            int j = 0;
            post.addComment(new Comment(
                    String.format(
                "Post comment %d:%d", i, j++)));
            post.addComment(new Comment(
                    String.format(
                "Post comment %d:%d", i, j++)));
            session.persist(post);
            if(i % batchSize == 0 && i > 0) {
                session.flush();
                session.clear();
            }
        }
    });

    long startNanos = System.nanoTime();
    LOGGER.info("Test fetch size");
    doInTransaction(session -> {
        List posts = session.createQuery(
            "select p " +
            "from Post p " +
            "join fetch p.comments ")
        .list();
        LOGGER.info("{}.fetched {} entities",
            getClass().getSimpleName(),
            posts.size());

    });
    LOGGER.info("{}.testFetch took {} millis",
        getClass().getSimpleName(),
        TimeUnit.NANOSECONDS.toMillis(
            System.nanoTime() - startNanos
    ));
}

要将Hibernate配置为使用显式Statement fetchSize ，我们需要设置以下Hibernate属性：

properties.put("hibernate.jdbc.fetch_size", fetchSize());

每个测试将插入5000个Post实体，每个实体具有2个Comment 。

针对商业数据库运行第一个测试，结果如下：

提取大小	持续时间[毫秒]
1个	1190
10	640
100	481
1000	459
10000	449
默认值（10）	545

提取大小越大，则提取整个结果集所需的往返行程越少。 如果返回的行包含许多列，则较大的访存大小将按比例需要较大的数据库缓冲区。

第二轮测试针对PostgreSQL 9.4运行，结果如下：

提取大小	持续时间[毫秒]
1个	1181
10	572
100	485
1000	458
10000	437
默认（全部）	396

即使fetchSize等于要返回的总行数，默认的fetch大小也会产生最佳结果。 由于没有上限缓冲区限制，因此在检索大型结果集时，默认的提取大小可能会导致OutOfMemoryError问题。

结论

虽然大多数数据库服务都不会对结果集的获取大小施加默认上限，但是最好限制整个结果集（如果要求允许的话）。 大小有限的结果集应解决无限制的获取大小缺陷，同时即使在查询的数据逐渐增长的情况下，也要确保可预测的响应时间。 查询越短，行级锁被释放的越快，数据访问层的可伸缩性就越高 。

• 代码可在GitHub上获得 。

翻译自: https://www.javacodegeeks.com/2015/04/select-statements-batch-fetching-with-jdbc-and-hibernate.html