SQL INDEX와 정규화

19 June 2020 - 2 mins read time
Tags: SQL Blog

참고:lawr

INDEX

개념

• INDEX는 테이블에 붙여진 색인이다. INDEX의 역활은 검색속도의 향상이다.
• 검색시 FULL SCAN이 아닌 INDEX 파일을 검색하여 검색속도를 향상시킨다.
• 책의 목차도 INDEX라고 생각하면 이해하기 쉽다. 책안에 특정한 부분을 찾고 싶은 경우 본문을 처음부터 읽어나가기 보다 목차나 색인을 활용하여 찾는편이 효율적이다.
• 대부분의 데이터베이스에서는 테이블을 삭제하면 인덱스도 같이 삭제된다.

검색 알고리즘

풀 테이블 스캔(full table scan)

• 인덱스가 지정되지 않은 테이블을 검색할 때는 풀 테이블 스캔이라 불리는 검색을 사용한다.
• 테이블에 저장된 모든 값을 처음부터 차례로 조사해 나가는 방법이다.

이진탐색(binary search)

• 차례로 나열된 집합에 대해 유효한 검색 방법이다.
• 처음부터 순서대로 조사하는 것이 아니고 집합을 반으로 나누어 조사하는 검색방법이다.

이진 트리

• 고속으로 검색할 수 있는 탐색 방법이지만 데이터가 미리 정렬되어 있어야 한다.
• 하지만 테이블 내의 행을 언제나 정렬된 상태로 두는 것은 힘들다.
• 테이블에 인덱스 작성시 테이블 데이터와 별개로 인덱스용 데이터가 저장장치에 만들어진다.
• 이때 이진 트리라는 데이터 구조로 작성된다.

INDEX의 장점

• 키 값을 기초로 하여 테이블에서 검색과 정력 속도를 향상시킨다.
• 테이블의 기본 키는 자동으로 인덱스된다.
• 인덱스를 사용하면 테이블 행의 고유성을 강화시킨다.

INDEX의 단점

• 인덱스를 만들면 .mdb 파일 크기가 증가한다.
• 여러 사용자가 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
• 데이터변경 작업이 자주 일어날 경우 인덱스를 재작성해야 할 필요가 있어 성능에 영향을 끼친다.
• 인덱스는 DML에 취약함

정규화

개념

• 데이터의 중복을 최소화 하고 이상 현상(Anomaly)이 발생하지 않도록 함수 종속을 기반으로 연관성 있는 속성들로만 엔터티를 (재)구성하는 과정
• 하나의 데이터는 한 곳에 저장한다라는 개념을 기반으로 테이블 형태를 개선해 나가는 것이다.
  • 이상현상: 불필요한 데이터 중복으로 인해 데이터의 삽입/갱신/삭제 시 발생하는 데이터 이상 현상
    • 삽입 이상: 새 데이터를 삽입할때 불필요한 데이터까지 함께 삽입되는 현상
    • 갱신 이상: 중복데이터 중 일부 데이터만 변경하여 데이터가 불일치하게 되는 현상
    • 삭제 이상: 데이터를 삭제할 때 필요한 데이터까지도 함께 삭제되는 데이터 손실 현상
  • 함수 종속: 한 엔터티 내에서 특정 속성(들)의 값을 알게 되면 다른 속성들의 값이 저절로 결정될 때
    기준이 되는 속성을 결정자 속성이라 하고, 결정자에 의해 정해지는 속성을 종속자 속성이라 한다.

정규화의 종류

제1 정규형

• 모든 속성의 값이 원자 값(atomic value)로만 구성되어야 한다.
• 중복을 제거하는 테이블의 분할이 이루어진다.
• 다중 속성값이 존재하지 않는다.

제2 정규형

• 주식별자가 아닌 모든 속성이 주식별자 속성에 완전 함수 종속된다.
• 부분 함수종속성을 찾아내서 테이블을 분할하는 것이다.
• 기본키에 중복이 없는지 조사한다.

제3 정규형

• 일반 속성들 간에 함수 종속이 존재하지 않는다.
• 기본키 이외의 부분에서 중복이 없는지를 조사한다.
• 이행적 함수 종속이 존재하지 않는다.

반정규화

• 정규화된 엔터티, 속성, 관계에 대해 시스템의 성능 향상과 개발과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링의 기법이다.
  • 엔터티(테이블) 반정규화, 속성(칼럼) 반정규화, 관계 반정규화
• 반정규화를 자주하게 되면 시스템 전반적으로 데이터 무결성이 깨질 위험이 있으므로, 꼭 필요한 경우에만 사용하애 하며 데이터 정합성을 유지하기 위한 방안을 생각해야 한다.