Move Fast

  트랜잭션(Transaction)

• 트랜잭션 : 일관성을 유지하는 단위
• 항공기 예약, 은행, 신용 카드 처리, 대형 할인점 등에서는 대규모 데이터베이스를 수백, 수천 명 이상의 사용자들이 동시에 접근한다.
• 많은 사용자들이 동시에 데이터베이스의 서로 다른 부분 또는 동일한 부분을 접근하면서 데이터베이스를 사용한다.
• 동시성 제어(Concurrency control)
• 동시에 수행되는 트랜잭션들이 데이터베이스에 미치는 영향은 이들을 순차적을 수행하였을 때 데이터베이스에 미치는 영향과 같도록 보장하는 것
• 다수 사용자가 데이터베이스를 동시에 접근하도록 허용하면서 데이터베이스의 일관성을 유지한다.
• 회복(Recovery) : 데이터베이스를 갱신하는 도중에 시스템이 고장 나도 데이터베이스의 일관성을 유지한다.
• 반드시 회복과 동시성 제어가 함께 제공되어야 한다.

사원이 500명 재직하고 있는 회사에서 모든 사원의 급여를 6% 인상하는 연산을 데이터베이스의 EMPLOYEE 릴레이션에서 수행한다. 이때 500명 전원의 급여가 수정되거나 한 명의 급여도 갱신되지 않도록 DBMS가 보장해야 한다.

320번째 사원까지 수정한 상태에서 컴퓨터 시스템이 다운된 후에 재기동되었을 때 DBMS는 어떻게 대응해야 하는가?

-> 예전 상태로 돌려보낸다. 이후 다시 질의한다.

DBMS가 추가로 정보를 유지하지 않는다면 DBMS가 재기동된 후에 어느 직원의 투플까지 수정되었는가를 알 수 없다. 

-> 로그(log) 유지 필요

은행 고객은 자신의 계좌에서 다른 계좌로 송금할 수 있다. 정미림은 자신의 계좌에서 100,000원을 인출하여 안명석의 계좌로 이체하려고 한다. 고객들의 계좌 정보가 CUSTOMER 릴레이션에 들어 있다.

두 개의 UPDATE 문을 사용하여, 하나의 UPDATE문에서는 정미림의 잔액을 100,000원 감소시키고, 또 다른 UPDATE문에서는 안명석의 잔액을 100,000원 증가시킨다.

첫 번째 UPDATE문을 수행한 후에 두 번째 UPDATE문을 수행하기 전에 컴퓨터 시스템이 다운되면 재기동한 후에 정미림은 보냈으나 안명석은 받지 못한 상태가 될 수 있다.

대응책으로, 두 개의 UPDATE문을 둘 다 완전하게 수행되거나 한 UPDATE문도 수행되어서는 안되도록, 즉 하나의 단위(트랜잭션)처럼 동작하도록 DBMS가 보장해야 한다.

기본적으로 각각의 SQL문이 하나의 트랜잭션으로 취급된다.

BEGIN TRANSACTION

[sql1]

[sql2]

...

END TRANSACTION

두 개 이상의 SQL문들을 하나의 트랜잭션으로 취급하려면 사용자가 이를 명시적으로 표시해야 한다.

여행사에서 고객의 요청에 따라 항공기를 예약하려고 한다. 아래의 응용 프로그램은 고급 프로그래밍 언어로 작성한 프로그램 내에서 세 개의 SQL문을 내포시킨 것이다. 이 응용 프로그램에서는 두 개의 릴레이션을 사용한다.

FLIGHT 릴레이션은 각 항공기편마다 FNO(항공기편 번호), DATE(출발일), SOURCE(출발지), DESTINATION(목적지), SEAT_SOLD(팔린 좌석 수), CAPACITY(총 좌석수) 등의 애트리뷰트를 갖는다.

FLIGHT(FNO, DATE, SOURCE, DESTINATION, SEAT_SOLD, CAPACITY)

RESERVED 릴레이션은 각 예약 고객마다 FNO(항공기 번호), DATE(출발일), CUST_NAME(고객 이름), SPECIAL(비고) 등의 애트리뷰트를 갖는다.

RESERVED(FNO, DATE, CUST_NAME, SPECIAL)

FLIGHT 릴레이션에서 고객이 원하는 날짜의 항공기편에 빈 좌석이 남아 있으면 팔린 좌석수를 1만큼 증가시키고, RESERVED 릴레이션에 예약 고객에 관한 투플을 삽입한다.

만일 SQL2 를 수행하고 SQL3를 수행하기 전에 컴퓨터 시스템이 다운되고 재기동한 한다면? 

-> 위의 세 개의 SQL문이 모두 완전하게 수행되거나 하나도 수행되어서는 안되도록, 즉 하나의 단위(트랜잭션)처럼 DBMS가 취급해야 한다.

DBMS는 각 SQL문의 의미를 알 수 없으므로 하나의 트랜잭션으로 취급해야 하는 SQL문들의 범위를 사용자가 명시적으로 표시해야 한다.

  트랜잭션의 특성(ACID 특성)

• 원자성(Atomicity)

- 한 트랜잭션 내의 모든 연산들이 완전히 수행되거나 전혀 수행되지 않음(all or nothing)을 의미한다.

- DBMS의 회복 모듈은 시스템이 다운되는 경우에, 부분적으로 데이터베이스를 갱신한 트랜잭션의 영향을 취소함(nothing 보장)으로써 트랜잭션의 원자성을 보장한다.

- 완료된 트랜잭션이 갱신한 사항은 트랜잭션의 영향을 재수행함(all 보장)으로써 트랜잭션의 원자성을 보장한다.

• 일관성(Consistency)

- 어떤 트랜잭션이 수행되기 전에 데이터베이스가 일관된 상태를 가졌다면 트랜잭션이 수행된 후에 데이터베이스는 또 다른 일관된 상태를 가진다.

- 트랜잭션이 수행되는 도중에는 데이터베이스가 일시적으로 일관된 상태를 갖지 않을 수 있다. (일시적 불일치 상태)

• 고립성(Isolation)

- 한 트랜잭션이 데이터를 갱신하는 동안 이 트랜잭션이 완료되기 전에는 갱신 중인 데이터를 다른 트랜잭션들이 접근하지 못하도록 해야 한다.

- 다수의 트랜잭션들이 동시에 수행되더라도 그 결과는 어떤 순서에 따라 트랜잭션들을 하나씩 차례대로 수행한 결과와 같아야 한다.

- DBMS의 동시성 제어 모듈이 트랜잭션의 고립성을 보장한다.

- DBMS는 응용들의 요구사항에 따라 다양한 고립 수준(isolation level)을 제공한다.

• 지속성(Durability)

- 일단 한 트랜잭션이 완료되면 이 트랜잭션이 갱신한 것은 그 후에 시스템에 고장이 발생하더라도 손실되지 않는다.

- 완료된 트랜잭션의 효과는 시스템이 고장난 경우에도 데이터베이스에 반영된다.

- DBMS의 회복 모듈은 시스템이 다운되는 경우에도 트랜잭션의 지속성을 보장한다

  트랜잭션의 완료(Commit)와 철회(Abort)

• Commit : 트랜잭션에서 변경하려는 내용이 데이터베이스에 완전하게 반영되는 것
• SQL 구문 : COMMIT WORK
• Abort : 트랜잭션에서 변경하려는 내용이 데이터베이스에 일부만 반영된 경우에는 원자성을 보장하기 위해서, 트랜잭션이 갱신한 사항을 트랜잭션이 수행되기 전의 상태로 되돌리는 것
• SQL 구문 : ROLLBACK WORK

  트랜잭션이 성공하지 못하는 경우

• 시스템(사이트) 고장 : 중앙 처리 장치, 주기억 장치, 전원 공급 장치 등이 고장난 경우
• 트랜잭션 고장 : 트랜잭션이 수행되는 도중에 철회되는 경우
• 매체 고장 : 디스크 헤드, 디스크 컨트롤러 등이 고장 나서 보조 기억 장치의 전부 또는 일부 내용이 지워진 경우
• 통신 고장
• 자연적 재해
• 부주의 또는 고의적인 고장

  동시성 제어(Concurrency Control)

• 대부분의 DBMS들은 다수 사용자용이며, 여러 사용자들이 동시에 동일한 테이블을 접근하기도 한다.
• DBMS의 성능을 높이기 위해 여러 사용자의 질의나 프로그램들을 동시에 수행하는 것이 필수적이다.
• 동시성 제어 기법 : 여러 사용자들이 다수의 트랜잭션들을 동시에 수행하는 환경에서 부정확한 결과를 생성(트랜잭션들 간의 간섭)하지 않도록 한다.
• 직렬 스케줄(Serial Schedule) : 여러 트랜잭션들의 집합을 한 번에 한 트랜잭션씩 차례대로 수행한다.
• 비직렬 스케줄(Non-serial Schedule) : 여러 트랜잭션들을 동시에 수행한다. (실제로는 매우 빠른 속도로 번갈아가며 수행하여 동시에 수행하는 것처럼 보임)
• 직렬가능(Serializable) : 비직렬 스케줄의 결과가 어떤 직렬 스케줄의 수행 결과와 동등함을 의미한다.
• 데이터베이스 연산

- Input(X) : 데이터베이스 항목 X를 포함하고 있는 블록을 주기억 장치의 버퍼로 읽어들인다.

- Output(X) : 데이터베이스 항목 X를 포함하고 있는 블록을 디스크에 기록한다.

- read_item(X) : 주기억 장치 버퍼에서 데이터베이스 항목 X의 값을 프로그램 변수 X로 복사한다.

- write_item(X) : 프로그램 변수 X의 값을 주기억 장치 내의 데이터베이스 항목 X에 기록한다.

• 동시성 제어 없이 다수의 트랜잭션을 동시에 수행할 때 생길 수 있는 문제

- 갱신 손실(lost update) : 수행 중인 트랜잭션이 갱신한 내용을 다른 트랜잭션이 덮어 씀(overwrite)으로써 갱신이 무효가 되는 것

- 오손 데이터 읽기(dirty read) : 완료되지 않은 트랜잭션이 갱신한 데이터를 읽는 것

- 반복할 수 없는 읽기(unrepeatable read) : 한 트랜잭션이 동일한 데이터를 두 번 읽을 때 서로 다른 값을 읽는 것

하나의 SQL문은 DBMS 내에서 여러 개의 명령들로 나뉘어 수행된다. 다수 사용자 환경에서는 여러 사용자들이 동시에 요청한 트랜잭션의 명령들이 섞여서 수행될 수 있다. 트랜잭션 T1은 X에서 Y로 100,000을 이체하고, 트랜잭션 T2는 X의 값에 50,000을 더하려고 한다. 두 트랜잭션이 수행되기 전의 X와 Y의 초기값이 각각 300,000과 600,000이라고 가정하면 T1의 수행을 먼저 완료하고 T2의 수행을 완료하던지, T2의 수행을 먼저 완료하고 T1의 수행을 완료하던지 관계 없이 X의 최종값은 250,000, Y의 최종값은 700,000이 되어야 한다.

트랜잭션 T1이 정미림의 잔액을 100,000원 감소시킨 후에 트랜잭션 T2는 모든 계좌의 잔액의 평균값을 검색하였다. 그 이후에 T1이 어떤 이유로 철회되면 T1이 갱신한 정미림 계좌의 잔액은 원래 상태로 되돌아간다. 따라서 T2는 완료되지 않은 트랜잭션이 갱신한 데이터, 즉 틀린 데이터를 읽었다.

먼저 트랜잭션 T2는 모든 계좌의 잔액의 평균값을 검색하였다. 트랜잭션 T2가 완료되기 전에 트랜잭션 T1이 정미림의 잔액을 100,000원 감소시키고 완료되었다. 트랜잭션 T2가 다시 모든 계좌의 잔액의 평균값을 검색하면 첫 번째 평균값과 다른 값을 보게 된다. 동일한 읽기 연산을 여러 번 수행할 때 매번 서로 다른 값을 보게 될 수 있다. 즉, 한 트랜잭션이 동일한 데이터에 접근할 때 다른 트랜잭션에 의해 바뀐 값을 읽게 되는 것이다.

여러 여행사에서 동시에 고객들의 요청에 따라 동일한 날짜에 출발하는 항공기의 빈 좌석 유무를 검사할 수 있다.

만일 두 여행사에서 각각 트랜잭션을 수행하는 과정에 SQL문의 수행 결과로 특정 항공기에 빈 좌석이 1개 남아 있다는 사실을 확인하고 동시에 두 여행사에서 SQL문을 수행하여 팔린 좌석수를 1만큼씩 증가시키고 자신의 고객의 정보를 항공사 데이터베이스에 입력하려 할 때 DBMS가 아무런 조치를 취하지 않으면, 1개 남은 좌석에 두 명의 고객이 배정되는 결과를 초래하게 된다.

  로킹(locking)

