Блог

У меня есть приложение на основе Java 8 Spring Boot 2.3.3 (используйте hibernate 5.4.20), у меня есть Postgresql. Я хотел бы, наконец, понять, лучше ли (для производительности) использовать или нет представление базы данных и или @Subselect.

Просто краткий обзор: у меня есть объект «Книга» и 3 объекта «BookRank» (пользователи дают книгам от 1 до 10 звезд), «BookComment» (комментарии пользователей к книге), «BookLike» (пользователи ставят лайки книгам), каждый из которых имеет отношение к книге (ManyToOne)поэтому в результате моего запроса я хочу упорядочить книги таким образом:

 order by avg(book_rank) desc, sum(book_rank) desc, count(book_comment) desc, count(book_like) desc
  

Конечно, есть много книг, много оценок, комментариев и лайков … 🙂

Я нашел 4 способа сделать то же самое, начиная со сложного запроса с 3 подвыборками внутри (для меня это было лучшее решение, но если у кого-то есть лучший способ сделать это, пожалуйста, скажите мне).

 select *
from(
    select bbb.id as book_id, min(bbb.b_rank_average) as b_rank_average, min(bbb.b_rank_sum) as b_rank_sum, min(bbb.b_comment_count) as b_comment_count, count(b_l.id) as b_like_count
    from(
        select rr.id, min(bb.b_rank_average) as b_rank_average, min(bb.b_rank_sum) as b_rank_sum, count(b_c.id) as b_comment_count
        from(           
            select b.id,  avg(b_r.rank) as b_rank_average, sum(b_r.rank) as b_rank_sum
            from book as b
            left join book_rank as b_r on (b.id = b_r.book_id and b_r.deleted = false)
            group by b.id) as bb
            
        left join book_comment as b_c on (bb.id = b_c.book_id and b_c.deleted = false)
        group by bb.id) as bbb
                
    left join book_like as b_l on (bbb.id = b_l.book_id and b_l.deleted = false)
    group by bbb.id
    ) as bbbb
    
left join book on bbbb.book_id = book.id
where book.deleted = false
order by b_rank_average desc nulls last, b_rank_sum desc nulls last, b_comment_count desc, b_like_count desc, book_id desc;
  

Я хотел бы знать, какой из них является лучшим…

1) чистый собственный запрос в репозитории — нет представления — нет неизменяемой сущности

В моем репозитории у меня просто есть метод с собственным запросом, написанным выше

2) с представлением базы данных и собственным запросом в репозитории

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

 create or replace view book_ranking as

    select bbb.id as book_id, min(bbb.b_rank_average) as b_rank_average, min(bbb.b_rank_sum) as b_rank_sum, min(bbb.b_comment_count) as b_comment_count, count(b_l.id) as b_like_count
    from(
        select rr.id, min(bb.b_rank_average) as b_rank_average, min(bb.b_rank_sum) as b_rank_sum, count(b_c.id) as b_comment_count
        from(           
            select b.id,  avg(b_r.rank) as b_rank_average, sum(b_r.rank) as b_rank_sum
            from book as b
            left join book_rank as b_r on (b.id = b_r.book_id and b_r.deleted = false)
            group by b.id) as bb
            
        left join book_comment as b_c on (bb.id = b_c.book_id and b_c.deleted = false)
        group by bb.id) as bbb
                
    left join book_like as b_l on (bbb.id = b_l.book_id and b_l.deleted = false)
    group by bbb.id
  

и изменение собственного запроса в методе репозитория только для использования представления:

 select * 
from book_ranking as the_view
left join book on the_view.book_id = book.id 
where book.deleted = false
order by b_rank_average desc nulls last, b_rank_sum desc nulls last, b_comment_count desc, b_like_count desc, book_id desc;
  

3) с представлением базы данных и неизменяемым объектом, который выбирает представление и запрос jpql в репозитории

Использование объекта (неизменяемого) для инкапсуляции представления выше

 @Entity
@Subselect("select * from book_ranking")
public class BookRanking implements Serializable {
    ....
}
  

