로그에 변동량과 총액이 같이 있었다

코인 로그 테이블 설계를 봤다. 컬럼이 이랬다.

user_id
up_coin     변동된 코인 개수
tot_coin    변동 후 총 보유코인 개수
reason      변동 사유
reg_date    등록일시

변동량과 변동 후 총액이 둘 다 있어서 중복 아닌가 싶었다.

Table of contents

하나면 되지 않나

총액은 변동량을 다 더하면 나온다.

tot_coin(n) = tot_coin(n-1) + up_coin(n)

그러면 up_coin 만 있으면 tot_coin 은 계산되고 반대로 tot_coin 만 있어도 차이를 빼면 변동량이 나온다. 둘 중 하나는 없어도 될 것 같았다.

계산으로 나오는 값을 저장해 두는 것은 보통 피하는 일이다. 원본이 하나면 어긋날 자리가 없지만 두 곳에 있으면 어긋날 수 있기 때문이다.

부호가 붙어 있었다

설명에 이렇게 적혀 있었다.

코인이 늘었을 때에는 양수, 줄었을 때에는 음수가 저장된다

한 컬럼에 증감을 부호로 담는다. type 컬럼을 두고 더하기와 빼기를 나누는 방식도 있는데 여기는 부호 하나다.

이렇게 하면 합계가 그냥 나온다.

SELECT SUM(up_coin) FROM log_coin WHERE user_id = ?;

조건 없이 더하면 순증감이다. type 으로 나눴으면 이렇게 된다.

SELECT SUM(CASE WHEN type='+' THEN amount ELSE -amount END) ...

한 단계가 는다.

총액이 왜 필요한가

부호는 이해가 됐는데 tot_coin 이 여전히 걸렸다. 며칠 생각하다 몇 가지가 떠올랐다.

첫째로 그 시점의 값을 바로 안다. 총액이 없으면 그 시점 잔액을 알려고 처음부터 다 더해야 하는데 있으면 그 행만 보면 되고 로그 한 줄을 보고 이때 얼마였는지를 바로 알 수 있다.

둘째로 어긋남이 드러난다.

이전 행의 tot_coin + 이번 up_coin == 이번 tot_coin ?

맞아야 정상이고 안 맞으면 로그를 안 남기고 코인이 바뀐 것이다. 손으로 UPDATE 를 돌렸거나 로그를 안 남기는 코드 경로가 있다는 뜻이 된다. up_coin 만 있으면 이 검사가 안 된다.

그 시절 MySQL 에는 앞 행 값을 가져오는 윈도우 함수가 없어서 같은 표를 자기 자신에 조인해야 했다. 바로 앞 행을 찾아 tot_coin + up_coin 이 이번 행의 tot_coin 과 같은지를 본다.

셋째로 유저 테이블과 대조된다. 유저 테이블에도 보유 코인이 있어서 user_info.user_coin 이 log_coin 의 마지막 tot_coin 과 같은지 볼 수 있고 두 값이 어긋나면 둘 중 하나가 잘못된 것이다.

중복이 검사가 됐다

여기서 이해가 됐다. 같은 정보를 두 번 저장하는 것이 중복인데 그 덕분에 두 값이 맞는지 볼 수 있다.

중복이라서 검사가 가능하다. 한쪽만 있으면 틀렸는지 알 방법이 없다. 중복이 문제가 아니라 목적이었다.

reason 컬럼도 있었는데 이것이 있으면 이 유저의 코인이 왜 이만큼 늘었는지와 어떤 사유로 나간 코인이 얼마인지에 답할 수 있다.

SELECT reason, SUM(up_coin) FROM log_coin GROUP BY reason;

GROUP BY reason 으로 사유별 집계가 된다.

비교 — 본체에 둔 결제 총액

유저 테이블을 보니 이런 것이 있었다.

user_total_payment   유저 결제 총액

설명이 이랬다.

매 결제 시마다 결제 로그는 별도의 로그 테이블에 저장하고
결제 총액만 여기에 저장한다

로그는 따로 두고 총액은 본체에 둔 것이다. 이유가 짐작됐는데 화면에 총액을 보일 때마다 로그 전체를 SUM 하면 로그가 쌓일수록 계산이 무거워지고 미리 저장해 두면 컬럼 하나만 읽는다. 자주 보이는 값이면 미리 갖고 있는 편이 낫다.

대신 대가가 있다. 결제 처리와 로그 저장과 총액 갱신이 이어지는데 두 번 쓰는 것이라 하나만 되면 어긋난다.

SELECT u.user_id, u.user_total_payment, SUM(l.money)
  FROM user_info u JOIN log_payment l ON ...
 GROUP BY u.user_id
HAVING u.user_total_payment <> SUM(l.money);

그래서 crontab 에 걸어 주기적으로 대조하고 어긋난 유저가 나오면 그때 본다.

정리하니 같은 모양이 두 번 나왔다. 코인은 로그에 변동량과 그 시점 총액을 두고 결제는 로그에 내역을 두고 본체에 총액을 둔다. 둘 다 계산할 수 있는 값을 저장해 둔 것이고 둘 다 대조가 가능해진다.

정리

• 로그에 up_coin 과 tot_coin 이 둘 다 있었다
• SUM(up_coin) 으로 나오는 값이라 중복처럼 보인다
• 그래서 대조가 되고 중복이 목적이 된다
• 앞 행의 tot_coin + up_coin 이 이번 행의 tot_coin 이어야 한다
• 윈도우 함수가 없던 판이라 같은 표를 자기 자신에 조인해 확인했다
• 안 맞으면 로그 없이 UPDATE 가 돌았다는 뜻이다
• 부호 하나로 담으면 SUM(up_coin) 이 곧 순증감이다
• reason 컬럼이 있으면 GROUP BY reason 집계가 된다
• 자주 보이는 총액은 본체에 미리 두는 편이 가볍다
• 대신 두 곳에 쓰니 crontab 으로 주기적으로 대조해야 한다