• 데이터 항목을 로킹하는 개념은 동시에 수행되는 트랜잭션들의 동시성을 제어하기 위해서 가장 널리 사용되는 기법
• 로크(lock) : 데이터베이스 내의 각 데이터 항목과 연관된 하나의 변수
• 각 트랜잭션이 수행을 시작하여 데이터 항목을 접근할 때마다 요청한 로크에 관한 정보는 로크 테이블(lock table) 등에 유지된다.
• 트랜잭션에서 갱신 목적으로 데이터 항목을 접근할 때는 독점 로크(X-lock, eXclusive lock)를 요청한다. (독점 로크는 다른 트랜잭션이 접근하는 것을 막는다.)
• 트랜잭션에서 검색 목적으로 데이터 항목을 접근할 때는 공유 로크(S-lock, Shared lock)를 요청한다.
• 트랜잭션이 데이터 항목에 대한 접근을 끝낸 후에 로크를 해제(unlock)한다.
• 로크를 해제할 때 로크 테이블에서 해당 로크의 레코드(로크 정보)가 삭제된다.

  2단계 로킹 프로토콜(2-phase locking protocol)

• 로크를 요청하는 것과 로크를 해제하는 것이 2단계로 이루어진다.
• 로크 확장 단계가 지난 후에 로크 수축 단계에 들어간다.
• 일단 로크를 한 개라도 해제하면 로크 수축 단계에 들어간다.
• 로크 확장 단계(1단계) : 트랜잭션이 데이터 항목에 대하여 새로운 로크를 요청할 수 있지만 보유하고 있던 로크를 하나라도 해제할 수 없다.
• 로크 수축 단계(2단계) : 보유하고 있던 로크를 해제할 수 있지만 새로운 로크를 요청할 수 없다. 로크를 조금씩 해제(다른 트랜잭션이 로크를 걸 수 있음)할 수도 있고, 트랜잭션이 완료 시점에 이르렀을 때 한꺼번에 모든 로크를 해제(일반적인 방식)할 수도 있다.
• 로크 포인트(lock point) : 한 트랜잭션에서 필요로 하는 모든 로크를 걸어놓은 시점

A=100, B=200일 때,

T1 실행 후, T2 실행 : A=202, B=402

T2 실행 후, T1 실행 : A=201, B=401

직렬 스케줄의 경우 : A=202, B=401

* 로크를 일찍 해제하는 방식은, 직렬 가능한 스케줄을 생성하지 못한다.

비직렬 스케줄과 직렬 스케줄의 결과가 일치하지 않는다. *

  데드록(Deadlock)

• 2단계 로킹 프로토콜에서는 데드록이 발생할 수 있다.
• 데드록은 두 개 이상의 트랜잭션들이 서로 상대방이 보유하고 있는 로크를 요청하면서 기다리고 있는 상태(자원을 기다리는 상태)를 말한다.

1) T1이 X에 대해 독점 로크를 요청하여 허가받음

2) T2가 Y에 대해 독점 로크를 요청하여 허가받음

3) T1이 Y에 대해 공유 로크나 독점 로크를 요청하면, 로크가 해제될 때까지 기다림

4) T2가 X에 대해 공유 로크나 독점 로크를 요청하면 로크가 해제될 때까지 기다림

• 데드록을 해결하기 위해서는 데드록을 방지하는 기법이나, 데드록을 탐지하고 희생자(우선순위가 낮은 트랜잭션 또는 최근에 시작된 트랜잭션)를 선정하여 데드록을 푸는 기법 등을 사용한다.
• 데드록이 발생할 필요 충분 조건

- Mutual exclusion : 공유자원이 아닌 경우

- Hold and wait

- No preemption : 자원을 양보하지 않고, 상대방도 강제로 뺏지 못하는 경우

- Circular wait : waiting 상태를 유지하는 경우

  다중 로크 단위(Multiple Granularity)

• 대부분의 트랜잭션들이 소수의 투플들을 접근하는 데이터베이스 응용에서는 투플 단위로 로크를 해도 로크 테이블을 다루는 시간이 오래 걸리지 않는다.
• 트랜잭션들이 많은 투플을 접근하는 데이터베이스 응용에서 투플 단위로만 로크를 한다면 로크 테이블에서 로크 충돌을 검사하고, 로크 정보를 기록하는 시간이 오래 걸린다.
• 트랜잭션이 접근하는 투플의 수에 따라 로크를 하는 데이터 항목의 단위를 구분할 필요가 있다.
• 한 트랜잭션에서 로크할 수 있는 데이터 항목이 두 가지 이상 있으면 다중 로크 단위라고 말한다.
• 데이터베이스에서 로크할 수 있는 단위로는 데이터베이스, 릴레이션, 디스크 블록, 투플 등이 있다. 데이터베이스 단위는 주로 백업 시 사용된다.
• 일반적으로 DBMS는 각 트랜잭션에서 접근하는 투플 수에 따라 자동적으로 로크 단위를 조정한다.
• 로크 단위가 작을수록 로킹에 따른 오버헤드가 증가한다.
• 로크 단위가 작을수록 동시성의 정도는 증가한다.

릴레이션 R에 속하는 디스크 블록 b1에 다섯 개의 투플 t1, t2, t3, t4, t5가 있다고 가정하자. 또한 트랜잭션 T1은 이 중에서 투플 t1과 t4를 갱신하고, 트랜잭션 T2는 투플 t2를 검색한다고 가정하자. 만일 로크 단위가 투플이라면 두 트랜잭션이 접근하는 투플들이 서로 상이하므로 해당 투플에 로크를 걸고 두 트랜잭션이 동시에 수행될 수 있다.

위의 상황에서, 트랜잭션 T1은 블록 단위, T2는 투플 단위로 로크를 하는 경우에, 먼저 T1이 블록 b1에 대해 독점 로크를 요청하여 허가를 받으면 이 블록에 들어 있는 투플 다섯 개에도 모두 독점 로크가 걸린다. 그 다음에 트랜잭션 T2가 투플 t2에 대해 공유 로크를 요청하면 트랜잭션 T1이 로크를 풀 때까지 기다려야 한다.

  팬텀 문제(Phantom Problem)

• 두 개의 트랜잭션 T1과 T2가 EMPLOYEE 릴레이션에 대해서 아래와 같은 순서대로 수행된다고 가정해보자.
• 트랜잭션 T1은 EMPLOYEE 릴레이션에서 1번 부서에 근무하는 사원들의 이름을 검색하는 동일한 SELECT문을 두 개 포함하고, 트랜잭션 T2는 1번 부서에 근무하는 사원 투플을 한 개 삽입하는 INSERT문을 포함한다.
  

• 시간 1에 트랜잭션 T1의 SELECT문이 수행되면 1번 부서에 근무하는 사원들의 이름이 검색된다.
• 시간 2에 트랜잭션 T2의 INSERT문이 수행되면 EMPLOYEE 릴레이션에 1번 부서에 근무하는 홍길동 사원에 대한 레코드가 삽입된다.
• 시간 3에 트랜잭션 T1의 두 번째 SELECT문이 수행되면 추가된 투플까지 포함되어 기존의 정보 + 1명의 정보가 출력된다. 즉, “반복할 수 없는 읽기”가 발생하였다.
• 한 트랜잭션 T1에 속한 첫 번째 SELECT문과 두 번째 SELECT문의 수행 결과가 다르게 나타나는 현상을 팬텀 문제라 한다.
• 로킹에도 불구하고 나타난 반복할 수 없는 읽기는 투플 단위의 로크로는 해결할 수 없다.
• 인덱스 단위의 로크의 경우, T1이 인덱스 로크를 풀 때까지 T2는 독점 로크를 걸지 못하게 된다.
• 팬텀 문제의 해결책 : 인덱스 수준에서 로킹하는 것이 필요하다.

  회복의 개요

• 회복의 필요성

- 어떤 트랜잭션 T를 수행하는 도중에 시스템이 다운되었을 때 T의 수행 효과가 디스크의 데이터베이스에 일부 반영되었을 수 있다. (원자성 X)

- 어떻게 T의 수행을 취소하여 원자성을 보장할 것인가?

- 트랜잭션 T가 완료된 직후에 시스템이 다운되면 T의 모든 갱신 효과가 주기억 장치로부터 디스크에 기록되지 않았을 수 있다. (지속성 X)

- 어떻게 T의 수행 결과가 데이터베이스에 완전하게 반영되도록 할 것인가?

- 디스크의 헤드 등이 고장 나서 디스크의 데이터베이스를 접근할 수 없다면 어떻게 할 것인가?

• 여러 응용이 주기억 장치 버퍼 내의 동일한 데이터베이스 항목을 갱신한 후에 디스크에 기록함으로써 성능을 향상시키는 것이 중요하다.
• 버퍼의 내용이 디스크에 기록하는 횟수를 가능하면 최대한 줄이는 것이 일반적이다.
• 버퍼가 꽉 찼을 때 또는 트랜잭션이 완료될 때(강제기록) 버퍼의 내용이 디스크에 기록될 수 있다.
• 트랜잭션이 버퍼에는 갱신 사항을 반영했지만 버퍼의 내용이 디스크에 기록되기 전에 고장이 발생할 수 있다.
• 고장이 발생하기 전에 트랜잭션이 완료 명령(COMMIT)을 수행했다면 회복 모듈은 이 트랜잭션의 갱신 사항을 재수행(REDO)하여 트랜잭션의 갱신이 지속성을 갖도록 해야 한다.
• 고장이 발생하기 전에 트랜잭션이 완료 명령(COMMIT)을 수행하지 못했다면 원자성을 보장하기 위해서 이 트랜잭션이 데이터베이스에 반영했을 가능성이 있는 갱신 사항을 취소(UNDO)해야 한다.
• 단, 갱신한 값이 아닌 현재 값이 블록에 있는 상태라면 취소나 재수행을 할 수 없다.

  저장 장치의 유형

• 주기억 장치와 같은 휘발성 저장 장치에 들어 있는 내용은 시스템이 다운된 후에 모두 사라진다.
• 디스크와 같은 비휘발성 저장 장치에 들어 있는 내용은 디스크 헤드 등이 손상을 입지 않는 한 시스템이 다운된 후에도 유지된다.
• 안전 저장 장치(stable storage) : 모든 유형의 고장을 견딜 수 있는 저장 장치
• 두 개 이상의 비휘발성 저장 장치가 동시에 고장날 가능성이 매우 낮으므로 비휘발성 저장 장치에 두 개 이상의 사본을 중복해서 저장함으로써 안전 저장 장치를 구현한다.

  재해적 고장과 비재해적 고장

• 재해적 고장

- 디스크가 손상을 입어서 데이터베이스를 읽을 수 없는 고장

- 재해적 고장으로부터의 회복은 데이터베이스를 백업해 놓은 자기 테이프를 기반으로 한다.

• 비재해적 고장

- 그 이외의 고장

- 대부분 회복 알고리즘들은 비재해적 고장에 적용된다.

- 로그를 기반으로 한 즉시 갱신, 로그를 기반으로 한 지연 갱신, 그림자 페이징(shadow paging) 등 여러 회복 알고리즘들이 있다.

- 대부분 상용 DBMS에서 로그를 기반으로 한 즉시 갱신 방식을 사용한다.

  로그를 사용한 즉시 갱신

• 즉시 갱신에서는 트랜잭션이 데이터베이스를 갱신한 사항이 주기억 장치의 버퍼에 유지되다가 트랜잭션이 완료되기 전이라도 디스크의 데이터베이스에 기록될 수 있다.
• 데이터베이스에는 완료된 트랜잭션의 수행 결과뿐만 아니라 철회된 트랜잭션의 수행 결과도 반영될 수 있다.
• 트랜잭션의 원자성과 지속성을 보장하기 위해 DBMS는 로그(Log)라고 부르는 특별한 파일을 유지한다.
• 데이터베이스의 항목에 영향을 미치는 모든 트랜잭션의 연산(갱신 연산)들에 대해서 로그 레코드를 기록한다.
• 각 로그 레코드는 로그 순서 번호(Log Sequence Number, LSN)로 식별된다.
• 주기억 장치 내의 로그 버퍼에 로그 레코드들을 기록하고 로그 버퍼가 꽉 찰 때 디스크에 기록한다.
• 메인 메모리에 데이터베이스 버퍼보다 로그 버퍼를 항상 먼저 디스크로 내보낸다.
• 로그는 데이터베이스 회복에 필수적이므로 일반적으로 안전 저장 장치에 저장된다.
• 이중 로그(dual logging) : 로그를 두 개의 디스크에 중복해서 저장하는 것
• 각 로그 레코드가 어떤 트랜잭션에 속한 것인가를 식별하기 위해서 각 로그 레코드마다 트랜잭션 ID를 포함시킨다.
• 동일한 트랜잭션에 속하는 로그 레코드들은 연결 리스트로 유지된다.
  

[ 디스크와 메모리 내 버퍼의 읽기/쓰기 관계 ]

고장이 나서 재기동하면, 고장 전 로그 버퍼에 있는 로그 레코드가 로그 파일에 기록되었을 때

로그 파일에 있는 새 값을 가지고 연산한 결과를 데이터베이스 버퍼에 다시 기록한다.

아래의 두 트랜잭션 T1과 T2를 고려해 보자. T1 다음에 T2가 수행되고, 데이터베이스 항목 A, B, C, D, E의 초기값은 각각 100, 300, 5, 60, 80이라고 가정한다. 

이 두 트랜잭션을 수행하면 아래의 오른쪽 표와 같은 로그 레코드들이 생성된다. 오른쪽 표에서 2번 로그 레코드는 트랜잭션 T1이 데이터베이스 항목 B를 이전값 300에서 새값 400으로 갱신했음을 나타낸다. 일단 이 로그 레코드가 디스크의 로그에 기록된 후에는 B가 새값으로 고쳐진 주기억 장치의 버퍼가 언제든지 디스크의 데이터베이스에 기록될 수 있다.

  트랜잭션의 완료점(Commit Point)

