jpa：多个aurora-rds-mysql操作占用了太多时间

t2a7ltrp 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(418)

具有将数据插入多个表的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（）都在创建新的连接。