데이터베이스 설계와 인덱싱

데이터베이스 설계 단계

데이터베이스 설계: 사용자의 다양한 요구 사항을 고려하여 데이터베이스를 생성하는 과정

 

관계 데이터베이스의 대표적인 설계 방법

1) 정규화를 이용한 설계

2) E-R 모델과 릴레이션 변환 규칙을 이용한 설계

 

E-R 모델과 릴레이션 변환 규칙을 이용한 설계의 과정

1단계: 요구 사항 분석

사용자의 요구 사항을 수집하고 분석하여 개발할 데이터베이스의 용도를 파악 → 요구 사항 명세서 작성

데이터베이스를 실제로 사용할 주요 사용자 범위 결정

사용자가 조직에서 수행하는 업무 분석

면담, 설문 조사, 업무 관련 문서 분석 등의 방법을 이용해 요구 사항 수집

수집된 요구 사항에 대한 분석 결과를 요구 사항 명세서로 작성

 

2단계: 개념적 설계

DBMS에 독립적인 개념적 스키마 설계

요구 사항 분석 결과물을 개념적 데이터 모델을 이용해 개념적 구조로 표현 (개념적 모델링) → 개념적 스키마(E-R 다이어그램)

요구 사항 분석 결과를 기반으로 중요한 개체를 추출, 개체 간의 관계를 결정하여 E-R 다이어그램으로 표현

Step 1. 개체와 속성 추출

개체: 저장할 만한 가치가 있는 중요 데이터를 가진 사람이나 사물 등

요구 사항 문장에서 업무와 관련이 깊은 의미 있는 명사를 찾아 개체와 속성으로 분류한다.

(일반적이고 광범위한 의미의 명사는 제외하며, 의미가 같은 명사가 여러 개면 대표 명사 하나만 선택한다.)

 

Step 2. 관계 추출

관계: 개체 간의 의미 있는 연관성

요구 사항 문장에서 개체 간의 연관성을 의미 있게 포함한 동사를 찾아 매핑 카디널리티와 참여 특성을 결정한다.

매핑 카디널리티(1:1, 1:n, n:m), 참여 특성(필수적, 선택적)

 

Step 3. E-R 다이어그램 작성

1과 2를 기반으로 통합

 

 

3단계: 논리적 설계

DBMS에 적합한 논리적 스키마 설계

개념적 스키마를 논리적 데이터 모델을 이용해 논리적 구조로 표현 → 논리적 모델링(데이터 모델링)

E-R 다이어그램을 릴레이션 스키마로 변환 후, 데이터 타입, 길이, 널 값 허용 여부, 기본 값, 제약조건 등을 세부적으로 결정하고 문서화

 

E-R 다이어그램을 릴레이션 스키마로 변환하는 규칙

1. 모든 개체는 릴레이션으로 변환한다.

2. 다대다 관계는 릴레이션으로 변환한다.

3. 일대다 관계는 외래키로 표현한다.

4. 일대일 관계는 외래키로 표현한다.

5. 다중 값 속성은 릴레이션으로 변환한다.

* 순서대로 적용해되, 해당되지 않는 규칙은 제외

 

1. 모든 개체는 릴레이션으로 변환한다

E-R 다이어그램의 각 개체를 하나의 릴레이션으로 변환

개체 이름, 속성, 키 속성 → 릴레이션 이름, 속성, 기본 키

* 개체 속성이 복합 속성인 경우 복합 속성을 구성하고 있는 단순 속성들로 변환

 

2. 다대다 관계는 릴레이션으로 변환한다

E-R 다이어그램의 다대다 관계를 하나의 릴레이션으로 변환

관계 이름, 속성 → 릴레이션 이름, 속성

* 관계에 참여하는 개체를 규칙 1에 따라 릴레이션 변환 후, 각각 릴레이션 기본키를 관계 릴레이션의 외래키로 지정해 두 릴레이션의 외래키들을 조합하여 관계 릴레이션의 기본키로 지정

 

3. 일대다 관계는 외래키로 표현한다

1) 일반적인 일대다 관계는 외래키로 표현한다.

 - 일대다 관계에서 1측에 있는 개체 릴레이션의 기본키를 n측에 있는 개체 릴레이션에 포함시켜 외래키로 지정

 - 관계의 속성들도 n측 개체 릴레이션에 포함시킴

2) 약한 개체가 참여하는 일대다 관계는 외래키를 포함해서 기본키로 지정한다.

 - 일대다 관계에서 1측 개체 릴레이션의 기본키를 n측 개체 릴레이션에 포함시켜 외래키로 지정

 - 관계 속성들도 n측 개체 릴레이션에 포함시킴

 - n측 개체 릴레이션은 외래키를 포함하여 기본키를 지정(강한 개체의 기본키를 이용해 식별해야 함)

 

4. 일대일 관계는 외래키로 표현한다

1) 일반적인 일대일 관계는 외래키를 서로 주고받는다.

 - 서로의 기본키를 주고받아 외래키로 지정

 - 관계 속성들도 모든 개체 릴레이션에 포함시킴

 - 불필요한 데이터 중복 발생 가능성O

2) 일대일 관계에 필수적으로 참여하는 개체의 릴레이션만 외래키를 받는다.

 - 관계 속성들은 관계에 필수적으로 참여하는 개체 릴레이션에 포함시킴

3) 모든 개체가 일대일 관계에 필수적으로 참여하면 릴레이션 하나로 합친다.

 - 관계 이름을 릴레이션 이름으로 사용하고, 관계 참여하는 두 개체 속성을 관계 릴레이션에 모두 포함시킴

 - 두 개체 릴레이션의 키 속성을 조합해 관계 릴레이션의 기본키로 지정

 