• 한 트랜잭션의 데이터베이스 갱신 연산이 모두 끝나고 데이터베이스 갱신사항이 로그에 기록된 시점
• DBMS의 회복 모듈은 로그를 검사하여 로그에 [Trans-ID, start] 로그 레코드와 [Trans-ID, commit] 로그 레코드가 모두 존재하는 트랜잭션들은 재수행(REDO)한다. (all 보장)
• [Trans-ID, start] 로그 레코드는 로그에 존재하지만 [Trans-ID, commit] 로그 레코드가 존재하지 않는 트랜잭션들은 취소(UNDO)한다. (nothing 보장)
• 재수행 과정

1) 로그 버퍼에서 디스크(로그 파일)로 저장했던 로그 레코드를 회복 모듈이 참조한다.

2) 디스크에서 DB버퍼로 읽어온 기존값을 새 값으로 갱신한다.

3) 이후 디스크에 다시 기록한다.

[ 위의 예시의 트랜잭션의 완료점에 따른 작업과 결과 ]

  로그 먼저 쓰기(Write-Ahead Logging, WAL)

• 트랜잭션이 데이터베이스를 갱신하면 주기억 장치의 데이터베이스 버퍼에 갱신 사항을 기록하고, 로그 버퍼에는 이에 대응되는 로그 레코드를 기록한다.
• 만일 데이터베이스 버퍼가 로그 버퍼보다 먼저 디스크에 기록되는 경우에는 로그 버퍼가 디스크에 기록되기 전에 시스템이 다운되었다가 재기동되었을 때 주기억 장치는 휘발성이므로 데이터베이스 버퍼와 로그 버퍼의 내용은 전혀 남아 있지 않는다.
• 로그 레코드가 없어서 이전값을 알 수 없으므로 트랜잭션의 취소가 불가능하다.
• 따라서 데이터베이스 버퍼보다 로그 버퍼를 먼저 디스크에 기록해야 한다.

  체크포인트(Checkpoint)

• 시스템이 다운된 시점으로부터 오래 전에 완료된 트랜잭션들이 데이터베이스를 갱신한 사항은 이미 디스크에 반영되었을 가능성이 크다.
• DBMS가 로그를 사용하더라도 어떤 트랜잭션의 갱신 사항이 주기억 장치 버퍼로부터 디스크에 기록되었는가를 구분할 수 없다.
• 따라서 DBMS는 회복시 재수행할 트랜잭션의 수를 줄이기 위해서 주기적으로 체크포인트를 수행한다.
• 체크포인트 시점에는 주기억 장치의 버퍼 내용이 디스크에 강제로 기록되므로, 체크포인트를 수행하면 디스크 상에서 로그와 데이터베이스의 내용의 일치하게 된다.
• 체크포인트 작업이 끝나면 로그에 [checkpoint] 로그 레코드가 기록된다.
• 일반적으로 체크포인트를 10~20분마다 한 번씩 수행한다.
•  체크포인트를 할 때 수행되는 작업

- 수행 중인 트랜잭션들을 일시적으로 중지시킨다. 회복 알고리즘에 따라서는 이 작업이 필요하지 않을 수 있다.

- 주기억 장치의 로그 버퍼를 디스크에 강제로 출력한다.

- 주기억 장치의 데이터베이스 버퍼를 디스크에 강제로 출력한다.

- [checkpoint] 로그 레코드를 로그 버퍼에 기록한 후 디스크에 강제로 출력한다.

- 체크포인트 시점에 수행 중이던 트랜잭션 ID도 [checkpoint] 로그 레코드에 함께 기록된다.

- 일시적으로 중지된 트랜잭션의 수행을 재개한다.

• 체크포인트를 하지 않았을 때

트랜잭션 Ta는 무시, 트랜잭션 Tb, Tc는 재수행, Td는 취소

  데이터베이스 백업과 재해적 고장으로부터의 회복

• 아주 드물지만, 데이터베이스가 저장되어 있는 디스크의 헤드 등이 고장나서 데이터베이스를 읽을 수 없는 경우가 발생한다.
• 이런 경우에 데이터베이스를 회복하는 한 가지 방법은 주기적으로 자기 테이프에 전체 데이터베이스와 로그를 백업하고, 자기 테이프를 별도의 공간에 안전하게 보관하는 것이다.
• 사용자들에게 데이터베이스 사용을 계속 허용하면서, 지난 번 백업 이후에 갱신된 내용만 백업을 하는 점진적인 백업(incremental backup)이 바람직하다.

  Transact-SQL의 트랜잭션

• Transact-SQL : MSSQL 상에서의 SQL문으로, SQL2의 트랜잭션 구문을 지원한다.
• 사용자나 응용 프로그램은 트랜잭션 시작과 끝을 명시하여 트랜잭션을 제어한다.
• 즉, 트랜잭션의 범위를 조정할 수 있다.
• 한 트랜잭션은 임의의 SQL문으로 시작되거나, 트랜잭션의 시작을 표시하는 명시적인 키워드를 사용하여 시작된다.
• BEGIN TRANSACTION : 트랜잭션의 시작을 명시적으로 표시하기 위한 SQL문
• 한 트랜잭션의 끝을 표시하기 위해서 사용자는 COMMIT 또는 ROLLBACK문을 입력한다.
• 사용자가 정의한 트랜잭션 이름이 허용되는 것을 제외하면 COMMIT TRANSACTION문은 COMMIT WORK(데이터베이스에 갱신 사항을 완전하게 반영)와 동일한 기능을 가진다.
• Ex. 트랜잭션의 시작을 명시적인 키워드로 나타내지 않고, 두 개의 UPDATE문으로 이루어진 트랜잭션을 명시하였다. COMMIT 키워드를 사용하여 트랜잭션의 끝을 표시(하나의 트랜잭션으로 취급)하였다.

• 트랜잭션의 속성

- 만일 트랜잭션이 데이터베이스를 읽기만 한다면 트랜잭션이 읽기 전용임을 명시하여 DBMS가 동시성의 정도를 높일 수 있다.

- 만일 어떤 트랜잭션이 읽기 전용이라고 명시했으면 그 트랜잭션은 어떠한 갱신 작업도 수행할 수 없다. 아래와 같은 SQL문은 허용되지 않는다.

- 트랜잭션에 대해 SET TRANSACTION READ WRITE를 명시하면, SELECT, INSERT, DELETE, UPDATE문을 모두 수행할 수 있다.

  고립 수준

• SQL2에서 사용자가 동시성의 정도를 몇 가지로 구분하여 명시할 수 있다.
• 고립 수준 : 한 트랜잭션이 다른 트랜잭션과 고립되어야 하는 정도
• 고립 수준이 낮으면 동시성은 높아지지만 데이터의 정확성(일관성)은 떨어진다.
• 고립 수준이 높으면 데이터가 정확해지지만 동시성이 저하(동시에 수행될 수 없음)된다.
• 응용의 성격에 따라 허용 가능한 고립 수준을 선택함으로써 성능을 향상시킬 수 있다.
• 응용에서 명시한 고립 수준에 따라 DBMS가 사용하는 로킹 동작(로크를 얼마나 지속할 것인가)이 달라진다.
• 한 트랜잭션에 대해 명시한 고립 수준에 따라 그 트랜잭션이 읽을 수 있는 데이터에만 차이가 있다.
• 상용 DBMS에서 제공하는 몇 가지 고립 수준

- 가장 낮은 고립 수준

- 트랜잭션 내의 질의들이 공유 로크를 걸지 않고 데이터를 읽는다.

- 오손 데이터 읽기, 반복할 수 없는 읽기, 팬텀 문제 발생

- 갱신하려는 데이터에 대해서는 독점 로크를 걸고, 트랜잭션이 끝날 때까지 보유한다.

- 오손 데이터가 미미한 영향을 주는 경우(Ex. N이 큰 숫자일 때, N개의 정수들의 평균값 구하기)에 사용된다.

- 트랜잭션 내의 질의들이 읽으려는 데이터에 대해서 공유 로크를 걸고, 읽기가 끝나자마자 로크를 해제한다. (2단계 로킹 프로토콜을 지키지 않음)

- 따라서 동일한 데이터를 다시 읽기 위해 공유 로크를 다시 걸고 데이터를 읽으면, 이전에 읽은 값과 다른 값을 읽는 경우가 생길 수 있다. (반복할 수 없는 읽기, 팬텀 문제 발생)

- 갱신하려는 데이터에 대해서는 독점 로크를 걸고, 트랜잭션이 끝날 때까지 보유한다.

- 이 고립수준은 Transact-SQL의 디폴트 고립 수준 (MSSQL 기준)

- 질의에서 검색되는 데이터에 대해 공유 로크를 걸고, 트랜잭션이 끝날 때까지 보유한다.

- 한 트랜잭션 내에서 동일한 질의를 두 번 이상 수행할 때 매번 같은 값을 포함한 결과를 검색하게 된다.

- 갱신하려는 데이터에 대해서는 독점 로크를 걸고, 트랜잭션이 끝날 때까지 보유한다.

- 레코드 수준의 로킹

- 2단계 로킹 프로토콜은 지키지만, 팬텀 문제를 해결하지 못한다.

- 가장 높은 고립 수준

- 질의에서 검색되는 투플들 뿐만 아니라 인덱스에 대해서도 공유 로크를 걸고 트랜잭션이 끝날 때까지 보유한다.

- 갱신하려는 데이터에 대해서는 독점 로크를 걸고 트랜잭션이 끝날 때까지 보유한다.

- SQL2의 디폴트 고립 수준

- 데이터베이스의 일관성을 높이지만, 동시성이 저하된다.

  뷰와 시스템 카탈로그

• 관계 데이터베이스 시스템의 뷰(View)는 다른 릴레이션으로부터 유도된 릴레이션(Derived Relation)으로서 ANSI/SPARC 3단계 아키텍처의 외부 뷰와는 다르다.
• 뷰의 사용목적

- 관계 데이터베이스 시스템에서 데이터베이스의 보안 메커니즘

- 복잡한 질의를 간단하게 표현하는 수단

- 데이터독립성을 높이기 위한 수단

• 시스템 카탈로그는 시스템 내의 객체(기본 릴레이션, 뷰, 인덱스, 사용자, 접근 권한 등)에 관한 정보를 포함한다.
• 시스템 카탈로그를 적절히 활용하면 원하는 릴레이션을 데이터베이스에서 찾고, 그 릴레이션에 어떤 애트리뷰트들이 들어 있으며, 각 애트리뷰트의 데이터 타입은 무엇인가 등을 쉽게 파악할 수 있다.

  뷰의 개요

• ANSI/SPARC 3단계 아키텍처에서 외부 뷰 : 특정 사용자가 보는 데이터베이스의 구조
• 관계 데이터베이스에서의 뷰 : 한 사용자의 전체 외부 뷰 대신에 하나의 가상 릴레이션(Virtual Relation)을 의미
• 뷰는 기존의 기본(실제) 릴레이션(Base Relation)에 대한 SELECT문의 형태로 정의된다.
• 사용자는 여러 개의 릴레이션과 뷰를 사용할 수 있다.
• 뷰는 릴레이션으로부터 데이터를 검색하거나 갱신할 수 있는 동적인 창(Dynamic Window)
• 뷰는 실제 릴레이션이 아니므로, 레코드를 가지지 않는다.

  스냅샷(Snapshot)

• 어느 시점에 SELECT문의 결과를 기본 릴레이션의 형태로 저장해 놓은 것
• 스냅샷은 사진을 찍은 것과 같아서 스냅샷을 정의하는 시점의 기본 릴레이션의 내용이 스냅샷에 반영된다.
• 어떤 시점의 조직체의 현황 (Ex. 몇 년 몇월 시점에 근무하던 사원들의 정보, 재고 정보 등이 스냅샷으로 정의될 수 있다.)
• 정적인 창(Static Window)

  뷰의 정의

• 뷰를 정의하는 SQL문

• 뷰의 이름 다음에 애트리뷰트들을 생략하면 뷰를 정의하는데 사용된 SELECT문의 SELECT절에 열거된 애트리뷰트들의 이름과 동일한 애트리뷰트들이 뷰에 포함된다.
• 아래의 조건에 해당될 경우, 뷰를 정의할 때 모든 애트리뷰트들의 이름을 지정해야 한다.

- 뷰를 정의하는 SELECT절에 산술식 또는 집단 함수에 사용된 애트리뷰트가 있는 경우

- 뷰의 정의에 조인이 포함되어 있고 두 개 이상의 다른 릴레이션으로부터 가져온 애트리뷰트들의 이름이 같아서 뷰에서 두 개 이상의 애트리뷰트의 이름이 같게 되는 경우

• Ex. EMPLOYEE 릴레이션에 대해서 “3번 부서에 근무하는 사원들의 사원번호, 사원이름, 직책으로 이루어진 뷰”를 정의

- 아래의 뷰의 정의에는 뷰의 애트리뷰트들을 별도로 명시했기 때문에 뷰에는 ENO, ENAME, TITLE의 세 애트리뷰트가 포함된다.

