관계와 조인의 대응, 트랜잭션 범위

1과목의 마지막 자리입니다. 앞의 세 노트가 모델을 세우는 법이었다면 이 노트는 그 모델이 SQL로 어떻게 내려앉는가를 다룹니다. SQLP가 1과목을 두는 이유가 여기에 있습니다 — 3과목에서 실행계획을 읽고 힌트를 붙이기 전에, 그 실행계획이 왜 그런 모양인지가 모델에 이미 적혀 있기 때문입니다. 조인이 다섯 번인 것도, 조인 조건이 세 칼럼인 것도, 아우터 조인이 필요한 것도 모두 ERD 한 장에서 미리 읽을 수 있습니다.

PK-FK와 조인 조건

관계가 내려앉는 자리

논리 모델의 관계선은 물리 모델에서 외래키 칼럼 하나로 내려앉고, SQL에서는 조인 조건 하나가 됩니다. 1:M 관계라면 M쪽 테이블이 1쪽의 기본키를 외래키로 갖고, 그 두 칼럼을 등호로 이은 것이 조인 조건입니다.

-- 부서 1 : 사원 M 관계가 그대로 조인 조건이 된다
SELECT e.사번, e.사원명, d.부서명
  FROM 사원 e
  JOIN 부서 d ON d.부서번호 = e.부서번호;

이 대응이 일대일이라는 것이 요점입니다. ERD에 관계선이 넷 그려져 있고 그 넷을 모두 타야 원하는 값에 닿는다면, 그 SQL에는 조인이 최소 넷 들어갑니다. 반대로 조인이 여섯인 SQL을 보면 관계선 여섯을 탄 것이거나, 같은 테이블을 두 번 부른 것이거나, 조인 조건을 빠뜨려 관계 없는 테이블이 끌려 들어온 것입니다.

조인 칼럼 수와 인덱스

식별자가 몇 칼럼인지는 그대로 조인 조건의 칼럼 수가 됩니다. 첫 노트에서 본 식별 관계가 여기로 이어집니다 — 식별 관계로 세 단계를 내려온 테이블의 기본키가 세 칼럼이면, 그 테이블을 부모와 잇는 조인 조건도 세 개입니다.

SELECT *
  FROM 주문상세 d
  JOIN 상세이력 h
    ON h.주문번호 = d.주문번호
   AND h.상세순번 = d.상세순번;

조인 조건이 늘면 인덱스도 따라 늡니다. 기본키 인덱스가 (주문번호, 상세순번, 이력순번)으로 서 있을 때 주문번호만으로 찾는 조회는 선두 칼럼을 쓰므로 인덱스를 탑니다. 하지만 상세순번만으로 찾는 조회는 선두 칼럼이 빠져 있어 그 인덱스를 제대로 쓰지 못합니다. 복합 인덱스는 앞에서부터 이어져야 쓸모가 있고, 그 순서를 정하는 것이 식별자 구성입니다.

정규화 수준과 조인 개수

정규형이 늘리는 조인

정규화는 한 테이블에 뭉쳐 있던 속성을 결정자별로 갈라 놓는 작업입니다. 갈라 놓은 만큼 값을 다시 모으려면 조인해야 하므로, 정규화 수준이 높을수록 한 조회가 타는 조인 개수가 늘어납니다.

주문 화면 하나를 그리는 데 주문·주문상세·상품·상품분류·회원·배송지를 모두 읽어야 한다면 조인이 다섯입니다. 여섯 테이블이 각각 필요한 값을 하나씩 갖고 있기 때문이지, SQL을 잘못 쓴 것이 아닙니다.

반정규화가 줄이는 조인

반대 방향도 성립합니다. 주문상세에 상품명을 복사해 두면 상품 테이블로 가는 조인 하나가 사라지고, 그만큼 읽을 블록이 줄어듭니다. 앞 노트에서 본 반정규화의 대가 — 갱신 경로가 둘이 되는 것 — 을 여기서 조인 하나와 맞바꾸는 셈입니다.

그래서 「조인이 많아서 느리다」는 진단은 절반만 맞습니다. 조인 개수는 모델이 정한 값이고, 실제로 느린 원인은 대개 각 조인 단계에서 걸러지는 건수 쪽입니다.

필수·선택 관계와 아우터 조인

필수 관계

참여가 반드시 일어나는 관계에서는 외래키가 NOT NULL이고 부모 행도 반드시 있습니다. 그러면 내부 조인만으로 충분하고, 조인 때문에 행이 사라지는 일이 없습니다.

선택 관계

