JPA 쿼리 성능 향상 ( 퀴리 튜닝, 정규화 및 비정규화 )

문제 발생

엑셀에 넘겨줄 데이터를 전달하고 있습니다. 시간을 보시면 1분이 넘어가는 걸 볼 수 있습니다. 개발 서버로 진행했을 때, 이 정도인 데 운영 서버의 상태, 클라이언트의 엑셀 폼 만드는 속도까지 합해지면 속도가 더 느려질 수 있는 상황이였습니다. 기능을 만들긴 했지만, 성능, 속도면에서 생각하지 않았다는 점에서 이 짤이 생각나네요...

문제 원인

약, 2만 건의 교육 테이블에서 정보를 가져와서 해당되는 설문지 버전을 가져와, 사용자에 해당되는 답안지를 for문에...for문에..for으로 가져오고 있었습니다.

이제와 생각해보면 진짜 말도 안 되는 프로세스인데, (n * n * n) = O(n³)의 속도를 가질 것입니다..

문제의 코드를 보시면, Edu를 JPA로 넘겨받아 클라이언트에 필요한 정보를 만드는 모습입니다.

// 변수 초기화
List<ResponseEduExcel> eduExcelList = new ArrayList<>();

String eduSeq = "";
String userName = "";
String childName = "";
String nurseName = "";

String userAnswer1 = "";
String userAnswer2 = "";
String userAnswer3 = "";
String userAnswer4 = "";
String userAnswer5 = "";

ResponseEduExcel eduExcel = ResponseEduExcel.build(); // 객체 생성

// JPA 방식으로 개발
List<EduEntity> edu = eduRepository.findAll();
// 교육 테이블의 데이터를 모두 가져오기
for (int i = 0; i < edu.size(); i++)  {
	// edu에서 가져올 수 있는 정보 Set하는 코드
	
	// 교육-설문지 버전에 해당되는 답안지 가져오기
	for (int j = 0; j < survey.size(); j++)  {
	
		// 해당 답안지의 해당 정답(정답 테이블) 가져오기
		for (int k = 0; k < userAnswer.size(); k++) {
		
			// userAnswer1 ~ userAnswer5 정보 Set하는 코드
		}
		eduExcelList.add(객체);
	}
}

userAnswer에는 사용자가 어떤 교육을 시행했으며, 어떤 버전으로 어떤 설문지를 받았는지 저장한 테이블이고, questionAnswer에는 그 설문지에 사용자가 어떤 답을 했는 지를 알 수 있습니다.

해결방법은 첫 번째, 쿼리 튜닝으로 클라이언트에게 건네줄 데이터를 쿼리를 통해서 건네주는 방식으로 변환하는 방법이 있겠습니다.

두 번째는 테이블을 비정규화시킴으로 회원의 정답지를 도달하기 위한 많은 테이블들을 합쳐보는 방법도 있겠습니다. 

해결방법

1. 쿼리 튜닝

자바에서 쿼리를 실행하는 방법은 JPA, JPQL, Native Query 세 가지 중에서 기존 방법은 JPA를 이용을 했지만, JPA는 복잡한 쿼리문은 작성하기 힘들다는 점에, JPQL은 도메인 모델과 일치해야 함에 따라, Native Query를 이용해 복잡한 쿼리를 한 번에 작성하도록 수정했습니다.

수정

1. 가져올 데이터를 interface로 구현

public interface EduInterface {

   Long getEduSeq(); // "eduSeq"
   String getUserName(); // "userName"
   String getChildName(); // "childName"
   String getNurseName(); // "nurseName"
   String getRgstDate(); // "rgstDate"
   String getUserAnswer1(); // "userAnswer1"
   String getUserAnswer2(); // "userAnswer2"
   String getUserAnswer3(); // "userAnswer3"
   String getUserAnswer4(); // "userAnswer4"
   String getUserAnswer5(); // "userAnswer5"

}

 

2. JPA에서 Native Query로 작성

1. edu, question_answer, user, answer 조인 쿼리

SELECT e.edu_seq AS eduSeq, u.user_name AS userName,
	(SELECT child_name FROM child AS c WHERE e.child_seq = c.child_seq) AS childName,
	(SELECT admin_name FROM admin AS a WHERE e.admin_seq = a.admin_seq) AS nurseName,
	a.answer AS answer
FROM user_answer AS ua
JOIN edu AS e
ON ua.edu_seq = e.edu_seq
JOIN question_answer AS qa
ON ua.user_answer_seq = qa.user_answer_seq
JOIN user AS u
ON u.user_seq = e.user_seq
JOIN answer AS a
ON a.answer_seq = qa.answer_seq
ORDER BY u.user_seq, ua.question_seq

