공통 코드 설계

• 데이터 입력시 하드코딩된 상태값을 사용하는 경우 여러 문제가 파생된다.

-- 1. 데이터 불일치 문제
-- 비즈니스 로직 상 동일한 의미를 갖지만 입력값 형태가 달라 데이터 불일치
INSERT INTO orders (member_id, order_status, total_amount, created_at) VALUES
(2, '주문 완료', 75000, '2026-01-15 11:00:00'),
(3, 'ORDER_COMPLETED', 85000, '2026-01-16 11:00:00'),

-- 2. 오타로 인한 문제
INSERT INTO orders (member_id, order_status, total_amount, created_at) VALUES
(2, '주문 완룡', 75000, '2026-01-15 11:00:00'),

-- 3. 상태 변경의 어려움
-- 주문완료 상태값을 ORDER_COMPLETED로 변경?
-- 전체 컬럼값을 조회하여 변경해야 한다.

• 위와 같은 문제 해결을 위해 코드값 분리 방법을 사용한다.
• 코드값 분리는 값과 표시 이름을 분리하는 것이다.
• 공통코드(Common Code)는 시스템 전체에서 사용하는 코드값과 그에 대응하는 이름을 중앙에서 관리하는 방식이다.

INSERT INTO orders (member_id, order_status, total_amount, created_at) VALUES
(1, 'ORDER', 50000, '2026-01-15 10:30:00'),
(2, 'PAID', 75000, '2026-01-15 11:00:00'),
(3, 'SHIPPING', 30000, '2026-01-15 12:00:00'),
(1, 'DELIVERED', 120000, '2026-01-14 09:00:00'),
(4, 'CANCEL', 45000, '2026-01-13 15:00:00');

• 코드값 | 한글 이름 | 영문 이름 형태로 컬럼들을 구성한 테이블을 따로 분리하여, 실제 테이블 표시에는 각 언어에 맞게 조인하는 구조이다.

CREATE TABLE common_code (
    code VARCHAR(50) PRIMARY KEY ,
    name VARCHAR(100) NOT NULL
)

• 코드값을 기본 키로 사용한다.
• 만약 서로 성격이 다른 코드가 생겼다면?
  • 주문 및 배송 상태를 나타내는 코드값과 멤버십 등급을 나타내는 코드값이 하나의 공통 코드에 추가된다면 잘못된 데이터가 사용될 수 있다.
• 이 경우 코드 그룹화 방식을 사용한다.
• 일반적으로 공통 코드는 대리키를 사용하지 않는다.

