INSERT 기초: 적립/차감 이벤트를 ‘원장’에 일관되게 기록하는 패턴

포인트 시스템에서 INSERT는 단순한 데이터 입력이 아닙니다. 한 줄의 INSERT는 “어떤 일이 있었는지”를 원장에 남기는 행위이고, 이 기록은 나중에 정산, 고객 문의 대응, 장애 복구의 근거가 됩니다.

그래서 포인트 원장(point_history)에 데이터를 넣을 때는 “일단 들어가면 된다”보다 일관성 있게 들어가야 한다가 더 중요합니다. 이번 글에서는 INSERT 문법 자체를 익히면서, 포인트 시스템에서 자주 발생하는 실수(부호 뒤집힘, ref_id 누락, created_at 불일치)를 줄이는 입력 패턴을 정리합니다.

이 단원의 목적: INSERT를 ‘기록 규칙’으로 만들기

많은 시스템에서 데이터 품질은 조회가 아니라 입력에서 결정됩니다. 포인트 원장에 기록되는 한 줄 한 줄이 흔들리면, 나중에 SUM으로 잔액을 계산할 때도, 특정 주문을 추적할 때도 “해석 비용”이 계속 발생합니다. 이번 글의 목표는 다음 3가지입니다.

  1. 포인트 원장에 반드시 들어가야 하는 최소 컬럼을 고정한다
  2. 적립/차감/만료/취소 같은 이벤트를 INSERT 템플릿으로 통일한다
  3. 중복/재시도 상황에서 안전하게 기록되는 방향(멱등성)을 염두에 둔다

1) 먼저, 포인트 원장에 “반드시” 들어가야 할 것

시스템마다 컬럼 구성은 다르지만, 원장이라는 성격을 유지하려면 아래 항목은 사실상 필수입니다.

  • uid: 누구에게 발생한 일인지
  • action_type: 어떤 행위인지(적립/사용/만료/취소 등)
  • amount: 얼마가 변했는지(부호 정책 포함)
  • created_at: 언제 발생했는지
  • ref_id: 무엇(주문/이벤트/요청)에 의해 발생했는지(가능하면)

여기서 가장 많이 흔들리는 부분이 amount의 부호 정책입니다. “적립은 +, 차감은 -”처럼 한 번 정했으면 끝까지 고정하는 편이, 조회 쿼리(특히 SUM)와 운영 점검이 쉬워집니다.

2) INSERT 기본 문법: 한 건 기록

가장 기본적인 형태는 아래처럼 한 줄을 넣는 것입니다. 연습할 때는 “한 건씩 정확히 넣기”가 먼저이고, 그 다음에 여러 건 입력이나 배치 입력을 고려하는 순서가 좋습니다. 

SET @uid := '11111111-1111-1111-1111-111111111111'; INSERT INTO point_history (uid, point_type, action_type, amount, ref_id, memo, created_at) VALUES (@uid, 'FREE', 'CHARGE', 1000, 'EVENT_20251214', '가입 축하 적립', NOW());

위 INSERT는 “무료 포인트 1000 적립”이라는 사실을 남깁니다. 이후 잔액은 이 원장들을 SUM으로 합산해 계산합니다.

3) 여러 건 입력: VALUES를 여러 줄로 써도 된다

테스트 데이터를 만들거나, 동일한 정책의 이벤트를 한 번에 넣을 때는 여러 행을 한 번의 INSERT로 처리할 수 있습니다. DB 입장에서는 여러 번 INSERT 하는 것보다 부담이 줄어드는 경우도 많습니다. 

INSERT INTO point_history (uid, point_type, action_type, amount, ref_id, created_at) VALUES (@uid, 'FREE', 'CHARGE', 200, 'EVENT_20251220', NOW() - INTERVAL 2 DAY), (@uid, 'PAID', 'CHARGE', 500, 'ORDER_9001', NOW() - INTERVAL 7 DAY), (@uid, 'FREE', 'USE', -300, 'USE_7001', NOW() - INTERVAL 5 DAY);

이렇게 입력한 다음, 이전 글에서 만든 잔액/내역 쿼리로 바로 검증할 수 있습니다.

4) 포인트 시스템에서 자주 쓰는 INSERT 템플릿 4가지

실제 구현에서는 “INSERT 문법”보다 “이벤트별로 어떤 값을 넣는가”가 더 중요합니다. 아래 템플릿은 원장 기록의 형태를 통일하기 위한 예시입니다. 시스템 정책에 맞게 memo/ref_id 규칙을 정해두면 운영이 훨씬 편해집니다.

(1) 적립(CHARGE): +amount

INSERT INTO point_history (uid, point_type, action_type, amount, ref_id, memo, created_at) VALUES (@uid, 'FREE', 'CHARGE', 300, 'EVENT_ATTEND_20251214', '출석 적립', NOW());

