[SQLD] 1과목 2장 데이터 모델과 SQL (구 데이터 모델과 성능 요약 / 2024 개정사항 반영)

※ 이전 글 : 2023.02.24 - [Cerificate/SQLD] - [SQLD] 1과목 1장 데이터 모델링의 이해 요약 (엔터티~식별자 / 2024 개정사항 반영)

 

  1장의 내용을 아직 체화하지 못하였지만 진도가 너무 느려 바로 이어서 쓰기 시작한다. 이번에는 어떤 내용이 나올까...

  2024년에 개정된 내용을 이기적 SQLD 강의를 보고 추가하여 보라색으로 표시하였다. 1과목 2장에서는 성능 부분이 SQLD 레벨에 맞지 않다고 판단하여 빠진 것으로 보인다. 보다 실무에 적합하도록 설계도를 보고 DB를 이해할 수 있는 능력을 묻는 방향으로 개정된 듯하다. 

 

Index

 

과목 1. 데이터 모델링의 이해

2장 데이터 모델과 성능

1절 성능 데이터 모델링의 개요 

1. 성능 데이터 모델링의 정의

• 데이터 모델의 성능 저하 원인
  • 데이터 모델 구조
  • 대용량 데이터
  • 특성을 고려하지 않고 인덱스 생성
• 정의 : 데이터베이스 성능 향상을 목적으로 설계 단계의 데이터 모델링부터 관련 사항 (정규화, 반정규화, 테이블 분할 등)이 데이터 모델링에 반영되도록 하는 것.

2. 성능 데이터 모델링 수행 시점 

• 사전에 할수록 비용이 저렴. 특히 분석/설계 단계에서 데이터 모델에 성능을 고려한 데이터 모델링을 수행할 경우, 성능저하에 따른 재업무 (Rework) 비용을 최소화 가능.

3. 성능 데이터 모델링 고려사항

• ① 데이터 모델링 시, 정확한 정규화 수행. ▷ 정규화된 모델은 데이터를 관심사별로 분산시키는 효과.
• ② 데이터베이스 용량산정 실시. ▷ 특정 엔터티 (테이블)에 데이터가 모이는지 파악.
• ③ 데이터베이스에 발생되는 트랜잭션의 유형 파악. 
• ④ 용량과 트랜잭션의 유형에 따른 반정규화 수행.
• ⑤ 이력모델의 조정, PK/FK조정, 슈퍼타입/서브타입 조정 등 수행.
• ⑥ 성능관점에서의 데이터 모델 검증.

2절 정규화와 성능

1. 정규화를 통한 성능 향상 전략

• 정규화 
  • 데이터에 대한 중복성을 제거하여 데이터가 관심사별로 처리되는 경우가 많아지므로 성능이 향상되는 특징. (Data On Air)
  • 데이터의 중복을 제거하고 데이터가 관심사별로 처리되도록 엔터티를 나누어 성능을 향상시키는 활동 (이기적)
  • 정규화 시, DML (입력/삭제/수정)의 성능은 향상됨. 조회는 저하될 수도 있기에 반정규화를 시도하기도 함.
• 데이터 처리 성능 : 아래 두 성능은 일반적으로 trade-off.
  • 조회
  • 입력/수정/삭제 성능
• 정규화: 데이터를 결정하는 결정자에 의해 함수적 종속을 가지고 있는 일반속성을 의존자로 하여 입력/수정/삭제 이상을 제거하는 것.

2. 함수정 종속성에 기반한 정규화 수행

• 함수적 종속 (함수적 종속성 | Functional Dependency) : 데이터들이 어떤 기준값 (컬럼)에 의해 종속되는 현상 | 기준값 - 결정자, 종속값 - 종속자
• 예) 결정자 (소속ID) - 종속자 (소속부서명)

반응형

3절 반정규화와 성능

1. 반정규화를 통한 성능향상 전략