• Ex. EMPLOYEE와 DEPARTMENT 릴레이션에 대해서 “기획부에 근무하는 사원들의 이름, 직책, 급여로 이루어진 뷰”를 정의

  뷰를 사용하여 데이터를 접근할 때 관계 DBMS에서 거치는 과정

• 시스템 카탈로그로부터 뷰의 정의에서 사용된 SELECT 문을 검색한다.
• 기본 릴레이션에 대한 뷰의 접근 권한을 검사한다.
• 뷰에 대한 질의를 기본(실제) 릴레이션에 대한 동등한 질의로 변환한다.
• 뷰를 정의할 때 사용한 SELECT문이 시스템 카탈로그에 저장되어 있어 뷰를 참조한 질의가 가능
• 뷰를 정의할 때 사용한 WHERE절 조건 + 뷰를 참조한 질의에서의 WHERE절 조건을 모두 만족해야 한다.

  뷰의 장점

• 뷰는 복잡한 질의를 간단하게 표현할 수 있게 한다.

- 기획부에 근무하는 사원들 중에서 직책이 부장인 사원의 사원이름과 급여를 검색하는 질의를 기본 릴레이션을 사용하여 표현하면 아래와 같이 다소 복잡한 형태의 질의가 된다.

- 뷰(위에서 정의한 EMP_PLANNING사용)에 대해서 같은 결과를 검색하는 질의를 표현하면,

• 뷰는 데이터 무결성을 보장하는데 활용된다.

- 기본적으로 뷰를 통해 투플을 추가하거나 수정할 때 투플이 뷰를 정의하는 SELECT 문의 WHERE절의 기준에 맞지 않으면 뷰의 내용에서 사라진다. (즉, 투플은 삽입 되고, 뷰의 조건까지 맞는지 확인함)

- 이 뷰를 정의할 때 WITH CHECK OPTION을 명시했다고 가정한다.

- WITH CHECK OPTION : 뷰를 통해서 갱신한 결과를 뷰를 통해 보지 못하는 경우, 볼 수 있게 해준다.

• 뷰는 데이터 독립성을 제공한다.

- 뷰는 데이터베이스의 구조가 바뀌어도 기존의 질의(응용 프로그램)를 다시 작성할 필요성을 줄여준다.

- 응용의 요구사항이 변경되어 기존의 EMPLOYEE 릴레이션이 아래의 두 릴레이션으로 분해되었다고 가정하자. 응용 프로그램에서 기존의 EMPLOYEE 릴레이션을 접근하던 SELECT문은 더 이상 수행되지 않으므로, EMP1과 EMP2에 대한 SELECT문으로 변경해야 한다. (질의가 많을수록 시간이 많이 걸린다.)

EMP1(EMPNO, EMPNAME, SALARY),

EMP2(EMPNO, TITLE, MANAGER, DNO)

- 그러나 아래와 같이 EMPLOYEE 라는 뷰를 정의했다면 응용 프로그램에서 EMPLOYEE 릴레이션을 접근하던 SELECT문(기존의 질의)은 계속해서 수행될 수 있다.

• 뷰는 데이터 보안 기능을 제공한다.

- 뷰는 뷰의 원본이 되는 기본 릴레이션에 직접 접근할 수 있는 권한을 부여하지 않고 뷰를 통해 데이터를 접근하도록 하기 때문에 보안 메커니즘으로 사용할 수 있다.

- 뷰는 일반적으로 기본 릴레이션의 일부 애트리뷰트들 또는 일부 투플들을 검색하는 SELECT문으로 정의되므로 뷰를 통해서 기본 릴레이션을 접근하면 기본 릴레이션의 일부만 검색할 수 있다.

- Ex. EMPLOYEE 릴레이션의 SALARY 애트리뷰트는 숨기고 나머지 애트리뷰트들은 모든 사용자가 접근할 수 있도록 하려면 SALARY 애트리뷰트를 제외한 EMPLOYEE 릴레이션의 모든 애트리뷰트를 포함하는 뷰를 정의하고, 사용자에게 뷰에 대한 SELECT 권한을 허가하면 된다.

• 동일한 데이터에 대한 여러 가지 뷰를 제공한다.

- 뷰는 사용자들의 그룹이 각자 특정한 기준에 따라 데이터를 접근하도록 한다.

  뷰의 갱신

• 뷰에 대한 갱신도 기본 릴레이션에 대한 갱신으로 변환된다.
• 갱신이 불가능한 뷰

- 한 릴레이션 위에서 정의되었으나 그 릴레이션의 기본 키가 포함되지 않은 뷰 (기본키!=널값)

- 기본 릴레이션의 애트리뷰트들 중에서 뷰에 포함되지 않은 애트리뷰트에 대해 NOT NULL이 지정되어 있을 때 (갱신 시 널값 허용X)

- 집단 함수가 포함된 뷰

- 조인으로 정의된 뷰

  시스템 카탈로그

• 데이터베이스의 객체(사용자, 릴레이션, 뷰, 인덱스, 권한 등)와 구조들에 관한 모든 데이터를 포함한다.
• 시스템 카탈로그를 메타데이터(metadata, 데이터에 관한 데이터)라고 한다.
• 시스템 카탈로그는 사용자 및 질의 최적화 모듈 등 DBMS 자신의 구성요소에 의해서 사용된다.
• 시스템 카탈로그는 관계 DBMS마다 표준화되어 있지 않아서 관계 DBMS마다 서로 다른 형태로 시스템 카탈로그 기능을 제공한다.
• 데이터 사전(data dictionary) 또는 시스템 테이블(system table)이라고도 부른다.

  시스템 카탈로그가 질의 처리에 활용되는 방식

• SELECT 문이 문법적으로 정확한가를 검사한다.
• SELECT 문에서 참조하는 릴레이션이 데이터베이스에 존재하는가를 검사한다.
• SELECT 절에 열거된 애트리뷰트와 WHERE 절에서 조건에 사용된 애트리뷰트가 해당 릴레이션에 존재하는가를 검사한다.

• 위의 질의에서, SALARY 애트리뷰트가 수식에 사용되었으므로 이 애트리뷰트의 데이터 타입이 숫자형(정수형이나 실수형)인가를 검사하고, TITLE이 문자열과 비교되었으므로 이 애트리뷰트의 데이터 타입이 문자형(CHAR(n) 또는 VARCHAR(n) 등)인지 등을 검사한다.
• 만약 질의에서 DNO = ‘2’ 라는 조건이 WHERE절에 있다면, 위의 데이터 타입을 검사하는 단계에서 걸러진다.
• 위 질의를 입력한 사용자가 EMPLOYEE 릴레이션의 EMPNAME, SALARY 애트리뷰트를 검색할 수 있는 권한이 있는가를 확인한다.
• WHERE 절 조건에 사용된 애트리뷰트(TITLE 애트리뷰트와 DNO 애트리뷰트)에 인덱스가 정의되어 있는지 확인한다.
• 두 애트리뷰트에 각각 인덱스가 존재한다고 가정하자. DBMS가 두 인덱스 중에서 선별력 있는 것(검색되는 투플 수가 적은 것)을 선택하기 위해서는 관계 데이터베이스 시스템에 데이터베이스 외에 추가로 정보를 유지해야 한다.
• 한 릴레이션의 전체 투플 수와 그 릴레이션에 정의된 각 인덱스에 존재하는 상이한 값들의 개수를 유지한다면 어느 인덱스를 사용하는 것이 유리한가를 예상할 수 있다.

- EMPLOYEE 릴레이션의 전체 투플 수가 7일 때, 직책(TITLE) 애트리뷰트에는 사원, 대리, 과장, 부장, 사장의 다섯 가지 값들이 존재한다. 부서번호(DNO) 애트리뷰트에는 1, 2, 3의 세 가지 값들이 존재한다.

- 전체 레코드 수/애트리뷰트에 속한 값의 개수의 확률이 낮을수록 검색 시 디스크 블록에서부터 가져오는 레코드 수가 적으므로 성능 상으로 유리하다.

- 따라서 TITLE 애트리뷰트에 정의된 인덱스가 DNO에 정의된 인덱스보다 대상 투플들을 더 좁혀 주므로 선별력이 높다.

  질의 최적화

• DBMS가 질의를 수행하는 여러 가지 방법들 중에서 가장 비용이 적게 드는 방법을 찾는 과정
• 질의 최적화 모듈이 정확한 결정을 내릴 수 있도록 DBMS는 자체 목적을 위해서 시스템 카탈로그에 다양한 정보를 유지한다.
• 릴레이션을 생성할 때 시스템 카탈로그에서 기능별로 필요한 정보를 세분화하여 저장한다.
• 질의 최적화 모듈이 정확한 수행 방법을 결정하기 위해서는 릴레이션에 관한 다양한 통계 정보가 정확하게 유지되어야 한다.

  관계 DBMS의 시스템 카탈로그

• 사용자 릴레이션과 마찬가지 형태로 저장되기 때문에 사용자 릴레이션에 적용되는 회복 기법과 동시성 제어 기법을 동일하게 사용할 수 있다.
• 시스템 카탈로그는 사용자 릴레이션처럼 SELECT 문을 사용하여 내용을 검색할 수 있다.
• 시스템 카탈로그에는 릴레이션, 애트리뷰트, 인덱스, 사용자, 권한 등 각 유형마다 별도의 릴레이션이 유지된다.
• 릴레이션에 관한 정보를 유지하는 릴레이션의 이름이 SYS_RELATION, 애트리뷰트에 관한 정보를 유지하는 릴레이션의 이름이 SYS_ATTRIBUTE라고 가정하면, 아래와 같은 릴레이션들이 시스템 카탈로그에 존재한다.

  시스템 카탈로그의 갱신

• 어떤 사용자도 시스템 카탈로그를 직접 갱신할 수 없다.
• DELETE, UPDATE, INSERT문을 사용하여 시스템 카탈로그를 변경할 수 없다.
• EMPLOYEE 릴레이션의 소유자인 KIM이 EMPLOYEE 릴레이션에서 MANAGER 애트리뷰트를 삭제하기 위해 시스템 카탈로그에 대해 DELETE 문을 사용하면 DBMS가 거절한다.
  

• 데이터 정의어(DDL)을 통해서만 시스템 카탈로그를 갱신할 수 있다.

ALTER TABLE EMPLOYEE DROP COLUMN MANAGER;

  시스템 카탈로그에 유지되는 통계 정보

• 릴레이션마다 투플의 크기, 투플 수, 각 블록의 채우기 비율, 블록킹 인수(블록당 삽입가능한 레코드 수), 릴레이션의 크기(블록 수) 정보를 유지한다.
• 뷰마다 뷰의 이름과 정의에 대한 정보를 유지한다.
• 애트리뷰트마다 애트리뷰트의 데이터 타입, 크기, 애트리뷰트 내의 상이한 값들의 수, 애트리뷰트 값의 범위, 선택율(조건을 만족하는 투플 수/전체 투플 수)에 대한 정보를 유지한다.
• 사용자마다 접근할 수 있는 릴레이션과 권한에 대한 정보를 유지한다.
• 인덱스마다 인덱스된 애트리뷰트, 클러스터링 인덱스/비 클러스터링 인덱스의 여부, 밀집/희소 인덱스의 여부, 인덱스의 높이, 1단계 인덱스의 블록 수에 대한 정보를 유지한다.

  MS SQL Server의 시스템 카탈로그

• 통계 정보는 UPDATE STATISTICS문을 사용하여 수동으로 갱신할 수 있다.
• SQL Server는 릴레이션의 데이터가 변경될 때 주기적으로 통계 정보를 자동으로 갱신한다.
• 통계 정보를 갱신할 필요성이 있는가를 확인하기 위해서 샘플링 방법을 사용한다.
• 통계 정보가 갱신되는 빈도는 애트리뷰트 또는 인덱스의 크기와 변경되는 데이터의 양에 따라 결정된다.
• 모든 릴레이션들에 대한 구성을 정의하는 데이터를 시스템 테이블(System Table)이라고 부르는 특수한 테이블 집합에 저장한다.
• 사용자나 응용 프로그램이 정보 스키마 뷰, Transact-SQL문 및 함수, 시스템 저장 프로시저 등을 사용하여 시스템 테이블에 저장된 정보를 검색한다.

[ 각 데이터베이스에 있는 주요 시스템 테이블들의 이름과 기능 ]

• 시스템 테이블을 사용하여 사용자 KIM이 소유한 개체들을 찾기 위해서 SQL Server 관리 스튜디오의 편집기 창에서 다음과 같은 질의를 수행한다.
  

- OWNER : 개체를 소유한 사용자의 식별자

- NAME : 개체의 이름

- xtype은 개체의 유형으로서, 아래 항목들 중 하나를 반환한다.

C : CHECK 제약 조건

D : 디폴트값 제약 조건

F : 외래 키 제약 조건

PK : 기본 키 제약 조건

U : 사용자 릴레이션

UQ : UNIQUE 제약조건

V : 뷰

• INFORMATION_SCHEMA를 사용하여 사용자 KIM이 소유한 릴레이션이나 뷰에 관한 정보를 검색하기 위해서 다음과 같은 질의를 수행한다.

• INFORMATION_SCHEMA의 컬럼

- TABLE_CATALOG : 데이터베이스의 이름

- TABLE_SCHEMA : 릴레이션이나 뷰를 소유한 사용자의 식별자

- TABLE_NAME : 릴레이션이나 뷰의 이름

