Quiz 조회 최적화 과정
사용자(user_id)별로 정답을 맞히지 못한 퀴즈(correct = false)를 무작위로 10개 조회하는 기능을 구현했다.
처음에는 간단한 방법을 선택했지만 성능 문제로 인해 최적화 과정을 거쳤고 이를 작성했다.
*study_id, user_id, correct 컬럼을 활용했다.
1. 초기 구현 : ORDER BY RAND()
처음에는 ORDER BY RAND()를 사용해 무작위로 데이터를 조회했다.
1
2
@Query(value = "SELECT * FROM quiz q where q.correct=false and q.user_id = :userId order by RAND() limit 10", nativeQuery = true)
List<Quiz> findQuizByInCorrect(Long userId);
그러나 ORDER BY RAND()는 대량의 데이터를 다룰 경우 속도가 매우 느려진다는 단점이 있었다.
대안 찾기
application에서 랜덤 숫자를 생성해 해당 id 값을 조회하려고 했지만
사용자 별로 study_id가 연속적이지 않아 적합하지 않았다.
*JPQL → Entity 이름 사용
*Native SQL → DB table 이름 사용
자세한 내용은 하단에 작성했다.
2. 개선 : ROW_NUMBER() 를 활용
ROW_NUMBER() 는 결과 집합의 각 행에 고유 번호를 부여한다.
번호는 그룹별로 정렬 기준(ORDER BY)에 따라 할당되며 번호는 항상 1부터 시작한다.
그룹화 : PARTITION BY 는 그룹별로 번호를 부여하거나 생략하면 전체 데이터를 하나의 그룹으로 처리한다. (선택)
정렬 : ORDER BY 는 행의 순서를 정의한다. (번호 부여 순서 지정) (필수)
예시)
study_id = [1, 3, 6, 15] → ROW_NUMBER() = [1, 2, 3, 4]
→ ROW_NUMBER()를 사용하면 id가 중간에 비어 있어도 영향을 받지 않는다.
| study_id | ROW_NUMBER |
|---|---|
| 1 | 1 |
| 3 | 2 |
| 6 | 3 |
1
2
SELECT ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY study_id) AS r, quiz.*
FROM quiz;
| user_id | study_id | ROW_NUMBER |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 3 | 2 |
| 2 | 2 | 1 |
| 2 | 4 | 2 |
랜덤 숫자 생성
쿼리에서 랜덤하게 데이터를 선택하기 위해 MOD와 FLOOR를 활용했다.
RAND() : 0과 1 사이의 난수를 반환
FLOOR() : 소수점 이하를 버리고 정수로 변환
MOD(a, b) : a를 b로 나눈 나머지를 반환
FLOOR(RAND() * n) : 0 ~ n-1 범위의 정수 생성
FLOOR(RAND() * 3)→ 0, 1, 2FLOOR(RAND() * 10)→ 0 ~ 9FLOOR(RAND() * 100)→ 0 ~ 99
개선된 쿼리
1
2
3
4
5
6
@Query(value = "select * from " +
"(select row_number() over(order by id) r, quiz.* " +
"from quiz " +
"where correct = false and user_id = :userId) sub " +
"where mod(r, floor(rand() * 3) + 1 ) = 0 limit 10", nativeQuery = true)
List<Quiz> findRandomQuiz(Long userId);
RAND() * 3 : 0 ~ 3 미만의 난수를 생성
FLOOR(RAND() * 3) + 1 : 1 ~ 3 범위의 정수를 생성
MOD(r, FLOOR(RAND() * 3) + 1) = 0 : r을 랜덤 값으로 나눴을 때 나머지가 0인 값만 선택
Limit 10 : 최대 10개의 결과값 반환(만약 10보다 적은 데이터라면 그만큼의 데이터수만 가져다 준다.)
ex)
MOD(1, 3) = 1 (조건 불만족)
MOD(2, 3) = 2 (조건 불만족)
MOD(3, 3) = 0 (조건 만족)
결과적으로 r = 3, 6, 9, …가 조건을 만족한다.
매번 실행될 때 다른 난수를 반환하므로 랜덤 조건이 적용되었다.
참고) r은 단순히 필터링된 데이터셋에서 순번을 나타내는 컬럼이며
where 조건에 따라서 해당 컬럼들을 select에 작성해야해서 quiz.*로 작성했다.
서브쿼리는 반드시 별칭(alias)을 지정해야해서 sub로 작성했다.
3. 성능 비교 및 분석
기존 쿼리(ORDER BY RAND())와 개선된 쿼리(ROW_NUMBER())의 성능을 비교했다.
*편의상 일부 코드를 생략했다.
1
2
3
4
5
6
7
8
9
10
11
12
13
List<StudyResponseDto> list = new ArrayList<>();
// 1번
long start = System.nanoTime();
List<Quiz> quizByInCorrect = quizRepository.findQuizByInCorrect(user.getId());
long end = System.nanoTime();
log.info("시간 소요 1 : {} ", (end-start));
// 2번
long start1 = System.nanoTime();
List<Quiz> randomQuiz = quizRepository.findRandomQuiz(user.getId());
long end1 = System.nanoTime();
log.info("시간 소요 2 : {} ", (end1-start1));
quiz 2는 랜덤으로 n칸씩 띄어서 가져오기 때문에 데이터의 개수에 따라서 10개 미만으로 나올 수도 있다.
따라서 2번째 test부터 quiz 1의 limit를 5로 고정해서 테스트 했다.
평균적으로 6개를 조회하는데
ORDER BY RAND()는 0.085862초, ROW_NUMBER()는 0.018174초가 걸렸다.
개선한 쿼리가 약 4.7배 빠른 성능을 보였다.
| Test Case | Quiz 1 (ORDER BY RAND) | Quiz 2 (ROW_NUMBER) |
|---|---|---|
| Run 1 | 121,789,400 (10개) | 2,491,901 (5개) |
| Run 2 | 128,090,999 (5개) | 35,581,300 (8개) |
| Run 3 | 79,390,300 (5개) | 13,276,901 (7개) |
| Run 4 | 35,747,301 (5개) | 23,408,500 (7개) |
| Run 5 | 64,292,000 (5개) | 16,110,600 (6개) |
성능 차이 분석
개선한 쿼리도 order by는 쓰이고 있지만 성능 차이가 발생하는 이유에 대해서 분석해봤다.
DBMS에서 인덱스는 데이터의 읽기 속도를 높이는 기능이다.
pk는 레코드(하나의 행)를 대표하는 column의 값으로 만들어진 index를 의미한다.
1. ORDER BY RAND()
임의 정렬 방식:
RAND()로 각 레코드에 임의의 값을 부여하고 이를 정렬하여 결과를 반환한다.인덱스 활용X:
ORDER BY RAND()를 이용한 임의 정렬이나 조회는 인덱스를 사용할 수 없다.전체 데이터 정렬:
WHERE조건을 만족하는 데이터를 모두 가져와 임의값으로 정렬한 후 제한(LIMIT)을 적용한다.
2. ROW_NUMBER()
인덱스 활용:
ROW_NUMBER()를 사용해 데이터를 정렬할 때 인덱스를 활용해서 불필요한 정렬 작업을 줄였다.정렬 효율성: 실제 인덱스 값이 정렬된 상태이므로 순서대로 읽는 작업만 수행한다.
추가적인 정렬 생략: MySQL 서버는 인덱스를 활용할 경우 추가적인 정렬 작업을 하지 않는다.
ORDER BY RAND()는 WHERE 조건에 맞는 데이터를 모두 가져온 후 정렬하며
ROW_NUMBER()는 조건에 맞는 데이터를 가져오며 정렬까지 처리하므로 성능차이가 발생한 것 같다.
추가 설명)
JPQL
JPQL(Java Persistence Query Language)는 JPA에서 SQL을 추상화하여 만든 객체 지향 쿼리 언어다.
Entity 중심 : db 테이블이 아닌 entity를 대상으로 쿼리를 작성한다.
ORM 친화적: 데이터베이스의 구체적인 테이블 이름이나 필드 이름을 몰라도 Entity와 매핑된 데이터를 처리할 수 있다.
데이터베이스 독립적 : JPQL은 SQL을 추상화해서 특정 데이터베이스 SQL에 의존하지 않는다.
1
@Query("SELECT u FROM User u WHERE u.name = :name")
여기서 User는 JPA 엔티티 이름이다.
Native SQL
SQL 그대로 사용: DB에 직접 SQL 쿼리를 실행하며 테이블 이름과 컬럼 이름을 명시적으로 사용한다.
데이터베이스 의존적: DB 고유의 SQL 문법(예: 특정 함수나 확장 기능)을 사용할 수 있다.
유연성: JPQL로 처리하기 어려운 복잡한 쿼리를 작성할 수 있다.
1
@Query(value = "SELECT * FROM user WHERE name = :name", nativeQuery = true)
여기서 user는 데이터베이스의 테이블 이름이다.
정리
| 항목 | JPQL | Native SQL |
|---|---|---|
| 기준 | Entity 기반 | 테이블 기반 |
| 유연성 | JPA와 매핑된 객체만 조회 가능 | 모든 SQL 기능 사용 가능 |
| 의존성 | 데이터베이스 독립적 | 데이터베이스 종속적 |
| 예시 | @Query("SELECT u FROM User u") | @Query(value = "SELECT * FROM user", nativeQuery = true) |
REFERENCE
ROW_NUMBER()
Order by
- [Real MySQL 8.0]
- [mysql] ORDER BY rand()
JPQL