관계형 모델에서 벗어나 다양한 예를 시작으로 테이블의 물리 설계인 인덱스, 오랜 시간에 걸쳐 운영하는 데 필요한 리팩터링 그리고 데이터베이스 응용프로그램에서 빼놓을 수 없는 트랜잭션에 대해 설명한다.

1. 이력 데이터의 문제점

이력데이터는 관계형 데이터베이스가 잘 처리할 수 없다

느린 쿼리 대부분이 이력 데이터를 다루는 데 실패한 것들이다.

세상은 이력 데이터가 넘쳐난다

응용프로그램이 로그 데이터를 테이블에 저장하거나, 과거부터 현재에 이르는 데이터를 저장할 때 생성되는 것이 이력 데이터다. 이력데이터는 대부분 타임스탬프나 버전 번호와 같이 저장될 것이다. 쇼핑 사이트의 구매 이력, 가격의 이력, 응용프로그램의 조작 이력등 데이터베이스는 이력 데이터로 넘쳐난다.

하지만 실제로 이력 데이터를 능숙하게 다루는 경우는 상당히 적다.

이력과 관계형 모델의 상성 문제

이력은 그 특성상 릴레이션의 요건을 만족하게 할 수 없다.

릴레이션은 집합이다. 따라서 각 요소 사이에 순서가 없다.

하지만 이력에는 어느 쪽이 오래된 쪽인지, 새로운 것인지에 관한 순서가 있다. 즉, 이력데이터를 RDB에서 다루려면 원래의 릴레이션으로 표현할 수 있는지 없는지라는 본질적인 관문을 가로막고 있다.

이력 데이터를 처리하는데 있어서 또 다른 하나의 고민거리가 테이블이 쉽게 커지는 것이다. 데이터가 크면 클수록 쿼리가 효율적이지 않으면 영향이 쉽게 표면화된다.

→ 데이터가 적을 떄는 빠르게 처리할 수 있었던 것도 점점 건수가 늘어나면서 성능이 저하되고, 알아챘을 때는 손을 쓰기에 늦어버린다.

이력 데이터의 구체적인 예

이력 데이터의 쇼핑몰의 가격표를 가져왔다. 가격표가 이력 데이터가 되는 이유는 가격이 변동하기 때문이다. 가격은 현재, 과거뿐만 아니라 ‘어떤 날 어떤 시간부터 새로정해질 가격’ 과 같이 미래의 예정 가격도 고려해야 한다.

그러나 상품별로 특정 시점에서 유효한 가격은 한 개뿐이다. → 이를 관계형 모델을 사용해 표현하기 어렵다.

쇼핑 사이트 가격표

# 턱걸이 기계의 현재 가격 구하기
SELECT price
FROM price_list
WHERE item = '턱걸이 기계'
AND NOW() BETWEEN start_date AND end_date

이력 데이터의 어떤 것이 문제가 되는가?

릴레이션과 시간축의 직교성

가장 큰 문제는 이력 데이터는 시간축과 릴레이션이 직교하지 않는다는 점이다.

→ 시간에 따라 쿼리의 실행결과가 변한다는 뜻이다.

예를 들어 턱걸이 기계의 현재 가격을 구하는 쿼리를 실행한 결과는 시간에 따라 변한다.

시간축과 직교하지 않는 것은 릴레이션이라고 할 수 없다. 릴레이션은 어떤 지점에 대한 사실의 집합이기 때문이다. 릴레이션의 요건을 만족하지 않은 것은 1NF가 되어 있지 않다고 생각할 수 있다.

NULL의 가능성

가장 새로운 가격 항목에는 end_date에 9999-12-31이라고 설정돼 있다. 이는 실질적으로 기한이 설정돼 있지 않고 이 값 대신 NULL이 될 수도 있다.

end_date가 NULL이 되면 조금 전 쿼리의 검색 조건은 IS NULL을 사용하도록 변경 해야 한다.

SELECT price
FROM price_list
WHERE item = '턱걸이 기계'
AND NOW() BETWEEN start_date AND end_date
OR (start_date >= NOW() AND end_date IS NULL);

쿼리 조건이 복잡해졌다. NULL은 3치 논리(3VL)로 다뤄야 하므로 검색 조건이 복잡해지기 쉽고, 성능에도 악영향이 있다.

