데이터 모델과 성능

성능 데이터 모델링의 개요

정의

• 데이터베이스의 성능 향상을 목적으로 데이터 모델 설계 시점부터 정규화, 반정규화, 테이블 통합, 테이블 분할, 조인 구조, PK, FK 등 여러 가지 성능과 관련된 사항들이 데이터 모델링 작업에 반영될 수 있도록 하는 것임

수행 시점

• 분석/설계, 구현, 테스트, 운영 의 4단계에서 뒤로 갈수록 성능 데이터 모델링의 비용이 더 많이 들어가게 됨

정규화

정규화 용어

• 정규화 : 함수적 종속성(Functional Dependency) 등과 같은 이론에 근거하여 관계형 데이터베이스 테이블의 삽입/삭제/갱신 Anomaly 현상 발생을 최소화하기 위해 좀 더 작은 단위의 테이블로 설계하는 과정

• 함수적 종속성(FD Functional Dependency) : 테이블의 특정 컬럼 A 의 값을 알면, 다른 칼럼 B의 값을 알 수 있을 때 ( f(a) ⇒ b ) 칼럼 B는 칼럼 A에 함수적 종속성이 있다고 함
• 예) 주민번호를 알면 고객명을 알 수 있을 때 ‘고객명은 주민등록번호에 함수적 종속성이 있다’라고 할 수 있음

• 결정자(Determinant) : 위의 함수적 종속성 에서 컬럼 A를 결정자 라고 함. 즉 주민번호는 고객명을 결정하므로 주민번호는 고객명의 결정자임

• 다치종속(MultiValued Dependency) : 결정자 컬럼 A에 의해 칼럼 B의 값을 다수 개 알수 있을 때, 칼럼 B는 칼럼 A에 다치 종속되었다고 함
• 예) 학번을 통해 해당 학생이 수강신청한 다수 개의 수강과목을 알 수 있을 때, ‘수강과목은 학번에 다치종속된다’ 라고 할 수 있음

정규화 효과 및 장점

• 정규화는 식별자가 아닌 속성이 한 번만 표현됨에 따라 중복이 최소화됨

• 이에 따라 데이터 품질이 확보되고 저장공간이 절약되며, DML 처리 시 성능이 향상됨

정규화 이론

• 1차, 2차, 3차, 보이스-코드 정규화는 함수 종속성에 근거하여 정규화를 수행

• 4차 정규화는 속성의 값이 여러 개 발생하는 다치종속, 5차 정규화는 조인에 의해 발생하는 이상현상 제거로 정규화를 수행함

• 1차 정규화 : 한 속성에 여러 개의 속성값을 갖거나 같은 유형의 속성이 여러 개인 경우 해당 속성을 분리함
• 1차 정규화 작업으로 속성의 원자성을 확보

• 2차 정규화 : 주 식별자 에 완전 함수 종속되지 않은 속성을 분리한다.
• 2차 정규화 작업으로 부분 종속 속성(Partial Dependency Attribute)이 된 속성을 분리
• 부분 종속 속성 : PK의 일부에만 함수적 종속성을 가지는 컬럼

• 3차 정규화 : 일반 속성끼리 함수 종속이 발생한 속성을 분리
• 3차 정규화 작업으로 이행적 종속 속성(Transitive Dependency Attribute)을 분리

• 보이스-코드 정규화 : 결정자 안에 함수 종속을 가진 주식별자 속성을 분리

• 4차 정규화 : 다가 종속(Multi-Valued Dependency) 속성을 별도의 엔티티로 분리함

• 5차 정규화 : 결합 종속(Join Dependency)일 경우는 2개 이상의 엔티티로 분리

제 1 정규형

• # 은 PK를 가리키는 표기

• 제 1정규형을 만족하려면 회원 연락처 엔티티를 추가하여 연락처 컬럼의 여러 값을 나눔

제2 정규형

• 제 2정규형은 제 1 정규형을 만족하고 모든 PK 가 아닌 칼럼은 PK 전체에 종속되어야 함

• PK에 종속적이지 않거나 PK 중 일부 컬럼에만 종속적인 컬럼(부분 종속)은 분리되어야 함

• 고객명과 고객등급이 (고객아이디, 주문순번)에 완전 종속이 아닌 고객아이디에만 부분종속하고 있음

