CS 스터디 7주차: 정규화, 조인, 인덱스

주문 목록에 고객 이름을 보여주려면 어디에 이름을 저장해야 할까요? 주문 행마다 넣으면 조회는 간단하지만, 같은 고객이 주문할 때마다 이름이 반복됩니다. 이름을 바꾸려면 여러 행을 고쳐야 합니다.

고객 이름을 별도 테이블에 두면 중복은 줄어듭니다. 대신 주문을 읽을 때 두 테이블을 연결해야 합니다. 데이터가 많아지면 필요한 주문을 찾는 경로도 중요해집니다. 이 흐름에서 정규화는 어디에 저장할지, 조인은 어떻게 함께 읽을지, 인덱스는 어떻게 찾을지에 답합니다.

먼저 이 세 개념의 바탕이 되는 테이블과 키부터 보겠습니다. 글에 나오는 고객과 주문은 원리를 설명하기 위한 작은 예시이며, 특정 서비스에서 측정한 데이터는 아닙니다.

데이터베이스의 기본 단위: 테이블, 행, 열

데이터베이스는 관련된 데이터를 저장하고 조회할 수 있게 조직한 집합입니다. 이를 관리하는 소프트웨어가 DBMS(Database Management System)입니다. PostgreSQL 같은 관계형 DBMS(RDBMS)는 데이터를 테이블로 다루고, 여러 사용자의 읽기와 쓰기를 조정합니다. PostgreSQL 관계형 데이터 개념

행과 열은 무엇을 나타낼까요?

테이블은 처음에는 스프레드시트처럼 생각해도 됩니다. orders_flat에서 행(row) 하나는 주문 한 건이고, order_id, customer_id, customer_name, item_name은 열(column)입니다. 열에는 bigint, text 같은 데이터 타입을 정합니다.

테이블 이름·열 이름·타입·제약조건을 포함한 구조가 스키마(schema)입니다. 주문이 생기면 행이 늘어나지만, 열의 구조를 바꾸려면 스키마를 변경해야 합니다.

이 구조에서 order_id는 주문을 구별하고, customer_id는 주문한 고객을 가리킵니다. 두 값이 모두 숫자여도 역할은 다릅니다. 이 차이는 뒤에서 기본 키와 외래 키로 구체화됩니다.

SQL은 어떤 일을 할까요?

SQL은 데이터를 읽고 바꾸는 언어입니다. 다음 쿼리는 고객 1의 주문에서 두 열을 선택하고, 주문 번호 순으로 결과를 정렬합니다.

SELECT order_id, item_name
FROM orders_flat
WHERE customer_id = 1
ORDER BY order_id;

SELECT는 결과에 보여줄 열, FROM은 읽을 테이블, WHERE는 남길 행의 조건입니다. ORDER BY가 없으면 결과 행의 순서는 보장되지 않습니다. SQL에는 행을 추가하는 INSERT, 값을 바꾸는 UPDATE, 행을 지우는 DELETE도 있습니다. PostgreSQL SQL 튜토리얼

정규화: 같은 사실을 어디에 저장할까요?

고객 이름이 주문마다 반복되면

주문 한 건을 한 행에 저장한다고 가정해 보겠습니다.

order_id	customer_id	customer_name	item_name
101	1	김민지	키보드
102	1	김민지	마우스
103	2	이준	모니터

order_id는 주문을 식별합니다. 반면 고객 이름은 고객에 대한 사실입니다. customer_id가 1이면 customer_name은 김민지로 정해집니다. 이를 customer_id → customer_name이라는 함수 종속으로 표현합니다. 주문이 늘수록 같은 이름도 반복됩니다.

고객 이름을 바꾸면 어디를 수정해야 할까요?

같은 고객이 두 번 주문한 상태에서 시작합니다. 단계를 눌러 데이터가 달라지는 모습을 보세요.

① 중복된 표 ② 한 행만 수정 ③ 분리 후 수정

orders_flat · 고객 이름이 주문마다 저장됨

order_id	customer_id	customer_name	item_name
101	1	김민지	키보드
102	1	김민지	마우스
103	2	이준	모니터

고객 1의 이름이 두 행에 중복됩니다. 이름이 바뀌면 두 행 모두 수정해야 합니다.

고객 1의 이름을 한 행에서만 바꾸면 같은 고객 ID에 두 이름이 남습니다.

이것이 갱신 이상입니다. 고객의 사실을 customers에, 주문의 사실을 orders에 두고 customer_id로 연결하면 이름을 한 곳에서 관리할 수 있습니다.

기본 키와 외래 키로 관계를 표현하기

CREATE TABLE customers (
  customer_id bigint PRIMARY KEY,
  customer_name text NOT NULL
);

CREATE TABLE orders (
  order_id bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (customer_id),
  item_name text NOT NULL
);

PRIMARY KEY는 각 행을 구별하는 기본 키입니다. NULL이나 중복 값을 허용하지 않습니다. orders.customer_id의 REFERENCES는 외래 키 제약으로, 존재하는 고객만 주문에서 가리키도록 검사합니다. 여러 주문이 같은 고객을 가리킬 수 있으므로 외래 키 값은 중복될 수 있습니다. NOT NULL은 그 열을 비워 둘 수 없게 합니다. PostgreSQL 제약조건 문서

뒤의 조인 예시에는 다음 세 고객과 세 주문을 사용합니다. 박서연은 고객 테이블에만 있고 아직 주문이 없습니다.

INSERT INTO customers (customer_id, customer_name)
VALUES (1, '김민아'), (2, '이준'), (3, '박서연');

INSERT INTO orders (order_id, customer_id, item_name)
VALUES (101, 1, '키보드'), (102, 1, '마우스'), (103, 2, '모니터');

숫자가 같으면 같은 고객을 가리킵니다

orders.customer_id참조customers.customer_id

주문 101 1 고객 1 김민아

주문 102 1 고객 1 김민아

주문 103 2 고객 2 이준

외래 키는 두 테이블 사이의 참조가 유효한지 검사합니다. 뒤에서 사용할 JOIN은 조회할 때 연결 조건에 맞는 결과 행을 구성합니다.

이 예시는 몇 정규형에 해당할까요?

원래 표에서는 order_id → customer_id → customer_name으로 이어집니다. 고객 이름이 주문 ID에 직접 의존하는 대신 고객 ID를 거쳐 정해지는 이행 종속입니다. 테이블을 나누면 고객 이름은 고객 키에 직접 의존하고, 주문에는 고객을 참조하는 키만 남습니다. 이는 제3정규형에서 다루는 문제를 보여줍니다.

정규형 전체를 이 예시 하나로 판정할 수는 없습니다. 제1정규형은 한 칸에 여러 값을 넣는 문제를, 제2정규형은 복합 키의 일부에만 의존하는 속성을 따로 다룹니다. IBM의 정규화 설명은 각 단계와 갱신 이상의 관계를 예시로 설명합니다.

고객과 주문을 한 표에만 두면 주문이 없는 고객을 등록하기 어렵습니다. 이는 삽입 이상입니다. 마지막 주문을 지울 때 고객 정보까지 사라지는 것은 삭제 이상입니다. 분리하면 고객의 존재와 주문의 존재를 따로 기록할 수 있습니다.

그렇다고 모든 중복을 없애야 하는 것은 아닙니다. 주문 당시의 상품명이나 가격을 거래 기록으로 보존하려면 현재 상품 정보와 별도로 저장할 이유가 있습니다. 어떤 사실의 원본을 어디에 둘지 먼저 정해야 합니다.

트랜잭션: 여러 변경을 어디까지 한 작업으로 볼까요?

테이블을 나누면 한 번의 업무가 여러 SQL 문장에 걸칠 수 있습니다. 앞의 3명·3건 데이터에서 새 고객 4와 첫 주문 104를 함께 저장한다고 가정하겠습니다. 고객 추가만 성공하고 주문 추가가 실패하면, 업무가 끝나지 않았는데 고객만 남습니다.

트랜잭션은 여러 변경을 하나의 작업 단위로 묶습니다. PostgreSQL에서는 BEGIN으로 시작하고, 모두 성공하면 COMMIT으로 확정합니다. 중간에 실패하면 ROLLBACK으로 취소할 수 있습니다. 아래의 두 경로는 같은 시작 상태에서 각각 성공과 실패를 보여줍니다.

같은 시작, 다른 결과: 커밋과 롤백

시작 상태 · 고객 3명 / 주문 3건

두 문장 모두 성공

BEGIN고객 4 추가주문 104 추가COMMIT ✓

고객 4 주문 104

두 변경 모두 반영 고객 4명 / 주문 4건

두 번째 문장 실패

BEGIN고객 4 추가주문 104 추가 ✕ 실패ROLLBACK ↶

고객 4

앞선 고객 추가도 취소 고객 3명 / 주문 3건

BEGIN;

INSERT INTO customers (customer_id, customer_name)
VALUES (4, '최하나');

INSERT INTO orders (order_id, customer_id, item_name)
VALUES (104, 4, '헤드셋');

