📌 [정보처리기사] 물리 데이터베이스 설계 - 뷰(View) 설계/클러스터 설계/파티션 설계/용량 설계

 

 

목차

 

🎯 뷰(View) 설계

 

뷰(View)의 개념

📌 뷰는 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블이다.

 

• 뷰는 저장장치 내에 물리적으로 존재하지 않지만, 사용자에게는 있는 것처럼 간주된다.
• 뷰는 데이터 보정 작업, 처리 과정 시험 등 임시적인 작업을 위한 용도로 활용된다.
• 뷰는 조인문의 사용 최소화로 사용상의 편의성을 최대화한다.
• 뷰를 생성하면 뷰 정의 시스템 내에 저장되었다가 생성된 뷰 이름을 질의어(예를 들면 SQL)에서 사용할 경우 질의어가 실행될 때 부에 정의된 기본 테이블로 대체되어 기본 테이블에 대해 실행된다.
• 다음 그림은 뷰 A가 테이블1, 테이블2, 테이블3에서 유도되어 생성되며, 뷰 A를 통해 테이블1, 테이블2, 테이블3 에 대한 데이터에 접근할 수 있음을 나타낸 것이다.

 

 

 

뷰(View)의 특징

• 뷰는 기본 테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 사용하며, 조작도 기본 테이블과 거의 같다.
• 뷰는 가상 테이블이기 때문에 물리적으로 구현되어 있지 않다.
• 데이터의 논리적 독립성을 제공할 수 있다.
• 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해진다.
• 뷰를 통해서만 데이터에 접근하게 하면 뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있다.
• 기본 테이블의 기본키를 포함한 속성(열) 집합으로 뷰를 구성해야만 삽입, 삭제, 갱신, 연산이 가능하다.
• 일단 정의된 뷰는 다른 뷰의 정의에 기초가 될 수 있다.
• 뷰가 정의된 기본 테이블이나 뷰를 삭제하면 그 테이블이나 뷰를 기초로 정의된 다른 뷰도 자동으로 삭제된다.
• 뷰를 정의할 때는 CREATE문, 제거할 때는 DROP문을 사용한다.

 

💡 정의 테이블 : 뷰는 하나 이상의 다른 테이블로부터 유도된 하나의 가상 테이블이며, 뷰를 만들기 위해 유도된 기본 테이블을 정의 테이블이라 한다.

 

 

 

뷰(View)의 장, 단점

장점	단점
- 논리적 데이터 독립성을 제공한다. - 동일 데이터에 대해 동시에 여러 사용자의 상이한 응용이나 요구를 지원해 준다. - 사용자의 데이터 관리를 간단하게 해준다. - 접근 제어를 통한 자동 보안이 제공된다.	- 독립적인 인덱스를 가질 수 없다. - 뷰의 정의를 변경할 수 없다. - 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신 연산에 제약이 따른다.

 

 

 

뷰 설계 순서

1. 대상 테이블을 선정한다.
   • 외부 시스템과 인터페이스에 관여하는 테이블
   • CRUD 매트릭스를 통해 여러 테이블이 동시에 자주 조인되어 접근되는 테이블
   • SQL문 작성 시 거의 모든 문장에서 인라인 뷰 방식으로 접근되는 테이블
2. 대상 컬럼을 선정한다.
   • 보안을 유지해야 하는 컬럼은 주의하여 선별한다.
3. 정의서를 작성한다.
   • 뷰 정의서 예시

뷰명	뷰 설명	관련 테이블	관련 컬럼명	데이터 타입
V_SELLITEM	판매와 판매목록 처리	SELL	SELLNO SELLNAME SELLDATE	VARCHAR(5) VARCHAR(40) DATE
		SELLITEM	ITEMNO PRICE	VARCHAR(5) NUMBER(10)

 

 

💡 서브쿼리가 FROM 절 안에서 사용되는 경우, 해당 서브쿼리를 '인라인 뷰'라고 한다. FROM절에서 사용된 서브쿼리의 결과가 하나의 테이블에 대한 뷰(View)처럼 사용된다.
💡 서브쿼리란 ? SELECT 문 안에 다시 SELECT 문이 기술된 형태의 쿼리(QUERY)이다.

 

 

 

