1과목 데이터 모델링의 이해
2장. 데이터 모델과 성능
성능 데이터 모델링
- DB 성능향상을 목적으로 설계단계의 데이터 모델링 때부터 정규화, 반정규화, 테이블 통합, 테이블분할, 조인구조, PK, FK 등 여러가지 성능과 관련된 사하이 데이터 모델링에 반영될 수 있도록 하는 것이다.
- 분석/설계 단계에서 데이터 모델에 성능을 고려한 데이터 모델링을 수행할 경우 성능저하에 따른 재업무 비용을 최소화 할 수 있다.
- 데이터의 증가가 빠를수록 성능저하에 따른 성능개선비용은 기하급수적으로 증가하게 된다.
성능 데이터 모델링 고려사항 순서
- 데이터 모델링을 할 때 정규화를 정확하게 수행
- DB 용량산정을 수행
- DB에 발생되는 트랜잭션의 유형을 파악한다.
- 용량과 트랜잭션의 유형에 따라 반정규화를 수행
- 이력모델의 조정, PK/FK 조정, 슈퍼/서브타입 조정
- 성능관점에서 데이터 모델을 검증한다.
함수적 종속성
- 데이터들이 어떤 기준 값에 의해 종속되는 형상
(-) 정규화
- 반복적인 데이터를 분리하고 각 데이터가 종속된 테이블에 적절하게 배치되도록 하는 것
- 1차 정규화: 같은 성격, 내용 컬럼이 연속될 때 컬럼 제거, 테이블 생성
- 2차 정규화: PK 복합키 구성일 때 부분적 함수 종속 관계 테이블 분리
- 3차 정규화: PK가 아닌 일반 컬럼에 의존하는 컬럼 분리
(+) 반정규화
- 정규화된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 개발과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링의 기법
- 조회 시 디스크 I/O가 많거나 경로가 멀어 조인에 의한 성능 저하를 막기 위해 수행
일반적으로 정규화시 입력/수정/삭제 성능이 향상되며, 반정규화시 조인 성능이 향상된다.
반정규화 절차
1. 반정규화 대상조사 (범위처리빈도수, 범위, 통계성)
- 자주 사용되는 테이블에 접근하는 프로세스의 수가 많고 항상 일정한 범위만을 조회하는 경우
- 테이블에 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우에 처리범위를 일정하게 줄이지 않으면 성능을 보장할 수 없는 경우
- 통계성 프로세스에 의해 통계 정보를 필요로 할 때 별도의 통계테이블을 생성
- 테이블에 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기수적으로 어려울 경우
2. 다른 방법유도 검토 (뷰, 클러스터링, 인덱스 조정)
- VIEW 사용: 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 VIEW를 사용한다.
- 클러스터링: 대량의 데이터처리나 부분처리에 의해 성능이 저하되는 경우 크럴스터링을 적용하거나 인덱스를 조정함
- 파티셔닝: 대량의 데이터는 PK의 성격에 따라 부분적인 테이블로 분리할 수 있다. 파티셔닝 키에 의해 물리적 저장공간 분리
- 캐시: 응용 애플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능을 향상시킬 수 있다.
3. 반정규화 적용
3-1. 테이블 반정규화
- 테이블 병합
- 1:1 관계를 통합하여 성능향상
- 1:M 관계를 통합하여 성능향상
- 슈퍼/서브 관계를 통합하여 성능향상
- 테이블 분할(수직분할, 수평분할)
- 수직분할: 칼럼단위 테이블을 디스크 I/O를 분산처리하기 위해 테이블을 1:1로 분리하여 성능향상
- 수평분할: 로우단위로 집중 발생되는 트랜잭션을 분석하여 디스크 I/O 및 데이터 접근의 효율성을 높여 성능을 향상하기 위해 로우단위로 테이블을 쪼갬
- 테이블 추가
- 중복: 다른 업무이거나 서버가 다른 경우 동일한 테이블구조를 중복하여 원격조인을 제거하여 성능 향상
- 통계: SUM, AVG 등을 미리 수행하여 계산해둠으로써 조회 시 성능을 향상
- 이력: 이력테이블 중에서 마스터 테이블에 존재하는 레코드를 중복하여 이력테이블에 존재시켜 성능 향상
- 부분: 하나의 테이블의 전체 칼럼 중 자주 이용하는 집중화된 칼럼들이 있으 ㄹ때 디스크 I/O를 줄이기 위해 해당 칼럼들을 모아놓은 별도의 반정규화된 테이블을 생성
3-2. 칼럼 반정규화
- 중복칼럼 추가: 조인에 의해 처리할 때 성능저하를 예방하기 위해 중복된 칼럼을 위치시킴.
- 파생칼럼 추가: 트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능저하를 예방하기 위해 미리 값을 계산하여 칼럼에 보관
- 이력테이블 칼럼추가: 대량의 이력데이터를 처리할 때 불특정 날 조회나 최근 값을 조회할 때 나타날 수 있는 성능저하를 예방하기 위해 이력테이블에 기능성 칼럼(최근값 여부, 시작과 종료일자 등)을 추가함
- 응용시스템 오작동을 위한 칼럼 추가: 업무적으로는 의미가 없지만 사용자의 실수로 원래 값으로 복구하기 원하는 경우 이전 데이터를 임시적으로 중복하여 보관하는 기법
- PK에 의한 컬러 추가: 단일 PK 안에서 특정 값을 별도로 조회하는 경우 성능 저하 발생할 수 있어 일반속성으로 추가함
3-3. 관계 반정규화: 무결성 유지
- 중복관계 추가: 데이터를 처리하기 위한 여러 경로를 거쳐 조인이 가능하지만 이 때 발생할 수 있는 성능저하를 예방하기 위해 추가적인 관계를 맺는 방법
- 로우 체이닝: 로우의 길이가 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 않고 두 개 이상의 블록에 걸쳐 하나의 로우가 저장되어 있는 형태
- 로우 마이그레이션: 데이터블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록의 빈 공간을 찾아 저장하는 방식
로우 체이닝과 로우 마이그레이션이 발생하여 많은 블록에 데이터가 저장되면 DB 메모리에서 디스크 I/O가 발생할 때 많은 I/O가 발생하여 성능저하 발생 트랜잭션을 분석하여 적절하게 1:1관계로 분리함으로써 성능향상이 가능하도록 해야 한다.
PK에 의해 테이블을 분할하는 방법(파티셔닝)
- RANGE PARTITION: 대상 테이블이 날짜 또는 숫자값으로 분리가 가능하고 각 영역별로 트랜잭션이 분리되는 경우
- LIST PARTITION: 지점, 사업소 등 핵심적인 코드값으로 PK가 구성되어 있고 대량의 데이터가 있는 테이블의 경우
- HASH PARTITION: 지정된 HASH 조건에 따라 해시 알고리즘이 적용되어 테이블이 분리
테이블에 대한 수평/수직분할의 절차
- 데이터 모델링을 완성한다.
- DB 용량산정을 한다.
- 대량 데이터가 처리되는 테이블에 대해 트랜잭션 처리 패턴을 분석한다.
- 칼럼 단위로 집중화된 처리가 발생하는지, 로우 단위로 집중화된 처리가 발생하는지 분석하여 집중화된 단위로 테이블을 분리하는 것을 검토한다.
- 컬럼 많음 -> 1:1 분리
- 데이터 많음 -> 파티셔닝
슈퍼/서브 타입 모델
- 업무를 구성하는 데이터를 공통과 차이점의 특징을 고려하여 효과적 표현.
[논리적 모델] - 슈퍼 타입: 공통 부분
- 서브 타입: 공통으로부터 상속받아 다른 엔터티와 차이가 있는 속성
슈퍼/서브 타입 데이터 모델의 변환기술
- 개별로 발생되는 트랜잭션에 대해서는 개별 테이블로 구성
- 슈퍼타입+서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼+서브타입 테이블로 구성
- 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성
인덱스 특성을 고려한 PK/FK DB 성능향상
- 인덱스의 특징은 여러 개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 좋은 효율을 나타낸다.
- 앞쪽에 위치한 속성의 값이 가급적 '=' 아니면 최소한 범위 'BETWEEN' '<>'가 들어와야 효율적이다.
분산 DB
- 여러 곳으로 분산되어있는 DB를 하나의 가상시스템으로 사용할 수 있도록 한 DB
- 논리적으로 동일한 시스템에 속하지만, 컴퓨터 네트워크를 통해 물리적으로 분산되어 있는 데이터집합
분산 DB를 만족하기 위한 6가지 투명성
- 분할 투명성(단편화): 하나의 논리적 Relation이 여러 단편으로 분할되어 각 사본이 여러 site에 저장
- 위치 투명성: 사용하려는 데이터의 저장 장소 명시 불필요, 위치정보가 시스템 카탈로그에 유지
- 지역사상 투명성: 지역 DBMS와 물리적 DB사이의 Mapping 보장
- 중복 투명성: DB 객체가 여러 site에 중복되어 있는지 알 필요가 없는 성질
- 장애 투명성: 구성요소의 장애에 무관한 트랜잭션의 원자성 유지
- 병행 투명성: 다수 트랜잭션 동시 수행시 결과의 일과성 유지, TimeStamp, 분산 2단계 Locking 이용
분산 DB 장-단점
- 장점: 지역 자치성, 신뢰성 가용성, 효용성, 융통성, 빠른 응답속도, 비용절감, 각 지역 사용자 요구 수용
- 단점: 비용증가, 오류의 잠재성 증대, 설계 관리의 복잡성, 불규칙한 응답 속도, 통제의 어려움, 데이터 무결성 위협
분산 DB 적용 기법
- 테이블 위치 분산: 설계된 테이블을 본사와 지사단위로 분산, 위치별 DB문서 필요
- 테이블 분할 분산: 각각의 테이블을 쪼개어 분산
- 수평분할: 로우 단위로 분리, 지사별로 다를때, 중복X
- 수직분할: 칼럼 단위로 분리, 각 테이블에 동일 PK 있어야 함.
- 테이블 복제 분산: 동일한 테이블을 다른 지역이나 서버에서 동시에 생성하여 관리하는 유형
- 부분복제: 마스터 DB에서 테이블의 일부의 내용만 다른 지역이나 서버에 위치
- 광역복제: 마스터 DB 테이블의 내용을 각 지역이나 서버에 존재
- 테이블 요약 분산: 지역 간에 또는 서버 간에 데이터가 비슷하지만 서로 다른 유형으로 존재하는 경우
- 분석요약: 동일한 테이블 구조를 가지고 있으면서 분산되어 있는 동일한 내용의 데이터를 이용하여 통합된 데이터를 산출하는 방식
- 통합요약: 분산되어 있는 다른 애용의 데이터를 이용하여 통합된 데이터를 산출하는 방식.
분산 DB 설계를 고려해야 하는 경우
- 성능이 중요한 사이트
- 공통코드, 기준정보, 마스터 데이터의 성능향상
- 실시간 동기화가 요구되지 않는 경우, Near Real Time 특징을 가지고 있는 경우
- 특정 서버에 부하가 집중되어 부하를 분산
- 백업 사이트 구성하는 경우
2과목
1장. SQL 기본
DB
- 특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것을 의미한다.
DBMS
- 효율적인 데이터 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 SW
SQL
- 관계형 DB에서 데이터 정의, 조작, 제어를 위해 사용하느 언어
- DML: SELECT, INSERT, UPDATE, DELETE
- DDL: CREATE, ALTER, DROP, RENAME
- DCL: GRANT, REVOKE
- TCL: COMMIT, ROLLBACK
테이블
- DB 기본 단위, 데이터를 저장하는 객체
- 가로 = 행 = 로우 = 튜플 = 인스턴스
- 세로 = 열 = 컬럼
정규화
- 데이터의 정합성 확보와 데이터 입력/수정/삭제시 발생할 수 있는 이상현상을 방지하기 위해 중복 제거
기본키
- 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 칼럼
외부키
- 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 칼럼
DDL
데이터 유형
-
CHAR(s): 고정 길이 문자열 정보. 최대 길이 만큼 공간 채움
-
VARCHAr2(s): 가변 길이 문자열 저보. 할당된 변수 값의 바이트만 적용
-
NUMBER: 정수, 시수 등 숫자 정보
-
DATE: 날짜와 시각 정보
-
테이블 명은 다른 테이블의 이름과 중복되면 안된다.
-
테이블 내의 칼럼명은 중복될 수 없다.
-
각 칼럼들은 , 로 구분되고 ; 로 끝난다.
-
칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
-
테이블 명과 칼럼명은 반드시 문자로 시작해야 한다.
제약조건: 데이터의 무결성 유지
- PRIMARY KEY(기본키): UNIQUE & NOT NULL
- UNIQUE KEY(고유키): 고유키 정의
- NOT NULL: NULL 값 입력금지
- CHECK: 입력 값 범위 제한
- FOREIGN KEY(외래키): NULL 가능, 여러속성가능
테이블 생성
CREATE TABLE PLAYER(
PLAYER_ID CHAER(7) NOT NULL,
PLAYER_NAME VARCHAR2(20) NOT NULL);테이블 구조 변경
-
추가: ALTER TABLE PLAYER ADD(ADDRESS VARCHAR2(80));
-
삭제: ALTER TABLE PLAYER DROP COLUMN ADDRESS;
-
수정: ALTER TABLE TEAM_TEMP MODIFY(ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);
-
제약조건 삭제: DROP CONSTRAINT 조건명;
-
제약 조건 추가: ADD CONSTRAINT 조건명 조건 (칼럼명);
-
테이블명 변경: RENAME PLAYER TO PLAYER_BACKUP;
-
테이블 삭제: DROP TABLE PLAYER;
-
테이블 데이터 삭제: TRUNCATE TABLE PLAYER;
-
컬럼명 변경: RENAME COLUMN TEAM_ID TO T_ID;
DML
- DDL 명령어의 경우 실행시 AUTO COMMIT 하지만 DML의 경우 COMMIT을 입력해야 한다.
INSERT INTO PLAYER (PLAYER) VALUES ('PJS');
UPDATE PLAYER SET BACK_NO = 60;
DELETE FROM PLAYER;
SELECT PLAYER_ID FROM PLAYER;
SELECT PLAYER AS "선수명" FROM PLAYER;
DISTINCT: 중복 시 1회만 출력와일드카드
- *: 모든
- %: 모든
- -: 한 글자
합성 연산자
- 문자와 문자 연결: ||
TCL
- 트랜잭션: 밀접히 관련되어 분리될 수 없는 1개 이상의 DB 조작. 논리적 연산단위.
- COMMIT: 올바르게 반영된 데이터를 DB에 반영
- ROLLBACK: 트랜잭션 시작 이전의 상태로 되돌림. COMMIT 되지 않은 모든 트랜잭션을 롤백함.
- SAVEPOINT: 저장 지점
트랜잭션의 특성
- 원자성: 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않아야 함.
- 일관성: 트랜잭션 실행 전 DB 내용이 잘못 되지 않으면 실행 후도 잘못 되지 않아야 함.
- 고립성: 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
- 지속성: 트랜잭션이 성공적으로 수행되면 DB의 내용은 영구적으로 저잘된다.
SAVEPOINT SVPT1;
ROLLBACK TO SVPT1;
COMMIT;
개발 기록장