점검 SQL: “원장 합계(SUM) vs balance” 불일치를 찾아내고 원인을 좁히는 방법

포인트 시스템은 “정상 처리”만 잘 만들면 끝나는 것처럼 보이지만, 실제로는 시간이 지날수록 다양한 이유로 데이터가 어긋날 수 있습니다. 예를 들어 이런 상황이 생깁니다.

  • 중간 장애로 트랜잭션이 일부만 반영된 것처럼 보이는 케이스(재처리/수동 개입 포함)
  • 운영 정정(ADJUST)이나 소프트 삭제(hide) 처리 기준이 흔들린 케이스
  • 배치 작업(만료/정산)이 예상과 다른 시간에 들어간 케이스
  • 코드 변경 이후 특정 action_type이 누락/중복 집계된 케이스

그래서 “점검 쿼리”는 기능 쿼리만큼 중요합니다. 특히 balance 테이블(잔액 캐시)을 함께 쓰는 구조라면, 주기적으로 원장 합계와 balance가 같은지 확인해야 합니다.

이 단원의 목적: 불일치를 “발견”하고 “원인”을 좁힐 수 있는 쿼리 세트 만들기

이번 글에서는 아래 3가지를 목표로 합니다.

  1. 원장 합계(SUM)와 balance를 비교해 불일치 UID를 뽑는다
  2. 불일치가 발견되면 어디서부터 어긋났는지 범위를 좁힌다
  3. 자주 발생하는 유형(숨김 처리, 중복 요청, 배치 만료)을 점검 항목으로 만든다

1) 1차 점검: uid별 “원장 잔액”과 “balance 잔액” 비교

가장 기본은 uid별로 두 값을 나란히 놓고 비교하는 것입니다. 여기서는 hide=0만 원장 합계에 포함한다고 가정합니다. 

SELECT b.uid, b.balance AS balance_cache, COALESCE(l.ledger_balance, 0) AS balance_ledger, b.balance - COALESCE(l.ledger_balance, 0) AS diff FROM point_balance b LEFT JOIN ( SELECT uid, SUM(amount) AS ledger_balance FROM point_history WHERE hide = 0 GROUP BY uid ) l ON l.uid = b.uid WHERE b.balance <> COALESCE(l.ledger_balance, 0) ORDER BY ABS(b.balance - COALESCE(l.ledger_balance, 0)) DESC;

이 쿼리의 결과가 “불일치 목록”입니다. diff가 0이면 일치, 0이 아니면 어긋난 것입니다. 운영에서는 diff의 절댓값이 큰 것부터 보는 편이 효율적입니다.

2) 반대 방향도 확인: 원장에는 있는데 balance에 없는 uid

point_balance가 “모든 사용자”를 보장하지 않는 설계라면, 원장에는 있지만 balance에 없는 사용자가 생길 수 있습니다. 이런 케이스를 따로 보고 싶다면 기준 테이블을 바꿔야 합니다. 

SELECT l.uid, l.ledger_balance, b.balance AS balance_cache FROM ( SELECT uid, SUM(amount) AS ledger_balance FROM point_history WHERE hide = 0 GROUP BY uid ) l LEFT JOIN point_balance b ON b.uid = l.uid WHERE b.uid IS NULL ORDER BY ABS(l.ledger_balance) DESC;

이 결과는 “balance가 생성되지 않은 사용자”입니다. 정책상 정상일 수도 있고(잔액 캐시를 일부 사용자만 운영), 버그일 수도 있습니다(가입 시 balance row를 만들기로 했는데 누락).

3) 2차 점검: 불일치 uid 하나를 잡고 “언제부터” 틀어졌는지 좁히기

불일치 목록을 뽑았으면, 다음은 범위를 줄이는 단계입니다. 가장 빠른 방법은 “최근 이벤트부터” 역추적하는 것입니다.

(1) 최근 원장 내역 50건 확인

SET @uid := '11111111-1111-1111-1111-111111111111';

SELECT id, action_type, amount, ref_id, memo, hide, created_at
FROM point_history
WHERE uid = @uid
ORDER BY created_at DESC, id DESC
LIMIT 50;

(2) balance 값과 원장 합계를 동시에 확인

SELECT balance, updated_at FROM point_balance WHERE uid = @uid;

SELECT SUM(amount) AS ledger_balance
FROM point_history
WHERE uid = @uid AND hide = 0;

