[SQLp]과목 1 - 데이터 모델링의 이해

 

SQLp - 국가공인 SQL 전문가

INDEX

• 과목 1 - 데이터 모델링의 이해

  • 데이터 모델링의 이해

  • 데이터 모델과 성능

  ​

• 과목 2 - SQL 기본 및 활용

  • SQL 기본

  • SQL 활용

  • SQL 최적화 기본 원리

  ​

• 과목 3 - SQL 고급 활용 튜닝

  • 아키텍처 기반 튜닝 원리

  • Lock 과 트랜잭션 동시성 제어

  • 옵티마이저 원리

  • 인덱스와 조인

  • 고급 SQL 튜닝

---

 

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

 

1 . 데이터 모델링의 이해

• 데이터 모델의 이해

  1. 모델링의 이해

  • 모델링의 정의

    복잡한 현실 세계를 일정한 표기법에 의해 표현하는 일 (추상화, 단순화, 명확화)

  • 모델링의 특징

    추상화 : 현실세계를 일정한 형식 (표기법) 에 맞추어 표현

    단순화 : 복잡한 현실세계를 규약이나 표기법, 언어로 표현하여 쉽게 이해할 수 있도록 표현

    명확화 : 대상의 애매모호함을 제거하여 정확하게 현상을 기술
    

 

 

2. 데이터 모델의 이해

• 데이터 모델링의 정의

  • 정보 시스템을 구축하기 위한 데이터 관점의 업무 분석 기법
  • 현실 세계의 데이터에 대해 약속된 표기법에 의해 표현하는 과정
  • 데이터베이스를 구축하기 위한 분석, 설계의 과정

  정리 : 데이터를 시스템 구축 방법론을 통해 분석, 설계 하여 정보 시스템을 구축하는 과정

• 데이터 모델의 기능

  • 시스템 가시화
  • 시스템 구조, 행위 명세화
  • 시스템 구축을 위한 구조화 된 틀 제공
  • 문서화
  • 다양한 관점에서 접근 가능 ( 각 각의 영역에 대한 상황의 취사선택 후 확인 가능 )
  • 구체화된 표현 방법 제공

 

3. 데이터 모델링의 중요성 및 유의점

• 파급효과 ( Leverage )

  • 데이터 구조 혹은 모델의 변경은 영향 분석 후에 해당 분야의 변경이 일어난다. 즉 잘못된 모델링은 프로젝트의 구축에서 위험을 초래한다.
    

• 복잡한 요구 사항의 간결한 표현

  • 건물의 설계 도면과 유사하여 여러 사람이 볼 수 있도록 간결하고 정확하게 전달, 설계하여야 한다. (이를 위한 도구이기도 하다. )

• 데이터 품질

  • 데이터 구조 및 설계가 잘못되면 비즈니스의 손해와 직결될 수 있다.

• 데이터 모델링 유의점

  • 중복, 비유연성, 비일관성

4. 데이터 모델링의 3단계 진행

   중요 : 개념적 데이터 모델링, 논리적 데이터 모델링, 물리적 데이터 모델링

 
x
- 개념적 데이터 모델
  + 데이터 요구사항 탐색 및 분석 (요구사항 발견 지원 / 현 시스템의 개발 필요 부분을 이해 가능)
​
  + 핵심 엔터티, 관계 발견
  
  + Entity-Relation 다이어그램 작성
​
- 논리적 데이터 모델
  + 비즈니스 정보의 논리적 구조와 규칙을 명확하게 표현
​
  + 데이터 모델링 ( 시스템 설계의 전 과정을 지원 - *과정의 도구* )
  
  + 정규화  : 일관성을 확보하고 중복을 제거하여 신뢰성 있는 데이터 구조 설계 가능
​
  + 이력 관리에 대한 전략 정의, 상세화 등 
