[2] 반정규화된 테이블의 성능저하 사례1
- 용어 정리
조인 : 한 데이터베이스 내의 여러 테이블의 레코드를 조합하여 하나의 열로 표현한 것이다.
PK(Primary Key) : 중복될 수 없는 유일한 단일값(Unique)을 가지며, 값이 비어있지(Not Null) 않아야 한다.
Unique Index : 인덱스가 걸려있는 컬럼에 중복될 수 없는 유일한 값만을 보장하며, 인덱스가 걸려있는 컬럼에 Null 값을 입력하는 것이 가능하다.
테이블 당 PK는 하나이고, UNIQUE INDEX는 여러 개를 생성하는 것이 가능하다.
(PK 컬럼에 인덱스가 생성되어 있지 않은 경우, PK 생성시 자동으로 UNIQUE INDEX가 생성된다.)
[3] 반정규화된 테이블의 성능저하 사례2
- 용어 정리
DISTINCT : 중복된 행을 제거하고 한번만 표시하는 키워드
인라인뷰 : FROM 절에서 안에서 사용되는 서브쿼리를 의미한다.
서브쿼리란? SELECT 문 안에서 다시 SELECT 문이 기술된 형태의 쿼리이다.
뷰란? 저장장치에 내에 물리적으로 존재하지는 않지만, 사용자에게 있는 것처럼 보여지는 가상의 테이블이다.
[4] 반정규화된 테이블의 성능저하 사례3
- 한 테이블에 인덱스가 많아지면 조회 성능은 향상되지만, 데이터 입력/수정/삭제 의 성능은 저하된다.
- 용어 정리
1차 정규화 : 같은 성격과 내용의 속성이 중복될 때, 중복 값을 제거하고 새로운 테이블(PK) 추가와 기존 테이블과의 1:M 관계 형성을 한다.
2차 정규화 : 기본 키가 2개 이상의 속성일 때, 부분 함수 종속성 제거를 실행한다. (PK가 2개 이상일 때, 종속되는 관계가 있다면 분리한다.)
부분 함수 종속성이란? 기본 키 전체가 아니라 일부에 대해 종속되는 것
3차 정규화 : 기본 키(PK)에 의존하지 않고 일반 속성에 의존하는 컬럼이 있다면 이를 제거한다.
[5] 반정규화된 테이블의 성능저하 사례4
- 용어 정리
트랜잭션 : DB의 데이터 상태를 변화시키는 명령문(여러 쿼리)을 모아 놓은 논리적인 작업 단위이다.
[6] 함수적 종속성(Functional Dependency)에 근거한 정규화 수행 필요
함수의 종속성 : 데이터들이 어떤 기준 값(결정자, Determinant)에 의해 종속되는 현상을 말한다.
결정자(Determinant) <-> 종속자(Dependent)
ex. 이름, 출생지, 호주라는 속성은 주민등록번호 속성에 종속된다.
주민등록번호 -> (이름, 출생지, 호주)
주민등록번호가 이름, 출생지, 호주를 함수적으로 결정한다.
- 데이터의 구조를 함수적 종속관계에 의해 정규화 사상에 맞게 분리해야 한다.
제3절 반정규화(De-Normalization)와 성능
[1] 반정규화를 통한 성능향상 전략
1. 반정규화(= 역정규화)의 정의
정규화된 엔터티, 속성 등에 대해 시스템의 성능 향상과 개발, 운영의 단순화를 위해 중복, 통합 등을 수행한다. (정규화와 반대로 동작한다.)
비정규화와는 구분이 된다. 정규화를 아예 수행하지 않은 모델을 지칭할 때는 비정규화라고 한다.
- 용어 정리
데이터 무결성 : DB에 저장된 데이터들이 정확성과 일관성을 유지해야 한다.
- 정규화만 수행하면 엔터티의 개수가 많아지면서 조인의 횟수가 증가하여 조회에 대한 처리 성능이 떨어진다. 이때 부분적으로 반정규화를 수행한다면 조회에 대한 처리 성능을 향상시킬 수 있다.
- 반정규화를 기술적으로 수행하지 않는 경우
성능이 저하된 데이터베이스가 생성될 수 있다.
구축단계나 시험단계에서 반정규화를 적용할 때 수정에 따른 노력비용이 많이 들게 된다.
2. 반정규화의 적용방법
무분별하게 반정규화를 적용할 경우, 데이터 무결성을 해치는 결정적인 역할을 하게 된다.
반정규화를 적용할 때는 데이터 무결성이 충분히 유지될 수 있도록 프로세스 처리에 있어서 안정성이 먼저 확인되어야 한다.
- 반정규화 절차
1) 반정규화 대상조사 : 데이터가 대량이고 성능이 저하될 것으로 예상될 때 실행한다.
▷ 범위 처리 빈도수 조사 : 자주 사용되는 테이블에 접근(Access)하는 프로세스의 수가 많고 항상 일정한 범위만 조회하는 경우
▷ 대량의 범위 처리 조사 : 테이블에 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우
▷ 통계성 프로세스 조사 : 통계성 프로세스에 의해 통계 정보를 필요로 하는 경우
▷ 테이블 조인 개수 : 테이블에 지나치게 많은 조인(JOIN)이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우
2) 다른 방법유도 검토 : 데이터 무결성을 깨트릴 위험을 방지하기 위해 반정규화를 결정하기 이전에 실행한다.
▷ 뷰(VIEW) 생성 : 지나치게 많은 조인(JOIN)이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우, 뷰는 조회의 성능 향상과는 관계가 없음
▷ 클러스터링 적용 : 대량의 데이터 처리에 의해 성능이 저하되는 경우,
대량의 데이터를 특정 클러스터링 팩터에 의해 저장방식을 다르게 하는 방법, 입력/수정/삭제 성능이 많이 감소
▷ 인덱스의 조정 : 부분 처리에 의해 성능이 저하되는 경우
▷ 파티셔닝 기법 적용 : 특정 기준에 의해 물리적인 저장 공간이 구분되고 트랜잭션이 들어올 때 일정한 기준에 의해 들어올 경우,
대량의 데이터를 직접 통합/분리하지 않고 기본 키(혹은 파티셔닝 키)에 따라 부분적인 테이블로 저장하여 분리하는 방법
▷ 응용애플리케이션 이용 :로직을 구사하는 방식을 변경하는 방법
ex1. 응용 메모리 영역에 데이터를 처리하기 위한 값을 기억
ex2. 중간 클래스 영역에 데이터를 기억하여 공유
3) 반정규화 적용
▷ 테이블의 반정규화
▷ 속성의 반정규화
▷ 관계의 반정규화
- 테이블, 속성, 관계에 대해 중복을 수행하는 것 뿐만 아니라, 추가/분할/제거 또한 반정규화에 포함된다.
[2] 반정규화의 기법
1. 테이블 반정규화
|
기법분류
|
기법
|
내용
|
|
테이블 병합
|
1:1 관계 테이블 병합
|
1:1 관계를 통합하여 성능 향상
|
|
1:M 관계 테이블 병합
|
1:M 관계를 통합하여 성능 향상
|
|
|
슈퍼/서브 타입 테이블 병합
|
슈퍼/서브 관계를 통합하여 성능 향상
|
|
|
테이블 분할
|
수직 분할
|
칼럼 단위의 테이블에서 디스크 I/O를 분산처리 하기 위해 테이블을 1:1 관계로 분리하여 성능 향상
(트랜잭션 처리 유형 파악이 선행되어야 한다.) |
|
수평 분할
|
로우 단위로 집중 발생되는 트랜잭션을 분석하여 디스크 I/O 및 데이터 접근의 효율성을 높여,
성능 향상을 위해 테이블을 로우 단위로 분리 (관계가 없다.) |
|
|
테이블 추가
|
중복 테이블 추가
|
업무나 서버가 다른 경우 동일한 테이블 구조를 중복하여 원격 조인을 제거하여 성능 향상
|
|
통계 테이블 추가
|
SUM, AVG 등을 미리 수행하여 계산해 둠으로써 조회 시 성능 향상
|
|
|
이력 테이블 추가
|
이력 테이블 중에서 마스터 테이블에 존재하는 레코드를 중복하여 이력 테이블에 존재하는 방법
|
|
|
부분 테이블 추가
|
하나의 테이블에 자주 이용하는 집중화된 칼럼들이 있을 때 디스크 I/O를 줄이기 위해 해당 컬럼을 모아놓은 별도의 테이블을 생성하는 방법
|
2. 칼럼 반정규화
|
기법 분류
|
내용
|
|
|
중복 칼럼 추가
|
조인에 의해 처리할 때 성능 저하를 예방하기 위해 즉, 조인을 감소시키기 위해 중복된 칼럼을 위치시킴
|
|
|
파생 칼럼 추가
|
트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능 저하를 예방하기 위해 미리 값을 계산하여 칼럼에 보관함
|
|
|
이력 테이블 칼럼 추가
|
대량의 이력 데이터를 처리할 때 불특정 날 조회나 최근 값을 조회할 때 나타날 수 있는 성능 저하를 예방하기 위해 이력 테이블에 기능성 칼럼(최근 값 여부, 시작과 종료일자 등)을 추가함
|
|
|
PK에 의한 칼럼 추가
|
복합 의미를 갖는 PK 를 단일 속성으로 구성했을 경우 발생됨. 단일 PK 안에서 특정 값을 별도로 조회하는 경우 성능 저하가 발생될 수 있음. 이때 이미 PK 안에 데이터가 존재하지만, 성능 향상을 위해 일반 속성으로 포함하는 방법
|
|
|
응용시스템 오작동을
위한 칼럼 추가 |
업무적으로 의미는 없지만 사용자가 데이터 처리를 하다가 잘못 처리하여 원래 값으로 복구하기를 원하는 경우 이전 데이터를 임시적으로 중복하여 보관하는 방법. 칼럼으로 이것을 보관하는 방법은 오작동 처리를 위한 임시적인 기법이지만 이것을 이력 데이터 모델로 풀어내면 정상적인 데이터 모델의 기법이 될 수 있음
|
|
3. 관계 반정규화
|
기법 분류
|
내용
|
|
|
중복 관계 추가
|
데이터를 처리하기 위한 여러 경로를 거쳐 조인이 가능하지만 이 때 발생할 수 있는 성능 저하를 예방하기 위해 추가적인 관계를 맺는 방법이 관계의 반정규화임
|
|
테이블, 칼럼의 반정규화는 데이터 무결성에 영향을 미치지만, 관계의 반정규화는 데이터 무결성을 깨뜨리지 않고 데이터 처리 성능을 향상시킨다.
[3] 정규화가 잘 정의된 데이터 모델에서 성능이 저하될 수 있는 경우
정규화 된 모델이 적절하게 반정규화 되지 않으면 SQL 구문이 복잡하게 되어버린다.
[4] 정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우
- 용어 정리
기본 키(PRIMARY KEY)
외래 키(FOREIGN KEY)(= 참조 키)
- 외래 키 사용과 분산 데이터 베이스 환경 등의 이유로 조인의 횟수가 증가해서 조회 성능이 저하된다.
- 반정규화를 적용할 때 꼭 기억해야 할 부분
데이터 입력/수정/삭제 성능은 감소하게 된다.
데이터 무결성 유지에 주의해야 한다.
제4절 대량 데이터에 따른 성능
[1] 대량 데이터발생에 따른 테이블 분할 개요
* 데이터가 하나의 테이블에 집중되었을 때 트랜잭션이 분산 처리 될 수 있도록 테이블 단위에서 분할의 방법을 적용해야 한다.
(대량의 데이터가 존재하여 테이블에 많은 트랜잭션이 발생해서 성능이 저하될 때, 테이블 구조에 수평/수직 분할 설계를 통해 성능 저하를 예방)
- 대량의 데이터로 인해 인덱스를 생성할 때 인덱스의 크기가 커지고, 조회의 성능에도 영향을 미치게 된다. 조회에 미치는 영향은 비교적 적지만, 트랜잭션의 경우 인덱스 특성상 작업이 증가하여 입력/수정/삭제의 성능에 많은 영향을 미치게 된다.
- 용어 정리
로우 체이닝(Row Chaining) : 로우 길이가 너무 길어서 데이터 블록 하나에 데이터가 저장되지 않고 블록 두 개 이상에 하나의 로우(데이터)가 저장되는 형태이다.
로우 마이그레이션(Row Migration) : 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록의 빈 공간을 찾아 저장하는 형태이다.
로우 체이닝과 로우 마이그레이션이 발생하여 데이터를 저장하는데 많은 블록을 사용하게 되면 불필요한 I/O이 증가하여 성능이 저하된다.
[TISTORY] 듀의 자료저장소 (출처 링크 댓글)
[데이터 전문가 포럼] SQL 개발자 스터디 교재(2020.08.22.)
#SQLD