Spring Data JPA利用@EntityGraph解决N+1的SQL查询问题： @OnetoOne @OneToMany
zhangzhenhuajack opened this issue · 2 comments
zhangzhenhuajack commented
出现N+1的SQL的场景，我们有如下四个实体，核心内容如下：
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.annotations.*;
import javax.persistence.*;
import javax.persistence.Entity;
import javax.persistence.Table;
import java.time.Instant;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
@Setter
@Getter
@EqualsAndHashCode(of = {"id"}, callSuper = true)
@Entity
@Table(name = "tpusers")
public class Tpuser {
private String name;
private String email;
private String uuid;
private Parent parent;
private Teacher teacher;
private List<ThirdPartyTpuser> thirdPartyTpusers;
private Long id;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Long getId() {
return this.id;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "tpuser")
public List<ThirdPartyTpuser> getThirdPartyTpusers() {
return thirdPartyTpusers;
}
@OneToOne(mappedBy = "tpuser")
@Fetch(FetchMode.JOIN)
public Parent getParent() {
return parent;
}
@OneToOne(mappedBy = "tpuser")
@Fetch(FetchMode.JOIN)
public Teacher getTeacher() {
return teacher;
}
}
@Getter
@Setter
@Entity
@Table(name = "parents")
@Where(clause = "deleted = false")
public class Parent extends AbstractDeletedAuditBase {
private String address;
private Long state;
private Tpuser tpuser;
private Long id;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Long getId() {
return this.id;
}
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "tpuser_id", referencedColumnName = "id")
public Tpuser getTpuser() {
return tpuser;
}
}
@Getter
@Setter
@EqualsAndHashCode(of = "tpuser_id")
@Entity
@Table(name = "teachers")
@Include(rootLevel = true, type = "Teachers")
@Where(clause = "deleted = false")
public class Teacher extends AbstractDeletedAuditBase {
private Long areaId;
private TeacherType type;
private Tpuser trouser;
private Long id;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Long getId() {
return this.id;
}
@Enumerated(EnumType.STRING)
public TeacherType getType() {
return type;
}
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "tpuser_id")
public Tpuser getTpuser() {
return tpuser;
}
}
@Getter
@Setter
@Entity
@Table(name = "third_party_tpusers")
public class ThirdPartyTpuser extends AbstractVersionAuditBase {
private String platform;
private String openid;
private String unionid;
private Tpuser tpuser;
private Long id;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Long getId() {
return this.id;
}
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "uid")
public Tpuser getTpuser() {
return tpuser;
}
}
也就说上面，上面四个四个实体之间的关系是Tpuser EAGER Parent AEGER teacher LAZY third_party_tpusers
那么我们发生的问题如下：
public interface TpuserRepository extends GenericUserRepository<Tpuser> {
// @EntityGraph(attributePaths = {"thirdPartyTpusers","teacher","parent"}, type = EntityGraph.EntityGraphType.LOAD) 这个注解注释掉之后就会产生N+1的sql问题
List<Tpuser> findAllByIdIn(Iterable<Long> ids);
}
当我们把@entitygraph注释掉之后，执行如下测试用例的时候就会发生N+1的sql问题
@Test
public void findByUuid() throws Exception {
List<Tpuser> tpusers = userRepository.findAllByIdIn(Lists.newArrayList(1L,2L));
tpusers.forEach(tpuser -> {
//我们利用 getId来模拟业务用到其它三个实体里面的值
System.out.println(tpuser.getThirdPartyTpusers().get(0).getId());
System.out.println(tpuser.getTeacher().getId());
System.out.println(tpuser.getParent().getId());
});
}
N+1的sql现象如下：
2021-09-17 16:42:03.356 DEBUG [-,cebfb5f6a6b6a1c9,cebfb5f6a6b6a1c9,true] 40539 --- [nio-9000-exec-1] org.hibernate.SQL : select tpuser0_.id as id1_24_, tpuser0_.created_at as created_2_24_, tpuser0_.updated_at as updated_3_24_, tpuser0_.lock_version as lock_ver4_24_, tpuser0_.auto_generate as auto_gen5_24_, tpuser0_.email as email6_24_, tpuser0_.gender as gender7_24_, tpuser0_.invitation_code_group as invitati8_24_, tpuser0_.invited_by_code as invited_9_24_, tpuser0_.mobile_phone as mobile_10_24_, tpuser0_.mobile_phone_validated as mobile_11_24_, tpuser0_.name as name12_24_, tpuser0_.password_hash as passwor13_24_, tpuser0_.password_updated_at as passwor14_24_, tpuser0_.state as state15_24_, tpuser0_.uuid as uuid16_24_ from tpusers tpuser0_ where tpuser0_.id in (? , ?)
2021-09-17 16:42:03.480 TRACE [-,cebfb5f6a6b6a1c9,cebfb5f6a6b6a1c9,true] 40539 --- [nio-9000-exec-1] o.h.type.descriptor.sql.BasicExtractor : extracted value ([uuid16_24_] : [VARCHAR]) - [81164fff-4184-47c3-84a5-d44e71400bd4]
2021-09-17 16:42:03.500 DEBUG [-,cebfb5f6a6b6a1c9,cebfb5f6a6b6a1c9,true] 40539 --- [nio-9000-exec-1] org.hibernate.SQL : select parent0_.id as id1_13_0_, parent0_.created_at as created_2_13_0_, parent0_.updated_at as updated_3_13_0_, parent0_.lock_version as lock_ver4_13_0_, parent0_.deleted as deleted5_13_0_, parent0_.deleted_at as deleted_6_13_0_, parent0_.address as address7_13_0_, parent0_.state as state8_13_0_, parent0_.tpuser_id as tpuser_i9_13_0_ from parents parent0_ where parent0_.tpuser_id=? and ( parent0_.deleted = 0)
2021-09-17 16:42:03.545 DEBUG [-,cebfb5f6a6b6a1c9,cebfb5f6a6b6a1c9,true] 40539 --- [nio-9000-exec-1] org.hibernate.SQL : select teacher0_.id as id1_15_0_, teacher0_.created_at as created_2_15_0_, teacher0_.updated_at as updated_3_15_0_, teacher0_.lock_version as lock_ver4_15_0_, teacher0_.deleted as deleted5_15_0_, teacher0_.deleted_at as deleted_6_15_0_, teacher0_.address as address7_15_0_, teacher0_.area_id as area_id8_15_0_, teacher0_.last_login_date as last_log9_15_0_, teacher0_.state as state10_15_0_, teacher0_.tpuser_id as tpuser_12_15_0_, teacher0_.type as type11_15_0_ from teachers teacher0_ where teacher0_.tpuser_id=? and ( teacher0_.deleted = 0)
2021-09-17 16:42:03.581 DEBUG [-,cebfb5f6a6b6a1c9,cebfb5f6a6b6a1c9,true] 40539 --- [nio-9000-exec-1] org.hibernate.SQL : select parent0_.id as id1_13_0_, parent0_.created_at as created_2_13_0_, parent0_.updated_at as updated_3_13_0_, parent0_.lock_version as lock_ver4_13_0_, parent0_.deleted as deleted5_13_0_, parent0_.deleted_at as deleted_6_13_0_, parent0_.address as address7_13_0_, parent0_.state as state8_13_0_, parent0_.tpuser_id as tpuser_i9_13_0_ from parents parent0_ where parent0_.tpuser_id=? and ( parent0_.deleted = 0)
2021-09-17 16:42:03.622 DEBUG [-,cebfb5f6a6b6a1c9,cebfb5f6a6b6a1c9,true] 40539 --- [nio-9000-exec-1] org.hibernate.SQL : select teacher0_.id as id1_15_0_, teacher0_.created_at as created_2_15_0_, teacher0_.updated_at as updated_3_15_0_, teacher0_.lock_version as lock_ver4_15_0_, teacher0_.deleted as deleted5_15_0_, teacher0_.deleted_at as deleted_6_15_0_, teacher0_.address as address7_15_0_, teacher0_.area_id as area_id8_15_0_, teacher0_.last_login_date as last_log9_15_0_, teacher0_.state as state10_15_0_, teacher0_.tpuser_id as tpuser_12_15_0_, teacher0_.type as type11_15_0_ from teachers teacher0_ where teacher0_.tpuser_id=? and ( teacher0_.deleted = 0)
2021-09-17 16:42:03.623 TRACE [-,cebfb5f6a6b6a1c9,cebfb5f6a6b6a1c9,true] 40539 --- [nio-9000-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BIGINT] - [8991696]
2021-09-17 16:42:03.768 DEBUG [-,cebfb5f6a6b6a1c9,cebfb5f6a6b6a1c9,true] 40539 --- [nio-9000-exec-1] org.hibernate.SQL : select thirdparty0_.uid as uid15_18_1_, thirdparty0_.id as id1_18_1_, thirdparty0_.id as id1_18_0_, thirdparty0_.created_at as created_2_18_0_, thirdparty0_.updated_at as updated_3_18_0_, thirdparty0_.lock_version as lock_ver4_18_0_, thirdparty0_.avatar_url as avatar_u5_18_0_, thirdparty0_.city as city6_18_0_, thirdparty0_.country as country7_18_0_, thirdparty0_.nickname as nickname8_18_0_, thirdparty0_.openid as openid9_18_0_, thirdparty0_.platform as platfor10_18_0_, thirdparty0_.province as provinc11_18_0_, thirdparty0_.sex as sex12_18_0_, thirdparty0_.uid as uid15_18_0_, thirdparty0_.unionid as unionid13_18_0_, thirdparty0_.uuid as uuid14_18_0_ from third_party_tpusers thirdparty0_ where thirdparty0_.uid in (?, ?)
问题总结：
也就是当我们查询2条tpuser的时候就会产生6条SQL，而最后的third_party_tpusers 只生成了一条sql是因为我们配置了spring.jpa.properties.hibernate.default_batch_fetch_size=50 但是fetch_size解决不了 @OnetoOne的N+1的SQL问题。
zhangzhenhuajack commented
解决办法是把 // @EntityGraph(attributePaths = {"thirdPartyTpusers","teacher","parent"}, type = EntityGraph.EntityGraphType.LOAD) 这个注解注释掉之后就会产生N+1的sql问题注释去掉变成如下：
public interface TpuserRepository extends GenericUserRepository<Tpuser> {
@EntityGraph(attributePaths = {"thirdPartyTpusers","teacher","parent"}, type = EntityGraph.EntityGraphType.LOAD)
List<Tpuser> findAllByIdIn(Iterable<Long> ids);
}
我们再执行上面的测试用例，而打印的SQL就会变成一个SQL，如下：
2021-09-17 16:46:02.102 DEBUG [-,,,] 40625 --- [ main] org.hibernate.SQL :
select tpuser0_.id as id1_24_0_,
parent1_.id as id1_13_1_,
thirdparty2_.id as id1_18_2_,
teacher3_.id as id1_15_3_,
tpuser0_.created_at as created_2_24_0_,
tpuser0_.updated_at as updated_3_24_0_,
tpuser0_.lock_version as lock_ver4_24_0_,
tpuser0_.auto_generate as auto_gen5_24_0_,
tpuser0_.email as email6_24_0_,
tpuser0_.gender as gender7_24_0_,
tpuser0_.invitation_code_group as invitati8_24_0_,
tpuser0_.invited_by_code as invited_9_24_0_,
tpuser0_.mobile_phone as mobile_10_24_0_,
tpuser0_.mobile_phone_validated as mobile_11_24_0_,
tpuser0_.name as name12_24_0_,
tpuser0_.password_hash as passwor13_24_0_,
tpuser0_.password_updated_at as passwor14_24_0_,
tpuser0_.state as state15_24_0_,
tpuser0_.uuid as uuid16_24_0_,
parent1_.created_at as created_2_13_1_,
parent1_.updated_at as updated_3_13_1_,
parent1_.lock_version as lock_ver4_13_1_,
parent1_.deleted as deleted5_13_1_,
parent1_.deleted_at as deleted_6_13_1_,
parent1_.address as address7_13_1_,
parent1_.state as state8_13_1_,
parent1_.tpuser_id as tpuser_i9_13_1_,
thirdparty2_.created_at as created_2_18_2_,
thirdparty2_.updated_at as updated_3_18_2_,
thirdparty2_.lock_version as lock_ver4_18_2_,
thirdparty2_.avatar_url as avatar_u5_18_2_,
thirdparty2_.city as city6_18_2_,
thirdparty2_.country as country7_18_2_,
thirdparty2_.nickname as nickname8_18_2_,
thirdparty2_.openid as openid9_18_2_,
thirdparty2_.platform as platfor10_18_2_,
thirdparty2_.province as provinc11_18_2_,
thirdparty2_.sex as sex12_18_2_,
thirdparty2_.uid as uid15_18_2_,
thirdparty2_.unionid as unionid13_18_2_,
thirdparty2_.uuid as uuid14_18_2_,
thirdparty2_.uid as uid15_18_0__,
thirdparty2_.id as id1_18_0__,
teacher3_.created_at as created_2_15_3_,
teacher3_.updated_at as updated_3_15_3_,
teacher3_.lock_version as lock_ver4_15_3_,
teacher3_.deleted as deleted5_15_3_,
teacher3_.deleted_at as deleted_6_15_3_,
teacher3_.address as address7_15_3_,
teacher3_.area_id as area_id8_15_3_,
teacher3_.last_login_date as last_log9_15_3_,
teacher3_.state as state10_15_3_,
teacher3_.tpuser_id as tpuser_12_15_3_,
teacher3_.type as type11_15_3_
from tpusers tpuser0_
left outer join parents parent1_ on tpuser0_.id = parent1_.tpuser_id and (parent1_.deleted = 0)
left outer join third_party_tpusers thirdparty2_ on tpuser0_.id = thirdparty2_.uid
left outer join teachers teacher3_ on tpuser0_.id = teacher3_.tpuser_id and (teacher3_.deleted = 0)
where tpuser0_.id in (?, ?)
从而解决了问题。
zhangzhenhuajack commented
不过建议对JPA不熟悉的，建议实体上都不要用关联关系。用mysql的思路解决问题即可；