- TABLE_TYPE : 릴레이션의 유형 (BASE TABLE or VIEW 가 될 수 있다.)

• INFORMATION_SCHEMA를 사용하여 사용자 KIM이 소유한 EMPLOYEE 릴레이션의 애트리뷰트 정보를 찾기 위해서 다음과 같은 질의를 수행한다.

- COLUMN_NAME : 애트리뷰트의 이름

- ORDINAL_POSITION : 애트리뷰트의 번호(ID)

- DATA_TYPE : 데이터 타입

- COLUMN_DEFAULT : 애트리뷰트의 디폴트 값

• 뷰가 어떤 select문으로 정의되어 있는지 알기 위해서 아래와 같은 시스템 저장 프로시저를 수행한다.

• 특정 릴레이션이 사용하는 디스크 공간 정보를 알아보기 위해서 아래와 같은 시스템 저장 프로시저를 수행한다.

- name : 릴레이션의 이름

- rows : 투플의 수

- reserved : 예약된 총 공간

- data : 실제로 사용되는 공간

- index_size : 인덱스가 사용하는 공간

- unused : 사용되지 않은 공간

• EMPLOYEE 릴레이션의 부서 번호 애트리뷰트인 DNO에 대해 인덱스를 생성하고, 생성된 인덱스를 통해서 통계 정보를 확인하는 질의

• 통계 정보는 레코드를 가져올 때의 선별력과 연관된다.
• 릴레이션에 정의된 인덱스에 관한 정보를 검색하려면 아래의 질의를 실행

- index_name : 인덱스의 이름

- index_description : 인덱스에 관한 설명

- index_keys : 인덱스가 정의된 애트리뷰트

- 기본 키는 SQL Server가 자동적으로 클러스터링 인덱스를 생성한다.

- UNIQUE 키워드를 사용한 애트리뷰트는 SQL Server가 비 클러스터링이면서 고유한 인덱스(보조 인덱스 - 밀집 인덱스)를 자동적으로 생성한다.

- 외래 키는 CREATE INDEX문을 사용하여 사용자가 명시적으로 인덱스를 정의해야 한다.

  릴레이션 정규화

• 부주의한 데이터베이스 설계는 제어할 수 없는 데이터 중복을 야기하여 여러 가지 갱신 이상(update anomaly)을 유발한다.
• 정규화(Normalization)는 주어진 릴레이션 스키마를 함수적 종속성과 기본 키를 기반으로 분석하여, 원래의 릴레이션을 분해함으로써 중복과 세 가지 갱신(삽입/삭제/수정) 이상을 최소화한다.
• 어떻게 좋은 데이터베이스 설계를 할 것인가, 데이터베이스에 어떤 릴레이션들을 생성할 것인가, 각 릴레이션에 어떤 애트리뷰트들을 둘 것인가는 정규화의 핵심이다.
•  좋은 관계 데이터베이스 스키마를 설계하는 목적

- 정보의 중복과 갱신 이상이 생기지 않도록 하고, 정보의 손실을 막으며, 실세계를 훌륭하게 나타내고, 애트리뷰트들 간의 관계가 잘 표현되는 것을 보장하며, 어떤 무결성 제약조건의 시행을 간단하게 하며, 아울러 효율성 측면도 고려하는 것

- 먼저 갱신 이상이 발생하지 않도록 노력하고 그 다음에 효율성을 고려한다. 효율성을 고려하는 부분에서 부분적 역정규화가 필요할 수 있다.

  갱신 이상(Update Anomaly)

• 수정 이상(Modification Anomaly) : 반복된 데이터 중에 일부만 수정하면 데이터의 불일치가 발생
• 삽입 이상(Insertion Anomaly) : 불필요한 정보를 함께 저장하지 않고는 어떤 정보를 저장하는 것이 불가능

(b, c) 데이터만 삽입하고 싶으나 (a, b)가 함께 삽입되지 않으면 (b, c)를 삽입할 수 없는 경우

• 삭제 이상(Deletion Anomaly) : 유용한 정보를 함께 삭제하지 않고는 어떤 정보를 삭제하는 것이 불가능

(b, c)만 삭제하고 싶으나 (a, b)가 함께 삭제되지 않으면 삭제할 수 없는 경우

위와 같은 구조와 내용을 갖는 사원 릴레이션으로부터 설계를 시작한다고 가정하자. 

이 회사에서는 각 사원이 두 개까지의 부서에 속할 수 있다. 

즉, 각 사원마다 부서 수를 제한하게 된다.

예시 1에서 봤던 릴레이션을 위의 구조로 바꾼다면, 각 사원마다 부서 수를 제한하게 되는 문제점을 해결할 수 있다.

그러나 아래와 같은 문제점이 발생하게 된다.

- 정보의 중복 : 각 사원이 속한 부서 수만큼 동일한 사원의 투플들이 존재하므로 사원번호, 사원이름, 주소, 전화번호 등이 중복되어 저장 공간이 낭비된다.

- 수정 이상 : 만일 어떤 부서의 이름이 바뀔 때 이 부서에 근무하는 일부 사원 투플에서만 부서이름을 변경하면 (부서이름을 한 레코드에서만 변경하면) 다른 사원들과는 부서번호는 동일하나 부서이름이 다른, 데이터베이스 불일치 상태가 된다.

- 삽입 이상 : 만일 어떤 부서를 신설했는데 아직 사원을 한 명도 배정하지 않았다면, 사원번호가 기본 키인데 널 값이 들어가기 때문에 이 부서에 관한 정보를 입력할 수 없다.

- 삭제 이상 : 만일 어떤 부서에 속한 사원이 단 한 명 있는데, 이 사원에 관한 투플을 삭제하면 이 사원이 속한 부서에 관한 정보도 릴레이션에서 삭제된다.

결론적으로, 서로 독립적인 개체를 하나의 릴레이션에 두면 안된다.

  릴레이션 분해

• 하나의 릴레이션을 두 개 이상의 릴레이션으로 나누는 것
• 릴레이션의 분해는 필요한 경우에는 분해된 릴레이션들로부터 원래의 릴레이션을 다시 구할 수 있음(Ex. 조인 연산)을 보장해야 한다.
• 분해를 잘못하면 두 릴레이션들로부터 얻을 수 있는 정보가 원래의 릴레이션이 나타내던 정보보다 적을 수도 있고 많을 수도 있다. (많은 경우: 가짜 투플이 생긴 경우)
• 릴레이션의 분해는 릴레이션에 존재하는 함수적 종속성에 관한 지식을 기반으로 한다.
• Ex. 위의 나쁜 설계의 예시를 분해해보자.

- 부서 이름의 수정 : 어떤 부서에 근무하는 사원이 여러 명 있더라도 사원 릴레이션에는 부서 이름이 포함되어 있지 않으므로 수정 이상이 나타나지 않는다.

- 새로운 부서를 삽입 : 만일 어떤 신설 부서에 사원이 한 명도 배정되지 않았더라도, 부서 릴레이션의 기본 키가 부서번호이므로 이 부서에 관한 정보를 부서 릴레이션에 삽입할 수 있다.

- 마지막 사원 투플을 삭제 : 만일 어느 부서에 속한 유일한 사원에 관한 투플을 삭제하더라도 이 부서에 관한 정보는 부서 릴레이션에 남아 있다.

  정규형(Normal Form)의 종류

• 제 1정규형(First Normal Form)
• 제 2정규형(Second Normal Form)
• 제 3정규형(Third Normal Form)
• BCNF(Boyce-Codd Normal Form)
• 제 4정규형(Fourth Normal Form)
• 제 5정규형(Fifth Normal Form)
• 일반적으로 산업계의 데이터베이스 응용에서 데이터베이스를 설계할 때 BCNF 까지 고려한다.

  관계 데이터베이스 설계의 비공식적인 지침

• 이해하기 쉽고 명확한 스키마 만들기 : 여러 엔티티 타입이나 관계 타입에 속한 애트리뷰트들을 하나의 릴레이션에 포함시키지 않는다.

• 널값 피하기
• 가짜 투플 생기지 않게 하기 (릴레이션 분해[정규화] 전의 릴레이션(기존)의 투플보다 많은 경우)
• 스키마 정제하기

  함수적 종속성의 개요

• 함수적 종속성은 정규화 이론의 핵심
• 릴레이션의 애트리뷰트들의 의미로부터 결정된다.
• 릴레이션 스키마에 대한 적용이지, 릴레이션의 특정 인스턴스에 대한 적용이 아니다.
• 릴레이션의 가능한 모든 인스턴스들이 (어떤 레코드가 삽입되어도) 만족해야 한다.
• 실세계에 대한 지식과 응용의 의미를 기반으로 어떤 함수적 종속성들이 존재하는가를 파악해야 한다.
• 함수적 종속성은 제 2정규형부터 BCNF까지 적용된다.
• 결정자(Determinant)

- 어떤 애트리뷰트의 값은 다른 애트리뷰트의 값을 고유하게 결정할 수 있다.

- 결정자 : 주어진 릴레이션에서 다른 애트리뷰트(또는 애트리뷰트들의 집합)를 고유하게 결정하는 하나 이상의 애트리뷰트

- 기본 키 애트리뷰트는 결정자이다.

- 레코드를 고유하게 식별하지 못하는 애트리뷰트(Ex. 주소)는 다른 애트리뷰트(Ex. 사원이름)를 고유하게 결정하지 못한다.

- 결정자 표기법 : A → B (A가 B를 결정한다. 또는 A는 B의 결정자이다.)

- Ex. 결정자 찾기 예제

위 릴레이션에서는 아래와 같은 결정자들을 발견할 수 있다.

사원번호 → 사원이름

사원번호 → 주소

사원번호 → 전화번호

부서번호 → 부서이름

(사원번호, 부서번호) → 직책

• 함수적 종속성

- 애트리뷰트 A가 애트리뷰트 B의 결정자(A → B)이면, B가 A에 함수적으로 종속한다고 말한다.

- 주어진 릴레이션 R에서 애트리뷰트 B가 애트리뷰트 A에 함수적으로 종속하는 필요 충분 조건은 각 A 값에 대해 반드시 한 개의 B 값이 대응된다.

- Ex. 사원번호가 사원이름, 주소, 전화번호의 결정자이므로 사원이름, 주소, 전화번호는 사원번호에 종속한다. 그러나, 직책은 (사원번호, 부서번호)에 함수적으로 종속하지, 사원번호에 함수적으로 종속하지는 않는다.

• 완전 함수적 종속성(Full Functional Dependency, FFD)

- 주어진 릴레이션 R에서 애트리뷰트 B가 애트리뷰트 A에 함수적으로 종속하면서 애트리뷰트 A의 어떠한 진부분 집합에도 함수적으로 종속하지 않으면, 애트리뷰트 B가 애트리뷰트 A에 완전하게 함수적으로 종속한다고 말한다.

- 여기서 애트리뷰트 A는 복합 애트리뷰트

- Ex. A가 (C, D)로 이루어진 복합 애트리뷰트일 때, A → B = (C, D) → B 이다. 여기서 C → B 또는 D → B 가 아니라면, A → B는 완전 함수적 종속성이다.

• 이행적 함수적 종속성(Transitive Functional Dependency, TFD)

- 한 릴레이션의 애트리뷰트 A, B, C가 주어졌을 때 애트리뷰트 C가 이행적으로 A에 종속한다는 것의 필요 충분 조건

- A가 릴레이션의 기본 키라면 키의 정의에 따라 A→B와 A→C가 성립한다. 만일 C가 A외에 B에도 함수적으로 종속(B→C)한다면 C는 A에 직접 함수적으로 종속하면서 B를 거쳐서 A에 이행적으로 종속한다고 표현한다.

  릴레이션 분해의 특징

• 릴레이션을 분해하면 중복이 감소되고 갱신 이상이 줄어드는 장점이 있다.
• 바람직하지 않은 문제들을 포함하여 몇 가지 잠재적인 문제들을 야기할 수 있다.

- 릴레이션이 분해되기 전에는 조인이 필요 없는 질의가 분해 후에는 조인을 필요로 하는 질의로 바뀔 수 있다.

- 분해된 릴레이션들을 사용하여 원래 릴레이션을 재구성하지 못할 수 있다.

• 릴레이션 분해 시, 기본 키와 외래 키의 관계를 잘 알고 분해해야 한다.
• 분해 후 릴레이션들이 조인을 많이 요구한다면, 이는 분해할 필요가 없는 릴레이션을 분해한 경우(불필요한 분해)이다.
• 완전 함수적 종속성을 만족하는 경우, 두 애트리뷰트(결정자)가 각각 기본 키가 되는 릴레이션으로 분해하는 것은 나쁜 분해이다. (조인하면, 가짜 투플이 생성됨)

  무손실 분해(Lossless Decomposition)

• 분해된 두 릴레이션을 조인했을 때, 원래의 릴레이션에 들어 있는 정보를 완전하게 얻을 수 있는 분해
• 여기서 손실이란 정보의 손실을 의미한다.
• 정보의 손실은 원래의 릴레이션을 분해한 후에 생성된 릴레이션을 조인한 결과에 들어 있는 정보가 원래의 릴레이션에 들어 있는 정보보다 적거나 많은 경우(불일치)를 모두 포함한다.
• 정보의 손실이 발생한 경우 어떤 정보가 줄어들었는지, 늘어났는지 알 수 없다.

  제 1정규형(First Normal Form)