뷰 설계 시 고려사항

• 테이블 구조가 단순화 될 수 있도록 반복적으로 조인을 설정하여 사용하거나 동일한 조건절을 사용하는 테이블을 뷰로 생성한다.
  • 예) <주문> 테이블과 <거래처> 테이블을 조인하여 사용하는 경우가 많다면 <주문> 테이블과 <거래처> 테이블에서 필요한 필드로 구성된 뷰를 생성한다.
• 동일한 테이블이라도 업무에 따라 테이블을 이용하는 부분이 달라질 수 있으므로 사용할 데이터를 다양한 관점에서 제시해야 한다.
  • 예) <회원> 테이블은 '회원번호', '이름', '주소', '전화번호', '총구매금액' 필드로 구성되어 있는데, 제품 발송 업무를 처리할 때는 '이름', '주소', '전화번호' 필드만 필요하므로 이 3개의 필드로 구성된 뷰를 설계한다.
• 데이터의 보안 유지를 고려하여 설계한다.
  • 예) <회원> 테이블의 '총구매횟수', '총구매금액' 등은 회사 차원에서 중요한 자료일 수 있으므로 발송 담당자가 볼 수 없도록 뷰를 설계한다.

 

---

 

 

🎯 클러스터 설계

 

클러스터(Cluster)의 개요

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

 

• 클러스터링 키로 지정된 컬럼 값의 순서대로 저장되고, 여러 개의 테이블이 하나의 클러스터에 저장된다.

🔔 예) 클러스터링 되지 않은 테이블

부서번호	부서
b001	기획부
b002	총무부
b003	영업부

 

<부서> 테이블

사원번호	이름	부서번호
1	사쿠라	b001
2	김채원	b001
3	허윤진	b002
4	카즈하	b003
5	김가람	b002

 

🔔 예) 클러스터링된 테이블

부서번호		부서
b001		기획부
	사원번호		이름
	1		사쿠라
	2		김채원
b002		총무부
	3		허윤진
	5		김가람
b003		영업부
	4		카즈하

 

💡 <부서> 테이블과 <사원> 테이블이 '부서번호' 필드를 기준으로 클러스터링 되었다. 이런 경우 '부서번호'를 클러스터링 키라고 한다.

 

💡 클러스터링은 비슷한 종류끼리 묶어준다는 의미로 테이블에서는 동일한 성격의 데이터를 동일한 데이터 블록에 저장하는 방법이고, 서버에서는 두 대 이상의 서버를 하나의 서버처럼 운영하기 위한 방법을 의미한다.

💡 클러스터링 키 ? 클러스터링된 테이블에서 각각의 행을 접근할 때 기준이 되는 열로 데이터를 조회하면 클러스터링 키로 지정된 필드에서 시작하여 클러스터링된 테이블의 데이터를 조회한다.

 

 

 

클러스터(Cluster)의 특징

• 클러스터링 된 테이블은 데이터 조회 속도는 향상시키지만 데이터 입력, 수정, 삭제에 대한 성능은 저하시킨다.
• 클러스터는 데이터의 분포도가 넓을수록 유리하다.
• 데이터 분포도가 넓은 테이블을 클러스터링 하면 저장 공간을 절약할 수 있다.
• 클러스터링된 테이블은 클러스터링 키 열을 공유하므로 저장 공간이 줄어든다.
• 대용량을 처리하는 트랜잭션은 전체 테이블을 스캔 하는 일이 자주 발생하므로 클러스터링을 하지 않는 것이 좋다.
• 처리 범위가 넣은 경우에는 단일 테이블 클러스터링을 조인이 많이 발생하는 경우에는 다중 테이블 클러스터링을 사용한다.
• 파티셔닝 된 테이블에는 클러스터링을 할 수 없다.
• 클러스터링을 하면 비슷한 데이터가 동일한 데이터 블록에 저장되기 때문에 디스크 I/O가 줄어든다.
• 클러스터링된 테이블에 클러스터드 인덱스를 생성하면 접근 성능이 향상된다.

 