​
- 물리적 데이터 모델
  + 어떻게 하드웨어에 표현될 것인가 (저장 구조, 장치, 접근 방법 등)
    데이터의 물리적 저장 방법에 대한 정의 : 물리적 스키마
​
  + 핵심 엔터티, 관계 발견
  
  + Entity-Relation 다이어그램 작성

 

5. 프로젝트 생명주기에서 데이터 모델링

   • 프로젝트 모델링 (설계 방법)에 따라 다르게 나타난다.

6. 데이터 모델링에서 데이터독립성의 이해

• 필요성

  • 개별 형식이 가지는 고유의 기능을 극대화
  • 유지비용 , 데이터 복잡도, 중복된 데이터 줄임 / 낮춤
  • 요구사항에 따른 화면과 DB 간의 상호간 독립성 유지

데이터 종속성 : 사용자의 접근 방식에 따라 데이터 구성 방식에 영향을 줌.

• 데이터베이스 3단계 구조

  • 외부단계 : 사용자 에 따라 데이터 유형, 관점, 방법의 영향을 받은 스키마 구조들로 구성 - 여러개의 사용자 관점으로 구성
  • 개념단계 : 데이터 유형의 공통적인 사항을 처리 (통합된 뷰)
  • 내부단계 : 물리적으로 저장된 방법에 대한 스키마 구조

• 두 영역의 데이터 독립성

  데이터베이스 3단계 구조 각각의 독립성을 지정하는 용어

  • 논리적 독립성 : 개념 스키마의 변경이 외부 스키마에는 영향을 주지 않음
  • 물리적 독립성 : 내부 스키마의 변경이 외부, 개념 스키마에 영향을 주지 않음

• 사상 (Mapping) : 상호 연관성

  • 외부적 / 개념적 사상 ( 논리적 사상 )
  • 개념적 / 내부적 사상 ( 물리적 사상 )

7. 데이터 모델링의 중요한 세가지 개념

• 엔터티
• 속성
• 관계

8. 데이터 모델의 표기법인 ERD 의 이해

• ERD 작업 순서

  1. 엔터티 작성
  2. 엔터티 배치
  3. 관계 설정
  4. 관계명 기술
  5. 관계의 참여도 기술
  6. 관계의 필수 여부 기술

 

• 엔터티 (Entity)

1. 엔터티의 특징

   • 해당 업무에서 필요하고 관리하고자 하는 정보여야 함

   • 식별자에 의한 식별

   • 영속적으로 존재하는 인스턴스의 집합

   • 업무 프로세스에 의한 이용

     • 업무 분석의 적확성과 관련이 있다. ( 프로세스 모델링, 상관 모델링 과정에서 문제점 도출 )

   • 속성이 있어야 함

     • 관계엔터티는 주 식별자 속성만 갖고 있어도 엔터티로 인정

   • 다른 엔터티와의 관계가 있어야 함

     • 관계를 생략하여 표현하는 경우는 예외

       통계성 엔터티 도출, 코드성 엔터티 도출, 시스템 처리시 내부 필요에 의한 엔터티 도출

2. 엔터티의 분류

실체유형에 따른 분류, 발생 시점에 의한 분류가 가능하다.

• 유무형에 따른 분류

  • 유형엔터티 : 물리적인 형태가 있고 안정적, 지속적 활용
  • 개념엔터티 : 물리적 형태는 없고 개념적 정보로 구분
  • 사건엔터티 : 업무를 수행함에 따라 발생

• 발생 시점에 따른 분류

  • 기본엔터티 : 독립적으로 생성, 부모의 역할, 관계에 의해 생성되지 않음, 고유의 주 식별자를 갖는다.
  • 중심엔터티 : 기본엔터티로부터 발생, 업무의 중심적인 역할 수행, 관계를 통해 행위엔터티 생성
  • 행위엔터티 : 두 개 이상의 부모엔터티로부터 발생, 내용이 자주 바뀜

• 속성 (Attribute)

