정규화와 반정규화, Null과 인조식별자

앞의 두 노트가 모델을 세우는 재료였다면 이 노트는 그 재료를 어디까지 쪼갤 것인가를 다룹니다. 정규화는 쪼개는 쪽이고 반정규화는 도로 붙이는 쪽인데, 둘 다 조회 성능과 정합성을 맞바꾸는 결정입니다. 여기에 Null과 인조식별자를 함께 두는 이유가 있습니다 — 셋 모두 「모델에서 한 칸을 어떻게 채울 것인가」라는 같은 질문의 다른 얼굴이고, 셋 모두 SQL의 결과 건수를 조용히 바꾸는 자리이기 때문입니다.

함수적 종속과 이상현상

함수적 종속

함수적 종속은 한 속성의 값이 정해지면 다른 속성의 값이 하나로 정해지는 관계입니다. 사번이 정해지면 사원명이 하나로 정해지므로 사원명은 사번에 함수적으로 종속되고, 이것을 다음과 같이 적습니다.

왼쪽을 결정자, 오른쪽을 종속자라 부릅니다. 정규화는 결국 「결정자가 아닌 것에 매달린 속성을 떼어내는 작업」이라 한 줄로 말할 수 있습니다.

세 가지 이상현상

정규화를 하지 않은 테이블에서는 저장 자체가 일을 냅니다. 이것을 이상현상이라 부르고 셋으로 갈립니다.

부서번호와 부서명을 사원 테이블에 함께 담아 두었다고 해 봅시다. 아직 사원이 없는 부서는 등록할 방법이 없고(삽입 이상), 부서명을 바꾸면 그 부서 사원 행을 전부 고쳐야 하며(갱신 이상), 마지막 사원이 퇴사해 행이 지워지면 부서가 있었다는 사실까지 사라집니다(삭제 이상).

정규형

제1정규형

모든 속성이 원자값을 갖는 상태입니다. 한 칸에 값이 여럿 들어 있거나 반복되는 칼럼 그룹이 있으면 어깁니다. 앞 노트에서 본 다중값 속성이 여기서 걸립니다.

제2정규형

제1정규형이면서 부분 함수 종속이 없는 상태입니다. 부분 함수 종속은 복합키의 일부에만 매달린 속성이 있는 것이므로, 주식별자가 단일 속성이면 제1정규형을 만족하는 순간 제2정규형도 만족합니다.

(주문번호, 상품코드)가 주식별자인 주문상세에 상품명이 들어 있으면, 상품명은 상품코드에만 매달려 있습니다. 같은 상품이 여러 주문에 나올 때마다 상품명이 되풀이되고, 상품명이 바뀌면 그 상품이 실린 모든 주문상세 행을 고쳐야 합니다.

제3정규형

제2정규형이면서 이행 함수 종속이 없는 상태입니다. 이행 함수 종속은 $A \rightarrow B$ 이고 $B \rightarrow C$ 여서 결과적으로 $A \rightarrow C$ 가 되는 것입니다. 사원 테이블에 부서번호와 부서명이 함께 있으면 사번이 부서번호를, 부서번호가 부서명을 결정하므로 부서명은 이행 종속입니다.

BCNF

BCNF는 모든 결정자가 후보키인 상태입니다. 제3정규형은 「기본키가 아닌 속성이 다른 비주요 속성에 매달리는 것」을 막지만, 후보키가 여럿이고 서로 겹칠 때는 그것만으로 부족합니다. 결정자 노릇을 하는데 후보키는 아닌 속성이 남을 수 있고, BCNF는 그 자리를 마저 정리합니다.

정규화가 진행될수록 테이블 수는 늘고 한 테이블의 칼럼 수는 줄어듭니다. 그 대가가 조인입니다 — 원래 한 테이블에서 읽던 값을 이제 두세 테이블을 이어야 읽습니다.

반정규화

판단 기준

반정규화는 조회 성능을 얻으려고 정규화된 모델을 일부러 되돌리는 작업입니다. 중복을 허용하는 결정이므로 순서가 있습니다.

1. 그 조회가 실제로 자주 일어나고 느린지 확인합니다.
2. 인덱스·조인 방식·SQL 수정으로 풀리는지 먼저 시도합니다.
3. 뷰나 클러스터, 파티셔닝처럼 모델을 건드리지 않는 방법을 검토합니다.
4. 그래도 남으면 반정규화합니다.

넷째 자리에 와서야 손대는 이유는, 반정규화가 갱신 경로를 하나 더 만드는 결정이기 때문입니다. 되돌리기도 쉽지 않습니다.

방법은 테이블을 병합하거나 분할하는 것, 칼럼을 중복시키는 것, 집계 결과를 미리 저장하는 것으로 나뉩니다.

부작용

