mysql - Spring JPA : Multiple Aurora RDS MySQL operation is taking too much time

Question

具有将数据插入多个表的 POST API。 (MySQL)

使用 JPA 实现。

以下是正在发生的操作顺序，任何建议:如何优化它。

SQL 查询:

1) Select * from University where UID = 'UNI1';
2) If (University Not Exist) then Insert INTO University ...

3) Select * from College where UID = 'UNI1'
4) If (College Not Exist) then Insert INTO College ...

**In Loop (For Each Student)**

5) Delete * from CollegeStudent;

LOOP :

6) Select * from Student where StudentId = 'ST22'
7) If (Student Not Exist) then Insert INTO Student ...

8)  Insert INTO CollegeStudent (Student, College);

LOOP ENDS;

代码片段:

      @Transactional      

      public void persistStudentResults(String universityId, String collegeId, List<Student> studentList) {
            University university= universityRepository.findByUniversityId(universityId);
            if (university == null) {
                university = createUniversityObject(universityId);
                universityRepository.save(university );
            }

            College college = collegeRepository.getCollegeByCollegeId(university.getUniversityId(), collegeId);

            if (college == null) {
                college = createCollegeObject(university , collegeId);
                collegeRepository.save(deviceDetails);
            }


            collegeStudentRepository.deleteByCollegeId(university.getUniversityId(), college.getCollegeId());

            for (Student student: studentList) {
                Student dbStudent = studentRepository.findByStudentId(student.getStudentId());

                if (dbStudent == null) {
                    dbStudent = createStudentObject(student);
                    studentRepository.save(dbStudent);                    
                }

                CollegeStudent collegeStudent = createCollegeStudentObject(dbStudent, college);
                collegeStudentRepository.save(collegeStudent);
            }
        }

休眠日志:

className=org.hibernate.engine.internal.StatisticalLoggingSessionEventListener, methodName=end> StatisticalLoggingSessionEventListener - Session Metrics {
   308714170 nanoseconds spent acquiring 1 JDBC connections;
   0 nanoseconds spent releasing 0 JDBC connections;
   524069 nanoseconds spent preparing 1 JDBC statements;
   309001256 nanoseconds spent executing 1 JDBC statements;
   0 nanoseconds spent executing 0 JDBC batches;
   0 nanoseconds spent performing 0 L2C puts;
   0 nanoseconds spent performing 0 L2C hits;
   0 nanoseconds spent performing 0 L2C misses;
   197852 nanoseconds spent executing 1 flushes (flushing a total of 1 entities and 0 collections);
   0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

似乎每个 save() 都在创建新的连接。

---

所用时间:

学生人数:5

MySQL 数据库:243 毫秒

极光数据库:32 秒

(如果使用 DBeaver 直接插入数据库:1.5 秒)

---

学生人数:30

MySQL 数据库:1 秒

极光数据库:173 秒

(如果使用 DBeaver 直接插入数据库:9 秒)

---

Answer 1

添加索引键以主要使用表中的列。