여기서 “최근 내역에는 정상처럼 보이는데 diff가 크다”면, 과거 어딘가에서 누락/중복이 누적됐을 가능성이 큽니다. 이런 경우는 기간을 나눠서 합계를 비교하면 범위를 빨리 좁힐 수 있습니다.

4) 기간 분할 점검: 월별/주별 누적 합계를 비교해서 분기점 찾기

불일치가 “언제부터 시작됐는지”를 찾는 실전 방법은 기간별 합계를 보고 이상한 구간을 찾는 것입니다. 여기서는 월별 순변동(net_delta)을 구해봅니다. 

SELECT DATE_FORMAT(created_at, '%Y-%m') AS ym, SUM(CASE WHEN hide = 0 THEN amount ELSE 0 END) AS net_delta, COUNT(*) AS rows_cnt FROM point_history WHERE uid = @uid GROUP BY DATE_FORMAT(created_at, '%Y-%m') ORDER BY ym ASC;

특정 달부터 숫자가 튀면, 그 달의 데이터(배치/정정/중복)를 집중적으로 보면 됩니다. 더 세밀하게 보고 싶다면 주별/일별로 버킷을 줄이면 됩니다.

5) 자주 나오는 원인 1: hide 처리 기준이 집계/갱신과 다르다

가장 흔한 불일치 유형 중 하나는 “원장 합계는 hide=0만 보는데, balance 갱신 로직에서는 hide를 고려하지 않았다” 같은 기준 불일치입니다.

hide=1인 내역이 많아졌다면, 아래처럼 규모를 확인해볼 수 있습니다. 

SELECT SUM(CASE WHEN hide = 1 THEN amount ELSE 0 END) AS hidden_amount_sum, COUNT(CASE WHEN hide = 1 THEN 1 END) AS hidden_rows FROM point_history WHERE uid = @uid;

숨김 데이터가 존재한다면, “숨김 처리 시 balance도 같이 보정했는지”를 확인해야 합니다. 원장만 숨기고 balance를 그대로 두면 diff가 생기는 것은 자연스럽습니다.

6) 자주 나오는 원인 2: 중복 요청/재시도에 대한 처리 누락

요청 선점(point_requests)을 도입했다면, 원장에 기록된 내용과 요청 상태가 일관적인지 확인할 수 있습니다. 예를 들어 DONE인데 원장이 없다거나, 원장은 있는데 요청 상태가 RESERVED로 남아있다면, 중간 장애/재처리 과정에서 꼬였을 가능성이 있습니다.

요청 상태별 개수 확인(최근 7일)

SELECT status, COUNT(*) AS cnt FROM point_requests WHERE uid = @uid AND created_at >= NOW() - INTERVAL 7 DAY GROUP BY status ORDER BY cnt DESC;

RESERVED가 오래 남아 있는 요청 찾기

SELECT request_id, request_type, amount, ref_id, status, created_at, updated_at FROM point_requests WHERE status = 'RESERVED' AND updated_at < NOW() - INTERVAL 10 MINUTE ORDER BY updated_at ASC LIMIT 100;

RESERVED가 오래 남아 있으면 “중간 장애” 가능성이 큽니다. 이런 건을 어떤 정책으로 정리할지(FAILED 전환, 재처리, 수동 확인)는 시스템마다 다르지만, 최소한 “남아 있는 건이 있는지”를 보는 쿼리는 갖고 있어야 합니다.

7) 자주 나오는 원인 3: 만료 배치(EXPIRE) 중복/누락

만료는 배치로 들어오고, 배치 로직은 종종 “한 번 더 실행되는” 상황을 겪습니다. 그래서 EXPIRE가 특정 ref_id(배치 실행 단위)로 들어간다면, 같은 ref_id가 중복으로 실행됐는지 확인할 수 있습니다. 

SELECT ref_id, COUNT(*) AS cnt, -SUM(amount) AS expire_amount FROM point_history WHERE action_type = 'EXPIRE' AND hide = 0 AND created_at >= NOW() - INTERVAL 30 DAY GROUP BY ref_id HAVING COUNT(*) > 1 ORDER BY cnt DESC;

이 쿼리는 “같은 ref_id로 EXPIRE가 여러 번 찍힌” 후보를 보여줍니다. ref_id를 배치 실행 단위로 설계해두면 이런 점검이 훨씬 쉬워집니다.