중복된 값은 원본이 바뀔 때 함께 바뀌어야 합니다. 그 갱신을 트리거로 걸든 애플리케이션이 하든, 한 곳이라도 빠지면 그날부터 두 값이 다릅니다. 그리고 어긋난 사실은 조회 시점에 드러나지 않습니다 — 오류 없이 틀린 값이 나올 뿐입니다.

여기에 갱신 비용도 붙습니다. 읽기를 한 번 줄이려고 쓰기를 두 번으로 늘린 것이므로, 갱신이 잦은 테이블에서는 총 비용이 오히려 커집니다. 그래서 반정규화가 잘 맞는 자리는 「자주 읽고 거의 안 바뀌는」 값입니다.

Null

3값 논리

Null은 값이 없다는 표시이지 0이나 빈 문자열이 아닙니다. 그래서 Null이 낀 비교는 참도 거짓도 아닌 알 수 없음이 되고, SQL의 조건 판정은 참·거짓·알 수 없음의 세 값으로 돌아갑니다.

WHERE 급여 = NULL이 아무 행도 못 찾는 이유가 이것입니다. 판정 결과가 알 수 없음이고, WHERE 절은 참인 행만 남기기 때문입니다. Null을 찾으려면 IS NULL을 씁니다. 산술도 마찬가지여서 Null이 하나라도 끼면 결과는 Null입니다.

집계 함수와 조건절

집계 함수는 Null을 세지 않습니다. 이 한 줄이 세 가지 결과를 낳습니다.

• COUNT(*)는 행을 세므로 Null도 셉니다. COUNT(칼럼)은 그 칼럼이 Null이 아닌 행만 셉니다.
• SUM은 Null을 건너뛰고 더합니다. 대상이 전부 Null이면 0이 아니라 Null입니다.
• AVG의 분모는 행 수가 아니라 Null이 아닌 값의 개수입니다. Null을 0으로 치고 싶다면 AVG(NVL(칼럼, 0))처럼 미리 바꿔야 합니다.

NOT IN은 더 조용합니다. 서브쿼리 결과에 Null이 하나라도 있으면 모든 비교가 알 수 없음이 되어 한 건도 나오지 않습니다. 오류가 아니라 빈 결과라서 원인을 찾기 어렵고, 그래서 NOT EXISTS로 바꿔 쓰는 것이 안전합니다.

-- 부서번호에 NULL이 섞여 있으면 결과가 0건이 된다
SELECT * FROM 사원 WHERE 부서번호 NOT IN (SELECT 부서번호 FROM 폐지부서);

-- NULL이 섞여 있어도 의도대로 동작한다
SELECT * FROM 사원 s
 WHERE NOT EXISTS (SELECT 1 FROM 폐지부서 d WHERE d.부서번호 = s.부서번호);

정렬에서 Null이 어디에 서는지는 DBMS마다 다릅니다. 자리를 못 박아야 하면 NULLS FIRST나 NULLS LAST를 명시합니다.

조인에서의 Null

조인 조건에 쓰인 칼럼이 Null이면 그 행은 어느 짝과도 맺어지지 않습니다. 앞 노트에서 본 선택 관계가 여기로 이어집니다 — 외래키가 Null을 허용한다는 것은 곧 내부 조인에서 그 행이 사라진다는 뜻입니다. 아우터 조인으로 살려 두더라도 반대편 칼럼은 Null로 채워지므로, 그 값을 그대로 집계에 넘기면 다시 세지 않는 값이 됩니다.

본질식별자와 인조식별자

두 식별자

업무에서 자연히 생겨나 그 자체로 대상을 구별하는 것이 본질식별자이고, 그런 값이 마땅치 않거나 너무 길어 일련번호를 새로 만들어 붙인 것이 인조식별자입니다. 주민등록번호나 사업자등록번호는 본질식별자, 주문상세_ID 같은 시퀀스 값은 인조식별자입니다.

인조식별자가 늘리는 것

인조식별자는 편합니다. 키가 한 칼럼으로 짧아지고, 업무 규칙이 바뀌어도 키가 흔들리지 않으며, 자식 테이블이 물려받을 칼럼도 하나뿐입니다. 대가는 셋입니다.

첫째, 유일성이 사라집니다. 인조식별자는 넣을 때마다 새 값이 나오므로 업무적으로 같은 행을 두 번 넣어도 막히지 않습니다. 그래서 본질식별자에 유니크 인덱스를 따로 걸어야 하고, 인덱스가 하나 늘어납니다.

둘째, 그 인덱스 때문에 저장 공간과 갱신 비용이 함께 늡니다. 인조식별자의 기본키 인덱스와 본질식별자의 유니크 인덱스가 나란히 서고, 행을 넣을 때마다 둘 다 갱신됩니다.

