Статьи

Пакетная выборка операторов SELECT с помощью JDBC и Hibernate

Январь 5, 2019

Вступление

Теперь, когда я рассмотрел поддержку пакетов Hibernate для операторов INSERT , UPDATE и DELETE , пришло время проанализировать пакетную выборку результирующего набора операторов SELECT .

Выборка JDBC ResultSet

JDBC ResultSet предлагает прокси- курсор на стороне клиента для извлечения данных, возвращаемых текущим оператором. Когда инструкция выполняется, результат должен быть перенесен с курсора базы данных на клиентский. Эта операция может быть выполнена сразу или по запросу.

Существует три типа курсоров ResultSet :

Тип курсора Описание
TYPE_FORWARD_ONLY Это тип курсора ResultSet по умолчанию. Результирующий набор может быть перемещен только вперед, а полученные данные могут быть извлечены сразу или извлечены во время итерации курсора. База данных может принять решение о получении данных в том виде, в котором они были доступны на момент начала запроса или при получении.
TYPE_SCROLL_INSENSITIVE Результирующий набор можно прокручивать как вперед, так и назад, и полученные данные нечувствительны к одновременным изменениям, происходящим, когда курсор еще открыт
TYPE_SCROLL_SENSITIVE Результирующий набор можно прокручивать как вперед, так и назад, и полученные данные чувствительны к одновременным изменениям, происходящим, когда курсор еще открыт. Поэтому данные выбираются по требованию, а не из кеша курсора базы данных.

Не все драйверы баз данных реализуют все типы курсоров, и поведение пакетной выборки контролируется через свойство fetchSize оператора JDBC , которое согласно Javadoc :

Дает драйверу JDBC подсказку о количестве строк, которые следует извлечь из базы данных, когда для объектов ResultSet, сгенерированных этим оператором, требуется больше строк. Если указанное значение равно нулю, то подсказка игнорируется. Значение по умолчанию равно нулю.

Поэтому стратегия выборки по умолчанию зависит от базы данных, и с точки зрения производительности приложения этот аспект очень важен при настройке уровня доступа к данным:

  • оракул

    По умолчанию, когда Oracle JDBC выполняет запрос, он извлекает набор результатов из 10 строк за раз из курсора базы данных. Согласно документации по драйверу Oracle JDBC : что «разумно», зависит от деталей приложения. Oracle предлагает, чтобы fetchSize был не более 100, хотя в некоторых случаях может быть целесообразным больший размер. Значение fetchSize, равное 100, может быть неоправданно большим для некоторых запросов, даже если возвращается много строк.

  • MySQL

    По умолчанию ResultSets полностью извлекаются и сохраняются в памяти. В большинстве случаев это наиболее эффективный способ работы, и благодаря дизайну сетевого протокола MySQL его легче реализовать.

  • SQL Server

    Обычно, когда драйвер Microsoft JDBC для SQL Server выполняет запрос, драйвер извлекает все результаты с сервера в память приложения. Хотя этот подход минимизирует потребление ресурсов на SQL Server , он может вызвать ошибку OutOfMemoryError в приложении JDBC для запросов, которые дают очень большие результаты.

  • PostgreSQL

    По умолчанию драйвер собирает все результаты для запроса сразу. Это может быть неудобно для больших наборов данных, поэтому драйвер JDBC предоставляет средство для базирования ResultSet на курсоре базы данных и выборки только небольшого числа строк.

  • DB2

    По умолчанию драйвер собирает все результаты для запроса сразу. Это может быть неудобно для больших наборов данных, поэтому драйвер JDBC предоставляет средство для базирования ResultSet на курсоре базы данных и выборки только небольшого числа строк. Свойство fetchSize отличается от свойства queryDataSize . fetchSize влияет на количество возвращаемых строк, а queryDataSize влияет на количество возвращаемых байтов.

    Например, если размер результирующего набора составляет 50 КБ, а значение queryDataSize равно 32767 (32 КБ), для получения результирующего набора потребуется две поездки на сервер базы данных. Однако, если для queryDataSize задано значение 65535 (64 КБ), для извлечения набора результатов требуется только одна поездка к источнику данных.