8) 불일치가 발견됐을 때 “복구”를 바로 하지 말고, 먼저 기준을 고정한다

diff가 발견되면 가장 먼저 해야 할 일은 “누가 기준인가”를 고정하는 것입니다. 보통은 원장이 기준이고(balance는 캐시), 원장을 기준으로 balance를 재생성하는 방향이 자연스럽습니다. 다만 시스템에 따라 원장을 일부 숨기거나 정정하는 정책이 있을 수 있으니, “원장 합계는 hide=0 기준” 같은 집계 기준을 먼저 확정해야 합니다.

이 기준이 흔들리면 점검 쿼리는 계속 결과가 바뀌고, 복구 작업도 오히려 데이터를 더 어지럽힐 수 있습니다.

다음 글 예고: 인덱스/실행계획(EXPLAIN)으로 점검 쿼리까지 빠르게 만들기

점검 쿼리는 데이터가 커질수록 느려지기 쉽습니다. 특히 uid별 SUM, 최근 N일 집계, ref_id 그룹핑은 원장 규모에 따라 비용이 크게 달라집니다. 다음 글에서는 EXPLAIN으로 실행 계획을 보고, 어떤 인덱스가 점검 쿼리에 직접적인 효과가 있는지, 그리고 “점검용 쿼리는 어떤 형태로 쪼개야 안전한지”를 정리합니다.

댓글

댓글 쓰기

이 블로그의 인기 게시물

JOIN 기초: users와 point_history를 합쳐 ‘회원별 요약(잔액/최근 활동)’ 만들기

포인트 원장(point_history)만으로도 잔액과 통계를 만들 수 있지만, 실제 화면이나 운영 도구에서는 거의 항상 “사용자 정보”가 함께 필요합니다. 예를 들어 이런 질문들은 point_history만으로는 답이 부족합니다. 이 uid가 누구인지(가입일, 상태 등) 함께 보고 싶다 회원 목록을 보면서 각 회원의 잔액/최근 활동을 한 줄로 요약하고 싶다 최근 7일 동안 사용이 많은 회원을 “회원 정보와 함께” 추려 보고 싶다 이때 필요한 것이 JOIN입니다. JOIN은 테이블을 “합치는” 기능이지만, 정확히는 두 테이블의 행을 연결 해 새로운 결과를 만드는 방식입니다. 이번 글에서는 포인트 시스템에서 가장 흔한 JOIN 패턴(회원 + 원장 요약)을 중심으로, INNER JOIN/LEFT JOIN의 차이와 실전에서 헷갈리는 포인트를 정리합니다. 이 단원의 목적: “회원 목록 + 포인트 요약”을 안정적으로 뽑아내기 JOIN은 문법을 외우는 것보다 “어떤 결과를 만들고 싶은지”를 먼저 그리는 것이 중요합니다. 이번 글의 목표는 아래 3가지입니다. INNER JOIN과 LEFT JOIN의 차이를 이해하고 상황에 맞게 선택한다 집계(GROUP BY)와 JOIN을 결합해 ‘회원별 잔액/통계’를 만든다 JOIN에서 자주 발생하는 오류(행 폭증, 중복 집계)를 피하는 패턴을 익힌다 준비: users에 최소한의 컬럼을 더해 “JOIN 결과가 읽히게” 만들기 이전 글에서는 users가 uid와 created_at만 있었지만, JOIN 결과를 보기 편하게 하려면 display_name 같은 컬럼이 하나 있으면 좋습니다. 실제 서비스라면 이메일/닉네임/상태값이 있을 텐데, 여기서는 학습 목적상 간단히 display_name을 추가합니다. -- 이미 있다면 생략 ALTER TABLE users ADD COLUMN display_name VARCHAR(50) NULL; -- 예시 값 입력(학습용) UPDATE users SET display_n...
자세한 내용 보기

집계 기초: SUM/COUNT로 잔액·기간별 적립/사용 리포트 만들기

