이력 데이터와 친해지기 (DB 스터디 4주차)

9.1 이력 데이터의 문제점

• 응용프로그램이 로그 데이터를 테이블에 저장하거나,과거부터 현재에 이르는 데이터를 저장할 때 생성되는 것
• 일반적으로 타임스탬프나 버전 번호와 같이 저장

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

• 릴레이션은 집합이므로 각 요소간 순서가 존재하지 않는다.
• 이력에는 어느 쪽이 오래된 쪽인지, 새로운 것인지에 관한 순서가 존재한다.
• 이력 데이터는 쉽게 테이블이 커지므로 성능 저하가 나타날 수 있다.

이력 데이터의 예

# 현재 날짜가 2014-12-31인 경우, 턱걸이 기계의 현재 가격 구하는 쿼리
SELECT PRICE
FROM PRICE_LIST
WHERE ITEM = '턱걸이 기계'
AND NOW() BETWEEN START_DATE AND END_DATE;

• 이력 데이터는 릴레이션이 시간축과 직교하지 않는다.
  • 시간에 따라 쿼리의 실행 결과가 변한다.
  • 같은 데이터, 같은 쿼리임에도 시간에 따라 결과가 달라진다.

NULL의 가능성

• end_date는 설계에 따라 NULL이 될 수도 있다.
• NULL이 되는 경우 검색 조건이 복잡해지고 성능에 악영향을 미칠 수 있다.
• 컬럼 값에 NULL이 들어갈 여지가 있는 것은 DB 설계에 문제가 있을 수 있다.

특정 행만 의미가 다르다

• end_date가 존재하지 않고 start_date만 존재하는 경우

SELECT PRICE 
FROM PRICE_LIST
WHERE ITEM = '턱걸이 기계'
AND START_DATE = (
	SELECT MAX(START_DATE)
	FROM PRICE_LIST 
	WHERE ITEM = '턱걸이 기계');
	

• 집계 연산은 관계형 모델을 벗어난 연산이다.
• 각 행의 의미가 균일하지 않은 문제가 발생
  • 릴레이션 : “참이 되는 명제의 집합, 릴레이션 내 튜플은 주어진 명제에 모두 참인 결과”
  • 테이블에는 start_date가 최신인 행 = 현재 가격(참)
  • 이외의 행 = 과거 가격(거짓)
  • 한 명제에 대해 튜플들의 결과가 참/거짓이 구분된다. → 두 개 이상의 릴레이션의 합집합일 가능성 존재

9.2 이력 데이터에 대한 해결책

릴레이션을 나눈다

• 같은 명제로 평가할 수 없는 튜플은 같은 릴레이션에 포함시키지 않는다.
• 튜플 기준으로 릴레이션을 나눠야 하기 때문에 속성별로 릴레이션을 나누는 정규화는 이러한 필요성을 확인하기 힘들다.
  • 현재의 가격 릴레이션과 과거의 가격 릴레이션으로 나눈다.
  • 나타나는 문제점
    • 현재부터 과거까지의 이력에서 통계를 찾는 경우 UNION쿼리 실행이 필요
    • 외부키 제약조건을 사용할 수 없으므로 트리거를 사용해야한다.
    • 데이터 부정합이 발생할 수 있다. (현재 데이터가 갱신되는 경우 하나의 트랜잭션 내에서 이뤄지지 않아 과거 데이터에도 적재되는 경우 부정합 문제 발생)

중복행을 허용

• 모든 이력 데이터를 하나의 릴레이션에 저장 후 특정 이력만 새로운 릴레이션에 중복해 저장하는 설계
• 특정 이력 릴레이션에 외부키 제약조건을 지정
• 모든 이력 데이터 릴레이션에 데이터를 우선 삽입
• 모든 이력 데이터 릴레이션은 update 하지 않음
  • update의 경우 새로운 행을 추가하고 특정 이력 릴레이션에서 오래된 행을 삭제
  • update 트리거 사용을 통한 테이블 동기화

대리키

대리키

기본키가 보안을 필요로 하는 속성을 가지고 있거나, 여러 개의 속성으로 구성되어 있어 복잡하거나,
기본키로 사용할 속성이 없을 경우에 일련번호 같은 가상의 속성을 생성하여 기본키로 사용하는 키

• 대리키 테이블에 대해 분해한 이력 테이블들이 외부키가 필요
• 분해한 이력 테이블간 튜플의 중복이 존재하면 안됨
• JOIN 연산을 통해 이력 집계
• 이력 테이블의 외래키를 대리키로 사용하기 때문에 기존 후보키가 될 수 있는 속성들은 유니크 제약조건 설정 필요
  • auto increment 값과 같이 일련번호를 사용하는 경우 해당 키 만으로 기본키가 될 수 있기 때문에 나머지 속성은 고유하지 않을 수 있음
  • 유니크 제약조건은 디스크 공간이 낭비되고 제약 확인 위한 오버헤드가 발생

9.3 이력데이터의 안티 패턴

• 이력 데이터를 다룰 때 피해야 하는 방식

플래그 사용

• 플래그를 사용하면 쿼리는 단순해 보이는 장점
• 플래그로 사용한 컬럼은 카디널리티가 낮아 효율이 높지 않다.
• 키가 아닌 속성(start_date, end_date) → 키가 아닌 속성 (flag) 결정 = 3NF 만족하지 않은 테이블
• 시간에 따라 유효성을 검증할 수 없으므로 정기적으로 플래그 수정 필요

절차형으로 구현하자

• 관계형 모델로 대응할 수 없는 경우 스토어드 프로시저나 함수로 로직을 구현
• 절차형 로직에 의존하면 관계형 모델의 데이터 정합성을 잃어버리는 문제 발생

9.4 요약

• DB 설계 검토해야하는 현상
  • 상태나 플래그를 나타내는 컬럼이 있는 경우
  • 초깃값이 NULL인 컬럼 존재하는 경우
  • 현재 시각과 비교하는 쿼리가 있는 경우
  • 온라인 트랜잭션 중에 ORDER BY N DESC LIMIT 1, MAX()/MIN()/COUNT()가 사용되는 경우
  • 버전을 나타내는 컬림이 있는 경우
  • INSERT/DELETE보다 UPDATE의 비율이 높은 경우
• 응용프로그램이 데이터를 어떻게 볼것인가? 라는 자의성을 통해 보는 것이 이력데이터 처리의 핵심