[SQLD] 식별자, 데이터 모델과 성능

식별자

정의

‘Entity 내에서 인스턴스들을 구분하는 구분자, 엔티티를 대표하는 속성’

하나의 엔티티에는 반드시 하나의 유일한 식별자가 존재한다.

특징

특징	내용	비고
유일성	주식별자에 의해 Entity내의 모든 인스턴스들을 유일하게 구분함	예) 사원번호가 주식별자가 모든 직원들에 대해 개인별로 고유하게 부여됨
최소성	주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야함	예) 사원번호만으로도 고유한 구조인데 사원분류코드 + 사원번호로 식별자가 구성될 경우 부적절한 주식별자구조임
불변성	주식별자가 한 번 특정 엔티티에 지정되면 그 식별자의 값은 변하지 않아야함	예) 사원번호의 값이 변한다는 의미는 이전기록이 말소되고 새로운 기록이 발생되는 개념임
존재성	주지정자가 지정되면 반드시 데이터 값이 존재 (Not Null)	예) 사원번호 없는 회사직원은 있을 수 없음

대체 식별자의 특징은 주 식별자의 특징과 일치하지만, 외부 식별자일 경우 주식별자 특징과 일치하지 않으며 참조무결성 제약조건 (Referential Integrity)에 따른 특징을 가지고 있다.

주식별자의 특징과 키의 종류

• 주식별자는 유일성과 최소성, Not Null을 만족하는 키로, Entity를 대표할 수 있어야한다.
• 자주 변경되지 않아야 하며 엔티티의 인스턴스를 유일하게 식별한다.

데이터베이스 키	설명
기본키 PK	후보키 중 엔티티를 대표할 수 있는 키
후보키 Candidate Key	유일성과 최소성, not null을 만족하는 키
슈퍼키 Super Key	유일성은 만족하지만, 최소성은 만족하지 않는 키
대체키 Alternate Key	여러 개의 후보키 중 기본키를 선정하고 남은 키

식별자의 분류

1. 대표성 여부에 따른 식별자 종류

   종류	설명
   주식별자 PK	유일성, 최소성, 대표성을 만족한다. 다른 엔티티와 참조관계로 연결될 수 있다.
   보조식별자	유일성, 최소성은 만족하지만 대표성을 만족하지 못해 참조관계를 연결하지 못한다.

   대표성?

   • 기본키에 선정되지 못한 후보키들이 ‘대표성’이 부족하여 기본키로 탈락된 것임

   • 그 엔티티가 가지고 있는 PK의 대표성이 떨어진다. 구분자로서의 의미가 떨어진다.

   • ex) 학생관리 시스템에서 각 학생의 ‘학번’은 모든 학생들을 구분하는 것에 의미를 두는 ‘주 식별자’이다.
     • 각 학생의 주민등록번호는 보조식별자이다. 각 학생들을 구분하는 것에 있어 유일성과 최소성을 만족하지만 학생관리 측면에서의 의미가 떨어진다.

2. 스스로 생성 여부에 따른 식별자 종류

   종류	설명
   내부식별자	엔티티 내부에서 스스로 만들어지는 식별자
   외부식별자	다른 엔티티와의 관계로 인해 만들어지는 식별자

   엔티티 내부에서 스스로 만들어진다?

   • 시스템이나 데이터베이스 내부에서만 사용되는 식별자.
   • ex) 데이터베이스의 기본키, 직원 정보를 저장하는 테이블에서 각 직원에게 부여된 고유ID 번호(1,2,3)

   다른 엔티티와의 관계로 인해 만들어진다?

   • 시스템 외부에서 사용되는 식별자, 사용자나 다른 시스템과의 상호작용에서 중요한 역할을 한다.
   • ex) 고객의 이메일 주소나 전화번호. 고객 관리 시스템에서 고객을 식별하기 위해 사용하는 이메일 주소

3. 속성의 수에 따른 식별자 종류

   종류	설명
   단일 식별자	하나의 속성으로 구분되는 식별자
   복합 식별자	두 개 이상의 속성으로 구성되는 식별자

