정보처리기사[실기 핵심 정리] - 데이터베이스 물리속성 설계

파티셔닝

1) 파티셔닝 개념

• 데이터베이스의 특정 부분(데이터)을 여러 섹션으로 분할 하는 방법
• 대용량 데이터의 조회 속도 향상, 관리 용이성, 성능 및 가용성 향상을 위해 사용

 

2) 샤딩(Sharding)

• 거대한 데이터베이스나 시스템을 작은 조각(샤드)으로 나누어 분산 저장 및 관리하는 기법
• 데이터의 크기가 매우 클 때 효과적
• 샤드(Shard)는 나눠진 데이터 블록의 구간(Epoch)을 의미

 

3) 파티셔닝의 장/단점

장점	● 데이터 가용성 향상 ● 파티션별 독립적인 백업 및 복구 가능 ● 성능 향상
단점	● 테이블 간의 조인 비용 증가 ● 테이블과 인덱스는 동일한 파이션 전략을 따라야 함

 

4) 파티셔닝 종류

• 수평 분활(Horizontal Partitioning)
  • 테이블 행을 기준으로 분할
  • 스키마를 복제한 후 샤드키를 기준으로 데이터를 나눈다.
• 수직 분활(Vertical Partitioning)
  • 테이블의 컬럼을 기준으로 분할하여 성능 향상
  • 자주 사용하는 컬럼 등을 분리시켜 성능을 향상
  • 하나의 테이블을 2개 이상으로 분리하는 작업

 

5) 분할기준

• 범위 분할(Range Partitioning)
  • Partition Key의 연속된 범위로 파티션을 정의
  • ex) 월별, 분기별 등
• 목록 분할(List Partitioning)
  • 특정 Partition에 저장될 Data에 대한 명시적 제어
  • ex) [한국, 일본, 중국 -> 아시아],[노르웨이,스워덴,핀란드 -> 북유럽]
• 해시 분할(Hash Partitioning)
  • 파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션 매핑
  • 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리
  • 병렬처리 시 성능효과 극대화
• 라운드 로빈 분할(Round Robin Partitioning)
  • 데이터를 균일하게 분배해서 저장하는 방식
• 합성 분할(Composite Partitioning)
  • 위의 기술들을 복합적으로 사용하는 방법
  • ex) 범위 분할 후 분할된 데이터를 해시 분할 하는 경우

클러스터 설계

1) 클러스터의 개념

• 자주 사용되는 테이블의 데이터를 디스크 상 동일한 위치에 저장하여 데이터 엑세스 효율을 향상시키는 물리적 저장 방법

 

2) 클러스터의 특징

• 그룹화된 데이터는 같은 데이터 테이블 블록에 저장되어 디스크 I/O를 최소화
• 클러스터링된 테이블 간의 조인 성능 향상
• 데이터 조회 성능은 향상되지만, 데이터의 추가/수정/삭제나 전체 스캔시 성능 저하 가능
• 데이터의 분포도가 넓을 경우 더 유리함
• 클러스터링된 테이블에서는 클러스터드 인덱스 생성 시 성능 향상

 

3) 클러스터 대상 테이블

• 데이터 분포도가 넓은 테이블
• 대량 데이터의 범위 조회가 빈번한 테이블
• 자주 변경(추가/수정/삭제)되지 않는 테이블
• 자주 조인되는 테이블
• 정렬, 그룹화, 연합 연산이 자주 사용되는 테이블

인덱스(Index)

1) 인덱스의 개념

• 데이터베이스 테이블 검색 속도 향상을 위한 저장 공간 활용 자료구조
• 책의 색인과 유사하며, 빠른 조회를 위해 데이터 위치 정보를 포함

 

2) 인덱스를 사용하는 이유

• 대규모테이블에서의 빠른 데이터 추출

 

3) 인덱스의 종류

• 클러스터 인덱스
  • 테이블당 1개만 허용되며, 해당 컬럼을 기준으로 테이블이 물리적으로 정렬
  • 데이터는 기본적으로 오름차순으로 정렬을 진행
  • 기본키를 설정하면 자동으로 클러스터드 인덱스가 적용
  • 인덱스 자체의 리프 페이지가 곧 데이터
  • 데이터 입력, 수정, 삭제 시 항상 정렬 상태를 유지
  • 넌 클러스형 인덱스보다 검색 속도는 빠르나, 데이터의 입력, 수정, 삭제 시에는 느림
• 넌클러스터 인덱스
  • 테이블당 약 240개의 인덱스 생성 가능
  • 레코드의 원본은 정렬되지 않고, 인덱스 페이지만 정렬
  • 인덱스 자체의 리프페이지는 데이터가 아니라 데이터가 위치하는 포인터(RID)이기 때문에 클러스터형보다는 검색 속도는 더 느리지만 데이터의 입력, 수정, 삭제는 더 빠름
  • 인덱스를 생성할 때 데이터 페이지는 그냥 둔 상태에서 별도의 인덱스 페이지를 따로 만들기 때문에 용량을 더 차지함
• 밀집 인덱스
  • 데이터 레코드 각각에 대해 하나의 인덱스가 만들어짐