(2) 사용(USE): -amount

사용은 차감이므로 amount는 음수로 기록합니다. “어떤 사용 요청인지”를 ref_id로 추적할 수 있으면, 중복 차감 방지나 고객 문의 대응에서 강력한 근거가 됩니다. 

INSERT INTO point_history (uid, point_type, action_type, amount, ref_id, memo, created_at) VALUES (@uid, 'FREE', 'USE', -150, 'USE_REQ_20251214_001', '상품 구매 사용', NOW());

(3) 만료(EXPIRE): -amount

만료는 “정책에 의해 차감되는 사건”입니다. 만료는 보통 배치 작업으로 들어오므로, ref_id에 “배치 실행 단위”를 남기는 것도 도움이 됩니다. 

INSERT INTO point_history (uid, point_type, action_type, amount, ref_id, memo, created_at) VALUES (@uid, 'FREE', 'EXPIRE', -50, 'EXPIRE_BATCH_20251214', '유효기간 만료', NOW());

(4) 사용취소(USE_CANCEL): +amount

사용취소는 “이전 차감의 되돌림”이므로, 결과적으로는 +가 됩니다. 이때 ref_id는 “취소의 근거”를 남기기 위해 원래 사용 요청을 참조하거나, 취소 요청의 ID를 별도로 발급하는 방식이 있습니다. 

INSERT INTO point_history (uid, point_type, action_type, amount, ref_id, memo, created_at) VALUES (@uid, 'FREE', 'USE_CANCEL', 150, 'USE_REQ_20251214_001', '사용 취소(원복)', NOW());

ref_id를 원래 사용 요청과 동일하게 두면 “사용과 취소를 한 묶음으로 추적”하기 쉬워집니다. 다만 UNIQUE 제약조건을 (uid, ref_id)로만 걸어둔 상태라면 충돌이 날 수 있으니, 이전 글에서 다룬 것처럼 (uid, ref_id, action_type) 같은 키 설계를 고려해야 합니다.

5) INSERT에서 자주 발생하는 실수와 예방책

(1) 부호가 뒤집히는 실수

사용인데 +150으로 들어가거나, 적립인데 -로 들어가면 잔액이 깨집니다. 예방책은 “부호 정책을 문서로 고정”하고, 테스트 데이터로 SUM 검증을 습관화하는 것입니다. 

-- 잔액 검증(항상 습관처럼) SELECT uid, SUM(amount) AS balance FROM point_history WHERE uid = @uid GROUP BY uid;

(2) created_at을 NOW()로만 넣는 문제

운영에서는 “실제 이벤트 발생 시간”과 “DB에 기록된 시간”이 다를 수 있습니다. 예를 들어 배치가 새벽에 돌면, 사용자 입장에서는 어제 발생한 이벤트가 오늘로 찍힐 수 있습니다. 그래서 시스템에 따라 created_at을 “이벤트 발생 시간”으로 넣고, 별도로 inserted_at 같은 컬럼을 둘 때도 있습니다.

지금 단계에서는 created_at을 “기록 시간”으로 단순화해도 충분하지만, 시간이 중요해지는 포인트 시스템에서는 이 구분을 알고 있는 것만으로도 나중에 설계를 확장할 때 도움이 됩니다.

(3) ref_id 누락: 추적 가능성이 떨어진다

ref_id는 필수는 아닐 수 있지만, 있으면 운영이 훨씬 편해집니다. 특히 주문/결제/사용 요청처럼 “건별 추적”이 중요한 이벤트는 ref_id를 적극적으로 남기는 편이 좋습니다.

6) 중복/재시도 상황에서 INSERT를 안전하게 만드는 방향

중복 요청을 완벽히 막으려면, 애플리케이션 레벨의 멱등 처리와 DB 제약조건이 함께 가야 합니다. DB 측면에서는 최소한 “같은 의미의 기록이 두 번 들어오지 않게” UNIQUE로 막는 것이 기본입니다.

예를 들어 “같은 이벤트 지급”은 (uid, ref_id) UNIQUE로 막을 수 있습니다. 만약 사용취소까지 ref_id를 공유한다면, (uid, ref_id, action_type)으로 유일성 범위를 조정합니다. 핵심은 “어떤 것이 같은 사건인가”를 먼저 정의하고, 그 정의를 키로 옮기는 것입니다.

7) 입력 후 검증: INSERT는 반드시 ‘확인 쿼리’와 함께 다룬다

원장에 기록을 남겼으면, 바로 검증하는 습관이 중요합니다. 아래 2개 쿼리는 포인트 시스템에서 가장 기본적인 확인 도구입니다.

(1) 최신 내역 확인

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

(2) 잔액 확인

SELECT uid, SUM(amount) AS balance FROM point_history WHERE uid = @uid GROUP BY uid;

