GameUniv 사이트 기능 중에 현재 내 점수에 해당하는 랭킹을 가져와야 하는 기능이 있다.
점수 - 유저 기록 테이블에서 user별로 group by를 하고 rank함수를 사용해서 랭킹을 가져오는 것으로 기능을 구현한 줄 알았다.
문제는 바로 group by를 하고 RANK함수를 사용하는 쿼리가 너무너무너무 느리다는 것이였다.
SELECT
MAX(id) AS pre_id,
MAX(university_name) AS pre_university_name,
MAX(game_score) AS pre_max_score
FROM
game_2048_record_tb
JOIN
user_tb
ON
user_email = user_tb.email
JOIN
university_tb
ON
user_tb.university_idx = university_tb.university_idx
WHERE
EXTRACT(MONTH FROM game_2048_record_tb.creation_time) = EXTRACT(MONTH FROM NOW())
AND
EXTRACT(YEAR FROM game_2048_record_tb.creation_time) = EXTRACT(YEAR FROM NOW())
AND
user_tb.is_delete IS NULL
GROUP BY
user_email
HAVING
MAX(game_score) < $1
ORDER BY
MAX(game_score) DESC
SELECT
RANK() OVER ( ORDER BY MAX(game_score) DESC) AS rank
FROM
game_2048_record_tb
WHERE
EXTRACT(MONTH FROM creation_time) = EXTRACT(MONTH FROM NOW())
AND
EXTRACT(YEAR FROM creation_time) = EXTRACT(YEAR FROM NOW())
AND
(
SELECT
is_delete
WHERE
game_2048_record_tb.user_email = user_tb.email
) IS NULL
GROUP BY
user_email
HAVING
MAX(game_score) >= $1
데이터 4만 개 정도 넣고 테스트를 돌려보니 너무 느렸다. 정...말... 너무 나도 느렸다. 해결 방법은 간단했다.
GROUP BY를 사용하지 않도록 사용자의 최대 점수를 담아 놓는 테이블을 만든다. 이 테이블의 점수 컬럼을 BTree 자료구조로 인덱싱을 해둔다.
SELECT
CAST(RANK() OVER ( ORDER BY game_score DESC) AS int) AS pre_rank,
game_score pre_max_score,
university_name AS pre_university_name,
user_name AS pre_user_name
FROM
${tableName}
JOIN
user_tb
ON
user_email = email
JOIN
university_tb
ON
university_tb.university_idx = user_tb.university_idx
WHERE
game_score > $1
AND
user_tb.is_delete IS NULL
ORDER BY
pre_rank DESC
LIMIT
1
테이블 구조를 변경하고 인덱싱을 걸었더니 훨씬 빨라졌다.
인덱싱을 적극적으로 이용하자.