• 1) 반정규화의 정의
  • 반정규화(=역정규화) : 정규화된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 개발 (Development)과 운영 (Maintenance)의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링의 기법.
  • 협의 : 데이터를 중복하여 성능을 향상시키기 위한 기법.
  • 광의 : 성능을 향상시키기 위해 정규화된 데이터 모델에서 중복, 통합, 분리 등을 수행하는 모든 과정.
  • 반정규화 수행 이유 : 데이터 조회 시, 디스크 I/O량이 많거나 경로가 멀어 조인으로 인한 성능 저하를 막기 위해.
  • 정규화는 입력/수정/삭제 + 조회의 성능을 향상시키는 역할. 그러나 정규화만을 수행하면 엔터티의 갯수가 증가하고 관계가 많아져 여러 개의 조인을 거쳐 데이터를 가져와야 함. ▷  조회에 대한 처리성능이 중요하다고 판단될 때 부분적으로 반정규화를 고려.
• 2) 반정규화 적용 방법
  • 반정규화 대상 조사
    • - 자주 사용되는 테이블에 접근 (Access)하는 프로세스의 수가 많고 항상 일정한 범위만을 조회하는 경우.
    • - 테이블에 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는데 처리범위를 일정하게 줄이지 않으면 성능을 보장할 수 없을 경우.
    • - 통계성 프로세스에 의해 통계 정보를 필요로 할 때 별도의 통계테이블 (반정규화 테이블)을 생성.
    • - 테이블에 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 
  • 다른 방법 검토
    • - 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우. ▷ 뷰(VIEW)를 사용.
    • - 대량의 데이터처리나 부분처리에 의해 성능이 저하되는 경우. ▷ 클러스터링을 적용 (조회 대부분일 때) or 인덱스를 조정.
    • - 대량의 데이터는 Primary Key의 성격에 따라 부분적인 테이블로 분리 가능. ▷ 인위적인 테이블을 통합/분리하지 않고 물리적인 저장기법에 따라 성능을 향상시키는 파티셔닝 기법(Partitioning) 적용.
    • - 응용 애플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능 향상 가능. ▷ 응용 메모리 영역에 데이터를 처리하기 위한 값을 캐쉬한다든지 중간 클래스 영역에 데이터를 캐쉬하여 공유.
  • 반정규화 적용

반정규화 절차 (Data On Air)

 

2. 반정규화 기법

• 1) 테이블 반정규화

테이블 반정규화 (Data On Air)

• 2) 칼럼 반정규화

칼럼 반정규화 (Data On Air)

• 3) 관계 반정규화

관계 반정규화 (Data On Air)

• 테이블과 칼럼의 반정규화 - 데이터 무결성에 영향. ↔ 관계의 반정규화 - 데이터 무결성을 깨뜨릴 위험 없이 데이터처리 성능 향상.

☆정규화 정리☆

정규화 과정 (이기적 SQLD 유튜브 강의 4회)

  쉽게 말하면, 정규화는 엔터티를 쪼개는 것이고 반정규화는 중복을 포함하고자 엔터티를 합치는 것이다. 모두 성능향상을 위해 실시한다. 

• 정규화
  • (1) 정의 
    • 데이터의 일관성, 최소한의 데이터 중복 & 데이터 유연성을 위한 방법이며 데이터를 분해하는 과정.
    • 정규화된 모델은 테이블이 분해됨. ▷ ex) 각각의 테이블을 특정 코드로 조인하여 하나의 합집합으로 만들 수 있음.
    • 불필요한 데이터를 입력하지 않아도 되므로 중복 데이터가 제거됨.
  • (2) 절차
    • 1차 정규화 (제1정규화): 엔터티에 다중속성이 있거나 하나의 속성에 여러 속성이 있으면 원자값으로 나눈다. (속성의 원자성 확보 | 속성의 중복값 제거 | 기본키 설정)
    • 1차 정규화는 도메인 원자성을 확보하는 과정으로 하나의 속성에 하나의 값만 있어야 한다.
    • 비정규형은 데이터 중복 등으로 정규화가 필요한 상태를 뜻함.
    • 2차 정규화 (제2정규화) : 1정규형에서 2정규형으로 엔터티를 바꿔 부분종속을 제거한다. (기본키가 복수인 속성 ▷ 부분 함수 종속성 제거 | 복합 인스턴스의 종속적 중복 제거)
    • 순서 주의: 1정규형에서 3정규형으로 2정규형을 스킵하고 넘어가는 것은 불가능. 
    • 3차 정규화 (제3정규화) : 엔터티에서 일반속성 간 함수종속이 발생하면 나눈다. (이행종속 제거) (기본키 이외의 칼럼 간 종속성 제거 ▷ 이행 함수종속성 제거 | 일반속성 종속성 제거)
    • BCNF (Boyce and Codd Normal Form): '강한 제3정규형'이라고도 함. | 후보키가 기본키를 종속시킬 경우 제거. | 복수의 후보키가 복합 속성이고 중첩되는 경우에만 해당.
    • 제4정규화: 여러 칼럼이 하나의 칼럼을 종속할 때, 분해하여 다중값 종속성 제거 | 다치 종속성 제거
    • 제5정규화: 조인으로 종속성이 발생하면 분해.

