데이터베이스 구축-2

DROP VIEW 뷰명;

파티셔닝

데이터베이스를 여러 부분으로 분할하는 것으로 데이터가 너무 커져서 조회하는 시간이 길어질 때 또는 관리 용이성, 성능, 가용성 등의 향상을 이유로 분할

하나의 데이터베이스 내에서 테이블을 논리적으로 나누는 방법

 

장점

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

단점

• 테이블간의 조인에 대한 비용이 증가
• 테이블과 인덱스를 별도로 파티션할 수 없다. 

종류

1) 수평 분할

• 하나의 테이블의 각 행들을 분할
• 스키마를 복제한 후 샤드키를 기준으로 데이터를 나눈다.

2) 수직 분할

• 테이블의 일부를 컬럼을 기준으로 분할
• 자주 사용하는 컬럼 등을 분리시켜 성능을 향상
• 하나의 테이블을 2개 이상으로 분리하는 작업

분할 기준

1) 범위 분할

• Partition Key의 연속된 범위로 파티션을 정의
• 파티션 키 위주로 검색이 자주 실행될 경우 유용
• ex) 월별, 분기별 등
• 예시

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date) * 100 + MONTH(sale_date)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION p202304 VALUES LESS THAN (202305),
    PARTITION p202305 VALUES LESS THAN (202306),
    PARTITION p202306 VALUES LESS THAN (202307)
);

 

2) 목록 분할

• 특정 Partition에 저장될 Data에 대한 명시적 제어
• 많은 SQL에서 해당 컬럼 조건이 많이 들어오는 경우 유용
• ex) [한국, 일본, 중국 -> 아시아 ]
• 예시

CREATE TABLE customers (
    id INT,
    name VARCHAR(50),
    region VARCHAR(20),
    PRIMARY KEY (id, region)
)
PARTITION BY LIST (region) (
    PARTITION pNorth VALUES IN ('North', 'N'),
    PARTITION pSouth VALUES IN ('South', 'S'),
    PARTITION pEast VALUES IN ('East', 'E'),
    PARTITION pWest VALUES IN ('West', 'W')
);

 

3) 해시 분할

• 파티션 키값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션 매핑
• 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리
• 병렬처리 시 성능효과 극대화

4) 라운드 로빈 분할

• 데이터를 균일하게 분배해서 저장하는 방식

5) 합성 분할

• 위의 기술들을 복합적으로 사용하는 방법

샤딩

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

단일의 데이터베이스에서 저장하기 너무 클 때 사용하여 데이터를 구간 별로 쪼개어 나눔으로서 노드에 무겁게 가지고 있던 데이터를 빠르게 검증할 수 있어 트랜잭션 속도를 향상시킬 수 있음

여러 독립된 데이터베이스 서버에 물리적으로 분할하는 방법

 

클러스터

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

데이터 저장 시 데이터 액세스 효율을 향상시키기 위해 동일한 성격의 데이터를 동일한 데이터 블록에 저장하는 물리적 저장 방법

=> 비슷한 위치에 모아두는 것 !

 

클러스터 대상 테이블

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

인덱스 ( Index )

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

테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회가 가능

 

1) 인덱스 생성

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

 

예시

CREATE INDEX idx_name_department ON employees (name, department);

위 코드와 같이 컬럼명을 2개를 넣어주면, 여러 열을 포함하는 복합 인덱스가 생성된다.

 

2) 인덱스 삭제

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

 

3) 인덱스 재구성

ALTER TABLE 테이블명 ENGINE=InnoDB;

 

뷰 (View)

뷰는 하나 이상의 기본 테이블로부터 유도된 이름을 가지는 가상 테이블로 실제로 데이터를 가지고 있지 않으며, 논리적으로만 존재하여 논리적 독립성을 제공할 수 있다.

뷰는 기본 테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 사용하며, 조작도 기본 테이블과 거의 동일하다.

필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해진다. 

뷰는 CREATE로 생성하고, DROP으로 삭제한다. ( ALTER로 변경이 불가능하다 )

 

뷰 생성 기본 문법

CREATE VIEW 뷰이름 AS
SELECT문;

 

예시

CREATE VIEW employee_view AS
SELECT employee_id, name, department
FROM employees
WHERE department = 'Sales';

 

뷰 삭제 기본 문법

DROP VIEW 뷰명;

 

시스템 카탈로그

데이터베이스에 저장되어 있는 모든 데이터 개체들에 대한 정의나 명세에 대한 정보가 수록되어 있는 시스템 테이블로 데이터 사전(Data Dictionary)이라고도 한다.

시스템 카탈로그에 저장된 내용을 메타데이터라고 한다.

시스템 카탈로그는 사용자와 데이터베이스 관리 시스템의 접근이 가능하다. 

 

특징

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

데이터베이스 관리 시스템에 의해 생성되고 유지된다.