한빛출판네트워크

제공 : 한빛 네트워크
저자 : Baron Schwartz
역자 : 노재현
원문 : How to Optimize Rank Data in MySQL

랭킹 컬럼을 추가하는 방법

랭킹 정보를 가지고 있는 Column 사실 일종의 캐시라고 봐야 한다. 이렇게 랭킹 정보를 저장하게 되면 검색을 할때 보다 빠르게 할 수 있게 된다. 하지만 이 방법에도 안 좋은 점이 있다. 바로 업데이트를 빠르고 부하가 가지 않도록 할 수 있는 방법이 없다면 랭킹 테이블을 관리하는데 상당히 힘들어지게 된다. 필자는 여기서 테이블을 하나 더 만들어서 인덱스를 더 크게 만들고 SELECT Query에서 하고 있던 일을 다른 곳으로 옮겨서 부하를 더 줄여보고자 한다. 이렇게 하는 것이 데이터의 변경이 자주 발생하지 않는 상황에서는 아주 좋은 방법이 될 수 있다. 필자가 초반에 말했듯이 우리가 구성하고자 하는 사이트는 업데이트가 자주 발생하게 될 것이기 때문에 이 방법을 사용 가능하게 만들기 위해서 빠르게 업데이트 할 수 있는 방법이 꼭 있어야만 한다.

다행스럽게도 방법은 있다. 두 가지 방법을 이용하게 되는데, 하나는 MySQL의 유저 변수를 이용해서 쓰기 커서를 시뮬레이션함으로써 랭킹을 한 번에 계산해 내는 것이다. 그리고 두 번째 방법은 스마트 통합 업데이트라는 것을 이용한다. 데이터의 업데이트를 하기전에 이 업데이트가 얼마나 큰 부하를 가져오는지를 계산을 해서 빠르게 업데이트 할 수 있는 경우에만 바로 업데이트를 실행하는 것이다.

다음은 필자가 이전의 Query에서 사용한 rank_in_game 이라는 Column을 추가할 때 사용한 방법이다.

drop table if exists score_ranked;

create table score_ranked like score;

alter table score_ranked add rank_in_game int not null default 0,
   add key(game, rank_in_game);

여기서는 기존의 테이블을 수정하기 보다는 새로운 테이블을 하나 만들어 내고 있다. 이렇게 하면 나중에 수정된 테이블과 수정되기전의 테이블을 가지고 벤치마크를 할 수가 있다.

이제 테이블에 데이터를 넣을 차례이다. 여기서 바로 위에서 말한 MySQL의 유저 변수를 사용해서 데이터를 넣으면서 랭킹을 계산하는 방법을 보게 될 것이다.

set @rank := 0, @game := null, @score := null;

insert into score_ranked(gamer, game, score, rank_in_game)
select gamer, game, score,
   greatest(
      @rank := if(@game = game and @score = score, @rank, if(@game <> game, 1, @rank + 1)),
      least(0, @score := score),
      least(0, @game  := game)) as rank
from score order by game desc, score desc;

좀 이해하기 어려울 것이지만 걱정하지 말자. 이제 곧 설명할 것이다.

유저 변수 값을 가진 쿼리가 작동하는 방식

MySQL의 유저 변수는 변경과 사용이 동시에 가능하기 때문에 이런 기능을 이용하면 무언가를 만들어낼때 정말 유용하게 사용될 수가 있다. 물론 여기서 간단하게 설명을 하겠지만, 필자의 웹사이트에 MySQL 유저 변수에 관한 글을 몇 개 올려놓았으니 참고하는데 도움이 되었으면 좋겠다. 그럼 우선 Query가 어떻게 작동하는지부터 살펴보도록 하자.

select gamer, game, score,
   greatest(
      -- 해당 row의 랭킹 값을 찾는다.
      @rank := if(@game = game and @score = score, @rank, if(@game <> game, 1, @rank + 1)),
      -- Save score and game for next row
      least(0, @score := score),
      least(0, @game  := game)) as rank
from score order by game desc, score desc;

GREATEST() 함수는 우선 생각하지 말고 안쪽부터 하나하나 보도록 하자. 우선 유저 변수 선언문은 모든 row에 대해서 실행되게 된다. 즉, 모든 결과를 모아놓고 실행하는 것이 아니고 각 row에 대해서 따로따로 생성될 때 실행을 하게 된다.

다음으로 이 Query는 score 테이블에서 모든 row를 랭킹 순으로 읽어들이게 된다. 당연히 이 Query가 정상적인 결과를 생성해내기 위해서는 row들이 순서대로 잘 차례차례 생성되어야 한다.