외래키가 Null을 허용하거나 짝이 없을 수 있는 관계입니다. 이 자리에 내부 조인을 쓰면 짝 없는 행이 결과에서 조용히 빠집니다. 그래서 선택 관계는 아우터 조인의 후보이고, ERD에서 선택 표시를 확인하는 것이 그대로 조인 방식을 고르는 근거가 됩니다.

아우터 조인의 조건절

아우터 조인을 쓰고도 결과가 내부 조인과 같아지는 실수가 흔합니다. 오른쪽 테이블에 대한 필터를 WHERE 절에 두면, 짝이 없어 Null로 채워진 행이 그 조건에서 걸러지기 때문입니다.

-- 배송지가 없는 회원이 사라진다 — 사실상 내부 조인
SELECT m.회원번호, b.주소
  FROM 회원 m
  LEFT JOIN 배송지 b ON b.회원번호 = m.회원번호
 WHERE b.사용여부 = 'Y';

-- 배송지가 없는 회원도 남는다
SELECT m.회원번호, b.주소
  FROM 회원 m
  LEFT JOIN 배송지 b
    ON b.회원번호 = m.회원번호
   AND b.사용여부 = 'Y';

규칙은 한 줄입니다 — 보존할 쪽이 아닌 테이블의 조건은 ON 절에 둡니다. 조인 조건과 필터 조건을 구분해 두는 습관이 여기서 값을 합니다. 내부 조인에서는 두 조건을 어느 절에 적어도 결과가 같지만, 아우터 조인에서는 절이 곧 의미가 됩니다.

트랜잭션 범위

모델이 묶는 단위

모델은 어떤 행들이 함께 생기고 함께 사라지는지도 표현합니다. 주문과 주문상세가 필수 관계로 묶여 있다면, 주문상세 없는 주문은 존재할 수 없다는 뜻이고, 그 둘의 입력은 한 트랜잭션 안에서 끝나야 합니다. 반대로 회원과 배송지가 선택 관계라면 회원만 먼저 넣고 배송지는 나중에 넣어도 모델을 어기지 않습니다.

여기서 트랜잭션은 전부 반영되거나 전부 취소되어야 하는 작업의 묶음입니다. 필수 관계는 그 묶음의 경계를 모델 위에 그려 놓은 표시입니다.

커밋 단위 읽기

두 테이블이 한 트랜잭션으로 묶여 있으면 중간 상태가 다른 세션에 보이지 않아야 합니다. 그래서 필수 관계로 묶인 자식 테이블을 부모와 따로 커밋하는 배치는, 커밋 사이의 짧은 시간 동안 모델이 금지한 상태 — 상세 없는 주문 — 를 실제로 만들어 냅니다. 그 시간에 집계가 돌면 건수가 맞지 않습니다.

반대 방향의 실수도 있습니다. 선택 관계로 이어진 테이블까지 한 트랜잭션에 욱여넣으면 잠금이 잡혀 있는 시간이 길어집니다. 대량 처리에서 커밋을 언제 끊을지 정할 때, 끊어도 되는 자리와 끊으면 안 되는 자리를 알려 주는 것이 바로 이 필수·선택 표시입니다. 모델을 읽지 않고 「1,000건마다 커밋」처럼 건수로만 끊으면 필수 관계의 한가운데가 잘릴 수 있습니다.

모델 오류와 성능

세 가지 경로

셋째 줄이 가장 크게 터집니다. 1,000행 테이블과 2,000행 테이블을 조인 조건 없이 이으면 $1{,}000 \times 2{,}000 = 2{,}000{,}000$ 행이 만들어집니다. 이것을 카티션 곱이라 하고, 조인 조건을 빠뜨렸을 때 나오는 결과입니다. 복합키를 쓰는 모델에서는 조건 세 개 중 하나만 빠져도 부분적으로 같은 일이 벌어지므로, 조인 조건의 개수가 키 칼럼 수와 맞는지 세어 보는 것이 첫 점검입니다.

조인 순서 짐작하기

실행계획을 보기 전에 모델만으로 짐작할 수 있는 것이 있습니다. 조인은 한 번에 두 집합씩 처리되므로, 먼저 읽는 쪽에서 많이 걸러질수록 뒤로 넘어가는 건수가 줄어듭니다.

• 조회 조건이 걸린 테이블이 먼저입니다. 기본 엔터티에 조건이 걸려 있으면 그쪽이 시작점이 됩니다.
• 1:M 관계에서 1쪽에 조건이 있으면 1쪽부터, M쪽에 선택적인 조건이 있으면 M쪽부터가 유리합니다.
• 사건·행위 엔터티는 대개 가장 크므로 마지막에 붙는 쪽이 자연스럽습니다.

