JPQL获取联接获取查询耗时过长

Question

我的rest应用程序使用JPQL查询。spring boot 2.5和mysql 5.7

我有一个表有4个onetomany，我需要一次性获取findAll查询的所有关系。

为了实现这一点，我使用了带有joinfetch的JPQL查询。它工作得很好，但是join fetch执行时间太长，如果没有所有日志，它需要2秒以上的时间，而对于日志，它则需要10秒，这并不好，因为如果我删除fetch并让hibernate激发n+1个查询，它会在更短的时间内获取所有记录。我也必须使用分页。

正在寻找JPQL查询本身的适当解决方案。

以下是配置和设置：

jpa:
open-in-view: false
properties:
hibernate.jdbc.time_zone: UTC
#      hibernate.id.new_generator_mappings: true
hibernate.connection.provider_disables_autocommit: true #https://vladmihalcea.com/why-you-should-always-use-hibernate-connection-provider_disables_autocommit-for-resource-local-jpa-transactions/
hibernate.cache.use_second_level_cache: true
hibernate.cache.region.factory_class: org.hibernate.cache.jcache.internal.JCacheRegionFactory
hibernate.javax.cache.provider: org.ehcache.jsr107.EhcacheCachingProvider
hibernate.cache.use_query_cache: true
hibernate.javax.cache.missing_cache_strategy: create
hibernate.connection.autocommit: true
# modify batch size as necessary
hibernate.jdbc.batch_size: 20
hibernate.default_batch_fetch_size: 20
hibernate.order_inserts: true
hibernate.order_updates: true
hibernate.batch_versioned_data: true
hibernate.query.fail_on_pagination_over_collection_fetch: false
hibernate.query.in_clause_parameter_padding: true
hibernate.dialect: org.hibernate.dialect.MySQL5InnoDBDialect
javax.persistent.sharedCache.mode: ENABLE_SELECTIVE

实体：