Hibernate ResultSet опции обхода

Интерфейс Java Persistence Query предлагает только полные результаты поиска через вызов метода Query.getResultList () .

Hibernate также поддерживает прокручиваемые курсоры ResultSet через специальный API-интерфейс Query.scroll () .

Единственное очевидное преимущество прокручиваемых ResultSets — это то, что мы можем избежать проблем с памятью на стороне клиента, так как данные выбираются по требованию. Это может звучать как естественный выбор, но в действительности вам не следует извлекать большие наборы результатов по следующим причинам:

  • Большие наборы результатов требуют значительных ресурсов сервера базы данных, и поскольку база данных является средой с высокой степенью параллелизма , это может препятствовать доступности и масштабируемости
  • Таблицы имеют тенденцию увеличиваться в размере, и умеренный набор результатов может легко превратиться в очень большой. Такая ситуация возникает в производственных системах еще долго после того, как код приложения был отправлен. Поскольку пользователи могут просматривать только относительно небольшую часть всего набора результатов, разбиение на страницы является более масштабируемой альтернативой извлечения данных.
  • Чрезмерно распространенное разбиение на страницы не подходит для больших наборов результатов (поскольку время отклика увеличивается линейно с номером страницы), и вы должны учитывать разбиение на наборы ключей при обходе больших наборов результатов. Пагинация набора ключей обеспечивает постоянное время отклика, нечувствительное к относительному положению загружаемой страницы.
  • Даже для заданий пакетной обработки всегда безопаснее ограничить обрабатываемые элементы умеренным размером пакета. Большие пакеты могут привести к проблемам с памятью или вызвать длительные транзакции, которые увеличивают размер журнала транзакций отмены / повтора

Время тестирования

Наша модель сущности домена выглядит следующим образом:

postcommentfetchsize

Следующий тест будет использоваться для проверки различных способов выбора набора результатов:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
@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 на использование явного оператора fetchSize , нам нужно установить следующее свойство Hibernate :

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

Каждый тест будет содержать 5000 объектов Post , каждый из которых имеет 2 комментария .

Одна коммерческая база данных

Первые тесты запускаются на коммерческой базе данных со следующими результатами:

Размер выборки Продолжительность [миллис]
1 1190
10 640
100 481
1000 459
10000 449
По умолчанию (10) 545

Чем больше размер выборки, тем меньше циклов требуется для извлечения всего набора результатов. Если возвращаемые строки содержат много столбцов, больший размер выборки потребует пропорционально больших буферов базы данных.

PostgreSQL

Второй тестовый цикл выполняется на PostgreSQL 9.4 со следующими результатами:

Размер выборки Продолжительность [миллис]
1 1181
10 572
100 485
1000 458
10000 437
По умолчанию (все) 396

Размер выборки по умолчанию дает лучший результат, даже если fetchSize равен общему количеству возвращаемых строк. Так как нет ограничения на верхний предел буфера, размер выборки по умолчанию может вызвать проблемы OutOfMemoryError при получении больших наборов результатов.

Вывод

Хотя большинство баз данных не устанавливают верхний предел по умолчанию для размера выборки результирующего набора, рекомендуется ограничить весь набор результатов (если этого позволяют требования). Результирующий набор ограниченного размера должен устранять недостатки неограниченного размера выборки, обеспечивая при этом предсказуемое время отклика, даже когда запрашиваемые данные постепенно растут. Чем короче запросы, тем быстрее снимаются блокировки на уровне строк и тем более масштабируемым становится уровень доступа к данным.

  • Код доступен на GitHub .
Ссылка: Пакетная загрузка операторов SELECT с JDBC и Hibernate от нашего партнера JCG Влада Михалча в блоге Влада Михалча .
53
Share: