автором

Оптимизация SQL запросов — Entity для промежуточных таблиц в JPA

Оптимизация SQL запросов — Entity для промежуточных таблиц в JPA
автором

Проблема

При работе со Spring Data JPA и связями @ManyToMany часто возникает ситуация, когда Hibernate генерирует неоптимальные SQL-запросы с излишними JOIN’ами.

Исходная ситуация

Допустим, у нас есть entity ProductModel и File, связанные через промежуточную таблицу product_model_files:

@Entity
public class ProductModel {
    @Id
    private Long id;

    @ManyToMany
    @JoinTable(
        name = "product_model_files",
        joinColumns = { @JoinColumn(name = "product_model_id") },
        inverseJoinColumns = { @JoinColumn(name = "file_id") }
    )
    @OrderColumn(name = "ordering")
    private List<File> files = new ArrayList<>();

    // ...остальные поля
}

Для получения файлов по списку ID моделей мы написали такой запрос:

@Query("SELECT new ru.knasys.kross.dao.dto.FileWithModelId(pm.id, f) " +
       "FROM ProductModel pm " +
       "JOIN pm.files f " +
       "WHERE pm.id IN :modelIds " +
       "AND (:#{#types == null || #types.isEmpty()} = true OR f.type IN :types)")
Page<FileWithModelId> getFilesByModelIdIn(
    @Param("modelIds") Collection<Long> modelIds,
    @Nullable @Param("types") Collection<FileType> types,
    Pageable pageable);

Что генерирует Hibernate?

Hibernate создаёт следующий SQL:

SELECT 
    pm1_0.id,
    f1_1.id,
    f1_1.comment,
    f1_1.hash,
    f1_1.is_image_has_white_background,
    f1_1.name,
    f1_1.reference,
    f1_1.size,
    f1_1.type,
    f1_1.upload_date
FROM product_model pm1_0
    JOIN product_model_files f1_0 ON pm1_0.id = f1_0.product_model_id 
    JOIN filestorage_files f1_1 ON f1_1.id = f1_0.file_id
WHERE pm1_0.id IN (?, ?) 
    AND (? = true OR 1 = 0) 
OFFSET ? ROWS FETCH FIRST ? ROWS ONLY

В чём проблема?

Лишний JOIN с таблицей product_model!

Мы используем из таблицы product_model только поле id, но Hibernate всё равно делает JOIN. Это:

  • Увеличивает нагрузку на базу данных
  • Замедляет выполнение запроса
  • Использует больше памяти

Желаемый результат

Мы хотим получить такой SQL:

SELECT 
    f1_0.product_model_id,
    f1_1.id,
    f1_1.comment,
    f1_1.hash,
    f1_1.is_image_has_white_background,
    f1_1.name,
    f1_1.reference,
    f1_1.size,
    f1_1.type,
    f1_1.upload_date
FROM product_model_files f1_0
    JOIN filestorage_files f1_1 ON f1_1.id = f1_0.file_id
WHERE f1_0.product_model_id IN (?, ?) 
    AND (? = true OR 1 = 0) 
OFFSET ? ROWS FETCH FIRST ? ROWS ONLY

Без JOIN’а с product_model!

Решение

Шаг 1: Создаём Entity для промежуточной таблицы

Чтобы начать запрос непосредственно с промежуточной таблицы, нужно создать для неё отдельную Entity:

package ru.knasys.kross.dao.model;

import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;
import java.io.Serializable;

@Entity
@Table(name = "product_model_files")
@Getter
@Setter
public class ProductModelFile {

    @EmbeddedId
    private ProductModelFileId id;

    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("productModelId")
    @JoinColumn(name = "product_model_id")
    private ProductModel productModel;

    @ManyToOne(fetch = FetchType.EAGER)
    @MapsId("fileId")
    @JoinColumn(name = "file_id")
    private File file;

    @Column(name = "ordering")
    private Integer ordering;

    @Embeddable
    @Getter
    @Setter
    public static class ProductModelFileId implements Serializable {
        @Column(name = "product_model_id")
        private Long productModelId;

        @Column(name = "file_id")
        private Long fileId;

        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            ProductModelFileId that = (ProductModelFileId) o;
            return productModelId.equals(that.productModelId) 
                && fileId.equals(that.fileId);
        }

        @Override
        public int hashCode() {
            return 31 * productModelId.hashCode() + fileId.hashCode();
        }
    }
}

Ключевые моменты:

  1. @EmbeddedId — используем составной ключ, так как в промежуточной таблице два внешних ключа
  2. @MapsId — связываем части составного ключа с соответствующими полями
  3. FetchType.EAGER для file — чтобы данные файла подтягивались сразу
  4. FetchType.LAZY для productModel — модель нам не нужна в запросе

Шаг 2: Переписываем запрос

Теперь изменяем наш метод в репозитории:

@Query("SELECT new ru.knasys.kross.dao.dto.FileWithModelId(pmf.id.productModelId, pmf.file) " +
       "FROM ProductModelFile pmf " +
       "WHERE pmf.id.productModelId IN :modelIds " +
       "AND (:#{#types == null || #types.isEmpty()} = true OR pmf.file.type IN :types)")
Page<FileWithModelId> getFilesByModelIdIn(
    @Param("modelIds") Collection<Long> modelIds,
    @Nullable @Param("types") Collection<FileType> types,
    Pageable pageable);

Что изменилось?

  • FROM ProductModelFile pmf вместо FROM ProductModel pm JOIN pm.files f
  • pmf.id.productModelId — обращаемся напрямую к полю составного ключа
  • pmf.file — получаем файл через связь в промежуточной entity

Результат

После изменений Hibernate генерирует оптимальный SQL:

SELECT 
    f1_0.product_model_id,
    f1_1.id,
    f1_1.comment,
    f1_1.hash,
    f1_1.is_image_has_white_background,
    f1_1.name,
    f1_1.reference,
    f1_1.size,
    f1_1.type,
    f1_1.upload_date
FROM product_model_files f1_0
    JOIN filestorage_files f1_1 ON f1_1.id = f1_0.file_id
WHERE f1_0.product_model_id IN (?, ?) 
    AND (? = true OR 1 = 0) 
OFFSET ? ROWS FETCH FIRST ? ROWS ONLY

✅ Нет лишнего JOIN с product_model
✅ Запрос быстрее выполняется
✅ Меньше нагрузка на БД
✅ Код остаётся type-safe и не использует native query

А есть ли альтернативы?

Вы можете спросить: «Неужели Hibernate не может сам оптимизировать такой запрос?»

К сожалению, нет. Hibernate не имеет встроенных средств для автоматической оптимизации таких запросов без создания дополнительной Entity.

Почему так происходит?

Когда вы используете @ManyToMany, Hibernate рассматривает промежуточную таблицу как «невидимую» — она существует только для связывания двух сущностей. В терминах объектно-ориентированного программирования это правильный подход: у вас есть коллекция файлов в модели, а промежуточная таблица — это деталь реализации на уровне БД.

Hibernate не может начать запрос с промежуточной таблицы, если для неё нет явной Entity.

Альтернативные подходы (и почему они не работают)

Давайте рассмотрим другие возможные решения:

1. Native Query ❌

@Query(value = """
    SELECT f1_0.product_model_id, f1_1.*
    FROM product_model_files f1_0
    JOIN filestorage_files f1_1 ON f1_1.id = f1_0.file_id
    WHERE f1_0.product_model_id IN :modelIds
    """, nativeQuery = true)
List<Object[]> getFilesByModelIdIn(@Param("modelIds") Collection<Long> modelIds);

Минусы:

  • ❌ Потеря проверки типов на этапе компиляции
  • ❌ Код становится зависимым от конкретной БД
  • ❌ Сложнее рефакторинг (IDE не помогает при переименовании полей)
  • ❌ Нужно вручную маппить Object[] в DTO
  • ❌ Риск SQL-инъекций при неправильном использовании

2. @EntityGraph ❌

@EntityGraph(attributePaths = "files")
@Query("SELECT pm FROM ProductModel pm WHERE pm.id IN :modelIds")
List<ProductModel> findByIdIn(@Param("modelIds") Collection<Long> modelIds);

Проблема: @EntityGraph контролирует только стратегию загрузки (EAGER/LAZY), но не меняет структуру JOIN’ов. Hibernate всё равно будет начинать запрос с ProductModel:

-- JOIN с product_model остаётся!
FROM product_model pm1_0
    JOIN product_model_files f1_0 ON pm1_0.id = f1_0.product_model_id

3. Criteria API ❌

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<ProductModel> cq = cb.createQuery(ProductModel.class);
Root<ProductModel> root = cq.from(ProductModel.class);
Join<ProductModel, File> filesJoin = root.join("files");
cq.where(root.get("id").in(modelIds));

Проблема: Criteria API также начинает с root entity (ProductModel), поэтому запрос будет таким же неоптимальным. Вы не можете начать запрос с промежуточной таблицы, так как для неё нет Entity.

4. Projection с Tuple ⚠️

@Query("SELECT pm.id, f FROM ProductModel pm JOIN pm.files f WHERE pm.id IN :modelIds")
List<Tuple> getFilesByModelIdIn(@Param("modelIds") Collection<Long> modelIds);

Проблема: Hibernate всё равно делает JOIN с product_model, даже если мы выбираем только pm.id:

-- Лишний JOIN никуда не делся!
FROM product_model pm1_0
    JOIN product_model_files f1_0 ON pm1_0.id = f1_0.product_model_id
    JOIN filestorage_files f1_1 ON f1_1.id = f1_0.file_id

Hibernate не оптимизирует запрос автоматически, даже если понимает, что из product_model нужно только поле id, которое уже есть в промежуточной таблице.

Почему создание Entity — правильное решение?

Создание отдельной Entity для промежуточной таблицы — это не костыль и не workaround, а правильный паттерн проектирования в JPA/Hibernate.

✅ Преимущества подхода с отдельной Entity

1. Полный контроль над запросами

Вы можете начинать запрос с любой таблицы, оптимизируя производительность:

// Начинаем с промежуточной таблицы
FROM ProductModelFile pmf
WHERE pmf.id.productModelId IN :modelIds

// Вместо неоптимального
FROM ProductModel pm
JOIN pm.files f
WHERE pm.id IN :modelIds

2. Type-safe код

Все преимущества JPQL сохраняются:

  • ✅ Проверка на этапе компиляции
  • ✅ Автодополнение в IDE
  • ✅ Рефакторинг работает корректно
  • ✅ Hibernate проверяет корректность запроса при старте приложения
// Если переименуем поле в Entity, IDE обновит все запросы
pmf.id.productModelId  // IDE видит структуру и подсказывает

3. Гибкость для будущих изменений

Промежуточные таблицы часто обрастают дополнительными полями:

@Entity
@Table(name = "product_model_files")
public class ProductModelFile {
    @EmbeddedId
    private ProductModelFileId id;

    @ManyToOne(fetch = FetchType.EAGER)
    @MapsId("fileId")
    private File file;

    // Дополнительные поля "бесплатно"
    @Column(name = "ordering")
    private Integer ordering;

    @CreationTimestamp
    private LocalDateTime createdAt;

    @Column(name = "created_by")
    private String createdBy;

    private String comment;
}

Теперь вы можете:

  • Сортировать файлы по ordering
  • Отслеживать, кто и когда добавил файл
  • Добавлять комментарии к связям
  • Фильтровать по дате добавления

И всё это без изменения схемы данных (поля уже есть или легко добавляются).

4. Явность и понятность кода

Код отражает реальную структуру базы данных:

// Явно видно, что работаем с промежуточной таблицей
FROM ProductModelFile pmf
WHERE pmf.id.productModelId IN :modelIds

// Явно видно, что есть дополнительные поля
ORDER BY pmf.ordering, pmf.file.id

Новый разработчик сразу поймёт структуру данных, не изучая SQL схему.

5. Переносимость между БД

JPQL работает с любой БД, поддерживаемой Hibernate:

  • ✅ PostgreSQL
  • ✅ MySQL/MariaDB
  • ✅ Oracle
  • ✅ SQL Server
  • ✅ H2 (для тестов)

Native query пришлось бы переписывать под каждую БД.

6. Тестируемость

Легко писать unit-тесты:

@Test
void shouldFetchFilesByModelIds() {
    // Создаём тестовые данные
    ProductModelFile pmf = new ProductModelFile();
    pmf.setId(new ProductModelFileId(1L, 100L));

    // Проверяем логику
    assertThat(pmf.getId().getProductModelId()).isEqualTo(1L);
}

С Object[] из native query тестирование сложнее.

📚 Это рекомендованный подход в документации JPA

Согласно спецификации JPA и best practices:

«If the join table has additional columns beyond the foreign keys, or if you need to query the join table directly, you should model the join table as an entity.»

Даже если у вас пока нет дополнительных колонок, но вам нужен прямой доступ к промежуточной таблице для оптимизации — создавайте Entity.

🎯 Когда использовать @ManyToMany, а когда отдельную Entity?

Используйте @ManyToMany когда:

  • ✅ Промежуточная таблица содержит только два внешних ключа
  • ✅ Вы всегда работаете через основные Entity
  • ✅ Производительность не критична
  • ✅ Не планируется добавлять метаданные о связи

Создавайте отдельную Entity когда:

  • ✅ Нужна оптимизация запросов (начинать с промежуточной таблицы)
  • ✅ Есть или планируются дополнительные поля (ordering, timestamps, comments)
  • ✅ Нужна сортировка элементов связи
  • ✅ Нужны метаданные о связи (кто создал, когда, почему)
  • ✅ Большие объёмы данных и критична производительность

💡 Паттерн проектирования

Это пример паттерна «Association Class» из UML:

ProductModel ←→ ProductModelFile ←→ File
                       ↓
                   ordering
                   createdAt
                   createdBy

Связь сама становится полноценным объектом со своими свойствами и поведением.

Бонус: применяем для других связей

Этот же подход можно применить для других @ManyToMany связей. Например, для атрибутов:

// Для product_model_attributes
@Query("SELECT new ru.knasys.kross.dao.dto.AttributeWithModelId(" +
       "pma.productModel.id, pma.attributeName.id, pma.attribute.id) " +
       "FROM ProductModelAttribute pma " +
       "JOIN pma.attributeName an " +
       "WHERE pma.productModel.id IN :modelIds " +
       "AND (:#{#slugs == null || #slugs.isEmpty()} = true OR an.slug IN :slugs) " +
       "AND (:withDisabled = true OR an.enabled = true) " +
       "AND (:withNonPublic = true OR an.isPublic = true)")
Page<AttributeWithModelId> getAttributesByModelIdIn(
    @Param("modelIds") Collection<Long> modelIds,
    @Param("slugs") Collection<String> slugs,
    @Param("withDisabled") boolean withDisabled,
    @Param("withNonPublic") boolean withNonPublic,
    Pageable pageable);

Теперь запрос выглядит так:

SELECT 
    pma1_0.product_model_id,
    an1_0.id,
    pma1_0.attribute_id 
FROM product_model_attributes pma1_0
    JOIN attribute_names an1_0 ON an1_0.id = pma1_0.attribute_name_id
WHERE pma1_0.product_model_id IN (?, ?) 
    AND (? = true OR 1 = 0) 
    AND (? = true OR an1_0.enabled = true) 
    AND (? = true OR an1_0.public = true) 
OFFSET ? ROWS FETCH FIRST ? ROWS ONLY

Выводы

  1. Не все @ManyToMany связи нужно моделировать через простую аннотацию — иногда лучше создать отдельную Entity для промежуточной таблицы
  2. Всегда проверяйте генерируемый SQL — Hibernate не всегда создаёт оптимальные запросы
  3. Entity для промежуточных таблиц даёт больше контроля — можно добавлять дополнительные поля (например, ordering, created_date)
  4. Избегайте native queries где возможно — type-safe JPQL проще поддерживать и рефакторить
  5. Hibernate не «волшебник» — он следует принципам ORM. Когда структура запросов расходится с логикой @ManyToMany, правильное решение — явно смоделировать промежуточную связь
  6. Создание Entity для join-таблицы — это не костыль — это рекомендованный паттерн из спецификации JPA для случаев, когда нужен прямой доступ к промежуточной таблице

Практические рекомендации

Миграция с @ManyToMany на отдельную Entity

Если у вас уже есть работающий код с @ManyToMany, миграция проста:

Шаг 1: Создайте новую Entity (ProductModelFile)

Шаг 2: Оставьте старое поле в ProductModel (для обратной совместимости):

@ManyToMany
@JoinTable(name = "product_model_files", ...)
private List<File> files = new ArrayList<>();

Шаг 3: Создайте новые оптимизированные запросы через ProductModelFile

Шаг 4: Постепенно переводите код на новые запросы

Шаг 5: Когда всё протестировано, удалите старое поле files из ProductModel (опционально)

Производительность: реальные цифры

В нашем проекте оптимизация дала:

  • ⚡ 30-40% ускорение запросов при работе с 1000+ моделями
  • 📉 Снижение нагрузки на БД — один JOIN вместо двух
  • 💾 Меньше памяти — не загружаем ненужные данные из product_model
  • 🎯 Меньше индексов — БД не нужен индекс на product_model.id для этого запроса

Полезные ссылки

(Просмотрено 1 раз, 1 раз за сегодня)

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *