[ SQLD ] 데이터 모델과 성능 240801

성능 데이터 모델링 개요

데이터 모델링을 할 때 데이터베이스 성능을 고려하여 수행하는 것

성능 데이터 모델링을 수행하는 시점은 빠를수록 좋다.

분석 / 설계 단계에서 성능 모델링을 수행해야 재업무 비용이 최소화된다.

일반적인 경우는 대략적인 설계 → 성능 저하 → 해당 부분만 SQL을 튜닝한다.

 

성능 데이터 모델링 진행 순서

- 정규화를 정확하게 수행한다 → 주요 관심사별로 테이블을 분산시킴

- 데이터베이스 용량산정 수행 → 각 엔터티에 어느 정도의 트랜잭션이 들어오는지 파악

→ 데이터를 조회하고, 삽입하고, 변경하고, 삭제하는 등 SQL문을 통해 가장 작업이 빈번하게 발생하는엔터티를 파악한다.- 데이터베이스에 발생되는 트랜잭션의 유형을 파악한다→ C ( Create, 삽입하기 ) R ( Read, 조회하기 ) U ( Update, 갱신하기 ) D ( Delete, 삭제하기 ) 등발생하는 작업의 유형을 파악한다.- 정규화가 정확히 수행된 상태에서 데이터베이스 용량과 트랜잭션의 유형에 따라 반정규화를 수행한다.반정규화는 정규화의 반대 반향으로 수행하는 것으로 주로 테이블, 속성, 관계에서 변형이 이루어지게 된다.

 

1970년대에 관계형 데이터베이스가 제안되었고,

1980년대에 관계형 데이터베이스가 상용화 되었다.

기업의 핵심 데이터는 대부분 관계형 데이터베이스로 저장되어 있고,

SQL 문장을 통해 관계형 데이터베이스를 사용하고 관리한다.

 

RDBMS ( 관계형 데이터베이스 시스템 ) 은 클라이언트가 요청한 데이터를

데이터베이스에서 추출해서 클라이언트의 요청에 응답한다.

 

관계형 데이터베이스는 데이터를 테이블로 관리하고, 복수의 테이블로 구성되어 있다.

테이블은 관계형 데이터베이스의 기본단위로 행과 열의 2차원 구조를 가진 데이터 저장 객체다.

열은 테이블에서 세로 방향으로 이루어진 개별 속성으로 칼럼 ( Column ) 또는 필드 ( Field ) 라고 한다.

행은 테이블에서 가로 방향으로 이루어진 연결된 데이터로 로우 ( Row ) 또는 레코드 ( Record ) 또는 튜플 ( Tuple ) 이라고 한다.

현장에서는 레코드 라는 용어로 자주 사용되는 것 같다.

테이블이 되려면 ( 하나의 ) 기본 키 ( Primary Key , PK ) 가 반드시 있어야 한다.

기본 키가 각 레코드를 구별하는 기준이 된다.

 

Relation Model Constraints

1. 도메인 제약 ( Domain Constraints )

- 속성 ( Attribute ) 에 대한 제약 → 속성이 ~ 해야 한다에 대한 제약

예를 들어 나이라는 속성에는 숫자이면서 숫자의 범위는 1에서 100이어야 한다.

나이라는 속성이 갖는 값의 도메인은 숫자이면서 그 숫자의 범위는 1에서 100이어야 하고,

그 외의 값을 갖게 되면 ( 도메인에 정의된 값이 아니면 ) 도메인 제약에 위배되었다고 한다.

 

또한 속성은 원자성을 가져야 해서 → 값이 더 이상 쪼개지지 않아야 한다.

취미라는 컬럼에 축구, 농구, 배구 라는 값을 가질 수 없다 → 별도의 테이블을 만들어 관리한다.

관계형 데이터베이스에서는 Composite Attribute 와 Multivalued Attribute는 허용되지 않는다.

 

Not Null 이 아닌경우Null 값은 허용된다.

 

2. 키 제약 ( Key Constraints )

- 릴레이션 ( Relation ) 에 대한 제약 → 테이블은 키 ( PK ) 를 가져야 한다에 대한 제약

릴레이션의 모든 튜플은 서로 식별이 가능해야 한다.

튜플의 수를 무제한으로 가정했을 때, 각 튜플이 어떤 속성에 의해서 식별이 가능한지를 생각해야 한다.

테이블의 각 튜플을 식별할 수 없다면 키가 없는 것 → 키 제약에 위배된 것이다.

키 제약에 위배된 경우, 인조식별자 ( ex. No ) 를 만들어서 각 튜플을 서로 구별할 수 있게 한다.

 

3. 개체 무결성 제약 ( Entity Integrity  Constraints )

- 기본 키 ( Primary Key ) 에 대한 제약 → 테이블이 갖는 PK는 Unique & Not Null해야 한다.

사람 테이블에서 각 튜플을 식별할 수 있는 키가 학번과 주민번호가 있다고 하면

학번과 주민번호는 대표성을 가질 수 있는 후보 키 ( Candidate Key ) 가 된다.

후보 키 중에 대표성을 갖는 키 하나를 선정하면 그것은 기본 키 ( 주 키, Primary Key, PK ) 가 된다.

 

주민번호만 있어도 키가 되지만 주민번호와 이름을 합쳐도 키가 된다.

후보키 + 알파는 Super Key 라고 하고,

Super Key중에서 최소성을 갖는 키가  후보 키 ( Candidate Key ) 가 되고,

후보 키 중에서 하나를 선정하면 대표성을 갖게 되고 이는 Primary Key 가 된다.

 

Primary Key 는 NOT NULL & UNIQUE 해야 한다.

 

4. 참조 무결성 제약 ( Referential Integrity Constraints )

- 외래 키 ( Foreign Key ) 에 대한 제약

 

Foreign Key에 대한 설명

테이블 ( 릴레이션 ) 1이 테이블2를 참조하는 경우

테이블 2의 기본 키는 테이블1에서 외래 키로 사용된다.

외래 키 ( Foreign Key, FK ) 는 자기 자신이 속한 테이블을 참조할 수도 있다.

 

테이블1이 테이블2를 참조하는 경우 테이블1의 FK는

1) Null 이거나 ( Primary Key 는 Null 이어서는 안된다. )

2) ( Null 이 아닌 경우 ) 테이블2의 Primary Key에 실제로 존재하는 값으로 구성되어야 한다.

 

---------------------------------------------------------------------------------------------------------------

 

직원

이름

주민번호(PK)

감독자 주민번호 (FK)

부서번호 (FK)

 

  부서

부서명

부서번호(PK)

관리자 주민번호 (FK)

1) 직원 릴레이션의 PK는 주민번호, 부서 릴레이션의 PK는 부서번호

2) 직원 릴레이션의 감독자 주민번호는 직원 릴레이션의 주민번호를 참조하는 FK다.

직원 릴레이션의 부서번호는 부서 릴레이션의 부서번호를 참조하는 FK다.

부서 릴레이션의 관리자 주민번호는 직원 릴레이션의 주민번호를 참조하는 FK다.

 

 

새직원 < '유관순', '555', NULL, 4> 삽입시 PK인 주민번호에 555가 이미 존재하기에

PK는 중복 삽입이 안되므로 ( UNIQUE 제약 위배 ) 개체 무결성 제약에 위배된다.

 

새직원 <'이방원', '123', '456', 5> 삽입시 PK인 주민번호를 참조하는 감독자 주민번호에는

NULL 이 아닐 경우 삽입할 값이 PK인 주민번호에 존재하는 값이어야 하는데

456은 PK인 주민번호에 존재하는 값이 아니므로 참조 무결성의 제약에 위배된다.

 

SSN = '666' 인 직원 삭제의 경우 참조 무결성의 제약에 위배된다.

부서 릴레이션의 관리자 주민번호는 직원 릴레이션의 주민번호를 참조하고 있는데