특정 행만 의미가 다르다.

설계에 따라서는 enddate 없이 startdate만 있는 경우도 있다. 날짜가 미래가 아닌 것 중에 가장 최근 가격이 현재의 가격일 때다.

쇼핑 사이트 가격표 변화

# 턱걸이 기계 현재 가격
SELECT price
FROM price_list
WHERE item = '턱걸이 기계'
AND start_date = (
	SELECT max(start_date)
	FROM price_list
	WHERE item = '턱걸이 기계');

• MySQL이면 ORDER BY start_date DESC LIMIT 1 과 같은 형식을 사용할 수 있다.

이 쿼리는 MAX()라는 집계함수를 사용하고 있지만, 집계는 관계형 연산이 아니다. 관계형 연산이란 어디까지 릴레이션을 입력하고 릴레이션이 출력되는 연산을 말한다.

이러한 함수가 좋지 않은 이유는 온라인 트랜잭션 중에 사용되기 때문이다. 집계함수를 보고나 집계처리에 사용하는 것은 전혀 문제 없다.

또 하나는 좀처럼 알아채기 어렵지만 각 행의 의미가 균일하지 않다는 문제다.

릴레이션은 참이 되는 명체이며 동시에 해당하는 술어가 있다.(뭘 넣든간 튜플에 포함 → 참, 튜플 미포함 → 거짓)

→ 릴레이션의 각 튜플의 의미는 명제함수만으로 결정되며 그 이상의 그 이하도 될 수 없다.

그런데 이 테이블에는 ‘날짜가 최신인 가격이 현재 가격을 나타낸다’ 는 것을 암시적으로 결정한 테이블이 존재한다. 조금 전의 쿼리는 암시적으로 결정된 것을 이용한 것이다.

→ 이 테이블은 ‘현재(유효한) 가격’ 과 ‘과거의 (무효한) 가격’ 이라는 두 가지 의미의 행을 포함할 수 있다.

‘X는 현재의 유효한 가격이다’라는 술어에 대한 참과 거짓이 튜플에 따라서 달라진다. 이와 같은 암시적인 의미가 존재하는 것은 릴레이션의 요건을 만족하지 않는다. 또는 이 릴레이션은 두 개 이상의 릴레이션의 합집합인지도 모른다.

릴레이션의 요건에서 벗어난 결과, 쿼리에도 그 영향을 미친다.

2. 이력 데이터에 대한 해결책

이력 데이터는 관계형 모델에 맞지 않으며 이력 데이터를 다루는 것은 모두 차선책이다.

릴레이션을 나눈다

관계형 모델적으로 의미가 다른 튜플, 즉 같은 명제함수로 평가할 수 없는 튜플은 같은 릴레이션에 포함시키지 않는다.

→ 의미별로 릴레이션을 나누는 것이 자연스러운 방법이다. 튜플별로 다른 릴레이션을 나눠야 할 필요성은 속성별로 릴레이션을 무손실 분해하는 정규화의 프로세스에서는 찾아볼 수 없다.

이 경우 문제를 인식하기가 어렵고 그 결과 나눌 필요성을 눈치채지 못하게 된다. 정규화로 문제가 드러나지 않는 것은 DB 설계상의 맹점이 되기 싶다.

가장 간단한 분할 방법

현재의 가격과 과거의 가격을 포함하는 두 개의 릴레이션으로 나누는 설계다.

price_list

pricelisthistory

# 현재 턱걸이 기계 가격 검색 조건
SELECT price
FROM price_list
WHERE item = '턱걸이 기계';

이 검색 조건의 쿼리는 간단하지만, 과거에서 현재에 이르는 모든 가격에 대한 조건을 지정한 쿼리를 수행할 때는 두 개의 테이블을 찾아봐야 하거나 필연적으로 UNION을 사용해 두 개의 쿼리를 실행해야 할 수도 있다.

단, WHERE 절에 NOW()나 서브쿼리에 MAX()를 사용할 때와 비교하면 UNION을 사용하는 쪽이 아직은 낫다.

또한 실제 응용프로그램에서는 UNION을 사용하지 않으면 안 되는 경우보다 어느 한쪽 테이블에 대해 쿼리를 실행하는 경우가 훨씬 많으므로 그런 의미에서도 문제가 되지 않는다.

