포스트

물리적 모델링 실습

게시 업데이트
By 조지헌
24 분읽는 시간
물리적 모델링 실습

논리적 모델링

인덱스 설계

시나리오1: 로그인 및 중복 ID, 이메일 체크

1
2
3
4
5
6
7
8
9

-- 로그인 시
select member_id, password
from member
where login_id = "user123"

-- 이메일로 회원 정보 찾기
select member_id
from member
where email = "user1234@example.com"
  • 문제점: member 테이블에 회원이 100만 명이 있다면, 조회 시 마다 100만 개의 데이터를 모두 스캔 해야 함
  • 해결책: login_id와 email 컬럼은 UNIQUE 제약조건을 걸어 두었기에 자동으로 고유 인덱스를 생성함

시나리오2: 회원의 주문 목록 조회

1
2
3
4
5

-- member_id가 10번인 회원의 모든 주문을 최신순으로 조회
select *
from orders
where member_id = 10
order by ordered_at desc

인덱스 필요성

  • member_id 컬럼에 인덱스가 없으면 orders 테이블을 모두 스캔해야 함
  • 따라서, 인덱스를 걸어야 함
  • FK의 경우 인덱스가 생성되므로 생략 가능

시나리오3: 상품명으로 상품 검색

1
2
3

select *
from product
where product_name like = '노트북%';

인덱스 필요성

  • name 컬럼에 인덱스가 없다면, 검색할 때마다 전체 상품을 스캔해야 하므로 매우 느림 검색 경험을 제공

인덱스 추가

1

create index idx_product_name on product (product_name);

시나리오4: 관리자의 주문 상태 및 기간별 조회(복합 인덱스)

1
2
3
4
5

-- 2025년 7월 한 달간 취소된 주문을 조회
select *
from orders
where order_status = 'CANCELED'
  and ordered_at between '2025-07-01 00:00:00' and '2025-07-31 23:59:59'

인덱스 필요성

  • order_status와 ordered_at 각각에 단일 인덱스가 있다면, DB 옵티마이저는 둘 중 더 효율저깅라고 판단되는 인덱스 하나만 사용하게 됨
  • 두 개 이상의 컬럼이 where 절 조건으로 함께 자주 사용 될 때는 복합 인덱스를 만들어주는 것이 훨씬 효율적

복합 인덱스 생성

1

create index idx_order_status_ordered_at on orders (order_status, ordered_at);

시나리오5: 사용자의 주문 상태 및 기간별 조회

1
2
3
4
5
6

-- member_id가 10번인 최근 3개월 간 '배송 완료(COMPLETED)된 주문을 조회
select *
from orders
where member_id = 10
  and order_status = 'COMPLETED'
  and ordered_at >= date_sub(now(), interval 3 month)

인덱스를 추가하지 않는 이유: 데이터의 선택도와 비용 고려

  • member_id의 압도적으로 높은 선택도
    • order_status = 'COMPLETED": 전체 주문 데이터가 1억 건이라면, ‘배송 완료’ 상태인 주문은 수 천만 건에 달할 수 있으므로 데이터를 크게 줄이지 못함
    • member_id = 10: 전체 1억 건의 주문 중 특정 회원 ID가 10번인 주문은 많아야 수십, 수백개이므로 데이터를 크게 줄임
  • 인덱스 사용 후의 동작 과정
    • idx_member_id 인덱스를 사용하여 member_id가 10인 주문 데이터가 디스크의 어디에 저장되어 있는지 빠르게 찾아냄
    • 찾아낸 수백 건의 데이터만 메모리로 가져옴
    • 메모리에 올라온 이 소량의 데이터를 대상으로 order_status 등 필터링을 진행

인덱스의 비용: 모든 쿼리에 인덱스를 만들지 않는 이유

  • 저장 공간 비용
  • 쓰기 성능 저하

