📚 지식 노트

표·행·키

관계형 모델은 1970년 E. F. Codd가 「A Relational Model of Data for Large Shared Data Banks」에서 내놓았다. 그 전의 데이터베이스는 레코드끼리 포인터로 이어 두고, 프로그램이 그 포인터를 따라가며 데이터를 찾았다. 저장 방식이 바뀌면 프로그램도 고쳐야 했다. Codd의 생각은 데이터를 값만 담긴 표로 보이고, 어디에 어떻게 저장했는지는 숨기자는 것이었다.

• 릴레이션(relation, 테이블): 같은 모양의 행을 모은 것. 수학에서는 행의 집합이라 순서가 없고 같은 행이 두 번 있을 수 없다. SQL 테이블은 실용을 위해 중복 행을 허용한다.
• 속성(attribute, 열): 이름과 자료형(도메인)이 있다. 칸 하나에는 더 나눌 수 없는 값 하나만 둔다.
• 기본 키(primary key): 행을 하나로 가리키는 열(또는 열 묶음). 같은 값이 두 번 나올 수 없고 비어 있을 수 없다.
• 외래 키(foreign key): 다른 표의 키를 담은 열. 데이터베이스가 "없는 고객을 가리키는 주문"을 막아 준다(참조 무결성).

키와 외래 키, 값의 범위 같은 규칙은 제약 조건(constraint)으로 표에 선언해 둔다. 응용 프로그램이 몇 개든 같은 규칙이 늘 지켜진다는 것이 장점이다. PostgreSQL 문서의 Constraints 장이 CHECK·NOT NULL·UNIQUE·PRIMARY KEY·FOREIGN KEY를 예와 함께 설명한다.

관계 대수와 선언형 SQL

표를 다루는 연산은 몇 가지뿐이다. 행을 고르는 선택(σ), 열을 고르는 투영(π), 두 표를 조건으로 잇는 조인(⋈), 그리고 합집합·차집합 같은 집합 연산이다. 연산의 입력도 표, 결과도 표라서 얼마든지 이어 붙일 수 있다. 이것이 관계 대수(relational algebra)다.

SQL은 이 연산을 직접 늘어놓지 않는다. "금액이 10만 원 이상인 주문을 한 고객의 이름"처럼 무엇을 원하는지만 적는다. 어떤 표를 먼저 읽고 어느 인덱스를 쓰며 조인을 어떤 방법으로 할지는 데이터베이스의 옵티마이저가 정한다. 이렇게 결과만 적는 방식을 선언형(declarative)이라 한다. 위 그림처럼 거르기를 조인 아래로 내리는 변환은 답을 바꾸지 않으면서 일을 줄이는데, 프로그래머가 아니라 데이터베이스가 그 판단을 한다는 점이 핵심이다. 실제 계획을 고르는 과정은 인덱스와 쿼리 실행 노트에 있다.

Carnegie Mellon 대학 데이터베이스 강의의 첫 강의 노트가 관계 대수 연산을 하나씩 그림으로 보여 준다.

조인의 종류와 NULL

조인은 짝이 없는 행을 어떻게 다루느냐로 갈린다. PostgreSQL 튜토리얼의 Joins Between Tables가 날씨 표와 도시 표로 차이를 보여 준다.

외부 조인이 만드는 NULL은 값이 아니라 "모른다"는 표시다. 그래서 7 = NULL도 NULL = NULL도 참이 아니라 NULL(알 수 없음)이 되고, WHERE는 참인 행만 남기므로 WHERE 열 = NULL은 아무 행도 고르지 못한다. NULL인지는 IS NULL로, NULL끼리도 같다고 보려면 IS NOT DISTINCT FROM으로 비교한다(Comparison Functions and Operators). LEFT JOIN 뒤에 오른쪽 표의 열을 WHERE에서 거르면 NULL 행이 빠져 INNER JOIN과 같아지는 실수도 같은 이유로 생긴다. 오른쪽 표의 조건은 ON에 둔다.

정규화와 이상 현상

표를 어떻게 나눌지 정하는 기준이 정규화(normalization)다. 목표는 하나의 사실을 한 곳에만 두는 것이다. 같은 사실이 여러 줄에 흩어져 있으면 고칠 때마다 모든 사본을 찾아야 하고, 하나라도 놓치면 데이터가 스스로 모순된다.

어떤 열이 다른 열에 따라 정해지는 관계를 함수 종속(functional dependency)이라 하고 X → Y로 쓴다. 학번을 알면 이름이 정해지므로 학번 → 이름이다. 정규형은 이 종속이 어디를 향하는지로 정의한다.

