순위를 빠르게 보여줘야 했다

게임에 순위표를 넣어 상위 100명과 내 순위를 보여 줬다.

Open Table of contents

내 순위가 느렸다

상위 100명은 InnoDB 테이블의 score 에 인덱스를 걸어 두니 금방 나왔다. ORDER BY score DESC LIMIT 100 이 인덱스를 역순으로 훑고 끝난다. 내 순위가 문제였다.

SELECT COUNT(*) + 1 FROM scores WHERE score > ?;

EXPLAIN 을 걸어 보니 인덱스는 타는데 훑는 행이 많았다. 나보다 높은 사람을 전부 세기 때문이다. 상위권은 셀 것이 적고 하위권은 거의 전부를 세므로 낮은 사람일수록 느렸다. 사용자가 늘수록 더 느려진다.

순위를 미리 계산해 저장할까 했는데 한 명의 점수가 바뀌면 그 아래가 전부 밀린다. 게임이라 점수가 계속 바뀌니 매번 전부 갱신할 수는 없었다.

점수 구간별 인원을 담는 표를 따로 뒀다. 내 순위는 위 구간들의 합에 내 구간 안에서 나보다 높은 사람을 더한 값이다. 위 구간은 미리 계산돼 있고 내 구간만 COUNT(*) 로 센다.

구간 크기가 관건이었다. 크게 나누면 구간 안에서 셀 것이 많고 작게 나누면 구간이 많아진다. 점수 분포를 보니 낮은 쪽에 몰려 있었다. 균등하게 안 나누고 낮은 쪽을 촘촘하게 잡았다. 높은 쪽은 넓게 뒀다. 각 구간의 인원이 비슷해지게 하는 것이 목표였다.

구간별 인원은 iBatis 에 배치용 쿼리를 하나 두고 주기적으로 다시 셌다. 실시간으로 갱신하면 부담이고 몇 분 늦어도 문제가 안 됐다.

몇 분 늦어도 되는 것은 화면 표시였다. 보상을 주는 순위는 정확해야 한다.

진행 중에는 근사 순위를 보여 준다. 기간이 끝난 뒤 한 번 정확히 계산해 지급한다. 진행 중에 정확할 필요가 없다는 것을 확인하고 나니 구조가 정해졌다.

화면에는 실시간과 차이가 있을 수 있다는 안내를 넣었다. 근사값을 보여 주면서 정확하다고 말하지 않는 것이 필요했다.

같은 점수인 사람이 여럿이면 순서가 정해지지 않는 문제도 있었다.

ORDER BY score DESC, achieved_at ASC, user_id ASC

먼저 도달한 사람이 앞에 오게 하고 시각까지 같으면 user_id 로 갈랐다. 마지막 자리가 기본 키라 값이 겹치지 않으니 항상 하나로 정해진다.

정해 두지 않으면 MySQL 이 어떤 순서로 주든 규격 위반이 아니다. 조회할 때마다 다를 수 있다. 같은 조회에 같은 결과가 나오는 것이 순위표에서는 특히 중요했다.