4. 대체 여부에 따른 식별자 종류

   종류	설명
   본질 식별자	업무에 의해 만들어지는 식별자
   인조 식별자	최대한 범용적인 값을 사용하고 유일한 값을 만들기 위해 인위적으로 만들어지는 식별자

   업무에 의해 만들어진다?

   • 실제 세계에서 고유한 속성을 기반으로 하는 식별자

   • 데이터 항목의 본질적인 특성을 반영한다.

     • ex) 주민등록번호, ISBN, 이메일 주소

       • 주민등록번호는 실제 세계에서 사람을 식별하는데 사용한다.

   유일한 값을 만들기 위해 인위적으로 만들어진다?

   • 실제 세계의 속성과는 무관하게, 시스템 내에서 임의로 생성된 고유 식별자

   • ex) UUID, 직원 정보 테이블에서 각 직원에게 부여된 UUID는 인조 식별자

주 식별자 도출기준

1. 해당 업무에서 자주 이용되는 속성을 PK로 지정한다.
2. 명칭, 내역 등과 같이 이름으로 기술된다면 가능하면 PK로 지정하지 않는다.
   • 특히, 동명이인이 있을 수 있으므로 이름은 최악의 경우이다.
3. 복합으로 주식별자를 구성할 경우 너무 많은 속성이 포함되지 않도록 새로운 인조식별자를 생성한다.
   • 식별자의 단순성, 성능, 유지보수 용이성, 가독성, 서로 다른 관계 처리에서 인조식별자가 이점을 보인다.

식별자관계와 비식별자관계 (강한 개체와 약한 개체)

엔티티 사이의 관계 유형은 업무특징, 자식 엔티티의 주식별자 구성, SQL 전략 등에 의해 결정된다.

항목	식별자 관계	비식별자 관계
목적	강한 연결관계 표현	약한 연결관계 표현
자식 주식별자 영향	자식 주식별자의 구성에 포함됨	자식 일반 속성에 포함됨
표기법	실선 표현	점선 표현
연결 고려사항	- 반드시 부모 엔티티에 종속된다. - 자식 주식별자 구성에 부모 주식별자가 포함이 필요 - 상속받은 주식별자 속성을 타 엔티티에 이전 필요	- 약한 종속관계 - 자식 주식별자 구성을 독립적으로 구성 - 자식 주식별자 구성에 부모 주식별자 구분 필요 - 상속받은 주식별자 속성을 타 엔티티에 차단 필요 - 부모쪽의 참여관계가 선택관계

1. 식별자 관계

   • 부모 식별자를 자식 엔티티의 PK로 이용하는 경우에 NULL값이 오면 안되니, 반드시 부모 엔티티가 생성되어야 자기 자신의 엔티티(자식 엔티티)가 생성되는 경우다.
   • 부모한테 받은 속성을 자식 엔티티가 모두 사용하고 그것만을 PK로 사용한다면 부모-자식의 관계는 1:1 관계다
   • 부모로부터 받은 속성을 포함하고 다른 부모에게 받은 속성을 포함하거나 스스로 가진 속성과 함께 PK로 지정한다면 1:N 관계가 된다.
   • 이 때 식별자를 공유한 부모엔티티를 강한개체 , 공유 받은 자식 엔티티를 약한 개체 라고 한다.

2. 비식별자 관계

   

   • 부모 엔티티로부터 속성을 받았지만 자식 엔티티의 PK로 사용하지는 않고, 일반 속성으로만 사용 하는 경우이다.
   • 강한 개체의 기본키를 다른 엔티티의 기본키가 아닌 일반 컬럼의 관계로 가지는 것이다.

식별자 관계로만 설정할 때의 문제점

조인에 참여하는 주식별자속성의 수가 많을 경우 정확하게 조인관계를 설정하지 않고, 즉 누락하여 개발하는 경우가 생길 수 있다.

