java - Spring数据查询非常慢

Question

我有一个 Spring Boot 项目，设置为将 MySQL 数据库与 Hibernate 结合使用。我最近向数据库表中添加了 3 万个新条目，从那时起，查询速度就非常慢。

我使用 Query DSL 来执行查询本身。假设我有以下 Candidate 实体，与 CandidateField 实体具有 OneToMany 关系:

@Entity
@Table(name = "Candidates", schema = "Candidate")
public class Candidate {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "CandidateID")
private Long candidateID;
@Column(name = "FirstName")
private String firstName;
@Column(name = "LastName")
private String lastName;
@Column(name = "Zip")
private String zip;
@Column(name = "Email")
private String email;

@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "candidate")
private List<CandidateField> fields;

//Constructors, getters, setters omitted

CandidateField 实体:

@Entity
@Table(name = "CandidateFields", schema = "Candidate")
public class CandidateField {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "CandidateFieldID")
@JsonIgnore
private Long candidateFieldID;
@JsonIgnore
@Column(name = "CandidateID")
private Long candidateID;
@Column(name = "Name")
private String name;
@ManyToOne
@JoinColumn(name = "CandidateID", referencedColumnName = "CandidateID", insertable = false, updatable = false)
@JsonIgnore
private Candidate candidate;

//Constructors, getters, setters omitted

最后，这是我的搜索代码:

@Autowired
CandidateRepository repository;
QCandidate candidate = QCandidate.candidate;
BooleanExpression predicate = candidate.fields.any().name.eq("Accounting");
Iterable<Candidate> results = repository.findAll(predicate);

最后一行大约需要 8 分钟来搜索 30k 记录的数据库(没有索引字段)，结果为 80 行。该问题在本地和我的在线临时服务器中都是相同的。它生成此查询，然后暂停 8 分钟:

select candidate0_.CandidateID as Candidat1_4_, candidate0_.Email as
Email7_4_, candidate0_.FirstName as FirstNam8_4_, candidate0_.LastName as
LastNam17_4_, candidate0_.Zip as Zip30_4_ from Candidate.Candidates
candidate0_where exists (select 1 from Candidate.CandidateFields fields1_
where candidate0_.CandidateID=fields1_.CandidateID and fields1_.Name=?)
binding parameter [1] as [VARCHAR] - [Accounting]

编辑:该问题肯定源于自动生成的查询中的 EXISTS 子句。我现在必须研究它为什么这样做以及如何使其生成更有效的查询

奇怪的是，如果我执行一个不带任何参数的简单 findAll() ，它实际上似乎是在选择和解析结果而不是暂停。如果我按 ID 而不是名称搜索，问题仍然存在。

在我的数据库管理工具中运行实际的 SQL 查询会在大约 1 秒内返回结果(这是根据请求提供 EXPLAIN 结果的查询):

EXPLAIN SELECT *
FROM Candidates
INNER JOIN Candidate.CandidateFields
WHERE CandidateFields.CandidateID = Candidates.CandidateID AND 
CandidateFields.Name = "Accounting";

结果:

<table>
 <tr>
   <th>id</th>
   <th>select_type</th>
   <th>table</th>
   <th>partitions</th>
   <th>type</th>
   <th>possible_keys</th>
   <th>key</th>
   <th>key_len</th>
   <th>ref</th>
   <th>rows</th>
   <th>filtered</th>
   <th>extra</th>
 </tr>
  <tr>
  <td>1</td>
   <td>SIMPLE</td>
    <td>CandidateFields</td>
    <td>null</td>
    <td>ALL</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
    <td>30601</td>
    <td>10</td>
    <td>Using where</td>
  </tr>
    <tr>
  <td>1</td>
   <td>SIMPLE</td>
    <td>Candidates</td>
    <td>null</td>
    <td>eq_ref</td>
    <td>PRIMARY</td>
    <td>PRIMARY</td>
    <td>4</td>
    <td>Candidate.CandidateFields.CandidateID</td>
    <td>1</td>
    <td>100</td>
    <td>Using where</td>
  </tr>
  
  </table>

编辑:该问题肯定源于自动生成的查询中的 EXISTS 子句。我现在必须研究它为什么这样做以及如何使其生成更有效的查询

非常感谢任何和所有的帮助。如果需要的话也可以澄清。干杯!

Answer 1

no fields are indexed

为什么不呢？只需在此处添加此索引即可:

CREATE INDEX i1 ON Candidate.CandidateFields(Name);

或者对于这个特定的查询甚至更快:

CREATE INDEX i2 ON Candidate.CandidateFields(Name, CandidateID);

因为后者将是半连接(EXISTS() 子查询)的覆盖索引。