时间戳

首页 > 解决方案 > 性能休眠@Subselect vs 数据库视图

database - 性能休眠@Subselect vs 数据库视图

问题描述

我有一个基于 Java 8 Spring Boot 2.3.3 的应用程序（使用 hibernate 5.4.20），我有一个 Postgresql。我想最终了解使用或不使用数据库视图和或@Subselect 是否更好（对于性能）。

只是一个快速的概述：我有一个实体“Book”和 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 注释中使用“视图查询”和 @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 实体和第 3 点的新 BookRanking 不可变实体（类似于视图）的相同存储库查询（非本地）

标签： databasespring-bootperformancehibernateview

解决方案

通常，数据库视图只是扩展为查询计划的关系，通常在解析期间，因此使用视图与直接写出整个查询应该没有显着区别。使用视图可以让您更轻松地重用查询，但是当您想要更改某些内容时，您将不得不更改视图，并且可能还必须更改使用该视图的所有应用程序，因此可重用性方面可能会让您望而却步。

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

推荐阅读