与SQL相比，Hibernate JPQL查询非常慢

Question

@Service
@Transactional
@Slf4j
public class PersonSyncServiceImpl
        extends HotelApiCommunicationService implements PersonSyncService {
[...]
    private PersonLinked getLinkedPerson(CNPerson cnPerson, Obiekt obiekt) {
        PersonLinked person = cnPerson.getPersonLinked();
        if (person == null) {     
            var o = cnPerson;
            List<PersonLinked> personList = personLinkedDao.findPersonLinkedByRecordData(
                    o.getPersonImiona(), o.getPersonNazwisko(), o.getPersonPesel(), o.getPersonEmail(), o.getPersonTelefon1());            
            person = personList.stream()                
                    .findFirst().orElse(null);
            if (person == null) {
                person = createPersonLinkedFromCnPerson(cnPerson);
                personLinkedDao.save(person);
            }
            cnPerson.setPersonLinked(person);            
        }
        return person;
    }
[...]   
}   

List<PersonLinked> personList = personLinkedDao.findPersonLinkedByRecordData(
        o.getPersonImiona(), o.getPersonNazwisko(), o.getPersonPesel(), o.getPersonEmail(), o.getPersonTelefon1());  

已定义查询的Dao：

@Repository
@Transactional
public interface PersonLinkedDao extends JpaRepository<PersonLinked, Long> {

    @Query("select o from PersonLinked o \n" +
            "where o.personImiona = :imie and o.personNazwisko = :nazwisko \n" +
            "  and (o.personPesel = :pesel or o.personEmail = :email or o.personTelefon1 = :telefon)")
    List<PersonLinked> findPersonLinkedByRecordData(
                                                  @Param("imie") String personImiona,
                                                  @Param("nazwisko") String personNazwisko,
                                                  @Param("pesel") String personPesel,
                                                  @Param("email") String personEmail,
                                                  @Param("telefon") String personTelefon);
}

来自Hibernate调试日志的SQL：

select [..]
from
    person personlinke0_ 
where
    personlinke0_.person_imiona=? 
    and personlinke0_.person_nazwisko=? 
    and (
        personlinke0_.person_pesel=? 
        or personlinke0_.person_email=? 
        or personlinke0_.person_telefon1=?
    )

当我在数据库上执行这个查询时，大约需要15ms，从代码上执行大约需要1.5秒。我在代码中注释掉了这一行，滞后消失了，所以问题肯定是这个jpql选择。

数据库连接配置：

spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL9Dialect
spring.datasource.url=jdbc:postgresql://192.168.1.200:5433/XXXXXXX
spring.datasource.username=XXXXX
spring.datasource.password=XXXXX
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.generate_statistics=true

更新1：

debug.log：

26-09-2020 16:06:36.130 [http-nio-8091-exec-2] DEBUG org.hibernate.SQL.logStatement - 
    select [...]
    from
        person personlinke0_ 
    where
        personlinke0_.person_imiona=? 
        and personlinke0_.person_nazwisko=? 
        and (
            personlinke0_.person_pesel=? 
            or personlinke0_.person_email=? 
            or personlinke0_.person_telefon1=?
        )
26-09-2020 16:06:36.130 [http-nio-8091-exec-2] DEBUG o.s.orm.jpa.JpaTransactionManager.doGetTransaction - Found thread-bound EntityManager [SessionImpl(1971671100<open>)] for JPA transaction
26-09-2020 16:06:36.130 [http-nio-8091-exec-2] DEBUG o.s.orm.jpa.JpaTransactionManager.handleExistingTransaction - Participating in existing transaction
26-09-2020 16:06:36.146 [http-nio-8091-exec-2] DEBUG o.s.orm.jpa.JpaTransactionManager.doGetTransaction - Found thread-bound EntityManager [SessionImpl(1971671100<open>)] for JPA transaction
26-09-2020 16:06:36.146 [http-nio-8091-exec-2] DEBUG o.s.orm.jpa.JpaTransactionManager.handleExistingTransaction - Participating in existing transaction
26-09-2020 16:06:36.146 [http-nio-8091-exec-2] DEBUG o.s.orm.jpa.JpaTransactionManager.doGetTransaction - Found thread-bound EntityManager [SessionImpl(1971671100<open>)] for JPA transaction
26-09-2020 16:06:36.146 [http-nio-8091-exec-2] DEBUG o.s.orm.jpa.JpaTransactionManager.handleExistingTransaction - Participating in existing transaction
26-09-2020 16:06:37.521 [http-nio-8091-exec-2] DEBUG org.hibernate.SQL.logStatement - 

@Entity
@Table(name = "person")
@Getter
@Setter
@SuperBuilder
@EqualsAndHashCode(of = "personId")
public class PersonLinked extends SCPerson {

    @Id
    @GeneratedValue(generator = "seq_person", strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "seq_person", sequenceName = "seq_person", allocationSize = 30)
    @Column(name = "OSOBA_ID", nullable = false)
    private Long personId;


    @OneToMany(mappedBy = "personLinked", fetch = FetchType.LAZY)
    private List<CNPerson> cnPersonList;

    @Tolerate
    public PersonLinked() {
        super();
    }

    @PrePersist
    @Override
    protected void preInsert() {
        super.preInsert();
    }
}

@MappedSuperclass
@Getter
@Setter
@SuperBuilder
public class SCPerson {
[...]
}

Answer 1

最后我找到了一个解决方案，问题出在代码的另一部分。在调用方法getLinkedPerson（）之前，我有这行代码：

List<CNPerson> cnPersonList = cnPersonDao.findCnPersonNotLinkedWithPerson(obiekt.getLoid());

CNPersons在这里列出了大约7万个对象。

我改成了：

List<Integer> ids = cnPersonDao.findCnPersonIdsNotLinkedWithPerson(obiekt.getLoid());

在Hibernate上下文刷新期间放慢速度。如果您更新了太多的对象，ORM引擎（比如说Hibernate）必须将它们吸收并保存在内存中。实际上，Hibernate必须具有更新对象的所有旧状态和所有新状态。有时它这样做是很不理想的。

您可以使用debug来指示这一点。试着找到最慢的地方，检查那里到底在调用什么。我可能猜测，当hibernate更新html" target="_blank">缓存状态时，它会变慢。

我认为是因为实体CNPerson和PersonLinked是链接的，但我不确定：

@ManyToOne(fetch = FetchType.LAZY,
        cascade = {CascadeType.MERGE, CascadeType.PERSIST})
@JoinTable(name = "cnperson_links",
        joinColumns = {@JoinColumn(name = "cnperson_loid")},
        inverseJoinColumns = {@JoinColumn(name = "person_id")})
private PersonLinked personLinked;