Query안에 있는 @rank 변수는 1부터 시작해서 매번 row를 검사할 때 마다 이전 @rank 변수의 값과 현재 읽어온 row의 rank 값을 비교해서 다를 경우 @rank 변수의 값을 1만큼 증가시켜 주고 있다. 그리고 각 row 마다 현재 게임과 점수가 같은지를 확인하고 있다. 게임도 같고 점수도 같을 경우 @rank 변수의 값은 증가시키지 않아도 되기 때문에 그대로 놔두게 되고, 게임이 변경된 경우에는 @rank 변수의 값을 1로 초기화 시키고 있다. 그리고 게임은 같지만 점수가 다를 경우에는 @rank 변수의 값을 1만큼 증가시켜 주고 있는 것을 볼 수 있다.

현재 row의 랭킹 값을 알아낸 후에는 이 랭킹값을 다음 row의 값과 비교하기 위해서 저장해 놓아야 하는데, 이 값을 select 리스트에 있는 column 값에 저장할 수는 없다. 우리의 테이블에는 이 값을 저장하기 위한 column이 없기때문이고, 그래서 여기서는 LEAST(0, ...) 함수안에 넣게 되었다. 그리고 이 모든 것을 GREATEST() 함수안에 넣었다. 이렇게 하면 @score와 @rank 값 지정문이 정상적으로 호출이 되고 사용이 끝나면 소거되게 된다.

GREATEST() 함수는 새로 계산된 @rank 변수 값을 리턴하게 된다.

랭킹 컬럼의 값을 관리하는 방법

여기까지 rank column을 초기화하는 부분에 대해서 설명을 했는데, 이제는 UPDATE 명령을 이용해서 score 테이블의 rank column의 값을 업데이트하는 것에 대해서 설명을 하겠다. 여기서도 이전과 마찬가지로 MySQL의 유저 변수 값을 이용할 수가 있다. 보통 UPDATE 명령에 있는 SET 절에 유저 변수를 바로 사용할 수는 없지만 함수를 이용하면 가능하게 된다.

set @rank := 0, @pos := 0, @game := null, @score := null;

update score_ranked
set rank_in_game = 
   greatest(
      @rank := if(@game = game and @score = score, @rank, if(@game <> game, 1, @rank + 1)),
      least(0, @pos   := if(@game = game, @pos + 1, 1)),
      least(0, @score := score),
      least(0, @game  := game)),
   pos_in_game = @pos
order by game desc, score desc;

이 Query를 수행하게 되면 테이블의 모든 row에 업데이트 명령을 수행하게 된다. 모든 row를 업데이트 하고 싶은 경우에 이 Query를 사용하면 좋을 것이다.

다른 DB를 사용해본 독자라면 이런 명령을 DB에 있는 커서를 이용해서 해본 독자도 있을 것이라고 생각한다. MySQL 5 같은 경우 커서를 지원하기는 하지만 읽기 전용이라 이런 용도로 사용할 수는 없다. 위의 방법은 바로 쓰기 커서를 시뮬레이션 한 것이라고 보면 된다.

업데이트 시나리오

위의 방법은 좀 비효율적이다. 모든 row를 매번 업데이트 할 필요는 없지 않은가. 그래서 여기서는 각각 최적화된 네가지 시나리오를 제시해 보고자 한다.

시나리오 1. 가장 좋은 경우. 이 경우는 insert 하거나 delete 하려는 데이터가 다른 데이터에 영향을 끼치지 않는 경우이다. 예를 들어, 1번 게임의 1010 번 유저의 점수를 삭제하려고 한다고 하자. 테이블에 1010 번 유저와 동일한 점수를 가진 유저가 있다면 다른 랭킹 정보를 업데이트 하지 않아도 아무 문제가 없게 된다. 물론 새로운 데이터를 넣으려고 하는 경우에도 같은 점수를 가진 유저가 테이블에 이미 존재했었다면 다른 데이터를 변경시키지 않아도 괜찮다.

시나리오 2. 한 개의 row만 업데이트가 필요한 경우. 예를 들어, 1번 게임의 1010번 유저의 점수가 97084로 줄었다고 하자. 이 때 이미 같은 점수를 가진 유저가 존재한다면 랭킹 정보를 업데이트 할 필요가 없다. 단지 이동한 1010번 유저의 랭킹만 업데이트를 해주면 된다. 또 다른 예로, 점수가 오르거나 줄어들었지만 해당 유저의 위 혹은 아래에 있는 유저의 점수를 벗어나지 않은 경우이다. 예를 들어 아래 테이블을 보도록 하자. 1번 게임의 랭킹 291 ~ 294를 보자. 만약에 2805번 유저의 점수가 97070으로 오른다고 할때, 다른 row에는 아무런 영향을 끼치지 못하게 된다. 왜냐하면 2805번 유저의 랭킹은 여전히 294가 되게 될 것이기 때문이다.

gamer	game	score	rank_in_game
9094	1	97084	291
7462	1	97076	292
4839	1	97075	293
2805	1	97062	294

