정보처리기사DB - 5.DB실무

 

1. 시스템 카탈로그 = Data Dictionary

- 앞에서 메타데이터(DDL의 결과)가 저장된다고 했다.

 

1.1 특징

- 테이블로 되어 있어 SQL로 검색 가능.

- DBMS에 의해 생성/유지

- 갱신 : 직접 SQL로 갱신 불가 -> DBMS가 변화를 감지하면 자동 갱신.

- 분산시스템에서는 제어정보(위치 투명성, 중복투명성 제공)도 포함.

 

1.2 구성요소

- SYSOBJECTS : 각 개체정보를 한 행(ROW)으로 관리.

-> 개체명, 소유자, 개체종류, 생성일 등으로 구성.

- SYSCOLUMNS : 테이블의 모든 속성 정보를 한 행(ROW)로 관리.

-> 속성명, 테이블명, 소유자, 속성타입, 자릿수 등..

- SYSINDEXES : 시스템의 모든 인덱스 정보를 한 행(ROW)로 관리.

-> 인덱스명, 테이블명, 속성수, 튜플수 ...

- SYSVIEWS : 시스템의 모든 뷰의 정보를 한 행(ROW)로 관리.

-> 뷰명, 소유자, 뷰 명령문 ...

- SYSUSERS : DB사용자와 그룹 정보를 한 행(ROW)으로 관리.

-> 사용자 ID, 사용자명, 그룹명, 생성일 .....

- SYSPROTECTS : 사용자 권한 정보를 한 행(ROW)으로 관리.

-> 개체명, 사용자ID, 권한, 보호타입.

 

2. 인덱스

; 빠른 레코드 접근을 위해 <키 값, 포인터> 쌍으로 구성되는 데이터

- 없다면, 전부다 뒤져야 한다.

- 기본인덱스 : 기본키를 위한 인덱스, R형 DBMS는 자동으로 생성

- 보조인덱스 : 기본인덱스 제외 나머지.

- 클러스터 인덱스 : 레코드 물리 순서가 인덱스 엔트리 순서와 일치되게 구성한 것.

 

cf) 트리 : 사이클을 이루지 않도록 구성한 그래프.

트리의 차수 : 서브노드의 숫자.

 

2.1 m-원 검색트리

; 이진 검색 트리를 일반화한 트리

-> 이진검색트리 : 한 노드가 1개의 키 값과 2개의 서브 노드를 갖음.

- 최대 m-1개의 키 값과 m개의 서브 노드를 갖는다.

- 이진 검색에 비해 분기율이 향상. 트리 깊이 낮아짐. => 검색 시간 감소.

- 복잡한 연산 수반(키의 삽입/삭제 시 트리 균형 유지)

 

2.1.1 특징

 

 

2.2 B- 트리

; 균형된 m-원 검색 트리, 많이 사용 됨.

 

2.3 B*-트리

; B- 트리의 빈번한 노드 분할을 줄이기 위해 탄생.

 

2.4 B+-트리

; 노드로 구성된 인덱스 세트와 /단말 노드로만 구성된 순차세트로 구분.

- 직접접근 : 인덱스세트

- 순차접근 : 순차세트

 

3. 트랜잭션(Transaction)

; DB의 하나의 논리적 기능 수행 작업 단위.(연산집합)

 

3.1 특성(ACID)

1. 원자성(Atomicity)

: 모두반영되든지 아니든지.

2. 일관성(Consistency)

; 시스템 고정요소는 트랜잭션 수행 전/후 상태가 같다.

3. 독립성(Isolation)

; 한 트랜잭션이 데이터 갱신 시 해당 데이터를 다른 트랜잭션이 접근 못 함.

4. 영속성(Durability)

; 완료후에는 시스템에 오류가 발생해도 변경 내용은 보존되어야 함.

 

3.2 상태

- 활동(실행 중),

- 실패(오류 발생으로 중단),

- 부분 완료(연산은 끝, COMMIT전)

- 완료(COMMIT 수행 후)

- 철회(ABORTED ;비정상 종료로 ROLLBACK 실행한 상태)

 

