데이터베이스 설계: 물리적 설계

데이터베이스 물리적 설계의 중요성

데이터베이스 물리적 설계는 논리적 데이터 모델을 기반으로 실제 데이터베이스 시스템에 데이터를 저장하고 접근하는 방법을 결정하는 중요한 단계입니다. 효율적인 물리적 설계는 데이터베이스 성능, 저장 공간 활용도, 유지보수성 등에 큰 영향을 미칩니다.

물리적 설계 주요 고려 사항

  • 테이블 스페이스 설계: 데이터 파일들을 논리적으로 그룹화하여 관리하고, I/O 성능을 최적화합니다.
  • 인덱스 설계: 쿼리 성능을 향상시키기 위해 필요한 인덱스를 생성하고 관리합니다.
  • 파티셔닝 전략: 대용량 테이블을 관리하기 쉽게 분할하고, 쿼리 성능을 개선합니다.
  • 데이터 타입 선정: 각 컬럼에 적합한 데이터 타입을 선택하여 저장 공간을 효율적으로 활용하고 데이터 무결성을 확보합니다.
  • 데이터 압축: 저장 공간을 절약하고 I/O 성능을 개선합니다.

테이블 스페이스 설계

테이블 스페이스는 데이터 파일들을 논리적으로 묶어 관리하는 단위입니다. 적절한 테이블 스페이스 설계는 데이터베이스 성능과 관리 용이성에 기여합니다.

테이블 스페이스 종류

  • SYSTEM 테이블 스페이스: 데이터 딕셔너리 등 시스템 관련 정보를 저장합니다.
  • SYSAUX 테이블 스페이스: 시스템 컴포넌트들이 사용하는 테이블 스페이스입니다.
  • USERS 테이블 스페이스: 사용자 데이터 및 인덱스를 저장합니다.
  • TEMP 테이블 스페이스: 정렬 작업 등 임시 데이터를 저장합니다.
  • UNDO 테이블 스페이스: 트랜잭션 롤백 정보를 저장합니다.

테이블 스페이스 생성 예시

다음은 사용자 데이터 저장을 위한 테이블 스페이스를 생성하는 예시입니다.


CREATE TABLESPACE app_data
DATAFILE 'app_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

이 예제는 ‘app_data01.dbf’라는 데이터 파일을 사용하여 ‘app_data’ 테이블 스페이스를 생성합니다. SIZE는 초기 크기를 지정하며, AUTOEXTEND ON은 공간이 부족할 때 자동으로 확장되도록 설정합니다. MAXSIZE는 최대 크기를 제한합니다.

인덱스 설계

인덱스는 테이블의 특정 컬럼을 기준으로 정렬된 데이터 구조입니다. 쿼리에서 WHERE 절에 자주 사용되는 컬럼에 인덱스를 생성하면 해당 쿼리의 성능을 크게 향상시킬 수 있습니다.

인덱스 종류

  • B-트리 인덱스: 일반적인 인덱스로, 대부분의 경우에 적합합니다.
  • 비트맵 인덱스: Cardinality가 낮은 컬럼에 적합합니다.
  • 함수 기반 인덱스: 컬럼의 특정 함수 결과에 대한 인덱스로, 함수를 사용하는 쿼리의 성능을 향상시킵니다.
  • 파티션 인덱스: 파티셔닝된 테이블에 대한 인덱스로, 관리 및 쿼리 성능을 최적화합니다.

인덱스 생성 예시

다음은 ’employees’ 테이블의 ‘last_name’ 컬럼에 B-트리 인덱스를 생성하는 예시입니다.


CREATE INDEX idx_employees_lname ON employees (last_name);

함수 기반 인덱스 생성 예시

다음은 ’employees’ 테이블의 ’email’ 컬럼을 소문자로 변환한 값에 대한 함수 기반 인덱스를 생성하는 예시입니다.


CREATE INDEX idx_employees_email_lower ON employees (LOWER(email));

파티셔닝 전략

파티셔닝은 대용량 테이블을 더 작고 관리하기 쉬운 파티션으로 나누는 기술입니다. 이를 통해 쿼리 성능을 향상시키고, 데이터 관리 작업을 효율적으로 수행할 수 있습니다.

