[MySQL] 데이터베이스 물리적 설계

물리적 설계

 

인덱스

• 인덱스를 사용하는 이유: 원하는 데이터에 신속하게 접근하기 위함
• 인덱스 파일, 데이터 파일로 구성된다.
• 인덱스 파일은 <탐색 키 값, 주소> 쌍으로 구성된다.

 

데이터 레코드를 접근하기 위해 먼저 인덱스를 찾은 후 인덱스가 가리키는 주소를 따라가 레코드에 접근하도록 데이터를 저장한다.

기본 인덱스

• 탐색 키가 데이터 파일의 기본 키(primary key)인 인덱스
• 기본 키 값에 따라 정렬된 데이터 파일에 대해 정의된다.
• 각 테이블마다 최대 한 개의 기본 인덱스를 가질 수 있다.

 

보조 인덱스

• 탐색 키 값에 따라 정렬되지 않은 데이터 파일에 대해 정의된다.
• 기본 인덱스에 비해 순차 접근이 비효율적이다.
• 밀집 인덱스(dense index)
  • 각 레코드의 키 값에 대해 인덱스에 엔트리를 유지한다.

인덱스 유형

• PRIMARY(기본 인덱스)
  • 기본키에 대해 자동으로 생성되는 고정된 이름의 인덱스
  • NULL값, 중복된 값을 가질 수 없다.
• UNIQUE(고유 인덱스)
  • 중복된 값을 가질 수 없다.
• INDEX, key(비고유 인덱스)
  • 중복된 값이 허용된다.

 

인덱스의 장단점

• 장점
  • 검색 속도가 향상 된다.
  • 소수 레코드들의 수정/삭제 연산 속도 향상
• 단점
  • 인덱스 저장 공간이 추가적으로 필요하다.
  • 삽입 / 삭제 / 수정 연산 속도가 저하된다(인덱스 파일, 데이터 파일을 같이 수정해야 하기 때문)

 

인덱스 선정 지침

• 기본키는 인덱스를 정의할 훌륭한 후보: 기본 인덱스
• 외래키도 인덱스를 정의할 중요한 후보이다.
• 한 테이블에는 가능한 3개 이하의 인덱스를 생성하는 것이 좋다.
• 자주 수정되지 않은 속성은 인덱스를 정의하지 않은 것이 좋다.
• 수정이 빈번하게 이뤄지는 테이블에는 인덱스를 많이 만드는 것을 피하는게 좋다.
• 정수형 속성에 인덱스를 생성(문자열에 비해 정수형이 빠르다.)
• 후보 키는 기본 키가 갖는 모든 특성을 마찬가지로 갖기 때문에 인덱스를 생성할 후보이다.
• 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고 데이터 삽입이 끝난 후 인덱스를 생성하는 것이 좋다.

 

질의 튜닝

• DBMS의 질의 최적화 모듈이 테이블의 크기가 작아서 인덱스가 도움이 되지 않는다고 판단하면 DBMS가 인덱스를 사용하지 않는다.
• 널 값에 대해서 일반적으로 인덱스가 사용되지 않는다.
• 인덱스가 정의된 속성에 산술연산자가 사용된 경우 인덱스가 사용되지 않는다.
• DBMS가 제공하는 내장 함수가 사용된 경우 인덱스가 사용되지 않는다.

 

역정규화

• 분해된 두 개 이상의 릴레이션들을 합쳐서 하나의 릴레이션으로 만드는 작업
• 보다 낮은 정규형으로 되돌아 가는 것이다.

 

목적

• 두 테이블에 대해 항상 혹은 대부분 조인에 의한 검색을 하는 경우
• 조인에 걸리는 시간을 절약하려는 목적이다.

 

수직분리

• 테이블의 레코드 수 및 칼럼의 수가 매우 많고, 칼럼들이 그룹화되어 각 그룹이 특정 부서 혹은 응용 프로그램에 의해서만 사용될 때
• 분리함으로써 데이터 크기 감소, 검색의 분산의 효과를 얻을 수 있다.

수평분리

• 레코드의 검색 빈도가 서로 다르다는 점을 이용하여 테이블을 분리한다.
• 데이터 크기 감소 효과를 얻을 수 있다.

칼럼 중복

• 조인하여 가져다 사용하는 칼럼의 수가 적을 경우 테이블 통합은 비효율적이다.
• 조인에 의해서 가져오는 칼럼을 중복해서 저장하는 편이 더 합리적이다.

 

테이블 기술서

• 개별 테이블에 대한 보다 자세한 문서화 수단이다.
• 테이블 하나하나에 대한 출력된 문서를 필요로 한다.

 

뷰(View)

• 하나 또는 둘 이상의 기본 테이블(base table)로부터 유도되어 만들어지는 가상 테이블(virtual table)
• 기본 테이블은 물리적으로 구현되어 데이터가 실제로 저장되지만, 뷰는 반드시 물리적으로 구현되는 것이 아니다.

• 데이터 검색
  • 뷰와 기본 테이블 간에 아무런 차이 없이 사용 가능하다.
• 데이터 변경
  • 뷰에 대한 삽입/삭제/수정 연산에는 약간의 제약이 있다.
  • 뷰에 대한 내용 변경은 곧 → 기본 테이블에 대한 내용 변경이기 때문