4. 회복(Recovery) ; 장애 발생으로 이전 상태로 복구하는 것.

4.1 장애 유형

- 실행 장애 : Query 실행 실패

-> 사용자 실수, 무결성 위반 등..

- 트랜잭션 장애 : 트랜잭션 실행 중지

-> 내부 입력데이터 오류, 불명확한 데이터, 시스템 자원 초과 등..

- 시스템 장애 : 실행 중인 모든 트랜잭션의 실행 중단.

-> 하드웨어 오동작, 정전, DBMS오류, 교착상태 ...

- 미디어 장애 : DB의 일부/전부가 물리적으로 손상

-> 디스크 블록의 손상, 디스크 헤드의 충돌.

 

4.2 회복 관리기(Recovery Manager)

: DBMS가 Log, Memory Dump 등을 이용해 회복기능 수행

 

Memory dump : DB 전체/일부 의 내용을 주기적으로 복사해 두는 작업.

log : <트랜잭션 ID, 데이터 아이템, 이전값, 이후값>

 

log의 예) 점수가 100에서 200으로 변경

<T0 Start>

<T0, 점수, 100, 200>

<T0 commit>

 

- 트랜잭션이 실패하면, log를 이용해 모든 변화를 Undo 시켜서 원래상태로 되돌린다.(commit 기록이 없는 것들)

 

- 트랜잭션이 성공했으나, 디스크에 반영되지 않았다면, log를 이용해

Redo를 수행한다.(start/commit이 있는 것들)

 

 

 

4.3 회복 기법

- Deferred Update(연기 갱신)

; 트랜잭션 성공까지 DB에 Update를 연기하는 방법

=> 변경 내용은 log에 보관

=> 완료시점에 log를 이용해 실제 DB에 반영

=> rollback 되면, 변경내용 취소 없이 무시하면 된다.

=> 회복은 Redo

 

- Immediate Update(즉시 갱신)

; 트랜잭션을 즉시 DB에 반영하는 기법

=> 모든 변경내용은 Log에 보관

=> 회복은 Redo/Undo

 

- Shadow Paging(그림자페이지)

; Update전의 DB를 페이지 단위로 구성하여 그림자 페이지로 별도 보관,

실제 페이지에 트랜잭션을 반영

=> Rollback 시애는 실제 페이지에 그림자 페이지를 집어넣는다.

=> log유지가 필요없고, Redo/Undo 작업도 필요없다.

 

- Check Point(검사점)

; Redo/Undo 수행시 로그 전체 조사를 피하기 위한 기법.

=> 트랜잭션 실행 중에, 주기적으로 로그에 검사점을 함께 보관한다.

=> 장애 발생시 최근 검사점으로 회복 작업을 수행한다.

 

- Media Recovery(미디어 회복)

; DB내용을 주기적으로 안전한 저장장치에 DUMP해 놓는다.

=> 장애가 발생하면, 최근 DUMP를 디스크에 넣고

=> 로그를 이용하여 DUMP 이후 트랜잭션을 Redo한다.

(Undo가 필요없다)

 

 

5. 병행제어(Concurrency Control)

; 동시에 여러 트랜잭션을 병행수행할 때, DB의 일관성을 파괴하지 않도록 제어하는 기술.

 

cf)Interleaving : 트랜잭션들이 번갈아가며 조금씩 자신의 일을 처리하는 것.

 

5.1 목적 : DB공유 최대화, 시스템 활용도 최대화, 사용자 응답시간 최소화, 시간당 트랜잭션 처리 최대화, DB일관성 유지.

 

5.2 병행제어가 없다면

Lost Update(갱신분실)

; 2개 트랜잭션이 같은 데이터 공유했을 때, 갱신 결과의 일부가 사라짐.

 

Inconsistency(불일치성,모순성)

; 복수의 사용자가 같은 데이터를 갱신할 때, 데이터가 일치하지 않아 모순이 발생하는 현상.

 

Cascading Rollback(연쇄 복귀) : 트랜잭션중 하나가 Rollback되면 다른 트랜잭션도 Rollback 되는 현상.

 

5.3 Lock(잠금)