식별자 관계만으로 연결된 데이터 모델은 주식별자 속성이 지속적으로 증가할 수 밖에 없는 구조로서 개발자 복잡성과 오류가능성을 유발시킬 수 있는 요인이 될 수 있다는 것이다.

비식별자 관계로만 설정할 때의 문제점

일반적으로 각각의 엔티티에는 중요한 기준 속성이 있는데 이러한 기준속성은 부모엔티티의 PK속성으로부터 상속되어 자식엔티티에 존재하는 경우가 많다. 이러한 속성의 예로 ‘주민등록번호’, ‘사원번호’, ‘주문번호’, ‘목록번호’ 등이 있다.

이런 속성은 부모엔티티를 조회할 때도 당연히 쓰이지만 자식 엔티티의 데이터를 조회할 때도 해당 조건이 조회의 조건으로 걸리는 경우가 다수이다. 그런데, 데이터 모델링을 전개할 때 각 엔티티 간의 관계를 비식별자 관계로 설정하면 이런 유형의 속성이 자식 엔티티에 상속이 되지 않아 자식 엔티티에서 데이터를 처리할 때 쓸데없이 부모엔티티까지 찾아가야 하는 경우가 발생한다.

데이터 모델과 성능

정규화

정의

• 정규화는 최소한의 데이터 중복, 최대한의 데이터 유연성, 데이터 일관성을 위해 데이터를 분리하는 과정이다.
• 데이터의 중복을 제거하고 데이터 모델의 독립성을 확보한다.

정규화를 통해 업무 상 변화가 생겨도 데이터 모델의 변경을 최소화 할 수 있다.

• 데이터 모델이 자주 변경되는 경우 정규화하는 것이 좋다.
• 제1정규화부터 제5정규화까지 있지만 보통 제3정규화까지만 수행한다.

정규화와 이상현상

직원정보 테이블에서 새로운 직원 엔티티가 추가된다고 가정하자. 만약 그 직원의 부서 정보가 없으면 ‘부서코드’를 임의로 채워넣어야한다. 즉 불필요한 정보를 꼭 추가해야한다.

새로운 부서 엔티티도 마찬가지인데, 사원 정보가 없다고 해도 임의의 값으로 ‘사원번호’를 입력하지 않으면 추가할 수 없다.

이를 이상현상이라고 하며 이럴 때 테이블 분해(정규화)가 필요하다.

정규화의 절차 (제3정규화까지)

정규화 절차	설명	함수적 종속성
제1정규화	속성의 원자성을 확보하며 PK를 설정	완전 함수 종속성
제2정규화	PK가 2개 이상의 속성으로 이루어진 경우, 부분 함수 종속성을 제거	부분 함수 종속성
제3정규화	PK를 제외한 칼럼 간 종속성 제거 = 이행 함수 종속성 제거	이행함수 종속성
BCNF	PK를 제외하고 후보키가 있는 경우, 후보키가 기본키를 종속시키면 분해한다.

정규화는 함수적 종속성을 근거로 한다. 함수적 종속성이란 데이터들이 어떤 기준값에 의해 종속되는 현상이다.

이 때, 기준 값을 결정자(Determinant)라고 하고 종속되는 값을 종속자(Dependent)라고 한다.

1. 제1정규화
   • 사람 엔티티는 주민번호, 이름, 출생지, 주소라는 속성을 갖는다.
     • 여기서 이름, 출생지, 주소라는 속성은 주민번호라는 속성에 종속된다.
   • 어떤 사람의 주민번호가 신고되면 그 사람의 이름, 출생지, 주소가 생성되어 딱 하나의 유일한 값을 갖게 된다.
     • 즉, 주민번호가 이름, 출생지, 주소를 함수적으로 결정한다.
     • 기호로 표시하면 주민번호 -> (이름, 출생지, 주소)
     • X(주민번호)가 Y(이름, 출생지, 주소)를 함수적으로 종속한다. 이 때 X가 기본키가 되었다.
     • 이렇게 기본키를 잡는 것이 제1정규화이다.
   • 또 이렇게 종속자가 PK에만 종속되는 것이 완전 함수 종속성이다.
   • 릴레이션에 속한 모든 속성의 도메인이 원자 값으로만 구성되어 있으면 제1정규형에 속한다.