인덱스 추가를 결정하는 실무적 기준

  • 데이터 분포와 조회 효율성
    • 수백 건 이하: member_id 인덱스 만으로도 충분히 빠름
    • 수천 건 이상: member_id로 필터링을 해도 여전히 수천, 수만 건의 주문이 있을 경우 복합 인덱스 사용을 고려
    • 서비스 규모가 아주 큰 경우: 약간의 지연 시스템에 민감하게 반응하기 때문에 인덱스를 고려
  • 쓰기 작업의 빈도와 중요도
    • 복합 인덱스를 추가하면 INSERT, UPDATE 작업에 부하가 걸림
    • 전체적인 주문 처리 속도가 느려짐
    • 조회 성능의 개선 효과가 쓰기 성능 저하로 인한 손실보다 확실히 클때만 인덱스를 추가해야 함
  • 실제 쿼리 패턴과 성능 측정(가장 중요)
    • 느린 쿼리(Slow Query) 로그 분석
    • EXPLAIN 실행 계획 분석
    • 성능 테스트

핵심은 예상 가능한 확실한 경우가 아니라면 미리 짐작해서 최적화하지 말고, 문제가 발생하지 전에 데이터를 기반으로 판단하고 개선하는 것입니다.

역정규화

중복 컬럼 추가

문제 상황

주문 내역 조회는 가장 빈번하게 일어나는 핵심 기능 중 하나이다. 사용자는 마이페이지에서 자신의 주문 내역을 볼 때, 각 주문에 어떤 상품들이 포함되어 있는지 상품명과 함께 확인하고 싶어 한다.

1
2
3
4
5
6
7
8

SELECT oi.order_id,
       p.product_name, -- 상품명을 위해 반드시 product 테이블을 JOIN 해야 한다.
       oi.order_price,
       oi.order_quantity
FROM order_item oi
       JOIN
     product p ON oi.product_id = p.product_id
WHERE oi.order_id = 100;

order_item 테이블에 product_name 컬럼을 추가하여 상품명을 직접 저장하여 JOIN을 없앨 수 있습니다.

1단계: 컬럼 추가

  • order_item 테이블에 상품명을 저장할 product_name 컬럼을 추가
1
2

ALTER TABLE order_item
  ADD COLUMN product_name VARCHAR(100) NOT NULL COMMENT '주문 당시 상품명';

2단계; 데이터 저장 방식 변경

order_item 데이터를 생성할 때, 애플리케이션 로직에서 product 테이블을 한 번 조회하여 상품명을 가져온 뒤, order_item 테이블의 product_name 컬럼에 저장해 주어야 합니다.

1
2
3
4
5
6
7

-- 이제 product 테이블과의 JOIN이 필요 없다!
SELECT oi.order_id,
       oi.product_name, -- order_item 테이블에서 바로 상품명을 조회한다.
       oi.order_price,
       oi.order_quantity
FROM order_item oi
WHERE oi.order_id = 100;

파생 컬럼 추가(계산된 값 저장)

문제 상황

쇼핑몰 관리자는 주문 관리 페이지에서 각 주문의 총 결제 금액을 한눈에 파악하고 싶어한다. 이 총 금액을 기준으로 매출 통계를 내거나, 특정 금액 이상 주문한 고객을 필터링하는 기능이 필요하다. 고객의 경우도 자신의 주문 관리 페이지에서 각 주문의 총 결제 금액을 한눈에 파악하고 싶어 한다.

1
2
3
4
5
6
7
8

SELECT o.order_id,
       o.ordered_at,
       SUM(oi.order_price * oi.order_quantity) AS total_amount -- 매번 복잡한 계산이 필요하다
FROM orders o
       JOIN
     order_item oi ON o.order_id = oi.order_id
WHERE o.order_id = 100
GROUP BY o.order_id;

1단계: 컬럼 추가

  • orders 테이블에 총 주문 금액을 저장할 total_amount 컬럼을 추가
1
2

ALTER TABLE orders
  ADD COLUMN total_amount INT NOT NULL COMMENT '총 주문 금액';

2단계: 데이터 저장 방식 변경

  • order_item에 각 상품 정보를 저장
  • 저장된 order_item들을 바탕으로 총 주문 금액을 계산
  • 계산된 총 금액을 orders 테이블의 total_amount 컬럼에 저장