1. 속성의 특징

   • 정규화 이론에 근간하여 정해진 주식별자에 함수적 종속성을 가져야 한다.
   • 하나의 속성에는 한 개의 값만을 갖는다.
     

2. 속성의 분류

• 특성에 따른 분류

  • 기본속성 : 업무로 부터 추출한 모든 속성 (다른 속성의 영향을 받은 속성을 제외)
  • 설계속성 : 데이터 모델링을 위해 혹은 업무를 규칙화하기 위해 속성을 새로 만들거나 변형한 속성
  • 파생속성 : 다른 속성에 영향을 받아 발생하는 속성 (ex. 계산된 값)

• 엔터티 구성방식에 따른 분류

  • PK 속성 : 엔터티를 식별할 수 있는 속성
  • FK 속성 : 엔터티와의 관계에서 포함된 속성
  • 일반속성 : 엔터티에 포함되어 있고 PK,FK 에 포함되지 않은 속성

• 세부의미로 나눌수 있는지에 따른 분류

  • 복합속성 , 단순속성

  도메인 : 속성이 가질 수 있는 값의 범위

 

• 관계 (Relationship)

1. 관계

   • 관계의 패어링

     • 엔터티안에 인스턴스가 개별적으로 관계를 가지는 것

     관계 : 관계 패어링의 집합

   • 관계의 분류
     

     • 존재에 의한 관계, 행위에 의한 관계

• 관계의 표기법

  • 관계명, 관계차수( Cardinality / Degree - 1:1, 1:m, m:n ), 관계선택사양( Optionality - 필수관계, 선택관계 )

• 식별자

1. 식별자

   • 식별자의 개념

     • 엔터티를 구성하고 있는 속성 중에서 엔터티를 대표할 수 있는 속성

     식별자는 논리 데이터 모델링에서 사용되고 키는 물리 데이터 모델링에서 사용되므로 이 둘은 다르다.

   • 식별자의 특징

     • 주식별자 : 유일성, 최소성, 불변성, 존재성
     • 대체식별자 : 주식별자와 일치
     • 외부식별자 : 참조무결설 제약조건 에 따른 특징

   • 분류

     • 대표성 여부 : 주식별자 (타 엔터티와 참조관계 연결 가능) / 보조식별자
     • 스스로 생성 여부 : 내부식별자 , 외부식별자
     • 속성의 수 : 단일식별자, 복합식별자
     • 대체여부 : 본질식별자, 인조식별자

   • 식별자관계와 비식별자관계

     • 식별자 관계 : 외부식별자(부모의 주식별자)가 주식별자(자식) 일 때 -> 반드시 부모 엔터티가 생성되어야 자신의 엔터티가 생성될 때
     • 비식별자 관계 : 외부식별자가 자식의 일반적인 속성일 때

     식별자관계로 이루어진 구조는 주식별자 속성이 증가할 수 밖에 없어 개발 복잡성과 오류 가능성을 유발할 수 있다.

     비식별자관계로 이루어진 구조는 상위 / 부모 엔터티까지 조회해야 하는 경우가 발생할 수 있다. 이는 불필요한 조인을 유발하여 성능 저하의 원인이 된다.

• 정리

*다음 내용은 차후 업데이트 예정입니다. * ​

---

 

2 . 데이터 모델과 성능

• 성능 데이터 모델링의 개요

1. 성능 데이터 모델링

   • 성능 데이터 모델링 정의

     • 데이터베이스 성능향상을 목적으로 성능과 관련된 사랑이 설계단계의 데이터 모델링에 반영될 수 있도록 하는 것

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

     • 정규화를 정확하게 수행
     • 데이터베이스 용량산정 수행
     • 트랜잭션 유형 파악
     • 용량과 트랜잭션 유형에 따른 반정규화 수행
     • 이력모델, PK/FK, 슈퍼타입/서브타입 조정 수랭
     • 성능 관점에서 데이터모델 검증