직원 릴레이션의 주민번호가 666인 직원이 삭제가 되면

부서 릴레이션의 관리자 주민번호가 참조하는 값이 없게 되기 때문이다.

→ 아무 조치 없이 직원 릴레이션의 주민번호(PK) 666인 레코드 삭제시 참조 무결성의 제약 조건에 위배 된다.

 

정규화

정규화와 정규형: 정규화의 결과인 정규형

( 1차 정규화를 거친 ) 1차 정규형 ( 1NF ) 은 모든 값 ( 데이터 ) 이 원자값을 가진 상태다.

원자값을 가진다는 건 데이터가 더 이상 쪼개질 수 없는 상태다.

ex. Multivalued Attribute 를 새로운 테이블로 만들어 해당 데이터가 원자값을 갖게 한다.

 

정규화는 함수적 종속성 ( FD, Functional Dependency ) 에 기반한다.

함수는 x값 대입의 ( 하나의 ) y 값이 결과로 산출되는 것을 의미한다.

함수적 종속성은 예를 들어 학번과 혈액형이 있을 때,

( 하나의 ) 혈액형이 있을 때 ( 하나의 ) 학번을 지목할 수 없는데

이런 경우 혈액형은 학번을 함수적으로 결정하지 못한다.

학번은 혈액형에 함수적으로 종속되지 않는다 라고 한다.

 

( 하나의 ) 학번을 대입했을 때 ( 하나의 ) 혈액형을 지목할 수 있다면

학번은 혈액형을 함수적으로 결정한다 라고 얘기하고,

혈액형은 학번에 함수적으로 종속된다 ( Functionally Dependent ) 라고 말할 수 있다.

함수적으로 종속되는 관계를 함수적 종속성 이라고 하고,

이런 함수적 종속성에 기반하여 정규화를 진행한다.

 

정규화와 성능

정규화를 하면 데이터가 관심사별로 묶인다.

관심사별로 묶인다는 것이 예를 들어 학생이라고 한다면

학생과 관련있는 학번, 이름, 나이 등의 속성을 가진 ( 하나의 ) 릴레이션 ( 테이블 ) 이 만들어지고,

학생 릴레이션은 여러 명의 학생 인스턴스로 구성된다.

이렇게 데이터가 관심사별로 묶이게 되면 ( 데이터베이스 시스템의 ) 성능이 향상된다.

성능이 향상된다는 것은 데이터를 빠르게 삽입, 갱신, 삭제 등이 가능하다는 것이다.

 

그런데 조회의 경우 성능면에 저하를 야기할 수 있다.

정규화를 하면 관심사별로 테이블이 나뉘게 되는데,

데이터 조회시 조인 ( 테이블 묶음 )  이 많이 발생할 경우 성능이 저하된다.

→ 정규화를 통해 일반적으로 성능이 향상되나 조회의 경우 처리 조건에 따라 성능이 향상되거나 저하된다.

 

정규화를 하면 입력, 수정, 삭제의 경우 성능이 ( 항상 ) 향상되나

조회시 처리 조건에 따라 성능이 향상되거나 혹은 저하될 수 있다.

 

반정규화: 정규화를 수행 후 성능상 저하가 발생한다면

정규화된 엔터티, 속성, 관계에 대해 성능 향상을 목적으로

중복, 통합, 분리를 수행하는 것을 의미한다.

( 정규화를 아예 수행하지 않는 건 비정규화 )

 

성능 향상을 목적으로 반정규화를 하는데,

반정규화 수행시 일반적으로 데이터 무결성이 침해된다.

( → 반정규화 수행시 대체적으로 속성 ( 칼럼 ) 의 중복이 많아진다. )

가능한 데이터의 무결성이 많이 침해되지 않는 선에서 최소한의 반정규화를 수행한다.

 

뷰 ( View ) 테이블은 뷰 ( View ) 를 통해 테이블이 생성된 결과다.