3단계: 조회 쿼리 개선

1
2
3
4
5
6

-- 계산 없이 orders 테이블에서 바로 총 금액을 조회한다.
SELECT order_id,
       ordered_at,
       total_amount
FROM orders
WHERE order_id = 100;

테이블 통합

문제 상황

orders와 delivery는 거의 항상 1:1관계 이므로 테이블을 합쳐 JOIN을 없애자는 개발자의 제안이 있었다고 가정해보겠습니다.

DB 반대 이유

  • 데이터의 생명주기가 다르다
  • 데이터의 변경 빈도가 다르다
  • 테이블의 책임과 응집도가 깨진다(SRP 위반)
  • 선택적 관계에 대한 처리
    • e-book이나 온라인 강의 같은 디지털 상품을 판매하게 된다면 배송은 NULL로 채워져야 함
  • 성능 이점이 크지 않음

결과

분리된 상태로 두자

테이블 정의서

member

제목내용
테이블 한글명회원
테이블 영문명member
테이블 설명쇼핑몰에 가입한 회원의 기본 정보를 저장하는 테이블
No.컬럼 한글명컬럼 영문명데이터 타입제약조건NULL 허용 여부기본 값비고
1회원IDmember_idBIGINTPK AUTO INCREMENT회원의 고유 식별자 (대리키)
2로그인IDlogin_idVARCHAR(50)UQ  회원이 로그인 시 사용하는 ID
3비밀번호passwordVARCHAR(255)   비밀번호 (반드시 암호화 하여 저장)
4회원명member_nameVARCHAR(50)   회원의 실명
5이메일emailVARCHAR(100)UQ  회원ㅇ 인증 및 소통을 위한 이메일
6주소addrVARCHAR(255) NULL 상품 배송을 위한 주소
7가입일created_atDATETIME   회원 가입 시점
8수정일updated_atDATETIME   데이터 수정 시 자동 갱신

product

제목내용
테이블 한글명상품
테이블 영문명product
테이블 설명쇼핑몰에서 판매하는 상품의 정보를 저장하는 테이블
No.컬럼 한글명컬럼 영문명데이터 타입제약조건NULL 허용 여부기본 값비고
1상품IDproduct_idBIGINTPK AUTO INCREMENT상품의 고유 식별자 (대리키)
2상품명product_nameVARCHAR(100)   상품의 이름
3상품 가격product_priceINT   상품의 현재 판매 가격
4재고 수량stock_quantityINT  0상품의 현재 재고 수량
5상품 등록일created_atDATETIME   상품이 시스템에 등록된 시점
6수정일updated_atDATETIME   데이터 수정 시 자동 갱신
인덱스 명컬럼비고
idx_product_nameproduct_name상품명 검색 성능 향상을 위한 인덱스

orders

제목내용
테이블 한글명주문
테이블 영문명orders
테이블 설명회원의 주문 정보를 저장하는 테이블
No.컬럼 한글명컬럼 영문명데이터 타입제약조건NULL 허용 여부기본 값비고
1주문IDorder_idBIGINTPK AUTO INCREMENT주문의 고유 식별자 (대리키)
2회원IDmember_idBIGINTFK  주문한 회원(member_member_id 참조)
3주문일시ordered_atDATETIME   고객이 주문한 비즈니스 시점의 시간, 백엔드 애플리케이션에서 전달
4주문 상태order_statusVARCHAR(20)  ORDERED예: ORDERED, CANCELED 등
5총 주문 금액total_amountINT   주문 상품 금액의 총합(역정규화)
6생성일created_atDATETIME   주문 데이터가 시스템에 생성된 시점
7수정일updated_atDATETIME   주문 상태 변경 등 데이터 수정 시 자동 갱신
인덱스 명컬럼비고
idx_order_status_ordered_atorder_status,ordered_at관리자의 주문 상태 및 기간별 조회 성능 향상을 위한 복합 인덱스

order_item

