반정규화와 성능 모델링 - 파생 컬럼 이력 테이블 슈퍼타입 서브타입 변환 (SQLD 기본 5장)
이 장에서 배우는 것
4장에서 한 사실을 한 곳에만 두도록 표를 나눴다. 그 결과 조회 한 번에 조인이 서너 개씩 붙는다. 느려진 화면을 두고 "정규화를 풀자"는 말이 나오는 순간이 반정규화의 출발점이다. 이 장은 반정규화를 마지막 수단으로 다룬다. 중복을 들이면 그 중복을 맞춰 두는 비용을 영원히 치러야 하기 때문이다.
- 반정규화 전에 검토할 대안(인덱스, 뷰, 파티션, 캐시)과 반정규화 절차를 정리한다.
- 테이블·컬럼·관계 반정규화 기법과, 파생 컬럼·이력 플래그가 치르는 정합성 비용을 실행으로 확인한다.
- 기본 키 컬럼 순서가 실행 계획을 바꾸는 모습과 슈퍼타입·서브타입 변환 방식을 비교한다.
핵심 개념
주문 목록 화면이 느리다는 신고가 들어왔다. 개발자는 주문 합계를 매번 계산하지 말고 주문 테이블에 저장하자고 제안했다. 합리적으로 들린다. 그러나 반품, 수량 변경, 할인 쿠폰 취소처럼 주문 상세가 바뀌는 모든 경로에서 그 합계를 함께 고쳐야 한다. 하나라도 놓치면 2장에서 본 것처럼 조용히 어긋난다.
반정규화 절차
- 대상 조사: 자주 쓰이는 조회인가, 넓은 범위를 읽는가, 통계성 조회인가, 조인이 지나치게 많은가를 본다.
- 다른 방법 검토: 인덱스 조정, 뷰, 클러스터링, 파티셔닝, 애플리케이션 캐시로 풀리는지 먼저 본다.
- 반정규화 적용: 위로 안 될 때만 테이블·컬럼·관계를 반정규화하고, 정합성을 지킬 방법(트리거, 배치, 애플리케이션)을 함께 정한다.
그림 · 반정규화 판단 순서 — 느린 조회를 찾으면 먼저 대상을 조사하고, 인덱스·뷰·파티션·캐시 같은 다른 방법을 검토한다. 그래도 안 될 때만 반정규화하고, 어긋나지 않게 맞출 수단을 함께 정한다.
반정규화 기법
| 대상 | 기법 | 설명 |
|---|---|---|
| 테이블 | 병합 | 1:1 관계, 1:M 관계, 슈퍼·서브타입 테이블을 하나로 합친다 |
| 테이블 | 분할 | 수직 분할(자주 쓰는 컬럼과 큰 컬럼을 나눔), 수평 분할(행을 기간·지역별로 나눔, 파티션) |
| 테이블 | 추가 | 중복 테이블, 통계 테이블, 이력 테이블, 부분 테이블 |
| 컬럼 | 추가 | 중복 컬럼, 파생 컬럼, 이력의 최신 여부 컬럼, PK 를 쪼갠 일반 컬럼, 오작동 복구용 이전 값 컬럼 |
| 관계 | 중복 관계 추가 | 여러 단계를 거쳐야 가는 관계를 직접 잇는 외래 키를 더한다 |
대량 데이터와 슈퍼타입·서브타입 변환
행이 매우 많은 테이블은 파티션으로 나눈다. 범위(날짜), 목록(지역 코드), 해시(고르게 분산) 방식이 있다. 컬럼이 많아 한 행이 큰 테이블은 자주 쓰는 컬럼끼리 수직 분할한다.
고객을 개인·법인으로 나누는 슈퍼타입·서브타입 모델은 물리 모델에서 세 방식 중 하나로 바꾼다.
- 하나로 통합(Single Type, All in One): 한 테이블에 구분 컬럼을 두고 서브타입 전용 컬럼은 NULL 을 허용한다. 전체를 한꺼번에 조회하는 일이 많을 때.
- 서브타입별 분리(Plus Type): 개인 고객, 법인 고객 테이블을 따로 두고 공통 컬럼을 각각 넣는다. 서브타입별로 따로 처리하는 일이 많을 때.
- 일대일 분리(One to One Type): 슈퍼타입 테이블과 서브타입 테이블을 모두 두고 1:1 로 잇는다. 공통 처리와 개별 처리가 비슷하게 섞일 때. 대신 조인이 늘어난다.
그림 · 슈퍼타입·서브타입을 테이블로 바꾸는 세 방식 — 하나로 통합하면 조인이 없지만 서브타입 전용 컬럼이 NULL 로 찬다. 서브타입별로 나누면 NULL 은 없지만 공통 조회에 UNION 이 필요하고, 일대일로 나누면 공통·개별을 모두 두는 대신 조인이 늘어난다.
표 · 슈퍼타입·서브타입 변환 방식 비교
| 방식 | 테이블 | NULL | 잘 맞는 경우 |
|---|---|---|---|
| 하나로 통합 | 1개 (구분 컬럼) | 서브타입 전용 컬럼에 많음 (예제: 개인 2명 중 biz_no 가 있는 행 0개) | 전체를 한꺼번에 조회할 때 |
| 서브타입별 분리 | 서브타입 수만큼 | 거의 없음 | 서브타입별로 따로 처리할 때 |
| 일대일 분리 | 슈퍼 1 + 서브타입 수 | 거의 없음 | 공통·개별 처리가 섞일 때 (조인 증가) |
분산 데이터베이스
데이터를 여러 장소에 나눠 두어도 사용자는 하나의 DB 처럼 쓰게 하는 것이 목표다. 이를 위해 분할·위치·지역 사상·중복·장애·병행 투명성을 갖춘다. 성능 관점에서는 자주 쓰는 데이터를 가까운 곳에 복제하는 대신, 복제본을 맞추는 비용이 생긴다. 반정규화와 같은 거래다.
예제 스키마
파생 컬럼용 주문 테이블, 가격 이력, 지역·일자별 매출, 개인·법인을 한 테이블에 합친 거래처(party)가 있다. daily_sales 의 기본 키는 (region, day) 순서다.
-- 5장: 반정규화와 성능 (가상 데이터)
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
order_total INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE order_item (
order_id INTEGER NOT NULL REFERENCES orders(order_id),
line_no INTEGER NOT NULL,
qty INTEGER NOT NULL,
unit_price INTEGER NOT NULL,
PRIMARY KEY (order_id, line_no)
);
CREATE TABLE price_hist (
product TEXT NOT NULL,
valid_from TEXT NOT NULL,
price INTEGER NOT NULL,
PRIMARY KEY (product, valid_from)
);
CREATE TABLE daily_sales (
region TEXT NOT NULL,
day TEXT NOT NULL,
amount INTEGER NOT NULL,
PRIMARY KEY (region, day)
) WITHOUT ROWID;
CREATE TABLE party (
party_id INTEGER PRIMARY KEY,
party_type TEXT NOT NULL CHECK (party_type IN ('P', 'C')),
name TEXT NOT NULL,
birth TEXT,
biz_no TEXT
);
INSERT INTO orders (order_id, customer) VALUES (1, '정민아');
INSERT INTO price_hist VALUES
('공책', '2026-01-01', 3500), ('공책', '2026-06-01', 4000),
('연필', '2026-01-01', 900), ('연필', '2026-08-15', 1000);
INSERT INTO daily_sales VALUES
('부산', '2026-09-01', 70), ('부산', '2026-09-02', 40),
('서울', '2026-09-01', 120), ('서울', '2026-09-02', 90);
INSERT INTO party VALUES
(1, 'P', '홍다온', '1998-04-11', NULL),
(2, 'P', '문세아', '2001-12-30', NULL),
(3, 'C', '맑은문구(주)', NULL, '123-45-67890');| party_id | party_type | name | birth | biz_no |
|---|---|---|---|---|
| 1 | P | 홍다온 | 1998-04-11 | NULL |
| 2 | P | 문세아 | 2001-12-30 | NULL |
| 3 | C | 맑은문구(주) | NULL | 123-45-67890 |
SQL과 실행 결과
파생 컬럼을 트리거로 맞추기
CREATE TRIGGER trg_item_ins AFTER INSERT ON order_item
BEGIN
UPDATE orders
SET order_total = order_total + NEW.qty * NEW.unit_price
WHERE order_id = NEW.order_id;
END;
INSERT INTO order_item VALUES (1, 1, 2, 4000);
INSERT INTO order_item VALUES (1, 2, 1, 15000);
SELECT order_id, order_total FROM orders;
-- 트리거는 INSERT 만 잡는다. 수량을 고치면?
UPDATE order_item SET qty = 3 WHERE order_id = 1 AND line_no = 1;
SELECT o.order_total,
(SELECT SUM(qty * unit_price) FROM order_item WHERE order_id = 1) AS computed
FROM orders o WHERE o.order_id = 1;실행 결과:
order_id order_total
-------- -----------
1 23000
order_total computed
----------- --------
23000 27000INSERT 두 번에는 합계가 23,000 으로 잘 따라왔다. 수량을 3 으로 고치자 실제 합계는 27,000 인데 저장 값은 23,000 에 머물렀다. 트리거가 INSERT 만 잡고 있었기 때문이다. 파생 컬럼은 원본이 바뀌는 모든 경로(INSERT, UPDATE, DELETE)를 빠짐없이 막아야 정합성이 지켜진다.
이력 테이블의 최신 값: 계산할 것인가, 저장할 것인가
정규화된 이력에서 최신 가격은 상관 서브쿼리로 구한다(10장에서 자세히 본다).
SELECT p.product, p.price
FROM price_hist p
WHERE p.valid_from = (SELECT MAX(q.valid_from) FROM price_hist q WHERE q.product = p.product)
ORDER BY p.product;실행 결과:
product price
------- -----
공책 4000
연필 1000조회가 매우 잦다면 최신 여부 컬럼을 더하는 반정규화를 검토한다. 이제 새 가격이 생길 때마다 이전 행의 표시를 끄고 새 행을 켜는 두 작업을 한 트랜잭션에 묶어야 한다.
ALTER TABLE price_hist ADD COLUMN is_current INTEGER NOT NULL DEFAULT 0;
UPDATE price_hist SET is_current = 1
WHERE (product, valid_from) IN (SELECT product, MAX(valid_from) FROM price_hist GROUP BY product);
SELECT product, price FROM price_hist WHERE is_current = 1 ORDER BY product;
-- 새 가격이 생기면 두 행을 한 트랜잭션에서 바꿔야 한다
BEGIN;
UPDATE price_hist SET is_current = 0 WHERE product = '연필' AND is_current = 1;
INSERT INTO price_hist VALUES ('연필', '2026-09-20', 1100, 1);
COMMIT;
SELECT product, valid_from, price, is_current
FROM price_hist WHERE product = '연필' ORDER BY valid_from;실행 결과:
product price
------- -----
공책 4000
연필 1000
product valid_from price is_current
------- ---------- ----- ----------
연필 2026-01-01 900 0
연필 2026-08-15 1000 0
연필 2026-09-20 1100 1기본 키 컬럼 순서와 실행 계획
EXPLAIN QUERY PLAN SELECT amount FROM daily_sales WHERE region = '서울' AND day = '2026-09-01';
EXPLAIN QUERY PLAN SELECT amount FROM daily_sales WHERE day = '2026-09-01';실행 결과:
QUERY PLAN
`--SEARCH daily_sales USING PRIMARY KEY (region=? AND day=?)
QUERY PLAN
`--SCAN daily_sales두 컬럼 모두 등호로 주면 기본 키로 바로 찾는다(SEARCH). 두 번째 컬럼 day 만 주면 전체를 읽는다(SCAN). 복합 키는 앞 컬럼부터 정렬되어 있으므로 앞 컬럼 조건 없이는 범위를 좁힐 수 없다. 날짜로만 조회하는 일이 많다면 인덱스를 더하거나, 설계 단계에서 키 순서를 (day, region) 으로 두는 것을 검토한다.
CREATE INDEX ix_daily_day ON daily_sales(day);
EXPLAIN QUERY PLAN SELECT amount FROM daily_sales WHERE day = '2026-09-01';
SELECT region, amount FROM daily_sales WHERE day = '2026-09-01' ORDER BY region;실행 결과:
QUERY PLAN
`--SEARCH daily_sales USING INDEX ix_daily_day (day=?)
region amount
------ ------
부산 70
서울 120하나로 통합한 슈퍼타입의 NULL
SELECT party_type, COUNT(*) AS n, COUNT(birth) AS has_birth, COUNT(biz_no) AS has_biz_no
FROM party GROUP BY party_type ORDER BY party_type;실행 결과:
party_type n has_birth has_biz_no
---------- - --------- ----------
C 1 0 1
P 2 2 0개인 행에는 사업자번호가, 법인 행에는 생일이 늘 NULL 이다. 통합 방식은 조인 없이 전체를 읽을 수 있는 대신 서브타입 전용 컬럼에 NOT NULL 을 걸 수 없다. "개인이면 생일 필수" 같은 규칙은 CHECK 로 따로 적어야 한다.
표준과 구현의 차이
아래 Oracle·SQL Server 동작은 비교 설명이며 실행하지 않았다.
| 항목 | SQLite(실행) | Oracle | SQL Server |
|---|---|---|---|
| 파티션 테이블 | 없음 | 범위·목록·해시·복합 파티션 | 파티션 함수와 파티션 구성표 |
| 기본 키 순으로 행 저장 | WITHOUT ROWID 테이블 | 인덱스 구성 테이블(IOT) | 클러스터형 인덱스(기본 키 기본값) |
| 집계 결과를 저장해 두는 객체 | 없음(테이블 + 트리거로 흉내) | 구체화된 뷰(Materialized View) | 인덱싱된 뷰 |
| 트리거 시점 | BEFORE, AFTER, INSTEAD OF(뷰) | BEFORE, AFTER, INSTEAD OF | AFTER, INSTEAD OF |
[구현 차이] Oracle 의 구체화된 뷰나 SQL Server 의 인덱싱된 뷰는 "통계 테이블 추가" 반정규화를 DB 가 대신 맞춰 주는 기능이다. 반정규화 전에 검토할 대안 목록에 들어간다.
시험에서 헷갈리는 지점
판단 1. "조회가 느리면 먼저 반정규화를 적용하고, 그래도 느리면 인덱스를 검토한다"
틀렸다. 순서가 반대다. 인덱스·뷰·파티션·캐시 같은 대안을 먼저 검토하고, 그래도 안 될 때 반정규화한다.
판단 2. "수평 분할은 컬럼 단위로, 수직 분할은 행 단위로 나누는 것이다"
틀렸다. 반대다. 수평 분할은 행을 나누고(2025년 주문, 2026년 주문), 수직 분할은 컬럼을 나눈다(자주 쓰는 컬럼, 큰 본문 컬럼).
판단 3. "복합 기본 키에서 등호 조건으로 자주 쓰이는 컬럼을 앞에 두는 것이 유리하다"
맞다. 위 실행 계획처럼 앞 컬럼 조건이 없으면 기본 키를 범위 좁히기에 쓰지 못한다. 범위 조건(BETWEEN, >)으로 쓰이는 컬럼은 뒤에 둔다.
연습 문제
- 다음을 반정규화 절차 순서대로 놓으라. (가) 파생 컬럼 추가 (나) 조인 개수와 조회 빈도 조사 (다) 인덱스·파티션으로 해결되는지 검토
- 게시판 테이블에 제목·작성자·작성일과 수십 KB 의 본문이 함께 있다. 목록 화면은 본문을 읽지 않는다. 어떤 반정규화 기법이 어울리는가?
- 개인 고객과 법인 고객이 각각 전혀 다른 화면과 배치에서만 처리되고, 둘을 함께 조회하는 일은 거의 없다. 슈퍼타입·서브타입 변환 방식 중 무엇을 고르는가?
- 예제의 INSERT 트리거만 있는 상태에서 주문 1에 (1, 1, 2, 500) 을 넣고, 그 주문의 상세를 모두 지운 뒤
order_total을 조회하라. 값을 먼저 예측하라. daily_sales를 날짜로만 조회하는 일이 대부분이다. 기본 키를 바꾸지 않고 해결하는 방법과, 설계 단계라면 고를 방법을 각각 쓰라.
정답과 해설
1. (나) → (다) → (가).
2. 수직 분할. 본문을 별도 테이블(게시글 번호, 본문)로 떼어 내면 목록 조회가 읽는 양이 크게 준다.
3. 서브타입별 분리(Plus Type). 각 처리가 자기 테이블만 읽으면 되고, 전용 컬럼에 NOT NULL 도 걸 수 있다.
4. 1000. INSERT 에서 2×500 = 1,000 이 더해졌고, DELETE 에는 트리거가 없어 빼지 않았다. 실제 상세는 0건인데 합계는 1,000 이다.
CREATE TRIGGER trg_item_ins AFTER INSERT ON order_item
BEGIN
UPDATE orders
SET order_total = order_total + NEW.qty * NEW.unit_price
WHERE order_id = NEW.order_id;
END;
INSERT INTO order_item VALUES (1, 1, 2, 500);
DELETE FROM order_item WHERE order_id = 1;
SELECT order_total FROM orders WHERE order_id = 1;실행 결과:
order_total
-----------
10005. 기본 키를 두고 day 에 인덱스를 추가한다(위 ix_daily_day 예제). 설계 단계라면 기본 키 순서를 (day, region) 으로 둔다. 둘 다 정합성 비용이 없는 대안이므로 반정규화보다 먼저 검토한다.
다음 장에서는 모델링의 마지막 주제로, NULL 을 허용할지 판단하는 기준과 트랜잭션 단위를 다룬다.