• 한 릴레이션 R이 제 1정규형을 만족할 필요 충분 조건은 릴레이션 R의 모든 애트리뷰트가 단일값을 갖는 것이다.
• 릴레이션의 모든 애트리뷰트에 반복 그룹(Repeating Group), 즉 다치 애트리뷰트가 없으면 제 1정규형을 만족한다.
• 제 1정규형으로 변환하는 방법

- 다치 애트리뷰트에 나타나는 집합에 속한 각 값마다 하나의 투플로 표현한다. (중복 발생)

- 다치 애트리뷰트에 나타나는 값들을 분리해서 새로운 릴레이션에 삽입한다. 원래 릴레이션의 기본 키를 새로운 릴레이션에 애트리뷰트로 추가한다. (두 애트리뷰트는 기본 키의 구성요소)

• 제 1정규형에 존재하는 갱신 이상

- 아래의 릴레이션은 모든 애트리뷰트가 단일값을 가지므로 제 1정규형을 만족한다. 이 릴레이션의 기본 키는 (학번, 과목번호)이다. 그러나 세 가지 갱신 이상이 존재한다.

- 수정 이상 : 한 학과에 소속한 학생 수만큼 그 학과의 전화번호가 중복되어 저장되므로 여러 학생이 소속된 학과의 전화번호가 변경되었을 때 그 학과에 속한 모든 학생들의 투플에서 전화번호를 수정하지 않으면 데이터베이스의 일관성이 유지되지 않는다.

- 삽입 이상 : 한 명의 학생이라도 어떤 학과에 소속되지 않으면(새로 신설된 학과 등의 경우) 이 학과에 관한 투플을 삽입할 수 없다. 학번이 기본 키의 구성요소인데 엔티티 무결성 제약조건에 따라 기본 키에 널값을 입력할 수 없기 때문이다.

- 삭제 이상 : 어떤 학과에 소속된 마지막 학생 투플을 삭제하면 이 학생이 소속된 학과에 관한 정보도 삭제된다.

• 제 1정규형에서 갱신 이상이 생기는 이유 : 기본 키에 대한 부분 함수적 종속성이 존재하므로 분해 시 부분 함수적 종속성을 제거해야 한다.
  

  제 2정규형(Second Normal Form)

• 한 릴레이션 R이 제 2정규형을 만족할 필요 충분 조건은 릴레이션 R이 제 1정규형을 만족하면서, 어떤 후보 키에도 속하지 않는 모든 애트리뷰트들이 R의 기본키에 완전하게 함수적으로 종속하는 것
• 기본 키가 두 개 이상의 애트리뷰트로 구성되었을 경우에만 제 1정규형이 제 2정규형을 만족하는가를 고려할 필요가 있다.
• 제 2 정규형에 존재하는 갱신 이상

- 아래의 릴레이션의 기본 키는 한 애트리뷰트인 학번이므로 제 2정규형을 만족한다. 그러나, 세 가지 갱신 이상이 존재한다.

- 수정 이상 : 여러 학생이 소속된 학과의 전화번호가 변경되었을 때 그 학과에 속한 모든 학생들의 투플에서 전화번호를 수정하지 않으면 데이터베이스의 일관성이 유지되지 않는다.

- 삽입 이상 : 어떤 학과를 신설해서 아직 소속 학생이 없으면(새로 신설된 학과 등의 경우) 그 학과의 정보를 입력할 수 없다. 학번이 기본 키의 구성요소인데 엔티티 무결성 제약조건에 따라 기본 키에 널값을 입력할 수 없기 때문이다.

- 삭제 이상 : 어떤 학과에서 마지막 학생의 투플이 삭제되면 그 학과의 정보도 함께 삭제된다.

• 제 2정규형에서 갱신 이상이 생기는 이유 : 학번 → 학과이름, 학번 → 학과전화번호인 상태에서 학과이름이 학과전화번호를 결정하기 때문에(학과이름 → 학과전화번호) 이행적 함수적 종속성이 존재한다. 따라서 릴레이션 분해 시 이행적 함수적 종속성을 제거해야 한다.

  제 3정규형(Third Normal Form)

• 한 릴레이션 R이 제 3정규형을 만족할 필요 충분 조건은 릴레이션 R이 제 2정규형을 만족하면서, 키가 아닌 모든 애트리뷰트가 릴레이션 R의 기본키에 이행적으로 종속하지 않는 것(직접 종속)
• 제 3정규형에 존재하는 갱신 이상

- 아래의 릴레이션에서 각 학생은 여러 과목을 수강할 수 있고, 각 강사는 한 과목만 가르친다. 이 릴레이션의 기본 키는 (학번, 과목)이다.

* 학번이나 과목 하나로는 강사 애트리뷰트를 결정하지 못한다. (직접 종속) *

- 키가 아닌 강사 애트리뷰트가 기본 키에 완전하게 함수적으로 종속하므로 제 2정규형을 만족하고, 키가 아닌 강사 애트리뷰트가 기본 키에 직접 종속하므로 제 3정규형도 만족한다.

- 단, 또 다른 함수적 종속성(강사 → 과목)이 존재한다.

- 수정 이상 : 여러 학생이 수강 중인 어떤 과목의 강사가 변경되었을 때 그 과목을 수강하는 모든 학생들의 투플에서 강사를 수정하지 않으면 데이터베이스의 일관성이 유지되지 않는다.

- 삽입 이상 : 어떤 과목을 신설하여 아직 수강하는 학생이 없으면 어떤 강사가 그 과목을 가르친다는 정보를 입력할 수 없다. 학번이 기본 키를 구성하는 애트리뷰트인데 엔티티 무결성 제약조건에 따라 기본 키를 구성하는 애트리뷰트에 널값을 입력할 수 없기 때문이다.

- 삭제 이상 : 어떤 과목을 이수하는 학생이 한 명밖에 없을 경우 이 학생의 투플을 삭제하면 그 과목을 가르치는 강사에 관한 정보도 함께 삭제된다. (실제 상황에서는 몇 명 이상일 경우 강의를 개설한다는 조건이 붙으나, 여기서는 그 조건이 없다고 가정한다.)

• 제 3정규형에서 갱신 이상이 생기는 이유 : 릴레이션에서 키가 아닌 애트리뷰트가 다른 애트리뷰트를 결정하기 때문이다. 위의 릴레이션의 후보키는 (학번, 과목)과 (학번, 강사)인데 강사 애트리뷰트가 과목 애트리뷰트를 결정하고 있다.
  

  BCNF

• 한 릴레이션 R이 BCNF를 만족할 필요 충분 조건은 릴레이션 R이 제 3정규형을 만족하고, 모든 결정자가 후보 키이어야 한다.
• 위의 예시처럼 수강 릴레이션에서 강사 애트리뷰트는 후보 키가 아님에도 불구하고 과목 애트리뷰트를 결정하기 때문에 BCNF가 아니다.
• 제 3정규형을 만족하는 대부분의 릴레이션들은 BCNF도 만족한다.
• 하나의 후보 키만을 가진 릴레이션이 제 3정규형을 만족하면 동시에 BCNF도 만족한다.
• 제 3정규형을 만족하는 릴레이션을 BCNF로 정규화하려면 키가 아니면서 결정자 역할을 하는 애트리뷰트와 그 결정자에 함수적으로 종속하는 애트리뷰트가 포함된 릴레이션을 하나 생성한다. 이 릴레이션에서 결정자는 기본 키가 된다.
• 이후 기존 릴레이션에 결정자를 남겨서 외래 키 역할과 동시에 기본 키의 구성요소가 되도록 한다.

[ 제 3 정규형의 릴레이션을 분해한 결과 ]

  정리

• 정규화 단계가 진행될수록 중복이 감소하고 갱신 이상도 감소된다.
• 정규화가 진전될수록 무결성 제약조건을 시행하기 위해 필요한 코드의 양도 감소된다.
• 정규화가 데이터베이스 설계의 중요한 요소이지만 성능상의 관점에서만 보면 높은 정규형을 만족하는 릴레이션 스키마가 항상 최적인 것은 아니다.
• 한 정규형에서 다음 정규형으로 진행될 때마다 하나의 릴레이션이 최소한 두 개의 릴레이션으로 분해된다.
• 분해되기 전의 릴레이션을 대상으로 질의를 할 때는 조인이 필요 없지만, 분해된 릴레이션을 대상으로 질의를 할 때는 같은 정보를 얻기 위해서 보다 많은 릴레이션들을 접근해야 하므로 조인의 필요성이 증가한다.

  역정규화(Denormalization)

• 데이터베이스 설계자는 응용의 요구 사항에 따라 데이터베이스 설계의 일부분을 역정규화함으로써, 데이터 중복 및 갱신 이상을 대가로 치르면서라도 성능상의 요구를 만족시킬 필요가 있다.
• 역정규화 : 주어진 응용에서 빈번하게 수행되는 검색 질의들의 수행 속도를 높이기 위해서 이미 분해된 두 개 이상의 릴레이션들을 합쳐서 하나의 릴레이션으로 만드는 작업
• 많은 데이터베이스 응용에서 검색 질의의 비율이 갱신 질의의 비율보다 훨씬 높다.
• 역정규화는 쉽게 말해 보다 낮은 정규형으로 되돌아가는 것이다.
• Ex. 제 3정규형을 만족하지 않더라도 발생하는 문제가 빈도 수가 적은 등의 감수할만한 상황인 경우 역정규화를 통해 제 2정규형만 만족하도록 한다.

  물리적 데이터베이스 설계

• 논리적인 설계의 데이터 구조를 보조 기억 장치상의 파일(물리적인 데이터 모델)로 사상한다.
• 하나의 파일에 여러 릴레이션이 저장될 수 있다.
• 예상 빈도를 포함하여 데이터베이스 질의와 트랜잭션들을 분석한다.
• 데이터에 대한 효율적인 접근을 제공하기 위하여 저장 구조와 접근 방법들을 다룬다.
• 특정 DBMS의 특성을 고려하여 진행된다.
• 질의를 효율적으로 지원하기 위해서 인덱스 구조를 적절히 사용한다.

  보조 기억 장치의 역할

• 사용자가 원하는 데이터를 검색하기 위해서 DBMS는 디스크 상의 데이터베이스로부터 사용자가 원하는 데이터를 포함하고 있는 디스크 블록(block)을 읽어서 주기억 장치(RAM)로 가져온다.
• 데이터가 변경된 경우에는 블록들을 디스크에 다시 기록한다.
• 블록 크기는 512바이트부터 수 킬로바이트까지 다양하다.
• 전형적인 블록 크기는 4,096바이트
• 각 파일은 고정된 크기의 블록들로 나누어져서 저장된다. 즉, 여러 개의 블록들에 파일의 데이터가 분산되어 저장된다.
• 디스크는 데이터베이스를 장기간 보관하는 주된 보조 기억 장치이다. (Ex. HDD)

  자기 디스크의 역할

• 디스크는 자기 물질로 만들어진 여러 개의 판으로 이루어진다.
• 각 면마다 디스크 헤드가 존재한다.
• 각 판은 트랙과 섹터로 구분된다.
• 정보는 디스크 표면 상의 동심원(트랙)을 따라 저장된다.
• 여러 개의 디스크 면 중에서 같은 지름을 갖는 트랙들을 “실린더”라고 부른다.
• 블록은 여러 개의 섹터들로 이루어진다.
• 디스크에서 임의의 블록을 읽어오거나 기록하는데 걸리는 시간 = 탐구 시간(seek time) + 회전 지연 시간(rotational delay) + 전송 시간(transfer time)

  버퍼 관리와 운영 체제

• 디스크 입출력은 컴퓨터 시스템에서 가장 속도가 느린 작업이므로 입출력 횟수를 줄이는 것이 DBMS의 성능을 향상하는데 매우 중요하다.
• 가능하면 많은 블록들을 주기억 장치에 유지하거나, 자주 참조되는 블록들을 주기억 장치에 유지하면 블록 전송 횟수(디스크->주기억 장치)를 줄일 수 있다.
• 버퍼 : 디스크 블록들을 저장하는데 사용되는 주기억 장치 공간
• 버퍼 관리자는 운영 체제의 구성요소로서 주기억 장치 내에서 버퍼 공간을 할당하고 관리한다.
• 운영 체제에서 버퍼 관리를 위해 흔히 사용되는 LRU(Least Recently Used) 알고리즘은 데이터베이스를 위해 항상 우수한 성능을 보이지는 않는다.

  디스크 상에서 파일의 레코드 배치

• 릴레이션의 애트리뷰트는 고정 길이 또는 가변 길이의 필드로 표현된다.
• 연관된 필드들이 모여서 고정 길이 또는 가변 길이의 레코드가 된다.
• 한 릴레이션을 구성하는 레코드들의 모임은 파일이라고 부르는 블록들의 모임에 저장된다.
• 한 파일에 속하는 블록들의 위치가 반드시 인접해 있을 필요는 없다. (분산되어 저장될 수 있다.)
• 인접한 블록들을 읽는 경우에는 탐구 시간과 회전 지연 시간이 들지 않기 때문에 입출력 속도가 빠르므로 블록들이 인접하도록 한 파일의 블록들을 재조직할 수 있다.
• BLOB(Binary Large Object) 타입

- 이미지(GIF, JPG), 동영상(MPEG, AVI) 등 대규모 크기의 데이터를 저장하는데 사용된다.