; 트랜잭션에 사용중인 데이터에 다른 트랜잭션의 접근을 막는 것.

- 데이터 접근 전, 잠금을 요청하여 허락받아야 함.

- 데이터 갱신시 Lock - Execute -> Unlock 의 절차를 밟아야 함.

 

5.3.1 잠금단위

; 병행제어에서 한번에 잠금할 수 있는 단위.

ex) DB, Table, Record, Field 등..

=> 크면 잠금 수가 작아 관리가 쉬움, 공유성은 낮아짐.

작다면 잠금수가 많아 관리 어려움, 공유성은 높아짐.

 

5.3.2 Dead lock(교착상태)

; 자원이 Lock되어 있어서, 다른 모든 트랜잭션의 진행이 안되는상황.

 

- Shared-Lock(공유잠금) : Read를 허용하는 잠금.

- Exclusive-lock(배타잠금) : Read/Write 불가.

 

 

5.4 병행제어 기법

5.4.1 Two-Phase Lock Protocol(2단계 잠금 규약) 기법

- Growing(확장)단계 : 잠금만 수행하고 해제는 할 수 없는 단계

- Shrinking(축소)단계 : 해제만 수행하고 잠금은 할 수 없는 단계

=> 스케줄의 직렬성은 보장하지만, Deadlock을 예방할 수 는 없다.

 

5.4.2 Time Stamp Ordering(타임스탬프 순서) 기법

cf) TimeStamp : 시스템이 트랜잭션 실행시 부여하는 값.

cf) 직렬스케줄 : 인터리빙이 없는 트랜잭션 스케줄

병행스케줄 : 인터리빙이 있는 트랜잭션 스케줄

 

- 가장 보편적인 방법, 교착상태가 발생하지 않는다.

- 트랜젝션 실행전에 타임스탬프를 부여하고, 타임스탬프 순서대로 트랜잭션을 수행한다.

- interleaving하게 실행한 것과, timestamp순서대로 실행하는 결과는 항상 동일하다.

 

 

6. 보안(Security)

; DB를 보호하는 것.

 

6.1 보안기술 목표

- 정보 보호(Information Security) : 정보의 노출 방지

- 정보 인증(Information Authentication) : 정보 수정/허위 정보 저장 방지

- 사용자인증(User Authentication) : DB사용자의 신원 확인

 

6.2 기법

6.2.1 SQL을 이용한 권한 부여기법

-> View(뷰) 기법

; 비공개가 필요한 데이터 값은 해당 열을 제외한 뷰를 생성하여 접근방지

 

-> DCL(Data Control Language) 기법

; GRANT/REVOKE 를 사용하여 권한 부여/해제

 

6.2.2 암호화 기법

2.1 Private Key Encryption(개인키 암호화) 기법

(= 대칭 암호 기법, 단일키 암호화 기법)

; 동일 키로 데이터를 암호화/복호화 한다.

 

평문정보 M -> 암호 알고리즘 E &개인키 K -> 암호문 C

암호문 C -> 복호 알고리즘 D & 개인키 K -> 평문정보 M

 

=> 종류 : 전위 기법, 대체 기법, 대수 기법, 합성 기법, DES(Data Encryption Standard)

 

장점 : 암호/복호화 속도 빠름, 단순한 알고리즘, 작은 파일 크기

단점 : 사용자에 비례하여 관리 키가 많아짐.

 

 

2.2 Public Key Encryption(공개키 암호화) 기법

= 비대칭 암호 기법

; 암호화 키(공개키)는 DB 사용자 공개

복호화 키(비밀키)는 관리자가 보관.

; 비밀키는 DB권한 사용자만 나누어 가짐.

 

평문정보 M -> 암호 알고리즘 E &공개키 P -> 암호문 C

암호문 C -> 복호 알고리즘 D & 비밀키 S -> 평문정보 M

 

ex) RSA(Rivest Shamir Adleman) 기법

 

장점 : 키의 분배 용이, 관리키 작음

단점 : 암호/복호 속도 느림, 알고리즘 복잡, 큰 파일 크기.

 

- 사용자마다 키를 주는가

- 키를 하나 만들어서 권한있는 여러명이 공유하는가.

 

