정보처리기사 실기 - 2. 데이터 입출력 구현(2) /인덱스/반정규화

2) 물리 데이터 저장소 설계 절차

물리 데이터 저장소 설계 절차 4단계

•물리 스키마 설계

- ERD 변환 (논리 -> 물리)

엔티티	테이블
속성	컬럼
주 식별자	기본키
외래 식별자	외래키
관계	관계
관계의 카디널리티	관계의 카디널리티
관계의 참여도	관계의 참여도

 

- 엔티티 -> 테이블 변환

 -> 일반화 관계는 3가지 타입으로 변환

 

  -> 슈퍼 타입 기준 변환 : 엔티티의 서브 엔티티를 슈퍼 엔티티에 통합하여 하나의 테이블로 변환

   -> 장점 : 데이터 액세스 용이, 수행속도 증가, 조인 감소, 복잡한 처리를 하나의 SQL로 통합하기 용이함

   -> 단점 : 컬럼 및 블록 수 증가, 인덱스 크기 증가, 서브 타입 구분이 필요한 경우 발생

 

  -> 서브 타입 기준 변환 : 슈퍼타입을 서브타입에 복제하여 하나의 테이블로 변환

   -> 장점 : 단위 테이블 크기 감소

   -> 단점 : 유니온 발생 가능, 복잡한 처리의 SQL 통합이 어려움

 

  -> 개별 타입 기준 변환 : 슈퍼 타입과 서브 타입들 각각 테이블로 변환

   -> 장점 : 서브 타입처리가 독립적으로 발생할 경우 유리, 트랜잭션이 주로 슈퍼타입에서 발생할 경우 사용

   -> 단점 : 모든 테이블을 조회해야 하는 경우 SQL 복잡, 테이블 CRUD가 빈번

 

- 관계 변환

•  1:M 관계는 Primary Key : Foreign Key 관계로 변환
• N:M 관계는 중간에 테이블을 하나 더 만들어서 N:1, 1:M으로 변환

 

- 인덱스 설계

 

 - 설계순서 : 인덱스 대상 선정 -> 인덱스 최적화 -> 인덱스 정의서 작성

* 인덱스 : 검색 연산의 최적화를 위해 데이터베이스 내의 각 ROW들에 대한 정보를 구성하는 데이터 구조

 

 - 인덱스 구조 설계 

  구성하는 구조나 특성을 기반으로 트리 기반, 해시 기반, 비트맵, 함수 기반, 조인, 도메인 등의 인덱스로 분류

 

 - 트리 기반 인덱스 : 일반적으로 트리 구조 기반의 B+트리 인덱스 활용

 

  - 인덱스 적용 대상 테이블 선정 

  -> Multiblock Read 수가 16일 때 테이블 크기가 16블록 이상일 경우 선정

 

* Single Block I/O : 한번의 I/O call에 하나의 테이터 블록만 읽어 메모리에 적재하는 방법

* Multi Block I/O : call이 필요한 시점에 인접한 블록들을 같이 읽어 메모리에 적재하는 방법

 

 - 인덱스 적용 대상 컬럼 선정

  -> 분포도가 10~15%인 컬럼

   -> 분포도 = 데이터별 평균 ROW수/테이블 총 ROW수 * 100

  -> 자주 사용하는 컬럼

 

 - 결합 인덱스 : 여러 개의 인덱스가 하나의 인덱스를 구성

  -> 컬럼의 순서가 중요

  -> 분포도가 좋은 컬림이 앞으로 나오도록

  -> 정렬이 자주 발생하는 컬럼이 앞으로 나오도록 설계

 

- 뷰 설계 원칙

• 테이블 구조의 단순화
• 다양한 관점
• 데이터의 보안 유지
• 논리적인 데이터의 독립성 유지

 

- 클러스터 설계

 지정된 컬럼 값의 순서대로 데이터행을 저장하는 방법, 하나 혹은 그 이상의 테이블을 같은 클러스터내 저장 가능

  -> 검색 효율 증가, 입력,수정,삭제 시 부하 증가

  -> 고려사항

• 분포도가 넓을수록 유리
• 다중 블록 이상의 테이블
• 반복 컬림이 정규화에 의헤 어쩔 수 없이 분할된 경우

 

- 파티션 설계

 -> 데이터 접근 범위를 줄여 성능 향상 기여

 -> 전체 데이터 훼손 가능성 감소

 -> 각각의 분할 영역을 독립적으로 백업, 복구 가능

 

 -> 테이블 파티션 종류

• 범위 분할(지정한 열값 기준)
• 해시 분할
• 조합 분할(범위 + 해시)

 

•물리 용량 산정

- 데이터베이스 물리 용량 설계

• 기초 데이터 수집
• 테이블 용량 설계
• 인덱스 용량 설계
• 테이블 스페이스 용량 설계
• 디스크 용량 설계

 

- 물리 데이터 저장소 분산 설계

 -> 트랜잭션 분석

 -> 무결성 설계

• 실체 무결성
• 영역 무결성
• 참조 무결성
• 사용자 정의 무결성

 

* 무결성 강화 방안

• 어플리케이션
• 데이터베이스 트리거
• 제약조건

 

 -> 분산 설계 : 하나의 논리적 데이터베이스가 물리적으로 네트워크에서 복수의 컴퓨터에 분산되어 있을 경우 사용자가 단일 데이터베이스와 같이 인식시키는 것

 -> 분산 데이터베이스 특성

• 분할 투명성
• 위치 투명성
• 중복 투명성
• 장애 투명성
• 병행 투명성

 