COMMIT;

위 코드는 두 문장이 모두 성공한 경로입니다. 두 번째 INSERT가 실패했다면 COMMIT 대신 ROLLBACK을 실행합니다. 그러면 먼저 성공한 고객 추가도 취소됩니다. 여러 변경을 전부 반영하거나 전부 취소하는 성질이 원자성(atomicity)입니다.

다른 트랜잭션이 진행 중인 변경을 어떻게 볼지는 격리 수준과 연결됩니다. 확정한 변경을 장애 후에도 보존하는 성질은 지속성입니다. 위 그림은 그중 원자성만 다룹니다. 뒤의 조인 예시는 고객 4를 추가하기 전의 데이터를 사용합니다. PostgreSQL 트랜잭션 튜토리얼

조인: 나뉜 행을 어떤 조건으로 연결할까요?

고객과 주문을 분리한 뒤 주문 내역에 고객 이름을 붙이려면, customer_id가 같은 행을 연결합니다. 여기서 조인의 결과는 조건에 맞는 행의 쌍입니다. 고객에게 주문이 두 건이면 결과에도 두 행이 나옵니다.

주문이 있는 고객만 읽기: INNER JOIN

INNER JOIN은 양쪽에서 조건에 맞는 행만 결과에 넣습니다.

SELECT c.customer_name, o.order_id, o.item_name
FROM customers AS c
INNER JOIN orders AS o
  ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

고객 1의 주문 101과 102는 각각 고객 1과 연결됩니다. 주문이 없는 박서연은 결과에 나타나지 않습니다.

아래 장면에서는 그중 주문 102 한 행을 따라가며 연결 조건이 결과에 어떻게 반영되는지 봅니다.

INNER JOIN · 주문 102

일시정지

O

orders주문 테이블

order_idcustomer_iditem_name

1011키보드

1021마우스

1032모니터

ON =

C

customers고객 테이블

customer_idcustomer_name

1김민아

2이준

3박서연

orders.customer_id = 1 customers.customer_id = 1

✓

결과 행같은 customer_id를 가진 행을 연결합니다

order_idcustomer_nameitem_name

102김민아마우스

주문 102의 customer_id = 1이 고객 1과 연결되어 결과 행이 만들어집니다. 행 하나를 따라가는 설명용 장면이며 실제 실행 순서나 처리 시간을 나타내지 않습니다.

주문이 없는 고객도 남기기: LEFT JOIN

고객 목록을 기준으로 주문이 없는 사람까지 남겨야 한다면 LEFT JOIN을 사용합니다. 아래 그림은 두 조인의 결과에서 박서연의 행이 어떻게 달라지는지 보여줍니다.

주문하지 않은 고객은 결과에 남을까요?

왼쪽 고객 3명과 오른쪽 주문 3건을 customer_id로 연결합니다.

customers

customer_id	customer_name
1	김민아
2	이준
3	박서연 · 주문 없음

orders

order_id	customer_id	item_name
101	1	키보드
102	1	마우스
103	2	모니터

INNER JOIN LEFT JOIN

결과 · 조건이 맞는 쌍만 남김

customer_name	order_id	item_name
김민아	101	키보드
김민아	102	마우스
이준	103	모니터

박서연은 연결되는 주문이 없어 결과에 나오지 않습니다.

INNER JOIN에서는 박서연이 빠지고, LEFT JOIN에서는 주문 열이 NULL인 채 남습니다.

SELECT c.customer_name, o.order_id, o.item_name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

LEFT JOIN에서 연결되는 주문이 없으면 주문 쪽 열은 NULL입니다. 여기에 WHERE o.order_id IS NOT NULL을 추가하면 주문이 없는 고객은 다시 빠집니다. 고객을 모두 남길 목적이라면 WHERE 조건까지 살펴야 합니다. PostgreSQL 조인 튜토리얼

NULL은 빈 문자열이나 숫자 0과 다릅니다. 값이 없거나 알려져 있지 않은 상태이므로 o.order_id = NULL이 아닌 o.order_id IS NULL로 검사합니다. 이 조건을 이용하면 주문이 없는 고객만 찾을 수도 있습니다.

조인은 어떤 행을 결과에 남길지를 정하는 SQL의 의미입니다. 데이터베이스가 그 결과를 실제로 만드는 과정에서는 중첩 루프, 해시 조인, 병합 조인처럼 서로 다른 실행 방법을 선택할 수 있습니다. 조인 문법만 보고 한 가지 실행 방법을 단정할 수는 없습니다.

