해당 포스트는
SQLD를 학습하며 정리한 내용에 대한 포스트입니다.
이론을 학습한 내용을 정리하고 퀴즈, 기출문제 등을 기록하려고 합니다.
🌈 반정규화 (De-Normalization)
데이터베이스의 성능 향상을 위하여, 데이터 중복을 허용하거나 그룹핑하여 JOIN을 줄이는 데이터베이스 성능 향상 방법
- 정규화를 했을 때 조회 성능이 심각하게 저하되는 경우 반정규화를 고려해 볼 수 있다.
반정규화는 조회(SELECT)속도는 향상시키지만, 데이터 모델의 유연성은 저하되고 정합성(데이터의 정확성과 일관성을 유지하고 보장) 이슈가 발생할 수 있다.
반정규화를 하면 데이터 무결성이 저해된다고 볼 수 있다.
주의해야할 점
조회 성능을 향상될 수 있으나, 입력, 수정, 삭제 성능은 저하될 수 있으며 데이터 정합성 이슈가 발생할 수 있다는 점이다.
반정규화는 정규화가 수행되고 난 뒤,
반드시 해야하는 것은 아니며수행속도가 느려지는 등문제가 발생할 시에 고려해볼 사항이다.
- 반정규화의 과정은 정규화가 끝난 후 거치게 되며 정규화와 마찬가지로 일정한 룰이 존재한다.
반정규화
정규화 된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 개발(Development)과 운영(Maintenance)의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링의 기법을 의미한다.
반정규화는 데이터를 중복하여 성능을 향상시키기 위한 기법이라고 정의할 수 있다.
좀 더 넓은 의미의 반정규화는 성능을 향상시키기 위해 정규화된 데이터 모델에서 중복, 통합, 분리 등을 수행하는 모든 과정을 의미한다.
데이터 무결성이 깨질 수 있는 위험을 무릅쓰고 데이터를 중복하여 반정규화를 적용하는 이유는
- 데이터를 조회할 때 디스크 I/O량이 많아서 성능이 저하
- 경로가 너무 멀어 조인으로 인한 성능저하가 예상
- 컬럼을 계산하여 읽을 때 성능이 저하될 것으로 예상
테이블의 반정규화
| 기법분류 | 반정규화기법 |
|---|---|
| 테이블 병합 | - 1:1 관계 테이블 병합 - 1:M 관계 테이블 병합 - 슈퍼/서브 테이블 병합 |
| 테이블 분할 | - 수직 분할 - 수평 분할 |
| 테이블 추가 | - 중복 테이블 추가 - 통계 테이블 추가 - 이력 테이블 추가 - 부분 테이블 추가 |
컬럼의 반정규화
| 반정규화 기법 |
|---|
| 중복컬럼 추가 |
| 파생컬럼 추가 |
| 이력테이블 컬럼 추가 |
| PK에 의한 컬럼 추가 |
| 응용시스템 오작동을 위한 컬럼 추가 |
💻 반정규화를 수행하는 경우
정규화에 충실하면 종속성, 활용성은 향상되지만
수행 속도가 느려지는 경우다량의 범위를 자주 처리해야 하는 경우특정 범위의 데이터만 자주 처리하는 경우요약/집계 정보가 자주 요구되는 경우
| 단계 | 설명 |
|---|---|
| 대상 조사 및 검토 | - 데이터 처리 범위, 통계성 등을 확인해서 반정규화 대상을 조사한다. |
| 다른 방법 검토 | - 반정규화를 수행하기 전에 다른 방법이 있는지 검토한다. - 예를 들어 클러스터링, 뷰, 인덱스 튜닝, 응용 프로그램, 파티션 등을 검토한다. |
| 반정규화 수행 | 테이블, 속성, 관계 등을 반정규화 한다. |
| 검증 및 모니터링 | 변경된 스키마가 실제로 성능 향상에 도움이 되었는지 확인하고 지속적으로 모니터링한다. |
클러스터링
대량데이터 탐색의 경우 인덱스가 아닌 파티션 및 데이터 클러스터링 등의 다양한 물리 저장 기법을 활용하여 성능 개선을 유도할 수 있다.
다만, 하나의 결과셋을 추출하기 위해 다량의 데이터를 탐색하는 처리가 반복적으로 빈번하게 발생한다면 이때는
반정규화를 고려하는 것이 좋다.
클러스터링 인덱스라는 것은 인덱스 정보를 저장할 때
물리적으로 정렬해서 저장하는 방법이다.조회 시에 인접 블록을 연속적으로 읽기 때문에 성능이 향상된다.
💻 계산된 컬럼 추가
배치 프로그램으로 총판매액, 평균잔고, 계좌평가 등을 미리 계산하고 그 결과를 특정 컬럼에 추가한다.
💻 테이블 반정규화
| 테이블 병합 | - 1:1 관계 테이블 병합 - 1:M 관계 테이블 병합 - 슈퍼 서브 타입 테이블 병합 |
| 테이블 분할 | - 테이블 수직 분할(속성분할) - 테이블 수평 분할(인스턴스 분할, 파티셔닝) |
| 테이블 추가 | - 중복 테이블 추가 - 통계 테이블 추가 - 이력 테이블 추가 - 부분 테이블 추가 |
🔍 테이블 병합
업무 프로세스상 JOIN이 필요한 경우가 많아 테이블을 통합하는 것이 성능 측면에서 유리할 경우 고려한다.
1대1 관계의 테이블을 하나의 테이블로 병합해서 성능을 향상시킨다. 비교적 판단하기 수월하며 부작용도 적다.
1:M 관계 테이블 병합의 경우 1쪽에 해당하는 엔터티의 속성 개수가 많으면 병합했을 경우 중복 데이터가 많아지므로 테이블 병합에 적절하지 못하다.
테이블 병합은 1:1 관계, 1:M 관계 모두 가능
1:M관계의 테이블을 병합하여 성능을 향상시킨다. 하지만 많은 양의 데이터 중복이 발생한다.슈퍼 타입과 서브 타입의 관계가 발생하면 테이블을 통합하여 성능을 향상시킨다.
Super type과 Sub type
공통 속성을 조회하는 빈도수가 개별 속성을 조회하는 빈도수보다 높을 때 공통 속성과 개별 속성을 별도로 관리하는 슈퍼-서브 타입의 설계가 적절하다.
- 고객 엔터티는 개인고객과 법인고객으로 분류된다.
이 때
고객엔터티는슈퍼 타입이고개인고객과법인고객은서브타입이 된다.
즉,
부모와자식간의 관계가 나타난다.슈퍼 타입과 서브 타임의 관계는 배타적 관계와 포괄적 관계가 있는데,
배타적 관계는 고객이 개인고객이거나 법인고객인 경우를 의미한다.
포괄적 관계는 고객이 개인고객일 수도 있고 법인고객일 수도 있는 것이다.
슈퍼 타입 및 서브 타입 변환 방법
| 변환 방법 | 설명 |
|---|---|
| OneToOne Type (1:1 타입) | - 슈퍼타입과 서브타입을 개별 테이블로 도출한다. - 테이블의 수가 많아서 조인이 많이 발생하고 관리가 어렵다. |
| Plus Type (슈퍼 + 서브타입) | - 슈퍼 타입과 서브 타입 테이블로 도출한다. - 조인이 발생하고 관리가 어렵다. |
| Single Type (All In One 타입) | - 슈퍼 타입과 서브 타입을 하나의 테이블로 도출 한다. - 조인 성능이 좋고 관리가 편리하지만, 입출력 성능이 나쁘다. |
🔍 테이블 분할
🪓 테이블 수직 분할
엔터티의 일부 속성을 별도의 엔터티(테이블)로 분할
테이블을 수직 분할하면 한 개의 블록에 더 많은 인스턴스를 저장할 수 있다.
속성의 사용 빈도나 속성값의 NULL 여부를 기준으로 분할 가능
1:1 관계의 테이블이 2개 생긴다.
수직 분할은 컬럼을 분할하여 테이블을 만드는 것이다.
한 테이블에 속성의 개수가 과도하게 많을 경우 조회 성능을 향상시키기 위해 자주 쓰이는 속성과 그렇지 않은 속성을 나누어 수직 분할할 수 있다.
특정 테이블에서 컬럼 일부만 추출하는 경우 DISK I/O를 경감할 수 있는 방법이다.
🪓 테이블 수평 분할
엔터티의 인스턴스를 특정 기준으로 별도의 엔터티로 분할
데이터베이스의 파티셔닝 기법을 주로 이용
테이블 수평 분할을 할 경우 관계 없는 다수의 테이블이 생성된다.
테이블 수평 분할의 기준은 주로 기간이지만 다른 속성이 될 수도 있다.
파티셔닝 기법
데이터베이스에서 파티션을 사용하여 테이블을 분할할 수 있다.
파티션을 사용하면 논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리하여 데이터 액세스 성능도 향상시키고, 데이터 관리방법도 개선할 수 있도록 테이블에 적용하는 기법
Range Partition:
데이터 값의 범위를 기준으로 파티션을 수행한다.List Partition:
특정한 값을 지정하여 파티션을 수행한다.Hash Partition:
해시 함수를 적용하여 파티션을 수행한다.Composite Partition:
범위와 해시를 복합적으로 사용하여 파티션을 수행한다.
2개 이상의 기법을 사용
해시 함수
임의의 길이를 갖는 메세지를 입력 받아 고정된 길이의 해시 값을 출력하는 함수.
암호 알고리즘에는 키가 사용되지만, 해시 함수는 키를 사용하지 않으므로 같은 입력에 대해서 항상 같은 출력이 나오게 된다.
이러한 해시 함수를 사용하는 목적은 메세지의 오류나 변조를 탐지할 수 있는 무결성을 제공하기 위해 사용된다.
파티션 테이블의 장점
데이터 조회 시에 액세스 범위가 줄어들기 때문에 성능이 향상된다.
데이터가 분할되어 있기 때문에 I/O의 성능이 향상된다.
각 파티션을 독립적으로 백업 및 복구가 가능하다.
📌 테이블 추가
중복 테이블 추가
데이터의 중복을 감안하더라도 성능상 반드시 필요하다고 판단되는 경우 별도의 엔터티를 추가한다.
통계 테이블 추가
이력 테이블 추가
부분 테이블 추가
하나의 테이블의 전체 컬럼 중 자주 사용하는 집중화된 컬럼들이 있을 때 디스크 I/O를 줄이기 위해 해당 컬럼들을 별도로 모아 놓은 반정규화 기법
💻 컬럼 반정규화
중복 컬럼 추가
조인감소를 위해 여러 테이블에 동일한 컬럼을 갖도록 함
업무 프로세스상 JOIN이 필요한 경우가 많아 컬럼을 추가하는 것이 성능 측면에서 유리할 경우 고려
파생 컬럼 추가
조회 성능을 우수하게 하기 위해 미리 계산된 컬럼을 갖도록 함
프로세스 수행 시 부하가 염려되는 계산값을 미리 컬럼으로 추가하여 보관하는 방식
상품의 재고나 프로모션 적용 할인가 등이 여기에 해당 할 수 있다.
이력 테이블 컬럼 추가
최신값을 처리하는 이력의 특성을 고려하여 기능성 컬럼을 추가
대량의 이력 테이블을 조회할 때 속도가 느려질 것을 대비하여 조회 기준이 될 것으로 판단되는 컬럼을 미리 추가해 놓는 방식이다.
최신 데이터 여부 등이 해당할 수 있다.
💻 관계 반정규화 (중복관계 추가)
업무 프로세스상 JOIN이 필요한 경우가 많아 중복 관계를 추가하는 것이 성능 측면에서 유리할 경우 고려한다.
여러 경로를 거쳐 조인 할 수 있지만, 성능 저하를 예방하기 위해 추가적인 관계를 맺음
중복관계를 추가해줌으로써 데이터 무결성을 깨뜨리지 않을 수 있다.
🎯 추가 내용
하나의 테이블에 많은 컬럼을 추가하는 방법으로 반정규화를 수행하면 정규화된 모델보다 조회 속도는 항상 빨라진다. 그 이유는 조인이 없어지기 때문이다? ❌
하나의 테이블에 컬럼의 수가 많아지면 조인의 수가 감소하는 것은 당연하다.
하지만, 하나의 테이블의 행의 길이가 길어져서 입출력을 하는데 있어서
Block Size가 커지기 떄문에하나의 행을 조회할 때도 여러 번에 입출력이 발생하여 항상 속도가 빨라지는 것은 아니고 오히려 성능이 떨어진다.
테이블의 반정규화 기법 중 데이터 무결성을 깨뜨릴 위험을 갖지 않고서도 데이터 처리의 성능을 향상시킬 수 있는 기법은?
무결성을 깨뜨지 않기 위해서는 중복성이 없어야 한다.
👉 중복관계추가
반정규화 대상에 대해 다른 방법으로 처리
지나치게 많은 조인(JOIN)이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 뷰(VIEW)를 사용하여 해결할 수도 있다.
대량의 데이터처리나 부분처리에 의해 성능이 저하되는 경우에 클러스터링을 적용하거나 인덱스를 조정함으로써 성능을 향상시킬 수 있다.
대량의 데이터는 Primary Key의 성격에 따라 부분적인 테이블로 분리할 수 있다. 즉 파티셔닝 기법(Partitioning)이 적용되어 성능 저하를 방지할 수 있다.
응용 애플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능을 향상시킬 수 있다.
📚 레퍼런스
정미나. (유튜브 선생님에게 배우는) 유선배 SQL개발자(SQLD) 과외노트 / [정미나 저] (2023). Print.
한국데이터산업진흥원. SQL 자격검정 실전문제 : 국가공인 SQL전문가·국가공인 SQL개발자 / 한국데이터산업진흥원 [편] (2020). Print.