5. 다중 값 속성은 릴레이션으로 변환한다

다중 값 속성과 함께 그 속성을 가지고 있던 개체 릴레이션의 기본키를 외래키로 가져와 새로운 릴레이션에 포함시킨다.

새로운 릴레이션의 기본키는 다중 값 속성과 외래키를 조합하여 지정한다.

 

기타 고려 사항

모든 관계를 독립적인 릴레이션으로 변환 가능 → 속성이 많은 관계는 유형에 상관없이 변환 고려

개체가 자기 자신과 관계를 맺는 순환 관계도 기본 규칙 적용

 

테이블 명세서 작성

릴레이션 스키마에 대한 설계 정보(데이터 타입과 길이, 널 허용 여부, 기본값, 제약조건 등)를 기술한 문서 작성

 

4단계: 물리적 설계

하드웨어나 운영체제 특성을 고려하여 필요한 인덱스 구조나 내부 저장 구조 등에 대한 물리적인 구조 설계

 

5단계: 구현

SQL로 작성한 명령문을 DBMS에서 실행하여 데이터베이스를 실제로 생성

 

 

데이터베이스의 물리적 저장

실제 데이터는 보조기억장치에 저장 (하드디스크, SSD, USB 메모리 등)

 

하드디스크

원형의 플레이트로 구성되어 플레이트는 논리적으로 트랙 > 섹터로 나뉘어 구성

플레이트가 초당 빠른 속도로 회전하며, 회전하는 플레이트를 하드디스크의 액세스 암과 헤더가 접근하여 원하는 섹터에서 데이터를 가져온다.

하드디스크에 저장된 데이터를 읽어오는 데 걸리는 시간에 영향을 주는 요인

- RPM; 모터에 의해 분당 회전하는 속도

- latency time; 데이터를 읽을 때 액세스 암이 이동하는 시간

- transfer time; 주기억장치로 읽어오는 시간

 

액세스 시간 = 탐색시간(액세스 헤드를 트랙에 이동시키는 시간) + 회전지연시간(섹터가 액세스 헤드에 접근하는 시간) + 데이터 전송시간(데이터를 주기억장치로 읽어오는 시간)

 

MySQL

SHOW ENGINES; 명령어를 통해 InnoDB 엔진 데이터베이스 파일 확인

데이터 파일(ibdata): 사용자 데이터와 개체를 저장, 테이블과 인덱스로 구성. 확장자는 *.ibd

폼파일(frm File): 테이블에 대한 각종 정보와 테이블을 구성하는 필드, 데이터 타입에 대한 정보 저장. 데이터베이스 구조 등의 변경사항이 있을 때 자동 업데이트됨

 

인덱스와 B-tree

인덱스

: 도서의 색인이나 사전과 같이 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조

 

인덱스의 특징

• 테이블에서 한 개 이상의 속성을 이용하여 생성
• 빠른 검색과 함께 효율적인 레코드 접근 가능
• 순서대로 정렬된 속성과 데이터의 위치만 보유 → 테이블보다 작은 공간 차지
• 저장된 값들은 테이블의 부분집합이 됨
• 일반적으로 B-tree 형태 구조
• 데이터의 수정, 삭제 등의 변경이 발생하면 인덱스 재구성 필요

 

MySQL 인덱스 종류

1. 클러스터 인덱스:

기본 인덱스

테이블 생성 시 기본키를 지정하면 기본키에 대해 클러스터 인덱스 생성. 미지정 시 먼저 나오는 UNIQUE 속성에 대해 생성

기본키나 UNIQUE 속성이 없는 테이블은 MySQL이 자체 생성한 행 번호(Row ID)를 이용하여 생성

 

2. 보조 인덱스:

클러스터 인덱스가 아닌 모든 인덱스

보조 인덱스의 각 레코드는 보조 인덱스 속성과 기본키 속성 값을 가짐

보조 인덱스를 검색하여 기본 키 속성 값을 찾은 후, 클러스터 인덱스로 가서 해당 레코드를 찾는 방식

 

 

인덱스 생성 시 고려사항

• WHERE절에 자주 사용되는 속성이어야 함
• 조인에 자주 사용되는 속성이어야 함
• 단일 테이블에 인덱스가 많으면 속도가 저하될 수 있음(테이블 당 4~5개 권장)
• 속성이 가공되는 경우 사용하지 않음
• 속성의 선택도가 낮을 때 유리함(속성의 모든 값이 다른 경우)

 

인덱스 관련 문법

인덱스 생성

 

인덱스 재구성

ANALYZE TABLE 테이블이름;

 

인덱스 삭제

DROP INDEX 인덱스이름 ON 테이블이름;

 

 

B-tree

그림 4-10 B-tree의 구조

• 노드의 데이터 수가 n개이면 자식 노드 개수는 n+1
• 노드의 데이터는 반드시 정렬된 상태
• 노드의 자식노드 데이터들은 노드 데이터 기준으로, 데이터보다 작은 값은 왼쪽 서브트리에, 큰 값들은 오른쪽 서브트리에 배치
• 루트 노드가 자식이 있다면 2개 이상의 자식이어야 함
• 루트 노드를 제외한 모든 노드는 적어도 M/2개의 데이터를 갖고 있어야 함

 

 

 

출처: 데이터베이스 개론 2판 김연희 저, MySQL로 배우는 데이터베이스 개론과 실습 박우창 외2