[Database] - Index(인덱스)

Index (인덱스)

목적

RDBMS에서 검색 속도를 높이기 위한 기술

테이블의 컬럼을 색인화한다.

마치, 두꺼운 책의 목차와 같다고 생각하면 편하다.

데이터베이스 안의 레코드를 처음부터 풀 스캔하지 않고, B+ Tree로 구성된 구조에서 Index 파일 검색으로 속도를 향상시키는 기술이다.

파일 구성

테이블 생성 시, 3가지 파일이 생성된다.

• FRM: 테이블 구조가 저장되어 있는 파일
• MYD: 실제 데이터가 있는 파일
• MYI: Index 정보가 들어있는 파일(Index 사용 시 생성)

사용자가 쿼리를 통해 Index를 사용하는 컬럼을 검색하게 되면, 이때 MYI 파일의 내용을 활용한다. (INDEX를 사용하지 않는 경우, MYI 파일은 비어져 있다)

장점

• 키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킨다.
• 인덱스를 사용하면 테이블 행의 고유성을 강화시킬 수 있다.
• 테이블의 기본키는 자동으로 인덱스된다.
• 필드 중에는 데이터 형식 때문에 인덱스 될 수 없는 필드도 있다.
• 여러 필드로 이루어진(다중 필드)인덱스를 사용하면 첫 필드 값이 같은 레코드도 구분할 수 있다.

참고로 액세스에서 다중 필드 인덱스는 최대 10개의 필드를 포함할 수 있다.

단점

• Index 생성시, .mdb 파일 크기가 증가한다.
• 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
• 인덱스 된 Field에서 Data를 업데이트하거나, Record를 추가 또는 삭제시 성능이 떨어진다.
• 데이터 변경 작업이 자주 일어나는 경우, Index를 재작성해야 하므로 성능에 영향을 미친다.

상황 분석

• 사용하면 좋은 경우

  • Where 절에서 자주 사용되는 Column
  • 외래키가 사용되는 Column
  • Join에 자주 사용되는 Column

• Index 사용을 피해야 하는 경우

  • Data 중복도가 높은 Column
  • DML이 자주 일어나는 Column

DML에 취약

1. INSERT
   • index split: 인덱스의 Block들이 하나에서 두개로 나누어지는 현상
   • 인덱스는 데이터가 순서대로 정렬되어야 한다. 기존 블록에 여유 공간이 없는 상황에서 그 블록에 새로운 데이터가 입력되어야 할 경우, 오라클이 기존 블록의 내용 중 일부를 새 블록에다가 기록한 후, 기존 블록에 빈 공간을 만들어서 새로운 데이터를 추가하게 된다.
   • 성능면에서 매우 불리하다.
     • Index split은 새로운 블록을 할당받고 Key를 옮기는 복잡한 작업을 수행한다. 모든 수행 과정이 Redo에 기록되고 많은 양의 Redo를 유발한다.
     • Index split이 이루어지는 동안 해당 블록에 대해 키 값이 변경되면 안되므로 DML이 블로킹된다.
2. DELETE
   • 테이블에서 데이터가 DELTE될 경우, 지워지고 다른 데이터가 그 공간을 사용할 수 있다.
   • index에서 데이터가 delete될 경우, 데이터가 지워지지 않고 사용 안됨 표시만 해둔다.
   • 즉, 테이블에 데이터가 1만건 있는 경우, 인덱스에는 2만건이 있을 수 있다는 뜻이다.
   • 인덱스를 사용해도 수행 속도를 기대하기 힘들다.
3. UPDATE
   • 인덱스에는 UPDATE 개념이 없다.
   • 테이블에 UPDATE가 발생할 경우, 인덱스에서는 delete가 먼저 발생한 후 새로운 작업의 insert 작업이 발생한다.
   • delete와 insert 두 개의 작업이 인덱스에서 동시에 일어나 다른 DML보다 더 큰 부하를 주게 된다.