2. 제2정규화

   • 부분 함수 종속성을 제거하는 것이다.

     • 부분 함수 종속성이란 PK가 2개 이상의 칼럼으로 이루어진 경우에만 발생한다. PK가 한 칼럼이면 제2정규화는 생략한다.

     • ex)

     • PK인 회원 ID가 변경되면 이름도 변경된다.
       • 회원 ID가 이름을 함수적으로 종속하는 것이다.
       • 이런 경우를 부분 함수 종속성이라고 하며 분해가 필요하다.
     • 부분 함수 종속성을 제거하면 아래와 같다.
       • 
       • 회원이라는 새로운 테이블이 도출되고 회원ID가 PK가 된다.
         • 기존 테이블의 PK를 이루고 있는 속성은 건드리지 않는다.
3. 제3정규화
   • 이행 함수 종속성을 제거하는 것이다.
     • 이는 PK를 제외한 칼럼 간 종속성이 발생하는 것으로 제1정규화와 제2정규화를 거친 뒤 제3정규화를 수행해야한다.
       • 주 식별자를 제외한 칼럼 간 종속성에 관한 것이므로, PK와 관련성이 가장 낮다.
       • \(X \rightarrow Y, Y \rightarrow Z\) 를 만족하는 속성 \(X,Y,Z\) 에 대해서 \(X \rightarrow Z\) 를 성립할 때를 의미한다.

위와 같이 관리점이 관리점 코드에 종속되는 것이 이행 함수 종속성이다.

이를 분리해보자.

• 제3정규화를 수행하면 이렇게 관리점 테이블이 도출되고 관리점 코드가 기본키가 된다.

1. BCNF(Boyce-Codd Normalization Form)
   • \(X \rightarrow Y\) 에서, \(Y\) 가 \(X\)의 부분집합 (trivial FD)이거나 \(X\)가 릴레이션 \(R\)의 슈퍼키일 경우이다.

정규화와 성능

정규화의 문제점

• 정규화는 데이터 중복성을 제거한다.
  • 그래서 데이터 모델의 유연성을 높이고 성능 향상에 도움이 된다.
  • 하지만, 데이터 조회 시 조인을 유발한다.
    • CPU, Memory 사용량이 더 크다.

• 이런 부분은 반정규화를 적용해서 해결한다.

• 결정자에 의해 동일한 의미의 일반속성이 하나의 테이블로 집약되므로 한 테이블의 데이터 용량이 최소화되는 효과가 있다.

반정규화

• 반정규화는 DB 성능 향상을 위해, 데이터 중복을 허용하고 조인을 줄이는 방법이다.
• 조회 속도는 향상되지만 데이터 모델의 유연성은 낮아진다.
  • 항상 그런 것은 아니다!

반정규화를 수행하는 경우

• 정규화에 충실하면 종속성, 활용성은 향상되지만 수행 속도가 느려지는 경우
• 다량의 범위를 자주 처리해야하는 경우
• 특정 범위의 데이터만 자주 처리하는 경우
• 요약/집계 정보가 자주 요구되는 경우
• 반정규화 정보에 대한 재현의 적시성으로 판단
  • 여러 테이블에 대해 다량의 조인이 필요한 경우, 적시성 확보를 위해 반정규화한다.

반정규화 절차

1. 반정규화 대상 조사
   • 범위 처리 빈도 수 조사, 대량의 범위 처리 조사, 통계성 프로세스 조사, 테이블 조인 개수 를 통해 대상을 조사한다.