- BLOB의 최대 크기는 MS SQL Server에서 2GB까지도 가능하다.

- DBMS 마다 제공하는 크기가 다르다.

• 채우기 인수

- 각 블록에 레코드를 채우는 공간의 비율

- 채우기 인수 = 블록 1개의 크기 / 레코드 1개의 크기

- 나중에 레코드가 삽입될 때 기존의 레코드들이 이동하는 가능성을 줄이기 위해서 한 블록에 레코드를 가득 채우지 않고 빈 공간을 남겨둔다.

• 고정 길이 레코드

- i번째 레코드를 접근하기 위해서 (레코드 길이)*(i-1)+1 의 위치에서 레코드를 읽는다.

- 레코드를 삭제하여 여러 개의 레코드가 이동할 때는 (삭제한 레코드 개수)*(레코드 길이) 바이트 만큼 이동한다.

- 한 개의 레코드를 삭제하면 고정길이 바이트만큼 이동한다.

• 파일 내의 클러스터링(intra-file clustering)

- 한 파일 내에서 함께 검색될 가능성이 높은 레코드들을 디스크 상에서 물리적으로 가까운 곳에 모아두는 것

- 논리적으로 연관되어 함께 검색될 가능성이 높은 두 개 이상의 파일에 속한 레코드들을 디스크 상에서 물리적으로 가까운 곳에 저장하는 것 (두 릴레이션에서 연관된 투플들이 같은 블록에 저장된다.)

• 파일 조직의 유형

- 히프 파일(heap file)

- 순차 파일(sequential file)

- 인덱스된 순차 파일(indexed sequential file)

- 직접 파일(hash file) : DBMS가 내부적으로 사용자가 정의한 SQL을 빠르게 처리하고자 사용

  히프 파일(비순서 파일)

• 가장 단순한 파일 조직
• 일반적으로 레코드들이 삽입된 순서대로 파일에 저장된다.
• 삽입이 쉬우며 레코드들의 순서는 없다.
• 삽입 : 새로 삽입되는 레코드는 파일의 가장 끝에 첨부된다.
• 검색 : 원하는 레코드를 찾기 위해서는 모든 레코드들을 순차적으로 접근해야 한다.
• 삭제 : 원하는 레코드를 찾은 후에 그 레코드를 삭제하고, 삭제된 레코드가 차지하던 공간을 재사용하지 않는다.
• 빈 공간을 재사용하려면 레코드 삽입 시(또는 검색의 경우) 파일의 끝 대신에 파일 중간에서 빈 자리를 찾아야 하므로 레코드 삽입 시간이 증가한다.
• 좋은 성능을 유지하기 위해서 히프 파일을 주기적으로 재조직(빈 공간들을 회수하여 자유 공간에 반환)할 필요가 있다.
• 릴레이션에 데이터를 한꺼번에 적재할 때(bulk loading), 릴레이션에 단지 몇 개의 블록들만 있을 때, 모든 투플들이 검색 위주로 사용될 때 히프 파일이 주로 사용된다.

[히프 파일에 저장된 레코드의 모습]

• 히프 파일의 성능

- 히프 파일은 질의에서 모든 레코드들을 참조하고, 레코드들을 접근하는 순서는 중요하지 않을 때, 효율적이다.

SELECT * FROM EMPLOYEE;

- 특정 레코드를 검색하는 경우에는 히프 파일이 비효율적이다. 히프 파일에 b개의 블록이 있다면, 원하는 블록을 찾기 위해 평균적으로 b/2개의 블록을 읽어야 한다.

SELECT TITLE FROM EMPLOYEE WHERE EMPNO=1365;

- 몇 개의 레코드들을 검색하는 경우에도 비효율적이다. 조건에 맞는 레코드를 이미 한 개 이상 검색했더라도 파일의 마지막 블록까지 읽어서 원하는 레코드가 존재하는지 확인해야하기 때문이다.

SELECT EMPNAME, TITLE FROM EMPLOYEE WHERE DNO=2;

- 급여의 범위를 만족하는 레코드들을 모두 검색하는 아래의 질의도 EMPLOYEE 릴레이션의 모든 레코드들을 접근해야 한다.

SELECT EMPNAME, TITLE FROM EMPLOYEE

WHERE SALARY >= 3000000 AND SALARY <= 4000000;

[ 히프 파일에서 연산의 유형별 소요 시간 ]

  순차 파일

• 레코드들이 하나 이상의 필드 값에 따라 순서대로 저장된 파일 (필드별로 정렬되어 저장된다.)
• 레코드들이 일반적으로 레코드의 탐색 키(search key) 값의 순서에 따라 저장된다.
• 탐색 키는 순차 파일을 정렬하는데 사용되는 필드로, 고유하게 식별하는 기본 키와는 다르다.
• 삽입 : 삽입하려는 레코드의 순서를 고려해야 하기 때문에 시간이 많이 걸릴 수 있다.
• 삭제 : 삭제된 레코드가 사용하던 공간을 빈 공간으로 남기기 때문에 히프 파일의 경우와 마찬가지로 주기적으로 순차 파일을 재조직해야 한다.
• 기본 인덱스가 순차 파일에 정의되지 않는 한 순차 파일은 데이터베이스 응용을 위해 거의 사용되지 않는다.
• 필드별로 정렬되어 있으므로 이진 탐색이 가능하다.

[ 순차 파일에 저장된 릴레이션 - 기본 키를 탐색 키로 사용함 ]

• 순차 파일의 성능

- EMPLOYEE 릴레이션이 순차파일에 EMPNO의 순서대로 저장되어 있을 때 아래의 SELECT 문은 이진 탐색을 이용할 수 있다.

SELECT TITLE FROM EMPLOYEE WHERE EMPNO=1365;

- 아래의 SELECT문은 비효율적인데, 그 이유는 WHERE절에 사용된 SALARY는 저장 순서와 무관하기 때문에 파일 전체를 탐색해야하기 때문이다.

SELECT EMPNAME, TITLE FROM EMPLOYEE

WHERE SALARY >= 3000000 AND SALARY <= 4000000;

  단일 단계 인덱스

• 인덱스된 순차 파일은 인덱스를 통해서 임의의 레코드에 접근할 수 있다.
• 인덱스 자체가 파일을 의미하며, 인덱스는 데이터 파일과는 구분된다.
• 단일 단계 인덱스의 각 엔트리는 <탐색 키, 레코드에 대한 포인터> 로 이루어진다.
• 탐색 키(search key) : 인덱스가 정의된 필드
• 포인터는 탐색할 레코드가 저장된 디스크 블록을 참조한다.
• 탐색 키의 값들은 후보 키처럼 각 투플마다 반드시 고유하지 않다.
• 키를 구성하는 애트리뷰트뿐만 아니라 어떤 애트리뷰트도 탐색 키로 사용될 수 있다.
• 엔트리들은 탐색 키 값의 오름차순으로 정렬된다. (이진 탐색 가능)
• 데이터 파일에 들어 있는 여러 애트리뷰트들 중에서 탐색 키에 해당하는 일부 애트리뷰트만 인덱스에 포함되므로 인덱스의 크기는 데이터 파일의 크기에 비해 훨씬 작다.
• 인덱스가 데이터 파일보다 크기가 작으므로 인덱스를 순차적으로 찾는 시간은 데이터 파일을 순차적으로 탐색하는 시간보다 적게 걸린다.
• 인덱스는 DBMS가 파일 내의 특정 레코드들을 빠르게 찾을 수 있도록 하는 데이터 구조이므로 인덱스를 통하여 질의를 수행하면 응답 시간이 향상된다.
• 인덱스는 임의 접근을 필요로 하는 응용에 적합하다.
• 하나의 파일에 여러 개의 인덱스들을 정의할 수 있다.

[ 좌 - 인덱스(정렬된 탐색 키), 우 - 릴레이션 ]

  기본 인덱스(Primary Index)

• 탐색 키가 데이터 파일의 기본 키인 인덱스를 기본 인덱스라고 부른다.
• 기본 인덱스는 기본 키의 값에 따라 정렬된 데이터 파일에 대해 정의된다.
• 각 릴레이션마다 최대한 한 개의 기본 인덱스(기본 키가 1개이므로)를 가질 수 있다.
• 레코드들이 탐색 키(=기본 키)의 오름차순으로 정렬되어 있다.
• 제일 작은 키 값을 인덱스로 두게 되면, 다른 레코드들에 대해 인덱스를 저장할 필요가 없어진다. (블록 내 레코드들은 오름차순 정렬이므로 탐색이 쉬움)

[ 좌 - 인덱스 블록, 우 - 데이터 블록 ]

• 기본 인덱스는 흔히 희소 인덱스로 유지할 수 있다.
• 희소 인덱스는 데이터 파일을 구성하는 각 블록을 대표하는 하나의 탐색 키 값이 인덱스 엔트리에 포함된다.

[ 희소 인덱스 ]

  클러스터링 인덱스(Clustering Index)

• 탐색 키 값에 따라 정렬된 데이터 파일에 대해 정의된다.
• 각각의 상이한 키 값(애트리뷰트)마다 하나의 인덱스 엔트리가 인덱스에 포함된다.
• 범위 질의에 유용하다. (Ex. WHERE EMPNO >= 1000 AND EMPNO <= 1200)
• 인덱스 엔트리들의 정렬 순서와 데이터 파일의 레코드들의 정렬 순서가 일치한다.
• 클러스터링 인덱스에서는 인접한 탐색 키 값을 갖는 레코드들이 디스크에서 가깝게 저장된다.
• 범위의 시작 값에 해당하는 인덱스 엔트리를 먼저 찾고, 범위에 속하는 인덱스 엔트리들을 따라가면서 레코드들을 검색할 때, 디스크에서 읽어오는 블록 수가 최소화된다.
• 어떤 인덱스 엔트리에서 참조되는 데이터 블록을 읽어오면 그 데이터 블록에 들어 있는 대부분의 레코드들은 범위를 만족한다.
• 비 클러스터링 인덱스는 인덱스 엔트리들이 인접해 있어도 레코드들은 대부분의 경우 멀리 떨어져 있다. 따라서 최악의 경우 같은 블록을 여러 번 접근할 수 있다.

  보조 인덱스(Secondary Index)

• 한 파일은 기껏해야 한 가지 필드들의 조합에 대해서만 정렬될 수 있다.
• 보조 인덱스는 탐색 키 값에 따라 정렬되지 않은 데이터 파일에 대해 정의된다.
• 보조 인덱스는 일반적으로 밀집 인덱스(인덱스 수가 희소 인덱스보다 많다)이므로 같은 수의 레코드들을 접근할 때 보조 인덱스를 활용하면 기본 인덱스를 활용하는 것보다 디스크 접근 횟수가 증가할 수 있다.
• 임의 접근(Random Access) 시 모든 레코드에 대해 바로 접근할 수 있다.
• 레코드가 없을 경우에도 인덱스만 보고 파악할 수 있다.
• Ex. 신용카드 회사에서는 신용카드 번호를 사용하여 고객의 레코드를 빈번하게 검색하므로 신용카드 번호에 대해 기본 인덱스를 생성한다. 또한 카드를 분실한 고객이 분실 신고를 할 때 신용카드번호를 기억하지 못한다면 주민등록번호를 사용하여 고객의 레코드를 빠르게 찾을 수 있어야 한다. 그러나 회사의 고객 파일에 기본 인덱스(신용카드번호)만 존재하고 주민등록번호에 보조 인덱스가 존재하지 않는다면 주민등록번호를 사용하여 특정 고객의 레코드를 찾을 때 몇십분씩 시간이 걸릴 수 있다. 주민등록번호는 신용카드번호만큼 특정 고객의 레코드를 찾는데 자주 사용되지는 않지만 주어진 주민등록번호를 갖는 레코드를 빠르게 찾기 위해서 보조 인덱스를 정의하는 것이 바람직하다.

[ 보조 인덱스 ]

  희소 인덱스와 밀집 인덱스의 비교

• 희소 인덱스 : 일부 키 값(각 블록을 대표하는 탐색 키 값)에 대해서만 인덱스에 엔트리를 유지하는 인덱스

• 밀집 인덱스 : 각 레코드의 키 값에 대해서 인덱스에 엔트리를 유지하는 인덱스

• 밀집 인덱스는 데이터 레코드들이 탐색 키 값이 증가하는 순서대로 정렬되어 있는 경우 클러스터링 인덱스이기도 하다.
• 희소 인덱스는 각 데이터 블록마다 한 개의 엔트리를 갖고, 밀집 인덱스는 각 레코드마다 한 개의 엔트리를 가진다.
• 레코드의 길이가 블록 크기보다 훨씬 작은 일반적인 경우(한 블록에 들어가는 레코드 수가 많은 경우)에는 희소 인덱스의 엔트리 수가 밀집 인덱스의 엔트리 수보다 훨씬 적다.
• 희소 인덱스는 일반적으로 밀집 인덱스에 비해 인덱스 단계 수가 1정도 적으므로 인덱스 탐색 시 디스크 접근 수가 1만큼 적을 수 있다.
• 희소 인덱스는 밀집 인덱스에 비해 모든 갱신과 대부분의 질의에 대해 더 효율적이다.
• 그러나 질의에서 인덱스가 정의된 애트리뷰트만 검색(COUNT() 사용)하는 경우에는 데이터 파일을 접근할 필요 없이 인덱스만 접근해서 질의를 수행할 수 있으므로 밀집 인덱스가 희소 인덱스보다 유리하다.(모든 레코드가 인덱스와 대응하므로)
• 한 파일은 한 개의 희소 인덱스와 다수의 밀집 인덱스를 가질 수 있다.
• 희소 인덱스는 디스크 상에서 데이터 파일의 물리적인 순서에 의존한다.

  클러스터링 인덱스와 보조 인덱스의 비교