포인트 시스템의 “진짜 쓸모”는 원장(point_history)에 기록된 사실을 사람이 이해할 수 있는 형태로 요약하는 데서 나옵니다. 화면에서는 잔액 한 줄로 보이지만, 그 뒤에는 적립/사용/만료/취소가 뒤섞인 수많은 기록이 존재합니다. 그래서 포인트 시스템에서 SQL의 핵심은 단순 조회(SELECT)보다 집계(aggregation) 입니다. 집계는 원장 데이터를 합치고(SUM), 개수를 세고(COUNT), 기준별로 묶어서(GROUP BY) “의미 있는 숫자”로 바꾸는 과정입니다. 이 단원의 목적: 원장 데이터로 ‘요약 결과’를 만드는 습관 만들기 이번 글에서는 아래 3가지를 목표로 합니다. SUM으로 잔액을 만들고, 기간별 합계를 안정적으로 계산한다 COUNT로 이벤트 건수를 집계해 “양”을 파악한다 집계를 할 때 자주 발생하는 실수(NULL, 숨김 데이터, 중복 기준)를 피한다 준비: 집계를 위한 최소 데이터 점검 집계는 데이터가 적으면 티가 안 나지만, 데이터가 쌓이면 작은 실수가 큰 오차로 확대됩니다. 그래서 집계 전에 “필수 조건”을 먼저 확인해 두면 좋습니다. amount가 NULL이 아닌가 숨김 데이터(hide=1)가 섞여 있다면 집계에서 제외할 것인가 부호 정책(적립 + / 차감 -)이 일관적인가 아래 예시는 hide 컬럼이 있다고 가정하고, 집계에서는 hide=0만 포함하는 패턴으로 진행합니다. SET @uid := '11111111-1111-1111-1111-111111111111'; 1) 잔액 계산의 기본: SUM(amount) 가장 기본적인 잔액 조회는 “해당 사용자의 amount를 전부 더하는 것”입니다. 이 방식의 장점은 단순함과 신뢰성입니다. 원장이 올바르면, 잔액은 항상 재현 가능합니다. SELECT uid, SUM(amount) AS balance FROM point_history WHERE uid = @uid AND hide = 0 GROUP BY uid; 여기서...
자세한 내용 보기

키 설계 기초: PRIMARY KEY/UNIQUE로 ‘중복 적립’과 ‘중복 차감’을 구조적으로 막는 방법

포인트 시스템에서 가장 골치 아픈 문제는 “한 번만 처리되어야 하는 요청이 두 번 처리되는 것”입니다. 네트워크 재시도, 클라이언트 중복 클릭, 서버 타임아웃 후 재요청은 운영에서 흔한 일이고, 이때 코드만으로 완벽하게 막는 건 생각보다 어렵습니다. 그래서 많은 서비스는 마지막 방어선으로 키(Primary Key/Unique Key) 를 활용합니다. 키 설계가 잘 되면, 애플리케이션이 실수하더라도 DB가 중복을 거절 합니다. 이번 글에서는 “키는 그냥 ID 하나”라는 수준을 넘어서, 포인트 원장(히스토리)에서 실제로 필요한 멱등성(idempotency) 을 어떻게 만들지에 초점을 맞춥니다. 이 단원의 목적: “중복이 생길 수밖에 없는 현실”을 전제로 설계하기 포인트 기능은 보통 결제, 이벤트, 쿠폰, 고객센터 보정 등 다양한 경로에서 호출됩니다. 호출 경로가 늘어날수록 중복 처리 가능성도 늘어납니다. 이번 글의 목표는 아래 3가지입니다. PK/UNIQUE의 역할을 정확히 이해하고 “어디에 걸어야 하는지” 결정한다 포인트 원장에서 중복을 막는 대표 전략(요청 ID/ref_id)을 설계한다 실제로 중복 요청이 들어오는 상황을 가정해, DB 레벨에서 안전하게 막는다 1) PRIMARY KEY는 “행을 식별하는 유일한 주소”다 PRIMARY KEY(PK)는 테이블에서 한 행을 유일하게 식별합니다. 흔히 AUTO_INCREMENT id를 PK로 두는 이유는 단순합니다. 쉽고, 빠르고, 안정적 이기 때문입니다. 하지만 포인트 시스템에서는 “id가 유일하다”만으로는 부족합니다. id는 단지 “기록의 번호”일 뿐, “같은 요청이 두 번 들어왔다”는 문제를 막아주지 않습니다. 즉, PK는 필요조건이지만 중복 방지의 충분조건은 아닙니다. -- 기록 자체의 식별자(기본) PRIMARY KEY (id) 2) UNIQUE는 “같은 의미의 기록이 두 번 들어오는 것”을 막는다 UNIQUE는 “비즈니스적으로 같은 의미”의 중복을 차단하는 도구입니다. ...
자세한 내용 보기