• 반정규화 
  • 정의 : 데이터베이스의 성능 향상을 위해 데이터 중복을 허용하고 조인을 줄이는 것. 반정규화는 조회 (Select) 속도를 향상시키나 모델의 유연성은 낮춤.
  • 주의사항 : 데이터를 입력, 수정, 삭제할 때는 성능이 떨어짐 + 데이터의 무결성 유지에 주의.

관계와 조인의 이해

  여기서부터는 2024년 이후 새로 변경된 내용을 추가한다.

• 조인: 식별자를 상속하여 데이터를 결합해 복수의 엔터티에서 필요 데이터를 한 번에 가져오는 것. 
• 계층형 데이터 모델: 계층구조를 지닌 데이터를 저장한 모델로 자기 자신의 엔터티와 관계가 발생하는 경우를 의미함.
• 상호배타적 관계: A집합과 B집합이 C집단으로 묶이나 서로 공통부분이 없는 관계. 예) A중학교와 B중학교 출신의 학생들이 C고등학교라는 카테고리로 묶임.

모델이 표현하는 트랜잭션의 이해

• 트랜잭션: DB의 논리적 연산 단위 혹은 논리적 업무 단위 
• 식별관계와 비식별관계의 구분: 외래키가 식별자로 사용되는가?
• 제시되는 조건에 부합하는 ERD인지, IE와 Barker 표기법의 차이에 주의. (Barker 표기에서의 점선 | #: 식별자 | *: null 불가 속성 | 0: null 가능 속성)

Null 속성의 이해

• Null: 아직 정해지지 않은 값.
  • 아직 특정값이 입력되지 않았음을 나타냄.
  • 테이블은 행 X 열 형태를 유지해야 함.
  • IE 표기법에서는 Null이 있는 속성을 알 수 없음.

본질식별자 vs. 인조식별자

  이전글의 식별자 파트에서 이어진다. 식별자는 대표성 여부, 자생 여부, 단일속성 여부, 대체 여부에 따라 분류되는 것을 확인하였다.

• 본질식별자: 업무 과정에서 생성된 식별자.
• 인조식별자: 원활한 처리를 위해 인위적으로 만든 식별자. 예) 직원 코드, 소속번호, 이력번호 등

---

2023년까지의 SQLD 1과목 2장

  뾰족뾰족 표시로 나뉜 구간은 2024 개정 이전의 범위이다.

4절 대량 데이터에 따른 성능

1. 대량 데이터발생에 따른 테이블 분할 개요

• 데이터가 특정 테이블에 몰리지 않도록 (트랜잭션 분산) 테이블 단위의 분할 시도.

테이블 수평/수직 분할을 통한 성능 향상 (Data On Air)

• 하나의 테이블에 300개 이상의 칼럼을 가지고 있는 경우 ▷ 로우체이닝과 로우마이그레이션이 많아져 성능 저하.
• 로우 체이닝 (Row Chaining) : 로우 길이가 너무 길어 데이터 블록 하나에 데이터가 모두 저장되지 않고 두 개 이상의 블록에 걸쳐 하나의 로우가 저장되어 있는 형태.
• 로우 마이그레이션 (Row Migration) : 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록의 빈 공간을 찾아 저장하는 방식.

