데이터베이스 설계 6) 물리적 저장구조와 인덱스

 

 

 

 

1. 물리적 데이터베이스

관계형 데이터베이스의 테이블과 레코드들은 디스크에 저장된다.

운영체제가 관리하는 파일 시스템을 이용한다.

 

- 기본 저장 구조 : 파일

- 입출력 단위 : 블록 (512 bytes ~ 수천 bytes)

 

 

 

 

 

 

 

 

 

2. 테이블의 물리적 저장 구조

파일 : 하나 이상의 테이블들을 저장

블록 : 하나 이상의 레코드들을 저장

각 블록은 하나의 테이블에 속한다.

 

 

 

 

 

 

3. 블록 내 레코드 저장 방식

 

1) 고정 길이로 저장된 레코드

-> 전체길이 = 28 x 5 = 140 bytes 

 

 

 

 

2) 가변 길이로 저장된 레코드

 

 

-> 전체 길이 : 90 bytes(한 단어당 2byte 가정)

 

 

 

 

 

 

4. 클러스터링

: 자주 검색되는 필드를 기준으로 관련된 레코드들을 같은 블록이나 인접한 블록에 저장하여 검색 성능을 높이는 방법이다.

 

ex) credit 필드를 기준으로 클러스터링

 

 

5. 인덱스

: 레코드에 대한 물리적 저장 위치를 별도로 기록한다.

- 레코드가 저장된 위치를 빨리 찾기 위해 사용한다.

 

1) 인덱스의 구조

- 인덱스 엔트리 : (검색 키, 주소) 쌍 

- 검색 키 : 테이블에 속한 한 개 이상의 필드 값들이다. 

 

 

 

- course 테이블의 Title 필드에 대한 인덱스이다.

 

 

 

 

 

 

 

 

2) SQL 질의와 인덱스

 

 

ex 1) 인덱스가 생성된 필드에 대한 조건을 포함하는 질의 처리 시 활용 예

: student 테이블의 레코드 수가 10,000개, dept_id가 '920'인 레코드가 500개라고 가정한다.

SELECT *
FROM STUDENT
WHERE DEPT_ID = '920' AND ADDRESS = '서울';

 

- 인덱스가 없을 경우,

모든 레코드를 순차적으로 검색해야 하므로 10,000개의 레코드를 모두 검색해야 한다.

- dept_id 필드에 인덱스가 있을 경우,

dept_id가 '920'인 레코드들만 검색 가능하다. -> 500개의 레코드만 검색하면 된다.

 

 

 

 

 

 

 

 

ex 2) Join 연산의 효율적인 처리

student 테이블의 레코드 수가 10,000개, department 테이블의 레코드 수가 100개라고 가정한다.

 

SELECT COUNT(*)
FROM STUDENT S, DEPARTMENT D
WHERE ADDRESS = '서울' AND S.DEPT_ID = D.DEPT_ID;

 

- 인덱스가 없을 경우,

: 조인 연산을 위해 student 와 department 테이블을 모두 순차적으로 검색해야 한다.

-> 시간 비용 : 10,000 x 100 

 

- student 테이블에 dept_id 필드에 대한 인덱스가 구축되어 있다면,

: department 테이블을 순차적으로 조회하면서 

각 레코드의 dept_id 값에 대해 위 인덱스를 사용하여 student 테이블에대응되는 레코드를 직접 조회한다.

-> 시간 비용 : 10,000 + 100

 

 

 

 

 

 

 

 

 

 

ex 3) 인덱스 만을 이용한 질의 처리

 

SELECT DEPT_ID, COUNT(*)
FROM STUDENT
GROUP BY DEPT_ID

- 인덱스가 없을 경우,

student 테이블의 모든 레코드를 조회해야 한다.

- student 테이블에 dept_id 필드에 대한 인덱스가 있을 경우,

인덱스에서 각 dept_id 값에 대한 인덱스 엔트리 개수를 세면 된다.

테이블에 접근할 필요도 없다.

 

 

 

 

 

 

 

3) 기본 키와 인덱스

: 기본 키에 대해서는 대부분의 DBMS가 자동으로 인덱스를 생성한다.

 

< 인덱스의 장단점 >

장점

- 검색 속도 향상

단점

- 테이블에 레코드를 삽입, 삭제, 수정 시 인덱스도 갱신이 필요하다.

- 인덱스의 개수가 많으면 삽입, 삭제, 수정 연산 속도가 저하된다.

- 인덱스를 저장하기 위한 디스크 공간이 필요하다.

 

-> 질의(검색) 시간 및 빈도, 삽입/삭제/수정 시간 및 빈도, 저장 공간의 크기 등을 종합적으로 고려하여 결정해야 한다.