• 희소 인덱스 
  • 레코드 그룹 또는 데이터 블록에 대해 하나의 인덱스가 만들어짐

 

4) 인덱스의 구조

• 트리 기반 인덱스
  • 대부분의 상용 DBMS는 데이터 검색 효율을 높이기 위해 B+ 트리 인덱스를 사용한다.
• 비트맵 인덱스
  • 주로 값의 범위가 제한적인 컬럼에 사용되며 각 값에 대한 비트 배열을 사용하여 인덱스한다.
• 함수 기반 인덱스
  • 컬럼 값이 아닌 특정 함수의 결과에 대해 인덱스를 생성한다.
• 비트맵 조인 인덱스
  • 비트맵 인덱스의 확장으로, 주로 조인 연산을 최적화하기 위해 사용된다.
• 도메인 인덱스
  • 도메인 인덱스는 사용자 정의 인덱스로, 특정 애플리케이션에 특화된 인덱싱을 구현한 수 있다.

 

5) 인덱스 컬럼의 선정

• 분포도가 좋은(10 ~ 15) 컬럼
• 자주 조합되며 사용되는 경우 결함 인덱스를 생성
• 가능한 수정이 빈번하지 않은 컬럼을 선정
• 한 컬럼이 여러 인덱스 포함되지 않도록 서계
• 기본키 및 외부키가 되는 컬럼을 선정

 

6) 인덱스 생성 시 고려사항

• 새로 추가된 인덱스는 기존 엑세스 경로에 영향을 미칠 수 있다.
• 지나치게 많은 인덱스는 오버헤드를 발생시킨다.
• 넓은 범위를 인덱스로 처리 시 많은 오버헤드를 발생시킨다.
• 옵티마이저를 위한 통계 데이터를 주기적으로 갱신한다.
• 인덱스를 위한 추가적인 저장 공간이 필요

 

5) 인덱스 생성/ 삭제

 

인덱스 만들기

CREATE INDEX 인덱스이름 ON 테이블명(컬럼명);

인덱스 삭제하기(DROP ~)

DROP INDEX 인덱스명;

인덱스 재구성

ALTER INDEX 인덱스명 REBUILD;

 

뷰(View)

1) 뷰의 개념

• 기본 테이블에서 유도된 이름이 있는 가상 테이블
• 실제 데이터 저장하지 않고 논리적으로만 존재
• 사용자는 실제 테이블과 같이 뷰를 조작 가능

 

2) 뷰의 특징

• 기본 테이블 유도로, 기본 테이블과 유사한 형태와 조작을 갖는다.
• 물리적 구현 없는 가상 테이블
• 논리적 데이터 독립성 제공
• 관리 용이 및 명령문 간소화(필요 데이터만 처리)
• 뷰를 통한 데이터 접근으로 안전 보호
• 데이터 조작(삽입, 삭제, 갱신)에 제한 존재
• 기존 테이블/ 뷰 삭제 시 관련 뷰도 자동 삭제
• 생성은 CREATE, 삭제는 DROP으로 가능하며 변경은 ALTER로 불가

 

3) 뷰의 장/단점

장점	● 논리적 데이터 독립성 ● 다양한 사용자 요구에 대한 동시 지원 ● 간결한 데이터 관리 ● 자동 보안 제공(접근 제어)
단점	● 독립적 인덱스 부재 ● ALTER VIEW 사용 불가 ● 뷰 조작(삽입, 삭제, 갱신 등)에 제약 존재

 

4) 뷰생성/ 삭제

뷰 생성

CREATE OR REPLACE VIEW 뷰이름(컬럼명,컬럼명2,...)
 AS
 SELECT 원본컬럼명, 원본컬럼명2,...
 FROM 테이블명
 WHERE 조건절;

뷰 삭제

DROP INDEX 뷰명;

 

시스템 카탈로그

1) 시스템 카탈로그

• 데이터베이스의 모든 데이터 개체들에 대한 정보를 저장한 시스템 테이블로,데이터 사전(Data Dictionary)이라고 한다
• DDL의 결과로 생성되는 데이터베이스 구조와 관련 통계 정보가 저장된다,
• 이러한 정보를 메타데이터라고 부른다.
• 사용자와 DBMS 모두 접근 가능하다.

 

2) 시스템 카탈로그의 내용

정보	설명
릴레이션 관련 정보	이름, 저장된 파일 정보, 속성 정보, 인덱스 이름, 무결설 제약 조건 등
인덱스 관련 정보	이름, 구조, 인덱스 키 정보 등
뷰 관련 정보	이름, 정의, 소유자 등
통계 관련 정보	릴레이션과 인덱스의 카디널리티, 인덱스의 높이와 범위 등
사용자 관련 정보	계정과 권한 정보

 

3) 시스템 카탈로그 특징

• 시스템 카탈로그 자체도 시스템테이블로 구성되어 있으며, 사용자는 SQL을 사용하여 내용을 조회할 수 있다.
• DBMS에 의해 생성되고 유지된다.
• 사용자는 시스템 카탈로그를 직접 갱신할 수 없다.