2. 대량 데이터 저장 및 처리

• 테이블에 많은 양의 데이터가 집중 ▷ 논리적으로는 하나의 테이블로 보이지만 물리적으로 여러 개의 테이블스페이스에 쪼개어 저장될 수 있는 구조의 파티셔닝을 적용하거나 PK로 테이블을 분할하는 방법을 적용.
  • 1) RANGE PARTITION 
    • 대상 테이블이 날짜 혹은 숫자값으로 나뉘고, 각 영역별로 트랜잭션이 분리 가능한 경우.
    • 데이터보관주기에 따라 테이블에 데이터를 쉽게 지우는 것이 가능.
  • 2) LIST PARTITION
    • 각종 범주, 핵심적인 코드값 등으로 PK가 구성되어 있고 대량의 데이터가 있는 테이블에 적용 가능.
    • 대용량 데이터를 특정값에 따라 분리 저장할 수는 있으나 RANGE PARTITION처럼 데이터 보관주기에 따라 쉽게 삭제하는 기능은 제공되지 않음.
  • 3) HASH PARTITION
    • 지정 HASH 조건에 따라 해쉬 알고리즘이 적용되어 테이블이 분리됨.

3. 테이블에 대한 수평/수직분할 원칙

• 1) 데이터 모델링 완성.
• 2) DB 용량산정.
  • 컬럼이 많으면 1:1 분리, 데이터 용량이 크면 파티셔닝.
• 3) 대량 데이터가 처리되는 테이블의 트랜잭션 처리 패턴 분석.
• 4) 칼럼 단위로 집중화된 처리가 발생하는지, 로우단위로 집중화된 처리가 발생되는지 분석하여 집중화된 단위로 테이블을 분리하는 것을 검토.

5절 데이터베이스 구조와 성능

1. 슈퍼타입/서브타입 모델의 성능고려 방법

• 1) 슈퍼/서브타입 데이터 모델의 개요
  • Extended ER모델이라고도 불림.
  • 데이터의 공통점과 차이점을 고려하여 효과적으로 표현할 수 있기 때문에 자주 쓰임. (공통 부분 - 슈퍼타입 모델링 | 차이점 - 다른 엔터티와 차이가 있는 속성에 대해서는 별도의 서브엔터티로 구분)
  • 논리적인 데이터 모델에서 이용 & 분석단계에서 많이 쓰이는 모델.
• 2) 슈퍼/서브타입 데이터 모델의 변환
  • 변환 오류로 성능이 저하되는 경우 
    • (1) 트랜잭션은 항상 일괄로 처리하는데 테이블은 개별로 유지되어 Union연산에 의해 성능 저하 발생.
    • (2) 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어 불필요하게 많은 양의 데이터가 집약된 경우.
    • (3) 트랜잭션은 항상 슈퍼+서브 타입을 공통으로 처리하는데 개별로 유지되어 있거나 하나의 테이블로 집약된 경우.
  • 데이터량이 소량일 경우 : 성능에 영향을 미치지 않으므로 데이터처리의 유연성을 고려하여 1:1 관계 유지.
  • 용량이 커지면 다음 3가지 방법 고려.
• 3) 슈퍼/서브 타입 데이터 모델의 변환기술
  • (1) 개별로 발생되는 트랜잭션 ☞ 개별 테이블로 구성 (1:1 관계)
  • (2) 슈퍼타입+서브타입에 대해 발생되는 트랜잭션 ☞ 슈퍼타입+서브타입 테이블로 구성 (Plus Type)
  • (3) 전체를 하나로 묶어 트랜잭션이 발생할 때 ☞ 하나의 테이블로 구성 (Single, All in One)
• 4) 슈퍼/서브타입 데이터 모델의 변환타입 비교

슈퍼/서브타입 데이터 모델의 변환타입 비교 (Data On Air)

 