제목내용
테이블 한글명주문 항목
테이블 영문명order_item
테이블 설명특정 주문에 포함된 개별 상품들의 상세 내역을 저장하는 테이블
No.컬럼 한글명컬럼 영문명데이터 타입제약조건NULL 허용 여부기본 값비고
1주문 상품IDorder_item_idBIGINTPK AUTO INCREMENT주문 상품의 고유 식별자 (대리키)
2주문IDorder_idBIGINTFK  해당 상품이 속한 주문(orders.order_id 참조)
3상품IDproduct_idBIGINTFK  주문된 상품(product.product_id 참조)
4주문 상품명product_nameVARCHAR(100)   주문 당시의 상품명 (스냅샷) (역정규화)
5주문 가격order_priceINT   주문 당시의 개별 상품 가격 (스냅샷)
6주문 수량order_quantityINT   주문한 상품의 개수
7생성일created_atDATETIME   데이터가 시스템에 생성된 시점
8수정일updated_atDATETIME   데이터 수정 시 자동 갱신

delivey

제목내용
테이블 한글명배송
테이블 영문명delivery
테이블 설명주문에 대한 배송 정보를 저장하는 테이블(주문과 1:1 관계)
No.컬럼 한글명컬럼 영문명데이터 타입제약조건NULL 허용 여부기본 값비고
1배송IDdelivery_idBIGINTPK AUTO INCREMENT배송의 고유 식별자 (대리키)
2주문IDorder_idBIGINTFK, UQ  배송될 주문(orders.order_id 참조)
3배송 상태delivery_statusVARCHAR(20)  READY예: READY, SHIPPING, COMPLETED 등
4운송장 번호tracking_noVARCHAR(50) NULL 배송 시작 시 택배사에서 발급하는 번호
5배송지ship_addrVARCHAR(255)   상품이 실제 배송될 주소
6생성일created_atDATETIME   배송 데이터가 시스템에 생성된 시점
7수정일updated_atDATETIME   배송 상태 변경 등 데이터 수정 시 자동 갱신

pay

제목내용
테이블 한글명결제
테이블 영문명pay
테이블 설명주문에 대한 결제 정보를 저장하는 테이블(주문과 1:1 관계)
No.컬럼 한글명컬럼 영문명데이터 타입제약조건NULL 허용 여부기본 값비고
1결제IDpay_idBIGINTPK AUTO INCREMENT결제의 고유 식별자 (대리키)
2주문IDorder_idBIGINTFK, UQ  결제 대상 주문(orders.order_id 참조)
3결제 수단pay_methodVARCHAR(50)   예: CREDIT_CARD, BANK_TRANSFER 등
4결제 금액pay_amountINT   실제 은행, 카드 등으 ㅣ결제가 이루어진 금액, 포인트 할인 등의 금액은 제외된다
5결제 상태pay_statusVARCHAR(20)   예: PAID, FAILED, CANCELED 등
6결제 일시paid_atDATETIME NULL 결제가 최종 완료된 시점, 비즈니스 관점에서 시간, 은행, 신용카드 사에서 결제된 시간
7생성일created_atDATETIME   결제 데이터가 시스템에 생성된 시점
8수정일updated_atDATETIME   결제 상태 변경 등 데이터 수정 시 자동 갱신

생성일과 수정일

실무에서는 모든 테이블에 생성일(created_at)과 수정일(updated_at) 컬럼을 넣는 것이 거의 표준처럼 여겨집니다.

등록자(created_by), 수정일(updated_by)도 같이 저장하면 누가 등록하고 수정했는지 쉽게 찾을 수 있어 유용합니다. 이부분은 DB에선 자동화할 수 없고 Spring Data JPA 기술을 사용하면 자동화 할 수 있습니다.

DDL과 DB 만들기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101

DROP TABLE IF EXISTS pay;
DROP TABLE IF EXISTS delivery;
DROP TABLE IF EXISTS order_item;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS product;

CREATE TABLE member
(
  member_id   BIGINT       NOT NULL AUTO_INCREMENT, -- 회원 ID (PK)
  login_id    VARCHAR(50)  NOT NULL,                -- 로그인 ID
  password    VARCHAR(255) NOT NULL,                -- 비밀번호 (암호화)
  member_name VARCHAR(50)  NOT NULL,                -- 이름
  email       VARCHAR(100) NOT NULL,                -- 이메일
  addr        VARCHAR(255) NULL,                    -- 주소
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (member_id),
  UNIQUE KEY uq_login_id (login_id),
  UNIQUE KEY uq_email (email)
);