package com.pitstop.catalogue.domain;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.pitstop.catalogue.util.EligibleOffer;
import org.hibernate.annotations.BatchSize;
import org.hibernate.annotations.CacheConcurrencyStrategy;
import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import java.io.Serializable;
import java.time.Instant;
import java.util.*;
/**
* A Product.
*/
@Entity
@Table(name = "product")
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Product implements Serializable {

private static final long serialVersionUID = 1L;

public static final String PRODUCT_NAME = "name";
public static final String PRODUCT_UNIT_PRICE = "unit_price";

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@NotNull
@Size(max = 250)
@Column(name = PRODUCT_NAME, length = 50, nullable = false)
private String name;

@Size(max = 1000)
@Column(name = "description", length = 200)
private String description;

private Long brandId;

@Size(max = 500)
@Column(name = "product_icon_url", length = 500)
private String productIconUrl;

@Column(name = PRODUCT_UNIT_PRICE)
private Double unitPrice;

@Column(name = "gst_rate")
private Double gstRate;

@Size(max = 45)
@Column(name = "part_no", length = 45)
private String partNo;

@Size(max = 45)
@Column(name = "serial_no", length = 45)
private String serialNo;

@Size(max = 45)
@Column(name = "unit_type", length = 45)
private String unitType;

@Column(name = "line_item_id")
private Long lineItemId;

private Long categoryId;

@OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JsonIgnoreProperties(value = {"product"}, allowSetters = true)
@BatchSize(size = 20)
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
private Set<Offer> offers = new HashSet<>();

@OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JsonIgnoreProperties(value = {"product"}, allowSetters = true)
@BatchSize(size = 20)
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
private Set<ProductAttributes> productAttributes = new HashSet<>();

@OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JsonIgnoreProperties(value = {"product", "productModel"}, allowSetters = true)
@BatchSize(size = 20)
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
private Set<ProductModelMapping> productModelMappings = new HashSet<>();

@OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JsonIgnoreProperties(value = {"product"}, allowSetters = true)
@BatchSize(size = 20)
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
private Set<ProductSeller> productSellers = new HashSet<>();

@Size(max = 45)
@Column(name = "tag", length = 50)
private String tag;

@Column(name = "created")
private Instant created;

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

private Integer live;

public Integer getLive() {
return live;
}

public void setLive(Integer live) {
this.live = live;
}

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getDescription() {
return description;
}

public void setDescription(String description) {
this.description = description;
}

public String getProductIconUrl() {
return productIconUrl;
}

public void setProductIconUrl(String productIconUrl) {
this.productIconUrl = productIconUrl;
}

public Double getUnitPrice() {
return unitPrice;
}

public void setUnitPrice(Double unitPrice) {
this.unitPrice = unitPrice;
}

public Double getGstRate() {
return gstRate;
}

public void setGstRate(Double gstRate) {
this.gstRate = gstRate;
}

public String getPartNo() {
return partNo;
}

public void setPartNo(String partNo) {
this.partNo = partNo;
}

public String getSerialNo() {
return serialNo;
}

public void setSerialNo(String serialNo) {
this.serialNo = serialNo;
}

public String getUnitType() {
return unitType;
}

public void setUnitType(String unitType) {
this.unitType = unitType;
}

public Set<Offer> getOffers() {
return offers;
}

public void setOffers(Set<Offer> offers) {
this.offers = offers;
}

public Set<ProductAttributes> getProductAttributes() {
return productAttributes;
}

public void setProductAttributes(Set<ProductAttributes> productAttributes) {
this.productAttributes = productAttributes;
}

public Set<ProductModelMapping> getProductModelMappings() {
return productModelMappings;
}

public void setProductModelMappings(Set<ProductModelMapping> productModelMappings) {
this.productModelMappings = productModelMappings;
}

public Set<ProductSeller> getProductSellers() {
return productSellers;
}

public void setProductSellers(Set<ProductSeller> productSellers) {
this.productSellers = productSellers;
}

public Long getLineItemId() {
return lineItemId;
}

public void setLineItemId(Long lineItemId) {
this.lineItemId = lineItemId;
}

@Transient
public double getSellingPrice() {
// As discussed with Rohit, iterate over all the valid offers (by date)
// and then apply which has the highest discount value. This is for Pitstop offers only.
// Sellers offers will be created by sellers.
if (this.offers.size() > 0) {
Double sellingPriceFromOffers = EligibleOffer.apply(this.offers, this.unitPrice);
return sellingPriceFromOffers;
}
return this.unitPrice;
}

@Transient
public double getProductUnitPrice() {
return this.unitPrice;
}

@Transient
public double getProductGstRate() {
if (callSellers() != null && callSellers().size() > 0) {
ProductSeller productSeller = callSellers().get(0);
return productSeller.getGstRate();
}
return gstRate;
}

@Transient
public List<ProductSeller> callSellers() {
//sorting products by seller price in ascending order
List<ProductSeller> sellers = new ArrayList<>();
sellers.addAll(productSellers);
Collections.sort(sellers, (Comparator.comparing(ProductSeller::getUnitPrice)));
return sellers;
}

public String getTag() {
return tag;
}

public void setTag(String tag) {
this.tag = tag;
}

public Instant getCreated() {
return created;
}

public void setCreated(Instant created) {
this.created = created;
}

public String getCreatedBy() {
return createdBy;
}

public void setCreatedBy(String createdBy) {
this.createdBy = createdBy;
}

public Long getBrandId() {
return brandId;
}

public void setBrandId(Long brandId) {
this.brandId = brandId;
}

public Long getCategoryId() {
return categoryId;
}

public void setCategoryId(Long categoryId) {
this.categoryId = categoryId;
}

@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Product product = (Product) o;
return id.equals(product.id) && brandId.equals(product.brandId) && categoryId.equals(product.categoryId);
}

@Override
public int hashCode() {
return Objects.hash(id, brandId, categoryId);
}

@Override
public String toString() {
return "Product{" +
"id=" + id +
", brandId=" + brandId +
", categoryId=" + categoryId +
'}';
}
}

存储库：

@Query(value = "select p " +
"from Product p " +
"left join fetch p.productAttributes " +
"left join fetch p.productModelMappings " +
"left join fetch p.productSellers " +
"left join fetch p.offers where p.live = 1",
countQuery = "select count(p.id) from Product p where p.live = 1")
Page<Product> findAll(Pageable pageable);

以下是正在激发的查询：