•데이터베이스 반정규화

 정규화된 데이터모델이 시스템의 성능 향상, 개발 과정의 편의성, 운영의 단순화를 목적으로 수행되는 의도적인 정규화 원칙 위배 행위

 

- 테이블 반정규화

 - 테이블 병합 : 조인되는 경우가 많아서 테이블을 합치는 것이 성능 향상에 효율적인 경우

 - 테이블 분할 : 테이블에서 특정 속성들만 집중적으로 접근할 경우

  -> 접근 빈도, Lock, 경합 감소

  -> 전체 조회시 유니온을 사용 -> 성능 저하

• 테이블 수직 분할 : 특정 속성들만 접근이 잦을 경우 컬럼을 쪼갬
• 테이블 수평 분할 : 스키마는 동일, 데이터 값 이용방법이 ROW 별로 구분될 경우 ex) 연도별 이력 조회 등

 

 - 테이블 추가

• 중복 테이블 추가 : 테이블 중복 추가 -> 원격 조인 제거 -> 성능 향상 -> 서버가 다른 경우
• 통계 테이블 추가 : SUM, AVG 등을 미리 수행하여 자동 계산해줌 -> 성능 향상

 

 - 컬럼 반정규화

• 중복 컬럼 추가 : 조인을 감소시키기 위해 중복된 컬럼을 추가 -> 자주 사용하는 컬럼
• 파생 컬럼 추가 : 트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능 저하를 예방하기 위해 미리 값을 계산하여 컬럼에 보관
• 이력 테이블 추가 : 불특정 일자 조회나 최근값을 조회할 때 나타날 수 있는 성능 저하를 예방하기 위해 이력테이블에 기능성 컬럼을 추가

 

- 그 외

 -> PK에 의한 컬럼 추가, Application 오동작 대응 컬럼 추가, 중복관계 추가

 

3) 데이터 조작 프로시저 최적화 하기

- SQL 성능 튜닝 진행 프로세스

 -> 데이터 모델 확인 -> 인덱스 컬럼 조사 -> 인덱스 비교 선택 -> 인덱스 효율성 검증 -> 드라이빙 테이블 선택 -> 조인 유형 선택 -> 함수, 인라인뷰 선택 -> 인덱스 블럭만으로 읽기 -> 힌트 사용

 

- SQL 문장의 파싱된 정보를 찾기 위한 라이브러리 캐시 검색

 -> 라이브러리 캐시와 해시 버킷의 체인리스트 검색

 -> 같은 SQL 스트림 검색

 -> 일치하는 버전 검색

 

- DB Optimizer

 SQL문의 문법적 오류를 확인하고 가장 빠른 데이터 액세스 경로 작성 및 채택 -> 최적의 경로를 찾아주는 DBMS 핵심 엔진

• 질의 변환기(Query Rewrite) : 서브 질의와 뷰 병합, 효과적인 플랜이 있는지 확인
• 쿼리 최적화기(Query Optimizer) : 질의에 대한 접근 경로 설정
• 실행 계획 생성기(QEP Generation) : QEP -> 질의 실행 시 필요한 상세 정보를 생성

 

* RBO(Rule Based Optimizer) : 통계 정보가 없는 상태에서 미리 정해진 Rule에 따라 질의 실행 계획 수립

* CBO(Cost Based Optimizer) : 통계 정보로부터 모든 접근 경로를 고려, 질의 실행 계획 수립 -> 최적화된 실행 계획이 수립되도록 힌트 등 부여

 

 

- SQL 조작을 위한 성능 개선

 - 힌트 사용 : 조인 접근 오퍼레이션 관련 힌트

• USE_NL : nested loop 조인
• USE_NL_WITH_INDEX : 조인
• USE_MERGE : 정렬 합병 조인
• USE_HASH : 해시 조인

 

 - 인덱스 활용

 - 조인 방식/순서 조정

 - 병렬 쿼리(Parallel Query) : 하나의 SQL을 여러개의 CPU가 병렬로 분할  처리

 - SQL 바인딩 변수 사용 : 바인드 변수 미사용시 리터럴 값을 SQL에 삽입하면 매 프리컴파일마다 새로운 SQL로 인식 -> 재컴파일 발생 -> 오래 걸림

 

- SQL 부분 범위 처리

 조건을 만족하는 전체 범위를 처리하는 것이 아닌 운반 단위까지만 처리하여 추출하는 처리방식

 -> 스캔 범위를 나누어서 운반단위를 가능한 빨리 채워서 퍼리 속도를 향상 -> 일부분을 처리하고서도 Optimizer의 특성 이용하여 정확한 결과 도출

 

 - SQL 부분범위 처리 적용 원칙

• 일부분만 처리한 결과가 전체 범위의 결과와 같다면 자격 있음
• Order by -> 처리 못함 -> 인덱스 이용
• UNION, MINUS, INTERSECT 조회 후 추가 연산 -> 처리 못함 -> EXISTS, NOT EXISTS, IN, NOT IN 등 대체

 

- SQL 부분 범위 처리 방안

 -> 인덱스나 클러스터를 이용하여 SORT 대체

 -> 인덱스가 다시 테이블을 읽지 않아도 되는 형태로 사용

 -> MAX 값을 얻어와야 할 경우 인덱스 이용

 -> EXISTS 활용 -> 데이터 존재 여부 체크 등의 로직 시

 

* 전체 범위 처리 결과 추출 방법 : 저장이 완료되면 필요한 2차 가공을 한 후 운반 단위만큼 추출시키고 다음 요구가 있을 때까지 멈춤

 

정보처리기사 실기 - 3. 통합 구현(1) /연계데이터/태그/JSON