이 검증을 반복하다 보면, “어떤 이벤트가 어떤 형태로 기록되어야 하는지”가 몸에 붙습니다. 결국 SQL 학습에서 중요한 건 문법 자체보다 일관된 데이터 모델을 만드는 감각입니다.

다음 글 예고: UPDATE/DELETE로 정정(ADJUST)과 소프트 삭제 전략 잡기

다음 글에서는 UPDATE/DELETE를 다룹니다. 포인트 시스템에서는 내역을 직접 수정하는 것은 매우 조심해야 하지만, 운영에서는 “잘못된 적립을 정정”하거나 “잘못 들어간 데이터를 숨김 처리”해야 하는 순간이 생깁니다. 이때 원장의 신뢰성을 유지하면서 정정하는 방법(ADJUST 기록 방식), 그리고 물리 삭제 대신 소프트 삭제를 쓰는 이유를 함께 정리합니다.

댓글

댓글 쓰기

이 블로그의 인기 게시물

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...
자세한 내용 보기

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

포인트 시스템은 “정상 처리”만 잘 만들면 끝나는 것처럼 보이지만, 실제로는 시간이 지날수록 다양한 이유로 데이터가 어긋날 수 있습니다. 예를 들어 이런 상황이 생깁니다. 중간 장애로 트랜잭션이 일부만 반영된 것처럼 보이는 케이스(재처리/수동 개입 포함) 운영 정정(ADJUST)이나 소프트 삭제(hide) 처리 기준이 흔들린 케이스 배치 작업(만료/정산)이 예상과 다른 시간에 들어간 케이스 코드 변경 이후 특정 action_type이 누락/중복 집계된 케이스 그래서 “점검 쿼리”는 기능 쿼리만큼 중요합니다. 특히 balance 테이블(잔액 캐시)을 함께 쓰는 구조라면, 주기적으로 원장 합계와 balance가 같은지 확인해야 합니다. 이 단원의 목적: 불일치를 “발견”하고 “원인”을 좁힐 수 있는 쿼리 세트 만들기 이번 글에서는 아래 3가지를 목표로 합니다. 원장 합계(SUM)와 balance를 비교해 불일치 UID를 뽑는다 불일치가 발견되면 어디서부터 어긋났는지 범위를 좁힌다 자주 발생하는 유형(숨김 처리, 중복 요청, 배치 만료)을 점검 항목으로 만든다 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_...
자세한 내용 보기

EXPLAIN 기초: 점검/리포트 쿼리가 느려질 때 “왜 느린지” 확인하는 방법

포인트 시스템을 만들어가면서 새삼 느끼는것은 생성형AI가 있어서 설계하고 구상하는게 너무 쉬워졌다는것입니다. 혼자서 했다면 아마도 어려움이 많았을거 같은데 AI와 대화를 하며 개선점을 찾아가는 방식으로 진행하다보니 생각보다 기능이 치밀하게 설계를 할 수 있었습니다. 지금까지 하나하나 학습범위를 늘려가며 왔는데 아직도 갈길이 먼거 같습니다.  포인트 원장(point_history)은 시간이 지날수록 커집니다. 데이터가 커지면, 기능 쿼리(내역/잔액)뿐 아니라 점검 쿼리(원장 vs balance 비교, 월별 집계, ref_id 그룹핑)도 점점 느려집니다. 이때 흔히 “인덱스를 더 만들면 되지 않을까?”라고 생각하지만, 인덱스는 무조건 늘린다고 좋아지지 않습니다. 어떤 쿼리가 어떤 방식으로 실행되는지 모르면 잘못된 인덱스를 만들거나, 효과 없는 튜닝을 반복할 가능성이 큽니다. 그래서 필요한 도구가 EXPLAIN 입니다. EXPLAIN은 “이 쿼리를 DB가 어떤 순서로 실행할지”를 보여주는 실행 계획입니다. 즉, 느린 이유를 추측하지 않고 눈으로 확인 할 수 있게 해줍니다. 이 단원의 목적: 실행 계획을 보고 “어디를 고칠지” 결정하기 이번 글의 목표는 아래 3가지입니다. EXPLAIN 결과에서 최소한의 핵심 항목을 읽을 수 있게 된다 포인트 시스템 쿼리에서 자주 나오는 느린 패턴(풀 스캔, filesort)을 구분한다 인덱스를 “조회 패턴에 맞게” 설계하는 감각을 만든다 1) EXPLAIN 사용법: SELECT 앞에 붙이면 된다 가장 기본은 SELECT 앞에 EXPLAIN을 붙이는 것입니다. EXPLAIN SELECT id, action_type, amount, created_at FROM point_history WHERE uid = '11111111-1111-1111-1111-111111111111' AND hide = 0 ORDER BY created_at DESC, id DESC LIMIT 50; 실행 결과는 ...
자세한 내용 보기