뷰 ( View ) 는 ( 임시 ) 테이블을 만드는 방법이 저장된 것이다.

방법이라는 것은 SQL 문을 의미하고, SQL문은 CREATE table ~ 이렇게 구성될 것이다.

 

생성한 뷰 자체는 성능 향상을 가져오지는 않는다.

신중하게 설계된 뷰를 재사용할 때 성능이 향상된다.

→ 잘 만들어진 뷰를 사용하면 테이블을  마구 만드는 것보다 성능이 향상될 것이다.

 

----------------------------------------------------------------------------------------------------

 

( 하나의 ) 주문은 여러 개의 제품을 포함할 수 있고,

( 하나의 ) 제품은 여러 개의 주문에 포함될 수 있다.

주문 릴레이션의 PK ( 주문 번호 ) 와 제품 릴레이션의 PK ( 제품 번호 ) 는

주문목록 릴레이션에서 외부 키이자 식별자로 존재하는데

주문 릴레이션과 제품 릴레이션을 연결하는데 존재가치가 있는 주문목록 릴레이션을

연관 엔터티라고 하고, 연관 엔터티의 경우 식별자 이외의 일반속성이 존재하지 않아도 된다.

( 일반 엔터티의 경우 식별자 이외의 일반속성이 존재하지 않는 경우 바람직하지 않다. )  

 

( 하나의 ) 주문번호만 알면 그 주문번호에는 어떤 제품들이 포함되어 있는지를

제품번호를 통해 알게 되고, 제품번호를 통해 각 제품들의 가격을 알 수 있다.

이런 경로를 통해 총 주문금액을 알 수 있지만 조인을 통한 성능저하로

질의가 예상되는 값을 미리 계산하여 주문 릴레이션에 주문총금액이라는 속성에 저장한다.

이런경우 주문총금액은 칼럼에 의한 파생이다.

→ ( 반정규화 기법중 파생 칼럼 추가에 해당한다 )

 

----------------------------------------------------------------------------------------------------

 

테이블 반정규화

두 테이블의 동시 조회가 많은 경우 두 개의 테이블을 합친다 → 관계 병합

테이블에 중복 칼럼을 추가했는데 해결이 안될 정도로

상대편의 테이블에 있는 칼럼을 많이 갖고 와야 한다면

아예 두 테이블을 하나로 합친다.

그 결과 이행함수 종속이 포함되는데 3정규형이 2정규형이 된다.

 

슈퍼 ( Super Type ) / 서브타입 ( Sub Type ) 모델의 변환 → 일반화 관계를 모델링하는 경우

일반화 ( Generalization ) 관계를 표현하는데

여러 엔터티의 공통 속성을 Super Type,

Super Type 을 상속한 개별 속성을 Sub Type 으로 구성한다.

 

공통적인 속성을 위에 만들고 ( Super Type )

공통적인 속성을 상속하는 개별 속성들로 ( Sub Type ) 구성 ( 공통적이지 않은 속성들을 일반 속성을 추가 )

공통적인 속성을 상속하는 개별 속성들은 Super Type의 PK를 외래 키이자 식별자로 둔다.→ 개별로 접근하는 트랜잭션이 많은 경우 1 : 1 로 변환한다.개별로 접근한다는 것은  다른 테이블의 도움을 받지 않고, 하나의 테이블에 접근한다는 것이다.

 

슈퍼타입 + 서브타입 접근 트랜잭션이 많은 경우 Plus Type ( 슈퍼타입과 서브타입을 합친 테이블 ) 으로 변환한다.

 

여러 서브 타입에 대한 동시 접근이 많을 때 All in One Type 으로 변환

 

※ 해시 함수 ( Hash Function )임의의 길이의 데이터를 짧은 길이의 데이터로 매핑하는 함수

→ 긴 길이의 데이터를 짧은 길이의 데이터로 변환

 

ex.- 123456 → 16- 203050 → 20 

 

참고 강의

[ 유튜브 ] 데이터베이스실무(SQLD대비) | 김남규 교수