2. 인덱스 특성을 고려한 PK/FK 데이터베이스 성능향상

• 1) PK/FK 칼럼 순서와 성능개요
  • 데이터베이스 테이블에서는 균형 잡힌 트리구조의 B*Tree구조를 보편적으로 사용.
  • 성능 저하 ▷ PK가 여러 개의 속성으로 구성된 복합식별자 일 때 PK순서에 대해 별로 고려하지 않고 데이터 모델링을 한 경우.
  • PK는 해당 해당테이블의 데이터를 접근할 가장 빈번하게 사용되는 유일한 인덱스 (Unique Index)를 모두 자동 생성
  • 여러 개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 보임. (앞쪽에 있는 속성값이 가급적 ‘=’ 아니면 최소한 범위 ‘BETWEEN’, ‘< >’가 들어와야 인덱스를 이용할 수 있음)
• 2) PK칼럼의 순서 미조정에 따른 성능 저하 : 인덱스 특징에 맞게 고려하지 않고 그대로 생성 시, 테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은 인덱스가 생성되므로 인덱스의 범위를 넓게 이용하거나 Full Scan을 유발하여 성능 저하 발생.

3. 물리적인 테이블에 FK제약이 걸려있지 않을 시, 인덱스 미생성으로 성능저하

• 물리적인 테이블에 FK를 사용하지 않아도 데이터 모델 관계에 따라 상속받은 FK속성들은 SQL WHERE 절에서 조인으로 이용되는 경우가 많아 FK 인덱스를 생성해야 성능이 좋은 경우가 많음.
• FK인덱스를 적절하게 설계하여 구축하지 않았을 경우, 개발초기에는 데이터량이 얼마 되지 않아 성능저하가 나타나지 않다가 시스템을 오픈하고 데이터량이 누적될수록 SQL성능이 나빠져 데이터베이스서버에 심각한 장애현상을 초래.

6절 분산 데이터베이스와 성능

1. 분산 데이터베이스의 개요

• 분산 데이터베이스 정의 
  • 분산된 데이터베이스를 하나의 가상 시스템으로 사용할 수 있도록 한 데이터베이스.
  • 논리적으로 동일한 시스템에 속하나 컴퓨터 네트워크를 통해 물리적으로 분산되어 있는 데이터들의 모임. 물리적 Site 분산, 논리적으로 사용자 통합·공유.
  • 데이터베이스를 연결하는 빠른 네트워크 환경을 이용하여 데이터베이스를 여러 지역 여러 노드로 위치시켜 사용성/성능 등을 극대화 시킨 데이터베이스 

2. 분산 데이터베이스의 투명성 (Transparency)

• 1) 분할 투명성 (단편화) : 하나의 논리적 Relation이 여러 단편으로 분할되어 각 단편의 사본이 여러 site에 저장.
• 2) 위치 투명성 : 사용하려는 데이터의 저장 장소 명시 불필요. 위치정보가 System Catalog에 유지되어야 함.
• 3) 지역 사상 투명성 : 지역DBMS와 물리적 DB사이의 Mapping 보장. 각 지역시스템 이름과 무관한 이름 사용 가능.
• 4) 중복 투명성 : DB 객체가 여러 site에 중복 되어 있는지 알 필요가 없는 성질.
• 5) 장애 투명성 : 구성요소 (DBMS, Computer)의 장애에 무관한 Transaction의 원자성 유지.
• 6) 병행 투명성 : 다수 Transaction 동시 수행시 결과의 일관성 유지, Time Stamp, 분산 2단계 Locking을 이용 구현.

3. 분산 데이터베이스의 적용 방법 및 장단점

• 1) 분산 데이터베이스 적용방법 : 업무의 흐름에 따라 업무구성별 아키텍처 특징에 기반하여 데이터베이스를 구성하는 것.
• 2) 분산 데이터베이스 장단점

분산 데이터베이스 장단점 (Data On Air)

4. 데이터베이스 분산구성의 가치

• 가장 핵심적인 가치는 바로 통합된 데이터베이스에서 제공할 수 없는 빠른 성능을 제공한다는 점.