짐작이 실행계획과 다르면 둘 중 하나입니다 — 통계 정보가 실제와 다르거나, 모델을 읽으며 놓친 카디널리티가 있는 것입니다. 어느 쪽이든 확인할 자리를 좁혀 주므로, 모델을 먼저 읽는 습관이 3과목에서 시간을 벌어 줍니다.

연습 문제

1. 부서 10건과 사원 100건이 있고, 사원 중 7명은 부서번호가 NULL이다. 두 테이블을 부서번호로 내부 조인했을 때와 사원을 보존하는 아우터 조인을 했을 때의 결과 건수는?
   ① 100건, 100건
   ② 93건, 100건
   ③ 93건, 93건
   ④ 100건, 107건

   ②. 내부 조인은 짝이 있는 행만 남기므로 부서번호가 NULL인 7명이 빠져 $100 - 7 = 93$ 건입니다. 사원을 보존하는 아우터 조인은 그 7명을 부서 칼럼이 NULL인 채로 살려 두므로 100건입니다.
2. 다음 SQL의 문제는?
   SELECT m.회원번호, b.주소 FROM 회원 m LEFT JOIN 배송지 b ON b.회원번호 = m.회원번호 WHERE b.사용여부 = 'Y'
   ① 문법 오류가 난다
   ② 배송지가 없는 회원이 결과에서 빠진다
   ③ 회원이 중복 출력된다
   ④ 인덱스를 타지 못한다

   ②. 짝이 없는 회원의 b.사용여부는 NULL이고, NULL과 'Y'의 비교는 알 수 없음이라 WHERE 절에서 걸러집니다. 결과적으로 내부 조인과 같아지므로 조건을 ON 절로 옮겨야 합니다.
3. 주문 200건이 있고 주문마다 주문상세가 평균 4건씩 달려 있다. 주문과 주문상세를 내부 조인한 결과 건수에 가장 가까운 것은?
   ① 200건
   ② 204건
   ③ 800건
   ④ 160,000건

   ③. 1:M 조인은 M쪽 건수만큼 행이 늘어나므로 $200 \times 4 = 800$ 건입니다. 주문상세는 모두 $800$ 건이므로, ④는 조인 조건을 빠뜨렸을 때 나오는 카티션 곱 $200 \times 800 = 160{,}000$ 건입니다.
4. 기본키가 (주문번호, 상세순번, 이력순번)인 테이블에서 인덱스를 가장 효율적으로 쓰지 못하는 조회 조건은?
   ① 주문번호 = :a
   ② 주문번호 = :a AND 상세순번 = :b
   ③ 상세순번 = :b
   ④ 주문번호 = :a AND 상세순번 = :b AND 이력순번 = :c

   ③. 복합 인덱스는 선두 칼럼부터 이어질 때 범위를 좁힐 수 있습니다. 선두인 주문번호가 조건에 없으면 인덱스 전체를 훑거나 테이블을 훑게 됩니다.
5. 모델의 필수 관계가 SQL 설계에 주는 정보로 옳은 것은?
   ① 그 관계는 아우터 조인으로 읽어야 한다
   ② 그 두 테이블의 입력은 한 트랜잭션으로 묶여야 한다
   ③ 외래키 칼럼에 NULL이 들어올 수 있다
   ④ 두 테이블을 반정규화해야 한다

   ②. 필수 관계는 자식 없이 부모가 존재할 수 없다는 선언이므로, 그 상태를 만들지 않으려면 함께 커밋되어야 합니다. ①·③은 선택 관계의 성질이고 ④는 관계의 필수 여부와 무관한 결정입니다.
6. 관계선이 다섯 그려진 ERD를 보고 어떤 조회의 SQL을 작성했더니 조인이 여덟 번 들어갔다. 이 차이가 생길 수 있는 경우를 두 가지 들고, 그중 어느 쪽이 오류인지 판단 근거와 함께 서술하시오.

   첫째는 같은 테이블을 여러 번 부른 경우입니다. 상위부서와 상위의 상위를 찾는 재귀 관계나, 출발지·도착지처럼 한 테이블을 역할별로 두 번 조인하면 관계선 하나가 조인 여러 개가 됩니다. 둘째는 조인 조건을 빠뜨린 채 테이블이 FROM 절에 들어온 경우입니다. 앞쪽은 정상이고 뒤쪽이 오류인데, 가르는 근거는 조인 조건의 유무입니다. 각 테이블이 앞선 집합과 등호 조건으로 이어져 있으면 역할이 다른 정상 조인이고, 조건 없이 이름만 올라와 있으면 카티션 곱이 되어 결과 건수가 폭증합니다. 실행계획에서는 MERGE JOIN CARTESIAN이나 예상 건수가 비정상적으로 큰 단계로 드러납니다. 채점은 두 경우를 든 것에 각 1점, 조인 조건 유무로 가른 근거에 2점입니다.