CREATE TABLE common_code_group (
    group_code VARCHAR(50) PRIMARY KEY ,
    group_name VARCHAR(100) NOT NULL,
    description VARCHAR(500),
    use_yn CHAR(1) NOT NULL DEFAULT 'Y',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• group_code: 그룹명 코드값 (ORDER_STATUS, MEMBER_GRADE)
• group_name: 그룹명 표시명 (주문상태, ORDER_STATUS ..)
• description: 그룹설명
• use_yn: 사용여부, 그룹 전체 비활성화 여부

CREATE TABLE common_code_detail (
    group_code VARCHAR(50) NOT NULL,
    code VARCHAR (50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500),
    sort_order INT NOT NULL DEFAULT 0,
    use_yn CHAR(1) NOT NULL DEFAULT 'Y',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT  CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• group_code: 현재 코드가 속한 그룹 코드값
• code: 실제 코드값
• name: 코드 표시명
• description: 코드 설명
• sort_order: 정렬순서
• use_yn: 특정 코드만 비활성화할 때 사용
• (group_code, code)가 복합키로 사용됨
  • 같은 그룹에서만 코드값이 다르면 됨

INSERT INTO common_code_detail (group_code, code, name, sort_order) VALUES
-- 주문 상태
('ORDER_STATUS', 'ORDER', '주문접수', 1),
('ORDER_STATUS', 'PAID', '결제완료', 2),
('ORDER_STATUS', 'SHIPPING', '배송중', 3),
('ORDER_STATUS', 'DELIVERED', '배송완료', 4),
('ORDER_STATUS', 'CANCEL', '주문취소', 5),

-- 멤버 등급
('MEMBER_GRADE', 'NORMAL', '일반회원', 1),
('MEMBER_GRADE', 'VIP', 'VIP 회원', 2),
('MEMBER_GRADE', 'VVIP', 'VVIP 회원', 3),

-- 결제 상태
('PAYMENT_STATUS', 'PENDING', '결제대기', 1),
('PAYMENT_STATUS', 'COMPLETE', '결제완료', 2),
('PAYMENT_STATUS', 'FAILED', '결제실패', 3),
('PAYMENT_STATUS', 'CANCEL', '결제취소', 4),
('PAYMENT_STATUS', 'REFUND', '환불완료', 5),

-- 결제 수단
('PAYMENT_METHOD', 'CARD', '신용카드', 1),
('PAYMENT_METHOD', 'BANK', '계좌이체', 2),
('PAYMENT_METHOD', 'VIRTUAL', '가상계좌', 3),
('PAYMENT_METHOD', 'MOBILE', '휴대폰결제', 4);

-- 쿼리
SELECT
    m.name,
    m.grade,
    cd.name
FROM members m
JOIN common_code_detail cd ON cd.group_code = 'MEMBER_GRADE' AND m.grade = cd.code
ORDER BY m.member_id;

-- 여러 코드 조인
SELECT
    p.payment_id,
    o.order_id,
    p.payment_method,
    pm.name AS method_name,
    p.payment_status,
    ps.name AS status_name,
    p.amount
FROM payments p
JOIN orders o ON p.order_id = o.order_id
JOIN common_code_detail pm ON pm.group_code = 'PAYMENT_METHOD' AND p.payment_method = pm.code
JOIN common_code_detail ps ON ps.group_code = 'PAYMENT_STATUS' AND p.payment_status = ps.code;
--   JOIN 성능을 위해 그룹코드를 명시해줘야 한다.

• 공통 코드와 관련된 여러 속성들이 존재할 수 있다.
  • 멤버 등급 -> 할인율, 적립률, 등
  • 이들을 단순 컬럼 추가 방식으로 구현하는 것은 확장성에 한계가 존재한다. (비즈니스 요구사항의 변경)
• 또한 각 그룹별로 요구하는 속성이 제각각이다. 이때는 EAV(Entity-Attribute-Value) 방식을 사용한다.
• 공통코드 사용시 장점만 있는 것은 아니다.
  1. 조인이 복잡해진다. (조인지옥)
  2. 코드 이름 조회 로직이 중복된다. 하나의 코드를 여러 비즈니스 로직에서 차용하는 경우 중복된다.
  3. SELECT 결과에 표시이름 필드를 항상 불러와야 한다.
• 이를 해결하기 위해 여러 방안들이 존재한다.
  1. SQL에서는 공통 코드값만 조회하고 애플리케이션 레벨에서 코드값을 이름으로 변경
     • 반환받은 코드값으로 표시이름 테이블에 한번 더 쿼리를 수행하여 MAP으로 저장
     • 이때 공통코드 조회로 인한 네트워크 통신 횟수가 잦아질 수 있다.
     • 주문 목록 조회 1회 쿼리 -> 각 주문 목록별 상태코드 새로 쿼리
  2. 캐싱 활용
     • 공통 코드가 한번 만들어지면 그 갯수가 차지하는 크기가 작고, 변경이 거의 없지만, 조회가 빈번하다.
     • 이 경우 메모리 캐싱을 활용한다. 애플리케이션 시작 시 메모리에 로드하는 것이다.
     • 메모리 캐싱은 데이터 수정 시 여러 운영 서버에 데이터 동기화를 해줘야 한다.
     • 일반적으로 캐시에 TTL을 추가하여 만료 시 데이터베이스에서 다시 로딩하도록 한다.

N+1 문제

• 1번의 쿼리로 끝낼 수 있는 작업을 N번 추가로 쿼리를 수행하는 문제를 말한다.
• 정확히는 결과 건수만큼 쿼리가 발생하는 문제이다.
• 공통코드를 애플리케이션 레벨에서 매번 조회하는 방식이 이 문제를 야기한다.
  1. 주문 목록 조회 (결과 5건)
  2. 주문 1 상태코드, 주문 2 상태코드 ...
• IN을 통해서 N+1 쿼리를 완화해야 한다.

애플리케이션 ENUM으로 공통코드 관리하기

• 애플리케이션 레벨에서 ENUM을 통해 공통 코드를 관리할 수도 있다.
• 이 경우 ENUM 속성이 수정되면 애플리케이션 코드를 새로 배포해야 한다.
• 코드 안정성, IDE 지원 등이 좋지만 DB에 비해 변경 유연성이 낮다.
• 분기처리 로직이 애플리케이션 로직에 깊게 관여되어 있다면 ENUM으로 관리하는 것이 좋다.

계층 구조 설계

• DB 설계시에는 계층 구조를 자주 설계하게 된다.
  • 전자제품 -> 컴퓨터 -> 노트북 / 데스크탑..
  • 전자제품 -> 스마트폰 -> 애플 / 삼성 ..

인접 리스트 모델

• 계층구조 구현을 위한 가장 직관적이고 널리 사용되는 방식이다.
  • 각 행이 자신의 부모를 참조하는 방식
  • 각 노드가 자신의 부모 노드를 가리키는 FK를 갖는다.
  • 최상위 노드는 부모가 없으므로 NULL

CREATE TABLE category (
    category_id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    parent_id BIGINT NULL,
    PRIMARY KEY (category_id),
    FOREIGN KEY (parent_id) REFERENCES category(category_id)
);

• 부모테이블의 개념보다는 자기 참조 관계에 해당한다.

INSERT INTO category (name, parent_id) VALUES
('전자제품', NULL),
('의류', NULL),
('식품', NULL),

('컴퓨터', 1), -- 4
('스마트폰', 1), -- 5

('데스크탑', 4),
('노트북', 4),

('남성의류',2),
('여성의류', 2);

• 직속 자식 조회를 위해서는 WHERE parent_id 조건만 추가하여 조회하면 된다.
• 부모 조회를 위해서는 현재 행의 부모 아이디 값과 카테고리 아이디값을 가지고 조인을 하면 된다. (상사 찾기 쿼리와 동일)
• 인접리스트를 통한 계층구조 구현에는 여러 장점들이 있다.
  1. 직관적
  2. 데이터 CRUD의 간편함
  3. 저장공간 효율
  4. 외래키 제약조건을 통해 참조 무결성 확보
• 반면 단점도 존재한다.
  1. 뎁스가 깊고 너비가 넓은 데이터 조회 성능이 떨어진다.
     • 애플리케이션 레벨에서의 반복 호출이 필요할 수 있다.
     • 네트워크 호출 비용, 성능 문제
• 계층의 최대 깊이가 고정되어 있다면 JOIN을 사용하여 한번의 쿼리로 처리 가능하다.
  • 깊이 수만큼 LEFT JOIN을 수행하면 된다.
  • 내부 조인을 수행하게 되면 자식이 없는 노드는 사라지게 된다.

SELECT c1.name, c2.name, c3.name
FROM category c1
JOIN category c2 ON c2.parent_id = c1.category_id
JOIN category c3 ON c3.parent_id = c2.category_id
WHERE c1.category_id = 1;

• INNER JOIN은 매칭되는 레코드가 없으면 해당 행을 제외하므로, LEFT JOIN을 사용해야 자식이 없는 노드도 포함된다.

LEFT JOIN

c1.name	c2.name	c3.name
전자제품	컴퓨터	데스크탑
전자제품	컴퓨터	노트북
전자제품	스마트폰	(null)

INNER JOIN

c1.name	c2.name	c3.name
전자제품	컴퓨터	데스크탑
전자제품	컴퓨터	노트북

• 손자 조회와 마찬가지로 조상을 조회하는 것도 어렵다.
• 계층의 깊이를 미리 알아야하거나 여러 쿼리가 동시에 필요하다는 점이 한계이다.
• 이러한 이유로 Recursive Query가 도입되었고, 이를 CTE(Common Table Expression)으로 작성할 수 있다.

CTE, 재귀쿼리

• CTE는 쿼리 내에서 임시로 사용할 수 있는 이름 있는 결과 집합이다. WITH 절을 사용하여 정의한다.

-- CTE 정의 쿼리
WITH cte_name AS (
    SELECT ..
)

-- CTE
SELECT * FROM cte_name;

• 아래는 예시이다. View와 유사하게 쿼리 결과를 참조하는 역할로도 사용 가능하다.

WITH top_categories AS (
    SELECT * FROM category WHERE parent_id IS NULL
)

SELECT * FROM top_categories;

• 재귀를 사용하는 경우 WITH RECURSIVE로 시작하고, 내부에 UNION ALL 또는 UNION을 적어야 한다.
  • 기본 케이스와 재귀케이스 결과를 합친다.
  • 기본 케이스: 재귀 시작점
  • 재귀 케이스: CTE 스스로를 참조하여 반복 실행되는 쿼리
• 실행흐름은 다음과 같다.
  1. 기본 케이스 실행 후 초기 결과 획득
  2. 재귀 케이스 실행 후 새로운 행 획득
  3. 새로운 행이 없을때까지 2번과정 반복
  4. 최종결과 반환

WITH RECURSIVE descendants AS (
    -- 기본 케이스
    SELECT category_id, name, parent_id, 1 AS depth
    FROM category
    WHERE category_id = 1

    UNION ALL

    -- 재귀케이스: 이전 결과 자식들 찾기
    SELECT c.category_id, c.name, c.parent_id, d.depth + 1
    FROM category c
    JOIN descendants d ON c.parent_id = d.category_id
)
SELECT * FROM descendants;

• UNION ALL 이후 부분이 재귀케이스에 해당한다.
• UNION ALL로 합쳐진 최종 결과가 반환되는 것은 별도이며, 재귀가 동작할 때는 재귀 수행 후 남는 부분 결과만 임시로 남아 사용된다.
  1. 기본케이스 수행 결과를 descendants에 포함
  2. 기본케이스의 category_id가 parent_id인 카테고리 테이블 조회
  3. 수행 결과는 result table, 기본 케이스 결과는 working table에 포함
  4. 재귀 수행 후의 descendants category_id가 parent_id인 카테고리 테이블 조회
  5. 최종 합쳐진 결과는 result table에 쌓여있으므로 이를 반환, 재귀 부분 결과들은 working table에 쌓아 반환
• 기본케이스와 재귀케이스 컬럼 수와 타입은 동일해야 한다.
• WITH 절의 depth 컬럼과 WHERE를 활용하면 재귀 뎁스를 지정할 수 있다.

WITH RECURSIVE ancestors AS (
    SELECT category_id, name, parent_id, 1 AS depth
    FROM category
    WHERE name = '노트북'

    UNION ALL

    SELECT c.category_id, c.name, c.parent_id, a.depth + 1
    FROM category c
    JOIN ancestors a ON a.parent_id = c.category_id
    WHERE a.depth < 2
)

폐쇄 테이블 (Closure Table)

• 인접리스트 / CTE만으로도 계층구조 요구사항 처리가 거의 가능하다.
• 만약 데이터 자체가 대용량이고, 계층구조 조회가 빈번하며, 뎁스가 깊은 경우 성능이 저하될 수 있다.
• 이 경우에는 폐쇄 테이블 모델 사용을 고려할 수 있다.
• 폐쇄 테이블은 모든 조상-자손 관계를 미리 계산하여 별도 테이블에 저장하는 방식이다.
  • 조회 시점의 재귀를 저장 시점으로 옮기는 것이다.
• 폐쇄 테이블 모델은 노드 테이블, 경로 테이블 두 테이블이 필요하다.
  • 노드 테이블: 실제 데이터 저장
  • 경로 테이블: 모든 조상 - 자손 관계 저장

CREATE TABLE category_closure (
    category_id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (category_id)
);

CREATE TABLE category_path (
    ancestor_id BIGINT NOT NULL,
    descendant_id BIGINT NOT NULL,
    depth INT NOT NULL,
    PRIMARY KEY (ancestor_id, descendant_id),
    FOREIGN KEY (ancestor_id) REFERENCES category_closure(category_id),
    FOREIGN KEY (descendant_id) REFERENCES category_closure(category_id)
);

CREATE INDEX idx_descendant ON category_path(descendant_id);
CREATE INDEX idx_depth ON category_path(depth);

INSERT INTO category_closure (category_id, name) VALUES (1, '전자제품');
INSERT INTO category_closure (category_id, name) VALUES (2, '컴퓨터');
INSERT INTO category_closure (category_id, name) VALUES (3, '스마트폰');

-- 경로 데이터
-- 자기 자신에 대한 경로 (depth = 0)
-- (ancestor_id, descendant_id, depth)
INSERT INTO category_path VALUES (1, 1, 0);
INSERT INTO category_path VALUES (2, 2, 0);
INSERT INTO category_path VALUES (3, 3, 0);

-- 전자제품(1)의 자식들
INSERT INTO category_path VALUES (1, 2, 1); -- 전자제품(1) -> 컴퓨터(2)
INSERT INTO category_path VALUES (1, 3, 1); -- 전자제품(1) -> 스마트폰(3)
INSERT INTO category_path VALUES (1, 4, 2); -- 전자제품(1) -> 노트북(4) (손자)
INSERT INTO category_path VALUES (1, 5, 2); -- 전자제품(1) -> 데스크탑(5) (손자)

• 위와 같은 구조로 테이블과 데이터가 구축된다.
• 모든 자손을 조회할때는 간단한 조인 쿼리만 수행하면 된다.

-- 조상 아이디가 1인 모든 자손
-- depth까지 활용하면 직속자손 등 자손 깊이까지 조정 가능
SELECT *
FROM category_closure c
JOIN category_path p ON c.category_id = p.descendant_id
WHERE p.ancestor_id = 1;

데이터 변경 이력 설계

• 비즈니스 관점에서 데이터 변경 이력을 관리하는 것은 매우 중요하다.
• 데이터 변경 이력을 관리하는 방법은 여러가지가 있다.

1. 변경 추적 컬럼 추가

• 가장 단순한 방법으로, 변경 주체나 관련 정보들을 저장할 컬럼을 하나 추가하는 방식이다.

CREATE TABLE product (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    price INT NOT NULL ,
    stock_quantity INT NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by VARCHAR(100) NOT NULL
);

• 실무에서는 변경 추적 컬럼을 거의 모든 테이블에 적용한다.
• 등록일, 등록자, 수정일, 수정자 컬럼은 항상 포함한다.

2. 변경 사유 추적

• change_type, change_reason과 같은 컬럼을 사용한다.
  • change_type: 변경 유형 (공통 코드 활용)
  • change_reason: 변경사유 (description 역할)
• 어떤 시스템에서 변경이 발생했는지 (API 서버 / 배치 서버 등)
• 어떤 IP에서 변경을 시도했는지
• source_system, client_ip 컬럼을 추가해서 관리한다.
  • 애플리케이션 레벨에서 입력값을 전달받아야 함

3. 이전 값 추적 (Audit 컬럼)

• 과거 데이터를 보존하여 변경 이전 값을 확인하는 방법은 여러 방법이 있다.
• 이전 값과 현재 값을 각각 별도의 컬럼에 저장
  • previous_value컬럼을 하나 추가한다.
• 위 방식은 바로 직전 값 하나만 보관 가능하다.
• 관리할 컬럼 수가 많아지는 경우 테이블 사이즈가 커지게 된다.
• 이때, 컬럼 추가 방식이 아닌 행 자체를 보관하는 방법을 고려할 수 있다.
  • 데이터를 수정할때 기존 행을 UPDATE하는게 아닌 새로운 행을 INSERT한다.
  • 이때는 is_current 컬럼으로 최신 데이터인지 여부를 구분할 수 있다.
  • 테이블 PK로 사용할 고유 ID와 데이터가 자체적으로 가지고 있는 아이디값을 따로 구분해야 한다.
  • 데이터가 변경되어도 데이터가 가지고있는 아이디값은 동일하기 때문이다. (상품 아이디)
• valid_from, valid_to컬럼을 추가하여 데이터 변경으로 인해 데이터가 유효한 기간을 설정하는 방식도 존재한다.
• 이러한 방식은 데이터가 계속해서 쌓이기 때문에 사이즈가 비대해진다는 단점이 존재한다.
• valid_from, valid_to 기반 이력조회 성능을 위해 인덱스를 추가해주자. (id, valid_from, valid_to)

4. 전체 행 스냅샷

• 이력 조회는 비즈니스 운영 과정에서 작은 부분만 차지한다.
• 이력 조회용 테이블을 완전히 따로 관리하는 것이 이번 방식이다.
• 이력 테이블은 created_at과 history_created_at으로 구분해야한다.
  • created_at은 데이터가 원본 테이블에서 생성된 시각이고, history_created_at은 이력 테이블에 추가된 시각이다.
  • 이외에 created_by, change_type 등 여러 다양한 컬럼들을 고려하면 된다.
• 이력 테이블은 반드시 INSERT 시점부터 추가가 시작되어야 한다.
  • change_type을 CREATE 등의 코드로 관리하면 된다.
  • INSERT 시점부터 관리하게 되면 이력의 연속성, 원본 테이블 복원 등의 장점을 갖는다.
• 저장 용량에 대한 비용이 현대에는 저렴해졌기 때문에 일반적으로 스냅샷 방식을 많이 사용한다.

5. 공통 이력 테이블

• 공통 이력 테이블은 모든 테이블의 변경 이력을 하나의 테이블에서 관리하는 방식이다.
  • JSON 기반으로 처리한다.
• 공통 이력 테이블은 구현의 단순함이 있지만, 여러 단점들이 존재한다.
  • 특정 테이블 상세 이력 조회가 불편하다.
  • 시점 복원이 어렵다.
  • 테이블이 매우 커진다.
  • 인덱스 효율이 떨어질 수 있다.
• 전체 시스템을 감사하고, 백업 저장소로서 역할을 하기에는 유용하다.
• 기본적으로 로그는 DB에 남기기보다 파일시스템 기반으로 남기는게 좋다.

SOFT DELETE

• DELETE쿼리는 데이터를 영구적으로 삭제한다.
  • 데이터를 언제 삭제했는지 모른다.
  • 법적, 비즈니스적 요구사항을 위반할 수 있다.
  • 데이터 복구가 불가능하다.
• SOFT DELETE는 데이터를 실제로 삭제하지 않고 삭제되었다는 표시만 하는 방식이다.
  • 논리적 삭제라고도 표현한다.
• 이를 실제로 구현하는 방법은 여러가지가 존재한다.
• is_deleted 컬럼 추가
  • DELETE 쿼리를 사용하지 않고 UPDATE문으로 해당 컬럼값 true로 변경
  • 삭제 일자를 알기 어렵고, 일정기간 이후의 영구 삭제 정책을 적용하기 어렵다.
  • 복구이력 관리가 어렵다.
• deleted_at 컬럼 추가
  • 삭제 시점을 저장한다. DEFAULT 값은 NULL로 한다.
  • 탈퇴 후 특정 기간이 지난 회원 정보를 영구적으로 삭제할 수 있다.
  • 일반적으로 사용되는 방식이다.
• 실무에서는 상태를 기반으로 관리하는 경우가 더 많다.
  • 데이터는 그대로 유지하면서 status 컬럼의 값만 STOP 등의 코드로 변경하는 방식이다.
  • 비즈니스 프로세스가 복잡하거나 데이터 생명주기가 중요한 경우 사용한다.
• 유니크 인덱스 컬럼에 SOFT DELETE를 적용하는 경우 같은 값으로 데이터 입력이 이루어질때 쿼리 오류가 발생할 수 있다.
  • 이 경우 탈퇴시에 해당 컬럼 값을 적절한 값으로 변경해준다. (ex - 기존ID_deleted_timestamp)

통계 테이블 설계

• 통계처리 쿼리는 많은 행을 처리해야 하기 때문에 데이터베이스에 부하를 주는 작업이다.
• 집계로 인해 서비스에 영향을 줄 수 있다.
• 이러한 문제 해결을 위해 통계를 위한 별도 테이블을 만드는 것을 고려할 수 있다.

CREATE TABLE daily_sales_stats (
    stat_date DATE NOT NULL,
    total_order_count INT NOT NULL DEFAULT 0,
    total_sales_amount BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (stat_date)
);

• 배치처리로 합계 전체 데이터 수를 집계해주는 테이블이다.
• 주별, 월별 데이터는 일별 데이터 테이블만 있으면 집계만 하면 된다.
• 오늘까지의 집계 데이터를 보고싶은 경우, 과거데이터가 집계된 통계 테이블과 원본 테이블의 오늘 데이터만 UNION ALL 하여 집계하면 된다.
  • 원본 테이블에는 적절한 인덱스가 적용되어 있어야 한다.
• 만약 통계 테이블에 배치처리 중 네트워크 에러 등으로 작업이 중단된 경우, 멱등성을 보장해야 한다.
  • 멱등성(Idempotency)이란, 몇번을 실행하든 그 결과가 항상 같아야 한다는 것이다.
  • 배치 시스템 처리가 중단되어 재실행했을때 누적중인 데이터가 그대로 남아있게 되면 값이 뻥튀기가 될것이다.
• 멱등성을 보장하는 방법으로 지우고 다시쓰기 (DELETE & INSERT)가 가장 확실하다.
  • 삭제시 DB는 복구를 위한 Undo Log를 남긴다. 다시 INSERT시 Redo Log를 기록 후 인덱스를 재생성한다.
  • 간단한 값 수정임에도 DB 엔진이 불필요한 작업들을 많이한다.
  • 만약 PK가 AUTO_INCREMENT라면 아이디값이 빠르게 고갈될 수도 있다.
• 데이터를 지우지 않으면서 변경된 값만 갱신하는 방법을 고안해야 한다.
  • UPSERT 최적화는 DELETE & INSERT 대신 값을 UPDATE해준다.
  • 조회 후 등록 수정 패턴으로, 데이터 조회 후 없으면 INSERT, 있으면 UPDATE하는 패턴이 가장 일반적이다.
  • 특정 날짜에 대한 통계데이터가 없으면 처리, 이미 있는상태면 재계산 된 데이터로 업데이트만 수행
  • 이는 SELECT & INSERT OR UPDATE 로 두 쿼리가 수행된다는 비효율이 존재한다.
  • SQL에서는 INSERT + UPDATE 최적화로 UPSERT를 제공한다.
• UPSERT: ON DUPLICATE KEY UPDATE
  • 삽입하려는 데이터의 PK가 이미 테이블에 존재하여 중복된 경우 삭제하지 않고 업데이트 로직을 수행하는 쿼리다.
  • INSERT INTO 구문에서 사용한다.

INSERT INTO 테이블명(컬럼1, 컬럼2, ..)
VALUES (값1, 값2, ..)
ON DUPLICATE KEY UPDATE
컬럼1 = 값1,
컬럼2 = 값2
..

• UPSERT는 멱등성도 보장한다.
• DELETE & INSERT는 수행 과정에서 많은 로그를 남긴다.
• UPSERT는 인덱스를 보존하고, 변경된 사항에 대해서만 최소한의 로그만 남기며, 저장 공간도 재사용한다. 네트워크 호출 횟수도 줄어든다.
• 주요하게 사용되지 않는 배치처리는 DELETE & INSERT를 사용하고, 실시간 집계는 UPSERT를 사용한다.
  • 행단위 중복을 확인하며 빠른 성능을 필요로 할때는 UPSERT
  • 대량 데이터를 한번에 처리할때는 모두 지우고 새로 INSERT 하는게 더 빠름

상속 관계 설계

• 대부분의 DBMS는 상속 기능을 지원하지 않는다. 이를 테이블로 직접 구현한 것을 슈퍼타입 / 서브타입 모델이라 부른다.
• 모든 상품은 상품 아이디, 상품명, 가격등의 공통속성을 갖는다. 상품의 상세 분류로 도서, 전자제품 등으로 세부 성격을 갖는다.
• 이들을 상속관계로 정의하기 위한 전략들이 존재한다.

서브타입별 개별 테이블 전략 (Roll-Down)

• 자식 클래스마다 별도 테이블을 만들고 각 테이블에 부모 속성까지 모두 포함시키는 방식이다. 가장 단순한 방식이다.
• 장점
  • 단순하고 직관적이다.
  • 자식 테이블별로 조회 성능이 좋다.
  • 각 자식 테이블별로 필요한 컬럼들로만 직접 구성하므로 NOT NULL 제약 조건을 걸수있다.
• 단점
  • 전체 자식 데이터들을 모아서 보여줄때 UNION ALL 처리를 해야하기 때문에 쿼리가 복잡해진다.
  • 부모 속성으로 조회할때 어떤 자식 테이블의 데이터인지 알수없다.
  • 전체 자식 테이블을 하나로 모아 FK 관계를 맺는것이 불가능하다.
    • FK는 하나의 테이블과만 관계를 맺을 수 있다.
  • 각 자식테이블 PK가 AUTO_INCREMENT를 사용하기 때문에 아이디 중복 문제가 발생한다.
  • 컬럼 추가 및 변경시 모든 자식 테이블 변경이 필요하다.
• 피하면 좋은 방법

단일 테이블 전략 (Single Table Strategy)

• 부모와 모든 자식의 속성을 하나의 테이블에 통합하는 방식이다.
• 자식 데이터 타입을 컬럼으로 관리한다. 인덱스까지 추가하여 자식 데이터 조회를 위한 성능을 개선한다.
  • 위와 같은 컬럼을 구분자 컬럼 (Discriminator Column)이라 한다.
• 장점
  • 외부에서 FK를 맺을 수 있다.
  • 자식 데이터 조회가 쉽다.
  • 자식 데이터와 조인이 쉽다.
• 단점
  • NULL값이 많아진다. 테이블 크기도 커진다.
  • NOT NULL 제약을 걸 수 없음
  • 자식별 제약 관리가 어렵다.

조인 전략

• 부모 테이블과 자식 테이블을 분리하고, 조인을 통해 데이터를 조회하는 방식이다.
• 부모 테이블이 공통 속성을 갖고, 자식 테이블이 부모테이블의 PK를 FK로 갖는다.
• 공통정보 조회를 위해서는 부모 테이블만 조회하면 된다.
• 부모 테이블에 자식데이터 type 컬럼을 추가하여 관리할 수 있다.
  • 런타임에 자식 데이터 타입을 모르는 경우 모든 자식 테이블과 LEFT JOIN하여 필터링을 한다.
• 장점
  • 정규화됨
  • NOT NULL 제약 사용 가능
  • FK 활용 가능
  • 확장성 확보
• 단점
  • 조인 필요, 성능 우려
  • INSERT시 두번의 쿼리 필요 (부모, 자식)
  • 조인으로 인한 쿼리 복잡성 증가
• 실무에서는 단일 테이블 전략과 조인 전략중 필요에 따라 선택을 하는 것이 좋다.
• 자식이 많고, 고유속성이 많으면 조인
• 자식이 적고 고유 속성이 적으면 단일 테이블

동적 설계 - EAV(Entity-Attribute-Value)

• 자식 테이블이 과하게 많아지는 경우 관리의 어려움이 따른다.
  • 추적의 어려움은 물론 컬럼 구성 변경으로 인해 ALTER TABLE을 하는 것은 부담이 크다.
• 자식 테이블별로 예측 불가능한 갯수와 성격의 속성이 필요할 수 있다.
• 동일한 자식 테이블을 활용하는 유저 입장에서 전체 컬럼이 필요하지 않을 수 있다.
• 상속 관계가 적합한 경우
  • 서브타입 종류가 명확하고 제한적
  • 각 서브타입 속성이 안정적이고 자주 변하지 않음.
  • 서브타입별 속성에 대한 제약이 중요
  • 서브타입별 복잡한 조인이나 조회가 자주 필요
• 상속관계가 적합하지 않은 경우
  • 서브타입이 계속 늘어날 수 있음
  • 속성이 동적으로 추가 수정 삭제가 되어야함
  • 서브타입별로 필요한 속성 예측이 어려움
  • 유연성이 데이터 무결성보다 중요
  • 사용자가 직접 속성을 정의해야 함.
• EAV 방식은 속성 이름과 값을 열이 아닌 행에 저장한다.
• EAV의 구성
  1. Entity: 속성을 가지는 주체
  2. Attribute: 엔티티가 갖는 특성의 이름
  3. Value: 해당 속성의 실제 값
• 전통적인 테이블에서는 어트리뷰트가 컬럼명, Value가 셀 값이 된다.
• EAV에서는 어트리뷰트와 Value 모두 데이터로 저장된다.

CREATE TABLE product_attribute (
    attribute_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    attr_name VARCHAR(100) NOT NULL ,
    attr_value VARCHAR(500),
    FOREIGN KEY (product_id) REFERENCES product(product_id)
);

• 새로운 속성을 추가하려면 attr_name과 함께 새로운 행을 INSERT 하면 된다.
• 엔티티별로 다른 속성을 가질 수 있다.
  • product_id기준으로 쿼리를 하면 해당 데이터의 모든 속성들을 한번에 조회 가능하다.
• 속성 추가 시 발생하는 휴먼에러를 줄이기 위해 속성 정의 테이블을 따로 구축하는 것이 좋다.

CREATE TABLE attribute_definition (
    attr_def_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    category_id BIGINT,
    attr_name VARCHAR(100) NOT NULL,
    attr_label VARCHAR(100) NOT NULL,
    attr_type VARCHAR(20) NOT NULL,
    is_required BOOLEAN DEFAULT FALSE,
    is_searchable BOOLEAN DEFAULT FALSE,
    display_order INT DEFAULT 0,
    options VARCHAR(500),
    created_at DATETIME NOT NULL,
    FOREIGN KEY (category_id) REFERENCES category(category_id)
);

• EAV는 한계가 존재한다.
  • 효율적인 인덱싱 제한
  • 조인 성능 저하
  • 참조 무결성 제약의 어려움
  • 데이터타입 강제 어려움
• 최신 데이터베이스에서 지원하는 JSON이 나은 선택지가 될 수 있다.

JSON

• EAV를 조인하여 원본 데이터 정보를 조회하려면 CASE WHEN을 통해 하드코딩 데이터 분기처리가 필요하다.
• EAV의 가장 큰 문제는 모든 값이 VARCHAR로 저장된다는 것이다.
• JSON을 사용하는 경우 여러 속성을 하나의 컬럼에 모아 저장이 가능하다.

CREATE TABLE product_json (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL ,
    category VARCHAR(100) NOT NULL ,
    attributes JSON,
    created_at DATETIME NOT NULL
);

INSERT INTO product_json (name, category, attributes, created_at) VALUES
(
    '갤럭시 S24',
    '스마트폰',
    '{"screen_size": 6.2, "storage": 256, "color": "블랙"}',
    NOW()
);

• JSON 사용시 여러 장점이 있다.
  • 네이티브 타입 지원
  • 하나의 컬럼에 여러 속성 저장
  • JSON 함수로 데이터 추출
  • 중첩구조 표현
• JSON_OBJECT 함수를 사용하면 키값 쌍을 직접 정의할 수 있다.

INSERT INTO product_json (name, category, attributes, created_at) VALUES
(
    '아이폰 15',
    '스마트폰',
    JSON_OBJECT(
    'screen', 6.1,
    'storage', 256
    ),
    NOW()
);

• 쉼표 기반으로 구분된다.
• JSON_ARRAY를 사용하면 배열 데이터도 넣을 수 있다.

INSERT INTO product_json (name, category, attributes, created_at) VALUES
(
    '아이폰 16',
    '스마트폰',
    JSON_OBJECT(
    'screen', 6.1,
    'storage', 256,
    'ports', JSON_ARRAY('HDMI', 'USB-C', 'MagSafe')
    ),
    NOW()
);

• JSON_EXTRACT를 사용하면 특정 경로의 값을 추출할 수 있다.
  • 경로는 $로 시작하며 .로 키값을 지정한다.
  • JSON에 정의한 값의 타입 그대로 조회한다.

SELECT
    name,
    JSON_EXTRACT(attributes, '$.storage'),
    JSON_EXTRACT(attributes, '$.screen_size')
FROM product_json;

• JSON_VALUE는 별도의 타입 지정을 안하는 경우 문자열로 반환한다.
  • RETURNING을 사용하여 원하는 타입을 명시적으로 지정 가능하다.

SELECT
    name,
    JSON_VALUE(attributes, '$.screen_size' RETURNING DECIMAL) AS size1,
    JSON_VALUE(attributes, '$.screen_size') AS size2
FROM product_json;

• 이 외에 다양한 문법들이 존재하는데, 필요에 따라 리서치

JSON 인덱스와 성능 최적화 1. 가상컬럼 인덱스

• JSON은 유연하지만 인덱스를 걸수없어 성능 최적화가 어렵게 느껴질 수 있다.
• 하지만 JSON은 내부 데이터도 인덱스를 걸 수 있으며 빠르게 조회하는 것도 가능하다.
• 가상컬럼 인덱스와 멀티밸류 인덱스를 사용하면 된다.
• 가상컬럼 인덱스 예시를 먼저 확인해보자.

ADD COLUMN v_storage INT GENERATED ALWAYS AS (attributes->'$.storage') VIRTUAL;

• 가상 컬럼은 조회할때만 계산되는 컬럼이다. 실제 디스크에 저장되지 않아 저장공간을 거의 차지하지 않는다.
• 데이터베이스가 가상컬럼을 읽을때 항상 지정한 JSON 경로에서 직접 읽어온다는 것이다.

CREATE INDEX idx_v_storage ON product_json(v_storage);

• 기본 인덱스 문법으로 인덱스 생성이 가능하다.
• 생성컬럼은 VIRTUAL과 STORED 타입으로 구분되는데, 일반적으로 VIRTUAL을 많이 사용한다. (STORED는 디스크에 직접 저장한다.)
• 가상컬럼 실제 데이터는 저장되지 않지만 인덱스 정보는 디스크에 저장한다.

JSON 인덱스와 성능 최적화 2. 함수 기반 인덱스

• 컬럼 생성 및 인덱스 등록이 번거롭다면 함수 기반 인덱스도 고려할 수 있다.

CREATE INDEX idx_func_storage
ON product_json ((CAST(attributes->'$.storage' AS UNSIGNED)));

• 내부적으로는 가상컬럼 생성이 이루어진다.
• 인덱스 생성이기 때문에 타입을 반드시 알아야 한다.
• 사용 편의성을 위한 기능이다.

JSON 인덱스와 성능 최적화 3. 멀티 밸류 인덱스

• 멀티 밸류 인덱스는 JSON 배열에 있는 데이터를 검색할때 사용된다.
• 하나의 행에 여러 인덱스 키를 매핑해준다.

CREATE INDEX idx_ports
ON product_json ((CAST(attributes->'$.ports' AS CHAR(20) ARRAY)));

• CAST (..)를 통해 JSON 배열 요소 타입을 지정한다.
• 멀티밸류 인덱스를 통해 조회가 이루어지게 하려면 조회 시 다음 함수들을 사용해야 한다.
  1. MEMBER OF(): 특정값이 배열에 있는지 확인
  2. JSON_CONTAINS(): 특정 JSON이 포함되어 있는지 확인
  3. JSON_OVERLAPS(): 두 배열간에 겹치는 요소가 있는지 확인

SELECT product_id, name, attributes ->> '$.ports'
FROM product_json
WHERE 'HDMI' MEMBER OF(attributes->'$.ports');
-- ..
WHERE JSON_CONTAINS(attributes->'$.ports', '"USB-C"');

• 배열 내부 값을 고속으로 조회하게 된다.
• JSON 데이터만으로는 참조 무결성 제약이 불가능하다.
• 데이터 무결성은 CHECK 제약으로 어느정도 확보 가능하다.
• JSON은 잦은 속성 변경 / 스키마 수정 및 예측불가능성 / 데이터 스냅샷 저장 등의 경우에 사용된다.
• 자주 검색하고 정렬하는 데이터, 집계, 트랜잭션 및 무결성, 일관성 등을 보장하기는 어렵다.