💡 분포도는 인덱스에서는 분포도가 좁은 테이블이 좋지만 클러스터링은 분포도가 넓은 테이블에 유리하다.
💡 단일 테이블 클러스터링이란 ? 여러개의 테이블 뿐만 아니라 한 개의 테이블에 대해서도 클러스터링을 수행할 수 있다. 특정 컬럼의 동일한 값을 동일 블록이나 연속된 블록에 저장하므로 데이터 조회 성능이 향상된다.
💡 파티셔닝 : 데이터베이스에서의 파티셔닝은 대용량의 테이블이나 인덱스를 작은 논리적 단위인 파티션으로 나누는 것이다.

 

 

 

클러스터 대상 테이블

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

 

💡 ORDER BY 정렬, GROUP BY는 그룹 지정, UNION은 테이블 통합 지정 시 사용하는 SQL 명령어이다.

 

 

 

---

 

 

🎯 파티션 설계

 

파티션(Partition)의 개요

📌 데이터베이스에서 파티션은 대용량의 테이블이나 인덱스를 작은 논리적 단위인 파티션으로 나누는 것을 말한다.

 

• 대용량 DB의 경우 중요한 몇 개의 테이블에만 집중되어 데이터가 증가되므로, 이런 테이블들을 작은 단위로 나눠 분산시키면 성능 저하를 방지할 뿐만 아니라 데이터 관리도 쉬워진다.
• 테이블이나 인덱스를 파티셔닝 하면 파티션 키 또는 인덱스 키에 따라 물리적으로 별도의 공간에 데이터가 저장된다.
• 데이터 처리는 테이블 단위로 이뤄지고, 데이터 저장은 파티션 별로 수행된다.

 

💡 파티션은 용량이 큰 테이블을 관리하기 쉽도록 작은 단위로 나눈 것을 말한다.
💡 파티션 키 : 파티션을 나누는 기준이 되는 열로 파티션 키로 지정된 열의 데이터 값에 따라 파티션이 나뉘어진다.
💡 테이블 단위로 처리 : 하나의 테이블이 여러 개의 파티션으로 나눠져 있어도 DB에 접근하는 애플리케이션은 테이블 단위로 데이터를 처리하기 때문에 파티션을 인식하지 못한다.

 

 

 

파티션의 장, 단점

장점	- 데이터 접근 시 액세스 범위를 줄여 쿼리 성능이 향상된다. - 파티션 별로 데이터가 분산되어 저장되므로 디스크의 성능이 향상된다. - 파티션 별로 백업 및 복구를 수행하므로 속도가 빠르다. - 시스템 장애 시 데이터 손상 정도를 최소화할 수 있다. - 데이터 가용성이 향상된다. - 파티션 단위로 입/출력을 분산시킬 수 있다.
단점	- 하나의 테이블을 세분화 하여 관리하므로 세심한 관리가 요구된다. - 테이블간 조인에 대한 비용이 증가한다. - 용량이 작은 테이블에 파티셔닝을 수행하면 오히려 성능이 저하된다.

 

 

파티션의 종류

📌 파티션의 종류는 파티셔닝 방식에 따라 범위 분할, 해시 분할, 조합 분할, 목록 분할, 라운드 로빈 분할 등으로 나뉜다.

 