CREATE TABLE product
(
  product_id     BIGINT       NOT NULL AUTO_INCREMENT, -- 상품 ID (PK)
  product_name   VARCHAR(100) NOT NULL,                -- 상품명
  product_price  INT          NOT NULL,                -- 가격
  stock_quantity INT          NOT NULL DEFAULT 0,      -- 재고 수량
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (product_id),
  INDEX          idx_product_name (product_name)       -- 상품명 검색용 인덱스
);

CREATE TABLE orders
(
  order_id     BIGINT      NOT NULL AUTO_INCREMENT,                   -- 주문 ID (PK)
  member_id    BIGINT      NOT NULL,                                  -- 회원 ID (FK)
  ordered_at   DATETIME    NOT NULL,                                  -- 주문일 (애플리케이션에서 생성)
  order_status VARCHAR(20) NOT NULL DEFAULT 'ORDERED',                -- 주문 상태
  total_amount INT         NOT NULL,                                  -- 총 주문 금액 (역정규화)
  created_at   DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (order_id),
  CONSTRAINT fk_orders_member FOREIGN KEY (member_id)
    REFERENCES member (member_id),
  INDEX        idx_order_status_ordered_at (order_status, ordered_at) -- 관리자용 주문 조회 인덱스
);

CREATE TABLE order_item
(
  order_item_id  BIGINT       NOT NULL AUTO_INCREMENT, -- 주문 상품 ID (PK)
  order_id       BIGINT       NOT NULL,                -- 주문 ID (FK)
  product_id     BIGINT       NOT NULL,                -- 상품 ID (FK)
  product_name   VARCHAR(100) NOT NULL,                -- 주문 당시 상품명 (역정규화)
  order_price    INT          NOT NULL,                -- 주문 당시 가격
  order_quantity INT          NOT NULL,                -- 주문 수량
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (order_item_id),
  CONSTRAINT fk_order_item_orders FOREIGN KEY (order_id)
    REFERENCES orders (order_id),
  CONSTRAINT fk_order_item_product FOREIGN KEY (product_id)
    REFERENCES product (product_id)
);

CREATE TABLE delivery
(
  delivery_id     BIGINT       NOT NULL AUTO_INCREMENT, -- 배송 ID (PK)
  order_id        BIGINT       UNIQUE NOT NULL,                -- 주문 ID (FK)
  delivery_status VARCHAR(20)  NOT NULL DEFAULT 'READY',-- 배송 상태
  tracking_no     VARCHAR(50) NULL,                     -- 운송장 번호
  ship_addr       VARCHAR(255) NOT NULL,                -- 배송지
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (delivery_id),
  UNIQUE KEY uq_delivery_order_id (order_id),
  CONSTRAINT fk_delivery_orders FOREIGN KEY (order_id)
    REFERENCES orders (order_id)
);

CREATE TABLE pay
(
  pay_id     BIGINT      NOT NULL AUTO_INCREMENT, -- 결제 ID (PK)
  order_id   BIGINT      UNIQUE NOT NULL,                -- 주문 ID (FK, Unique)
  pay_method VARCHAR(50) NOT NULL,                -- 결제 수단
  pay_amount INT         NOT NULL,                -- 결제 금액
  pay_status VARCHAR(20) NOT NULL,                -- 결제 상태
  paid_at    DATETIME NULL,                       -- 결제 완료일
  created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (pay_id),
  UNIQUE KEY uq_pay_order_id (order_id),          -- 주문 하나당 결제는 하나만 존재해야 함 (1:1) 관계 보장
  CONSTRAINT fk_pay_orders FOREIGN KEY (order_id)
    REFERENCES orders (order_id)
);

ERD

참고

DB
[인프런] 김영한의 실전 데이터베이스 - 설계1편 RDBMS
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.