• 고객 엔티티를 추가하여 고객아이디를 식별자로 하여 고객명과 고객등급 속성을 관리함으로 2개의 엔티티 모두 모든 속성이 식별자만으로 함수 종속을 가지게 됨
• 주문일자는 고객아이디 + 주문순번 속성의 조합이 결정
• 고객명과 고객등급은 고객아이디 속성이 결정

제 3정규형

• 제 3정규형은 제 2정규형을 만족하고 일반 속성들 간에도 함수 종속 관계가 존재하지 않아야 함

• 고객아이디 속성을 제외하고 직업코드와 직업명 사이에 함수종속이 발생
• 식별자를 제외한 일반 속성 간에 함수 종속이 발생하는 경우 제3정규형 위반

정규화와 성능

• 정규화를 수행한 후, 전에 없었던 조인이 발생하게 되더라도 효율적인 인덱스 사용을 통해 조인 연산을 수행하면 성능상의 단점은 거의 없다

• 정규화를 수행한 후, 적은 용량의 테이블이 생성된다면 조인 연산 시 적은 용량의 테이블을 먼저 읽어 조인을 수행하면 되므로 성능상 유리
• 정규화를 통해서 읽게 되는 데이터의 양도 줄어들 수 있다. 하나의 테이블에 다 데이터가 뭉쳐져 있으면 전체를 읽어야 하는데 분리되어 있으면 적은 양의 데이터만 읽고 데이터를 조회해 올 수 있기 때문

• 정규화가 제대로 되지 않으면 비슷한 종류의 속성이 여러 개가 되어 과도하게 많은 인덱스가 만들어질 수 있다. 정규화를 한다면 하나의 인덱스만 만들어도 된다.
• 정규화를 하는 이유는 중복이 최소화되어 데이터 품질이 확보되고 저장공간이 절약. DML 처리 시 성능도 향상됨 ( 그 이유는 인덱스를 과도하게 만들 필요가 없기에 DML 시 이점을 갖게 되는것)

정규화를 통한 성능 개선 사례

제 1정규화를 통한 성능 개선

• 위와 같이 테이블 구조가 되어있을 때, A유형기능분류코드,… 각각에 대해 모두 인덱스를 붙여야 함

• 인덱스가 많아지면 DML 성능에 영향을 주게 되고, 기능분류코드 유형이 추가될 때마다 추가적으로 인덱스를 생성해야 함

• 이렇게 개선 시, 모델유형기능 분류의 (유형코드, 기능분류코드, 모델코드) 에만 인덱스 단 1개를 생성함으로 인덱스 갯수가 줄어듬

제 2정규화를 통한 성능개선

• 성능상 유리한점
• 제2정규형을 만족하도록 엔티티를 나눈 경우에 조회 성능은 Unique Index로 조인이 걸리기 때문에 하나의 테이블에서 조회하는 것과 성능 차이가 미미하다
• 조회 조건을 ‘관서등록일자가 2020년 이후 관서를 모두 조회하라’로 한다면 제 2정규화 된 테이블이 훨씬 빠르다. 정규화되지 않은 모델에서는 납부자번호만큼 누적된 데이터를 다 읽어야 하지만 정규화된 모델에서는 관서수만큼만 존재하는 데이터를 읽어 곧바로 결과를 낼 수 있기 때문임

반정규화

• 데이터를 고의적으로 중복 저장하여 조회 성능을 향상시키기 위한 기법. 더 넓은 의미로는 조회 성능을 향상시키기 위해 정규화된 데이터 모델에서 중복, 통합, 분리 등을 수행하는 모든 과정을 의미함

• 데이터 무결성이 깨질 수 있는 위험을 무릅쓰고 데이터를 중복하여 반정규화를 적용하는 상황
• 데이터 조회 시 디스크 I/O 가 많아서 성능이 저하되는 경우
• 테이블 간 경로가 너무 멀어 조인으로 인한 성능저하가 예상되는 경우
• 칼럼을 계산하여 읽을 때 성능이 저하될 것으로 예상되는 경우

반정규화의 절차

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

2. 다른 방법 유도 검토
• 뷰 테이블
• 클러스터링 적용
• 인덱스의 조정
• 응용 애플리케이션 변경

3. 반정규화 적용
• 테이블 반정규화
• 속성의 반정규화
• 관계의 반정규화

반정규화 기법

테이블 반정규화

