물리적 설계의 객체별 활동

• 테이블 설계
  • 데이터베이스의 주요 객체인 테이블을 설계
  • 테이블의 용량을 산정하기 위해서는 각각의 테이블 마다 초기 건수와 월 증가 건수 ,보관 주기가 필요
    • 업무 담당자에게 양식을 배포하여 해당 데이터를 수집
    • 조산된 데이터를 근거로 하여 테이블 총 크기를 산정
  • 데이터가 없는 빈 테이블을 생성하고, SQL문을 이용하여 해당 테이블의 행 길이를 구함
  • 계산 공식에 의거하여 크기를 계산하는데, 30% 정도의 여유 공간을 감안하여 테이블의 총 크기를 계산
  • 건수가 많을 경우, 최대 행 길이와 평균 행 길이간의 차이가 전체 테이블 크기에 큰 영향을 미칠 수 있으므로 주의가 필요
  • 필드 수 및 건수가 많을 경우, 최대 행 길이와 평균 행 길이의 차이가 거의 없다면, 무시된 블록 헤더 정보에 대한 오버 헤드로 기억 장소가 부족하여 에러를 유발할 수 있으므로 주의가 필요
       
• 인덱스 설계
  • 테이블 질의 성능 향상을 위한 인덱스 설계
  • 설계 단계 시에는 데이터가 없고 업무구현 화면의 내용이 변화하므로 정확한 인덱스 설계가 힘듦
  • 일반적으로 변경될 가능성이 가장 적은 기본키와 외래키에 대한 인덱스를 설계하고 개발이 80% 이상 완료된 후에 추가적인 인덱스 선정이 필요
  • 인덱스 선정 시 기본키는 반드시 포함하고, 왜래키의 경우 외래키 제약 조건이 부여된 경우 해당 외래키에 대한 인덱스를 생성
  • 인덱스 용량 산정은 테이블 용량 산정 시와 동일하게 별도로 구성된 빈 테이블을 사용하여 인덱스 길이를 구함
  • 헤더 사이즈를 감안하여 인덱스의 평균 행 길이를 구하고, 데이터 건수(테이블의 건수와 동일)와 길이를 바탕으로 계산 공식에 의거하여 크기를 산정
  • 필요에 따라 여러 개의 필드 집합으로 인덱스 구성 가능
       
• 파티션 설계
  테이블의 목적에 맞도록 키 값에 따라 파트별로 구성하며, 파티션 대상 테이블은 업무 요건과 테이블에 대한 접근 패턴 등을 고려하여 파티션 테이블의 장점을 최대화할 수 있도록 선정함
  • 파티션이 필요한 테이블 선정 기준
    • 절대적인 용량이 커서 문제가 될 만한 대용량 테이블인 경우 예상 데이터 건수와 보관 주기를 바탕으로 선정
    • 이력 데이터와 같이 일자별로 데이터가 관리되며, 주기적으로 삭제가 발생하는 테이블의 경우 온라인 데이터 보유 기간과 유효 보유 기간에 따라 파티션 대상 테이블로 선정
    • 대용량 병렬 배치 작업의 대상이 되는 테이블, 즉 I/O 경합이 발생하는 큰 테이블 위주로 선정
    • 일부 데이터가 손상되더라도 나머지 데이터 사용이 가능해야 하는 테이블을 선정
    • 복구를 최대한 빨리 적용해야 하는 테이블을 선정
    • 여러 노드로부터 동시에 데이터 접근이 발생하여 락 경합 발생이 우려되는 테이블을 선정
  • 파티션의 종류
    • 범위(Range) 파티션
      • 테이블의 특정 필드의 범위를 기준으로 분할하는 방법
      • 주로 이력 데이터 또는 지역별 데이터를 관리하는 테이블에 적합
      • 파티션 키를 중심으로 데이터가 어느 파티션에 존재하는지 파악할 수 있어 데이터 관리 측면에서 유리
      • 파티션 키에 따라 테이블 파티션을 수행함으로써 특정 파티션에 데이터가 평중 될 수 있음
    • 해시(Hash) 파티션
      • 파티션의 키 값에 해시 함수를 적용하여 데이터를 분할하는 방식
      • 데이터 관리 목적보다는 성능 향상을 위해 사용
      • 해시 값을 중심으로 데이터가 분산되므로 어느 파티션에 어떤 데이터가 존재할지 알 수 없으나 파티션별 데이터는 균등한 분포도를 가짐
    • 리스트(List) 파티션
      • 파티션의 키 값 리스트를 기준으로 분할하는 방법
      • 여러 개의 필드로 파티션 할 수 있는 범위 파티션과 달리 하나의 필드로만 파티션 가능
      • 별개의 각 값들이 어떤 파티션에 속해야 할지 명시적으로 지정하기 때문에 순차적이지 않고, 서로 연관성이 없는 데이터를 그룹핑하기 때문에 관리하기에 용이
           
