性能hibernate @子选择与数据库视图

Question

我有一个基于Java8 Spring Boot 2.3.3的应用程序（使用Hibernate5.4.20），我有一个Postgreql。我想最终了解使用数据库视图和@Sub选择是否更好（对于性能）。

简单概述一下：我有一个实体“Book”和3个实体“BookRank”（用户给书打1到10颗星）、“BookComment”（用户对书的评论）、“BookLike”（用户把书的评论放在一起），每个实体都有Book关系（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 {
    ....
}

以及修改连接图书实体和新图书排名不变实体(即视图)的存储库查询(非本机)

@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注释中使用“视图查询”和如下所示的@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 {
    ....
}

并使用相同的存储库查询（不是本机）加入第3点的Book实体和新的BooRanking不可变实体（类似于视图）

Answer 1

一般来说，数据库视图只是一个关系，通常在解析期间被扩展到查询计划中，因此在使用视图和直接写出整个查询时应该没有明显的区别。使用视图可以使您更容易地重用查询，但当您想要更改某些内容时，您必须更改视图，可能还必须更改所有使用视图的应用程序，因此重用性方面会让您反感。

我通常不推荐视图，因为我看到人们在视图中加入了很多不必要的连接，唯一的目的就是“让别人更容易”。未使用的联接的问题是，数据库通常无法消除它们。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