2. 다른 방법으로 유도 조사

   • 반정규화 외 다른 방법 (클러스터링, 뷰, 인덱스 튜닝, 파티셔닝, 응용 어플의 로직 등)이 있는지 조사한다.
     • 클러스터링: 클러스터드 인덱스는 인덱스 정보를 저장할 때 물리적으로 정렬해 저장하는 방법으로, 인접 블록을 연속적으로 읽기 때문에 성능이 향상된다 / 참고
     • 파티셔닝: 논리적으로는 한 테이블이지만 여러 데이터 파일에 분산되어 저장하는 것. 데이터 조회 시 애겟스 범위가 줄고, 데이터가 분할되어 있어 I/O 성능이 향상된다. 각 파티션을 독립적으로 백업/복구할 수 있다.
       • 데이터 값의 범위를 기준으로 하는 Range파티셔닝
       • 특정 값을 지정하는 List파티셔닝
       • 해시함수를 적용하는 Hash파티셔닝
       • 범위와 해시를 복합적으로 사용하는 Composite 파티셔닝
3. 반정규화 수행

테이블 반정규화: 테이블 병합/분할/추가

1. 테이블 병합

   기법	내용
   1:1 관계 테이블 병합	1:1 관계를 통합하여 성능 향상
   1:N 관계 테이블 병합	1:N 관계를 통합하여 성능 향상
   슈퍼/서브 타입 테이블 병합	슈퍼/서브(부모-자식) 관계를 통합하여 성능향상

   슈퍼/서브 타입 변환 방법

   • 데이터 양 & 트랜잭션의 유형에 따라 변환

     • 

     • 구분	One to One type	plus type	Single Type
       특징	개별 테이블 유지	슈퍼 + 서브타입 테이블	하나의 테이블
       확장성	우수함	보통	나쁨
       조인 성능	나쁨	나쁨	우수함
       I/O량 성능	좋음	좋음	나쁨
       관리용이성	좋지 않음	좋지않음	좋음(1개)
       트랜잭션 유형에 따른 선택방법	개별 테이블로 접근이 많은 경우 선택	슈퍼 + 서브 형식으로 데이터를 처리하는 경우 선택	전체를 일괄적으로 처리하는 경우 선택

2. 테이블 분할

기법	내용
수직분할	컬럼 단위 테이블을, I/O 분산처리를 위해 1:1로 분리하여 성능 향상 트랜잭션이 처리되는 유형을 파악하는 것이 선행되어야한다.
수평분할	Row 단위로 집중 발생되는 트랜잭션을 분석해 I/O및 데이터 접근성의 효율을 높여 성능을 향상하기 위해 특정 값에 따라 Row단위로 테이블을 쪼갠다.

1. 테이블 추가

기법	내용
중복 테이블 추가	다른 업무거나 서버가 다른 경우 동일 테이블 구조를 중복하여 원격 조인을 제거하여 성능을 향상
통계 테이블 추가	SUM, AVG 등을 미리 수행하여 계산해 둠으로써 조회 시 성능을 향상
이력 테이블 추가	이력 테이블 중 마스터 테이블에 존재하는 레코드를 중복하여 성능 향상
부분 테이블 추가	한 테이블의 전체 칼럼 중 자주 이용하는 집중화된 칼럼들의 I/O를 줄이기 위해 해당 칼럼을 모아놓은 별도의 테이블을 생성

속성의 반정규화

기법	내용
중복 칼럼 추가	조인을 감소시키기 위해 칼럼 중복하기
파생 칼럼 추가	계산에 의해 발생되는 성능 저하를 예방하기 위해 미리 값을 계산한 칼럼 추가
이력 테이블 칼럼 추가	불특정 조회로 인한 성능 저하 예방을 위해 이력 테이블에 기능성 컬럼 (최근 값 여부, 시작과 종료일자 등) 추가
PK에 의한 칼럼 추가	단일 PK내에서 특정 값을 조회하는 경우 생기는 성능 저하를 예방하기 위해 이미 존재하는 PK데이터를 일반 속성으로 포함하는 칼럼을 추가
응용 시스템 오작동을 위한 칼럼 추가	업무적으로는 의미가 없지만 사용자의 잘못된 데이터 처리로 원래 값 복구를 원하는 경우, 이전 데이터를 임시적으로 중복하여 보관하는 칼럼추가

