SQL 데이터베이스(DB) 물리적 저장 구조와 인덱스

 

물리적 데이터베이스

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

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

 

 

테이블의 물리적 저장 구조

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

블록 : 하나 이상의 레코드들을 저장, 각 블록은 하나의 테이블에 속함

 

블록 내 레코드 저장 방식

- 테이블의 정의에 따라 고정길이 또는 가변길이의 레코드들을 저장

 

예) 고정길이

create table course (
     course_id char(4),
     title     char(20),
     credit    int
)

예) 가변길이

create table course (
      course_id char(4),
      title     varchar2(20),
      credit    int
)

 

클러스터링

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

 

인덱스(index)

사용 목적

- 파일 내에서 레코드가 저장된 위치를 빨리 찾기 위해 사용

 

✔ 검색 속도 향상

- 인덱스가 없으면 어느 위치에 있는지 알 수 없으므로 모든 레코드들을 순차적으로 검색해야함

예) 도서관의 책 검색, 전화번호부의 검색

 

데이터베이스의 인덱스

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

인덱스의 구조 → 인덱스 엔트리 : (검색 키, 주소) 쌍

 

SQL 인덱스

 

예) student 테이블의 레코드 수가 10000개, dept_id 값이 '920'인 레코드의 수는 500개라고 가정

select *
from student
where dept_id = ‘920’ and address = ‘서울’

 

✔ 인덱스가 없을 경우 : 모든 레코드를 순차 검색해야 함 → 10000개의 레코드를 모두 검색

✔ dept_id 필드에 인덱스가 있을 경우 : dept_id가 '920'인 레코드들만 검색 가능 → 500개의 레코드만을 검색

 

조인 연산의 효율적인 처리

 

예) student 테이블의 레코드 수가 10000개, department 테이블의 레코드 수가 100개라고 가정

select count(*)
from student s, department d
where address = ‘서울’ and s.dept_id = d.dept_id

 

✔ 인덱스가 없을 경우 : 조인 처리를 위해, department 테이블의 각 레코드에 대해 student 테이블의 모든 레코드들을 순차적으로 조회하여 비교, 시간 비용이 두 테이블의 레코드 수의 곱에 비례

 

- student 테이블의 dept_id 필드에 대한 인덱스가 구축되었다고 가정하면 효율적으로 처리 가능

- department 테이블을 순차적으로 검색하면서, 각 레코드의 dept_id 값에 대해 위 인덱스를 사용하여 student 테이블의 대응되는 레코드를 직접 조회

- 검색된 레코드 중에서 address 필드 값이 '서울' 인 레코드만 선택

- 시간 비용 : 두 테이블의 레코드 수의 합과 비례 10000+100

 

예)

select dept_id, count(*)
from student
group by dept_id

 

인덱스가 없을 경우 : student 테이블의 모든 레코드를 조회해야 함

 

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

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

- 테이블에 접근하지 않고 인덱스만으로 질의 처리 가능

 

 

기본키와 인덱스

 

◽ 기본키

- 레코드의 검색, 삽입, 삭제 시 빈번하게 조회됨

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

 

◽ 인덱스의 장점

- 검색 성능 향상

 

◽ 인덱스의 단점

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

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

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

 

✔ 질의 시간 및 빈도, 삽입/삭제/수정 시간 및 빈도, 저장 공간의 크기 등을 종합적으로 고려하여 결정해야 함