• 클러스터링 인덱스는 희소 인덱스일 경우가 많으며 범위 질의 등에 좋다.
• 릴레이션의 중간에 투플이 삽입되어 오버플로를 야기하고, 이로 인해서 인덱스 엔트리들의 정렬 순서와 데이터 파일의 레코드들의 정렬 순서가 불일치하는 경우 범위 질의에 적절하지 못하므로 클러스터링 인덱스는 무용지물이된다.
• 클러스터링 인덱스를 정의할 경우 채우기 인수에 낮은 값을 지정하여 추가로 삽입되는 레코드들에 대비하는 것이 바람직하다. (블록에 빈 공간을 두는 것)
• 보조 인덱스는 밀집 인덱스이므로 일부 질의에 대해서는 데이터 파일(디스크 블록)을 접근할 필요 없이 처리할 수 있다. (탐색 키인 애트리뷰트만 검색하는 경우)

  다단계 인덱스

• 인덱스 자체가 클 경우에는 인덱스를 탐색하는 시간도 오래 걸릴 수 있다.
• 다단계 인덱스 : 인덱스 엔트리를 탐색하는 시간을 줄이기 위해서 단일 단계 인덱스를 디스크 상의 하나의 순서 파일로 간주하고, 단일 단계 인덱스에 대해 다시 인덱스를 정의하는 방법이다.
• 2단계 이상의 인덱스는 희소 인덱스만 가능하다. 처음 정의한 인덱스를 1단계 인덱스(기존의 인덱스)라 하고 이 인덱스에 대해서 정의한 추가 인덱스를 2단계 인덱스라 한다.
• 다단계 인덱스는 가장 상위 단계의 모든 인덱스 엔트리들이 한 블록에 들어갈 수 있을 때까지 단일 단계 인덱스에 대해 다시 인덱스를 정의하는 과정을 반복한다.
• 마스터 인덱스(master index) : 가장 상위 단계 인덱스
• 마스터 인덱스는 한 블록으로 이루어지기 때문에 주기억 장치에 상주할 수 있다.
• 다단계 인덱스의 각 단계는 하나의 순서 파일이다.
• 새로운 인덱스 엔트리를 추가하거나 기존의 인덱스 엔트리를 삭제하면 단일 단계 인덱스의 경우보다 처리 과정이 복잡해진다.
• 대부분의 다단계 인덱스는 균형 탐색 이진트리를 일반화한 트리를 사용한다. 트리는 추가될 인덱스 엔트리들을 위해 각 인덱스 블록에 예비 공간을 남겨둔다.
• 인덱스의 각 단계들이 오름차순으로 유지되어야 하므로 다단계 인덱스의 갱신은 단일 단계 인덱스의 갱신보다 시간이 오래 걸리고 복잡해진다. 하지만 대부분의 데이터베이스 응용에서 검색 비율이 갱신 비율보다 월등히 높으므로 모든 DBMS에서는 인덱스를 다단계 인덱스로 유지한다.

[디스크접근횟수 3번]

  SQL의 인덱스 정의문

• SQL의 CREATE TABLE문에서 PRIMARY KEY절로 명시한 애트리뷰트(기본 키)에 대해서는 DBMS가 자동적으로 기본 인덱스를 생성한다.
• UNIQUE로 명시한 애트리뷰트에 대해서는 DBMS가 자동적으로 보조 인덱스를 생성한다.
• SQL2는 인덱스 정의 및 제거에 관한 표준 SQL문을 제공하지 않는다.
• 다른 애트리뷰트에 추가로 인덱스를 정의하기 위해서는 DBMS마다 다소 구문이 다른 CREATE INDEX문을 사용해야 한다.
• 다수의 애트리뷰트를 사용한 인덱스 정의

- 두 컬럼이 같이 사용되는 경우가 많을 때 사용된다.

- 다수의 애트리뷰트에 인덱스를 정의할 때 애트리뷰트들의 순서가 중요하다.

- 한 릴레이션에 속하는 두 개 이상의 애트리뷰트들의 조합에 대하여 하나의 인덱스를 정의할 수 있다.

CREATE INDEX EmpIndex ON EMPLOYEE (DNO, SALARY);

- 이 인덱스는 아래의 질의에 활용될 수 있다.

SELECT * FROM EMPLOYEE WHERE DNO=3 AND SALARY=4000000;

- 단, “WHERE SALARY=4000000 AND DNO=3”처럼 DNO와 SALARY의 순서가 바뀌면 질의는 실행되지 않는다.

- 정의한 인덱스에서는 DNO에 대해 오름차순으로 정렬한 뒤 DNO가 같으면 SALARY에 대해 오름차순으로 정렬했기 때문이다.

- 위에서 정의한 인덱스는 아래의 질의에도 활용될 수 있다.

SELECT * FROM EMPLOYEE WHERE DNO>=2 AND DNO <=3

AND SALARY >= 3000000 AND SALARY <= 4000000;

SELECT * FROM EMPLOYEE WHERE DNO=2;  (또는 DNO에 대한 범위 질의)

- 위에서 정의한 인덱스는 아래의 질의에는 활용될 수 없다.

SELECT * FROM EMPLOYEE

WHERE SALARY >= 3000000 AND SALARY <= 4000000;  (또는 SALARY에 대한 동등 조건)

• 복합 애트리뷰트에 인덱스를 정의할 때 3개 이하의 애트리뷰트를 사용하는 것이 바람직하다. 인덱스가 정의된 복합 애트리뷰트에 포함된 애트리뷰트의 개수가 늘어날수록 이 인덱스를 활용하는 탐색 조건이 복잡해지고, 인덱스 엔트리의 길이가 늘어나기 때문에 탐색 성능이 저하된다.

  인덱스의 장점과 단점

• 인덱스는 검색 속도를 향상시키지만 인덱스를 저장하기 위한 공간이 추가로 필요하고 삽입, 삭제, 수정 연산(인덱스를 만든 컬럼 값 변경 시)의 속도는 저하시킨다.
• 소수의 레코드들을 수정하거나 삭제하는 연산의 속도는 향상된다. 레코드를 수정하거나 삭제하려면 먼저 해당 레코드를 찾아야 하기 때문에 파일에 많은 인덱스가 정의되어 있지 않는 한, 인덱스를 사용하여 레코드를 찾는 효율이 증가되므로 일반적으로 인덱스를 갱신하는 데 필요한 추가 오버헤드를 무시할 수 있다.
• 릴레이션이 매우 크고, 질의에서 릴레이션의 투플들 중 일부를 검색하고, WHERE절이 잘표현되었을 때(인덱스를 만든 컬럼의 순서가 적절할 때) 특히 성능에 도움이 된다.

  인덱스 선정 지침과 데이터베이스 튜닝

• 가장 중요한 질의들과 이들의 수행 빈도, 가장 중요한 갱신들과 이들의 수행 빈도, 이와 같은 질의와 갱신들에 대한 바람직한 성능들을 고려하여 인덱스를 선정한다.
• 워크로드 내의 각 질의에 대해 이 질의가 어떤 릴레이션들을 접근하는가, 어떤 애트리뷰트들을 검색하는가, WHERE절의 선택/조인 조건에 어떤 애트리뷰트들이 포함되는가, 이 조건들의 선별력(Selectivity)은 얼마인가 등을 고려한다.
• 워크로드 내의 각 갱신에 대해 이 갱신이 어떤 릴레이션들을 접근하는가, WHERE절의 선택/조인 조건에 어떤 애트리뷰트들이 포함되는가, 이 조건들의 선별력은 얼마인가, 갱신의 유형(INSERT/DELETE/UPDATE), 갱신의 영향을 받는 애트리뷰트 등을 고려한다.
• 어떤 릴레이션에 인덱스를 생성해야 하는가, 어떤 애트리뷰트를 탐색 키로 선정해야 하는가, 몇 개의 인덱스를 생성해야 하는가, 각 인덱스에 대해 클러스터링 인덱스, 밀집 인덱스, 희소 인덱스 중 어느 유형을 선택할 것인가 등을 고려한다.
• 인덱스를 선정하는 한 가지 방법은 가장 중요한 질의들을 차례로 고려해보고, 현재의 인덱스가 최적의 계획에 적합한지 고려해보고, 인덱스를 추가하면 더 좋은 계획이 가능한지 알아본다.
• 물리적 데이터베이스 설계는 끊임없이 이루어지는 작업이다.

  인덱스를 결정하는데 도움이 되는 몇 가지 지침

• 기본 키는 클러스터링 인덱스를 정의할 훌륭한 후보
• 외래 키도 인덱스를 정의할 중요한 후보
• 한 애트리뷰트에 들어 있는 상이한 값들의 개수가 거의 전체 레코드 수와 비슷하고, 그 애트리뷰트가 동등 조건에 사용된다면 비 클러스터링 인덱스를 생성하는 것이 좋다. (Ex: 학번)
• 투플이 많이 들어 있는 릴레이션에서 대부분의 질의가 검색하는 투플이 2~4% 미만인 경우에는 인덱스를 생성하는 것이 좋다.
• 자주 갱신되는 애트리뷰트에는 인덱스를 정의하지 않는 것이 좋다. (Ex: 주소)
• 갱신이 빈번하게 이루어지는 릴레이션에는 인덱스를 많이 만드는 것을 피하는 것이 좋다. (3개 이하가 적당하다.)
• 가능하면 릴레이션당 인덱스의 개수를 3개 이내로 유지하는 것이 좋다.
• 후보 키는 기본 키가 갖는 모든 특성을 마찬가지로 갖기 때문에 인덱스를 생성할 후보이다.
• 인덱스는 파일의 레코드들을 충분히 분할할 수 있어야 한다.
• 정수형 애트리뷰트에 인덱스를 생성하는 것이 좋다. 그 다음에는 고정 길이 애트리뷰트에 인덱스를 만드는 것이 좋다.
• VARCHAR 애트리뷰트에는 인덱스를 만들지 않는 것이 좋다. (구글의 권장사항, Ex. CHAR(10)인 name 컬럼에서 10바이트 미만의 이름을 검색하면 빈 공간을 채워서 주기 때문에 검색 시 못찾는 경우가 발생한다.)
• 날짜형, 실수형 애트리뷰트에는 인덱스를 만들지 않도록 한다.
• 작은 파일에는 인덱스를 만들 필요가 없다.
• 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후에 인덱스들을 다시 생성하는 것이 좋다.
• 클러스터링 인덱스를 정의하기 전에는 클러스터링 인덱스를 정의할 애트리뷰트를 기준으로 먼저 데이터 파일을 정렬하는 것이 좋다.
• ORDER BY절(정렬 속도 향상), GROUP BY절(그룹화 속도 향상)에 자주 사용되는 애트리뷰트는 인덱스를 정의할 후보이다.
• 특정 레코드 검색 시 WHERE 절에서 확률(전체 레코드 수/컬럼의 상이한 값의 개수)이 낮은 인덱스를 사용하는 것이 좋다. 상이한 값이 많을수록 읽어오는 레코드 수가 적기 때문에 성능(시간 절약)이 향상된다.

  인덱스가 사용되지 않는 경우

• 시스템 카탈로그가 오래 전의 데이터베이스 상태를 나타낼 때
• DBMS의 질의 최적화 모듈이 릴레이션 크기가 작아서 인덱스가 도움이 안된다고 판단할 때
• 인덱스가 정의된 애트리뷰트에 산술 연산자가 사용될 때

SELECT * FROM EMPLOYEE WHERE SALARY*12 > 4000000;

단, 아래의 질의에는 인덱스가 사용된다.

SELECT * FROM EMPLOYEE WHERE SALARY > 4000000/12;

• DBMS가 제공하는 내장 함수가 집단 함수 대신에 사용될 때

SELECT * FROM EMPLOYEE WHERE SUBSTR(EMPNAME, 1, 1)=‘김’;

(SUBSTR은 MS SQL Server 내장 함수이다.)

아래의 질의처럼 집단 함수를 사용할 경우 인덱스가 사용된다.

SELECT * FROM EMPLOYEE FROM EMPNAME LIKE ‘김%’;

• 널값에 대해서는 일반적으로 인덱스가 사용되지 않는다. (검색되지 않음)

SELECT * FROM EMPLOYEE WHERE MANAGER IS NULL;

  질의 튜닝을 위한 추가 지침

• DISTINCT절의 사용을 최소화
• GROUP BY절과 HAVING절의 사용을 최소화
• 임시 릴레이션의 사용을 피할 것

위의 두 SELECT문 대신에 아래와 같은 한 개의 SELECT 문이 바람직하다.

• SELECT * 대신에 SELECT 절에 애트리뷰트 이름들을 구체적으로 명시할 것 (컬럼 추가/삭제 시 검색한 내용을 활용하는 소스 코드에서 오류가 발생할 수 있다. 또한 불필요한 애트리뷰트가 전송되지 않으므로 네트워크 트래픽이 감소된다.)