외부키를 사용할 수 없음

테이블을 두 개로 분해할 때 가장 큰 문제는 외부키 제약을 사용할 수 없다는 것이다. 외부키는 어떤 테이블에 행이 포함되는 것을 보장하는 제약이지만 대상이 되는 테이블은 하나여야 한다.

‘pricelist와 pricelist_history 중 하나의 테이블에 해당하는 행을 포함하는 것을 보장한다’ 와 같은 제약을 외부키로 표현할 수 없다.

현실적으로 이와 같은 제약을 표현하려면 트리거를 사용해야 한다. 자기 자신에게 트리거를 사용하면 성능적으로 외부키보다 떨어질 가능성이 크고 관리도 복잡해진다.

두 개의 테이블의 정합성

‘같은 행이 양쪽 테이블에 포함될 수 있다’ 는 상황은 데이터의 부정합이라고 할 수 있다. 이를 방지하려면 응용프로그램 쪽에서 확실하게 행의 이동을 한 개의 트랜잭션으로 실행되게 하거나, 트리거를 사용해 제약을 표현해야 한다

→ pricelisthistory 테이블에 BEFORE INSERT 트리거를 걸어 price_list에 같은 행이 존재하지 않는다

라는 것을 보증하는 등. 또한, 중복하는 행이 존재할 때는 예외를 발생기키는 것이 좋다.

UNION이나 트리거를 많이 쓰게 되지만, 릴레이션의 분할은 처음의 설계보다 훨씬 더 선호한다는 것을 기억하자.

각 테이블의 의미를 명확하게 해 한 개의 술어가 대응하는 릴레이션으로 취급하는 매우 큰 장점이 있다.

중복행을 허용

‘외부키를 사용하지 못하는건 불편하다’ 이런 경우 모든 가격을 일단 한 개의 릴레이션에 저장하고 현재의 가격만 다른 테이블에 중복되게 저장하는 설계를 생각할 수 있다.

price_list

pricelisthistory

이 설계의 문제는 중복되고 있다는 점이다. 좀 더 엄밀히 말하면 두 개의 릴레이션이 직교하지 않는다는 점이다.

하지만 DB상의 중복은 위험하지만 현재 문제점은 제한적이므로 대안이 있다.

먼저 pricelist 테이블에 있는 행은 pricelisthistory 테이블에 같은 행이 없으면 안 되므로 외부키 제약조건을 걸어야 한다. 이런 경우 먼저 pricelist_history 테이블에 행을 삽입(INSERT)해야 한다.

또한, 기본적으로 pricelisthistory 테이블의 행을 갱신(UPDATE)하지 않는다. 가격을 변경할 때는 새로운 행을 추가해야 한다. 이 때 price_list 테이블에 같은 아이템의 가격이 존재하면 오래된 쪽의 행을 삭제한다. 만약 그 외에도 여러 개의 속성이 있고 행을 갱신해야 할 때는 UPDATE 트리거를 사용해 테이블을 동기화하는 게 좋다.

대리키

‘중복은 안돼! 직교성 없는 설계는 싫어! 외부키는 쓸꺼야’ 이 경우엔 대리키를 사용한 설계를 검토하면 된다.

priceidmaster

price_list

pricelisthistory

단일 priceidmaster 테이블이 있으므로 외부키 제약을 쓸 수 있다. 오히려 pricelist 테이블, pricelisthistory 테이블 각각에서 priceidmaster 테이블로 외부키가 필요할 것이다. 또한, pricelist 테이블과 pricelisthistory 테이블 사이에도 행이 중복되어서는 안된다. 이쪽은 트리거를 사용해 제약을 표현하는 게 좋다.

문제는 JOIN(결합)이 증가한다는 점이다. JOIN은 RDB에 있어 기본적인 조작이며 JOIN 그 자체는 문제가 없다.

JOIN은 느리니 피해야 한다는 말은 많이 하지만 이는 잘못된 생각이다. 하지만 아무리 기본적인 조작이라도 조작 횟수를 줄여서 나쁠 건 없다.