3NF와 BCNF는 대부분 같지만 키가 여럿 겹칠 때 갈린다. 과목마다 교수가 여럿이고 교수는 한 과목만 맡는 (학생, 과목, 교수) 표를 보자. 키는 (학생, 과목)인데 교수 → 과목 종속이 있다. 오른쪽 과목이 키의 일부라 3NF는 통과하지만, 교수가 키가 아니므로 BCNF는 아니다. BCNF로 나누면 (학생, 교수)와 (교수, 과목)이 되는데, 이번에는 "학생은 과목마다 교수 한 명"이라는 규칙을 한 표 안에서 검사할 수 없게 된다. 그래서 실무 설계는 보통 3NF를 기준으로 삼고, BCNF는 이 손해가 없을 때 간다.

트랜잭션과 ACID

계좌 이체는 "A에서 빼기"와 "B에 더하기" 두 변경이다. 첫 번째만 되고 서버가 꺼지면 돈이 사라진다. 트랜잭션은 여러 변경을 하나로 묶어, 묶음 전체가 반영되거나 하나도 반영되지 않게 한다. SQL에서는 BEGIN으로 열고 COMMIT으로 확정하며, ROLLBACK으로 없던 일로 한다. PostgreSQL 튜토리얼의 Transactions 절이 바로 이 이체 예로 설명한다.

트랜잭션이 지키는 네 성질의 머리글자가 ACID다.

• 원자성(Atomicity): 전부 아니면 전무. 실패한 트랜잭션의 흔적은 남지 않는다. 엔진은 되돌리기용 기록(undo)으로 이를 지킨다.
• 일관성(Consistency): 트랜잭션은 제약 조건을 지키는 상태에서 지키는 상태로만 옮겨 간다. 나머지 셋과 달리 데이터베이스 혼자가 아니라 응용 프로그램의 올바른 트랜잭션과 함께 지키는 성질이다.
• 격리성(Isolation): 동시에 도는 트랜잭션이 서로의 중간 상태를 보지 않는다. 얼마나 엄격하게 막을지는 격리 수준으로 고른다(동시성과 격리 수준 노트).
• 지속성(Durability): 커밋이 성공했다고 답했으면 그 뒤 전원이 꺼져도 남는다. 엔진은 커밋 전에 로그를 디스크에 먼저 써서 지킨다(저장 엔진 노트의 WAL).

ACID의 C는 분산 시스템에서 말하는 일관성(모든 복제본이 같은 값을 보는가)과 이름만 같고 뜻이 다르다. 그 이야기는 복제·샤딩·합의 노트의 CAP 절에서 다룬다.

비정규화를 고르는 때

정규화는 쓰기를 안전하게 하지만 읽을 때 조인이 늘어난다. 읽기가 압도적으로 많고 조인 비용이 문제가 될 때 일부러 사본을 두는 것이 비정규화(denormalization)다.

• 미리 계산한 값: 게시글마다 댓글 수를 열로 두면 목록 화면이 댓글 표를 셀 필요가 없다. 댓글을 넣고 지울 때 같은 트랜잭션에서 함께 고쳐야 한다.
• 그때의 값을 남겨야 할 때: 주문서의 배송지와 가격은 고객이 나중에 주소를 바꾸거나 상품 가격이 올라도 주문 당시 값이어야 한다. 이것은 사본이 아니라 다른 사실(주문 시점의 값)이므로 정규화 위반도 아니다.
• 분석용 표: 집계 질의가 큰 표 여러 개를 조인하는 데이터 웨어하우스는 사실 표 하나에 차원 표를 붙인 스타 스키마처럼 의도적으로 넓은 표를 쓴다.
• 분산 데이터베이스: 표가 여러 서버에 흩어지면 조인이 네트워크 왕복이 되므로, 함께 읽는 데이터를 한 행(문서)에 모으는 쪽이 낫기도 하다.

순서는 늘 같다. 먼저 정규화해서 설계하고, 측정해서 느린 읽기를 찾은 뒤, 그 읽기 하나를 위해 사본을 두고 그 사본을 누가 언제 고칠지(트리거, 같은 트랜잭션, 배치 작업)를 함께 정한다. 사본을 고치는 길이 정해지지 않은 비정규화는 위 그림의 이상 현상을 그대로 되살린다. 실체화 뷰(materialized view)처럼 데이터베이스가 사본 관리를 맡아 주는 기능을 먼저 살펴볼 만하다.