범위 분할 (Range Partitioning)	지정한 열의 값을 기준으로 범위를 지정하여 분할한다. 예) 일별, 월별, 분기별 등
해시 분할 (Hash Partitioning)	- 해시 함수를 적용한 결과 값에 따라 데이터를 분할한다. - 특정 파티션에 데이터가 집중되는 범위 분할의 단점을 보완한 것으로 데이터를 고르게 분산할 때 유용하다. - 특정 데이터가 어디에 있는지 판단할 수 없다. - 고객번호, 주민번호 등과 같이 데이터가 고른 컬럼에 효과적이다.
조합 분할 (Composite Partitioning)	- 범위 분할로 분할한 다음 해시 함수를 적용하여 다시 분할하는 방식이다. - 범위 분할한 파티션이 너무 커서 관리가 어려울 때 유용하다.
목록 분할 (List Partitioning)	지정한 열 값에 대한 목록을 만들어 이를 기준으로 분할한다. 예) '국가'라는 열에 '한국', '미국', '일본'이 있는 경우 '미국'을 제외할 목적으로 '아시아'라는 목록을 만들어 분할함
라운드 로빈 분할 (Round Robin Partitioning)	- 레코드를 균일하게 분배하는 방식이다. - 각 레코드가 순차적으로 분배되며, 기본키가 필요 없다.

 

 

• 범위 분할 : '판매일자' 필드를 기준으로 연도별로 파티션을 나눈다.
• 해시 분할 : 해시 함수(F(X))에 '판매일자' 필드를 인수로 입력하여 그 결과값에 따라 파티션을 나눈다.
• 조합 분할 : '판매일자' 필드를 기준으로 연도별로 파티션을 나눈 다음 하나의 파티션 안에서 다시 해시 함수를 적용하여 다시 파티션을 나눈다.

 

 

파티션 키 선정 시 고려 사항

• 파티션 키는 테이블 접근 유형에 따라 파티셔닝이 이뤄지도록 선정 한다.
• 데이터 관리의 용이성을 위해 이력성 데이터는 파티션 생성주기와 소멸주기를 일치시켜야 한다.
• 매일 생성되는 날짜 컬럼, 백업의 기준이 되는 날짜 컬럼, 파티션 간 이동이 없는 컬럼, I/O 병목을 줄일 수 있는 데이터 분포가 양호한 컬럼 등을 파티션 키로 선정한다.

 

인덱스 파티션

📌 인덱스 파티션은 파티션된 테이블의 데이터를 관리하기 위해 인덱스를 나눈 것이다.

 

• 인덱스 파티션은 파티션된 테이블의 종속 여부에 따라 Local Partitioned Index와 Global Partitioned Index로 나뉜다.
  • Local Partitioned Index : 테이블 파티션과 인덱스 파티션이 1:1 대응되도록 파티셔닝 한다.
  • Global Partitioned Index : 테이블 파티션과 인덱스 파티션이 독립적으로 구성되도록 파티셔닝 한다.
  • Local Partitioned Index 가 Golbal Partitioned Index에 비해 데이터 관리가 쉽다.

 

• Local Partitioned Index : 파티션과 인덱스를 모두 '판매일자' 필드를 기준으로 수행한다.
• Global Partitioned Index : 파티션은 '판태일자' 필드를 기준으로 수행하고, 인덱스는 '지점' 필드를 기준으로 수행한다.
• 인덱스 파티션은 인덱스 파티션 키 컬럼의 위치에 따라 Prefixed Partitioned Index 와 Non-prefixed Partitioned Index로 나뉜다.
  • Prefixed Partitioned Index : 인덱스 파티견 키와 인덱스 첫 번째 컬럼이 같다.
  • Non-Prefixed Partitioned Index : 인덱스 파티션 키와 인덱스 첫 번째 컬럼이 다르다.

파티션 인덱스 유형	인덱스 파티션키	인덱스키 컬럼
Prefixed Partitioned Index	판매일자	판매일자(+품목코드)
Non-Prefixed Partitioned Index	판매일자	품목코드(+거래일자)

 

• Local과 Golbal, Prefixed와 Non-prefixed를 조합하여 Local Prefixed Partitioned Index, Local Non-Prefixed Partitoned Index, Global Prefixed Partitioned Index 등으로 구성하여 사용한다. 단, Global Non-Prefixed Partitioned Index는 허용되지 않는다.

 

 

---

 

 

🎯 데이터베이스 용량 설계

 

데이터베이스 용량 설계

📌 데이터베이스 용량 설계는 데이터가 저장될 공간을 정의하는 것이다.