또한, 대리키를 사용하는 것 자체에도 익숙하지 않을 것이다. 적절한 자연키가 있다면 대리키는 중복이기 때문이다. 더구나 pricelist 테이블, pricelisthistory 테이블 양쪽에 priceid 이외의 후보키에 대한 고유키 제약 조건이 필요하다.

기본키 이외에 유니크 키가 있다면 디스크 공간이 낭비되고 제약을 확인하기 위해 오버헤드가 발생한다.

그러나 처음의 설계와 비교하면 훨씬 바람직하다.

미래의 가격은 어떻게 해야할까?

가격 개정은 갑자기 발생하는 것이 아니라 사전에 알고 있는 경우가 많다. 따라서 언제부터 가격 개정을 할 것인지 계획을 세우고 그 데이터를 DB에 저장하게 된다.

다음 표는 대리키와 미래 가격 테이블(pricelistupcoming)을 추가했다.

priceidmaster

price_list

pricelistupcoming

pricelisthistory

미래의 가격이 적용되는 날짜에 이르렀을 때 이를 어떻게 현재의 가격 으로 해야 할까?

pricelist 테이블에 대한 쿼리는 어디까지나 pricelist 테이블에서만 데이터를 참조할 수 있다.

pricelistupcoming 테이블의 데이터를 유효화하려면 누군가는 이를 price_list 테이블로 옮겨야 한다.

→ 응용프로그램쪽에서 이런 로직을 구현해야 한다. 그 처리는 배치 처리로 구현하고 정기적으로 실행해야 한다.

배치 처리는 반드시 시간대로 수행되지 않을 수 있다는 점에 주의해야한다. DB 서버에 문제가 발생해 처리를 실행할 수 없거나 성능 문제로 처리가 늦어질 수도 있다.

이 DB 설계에서는 ‘특정 시각이 되면 가격이 바뀐다’ 라는 행동은 실현될 수 없다. 이러한 요건을 응용프로그램이 갖지 않도록 주의해야 한다. 적어도 배치 처리에 의한 지연을 허용하는 요건으로 해야한다.

대신 쿼리를 단순하게 작성할 수 있다는 큰 장점이 있다.

3. 이력데이터의 안티 패턴

플래그 사용

플래그 컬럼을 만들어 의미를 표면화하는 방법은 임시방편에 불과하다.

플래그를 사용한 설계

# 턱걸이 기계의 현재 가격
SELECT price
FROM price_list
WHERE item = '턱걸이 기계'
	AND flag = 1

이 쿼리는 잘돌아가는 것처럼 보이지만 몇가지 문제점이 있다.

1. flag 컬럼이 카디널리티가 낮아서 효율이 높지 않다.

0과 1의 두 개의 값만 취득한다면 카디널리티는 2다. 그렇다고 해도 카디널리티가 낮은 것은 본질적인 문제가 아니다. 단순히 작은 정보에 많은 공간을 할당하는 것은 효율적이지 않다는 것 뿐이다.

2. 가장 큰 문제는 이 릴레이션이 3NF가 되어 있지 않다.

플래그 값이 0과 1 어느 쪽이 될지는 startdate, enddate의 값으로 결정된다. 즉, {startdate, enddate} → {flag}라는 함수 종속성이 존재하고, 플래그는 중복이다.

3. 미래의 데이터와 마찬가지로 flag 값은 자동으로 변하지 않는다

시간이 지남에 따라 그 가격이 유효한가 아닌가? 라는 본래의 의미는 바뀐다. 예를 들어 end_date를 지나면 의미상으로 그 가격은 무효가 될 것이다. 하지만 flag 컬럼의 값은 시간이 지나면 자동으로 무효화되거나 무효화 되지 않는다. 배치 처리 등으로 정기적으로 flag 값을 다시 작성해야 한다.

그러나 startdate, enddate라는 두 개의 컬럼을 사용했을 때와 flag라는 컬럼을 사용했을 때의 쿼리 결과가 다를 수 있다. 만약 응용프로그램 쪽에 이와 같은 쿼리가 혼재돼 있으면 flag의 갱신이 늦어져서 데이터의 부정합이 발생할 가능성이 있다. 특히 나중에 flag를 추가할 때는 주의해야 한다.

4. 한 개의 상품에 대해서 flag=1 이 돼야 하는 행은 항상 한 개뿐이다.

