[정보처리기사 실기 공부] 데이터베이스 물리속성 설계

데이터베이스 물리속성 설계: 파티셔닝, 샤딩, 클러스터링, 인덱싱, 뷰, 시스템 카탈로그

안녕하세요! 오늘은 데이터베이스 물리속성 설계에 대해 알아보겠습니다. 데이터베이스를 효율적으로 관리하고 성능을 향상시키는 데 중요한 요소들을 살펴보겠습니다.

---

파티셔닝

• 개념
  • 데이터베이스를 여러 부분으로 분할하는 것

샤딩(Sharding)

• 개념
  • 하나의 거대한 데이터베이스나 네트워크 시스템을 여러 개의 작은 조각으로 나누어 분산 저장하여 관리하는 것

파티셔닝의 장/단점

• 장점
  • 가용성(Availability)
    • 물리적인 파티셔닝(Partitioning)으로데이터 가용성이 향상
    • 인해 전체 데이터의 훼손 가능성이 줄어들고
  • 관리용이성(Manageability)
    • 각 분할 영역(파티션별로)을 독립적으로 백업하고 복구
  • 성능(Performance)
    • 특정 DML과 Query의 성능을 향상

• 단점
  • Table 간의 Join에 대한 비용이 증가
  • Table과 Index를 별도로 파티션할 수는 없다.

파티셔닝의 종류

• 수평 분할(Horizontal Partitioning)
  • 하나의 테이블의 각 행들을 분할
• 수직 분할(Vertical Partitioning)
  • 테이블의 일부를 컬럼을 기준으로 분할

분할 기준

• 범위 분할(Range Partitioning)
  • Partition Key의 연속된 범위로 파티션을 정의
• 목록 분할(List Partitioning)
  • 특정 Partition에 저장될 Data에 대한 명시적 제어
• 해시 분할(Hash Partitioning)
  • 파티션 키값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션 매핑
• 라운드 로빈 분할(Round Robin Partitioning)
  • 데이터를 균일하게 분배해서 저장하는 방식
• 합성 분할(Composite Partitioning)
  • 위의 기술들을 복합적으로 사용하는 방법

파티션 생성 예제

CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH(MONTH(order_date)) (
    PARTITION p0 VALUES LESS THAN (2010) (
        SUBPARTITION s0,
        SUBPARTITION s1,
        SUBPARTITION s2
    ),
    PARTITION p1 VALUES LESS THAN MAXVALUE (
        SUBPARTITION s3,
        SUBPARTITION s4,
        SUBPARTITION s5
    )
);

 

 

클러스터

• 개념
  • 디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장시키는 방법

• 특징
  • 그룹된 데이터들이 같은 데이터 Block에
  • 저장되기 때문에 디스크 I/O를 줄여준다.
  • 클러스터된 테이블 사이에 조인이 발생할
  • 경우 처리 시간이 단축
  • 클러스터는 데이터 조회 성능을 향상시키지만
  • 데이터 저장, 수정, 삭제나 Full Scan 시 성능 저하

• 클러스터 대상 테이블
  • 분포도가 넓은 테이블
  • 대량의 범위를 자주 조회하는 테이블
  • 입력, 수정, 삭제가 자주 발생하지 않는 테이블
  • 자주 JOIN되어 사용되는 테이블
  • ORDER BY, GROUP BY, UINION이 빈번한 테이블

인덱스(Index)

• 개념
  • 추가적인 저장 공간을 활용하여 데이터베이스
  • 테이블의 검색 속도를 향상시키기 위한 자료구조

인덱스의 개념

 

인덱스를 사용하는 이유

• 규모가 큰 테이블에서 데이터를 빠르게 추출
• 조건 검색 절의 효율성
• 정렬 절의 효율성
• MIN, MAX의 효율적인 처리
• JOIN을 실행할 때 다른 테이블에서 열을 추출

인덱스의 종류

• 클러스터 인덱스
  • 테이블당 1개만 허용되며, 해당 컬럼을
  • 기준으로 테이블이 물리적으로 정렬
• 넌클러스터 인덱스
  • 테이블당 약 240개의 인덱스 생성 가능
  • 넌클러스터 인덱스에서 어떤 데이터(인덱스)를 태우게 할 것인지 결정하는 것이 hint라고 한다.

• 밀집 인덱스
  • 데이터 레코드 각각에 대해 하나의 인덱스가 만들어짐
• 희소 인덱스
  • 레코드 그룹 또는 데이터 블록에 대해 하나의 인덱스가 만들어짐

 

인덱스의 구조

• 트리 기반 인덱스
• 비트맵 인덱스
• 함수 기반 인덱스
• 비트맵 조인 인덱스
• 도메인 인덱스

인덱스 컬럼의 선정

• 분포도가 좋은(10%~15%)컬럼
• 자주 조합되어 사용되는 경우 결합 인덱스를 생성

인덱스 생성 / 삭제 예제

 

생성

CREATE CLUSTERED INDEX idx_sales_date ON sales(sales_date);

 

삭제

DROP INDEX idx_sales_date ON sales;

 

 

뷰(View)

• 개념
  • 하나 이상의 기본 테이블로부터 유도된 이름을 가지는 가상 테이블
• 특징
  • 뷰는 기본 테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 사용하며, 조작도 기본 테이블과 거의 같다.
  • 뷰는 가상 테이블이기 때문에 물리적으로 구현되어 있지 않다.
  • 데이터의 논리적 독립성을 제공할 수 있다.

뷰의 장 / 단점

장점	논리적 데이터 독립성을 제공한다.
	동일 데이터에 대해 동시에 여러 사용자의 상이한 요구를 지원해 준다.
단점	독립적인 인덱스를 가질 수 없다.
	ALTER VIEW문을 사용할 수 없다.

 

뷰 생성 / 삭제 예제

 

생성

CREATE VIEW customer_orders AS
SELECT c.id, c.name, o.order_date, o.amount
FROM customer c
JOIN orders o ON c.id = o.customer_id;

 

삭제

DROP VIEW customer_orders;

 

시스템 카탈로그

• 데이터베이스에 저장되어 있는 모든 데이터 개체들에 대한 정의나 명세에 대한 정보가 수록되어 있는 시스템 테이블
• 시스템 카탈로그를 데이터 사전(Data Dictionary)이라고 한다.
• 시스템 카탈로그에 저장된 내용을 메타 데이터라고 한다.
• 시스템 카탈로그는 사용자와 데이터베이스 관리 시스템의 접근이 가능하다.

시스템 카탈로그의 특징

• 시스템 카탈로그 자체도 시스템 테이블로 구성되어 있어 사용자가 SQL문을 이용하여 내용을 검색해 볼 수 있다. 

시스템 카탈로그의 종류 (오라클 기준)

종류	설명
SYSTABLES	기본 테이블 및 뷰 테이블의 정보를 저장하는 테이블
SYSCOLUMNS	모든 테이블에 대한 정보를 열(속성) 중심으로 저장하는 테이블
SYSVIEW	뷰에 대한 정보를 저장하는 테이블
SYSTABAUTH	테이블에 설정된 권한 사항들을 저장하는 테이블
SYSCOLAUTH	각 속성에 설정된 권한 사항들을 저장하는 테이블

 

---

이렇게 데이터베이스 물리적 설계에 대해 알아보았습니다. 각 요소의 특징과 장단점을 이해하고 데이터베이스 시스템을 효율적으로 구축하는 데 도움이 되기를 바랍니다. 2024년에 정보처리기사를 따기 위해 노력하는 모두에게 행운을 빕니다!