• 테이블 병합 : 테이블을 병합하여 테이블 간 조인 연산을 제거하는 방법
• 1:1 관계 테이블 병합 : 1:1 관계를 통합하여 성능향상
• 1:M 관계 테이블 병합 : 1:M 관계를 통합하여 성능향상
• 슈퍼/서브타입 테이블병합 : 슈퍼/서브 관계를 통합하여 성능향상

• 테이블 분할
• 수직분할 : 하나의 테이블을 디스크 I/O 분산 처리하기 위해 속성(컬럼)을 분할하여 두 개 이상의 테이블로 분할. 트랜잭션 처리되는 유형 파악이 선행 되어야 함
• 예) 게시글 테이블의 조회수 컬럼은 업데이트가 빈번하게 일어나므로 게시글의 조회수 저장용 테이블을 하나 더 만들어서 관리
• 테이블의 컬럼 수가 많을 때 테이블을 수직 분할하면 컬럼 수가 적어지므로 로우 마이그레이션, 로우 체이닝 현상이 감소하여 성능이 좋아짐
• 수평분할 : 로우 단위로 집중 발생되는 트랜잭션을 분석하여 디스크 I/O 및 데이터 접근 효율을 높여 성능을 향상하기 위해 로우 단위로 테이블을 쪼개는 방법
• 예) 요금납부 테이블을 각 년월별 요금납부 테이블로 분할
• 범위 파티셔닝: 특정 기간으로 데이터를 분리
• 리스트 파티셔닝 : 값의 종류(예: 지역별)로 데이터를 분리
• 해시 파티셔닝 : 데이터 입력 시 경합에 의한 성능 부하를 해소하기 위해 사용, 지정된 HASH 조건에 따라 해싱 알고리즘을 적용하여 테이블을 분리

• 테이블 추가
• 중복 테이블 추가 : 동일한 테이블 구조를 중복하여 원격조인을 제거
• 다른 업무이거나 서버가 다른 경우 동일한 테이블 구조를 중복하여 원격 조인을 제거하여 성능 향상
• 통계 테이블 추가 : SUM, AVG 등을 미리 수행하여 계산
• 이력 테이블 추가 : 마스터 테이블에서 자주 조회되는 레코드를 이력테이블에 중복하여 테이블 추가
• 부분 테이블 추가 : 특정 테이블에서 전체 컬럼 중 자주 조회되는 집중화된 컬럼들이 있을 때, 디스크 I/O를 줄이기 위해 해당 칼럼들을 모아놓은 별도의 반정규화된 테이블을 생성

컬럼 반정규화 기법

• 중복 칼럼 추가 : 조인 연산으로 인한 성능 저하를 예방하기 위해 중복된 칼럼을 추가하여 조인 연산 하지 않도록 함

• 파생 칼럼 추가 : 미리 값을 계산하여 칼럼에 보관 (eg. 총매출금액)

• 이력 테이블 칼럼 추가 : 대량의 이력 데이터를 처리할 때 불특정한 날 조회나 최근 값을 조회할 때 나타날 수 있는 성능 저하를 예방하기 위해 기능성 칼럼(최근값 여부, 시작과 종료일자 등)을 추가

• PK에 의한 칼럼 추가 : 복합의미를 갖는 PK를 단일 속성으로 구성하였을 경우 단일 PK 안에서 특정 값을 별도로 조회하는 경우 성능저하가 발생할 수 있다. (eg. 주문번호 값의 구성이 “상품코드 + 주문일자(YYYYMMDD) + 주문순번”으로 되어 있을 경우 주문일자를 일반 속성으로 도출한 후 해당 컬럼을 인덱스로 생성)

• 응용시스템 오작동을 위한 칼럼 추가 : 이전 데이터를 임시적으로 중복하여 보관

관계 반정규화 기법

• 중복 관계 추가 : 여러 경로를 거쳐 조인 할 수 있지만, 성능 저하를 예방하기 위해 추가적인 관계를 맺음 (eg. A>B>C 조인을 A>C 조인으로 만듦)

대량 데이터에 따른 성능

• 로우 체이닝과 로우 마이그레이션이 발생하여 많은 블록에 데이터가 저장되면 데이터 조회 시 절대적인 블록 I/O의 횟수가 많아지게 됨. 블록 I/O 횟수가 많아지면 디스크 I/O를 할 가능성도 높아짐
• 디스크 I/O 는 특정 블록을 데이터베이스 내 메모리에서 찾을 수 없어서 디스크를 읽게 되는 상황을 뜻함
• 디스크 I/O 는 고비용의 작업이므로 DBMS 성능의 급격한 저하를 유발함