select product0_.id as id1_5_0_, productatt1_.id as id1_6_1_, productmod2_.id as id1_9_2_, productsel3_.id as id1_10_3_, offers4_.id as id1_4_4_, product0_.brand_id as brand_id2_5_0_, product0_.category_id as category3_5_0_, product0_.created as created4_5_0_, product0_.created_by as created_5_5_0_, product0_.description as descript6_5_0_, product0_.gst_rate as gst_rate7_5_0_, product0_.line_item_id as line_ite8_5_0_, product0_.live as live9_5_0_, product0_.name as name10_5_0_, product0_.part_no as part_no11_5_0_, product0_.product_icon_url as product12_5_0_, product0_.serial_no as serial_13_5_0_, product0_.tag as tag14_5_0_, product0_.unit_price as unit_pr15_5_0_, product0_.unit_type as unit_ty16_5_0_, productatt1_.live as live2_6_1_, productatt1_.name as name3_6_1_, productatt1_.product_id as product_5_6_1_, productatt1_.value as value4_6_1_, productatt1_.product_id as product_5_6_0__, productatt1_.id as id1_6_0__, productmod2_.fuel_type as fuel_typ2_9_2_, productmod2_.live as live3_9_2_, productmod2_.name as name4_9_2_, productmod2_.product_id as product_5_9_2_, productmod2_.product_model_id as product_6_9_2_, productmod2_.product_id as product_5_9_1__, productmod2_.id as id1_9_1__, productsel3_.created as created2_10_3_, productsel3_.created_by as created_3_10_3_, productsel3_.description as descript4_10_3_, productsel3_.gst_rate as gst_rate5_10_3_, productsel3_.last_modified as last_mod6_10_3_, productsel3_.live as live7_10_3_, productsel3_.modified_by as modified8_10_3_, productsel3_.name as name9_10_3_, productsel3_.product_id as product13_10_3_, productsel3_.seller_id as seller_10_10_3_, productsel3_.seller_name as seller_11_10_3_, productsel3_.price as price12_10_3_, productsel3_.product_id as product13_10_2__, productsel3_.id as id1_10_2__, offers4_.brand_id as brand_id2_4_4_, offers4_.category_id as category3_4_4_, offers4_.created as created4_4_4_, offers4_.discount as discount5_4_4_, offers4_.end_date as end_date6_4_4_, offers4_.live as live7_4_4_, offers4_.name as name8_4_4_, offers4_.offer_type as offer_ty9_4_4_, offers4_.product_id as product11_4_4_, offers4_.start_date as start_d10_4_4_, offers4_.product_id as product11_4_3__, offers4_.id as id1_4_3__ from product product0_ left outer join product_attributes productatt1_ on product0_.id=productatt1_.product_id left outer join product_model_mapping productmod2_ on product0_.id=productmod2_.product_id left outer join product_seller productsel3_ on product0_.id=productsel3_.product_id left outer join offer offers4_ on product0_.id=offers4_.product_id where product0_.live=1
select count(product0_.id) as col_0_0_ from product product0_ where product0_.live=1
select productmod0_.id as id1_8_0_, productmod0_.live as live2_8_0_, productmod0_.name as name3_8_0_, productmod0_.product_make_id as product_4_8_0_ from product_model productmod0_ where productmod0_.id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
select productmod0_.id as id1_8_0_, productmod0_.live as live2_8_0_, productmod0_.name as name3_8_0_, productmod0_.product_make_id as product_4_8_0_ from product_model productmod0_ where productmod0_.id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Answer 1

您可以尝试实体图来提高性能，同时避免n+1问题。

1. 在实体类上创建实体图

   @NamedEntityGraph(name = Product.GRAPH_ALL_ATTRIBUTES),
   attributeNodes = {
   @NamedAttributeNode(value = "productAttributes"),
   @NamedAttributeNode(value = "productModelMappings"),
   @NamedAttributeNode(value = "productSellers"),
   @NamedAttributeNode(value = "offers")
   })
   @Entity
   @Table(name = "product")
   @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
   public class Product implements Serializable {  
   private static final long serialVersionUID = 1L;
   protected static final String GRAPH_ALL_ATTRIBUTES = "product.graph.all";
   public static final String PRODUCT_NAME = "name";
   public static final String PRODUCT_UNIT_PRICE = "unit_price";
   ...
   }
   

2. 在存储库界面中创建一个@EntityGraph注释查询方法。

   @EntityGraph(Product.GRAPH_ALL_ATTRIBUTES)
   Page<Product> findByLive(Integer live, Pageable pageable);