• 키 설계
  • 테이블을 대표하면서 유일성을 확보하는 주요 필드를 키로 설계하는데, 가능하면 모든 테이블에 기본키를 지정해야 함
  • 모든 SQL에서 항상 사용되는 속성은 키 설계 시 우선 순위를 제일 높게 해야 함
  • 분포도가 좋은 속성은 키본키 선정에서 우선순위를 높게 해야 함
    • 분포도가 좋다는 의미는 서로 다른 값들이 중복없이 존재한다는 것(즉, 같은 속성값을 갖는 튜플 수가 적다는 것)
    • SQL 사용시 분포도가 좋은 속성으로 접근하면 그만큼 적은 범위의 데이터에만 접근할 수 있음
  • 데이터 타입 선택시 길이가 자주 바뀌는 필드는 VARCHAR 타입을 사용하고, 날짜 단위로 처리하는 경우는 DATA 타입보다 VARCHAR(8) 타입을 사용하며, 인덱스 유형에 따라 정렬을 활용하는데 역정렬 인덱스 활용을 검토
  • 인덱스를 사용할 수 없는 상태에서 성능 향상이 필요하면 클러스터링을 검토
  • '=' 조회를 하는 필드는 기본키 조합 시 우선 순위가 높음
  • 날자와 같은 속성은 대부분이 기간을 조회하므로, 특정 기간을 검색할 때 인덱스 순서상 날자 속성 이하에 있는 속성은 SQL의 Where절에 나타나 있더라도 데이터를 걸러주지는 못함
       
• 기타 설계
  • 시퀀스(Sequence)
    • 시퀀스는 자동적으로 유일 값을 생성하는 것으로 락을 발생시키지 않으나, 불연속인 값이 생성될 가능성이 있음(이것은 Oracle에서 제공하는 기능
    • 채번 테이블이라는 현재 키 값 중 최대값을 자기고 있는 테이블을 두어 사용하는 방법으로 연속적인 값을 갖게 하는데 편리하나 락이 발생할 가능성이 있고, 접근이 많을 경우 성능에 영향을 미칠 수 있음
    • SQL을 이용하여 해당 테이블에서 최대갑 +1 을 찾아 키 값을 생성하는 방법은 채번 테이블과 유사한 방법이지만, 락 발생 가능성이 높고 오브젝트 생성은 관리 대상이 많아지므로 가급적 시쿼스를 생성해서 사용하는 것이 좋음
  • 뷰(View)
    • 뷰는 하나 이상의 테이블로부터 유도되는 가상 테이블로 뷰 위에 또 다른 뷰 정의가 가능하고, 뷰가 정이된 기본 테이블을 제거하면 뷰도 자동적으로 제거
    • 뷰에 대한 검색 연산은 기본 테이블을 처리하는 것과 동일하며, 특정 정보만 사용하도록 하는 것이 가능하여 보안 측면에서 활용되고, 논리적 데이터의 독립성을 제공하며 성능을 향상시키는 효과도 있음
    • Insert / Update / Delete는 일부 제약이 있고 독자적인 인덱스를 가질 수 없으며, Alter 문 사용이 불가하여 정의 변경이 안되는 등의 불편함이 있음
  • 트리거(Trigger)
    • 트리거는 정의된 이벤트(데이터 입력, 갱신, 삭제)가 발생할 때마다 DBMS가 자동적으로 수행하는 사용자 정의 프로시저로, 제약 조건과 같은 데이터베이스 무결성 유지 방법
    • 어떤 이벤트가 발생했을 때 조건이 참이되면 트리거와 연관된 동작을 수행하고, 그렇지 않으면 아무 동작도 하지 않는데, 테이블 수준의 트리거와 행(row) 수준의 트리거, 이벤트 발생 이전에 동작하는 트리거와 발생 이후에 동작하는 트리거로 구분
    • 트리거를 통해 수행할 수 없는 작업은 데이터베이스 생성(CREATE DATABASE), 변경(ALTER DATABASE), 제거 (DROP DATABASE), 복구(RESTORE DATABASE) 및 로그 복구(RESTORE LOG) 등
  • 프로시저(Procedure)
    • 프로시저는 일종의 단위 프로그램으로, 한 번 정의한 후 해당 프로시저명으로 계속적으로 이용할 수 있음
    • DBMS가 프로시저 코딩을 관리하고, 해당 프로시저명으로 호출하면 그때 보관하고 있던 코딩을 실행
  • 함수(Function)
    • 함수도 일종의 단위 프로그램인데, SQL의 일반 함수와 마찬가지로 사용자 함수를 정의한 후 해당 함수명으로 계속적으로 이용이 가능
    • 프로시저와 유사하지만 반환(Return) 값을 갖는 것이 다름