• 정규화와 성능

  일반적으로 정규화를 할 때 데이터 처리의 성능이 향상되며 조회처리 트랜잭션시 성능저하가 나타날 수 있음

1. 반정규화된 테이블의 성능저하

   • 정규화하여 조인을 하더라도 성능 저하를 예방할 수 있다.

     • PK Unique Index 를 이용

   • 함수적 종속관계가 형성되는 관계

     • 조인 조건을 찾기 위해 인라인뷰를 사용 -> 성능 저하

       정규화를 통해 드라이빙되는 대상 테이블의 데이터가 줄어 조회 처리가 빨라짐 (2차정규화)

   • 동일한 속성 형식을 여러개의 속성으로 나열

     • 계층형 데이터베이스의 잔재일 가능성이 높음

     • 한 테이블에 인덱스가 많아져 조회 성능은 향상되지만 삽입/삭제/수정 기능 저하

       중복속성에 대한 분리를 통해 칼럼단위의 중복을 해소 (1차 정규화)

2. 함수적 종속성에 근거한 정규화

   • 함수적 종속성

     • 정의 : 데이터들(종속자)이 어떤 기준값(결정자)에 의해 종속되는 현상

       주민등록번호 -> ( 이름, 출생지, 주소 )

       결정자 -> (종속자)

• 반정규화와 성능

1. 반정규화

   • 반정규화

     • 정규화된 엔터티, 속성 관계에 대해 성능향상과 운영, 개발의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링 기법이다.
     • 반정규화의 원인 : 디스크 I/O 량이 많음 (조회시) , 경로가 멀어 조인으로 인한 성능저하 , 컬럼을 계산하여 읽을 때의 성능저하
     • 성능 향상을 위한 테이블의 중복, 칼럼의 중복, 관계의 중복을 의도적으로 발생시킴
     • 데이터의 무결성을 보장할 수 있는 방법을 고려한 이후에 시행 해야 함

   • 다른 방법을 통한 성능 저하 해결

     반정규화는 무결성을 해치기 때문에 다음 사항들을 먼저 고려한다.

     • 조인이 많을 경우 : 성능을 고려한 뷰를 통해 해결 / 개발자 역량별 성능저하를 방지 ( 이 때에 뷰는 근본적 성능향상을 수행하지는 않는다. )
     • 대량의 데이터 처리에 의한 성능저하 : 클러스터링 적용 (조회 중심의 테이블에만 적용할 것 ), 인덱스 조정
     • 대량의 데이터는 PK의 성격에 따라 파티셔님 기법을 통해 성능 향상 가능 (물지적 저장 공간의 구분 기준이 특정하고, 트랜잭션의 발생 기준이 있을 때 )
     • 응용 애플리케이션의 로직 변경

2. 반정규화 기법

   • 테이블 반정규화

     • 테이블 병합
     • 테이블 분할
     • 테이블 추가

   • 컬럼 반정규화

     • 중복칼럼 추가
     • 파생칼럼 추가
     • 이력테이블 칼럼 추가
     • PK에 의한 칼럼 추가
     • 응용시스템 오작동을 위한 칼럼 추가

   • 관계 반정규화

     • 중복관계 추가

     테이블과 칼럼의 반정규화는 데이터 무결성에 영향을 미치나 관계 반정규화는 데이터 무결성을 깨트릴 위험 없이 성능향상을 가능하게 한다.

• 대량 데이터에 따른 성능

1. 대량 데이터발생

   • 테이블의 분할 필요

     • 성능 저하의 원인 : 로우체이닝 (로우가 길어 다수의 블록을 차지), 로우마이그레이션 (수정된 데이터를 다른 블록의 빈 공간에 저장할 때)

   • 한 테이블에 많은 수의 칼럼

     • 테이블을 1:1로 분할

   • 대량 데이터 저장 및 처리로 인한 성능저하

     • 파티셔닝 적용 / PK를 통한 테이블 분할
     • RANGE PARTITION , LIST PARTITION , HASH PARTITION

• 데이터베이스 구조와 성능

1. 성능 향상 방법

   • 슈퍼타입/ 서브타입 모델 (공통의 부분을 슈퍼 타입으로 묶기)

     • 트랜잭션의 유형, 데이터 양에 의해 물리적 데이터 모델 결정
     • 개별로 발생 : 개별 테이블로 구성 ( 슈퍼 - 서브 / 1:1 )
     • 슈퍼+서브 동시에 발생 : 각 서브 타입별로 구성 (슈퍼 + 각각의 서브 / Plus)
     • 전체를 하나로 묶어 트랜잭션이 발생 : 1개의 테이블로 통합 (Single)

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

     인덱스 : 접근 경로를 제공하는 오브젝트

     • 트랜잭션의 조회 유형을 고려하여 PK/FK 컬럼 순서 지정 필요

       PK는 unique index를 자동으로 생성하며 FK는 조인 조건절에서 자주 사용되기 때문

     -> INDEX의 특징은 INDEX WHERE 절에서 속성값이 위쪽에, '=', 'BETWEEN'를 통해 표현될 때 성능을 높일 수 있다.

     • 인덱스 순서가 중요한 이유 : 인덱스의 순서에 따라 데이터가 정렬되기 떄문 ( 인덱스1, 인덱스2 .. 이순서에 따라 정렬 )
     • 물리적인 테이블에 FK 제약을 걸었을 때에는 반드시 FK인덱스를 생성해야 한다 ( 조인시에 많이 사용하기 때문 )

 

• 분산 데이터베이스와 성능

1. 분산 데이터베이스의 투명성

   • 분할 투명성

     • 하나의 논리적 관계가 여러 단편으로 분할되어 각 사본이 여러 사이트에 저장

   • 위치 투명성

     • 저장 장소의 명시는 불필요하지만 위치 정보가 SYSTEM CATALOG에 유지되어야 함

   • 지역사상 투명성

     • 지역 DBMS와 물리적 DB 사이의 Mapping 보장

   • 중복 투명성

     • DB 객체가 여러 site에 중복되어 있는지 알 필요가 없는 성질

   • 장애 투명성

     • 구성요소의 장애에 무관하게 Transaction 의 원자성은 유지

   • 병행 투명성

     • 다수 트랜잭션을 통시에 수행할 때 결과의 일관성 유지, Time Stamp, 분산 2단계 locking 을 이용

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

   • 테이블 위치 분산

     • 정보를 이용하는 형태가 각 위치별로 차이가 있을 때
     • 위치별 데이터베이스 문서 필요

   • 테이블 분할 분산

     • 각각의 테이블을 분산하는 방법
     • 수평분할 (로우단위) : 각 node 별로 사용하는 row 가 다를 때 , pk 값의 중복이 일어나지 않아야 함
     • 수직분할 (컬럼단위) : node에 따라 칼럼을 기준으로 분할 / 각 node 별 동일한 pk 구조와 값을 갖는다.

   • 테이블 복제 분산

     • 동일한 테이블을 다른 지역이나 서버에서 동시에 관리
     • 부분복제 : 본사의 데이터는 지사데이터의 합
     • 광역복제 : 통합된 테이블을 한군데에서 갖고 있으면서도 각 지사에서도 동일한 데이터를 모두 갖는다.

   • 테이블 요약 분산

     • 분석요약 : 지사별 동일한 요약정보를 본사에 통합 훙 다시 전체의 요약정보를 산출 , 통계데이터를 산출할 경우 통합 통계 데이터 제공에 용이
     • 통합요약 : 각 지사별로 존재하는 다른 요약정보를 본사에 통합하여 다시 전체에 대해서 요약정보를 산출

• 정리