java - 递归 ORM 类的 Spring Repository 性能问题

Question

我的目标:

我有一个列表中最低级别的 child (例如技能 ID 10 和 12)。现在，我想要每个 child (在本例中为 34 岁的 parent )的所有 parent (parent_id = null)，并将它们再次保存在列表中。毕竟我想要从 parent 到每个 child 的路径(34-9-10 和 34-9-12)。稍后我想检查这些路径(34、9、10、12)上的每项技能。

最后，我有一个技能集合，说明了从上到下的路径。

情况:

我正在使用 MariaDB(MySQL 方言)并具有以下递归表(从 idSkill:9 到父级 34)

现在我要使用 Spring Crud Repository 请求每个父元素 (parent_id = null)。为此，我使用循环遍历包含所有父元素 ID 的列表并为每个父元素 ID 调用 findOne(parentelementid) 并使用延迟加载:

List<Skill> parentList = skillDAO.findBySkill(null);
HashMap<Integer, ArrayList<Integer>> parentTree = customSkillDAO.findParentIdsByPersonSkills(listPersonskill);

    //Integer: Durchnummeriert zur Eindeutigkeit, von 0,1,2...
    //List: Pfad vom höchsten Vaterlement zum niedrigsten Personskill
    //Notwendig, um den Pfad pro niedrigsten Knoten auf true zu setzen
    HashMap<Integer, ArrayList<Integer>> parentTree = customSkillDAO.findParentIdsByPersonSkills(listPersonskill);
    log.info("START FINDING CHECKED");
//keySet is just numbered from 0,1,2,3...
for (int counter : parentTree.keySet()) {
        //parentTree.get(counter) gives a list whith Integer that describes the path from top to bottom.
        //So the first element is always the parent.
        mapParentSkills.put(parentTree.get(counter).get(0), new SkillDTO(skillDAO.findOne(parentTree.get(counter).get(0))));
        mapParentSkills.get(parentTree.get(counter).get(0)).setChecked(true);
    }
    log.info("START FINDING NOT CHECKED");
//Add all other parent that are not checked
for (Skill skill : parentList) {
        if (!mapParentSkills.containsKey(skill.getIdSkill())) {
            mapParentSkills.put(skill.getIdSkill(), new SkillDTO(skill));
        }
    }

    log.info("ENDE SKILLS");

我得到了整棵树，这很好。唯一的问题是它需要大约 10 秒。 任何人都可以告诉我一些改进建议以至少在 <2 秒内完成吗？

这是我的类(class):

public class Skill implements java.io.Serializable {

    public Skill() {
}


@Id
@GeneratedValue(strategy = IDENTITY)

@Column(name = "idSkill", unique = true, nullable = false)
public Integer getIdSkill() {
    return this.idSkill;
}

public void setIdSkill(Integer idSkill) {
    this.idSkill = idSkill;
}

...一些未加载的@JsonBackReferences

@JsonBackReference
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "parent_id")
public Skill getSkill() {
    return this.skill;
}

public void setSkill(Skill skill) {
    this.skill = skill;
}

@JsonManagedReference
@OneToMany(fetch = FetchType.LAZY, mappedBy = "skill")
public Set<Skill> getSkills() {
    return this.skills;
}

public void setSkills(Set<Skill> skills) {
    this.skills = skills;
}

}

日志:

web - 2016-02-13 16:53:50,163 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - START FINDING CHECKED Hibernate: select levelbezei0_.idLevelBezeichnung as idLevelB1_4_0_, levelbezei0_.bezeichnung as bezeichn2_4_0_ from quanto_portal.levelBezeichnung levelbezei0_ where levelbezei0_.idLevelBezeichnung=? Hibernate: select skills0_.parent_id as parent_i4_15_0_, skills0_.idSkill as idSkill1_15_0_, skills0_.idSkill as idSkill1_15_1_, skills0_.levelBezeichnung_id as levelBez3_15_1_, skills0_.name as name2_15_1_, skills0_.parent_id as parent_i4_15_1_ from quanto_portal.skill skills0_ where skills0_.parent_id=?

...相同的选择约 50 次...