셋째, 조회 경로가 갈립니다. 업무는 여전히 본질식별자로 찾는데 자식 테이블은 인조식별자를 물고 있어, 상위 조건으로 하위를 찾을 때 조인이 한 번 더 붙습니다.

정리하면 인조식별자는 키를 짧게 만드는 대신 정합성 유지를 인덱스에 떠넘기는 선택입니다. 떠넘겼다는 사실을 잊고 유니크 인덱스를 걸지 않으면 중복 데이터가 조용히 쌓입니다.

연습 문제

1. (주문번호, 상품코드)가 기본키인 주문상세에 상품명이 들어 있다. 이 테이블이 어기는 정규형은?
   ① 제1정규형
   ② 제2정규형
   ③ 제3정규형
   ④ BCNF

   ②. 상품명이 복합키의 일부인 상품코드에만 매달려 있으므로 부분 함수 종속입니다. 부분 함수 종속을 없앤 상태가 제2정규형입니다.
2. 급여 칼럼에 값이 순서대로 100, 200, NULL, 300, NULL인 사원 5명이 있다. SELECT COUNT(*), COUNT(급여), SUM(급여), AVG(급여) FROM 사원의 결과는?
   ① 5, 5, 600, 120
   ② 5, 3, 600, 200
   ③ 3, 3, 600, 200
   ④ 5, 3, 600, 120

   ②. COUNT(*)는 행을 세므로 5, COUNT(급여)는 Null이 아닌 값만 세므로 3입니다. 합은 $100 + 200 + 300 = 600$ 이고, 평균의 분모는 행 수 5가 아니라 Null이 아닌 개수 3이므로 $600 \div 3 = 200$ 입니다.
3. SELECT * FROM 사원 WHERE 부서번호 NOT IN (SELECT 부서번호 FROM 폐지부서)가 한 건도 반환하지 않았다. 가장 가능성이 큰 원인은?
   ① 사원 테이블이 비어 있다
   ② 폐지부서의 부서번호에 NULL이 섞여 있다
   ③ 두 칼럼의 자료형이 다르다
   ④ 서브쿼리에 인덱스가 없다

   ②. NOT IN은 목록에 Null이 있으면 모든 비교가 알 수 없음이 되어 어떤 행도 참이 되지 못합니다. ③이면 대개 오류가 나고 ④는 속도 문제일 뿐 결과 건수를 바꾸지 않습니다.
4. 반정규화를 검토하기에 가장 적절한 자리는?
   ① 하루에도 수십 번 값이 바뀌는 재고 수량을 주문 테이블에 복사해 두려는 경우
   ② 거의 바뀌지 않는 상품 분류명을 조회가 잦은 주문상세에 함께 두려는 경우
   ③ 아직 성능 문제가 관측되지 않았지만 미리 조인을 줄여 두려는 경우
   ④ 인덱스를 추가하면 해결되는 조회가 느린 경우

   ②. 자주 읽고 거의 바뀌지 않는 값이 반정규화가 잘 맞는 자리입니다. ①은 갱신이 잦아 총비용이 커지고, ③은 문제 확인이 먼저이며, ④는 모델을 건드리지 않는 방법이 남아 있습니다.
5. 인조식별자를 도입했을 때 반드시 함께 해야 하는 조치는?
   ① 본질식별자 칼럼을 삭제한다
   ② 본질식별자에 유니크 인덱스를 건다
   ③ 모든 자식 테이블을 비식별 관계로 바꾼다
   ④ 기본키 인덱스를 비트맵 인덱스로 바꾼다

   ②. 인조식별자는 넣을 때마다 새 값이 나오므로 업무적으로 같은 행의 중복을 막지 못합니다. 그 역할을 본질식별자의 유니크 인덱스가 대신 맡습니다.
6. 주문 테이블에 「고객명」을 반정규화로 복사해 두었다. 이 결정이 나중에 만들어 낼 수 있는 문제를 하나 들고, 그 문제가 조회 시점에 어떤 모습으로 나타나는지 함께 서술하시오.

   고객이 개명하면 고객 테이블의 고객명은 바뀌지만 주문 테이블에 복사해 둔 고객명은 예전 값으로 남습니다. 갱신 경로가 둘인데 한쪽만 돌았기 때문입니다. 조회 시점에는 오류가 나지 않고, 주문 목록의 고객명과 고객 상세 화면의 고객명이 서로 다르게 나오거나 고객명으로 집계한 건수가 두 갈래로 갈리는 모습으로 나타납니다. 대응은 복사 값의 갱신을 트리거나 배치로 한 곳에 모으는 것이고, 주문 시점의 이름을 보존하는 것이 업무 요구라면 애초에 「주문시점고객명」처럼 이름을 달리해 의도를 남깁니다. 채점은 갱신 누락 지적 2점, 오류 없이 값만 어긋난다는 점 1점, 대응 1점입니다.