search

zhangzhenhuajack / spring-data-jpa-guide

spring-data-jpa-guide，Spring Data JPA实战，SpringDataJpa详解
http://www.jackzhang.cn/spring-data-jpa-guide/
379 stars 164 forks source link

Spring Data JPA利用@EntityGraph解决N+1的SQL查询问题： @OnetoOne @OneToMany #36

Open zhangzhenhuajack opened 3 years ago

zhangzhenhuajack commented 3 years ago

出现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 ([uuid1624] : [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_130, parent0_.created_at as created_2_130, parent0_.updated_at as updated_3_130, parent0_.lock_version as lock_ver4_130, parent0_.deleted as deleted5_130, parent0_.deleted_at as deleted_6_130, parent0_.address as address7_130, parent0_.state as state8_130, parent0_.tpuser_id as tpuser_i9_130 from parents parent0 where parent0.tpuserid=? 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_150, teacher0_.created_at as created_2_150, teacher0_.updated_at as updated_3_150, teacher0_.lock_version as lock_ver4_150, teacher0_.deleted as deleted5_150, teacher0_.deleted_at as deleted_6_150, teacher0_.address as address7_150, teacher0_.area_id as area_id8_150, teacher0_.last_login_date as last_log9_150, teacher0_.state as state10_150, teacher0_.tpuser_id as tpuser_12_150, teacher0_.type as type11_150 from teachers teacher0 where teacher0.tpuserid=? 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_130, parent0_.created_at as created_2_130, parent0_.updated_at as updated_3_130, parent0_.lock_version as lock_ver4_130, parent0_.deleted as deleted5_130, parent0_.deleted_at as deleted_6_130, parent0_.address as address7_130, parent0_.state as state8_130, parent0_.tpuser_id as tpuser_i9_130 from parents parent0 where parent0.tpuserid=? 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_150, teacher0_.created_at as created_2_150, teacher0_.updated_at as updated_3_150, teacher0_.lock_version as lock_ver4_150, teacher0_.deleted as deleted5_150, teacher0_.deleted_at as deleted_6_150, teacher0_.address as address7_150, teacher0_.area_id as area_id8_150, teacher0_.last_login_date as last_log9_150, teacher0_.state as state10_150, teacher0_.tpuser_id as tpuser_12_150, teacher0_.type as type11_150 from teachers teacher0 where teacher0.tpuserid=? 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_181, thirdparty0_.id as id1_181, thirdparty0_.id as id1_180, thirdparty0_.created_at as created_2_180, thirdparty0_.updated_at as updated_3_180, thirdparty0_.lock_version as lock_ver4_180, thirdparty0_.avatar_url as avatar_u5_180, thirdparty0_.city as city6_180, thirdparty0_.country as country7_180, thirdparty0_.nickname as nickname8_180, thirdparty0_.openid as openid9_180, thirdparty0_.platform as platfor10_180, thirdparty0_.province as provinc11_180, thirdparty0_.sex as sex12_180, thirdparty0_.uid as uid15_180, thirdparty0_.unionid as unionid13_180, thirdparty0_.uuid as uuid14_180 from third_partytpusers 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 3 years ago

解决办法是把 // @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 3 years ago

不过建议对JPA不熟悉的，建议实体上都不要用关联关系。用mysql的思路解决问题即可；