인덱스: 필요한 행에 어떻게 도달할까요?

한 건을 찾는 경로

별도로 주문이 101부터 108까지 있는 테이블에서 order_id = 107인 행을 찾는다고 가정하겠습니다. 테이블의 행을 차례로 읽을 수도 있고, 인덱스를 통해 후보 위치를 좁힐 수도 있습니다.

같은 주문을 찾는 경로를 비교해 보세요

화면에 들어오면 두 탐색 경로가 자동으로 진행되고 반복됩니다. 오른쪽은 B-tree 탐색을 단순화한 그림입니다.

주문 103 찾기 주문 107 찾기

전체 탐색 · 앞에서부터 확인

1. 101
2. 102
3. 103
4. 104
5. 105
6. 106
7. 107
8. 108

확인한 행 0개

인덱스 경로 · 범위를 좁혀 이동

분기: 105보다 작은가?

101 · 102 · 103 · 104

105 · 106 · 107 · 108

찾은 행: 107

진행한 단계 0/3

주문 107을 찾습니다. 두 경로가 자동으로 진행됩니다.

이 그림은 탐색 원리를 설명하는 모형입니다. 단계 수는 실제 디스크 읽기 횟수나 쿼리 실행 시간이 아닙니다.

순차 탐색은 행을 차례로 살펴보고, 인덱스 탐색은 값의 범위를 좁혀 주문 107에 도달합니다.

인덱스는 테이블의 모든 열을 복제한 표가 아닙니다. PostgreSQL의 B-tree 인덱스는 내부 페이지를 따라 내려가고, 리프 페이지에서 테이블 행을 가리키는 항목을 찾습니다. 그림은 범위를 고르고 행에 도달하는 과정만 단순화했습니다. 실제 페이지 구성이나 디스크 읽기 횟수를 나타내지는 않습니다. PostgreSQL B-tree 구조

앞서 만든 orders.order_id는 기본 키입니다. PostgreSQL은 기본 키에 고유 B-tree 인덱스를 자동으로 만듭니다. 같은 열에 동일한 인덱스를 다시 만들 필요는 없습니다. 반면 고객별 주문을 자주 찾는다면 customer_id 인덱스를 검토할 수 있습니다. PostgreSQL 기본 키 문서

인덱스가 있어도 항상 쓰지는 않습니다

인덱스 선택은 어떤 조건으로 얼마나 많은 행을 찾는지에 달렸습니다. order_id = 107처럼 한 건을 찾을 때와 모든 주문을 읽을 때는 유리한 경로가 다릅니다. customer_id = 1에 맞는 주문이 전체의 대부분이라면 인덱스를 거쳐 많은 테이블 행을 다시 읽는 것보다 순서대로 읽는 편이 저렴할 수 있습니다.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN
SELECT order_id, item_name
FROM orders
WHERE customer_id = 1;

표가 작거나 조건에 맞는 행이 많으면 순차 스캔의 비용이 더 낮을 수 있습니다. 인덱스는 저장 공간을 쓰고, 데이터를 추가·수정·삭제할 때마다 유지 비용도 듭니다. 쿼리와 데이터 규모에 맞춰 EXPLAIN으로 실행 계획을 확인해야 합니다.

실행 계획의 Seq Scan은 테이블을 순서대로 읽는 방식, Index Scan은 인덱스를 이용하는 방식입니다. EXPLAIN의 비용 값은 밀리초가 아닌 추정치입니다. 실제 실행을 비교할 때는 같은 데이터와 환경에서 EXPLAIN (ANALYZE, BUFFERS)를 볼 수 있습니다. 그 수치를 위 그림의 단계 수와 직접 연결해서는 안 됩니다. PostgreSQL EXPLAIN 설명

세 개념을 함께 보면

고객과 주문을 나누면 고객 이름의 원본을 한곳에 둘 수 있습니다. 기본 키와 외래 키는 각 행과 관계의 규칙을 정하고, 트랜잭션은 여러 변경을 함께 확정하거나 취소합니다. 조인은 나뉜 행을 조회 결과에서 연결하고, 인덱스는 필요한 행을 찾는 경로를 제공합니다.

설계 순서는 언제나 똑같지 않습니다. 먼저 데이터의 의미와 조회 요구를 정하고, 테이블 사이의 관계를 모델링한 뒤, 실제로 자주 실행되는 쿼리와 실행 계획을 보며 인덱스를 선택하는 편이 좋습니다. 이 글의 작은 표는 원리를 보여주지만 실제 성능을 예측해 주지는 않습니다.

썸네일 사진: Taylor Vick / Unsplash