• 데이터베이스 용량을 설계할 때는 테이블에 저장할 데이터 양과 인덱스, 클러스터 등이 차지하는 공간 등을 예측하여 반영해야 한다.

 

💡 데이터베이스의 용량 설계는 데이터베이스 설계만큼이나 중요하다. 용량이 필요할 때마다 용량을 증설한다면 데이터베이스의 전체적인 성능이 저하되고 비용이 계속 증가할 수 있다.

 

 

 

데이터베이스 용량 설계의 목적

• 데이터베이스의 용량을 정확히 산정하여 디스크의 저장 공간을 효과적으로 사용하고 확장성 및 가용성을 높인다.
• 디스크의 특성을 고려하여 설계함으로써 디스크의 입/출력 부하를 분산시키고 채널의 병목 현상을 최소화 한다.
• 디스크에 대한 입/출력 경합이 최소화되도록 설계함으로써 데이터 접근성이 향상된다.
• 데이터 접근성을 향상시키는 설계 방법
  • 테이블의 테이블스페이스와 인덱스의 테이블스페이스를 분리하여 구성한다.
  • 테이블스페이스와 임시 테이블스페이스를 분리하여 구성한다.
  • 테이블을 마스터 테이블과 트랜잭션 테이블로 분류한다.
• 데이터베이스에 생성되는 오브젝트의 익스텐트 발생을 최소화하여 성능을 향상시킨다.
• 데이터베이스 용량을 정확히 분석하여 테이블과 인덱스에 적합한 저장 옵션을 지정한다.

 

💡 익스텐트(범위, Extent) : 익스텐트는 기본적인 용량이 모두 찼을 경우 추가적으로 할당되는 공간을 의미한다.

 

 

 

데이터베이스 용량 분석 절차

1. 데이터 예상 건수, 로우(Row) 길이, 보존 기간, 증가율 등 기초 자료를 수집하여 용량을 분석한다.
   • 예) 기초자료 수집

   NO	엔티티	테이블	Row 길이	보존 기간	초기건수	주기별 발생건수	발생 주기	년증가율(건)
   001	주문	ODDER	1,000	2	19,250	80	일	500
   002	상품	ITEM	430	2	34,502	200	일	300
   003	판매자	SELLER	310	2	12,520	15	일	150

2. 분석된 자료를 바탕으로 DBMS에 이용될 테이블, 인덱스 등 오브젝트 별 용량을 산정한다.
3. 테이블과 인덱스의 테이블스페이스 용량을 산정한다.
   • 테이블스페이스 용량은 테이블스페이스에 생성되는 테이블 용량을 모두 더한 값에 약 40% 정도를 추가하여 산정한다.
   • 예) 테이블 용량이 100M라면 테이블스페이스의 용량은 100 + (100 x 40%) = 140M 정도로 산정한다.
4. 데이터베이스에 저장될 모든 데이터 용량과 데이터베이스 설치 및 관리를 위한 시스템 용량을 합해 디스크 용량을 산정한다.

 

💡 약 40%라는 값은 절대적인 값이 아니다. 업무에 따라 확장 가능성이 높은 것은 40% 정도 확장, 가능성이 낮은 것은 20% 정도로 지정하면 된다.

 

🔔 예) 디스크 용량 계산

NO	이름	용량(G)	사용용량(M)	사용비율	파일명	파일용량
1	디스크1	3	2,700	90%	AB001_ABC01	1,700M
					AB002_ABC01	1,000M
2	디스크2	5	2,500	50%	AB001_ABC02	1,500M
					AB002_ABC02	1,000M

 

• 디스크1 : 디스크에 저장될 전체 파일의 용량이 2,700M 이고 추후에 추가될 데이터가 많지 않아 300M의 여유 공간이 있는 3G 디스크를 선택하였다.
• 디스크2 : 디스크에 저장될 전체 파일의 용량이 2,500M 이나 추후에 추가될 데이터 양이 많아 2,500M의 여유 공간이 있는 5G 디스크를 선택하였다.