이 제약을 트리거로 표현할 수 있지만, 예를 들어 COUNT()를 사용해 개수를 확인하면 부하가 높아지게 된다.

그러나 안티 패턴을 피해야 하는 법은 없다. 도서 SQL 안티 패턴 에서도 소개된 것처럼 영향이 그다지 발생하지 않는 범위에서 안티 패턴이라는 것을 인식하고 사용하면 문제를 제어할 수 있다. 어떻게 해도 테이블을 분해할 수 없을 때는 플래그를 활용하는 것이 잘못된 판단이라고 단언할 수 없다.

절차형으로 구현하자

도저히 대응할 수 없는 경우(관계형 모델 방법으로) 절차형으로 로직을 구현하는 것은 좋은 방법이다. 그러나 이 방법은 절차형 로직에 의존하면 관계형 모델에 의한 강력한 데이터 정합성을 잃어버리게 되기 때문이다. 절차형 로직에 의존하는 것은 최후의 수단이라고 생각하고 가능한 한 DB의 설계를 먼저 연구해야한다.

테이블을 나눌 때의 물리적 장점

테이블을 분리했을 때 논리적으로도 좋지만 물리적으로도 좋은 점이 있다.

테이블을 여러 개로 나누면 테이블의 크기가 작아진다.

많은 RDB 제품의 인덱스는 B+트릴를 사용해 구현돼 있다. B+트리의 검색 알고리즘은 요소 수를 n이라고 하면 실행시간은 $O(logn)$이다. 즉 테이블이 커질수록 검색에 시간이 걸린다.

또한, 행이 늘어나면 효율이 떨어지기는 하지만 인덱스를 사용하는 편이 인덱스를 사용하지 않는 경우보다 훨씬 검색속도가 빠르다. 인덱스를 사용하지 않을 때의 검색은 풀 테이블 스캔이 되고 실행시간은 $O(n)$이 된다.

이번처럼 이력과 현재의 값 과 같이 테이블을 나누면 현재의 값 테이블은 매우 작아진다. 게다가 대부분 엑세스가 집중되는 곳은 현재의 값 이다. 따라서 쿼리의 대부분이 효율적일 가능서이 크다.

또한, 자주 엑세스하는 테이블이 작아지면 캐시의 히트율에도 영향을 준다. 현재의 값 테이블은 자주 사용하는 데이터가 들어 있으므로 캐시에서 내려가는 일은 드물다. 캐시가 잘 활용되면 성능에도 좋은 영향을 미친다.

요약

이력 데이터의 문제는 DB 설계의 곳곳에 숨어 있다. 시간축과 릴레이션이 직교하지 않는다 라는 문제점처럼 즉시 알아채기 어려워서 문제가 긴 시간 방치되기 쉽다.

잠재적인 문제를 알아내는 방법은 다음과 같다. 만약 이와 같은 현상이 있다고 생각되면 DB 설계를 검토해보자.

• 상태나 플래그를 나타내는 컬럼이 있다.
• 초기값이 NULL인 컬럼이 있다.
• 현재 시각과 비교하고 있다.
• 온라인 트랜잭션 중에 ORDER BY N DESC LIMIT 1 또는 MAX, MIN, COUNT가 사용되고 있다.
• 버전을 나타내는 컬럼이 있다.
• INSERT, DELETE보다 UPDATE의 비율이 높다

릴레이션 중에서 특별한 의미가 있는 튜플을 잘 찾아 그것을 분할할 수 있었다고 하자. 그런데도 이력 데이터를 분할할 가능성이 있고 없을 수도 있다.

pricelisthistory 테이블의 데이터가 이력 형식으로 남아 있다. 그러나 이걸 분할해야 할까?

응용프로그램이 이력 데이터 중에 새로운 ‘특별한 의미가 있는 튜플’을 발견하지 않는 한 여러 개의 릴레이션으로 분할할 필요는 없다.

비록 이력 데이터가 그대로 남아 있다고 해도 그것을 집계처리에 사용하는 것뿐이라면 아무런 문제가 없을 것이다. 응용프로그램이 데이터를 어떻게 볼 것인가? 라는 자의성을 통해 보는 것이 이력 데이터 처리의 핵심이다.