시나리오 3. 일정 범위의 row 들이 업데이트가 되어야 하는 경우. 위의 테이블에서 4839번 유저의 점수가 97080으로 변경되었다고 하자. 그럼 4938번 유저는 랭킹이 292가 되게 되고, 7462번 유저는 한단계 아래의 랭킹인 293이 되게 된다. 고로 2개의 row가 영향을 받게 된다. 만약에 2805번 유저의 점수가 97078이 되었다고 하자. 이번에는 7462번, 4839번 유저의 랭킹이 한 단계씩 아래로 떨어져야 하고 3개의 row가 영향을 받게 된다. 하지만 모든 row를 업데이트하는것에 비하면 변화가 국지적인것을 알 수 있다.

마지막으로 시나리오 4. 업데이트 되는 row보다 점수가 낮은 모든 row들이 영향을 받게 되는 경우. 1010번 유저의 점수가 97102로 업데이트가 되었다고 했을때 여전히 290번째 위치에 있게 되지만, 1010번 유저보다 점수가 낮은 모든 유저들의 랭킹을 1만큼 떨어트려서 업데이트를 해주어야 한다. 최악의 경우 테이블 내의 모든 데이터를 업데이트 해야할 수도 있다.

각 시나리오에 해당하는 경우를 구분하는 방법은 어렵지 않다. 다음의 inserts, deletes, updates Query에 해당하는 간단한 Query가 몇 개 필요할 뿐이다. 첫 번째 Query 문이 수행해야하는 작업이 부하가 큰 작업이라고 판단하게 되면 다른 Query 문을 이용해서 시나리오에 따른 각각의 작업을 수행할 수 있게 된다. 예를 들어, 다음 Query 문은 1번 게임에 97101의 점수를 가진 유저를 넣는다고 할때 이 작업이 부하가 큰 작업인지 아닌지를 구분하게 된다.

select count(*) as cheap,
   coalesce(
      avg(rank_in_game),
      (
         select rank_in_game
         from score_ranked use index(score)
         where game = 1 and score < 97101
         order by score desc limit 1
      )
   ) as rank
from score_ranked where game = 1 and score = 97101;

Query가 부하가 크지 않은 작업일 경우에 select 문에 있는 cheap에 0이 아닌 값을 리턴하게 되어 있다. cheap의 값이 0이 나오게 되는 경우에는 서브 Query가 해당 row를 넣어야 하는 범위를 알려주게 되는데, 이 값을 보고 부하가 큰 작업일지 아닐지를 판단하는 것은 바로 여러분의 몫이 된다.(통계적인 방법을 이용할 수 있을지도 모른다) 대부분의 삽입 명령어는 신규 유저의 유입으로 발생되는 명령이라는 것을 감안할 때 삽입해야 하는 데이터의 점수가 낮다면 랭킹 테이블의 끝 부분에 들어갈 확률이 높을 것이고, 이런 경우에는 일부의 row 들만 업데이트 함으로써 랭킹 테이블의 데이터를 유지할 수 있다.

유저 변수가 없는 일반 Query로도 위의 랭킹 데이터를 유지할 수가 있다. 예를 들어서, 위의 테이블에서 2805번 유저의 점수를 97085로 업데이트 하게 될 경우 세 명의 유저의 점수도 같이 업데이트를 시켜줘야만 하게 된다. 이 업데이트는 다음과 같은 Query로 할 수가 있다.

update score_ranked
   set score = if(gamer = 2805, 97085, score),
       rank_in_game = if(gamer = 2805, 291, rank_in_game + 1)
where game = 1 and rank_in_game between 291 and 294;

이와 마찬가지로 다른 경우에 대한 Query 또한 쉽게 작성할 수가 있다. 이런 최적화의 경우에 단일 row의 업데이트일 경우에 최적의 효과를 내지만, 몇몇 Query들은 테이블의 많은 영역을 업데이트 해야할 만큼 부하가 클 수도 있다. 어쩌면 테이블 전체의 업데이트를 해야할 수도 있는데 이런 경우에는 실시간으로 업데이트를 하는 것보다는 모아 놓았다 배치 업데이트를 하는 것이 효율적이다. 여기서 유저 변수를 포함한 Query를 사용하게 되면 부하가 큰 Query들을 배치작업으로 모아놓는 작업도 어렵지 않게 할 수 있지만, 유저 변수를 사용하지 않고 하게 되면 몇몇 Query들을 위의 설명과 같이 느린 연산을 통해서 해야만 하게 될 것이다.

---

역자 노재현님은 어렸을 때부터 컴퓨터를 접하게 된 덕에 프로그래밍을 오랫동안 정겹게 하고 있는 프로그래머 입니다. 특히나 게임 및 OS 개발에 관심이 많으며, 심심할 때면 뭔가 새로운 프로그램을 만들어내는 것을 좋아합니다. 다음에서 웹 관련 개발을 한 후에 현재는 www.osguru.net이라는 OS관련 웹사이트를 운영하며 넥슨에서 게임 개발을 하고 있습니다.
* e-mail: wonbear@gmail.com
* homepage: http://www.oguru.net

---