파티셔닝 종류

  • 범위 파티셔닝: 컬럼 값의 범위를 기준으로 파티션을 분할합니다.
  • 해시 파티셔닝: 해시 함수를 사용하여 파티션을 분할합니다.
  • 리스트 파티셔닝: 특정 컬럼 값 목록을 기준으로 파티션을 분할합니다.
  • 복합 파티셔닝: 여러 파티셔닝 방법을 결합하여 사용합니다.

범위 파티셔닝 예시

다음은 ‘sales’ 테이블을 ‘sales_date’ 컬럼을 기준으로 월별로 파티셔닝하는 예시입니다.


CREATE TABLE sales (
    sales_id NUMBER,
    sales_date DATE,
    product_id NUMBER,
    quantity NUMBER
)
PARTITION BY RANGE (sales_date) (
    PARTITION sales_q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION sales_q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION sales_q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
    PARTITION sales_q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

데이터 타입 선정

각 컬럼에 적합한 데이터 타입을 선택하는 것은 데이터베이스 성능과 저장 공간 활용도에 중요합니다.

주요 데이터 타입

  • NUMBER: 숫자 데이터를 저장합니다. 정수, 실수 등을 표현할 수 있습니다.
  • VARCHAR2: 가변 길이 문자열 데이터를 저장합니다. 최대 길이를 지정해야 합니다.
  • DATE: 날짜 및 시간 데이터를 저장합니다.
  • TIMESTAMP: DATE보다 더 정밀한 날짜 및 시간 데이터를 저장합니다.
  • CLOB/BLOB: 대용량 문자열 또는 이진 데이터를 저장합니다.
  • JSON: JSON 형식 데이터를 저장합니다.

데이터 타입 선정 시 고려 사항

  • 데이터의 특성: 숫자, 문자열, 날짜 등 저장할 데이터의 종류를 고려합니다.
  • 데이터의 크기: 예상되는 데이터의 최대 크기를 고려하여 충분한 공간을 확보합니다.
  • 데이터의 무결성: 데이터 타입에 따른 제약 조건을 활용하여 데이터 무결성을 유지합니다.
  • 성능: 데이터 타입에 따라 쿼리 성능이 달라질 수 있습니다.

데이터 압축

데이터 압축은 테이블이나 인덱스에 저장된 데이터의 크기를 줄이는 기술입니다. 압축을 통해 저장 공간을 절약하고, I/O 성능을 개선할 수 있습니다.

데이터 압축 종류

  • 기본 테이블 압축: 동일한 데이터 블록 내에서 반복되는 값을 제거하여 압축합니다.
  • OLTP 테이블 압축: DML 작업이 많은 환경에서 성능 저하를 최소화하면서 압축합니다.
  • 고급 행 압축: Exadata 환경에서 더 높은 압축률을 제공합니다.

테이블 압축 예시

다음은 ‘orders’ 테이블에 기본 테이블 압축을 적용하는 예시입니다.


CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER,
    order_date DATE,
    ...
) COMPRESS;

이미 존재하는 테이블에 압축 설정을 적용하려면 ALTER TABLE 명령을 사용합니다.


ALTER TABLE orders COMPRESS;

물리적 설계 검토 및 개선

물리적 설계는 데이터베이스 운영 환경과 워크로드에 따라 지속적으로 검토하고 개선해야 합니다. 다음은 물리적 설계 검토 및 개선 시 고려할 사항입니다.

  • 쿼리 성능 모니터링: 쿼리 실행 계획을 분석하고, 비효율적인 부분을 개선합니다.
  • 인덱스 사용률 분석: 사용되지 않는 인덱스를 제거하고, 필요한 인덱스를 추가합니다.
  • 파티션 관리: 파티션 크기를 조정하고, 파티셔닝 전략을 변경합니다.
  • 저장 공간 사용률 모니터링: 테이블 스페이스 사용률을 모니터링하고, 필요에 따라 확장합니다.

결론

효율적인 데이터베이스 물리적 설계는 데이터베이스 성능을 극대화하고, 관리 효율성을 높이는 데 필수적입니다. 다양한 물리적 설계 기술을 이해하고, 데이터베이스 환경과 워크로드에 맞게 적용하는 것이 중요합니다.

위로 스크롤