JPA的查询优化（抓取策略选择+@EntityGraph）

相关类：

org.hibernate.annotations.@Fetch

org.hibernate.annotations.FetchMode

不加默认是 FetchMode.SELECT，必会生成1+N问题； FetchMode.JOIN，只有findByid只执行1条SQL，其他还是1+N问题； FetchMode.SUBSELECT，任何情况下都是2条SQL，一条主，一条从； 而EntityGraph则更强大，任何情况下都是1条。

下面我测试结果：

数据准备

@Entity
@Data
public class Person {

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

   private String name;

   @OneToMany(cascade = CascadeType.ALL)
   @JoinColumn(name = "person_id",referencedColumnName = "id")
   private List<Address> addresses=new ArrayList<>();
}

@Data
@Entity
public class Address {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @JoinColumn(insertable = false,updatable = false)
    @ToString.Exclude
    private Person person;

}

添加数据，5*100个

@Test
void    contextLoads() {
    add("one");
    add("two");
    add("three");
    add("four");
    add("five");
}
private void add(String name){
    final Person person = new Person();
    person.setName(name);
    for (int i = 0; i < 100; i++) {
        person.getAddresses().add(new Address());
    }
    personRepository.save(person);
}

未加任何注解 （相当于@Fetch(value = FetchMode.SELECT)）

personRepository.findById(1L);生成两条{一条主，一条查所有的从}

Hibernate: select person0_.id as id1_1_0_, person0_.name as name2_1_0_ from person person0_ where person0_.id=?
Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?

personRepository.findAllById(List.of(2L, 3L));生成3条（1+N）{1条查所有的主，2（N是1中的数据大小）条每一个的所有的从}

Hibernate: select person0_.id as id1_1_, person0_.name as name2_1_ from person person0_ where person0_.id in (? , ?)
Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?
Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?

personRepository.findAll()生成1+N，1条主，对个单个主的所有从都单独查询

    Hibernate: select person0_.id as id1_1_, person0_.name as name2_1_ from person person0_
    Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?
    Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?
    Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?
    Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?
    Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?

@Fetch(value = FetchMode.JOIN)

personRepository.findById(1L);生成1条，返回N行，是N个从

Hibernate: select person0_.id as id1_1_0_, person0_.name as name2_1_0_, addresses1_.person_id as person_i2_0_1_, addresses1_.id as id1_0_1_, addresses1_.id as id1_0_2_, addresses1_.person_id as person_i2_0_2_ from person person0_ left outer join address addresses1_ on person0_.id=addresses1_.person_id where person0_.id=?

personRepository.findAllById(List.of(2L, 3L))；生成3（1+N）条，同FetchMode.SELECT

Hibernate: select person0_.id as id1_1_, person0_.name as name2_1_ from person person0_ where person0_.id in (? , ?)
Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?
Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?

personRepository.findAll()生成（1+N）条，同FetchMode.SELECT

    Hibernate: select person0_.id as id1_1_, person0_.name as name2_1_ from person person0_
    Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?
    Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?
    Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?
    Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?
    Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?

@Fetch(value = FetchMode.SUBSELECT)

personRepository.findById(1L)生成2条，同FetchMode.SELECT

Hibernate: select person0_.id as id1_1_0_, person0_.name as name2_1_0_ from person person0_ where person0_.id=?
Hibernate: select addresses0_.person_id as person_i2_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.person_id as person_i2_0_1_ from address addresses0_ where addresses0_.person_id=?

personRepository.findAllById(List.of(2L, 3L))；生成2条，1条所有的主，1条所有的从

Hibernate: select person0_.id as id1_1_, person0_.name as name2_1_ from person person0_ where person0_.id in (? , ?)
Hibernate: select addresses0_.person_id as person_i2_0_1_, addresses0_.id as id1_0_1_, addresses0_.id as id1_0_0_, addresses0_.person_id as person_i2_0_0_ from address addresses0_ where addresses0_.person_id in (select person0_.id from person person0_ where person0_.id in (? , ?))

personRepository.findAll()生成2条，1条所有的主，1条所有的从

    Hibernate: select person0_.id as id1_1_, person0_.name as name2_1_ from person person0_
    Hibernate: select addresses0_.person_id as person_i2_0_1_, addresses0_.id as id1_0_1_, addresses0_.id as id1_0_0_, addresses0_.person_id as person_i2_0_0_ from address addresses0_ where addresses0_.person_id in (select person0_.id from person person0_)

使用@EntityGraph 使用Join只使用1条SQL来完成查询

public interface PersonRepository extends JpaRepository<Person, Long> {
    @Override
    @EntityGraph(attributePaths = { "addresses" })
    List<Person> findAll();

    @Override
    @EntityGraph(attributePaths = { "addresses" })
    List<Person> findAllById(Iterable<Long> longs);

    @Override
    @EntityGraph(attributePaths = { "addresses" })
    Optional<Person> findById(Long aLong);
}

personRepository.findById(1L);生成1条,同JOIN，返回N个从，每个从都有主的字段

Hibernate: select person0_.id as id1_1_0_, person0_.name as name2_1_0_, addresses1_.person_id as person_i2_0_1_, addresses1_.id as id1_0_1_, addresses1_.id as id1_0_2_, addresses1_.person_id as person_i2_0_2_ from person person0_ left outer join address addresses1_ on person0_.id=addresses1_.person_id where person0_.id=?

personRepository.findAllById(List.of(2L, 3L));生成1条,同JOIN，返回N个从，每个从都有主的字段

Hibernate: select person0_.id as id1_1_0_, addresses1_.id as id1_0_1_, person0_.name as name2_1_0_, addresses1_.person_id as person_i2_0_1_, addresses1_.person_id as person_i2_0_0__, addresses1_.id as id1_0_0__ from person person0_ left outer join address addresses1_ on person0_.id=addresses1_.person_id where person0_.id in (? , ?)

personRepository.findAll()；生成1条,同JOIN，返回N个从，每个从都有主的字段

Hibernate: select person0_.id as id1_1_0_, addresses1_.id as id1_0_1_, person0_.name as name2_1_0_, addresses1_.person_id as person_i2_0_1_, addresses1_.person_id as person_i2_0_0__, addresses1_.id as id1_0_0__ from person person0_ left outer join address addresses1_ on person0_.id=addresses1_.person_id