Quiz 조회하기

오답 퀴즈 랜덤으로 뽑기

사용자의 오답(correct = false) 중에서 10개를 랜덤으로 뽑아 보여주는 기능이 필요했다.

처음엔 단순하게 ORDER BY RAND()를 썼다.

SELECT * FROM quiz
WHERE correct = false AND user_id = :userId
ORDER BY RAND() LIMIT 10

그런데 데이터가 많아지면 ORDER BY RAND()가 느려진다는 이야기를 많이 봤다.

그래서 ROW_NUMBER()로 바꿔봤다.

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

조건에 맞는 행에 순번(r)을 매기고, mod(r, 1~3난수) = 0으로 걸러 10개를 가져오는 방식이다.

System.nanoTime()

처음엔 System.nanoTime()으로 한 번씩 재서 비교했다.

long start = System.nanoTime();
List<Quiz> quizByInCorrect = quizRepository.findQuizByInCorrect(user.getId());
long end = System.nanoTime();
log.info("시간 소요 1 : {} ", (end - start));

long start1 = System.nanoTime();
List<Quiz> randomQuiz = quizRepository.findRandomQuiz(user.getId());
long end1 = System.nanoTime();
log.info("시간 소요 2 : {} ", (end1 - start1));

그때 나온 숫자는 이랬다.

Test Case	ORDER BY RAND	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개)

이 숫자로 “평균 6개 조회에 ORDER BY RAND()는 0.085초, ROW_NUMBER()는 0.018초, 약 4.7배 빠르다”고 정리했었다.

그런데 행이 겨우 6개인데 조회에 0.08초(80ms)가 걸린다는 건 말이 안 된다.

이건 쿼리가 느린 게 아니라, 첫 호출 때의 JIT 워밍업·커넥션 초기화 같은 잡음을 같이 잰 거였다.

실행마다 조회 개수도 5~10개로 들쭉날쭉했다. 한 번씩 잰 숫자로는 아무것도 신뢰할 수 없었다.

그래서 JMH로 다시 측정했다.

JMH로 다시 측정

실제 quiz 테이블은 건드리지 않으려고

벤치마크 전용 quiz_bench 테이블을 만들어 오답 데이터를 100 / 1,000 / 10,000 / 100,000개씩 채운 뒤 측정했다. (측정 후 테이블은 삭제한다.)

비교 대상은 세 가지다.

1. ORDER BY RAND() — 개선 전
2. ROW_NUMBER() + mod(r, rand()) — 위에서 개선했다고 생각한 방식
3. count 후 Java에서 순번을 뽑아 r IN (...) — SQL 대신 Java에서 난수를 뽑는 방식

측정 조건은 워밍업 3회, 측정 6회, fork 2회, 평균 실행 시간(AverageTime, ms/op)이다.

@BenchmarkMode(Mode.AverageTime)
@OutputTimeUnit(TimeUnit.MILLISECONDS)
@Warmup(iterations = 3, time = 1)
@Measurement(iterations = 6, time = 1)
@Fork(2)
public class QuizQueryBenchmark { ... }

결과는 이렇게 나왔다. (낮을수록 빠르다. ± 는 오차 범위)

오답 개수	① ORDER BY RAND()	② ROW_NUMBER + mod	③ count + IN (Java)
100	0.54 ± 0.13 ms	0.31 ± 0.04 ms	1.65 ± 1.09 ms
1,000	1.15 ± 0.07 ms	0.78 ± 0.07 ms	2.92 ± 0.43 ms
10,000	8.15 ± 4.10 ms	10.54 ± 0.82 ms	26.21 ± 2.19 ms
100,000	49.37 ± 6.16 ms	87.69 ± 14.44 ms	167.73 ± 5.23 ms

알게 된 것

• 데이터가 작을 때(100~1,000개)는 ROW_NUMBER()가 ORDER BY RAND()보다 조금 빠르긴 했다. 1.5배 정도

• 오히려 데이터가 커지면(10,000개 이상) ORDER BY RAND()가 더 빨랐다.

  100,000개에서는 49ms vs 88ms로 ROW_NUMBER() 방식이 거의 두 배 느렸다.

  서브쿼리로 전체에 순번을 매기는 비용이 커지기 때문인 것 같다.

• Java에서 순번을 뽑는 ③번 방식은 모든 구간에서 제일 느렸다.

  쿼리를 두 번 날리는 데다(count + IN), 1 ~ total 리스트를 통째로 만들어 섞기 때문이다. 100,000개에서 168ms까지 갔다.

결론

지금 이 앱의 오답 개수는 사용자당 많아야 수십 개 수준이다.

그 규모에서는 세 방식 모두 1ms 안팎이라 성능 차이가 사실상 의미가 없다.

그래서 성능이 아니라 정확성·예측 가능성을 기준으로 골랐다.

• ORDER BY RAND()는 쿼리 한 줄로 끝나고, 이 규모에서 제일 단순하면서 느리지도 않다.

• Java에서 순번을 뽑는 방식은 “항상 10개(부족하면 전부) + 확실히 섞임”을 보장할 수 있다는 장점이 있다.

  대신 데이터가 커지면 1 ~ total 리스트를 만드는 비용이 붙으니, 오답이 아주 많아질 앱이라면 이 부분은 다시 봐야 한다.

+) JPQL vs Native SQL

ROW_NUMBER()나 RAND() 같은 함수는 JPQL로는 표현할 수 없어서 Native SQL(nativeQuery = true)로 작성했다.

• JPQL: 엔티티를 대상으로 쓰고, DB에 종속되지 않는다.

• Native SQL : 실제 테이블을 대상으로 쓰고, DB에 종속된다. 대신 그 DB의 함수를 그대로 쓸 수 있다.