22. DB 물리속성 설계
Partitioning
개념
DB를 여러 부분으로 분할하는 것
데이터가 너무 커져서, 조회하는 시간이 길어질 때 또는 관리 용이성, 성능, 가용성 등의 향상을 이유로 분할
Sharding
하나의 거대한 DB나 네트워크 시스템을 여러 개의 작은 조각으로 나누어 분산저장하여 관리하는 것
단일의 DB에서 저장하기 너무 클 때 사용하여 데이터를 구간별로 쪼개어 나눔으로써 노드에 무겁게 가지고 있던 데이터를 빠르게 검증할 수 있어 빠른 트랜잭션 속도를 향상시킬 수 있음
샤딩을 통해 나누어진 블록들의 구간(epoch)을 샤드(shard)라고 부름
장 & 단점
장점
가용성 : 물리적인 파티셔닝으로 인해 전체 데이터의 훼손 가능성이 줄어들고 데이터 가용성이 향상
관리용이성 : 각 파티션 별 분할영역을 독립적으로 백업하고 복구
성능 : 특정 DML과 Query의 성능을 향상
단점
테이블 간의 Join에 대한 비용이 증가
테이블과 인덱스를 별도로 파티션 할 수 없음
종류
수평 분할 | Horizontal Partitioning
하나의 테이블의 각 행들을 분할
스키마를 복제한 후 샤드키를 기준으로 데이터를 나눔
수직 분할 | Vertical Partitioning
테이블의 일부를 컬럼을 기준으로 분할
자주 사용하는 컬럼 등을 분리시켜 성능을 향상
하나의 테이블을 2개 이상으로 분리하는 작업
분할 기준
범위 분할 | Range Partiioning
파티션 키의 연속된 범위로 파티션을 정의
파티션 키 위주로 검색이 자주 실행될 경우 유용
- ex) 월별, 분기별 등
목록 분할 | List Partitioning
특정 파티션에 저장될 데이터에 대한 명시적 제어
많은 SQL에서 해당 열의 조건이 많이 들어오는 경우 유용
- ex) 한국, 일본 -> 아시아
해시 분할 | Hash Partitioning
파티션 키 값에 해시 함수를 적용하고, 거기서 반환될 값으로 파티션 매핑
데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리
병렬 처리 시 성능효과 극대화
라운드 로빈 분할 | Round Robin Partitioning
- 데이터를 균일하게 분배해서 저장하는 방식
합성(조합) 분할 | Composite Partitioning
- 위의 기술들을 복합적으로 사용하는 방법
- ex) 범위 분할 후 분할된 데이터의 해시 분할 등
Cluster
개념
디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장시키는 방법
데이터 저장 시 데이터 액세스 효율을 향상시키기 위해 동일한 성격의 데이터를 동일한 데이터 블록에 저장하는 물리적 저장방법
특징
그룹화된 데이터들이 같은 데이터 블록에 저장되기 때문에 디스크 I / O를 줄여줌
클러스터된 테이블 사이에 조인이 발생할 경우 처리 시간이 단축
클러스터는 데이터 조회 성능을 향상시키지만 데이터 저장, 수정, 삭제나 Full Scan 시 성능 저하
클러스터는 데이터의 분포도가 넓을수록 유리함
파티셔닝된 테이블에는 클러스터링 불가
클러스터링된 테이블에 클러스터드 인덱스를 생성하면 접근 성능 향상
대상 테이블
분포도가 넓은 테이블
대량의 범위를 자주 조회하는 테이블
입력, 수정, 삭제가 자주 발생하지 않는 테이블
자주 조인되어 사용되는 테이블
ORDER BY, GROUP BY, UNION 이 빈번한 테이블
Index
개념
추가적인 저장 공간을 활용하여 DB 테이블의 검색 속도를 향상시키기 위한 자료구조
책의 맨 앞 또는 맨 뒤에 색인을 추가하는데, DB의 index는 책의 색인과 같음
테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 떄문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회
기본키의 경우, 자동으로 인덱스가 생성되며 인덱스 구축 시 두 개 이상의 컬럼을 결합하여 인덱스를 생성 할 수 있음
사용 이유
규모가 작지 않은 테이블에서 데이터를 빠르게 추출
조건 검색 절의 효율성
정렬 절의 효율성
MIN, MAX 의 효율적인 처리
JOIN 을 실행할 때 따른 테이블에서 열을 추출
종류
클러스터 인덱스
테이블당 1개만 허용되며 해당 컬럼을 기준으로 테이블이 물리적으로 정렬
데이터는 기본적으로 오름차순으로 정렬을 진행
기본 키를 설정하면 자동으로 클러스터드 인덱스가 적용
인덱스 자체의 리프 페이지가 곧 데이터
데이터 입력, 수정, 삭제 시 항상 정렬 상태를 유지
비 클러스터형 인덱스보다 검색 속도는 빠르나, 데이터의 입력, 수정, 삭제 시에는 느림
인덱스의 엔트리 순서가 레코드의 물리적 순서와 동일하게 유지
넌클러스터 인덱스
테이블당 약 240개의 인덱스 생성 가능
레코드의 원본은 정렬되지 않고, 인덱스 페이지만 정렬
인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터(RID)이기 떄문에 클러스터형보다 검색 속도는 더 느리지만 데이터의 입력, 수정, 삭제는 더 빠름
인덱스를 생성할 때 데이터 페이지는 그냥 둔 상태에서 별도의 인덱스 페이지를 따로 만들기 때문에 용량을 더 차지
보조 인덱스
- 탐색키 값에 따라 정렬되지 않은 데이터 파일에 대하여 정의
밀집 인덱스
- 데이터 레코드 각각에 대해 하나의 인덱스가 만들어짐
희소 인덱스
- 레코드 그룹 또는 데이터 블록에 대해 하나의 인덱스가 만들어짐
구조
트리 기반 인덱스
대부분 상용 DBMS에서는 트리 구조를 기반으로 하는 B+ 트리 인덱스를 주로 사용
B+ 트리는 루트에서 리프(leaves)노드까지 모든 경로의 깊이가 같은 밸런스 트리 형태
다른 인덱스에 비해 대용량 처리의 데이터 삽입과 삭제에 좋은 성능 유지
B- 트리 인덱스는 분기를 목적으로 하는 Branch Block을 가지고 있음
비트맵 인덱스
비트를 이용하여 컬럼값을 저장하고 이를 이용하여 주소를 자동으로 생성하는 인덱스
주어진 키 값을 포함하고 있는 로우에 대한 주소 정보를 제공하는 것
함수 기반 인덱스
함수나 수식으로 계산된 결과에 대해 B+ 트리 인덱스나 비트맵 인덱스를 생성, 사용할 수 있는 기능 제공
사용되는 함수 : 산술식(Arithmetic Expression), PL / SQL, Function, SQL Function, Package, C callout
비트맵 조인 인덱스
비트맵 조인 인덱스의 물리적인 구조는 비트맵 인덱스와 완전히 동일
인덱스의 구성 컬럼이 베이스 테이블의 컬럼 값이 아니라 조인된 테이블의 컬럼 값이라는 차이
도메인 인덱스
- 개발자가 자신이 원하는 인덱스 타입을 생성할 수 있게 함으로써 시스템에 많은 확장 가능
컬럼의 선정
분포도가 좋은(10%~15%) 컬럼
자주 조합되어 사용되는 경우 결합 인덱스 생성
가능한 수정이 빈번하지 않은 컬럼을 선정
한 컬럼이 여러 인덱스에 포함되지 않도록 설계
기본키 및 외부키가 되는 컬럼을 선정
생성 시 고려사항
새로 추가된 인덱스는 기본 액세스 경로에 영향을 미칠 수 있음
지나치게 많은 인덱스는 오버헤드를 발생
넓은 범위를 인덱스로 처리 시 많은 오버헤드 발생
옵티마이저를 위한 통계 데이터를 주기적으로 갱신
인덱스를 위한 추가적인 저장공간이 필요
View
개념
하나 이상의 기본 테이블로 유도된, 이름을 가지는 가상 테이블
뷰는 실제로 데이터를 저장하고 있지 않으며, 논리적으로만 존재
DB 사용자는 실제로 데이터가 존재하는 테이블과 동일하게 뷰를 조작
DB 뷰 정의 명령 :
create view v as \
특징
기본 테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 사용하며, 조작도 기본 테이블과 거의 같음
가상 테이블이기 때문에 물리적으로 구현되어 있지 않음
필요한 데이터만 뷰로 정의해서 처리할 수 있기 떄문에 관리가 용이하고 명령문이 간단해짐
뷰를 통해서만 데이터에 접근하게 하면 뷰에 나타나지 않는 데이터를 안전하게 보호
데이터의 삽입, 삭제, 갱신에는 제한이 있음
뷰가 정의된 기본 테이블이나 뷰를 삭제하면 그 테이블이나 뷰를 기초로 정의된 다른 뷰도 자동으로 삭제
CREATE로 생성, DROP으로 삭제
장 & 단점
장점
논리적 데이터 독립성 제공
동일 데이터에 대해 동시에 여러 사용자의 상이한 응용이나 요구를 지원
사용자의 데이터관리를 간단하게 해줌
접근 제어를 통한 자동 보안이 제공
단점
독립적인 인덱스를 가질 수 없음
ALTER VIEW문을 사용할 수 없음
뷰로 구성된 내용에 대한 삽입, 삭제, 갱신, 연산에 제약이 따름
테이블 저장 사이징
DB 용량 분석
정확한 데이터 용량을 산정하여 디스크 사용의 효율성을 높임
업무량이 집중되어 있는 디스크를 분리, 설계하여 디스크에 대한 입출력 부하 분산
데이터량이 많고 데이터의 증가량이 많을수록 디스크에 대한 입출력 분산이 철저하게 고려되어야 성능 향상
여러 프로세스가 동시에 접근할 때 발생하는 디스크 입출력 경합을 최소화하여 데이터의 접근 성능 향상
테이블 사이징
초기 크기 계산
테이블의 보관 주기를 확정하며 초기건수, 증가건수, 최대건수를 정의
테이블의 컬럼의 Length를 모두 더함
물리적인 공간의 확보가 가능하다면 테이블의 초기크기는
최대건수 X 컬럼 Length의 총합
확장 크기 계산
- 초기 크기 작업 후 테이블의 데이터 증가에 따라 달라짐
테이블 용량 계산
총 블록 헤드 계산
블록당 가능한 데이터 영역 계산
평균 행의 전체 길이 계산
총 평균 행 크기 계산
데이터 블록 내의 평균 행 수 계산
테이블에 요구하는 블록과 바이트 수를 계산
인덱스 용량 계산
총 블록 헤드 계산
블록당 가능한 데이터 영역 계산
결합된 열 길이 계산
인덱스 값 크기의 전체 평균 계산
DB Backup
개념
정전, 사이버 공격 및 다른 중단 사태에 대비하여 복구를 진행할 수 있도록 데이터를 주기적으로 복사하는 것을 의미
백업: 자료들을 복사, 보관복원: 손상된 DB를 복구
방식
전체 백업 | Full Backup
- 선택된 폴더의 데이터를 모두 백업
증분 백업 | Incremental Backup
- Full 백업 이후 변경 / 추가된 데이터만 백업
차등 백업 | Differential Backup
- Full 백업 이후 변경 / 추가된 데이터를 모두 포함하여 백업
실시간 백업 | RealTime Backup
- 즉각적으로 모든 변경사항을 분리된 스토리지 디바이스에 복사
트랜잭션 로그 백업 | Transaction log Backup
DB에서 실행되는 모든 SQL문을 기록한 로그
REDO(다시 실행), UNDO(원상태로 복구)로 복원
CHECK POINT : 설정한 지점 이전까지는 트랜잭션이 성공적으로 수행되어 disk에 확실히 저장된 상태
RTO & RPO
복구 시간 목표 | RTO
서비스 중단 시점과 서비스 복원 시점 간에 허용되는 최대 지연 시간
서비스를 사용할 수 없는 상태로 허용되는 기간
복구 시점 목표 | RPO
마지막 데이터 복구 시점 이후 허용되는 최대 시간
마지막 복구 시점과 서비스 중단 시점 사이에 허용되는 데이터 손실량