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

[zhangzhenhuajack]

出现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]

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

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