관계의 반정규화

기법	내용
중복 관계 추가	데이터 처리를 위한 여러 조인이 발생시키는 성능 저하 예방을 위해 추가적인 관계를 맺는 것

Row Chaining, Row Migration

• Row Chaning
  • Row에 데이터가 insert 후 delete 되어 한 행이 삭제되고 해당 블록에 빈 공간이 생겼을 때 새로운 것이 입력된다고 가정
  • 새로운 데이터가 입력될 때 처음에 빈 공간에 있는 블록에 입력되고 그 공간이 부족할 때 새로운 블록에 나머지 데이터를 입력하는 것을 로우 체이닝이라고 한다.
  • Row길이가 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 않고, 두 개 이상의 블록에 걸쳐 한 Row가 저장되어 있는 형태이다
• Row Migration
  • Row에 입력될 수 있는 데이터 영역에 데이터가 모두 입력되어 저장 공간이 부족한 경우에서 기존 데이터의 변경 작업이 일어난다고 가정하자.
  • 변경 작업에 의해 공간이 더 필요한데 저장 공간이 없을 경우 새로운 블록으로 이동시켜 변경 작업을 수행하는 것이 로우 마이그레이션 이다.
  • 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 블록에 저장하지 못하고 다른 블록의 빈 공간을 찾아 저장하는 방식이다.

분산 데이터베이스

정의

분산 데이터베이스는 데이터베이스를 연결하는 빠른 네트워크 환경을 이용해 데이터베이스를 여러 지역의 여러 노드로 위치시켜 사용성/성능 등을 극대화 시킨 데이터베이스이다.

효과적인 경우

• 성능이 중요한 사이트에 적용한다.
• 공통코드, 기준정보, 마스터 데이터 등에 대해 분산환경을 구성하면 성능이 좋아진다.
• 실시간 동기화가 요구되지 않을 때 좋다. 거의 실시간(Near Real Time)업무일 때도 분산 환경을 구성할 수 있다.
• 특정 서버에 부하가 집중이 될 때 부하 분산을 위해 쓸 수 있다.
• 백업 사이트(Disaster Recovery Site)를 구성할 때, 간단한 분산기능을 적용하여 구성할 수 있다.

투명성

투명성	설명
분할 투명성	고객은 하나의 논리적 릴레이션이 여러 단편으로 분할되어 각 단편의 사본이 여러 시스템에 저장되어 있음을 인식할 필요가 있다.
위치 투명성	고객이 사용하려는 데이터 저장 장소를 명시할 필요가 없으며 고객은 데이터가 어디에 있든 동일한 방법으로 데이터 접근이 가능해야한다.
지역 사용성 투명성	지역DBMS와 물적DBMS 사이의 매핑이 보장되어 각 지역 시스템 이름과 무관한 이름을 사용할 수 있다.
중복 투명성	DB객체가 여러 시스템에 중복되어 존재해도 고객과는 무관하게 데이터 일관성이 유지된다.
장애 투명성	DB가 분산된 각 지역의 시스템이나 통신망에 이상이 생겨도 데이터 무결성은 보장된다.
병행 투명성	여러 고객이 응용 프로그램이 동시에 분산DB에 대한 트랜잭션을 수행해도 결과에 이상이 없다.

데이터베이스 설계 방식

1. 상향식 설계 방식: 지역스키마 -> 전역 스키마 순으로 작성
2. 하향식 설계 방식: 전역 스키마 -> 지역 스키마 순으로 작성

장단점

장점	단점
신뢰성/가용성/효용성/융통성 high	설계/관리 복잡, 비용 high
병렬 처리 수행 -> 빠른 응답, 통신 비용 low	데이터 무결성에 대한 위협
시스템 용량 확장 easy	보안 관리/통제가 어렵고 오류의 잠재성이 크다.
각 지역 사용자들의 요구 수용 빠름	응답 속도 불규칙