5. 분산 데이터베이스의 적용 기법

• 1) 테이블 위치 분산 : 설계된 테이블의 위치를 각각 다르게 하는 것. 도식화된 위치별 데이터베이스 문서가 요구됨. 

테이블 위치 분산 (Data On Air)

수평분할 (Data On Air)

• 2) 테이블 분할(Fragmentation) 분산 : 위치만 다른 곳에 두는 것이 아니라 각각의 테이블을 쪼개어 분산.
  • 수평분할 (Horizontal Fragmentation) : 노드 (Node)에 따라 테이블을 특정 칼럼의 값을 기준으로 로우를 분리. ex) 지사별 구분.
  • 수직분할 (Vertical Fragmentation) : 노드 (Node)에 따라 테이블 칼럼을 기준으로 칼럼을 분리. ex) 지사, 본사 구분.

수직분할 (Data On Air)

• 3) 테이블 복제 (Replication) 분산 : 동일한 테이블을 다른 지역이나 서버에서 동시에 생성하여 관리하는 유형
  • 부분복제 (Segment Replication) : 통합된 테이블을 한 군데 (본사)에 가지고 있으면서 각 지사별로는 지사에 해당된 로우(Row)를 가지고 있는 형태 ▷여러 테이블에 조인(JOIN)이 발생하지 않는 빠른 작업 수행이 가능.
  • 광역복제 (Broadcast Replication) : 통합된 테이블을 한군데(본사)에 가지고 있으면서 각 지사에도 본사와 동일한 데이터를 모두 가지고 있는 형태.
• 4) 테이블 요약 (Summarization) 분산 : 지역 간 or 서버 간에 데이터가 비슷하지만 서로 다른 유형으로 존재하는 경우.
  • 분석요약 (Rollup Replication) : 각 지사별로 존재하는 동일 정보를 본사에서 통합하여 다시 전체에 대해서 요약정보를 산출하는 분산방법. ex) 동일한 취급 제품. ▷본사에서는 모든 지사 판매 실적을  요약. 
  • 통합요약 (Consolidation Replication) : 각 지사별로 존재하는 다른 내용의 정보를 본사에 통합하여 다시 전체에 대해서 요약정보를 산출하는 분산방법.
  • 분석요약은 지사에 있는 데이터를 이용하여 본사에서 통합하여 요약 데이터를 산정 ↔ 통합요약에서는 지사에서 요약한 정보를 본사에서 취합하여 각 지사별로 데이터를 비교하기 위해 이용.

6. 분산 데이터베이스 적용

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

---

참고

• Data On Air
• 소품집 블로그 (링크) - 정규화, 반정규화

 

SQL 자격검정 실전문제 오답

• 함수 종속성 규칙을 보고 정규화 구분.
• 칼럼에 반복적 형태의 속성값이 있다면 원자성을 위배하므로 1차 정규화 대상
• PK에 대해 반복되는 그룹이 없으므로 1차 정규형임 + 부분 함수종속의 규칙이 있기에 2차 정규형 X
• 특정 테이블에 사용 빈도가 높은 칼럼이 집중 ☞ 해당 칼럼을 별도로 모은 부분 테이블 추가!
• FK 속성 추가는 반정규화 기법 X
• 로우체이닝 발생 ☞ 트랜잭션이 접근하는 칼럼을 분석해 1:1로 테이블 분리.
• 트랜잭션 : 항상 전체를 통합해 분석 처리. | 슈퍼-서브 타입이 통합된 경우, 하나의 테이블만 읽으면 되므로 성능 향상.
• 인덱스 : 상수값으로 EQUAL 조건으로 조회되는 칼럼이 가장 앞으로!
• Global Single Instance (GSI) : 통합된 한 개의 인스턴스. 통합 DB구조를 뜻함 ↔ 분산 DB

---

• 23.02.24 이론 작성 시작.
• 23.02.25 이론 작성 완료.
• 23.03.06 오타 수정 및 오답노트 작성.
• 24.02.27 이기적 강의영상 내용 추가