и изменение запроса репозитория (не собственного), который объединяет объект Book и новый неизменяемый объект BookRanking (то есть представление)

 @Query("select b from BookRanking as b_r 
        join Book b on b_r.bookId = b.id 
        where b.deleted = false
        order by b_r.b_rank_average desc nulls last, b_r.b_rank_sum desc nulls last, b_r.b_comment_count desc, b_r.b_like_count desc, b_r.bookId desc)
  

4) Нет представления, но неизменяемый объект с запросом в @subselect и запросом jpql в репозитории

В базе данных нет представления, но у неизменяемого объекта BookRanking есть запрос в аннотации @Subselect с «view query» и аннотацией @Synchronize, подобной этой:

 @Entity
@Subselect("select bbb.id as book_id, min(bbb.b_rank_average) as b_rank_average, min(bbb.b_rank_sum) as b_rank_sum, min(bbb.b_comment_count) as b_comment_count, count(b_l.id) as b_like_count
    from(
        select rr.id, min(bb.b_rank_average) as b_rank_average, min(bb.b_rank_sum) as b_rank_sum, count(b_c.id) as b_comment_count
        from(           
            select b.id,  avg(b_r.rank) as b_rank_average, sum(b_r.rank) as b_rank_sum
            from book as b
            left join book_rank as b_r on (b.id = b_r.book_id and b_r.deleted = false)
            group by b.id) as bb
            
        left join book_comment as b_c on (bb.id = b_c.book_id and b_c.deleted = false)
        group by bb.id) as bbb
                
    left join book_like as b_l on (bbb.id = b_l.book_id and b_l.deleted = false)
    group by bbb.id )

@Synchronize({ "book", "book_rank", "book_comment", "book_like" })

public class BookRanking implements Serializable {
    ....
}

  

и используя тот же запрос репозитория (не собственный), который объединяет объект Book и новый неизменяемый объект BookRanking (который похож на представление) пункта 3

В общем, представление базы данных — это просто отношение, которое расширяется в плане запроса, обычно во время синтаксического анализа, поэтому не должно быть заметной разницы при использовании представления или записи всего запроса напрямую. Использование представления позволяет вам легче повторно использовать запрос, но если вы хотите что-то изменить, вам придется изменить представление и, возможно, также все приложения, использующие представление, поэтому аспект повторного использования может вас укусить.

Обычно я не рекомендую просмотры, потому что я видел, как люди добавляли много ненужных объединений в представления с единственной целью «упростить» для других. Проблема с неиспользуемыми объединениями заключается в том, что базы данных обычно не могут их устранить. TLDR, я рекомендую записывать запросы непосредственно в коде, потому что вы можете опустить ненужные соединения и, таким образом, повысить производительность.

Вы могли бы использовать следующий более простой запрос:

 select 
    b.id, 
    avg(b_r.rank) as b_rank_average, 
    sum(b_r.rank) as b_rank_sum,
    (select count(*) from book_comment as b_c where b.id = b_c.book_id and b_c.deleted = false) as b_comment_count
    (select count(*) from book_like as b_l where b.id = b_l.book_id and b_l.deleted = false) as b_like_count
from book as b
left join book_rank as b_r      on (b.id = b_r.book_id and b_r.deleted = false)
where b.deleted = false
group by b.id
order by b_rank_average desc nulls last, b_rank_sum desc nulls last, b_comment_count desc, b_like_count desc, b.id desc
  

который также может быть смоделирован с помощью запроса JPQL / HQL. Это выглядело бы очень похоже:

 select 
    b.id, 
    avg(r.rank) as b_rank_average, 
    sum(r.rank) as b_rank_sum,
    (select count(*) from b.comments c where c.deleted = false) as b_comment_count
    (select count(*) from b.booksLike l where l.deleted = false) as b_like_count
from book as b
left join b.ranks as r on r.deleted = false
where b.deleted = false
group by b.id
order by b_rank_average desc nulls last, b_rank_sum desc nulls last, b_comment_count desc, b_like_count desc, b.id desc