7. 튜닝(Tuning)

; DBMS의 성능을 향상시키는 작업(높은 처리량, 짧은 응답시간)

즉, 최적화 = 튜닝

=> 응용프로그램, DB자체, 운영체제 조정 등.

 

7.1 튜닝 목표

- SQL문 실행시 디스크 블록 접근 횟수 최소화.

- 한번 읽은 데이터는 메모리 영역에 보관하여, 메모리에서 신속히 가져오도록 한다.

- 모든 SQL문은 공유 가능하도록, 이름 작성 규칙을 준수한다.

- 잠금 기능 사용을 최소화 하여 여러명의 자원 접근을 허용한다.

 

7.2 튜닝 단계

1. 비즈니스 규칙(Business Rule) 튜닝

; 부적절한 비즈니스 규칙으로 성능 문제가 발생한다. 이 부분을 고려한다.

cf)비즈니스 규칙 : 기업의 업무규칙, 절차

 

2. 데이터 설계(Data Design) 튜닝

; 응용프로그램에서 필요한 데이터 정확히 파악.

데이터간 관계/속석 파악후 정보 구조화 작업.

3. 응용프로그램 설계(Application Design) 튜닝

; 프로세스의 성능을 조사하여, 부하 발생 시점에서 해당 응용프로그램 실행에 필요한 시간/데이터를 조사한다.

; 튜닝이 필요한 대상 프로그램을 선정하고, 집중적으로 튜닝한다.

 

4. 데이터 베이스의 논리적 구조 튜닝

; 작업 부하가 예상되는 질의와 갱신을 고려하여 스키마를 작성.

효율적인 논리구조를 생성한다.

 

5. 데이터베이스 접근 방식(Acess Method)튜닝; DBMS를 잘 활용하자(?)

 

6. 접근 경로 튜닝(Access Path)

; B-트리 인덱스, 비트맵 인덱스, 역방향 인덱스 등을 사용하여 효과적으로 접근한다.

 

7. 메모리 관리(Memory Management) 튜닝

; 메모리 자원을 효율적으로 할당하여 캐시 성능을 개선하고 SQL문의 파싱 작업을 감소시켜야한다.

 

8. 물리적 구조(Physical Structure) 및 입/출력(In/Out) 튜닝

; 효율적인 데이터 블록을 운영하여 접근 비용 최소화

; 디스크 입/출력 성능이 응용프로그램 성능 저하의 주된 원인.

=> 디스크 간 데이터를 분산하여 입/출력 경합을 감소시킨다.

 

9. 자원의 경합(Competition) 튜닝

; 공유 풀(Shared Pool), 잠금(Lock) 등 경합 형태 감소를 위한 노력을 수행해야 함.

 

10. H/W 시스템 특화된 부분의 튜닝

; H/W 시스템의 종류와 특성에 따라 성능의 차이가 남.

 

7.3 인덱스 튜닝

; 튜닝 방법이 쉽고 효율이 좋아 가장 많이 사용 됨.

 

7.3.1 인덱스 종류

- B-트리 인덱스

-> 루트에서 하위 노드로 키값의 크기를 비교해 나가며 단말 노드에서 찾고자 하는 데이터를 검색.

-> 데이터에 상관없이 모든 데이터 인덱스 탐색 시간이 동일.

 

- 비트맵 인덱스

-> 인덱스 컬럼의 데이터를 Bit값으로 바꿔 인덱스 키로 사용하는 방법.

-> 분포도 좋은 컬럼에 적합. Bit로 구성되어 효율적인 논리연산 가능.

-> 저장공간이 작다.

cf) 분포도가 좋다 : 전체 레코드 중 조건에 맞는 레코드 숫자가 적은 경우.

(분포도 = 선택성)

 

- 역방향 인덱스

-> 인덱스 컬럼의 데이터를 역으로 변환하여 인덱스 키로 사용.

-> B-트리 인덱스의 불균형 문제를 해결

-> 데이터 분포도고 좋아져, 검색 성능이 좋다.

 

- 클러스터드 인덱스(Clustered Index)

; 인덱스 키의 순서에 따라 데이터가 정렬/저장.

 

- 넌클러스터드 인덱스(Non-Clustered Index)

; 인덱스의 키만 정렬 되고, 데이터는 정렬 되지 않음.

 

 

7.3.2 인덱스 선정 기준

1. 분포도 좋은 컬럼은 단독 인덱스 생성

2. 데이터 변경이 빈번하지 않은 컬럼에 인덱스 생성

3. 자주 조합되어 사용되는 인덱스는 하나의 결합 인덱스 생성.

4. 결합 인덱스의 컬럼 순서는 분포도 낮은 컬럼을 선행 컬럼으로 설정.

 

7.3.3 인덱스를 사용하지 못하는 경우

-> 인덱스 키 생성 시 NULL값은 인덱스 대상에 포함되지 않으므로

IS NULL/ IS NOT NULL을 적용하여 추출한 데이터는 인덱스의 의미가 없다. => >' ' 를 대신 사용한다.

 

 

-> 부정연산자(<>,!=, NOT 등)는 다수데이터를 검색하므로, 인덱스를 이용하지 못하고 전체테이블을 대상으로 검색할 때 사용한다.

 

-> LIKE, %A 같은 경우, 찾고자 하는 검색어 첫글자를 알 수 없어서 인덱스를 처음부터 검색해야 한다.

 

-> 인덱스 컬럼에 함수/수식을 사용하면 인덱스 키값이 변형되어 인덱스로 사용 할 수 없게 됨.

 

8. 객체지향 데이터베이스(ODB;Object -oriented DataBase)

; 객체지향 데이터 모델 개념을 반영.

표준모델 ; ODMG(객체지향DB관리 그룹) 2.0 표준

 

8.1 ODB 구성

- 객체와 객체 식별자(OID; Object IDentifier)

-> 객체 ; 실세계 개체를 추상적으로 표현한 것.

-> 식별자 ; 식별하기 시스템이 자동생성. 변경 불가.

 

- 속성(Attribute)/메소드(Method)

-> 속성 : 객체 기술에 사용.

-> 메소드 : 객체 속성 값의 변경/검색을 기술

 

- 클래스(Class)/인스턴스(Instance)

-> 클래스 : 공통 특성을 소유한 객체를 그룹화/추상화.

-> 인스턴스 : 클래스를 생성한 뒤, 클래스에 속한 각각의 객체

 

- 상속(Inheritance)

; 계층 관계에 있는 클래스간 공통점/차이점을 기술하는 추상화 기술

; Sub Class & Super Class

; ISA관계라고 한다.

 

- 오버로딩(Overloading)/오버라이딩(Overriding)

오버로딩 : 하나의 메소드 이름으로 다른 파라미터를 지정하여 사용하는것.

오버라이딩 : 슈퍼클래스 메소드를 상속받아 다른 기능 메소드로 재정의 하는 것.

 

8.2 객체 관계 데이터베이스(ORDB; Object Relational DataDase)

; 관계형 + 객체지향

 

8.2.1 등장배경

- 관계형은 정형화(생성시 데이터 크기가 정해진)된 데이터 타입이 필요

=> 비 정형화 데이터 저장이 어려움.

 

8.2.2 ORDB 특징

- 데이터 언어 표준 : SQL3(ISO에서 제시)

- 확장 가능 데이터타입, 사용자 정의 데이터 타입, 사용자 정의 함수, 사용자 정의 프로시저, 사용자 정의 연산자, 대형 객체 타입, 상속 개념 지원.

 

 

9. 고급 DB

- Distributed DataBase(분산 데이터베이스)

- Multimedia DataBase(멀티미디어 데이터베이스)

- Main Memory DataBase(주기억장치 데이터베이스)

- Data Warehouse(데이터 웨어하우스)

- Data Mart(데이터 마트)

- Data Mining(데이터 마이닝)

- OLAP(Online Analytical Processing)

- OLTP(Online Transaction Processing)

- ODBC(Online DataBase Connectivity)

 

 

 

 

 

출처 : 시나공 2018 정보처리기사 실기(산업기사 포함), 한기준 외 3인, 길벗. 2018