时间戳

首页 > 解决方案 > JPA findAll() 性能缓慢 - 触发大量子查询

java - JPA findAll() 性能缓慢 - 触发大量子查询

问题描述

我看到很多查询被触发findAll()来自JpaRepository. 我知道如果我们有如此大量的数据，则必须在数据库中进行连接查询。我正在尝试使用findAll方法检索所有 Post 对象。

实体

@Entity
public class Post {
  @Id
  private String postId;
  private String postName;
  @OneToMany(mappedBy = "Post", cascade = CascadeType.ALL)
  private Collection<PostTag> postTags = new HashSet<PostTag>();
}

@Entity
public class Tag {
  @Id
  private String tagId;
  private String tagName;
  @OneToMany(mappedBy = "tag", cascade = CascadeType.ALL)
  @JsonIgnore
  private Collection<PostTag> postTags = new HashSet<PostTag>();
}

@Entity
public class PostTag {
  @EmbeddedId
  private PostTagId postTagId = new PostTagId();
  
  @ManyToOne
  @MapsId("postId")
  @JoinColumn(name = "post_Id")
  @JsonIgnore
  private Post post;
  
  @ManyToOne
  @MapsId("tagId")
  @JoinColumn(name = "tag_Id")
  private Tag tag;
  
  @OneToMany(mappedBy = "posttag", cascade = CascadeType.ALL)
  @JsonIgnore
  private Set<Items> items= new HashSet<Items>();
}

@Embeddable
public class PostTagId implements Serializable {
  private String postId;
  private String tagId;
  //equals & hashcode ommited
}

public class Items{
  @Id
  private String itemId;
  private String itemName;

  @ManyToOne
  @JoinColumns({@JoinColumn(name = "post_id"), @JoinColumn(name = "tag_id")})
  @JsonBackReference
  @JsonIgnore
  private PostTag postTag;

  @OneToMany(mappedBy = "items", cascade = CascadeType.ALL)
  @JsonIgnore
  private Set<SubItems> subItems= new HashSet<SubItems>();

}

public class SubItems{
  @Id
  private String subItemId;
  private String subItemName;

  @ManyToOne
  @JoinColumns({@JoinColumn(name = "itemId")})
  @JsonBackReference
  @JsonIgnore
  private Items items;

}

Hibernate 查询日志： 这是从 JPA 层触发的第一个查询，因为我正在使用条件搜索一些基于标签的过滤器。

select Post0_.postId , Post0_.postName  from Post Post0_ inner join 
PostTag posttag1_ on Post0_.postId=posttag1_.post_id inner join 
Tag Tag2_ on posttag1_.tag_id=Tag2_.tagId left outer join 
PostTag posttag3_ on Post0_.postId=posttag3_.post_id 
where (Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ?) 
and (posttag1_.somefield between ? and ?) order by posttag3_.somefield desc, posttag3_.pubDate desc offset 0 rows fetch next ? rows only

对于已触发休眠查询的每个帖子记录，

Hibernate: select count(Post0_.postId) as col_0_0_ from Post Post0_ inner join PostTag posttag1_ on Post0_.postId=posttag1_.post_id inner join Tag Tag2_ on posttag1_.tag_id=Tag2_.tagId where (Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ? or Tag2_.tagName like ?) and (posttag1_.somefield between ? and ?)
Hibernate: select PostTag0_.post_id as post_id1_5_0_, PostTag0_.tag_id as tag_i2_5_0_, PostTag0_.post_id as post_id1_5_1_, PostTag0_.tag_id as tag_i2_5_1_, PostTag0_.content as content3_5_1_, Tag1_.tagId as tagid1_2_2_, Tag1_.tagName as tagna2_2_2_ from PostTag PostTag0_ inner join Tag Tag1_ on PostTag0_.tag_id=Tag1_.tagId where PostTag0_.post_id=?
Hibernate: select items0_.post_id as post_id3_1_0_, items0_.tag_id as tag_i4_1_0_, items0_.itemId as itemi1_1_0_, items0_.itemId as itemi1_1_1_, items0_.itemName as itemn2_1_1_, items0_.post_id as post_id3_1_1_, items0_.tag_id as tag_i4_1_1_ from itemso items0_ where items0_.post_id=? and items0_.tag_id=?
Hibernate: selectsubitem0_.itemId as itemi3_3_0_,subitem0_.subitemId as subitemi1_3_0_,subitem0_.subitemId as subitemi1_3_1_,subitem0_.itemId as itemi3_3_1_,subitem0_.subitemName as subitemn2_3_1_ fromsubitemosubitem0_ wheresubitem0_.itemId=?

例如，如果我有 5 个帖子被标记在 2 个标签下，每个帖子标签都有项目和子项目，那么上述 4 个连续触发的查询取决于记录大小。

但是有什么方法可以findall()优化这个响应以从数据库中一次性获取？我知道第一个选择查询确实适用于数据库端，但在 java 层中，我必须映射到 DTO，所以这是不可避免的吗？

标签： javaspringhibernatejpa

解决方案

@OneToMany 获取类型默认为 EAGER，这会在您的示例中导致大量查询。为了减少休眠查询，您可以将@OneToMany 的 FetchType 更改为 LAZY。但这会导致另一个问题。此链接将向您展示使用 Hibernate 及其性能问题的 2 种方法。

参考链接：LazyInitializationException

推荐阅读