이렇게 되면 Java에서 객체로 받아서 for으로 userAnswer1, userAnswer2, ●●●● userAnswer5로 Set 하는 과정이 필요합니다. 첫 번째로, for문을 사용한다는 점에서 불필요함을 느꼈고, 중복되는 데이터들을 가져와야 한다는 점에서 불필요한 리소스를 사용한다는 생각이 들었습니다.

그래서, 저는 자바에서 Interface대로 EduSeq당 userAnswer1, userAnswer2, ●●●● userAnswer5로 임의 필드를 생성했습니다.

 

  2. 교육에 대한 사용자 답을 임시 필드로 생성

// Query문
 @Query(value = 
 	    "SELECT u.user_seq AS userSeq, " +
      "SELECT u.user_name AS userName, " + 
      "(SELECT child_name FROM child AS c where e.child_seq = c.child_seq) as childName, " + 
      "(SELECT admin_name FROM admin AS a where e.admin_seq = a.admin_seq) as nurseName, " + 
      "DATE_FORMAT(e.rgst_date, '%Y-%m-%d') AS rgstDate, " +
      "MAX(case when ua.question_seq = 1 then a.answer END) AS userAnswer1, " +
      "MAX(case when ua.question_seq = 2 then a.answer END) AS userAnswer2, " +
      "MAX(case when ua.question_seq = 3 then a.answer END) AS userAnswer3, " +
      "MAX(case when ua.question_seq = 4 then a.answer END) AS userAnswer4, " +
      "MAX(case when ua.question_seq = 5 then a.answer END) AS userAnswer5 " +
	  "FROM user_answer AS ua " +
	  "JOIN edu AS e " +
	  "ON e.edu_seq = ua.edu_seq " +
	  "JOIN question_answer AS qa " +
	  "ON qa.user_answer_seq = ua.user_answer_seq " +
	  "JOIN answer AS a " +
	  "ON a.answer_seq = qa.answer_seq " +
	  "JOIN user AS u " + 
	  "ON u.user_seq = e.user_seq " +
	  "GROUP BY e.edu_seq " + 
	  "ORDER BY u.user_seq ", nativeQuery = true)
List<EduInterface> findEduAnswer();

List<EduInterface> userAnswer = userAnswerRepo.findEduAnswer();

return userAnswer;

 

---

2. 테이블 정규화 비정규화

두 번째 방법은, Edu - UserAnswer을 정규화하는 방법이 있습니다.

Join 할 테이블이 많고 대량 데이터가 있는 테이블로서 비정규화를 진행하는 것이 좋을 것 같아서 이 방법도 좋겠다는 생각을 했습니다.

 

Edu에서는 기존의 eduseq와 기존이랑 다르게 userSeq, childSeq, adminSeq로 변경했습니다. 변경한 이유는 회원의 이름과 아이의 이름, 특히 담당 관리자의 이름이나 담당 관리자 자체가 변경될 가능성이 높은 점을 비롯해 Edu는 정규화하여 각각 교육 테이블에 Seq로 저장하는 것이 더 좋다고 판단했습니다.

 

UserAnswer에는 답안지에 관련된 내용을 다 담아봤습니다.

이렇게 되면 eduseq로 접근할 때 바로 UserAnswer(교육에 관한 사용자의 답) 접근이 가능하게 됩니다. 비정규화는 읽는 시간을 최적화하도록 설계된 데이터베이스입니다. 

 

Question에는 질문지에 대한 정보만 따로 저장했습니다.

userAnswer 테이블에 같이 담게 된다면, questionContent에는 긴 글, 최소 TINYTEXT정보가 담겨야 할 것이고, 각각 사용자의 설문지가 생성될 때마다 긴 글이 데이터에 저장되어야 하기 때문에, 데이터베이스 관리와 성능 부분에서 따로 관리하는 것이 낫다고 생각이 들어 UserAnswer에서 따로 관리하게 했습니다.

마치며

항상 프로젝트를 진행했을 때, 100건 안의 데이터를 다루다 보니 일단, 개발이 빠른 것을 기준으로 작성하고 있었던 자신에 대해 반성을 합니다. 이번 기회, 경험 덕분에 항상 데이터의 수를 염두해둬야 하며, 개발의 속도도 중요할 수 있지만 그보다 중요한 것은 개발 속도나 프로세스 부분에서 서로의 균형이 잘 맞는 코드인가?라고 생각이 들었습니다. 이번 프로젝트를 진행하면 큰 데이터를 다루는 능력이 아직은 부족하다는 것을 느꼈습니다. 속도 향상을 위해서는 정규화 및 비정규화와 쿼리 튜닝 해결이 있다는 것을 알게 되었고, 무조건적으로 정규화한다고 좋은 데이터베이스 설계는 아니구나라고 생각이 들었습니다. 다음부터는 자원의 소비, 전송 속도를 더 신경 써서 개발을 진행해야겠다는 생각이 들었습니다.

 

💡 더 성장하는 개발자가 될 수 있도록, 틀린 부분을 알려주신다면 감사하겠습니다.