web - 2016-02-13 16:53:51,523 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - START FINDING NOT CHECKED Hibernate: select skills0_.parent_id as parent_i4_15_0_, skills0_.idSkill as idSkill1_15_0_, skills0_.idSkill as idSkill1_15_1_, skills0_.levelBezeichnung_id as levelBez3_15_1_, skills0_.name as name2_15_1_, skills0_.parent_id as parent_i4_15_1_ from quanto_portal.skill skills0_ where skills0_.parent_id=?

..同样选择几百次...

web - 2016-02-13 16:53:59,289 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - ENDE SKILLS

更新日志

web - 2016-02-13 19:48:25,471 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - START FINDING CHECKED

Hibernate: select levelbezei0_.idLevelBezeichnung as idLevelB1_4_0_, levelbezei0_.bezeichnung as bezeichn2_4_0_ from quanto_portal.levelBezeichnung levelbezei0_ where levelbezei0_.idLevelBezeichnung=?

web - 2016-02-13 19:48:25,806 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - START FINDING NOT CHECKED

web - 2016-02-13 19:48:25,807 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - ENDE SKILLS

技能:

public SkillDTO(Skill skill) {
    idSkill = skill.getIdSkill();
    name = skill.getName();
    levelBezeichnung = skill.getLevelBezeichnung().getBezeichnung();
    checked = skill.isChecked();
    if (skill.getSkills().size() > 0) {
        Iterator<Skill> iteratorSkill = skill.getSkills().iterator();
        while (iteratorSkill.hasNext()) {
            Skill tempSkill = iteratorSkill.next();
            skills.add(convertSkillsToProfileDTO(tempSkill));
        }
    }
}

private SkillDTO convertSkillsToProfileDTO(Skill skill) {
    return new SkillDTO(skill);
}

Answer 1

我不确定，因为提供的代码不足以理解您的应用程序的功能。

但是可能过程需要很长时间，因为你在循环中发送了太多的请求。单独的请求通常比单个请求花费更多的时间。尝试用单个请求替换它。例如:

@Repository
public interface skillDAO extends CrudRepository<Skill, Integer>{

   ...

   @Query("select s from Skill s where s.skill is null")
   List<Person> findRootSkills();
}

并将其用作:

List<Skill> rootSkillList = skillDAO.findRootSkills();
for(Skill skill : rootSkillList){
        SkillDTO dto = new SkillDTO(skill)
        dto.setChecked(true);
        mapParentSkills.put(skill.getIdSkill(), dto);   
}

---

如果你需要从你的 parentTree 结构中通过 ID 获取技能，你可以做下一步:

@Repository
public interface skillDAO extends CrudRepository<Skill, Integer>{

   ...

   @Query("select s from Skill s where s.idSkill in (:idList)")
   List<Person> findSkillsById(@Param("idList") List<Integer> idList);
}

现在只需从您的 parentTree 中收集所有 ID 并获取 Skill 对象列表:

List<Integer> idList = new ArrayList<Integer>();
for (int counter : parentTree.keySet()) {
    idList.add(parentTree.get(counter).get(0));
}
List<Skill> rootSkillList = skillDAO.findSkillsById(idList);

//here you can fill mapParentSkills

我不确定，我是否正确检测到时间延迟点。可能，延迟在 DTO 的方法中 - setChecked(true)。但无论如何希望这会有用。

更新:

public SkillDTO(Skill skill) {
    ...
    //LOOKS LIKE NEXT LINE IS YOU PROBLEM
    if (skill.getSkills().size() > 0) {            
        Iterator<Skill> iteratorSkill = skill.getSkills().iterator();
        while (iteratorSkill.hasNext()) {
            Skill tempSkill = iteratorSkill.next();
            skills.add(convertSkillsToProfileDTO(tempSkill));
        }
    }
}

看来，我找到了性能问题的原因。在你的 Skill 类字段 skills 声明为:

@JsonManagedReference
@OneToMany(fetch = FetchType.LAZY, mappedBy = "skill")
public Set<Skill> getSkills() {
    return this.skills;
}

fetch = FetchType.LAZY 意味着，Set 将在您调用方法 getSkills() 时加载。因此，每次您调用方法 getSkills() 时，JPA 都会创建查询并将其发送到 DB 以获取技能列表。构造函数会为技能列表中的每项技能执行此操作。这需要很多时间。尝试用 fetch = FetchType.EAGER 替换 fetch = FetchType.LAZY，我怀疑性能会提高很多。