데이터베이스 구조와 성능

슈퍼/서브 타입 모델

• 슈퍼/서브 타입 모델 변환 방법
• 슈퍼 타입 : 슈퍼/서브 타입 모델을 하나의 테이블로 변환한 것 (eg. 개인고객, 법인고객 등 구분이 있을때 고객 테이블 하나에 다 넣도록 구성)
• 서브 타입: 슈퍼/서브 타입을 서브 타입 테이블들로 변환한 것 (eg. 개인고객, 법인고객 테이블로 구성)
• 개별 타입 : 슈퍼/서브 타입을 슈퍼 타입과 서브 타입의 각각 개별 테이블로 변환한 것(eg. 고객, 개인고객, 법인고객 테이블로 구성)

슈퍼/서브 타입 논리 데이터 모델

슈퍼 타입(물리)로 변환

• 하나의 테이블에 다 넣는 것. 1010만 건 데이터가 모이게 됨

• 슈퍼/서브 타입의 데이터 처리 시 항상 통합하여 처리한다고 가정하면 하나의 테이블로 구성하는 것이 성능 상 유리함

• 항상 통합하여 처리하는데 개별로 분리하게 되면 조인 연산 혹은 UNION ALL 연산 등이 빈번 해져서 오히려 성능에 부담을 줄 수 있기 때문

서브 타입(물리)로 변환

• 슈퍼/서브 타입 데이터 처리 시 10만 건인 대리인에 대해서 개별로 처리하는 일이 빈번하다고 가정할 때 이렇게 구성하는 것이 좋음

• 슈퍼 타입으로 변환할 시 10만건을 찾기 위해 1010만건 모두를 다 처리해야 하는 비효율이 생길 수 있기에

개별 타입(물리)로 변환

• 당사자 테이블에는 1010만건, 이해관계인(500만), 대리인(10만), 매수인(500만)

• 당사자, 이해관계인, 대리인, 매수인 각각에 대해 독립적으로 트랜잭션이 발생할 때 이렇게 구성하는 것이 좋음

PK 칼럼 순서와 성능

• 복합PK인 경우 PK 칼럼의 순서에 따라 SQL 문의 성능이 빨라질 수도 있고 느려질 수도 있음
• 예로, PK가 (행정동 코드, 기준년월, 인구구분코드, 연령대구분코드)로 되어 있고 조회 시 조건값이 (기준년월, 인구구분코드, 연령대구분코드)로만 조건값을 주면 PK 컬럼 맨 앞에 존재하는 행정동코드 칼럼에 대해서는 조건값이 들어오지 않기에 테이블 풀스캔을 해야 함
• 복합 PK인 경우 인덱스 구성 칼럼 중 맨 앞에 위치하는 칼럼이 가능한 한 조건절에서 “=” 조건으로 들어와야 함

• PK 순서를 잘못 지정하여 성능이 저하된 경우

현금출금기실적

#거래일자

#사무소코드

#출금기번호

#명세표번호

-건수

-금액

• 복합 PK : (거래일자, 사무소코드, 출금기번호, 명세표번호)

SELECT 건수,금액 FROM 현금출금기 실적
WHERE 거래일자 BETWEEN '20040701' AND '20040702'
	AND 사무소코드 = '00368';

• 위와 같이 SQL이 실행될때 정상적으로 복합 PK를 이용할 수는 있지만, 첫번째 PK에 대해서 = 조건으로 들어오지 않아 인덱스 스캔 효율이 떨어지게 됨
• PK 순서를 (사무소코드, 거래일자, .. )로 변경하게 되면 최적화된 인덱스 스캔이 가능함

외래키(FK) 컬럼에 대한 인덱스 생성의 중요성

• 위와 같은 테이블 구조에서, 필연적으로 학사기준 테이블과 수강신청 테이블을 조인하는 경우가 많음

• 이때 학사기준 테이블에서 특정 행을 읽은 후, 학사기준번호 칼럼으로 수강신청 테이블에서 매칭되는 결과를 조회하는데, 수강신청 테이블에 학사기준번호 컬럼에 대한 인덱스가 없을 경우, 학사기준 테이블의 대상 건수만큼 수강신청 테이블을 풀스캔을 해야 함(성능 저하) ⇒ 학사기준번호 컬럼 인덱스 생성!!